innobackupex cannot connect to localhost but can connect to 127.0.0.1

Problem:

[root@myserver# innobackupex --defaults-file=./temp.cnf --stream=tar /mysql-backup/ |
>     split -d --bytes=1073741824 - /mysql-backup/mysql.backup.test.tar
190327 15:58:01 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

190327 15:58:01  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/mysql/data/mysqld.sock' as 'root'  (using password: YES).
190327 15:58:01  version_check Connected to MySQL server
190327 15:58:01  version_check Executing a version check against the server...
190327 15:58:01  version_check Done.
190327 15:58:01 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /mysql/data/mysqld.sock
Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: YES).

Solution:

The solution to this annoying error is to tell innobackupex to connect to host 127.0.0.1 instead of localhost. You can either had "--host=127.0.0.1" to the command or add it into the defaults-file as "host=127.0.0.1".

Using autoexpect to write expect script

I was trying to write an expect script at the bash prompt and no matter what I tried it wouldn't work.

I was setting up a little script that Anisble would call to create the "encrypted" login-path as seen here:
https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysql-utils-intro-connspec-mylogin.cnf.html

I finally discovered there is a little program called "autoexpect" on my CentOS Linux system that will write it for me.

By entering in:
autoexpect <command>

...it goes through the questions the prompt is asking me and auto generates the script for me.

Thanks to this site:
https://likegeeks.com/expect-command/

Here is what it generated for me:

#!/bin/expect -f
set timeout -1
spawn mysql_config_editor set --login-path=/root/mypath --host=localhost --user=root --password
match_max 100000
expect -exact "Enter password: "
send -- "SuperSecurePassword\r"
expect -exact "\r
WARNING : '/root/mypath' path already exists and will be overwritten. \r
 Continue? (Press y|Y for Yes, any other key for No) : "
send -- "y\r"
expect eof

This little script actually works for both creating the file the first time and replacing it. The first time you run it, the system won't actually generate the WARNING about being overwritten but since the script is expecting, it will exit with a complaint but it still finishes that part of the script as desired. 

Pam authentication plugin dialog.dll and case sensitive

I setup a MySQL Server to use the Pam authentication plugin as described in these two blog posts:

https://www.percona.com/doc/percona-server/LATEST/management/pam_plugin.html

https://www.percona.com/blog/2017/04/21/how-to-setup-and-troubleshoot-percona-pam-with-ldap-for-external-authentication/

I came across a couple issues.

1. The username for logging into MySQL with Active Directory credentials is case sensitive. I kept trying to login with lowercase and kept getting "ERROR 1045 (28000): Access denied for user...". After switching to uppercase it finally worked.

2. I wasn't able to login from my Windows system using a GUI tool like MySQL workbench. It would error when trying to logon mention "dialog". I downloaded and installed MariabDB onto my Windows System. I found the dialog.dll file and copied it.
Copied from here:
C:\Program Files\MariaDB 10.3\lib\plugin\dialog.dll
To this location:
C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin\dialog.dll

How to grant view access to stored procedures

I have a client that uses a lot of triggers and stored procedures. Normally when I create a MySQL application user, that user has limited privileges such as SELECT, EXECUTE, SHOW VIEW, Insert, Update, Delete. When you login with this user and try to view the code inside of a stored procedure or a trigger, you will not be able to see it. It will show up as null. It is interesting that MySQL has a privilege for CREATE ROUTINE, ALTER ROUTINE but there isn't any "SHOW ROUTINE" privilege.

The work around to allow a user to view the code inside a stored procedure is to grant SELECT access to the mysql.proc table. Here is an example:

GRANT Select ON mysql.proc  TO 'MyUser'@'%';

If the user wants "Read Only" access but also needs to see triggers then I would grant the user these privileges:

GRANT Select, execute, SHOW VIEW, Trigger ON `MyDatabase`.* TO 'MyUser'@'%';

Be sure the user already exists and has a password before running these grant statements! On MySQL version before 5.7, running a grant statement like this without a password or password hash will auto create the user with a blank password.

Fixing definers for users that do not exist

Far too often, I get called to troubleshoot a problem because writes or some functionality is no longer working. When you manage thousands of database servers with thousands of databases all with different applications, you run into this problem now and again. When a developer or other DBA is terminated, their MySQL user will also get dropped on all our databases. Sometimes they have created views, events, stored procedures, functions, triggers and the definer for those objects became the user that created them by default. Unless you have monitoring step up to let you know when this happens or some kind of process to fix or prevent it, this can become very wide spread.

Fixing stored procedures, events and functions can be very easy because you can directly manipulate the values in the mysql.events table and mysql.proc table.


 UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%';

 UPDATE `mysql`.`event` p SET definer = 'root@localhost' WHERE definer='me@%';

https://dev.mysql.com/doc/refman/5.6/en//stored-routines-privileges.html

The MySQL manual warns against this with this text:

"The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table."

I've tested this by creating a basic stored procedure and changing the definer:

------------------------------------------------------------------
GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY 'testPASS!';

DROP TABLE t1;
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS `WriteToTable`;

DELIMITER ;;
CREATE DEFINER=`me`@`%` PROCEDURE `WriteToTable`()
BEGIN
INSERT INTO `t1` (`id`, `timestamp`) VALUES (NULL, NULL);
END;;
DELIMITER ;

call WriteToTable();

DROP USER 'me'@'%';

SELECT * FROM `mysql`.`proc` p  WHERE  name = 'WriteToTable';

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='me@%' AND name = 'WriteToTable';

call WriteToTable();

------------------------------------------------------------------

When I run this in the same session that was used to update the mysql.proc table, it fails with error because me@% does not exist anymore. However, if I close the session and log back in again then it works fine.

For updating triggers and views, there isn't an easy way to do it without dropping and re-creating and object.

Possible Mitigation for system performance after 'meltdown' bug patching

This is an interesting read on database performance (not specific to MySQL) after patching to secure data on multi tenant system.

https://blog.appoptics.com/visualizing-meltdown-aws/

Copied from the above article:

Applications that make frequent systems calls to read/write data either over network sockets or from disk systems will need to be better tuned for batching. Incurring small I/O operations is now more costly, and engineers will need to optimize their code to reduce the frequency of such calls. Finding the sweet spot between larger batch sizes and latency is difficult and will require software that adapts for multiple variables simultaneously. It was promising to see that the Kafka consumer libraries were able to optimize for this dynamically as network call latency increased.

Using triggers to audit database changes

I cannot count on people on my team to inform me of changes they are making to our databases so I've had to create some very basic monitoring which checks the status of certain system variables at a certain interval and saves this into a few tables. I've added triggers to these tables to audit changes and deletions.

This is an old but useful (and free) way of keeping historical information on changes in a MySQL databases. The triggers copy the old and the new value to a generic change log table (sometimes called audit log). In this manner multiple tables can use the same change log table.

In this example I have two tables, one called MyCluster and one called Tag. The Tag table uses key/value to store data while the MyCluster has specific attributes stored in columns for each cluster. Here is an example table structure for the two tables (actual table has many more columns):

CREATE TABLE `MyCluster` (
  `Profile` varchar(100) NOT NULL COMMENT 'Account where data came from',
  `DBClusterIdentifier` varchar(255) NOT NULL DEFAULT '',
  `Endpoint` varchar(255) DEFAULT NULL COMMENT 'Cluster Writer End Point Address',
  `EndpointIPAddress` varchar(50) DEFAULT NULL COMMENT 'Cluster Writer End Point IP Address',
  `ReaderEndpoint` varchar(255) DEFAULT NULL COMMENT 'Cluster Reader End Point Address',
  `ReaderEndpointIPAddress` varchar(50) DEFAULT NULL COMMENT 'Cluster Reader End Point IP Address',
  `ClusterCreateTime` timestamp NULL DEFAULT NULL COMMENT 'UTC time',
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Profile`,`DBClusterIdentifier`),
  KEY `ix_Endpoint` (`Endpoint`),
  KEY `ix_EndpointIPAddress` (`EndpointIPAddress`),
  KEY `ix_ClusterCreateTime` (`ClusterCreateTime`),
  KEY `ix_ReaderEndpoint` (`ReaderEndpoint`),
  KEY `ix_ReaderEndpointIPAddress` (`ReaderEndpointIPAddress`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cluster Specific info';


CREATE TABLE `Tag` (
  `Profile` varchar(100) NOT NULL COMMENT 'Account where data came from',
  `Type` varchar(255) NOT NULL DEFAULT '',
  `Identifier` varchar(255) NOT NULL DEFAULT '',
  `Key` varchar(100) NOT NULL DEFAULT '',
  `Value` varchar(1000) DEFAULT NULL,
  `ResourceARN` varchar(255) DEFAULT NULL,
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Profile`,`Type`,`Identifier`,`Key`),
  KEY `ix_Identifier` (`Identifier`),
  KEY `ix_Type` (`Type`),
  KEY `ix_Key` (`Key`),
  KEY `ix_Value` (`Value`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Instance Tag info.';

-- This is the audit table or log table:

CREATE TABLE `Audit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Profile` varchar(100) DEFAULT NULL,
  `Identifier` varchar(100) NOT NULL DEFAULT '',
  `TableName` varchar(100) DEFAULT NULL,
  `FieldName` varchar(100) DEFAULT NULL,
  `OldValue` varchar(100) DEFAULT NULL,
  `NewValue` varchar(100) DEFAULT NULL,
  `Type` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data comes from triggers on other tables';

-- Here are the example triggers...

-- Example of saving information from a change to column called EndpointIPAddress:

DROP TRIGGER IF EXISTS MyCluster_BU;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER MyCluster_BU BEFORE UPDATE ON MyCluster
  FOR EACH ROW BEGIN
    IF (OLD.EndpointIPAddress <> NEW.EndpointIPAddress AND OLD.EndpointIPAddress <> '' AND NEW.EndpointIPAddress <> '') THEN
        INSERT INTO Audit (
        `Profile`,
        `Identifier`,
        `TableName`,
        `FieldName`,
        `OldValue`,
        `NewValue`,
        `Type`,
        `timestamp`
        )
        VALUES (
        OLD.Profile,
        OLD.DBClusterIdentifier,
        'MyCluster',
        'EndpointIPAddress',
        OLD.EndpointIPAddress,
        NEW.EndpointIPAddress,
        'change',
        NOW()
        );
    END IF;

END$$
DELIMITER ;

-- Example of saving information after a delete occurs:

DROP TRIGGER IF EXISTS MyCluster_AD;

DELIMITER $$

CREATE DEFINER=`root`@`%` TRIGGER MyCluster_AD
AFTER DELETE
   ON MyCluster FOR EACH ROW

BEGIN

  INSERT INTO Audit (
  `Profile`,
  `Identifier`,
  `TableName`,
  `FieldName`,
  `OldValue`,
  `NewValue`,
  `Type`,
  `timestamp`
  )
  VALUES (
  OLD.Profile,
  OLD.DBClusterIdentifier,
  'MyCluster',
  'EndPoint',
  OLD.EndPoint,
  '',
  'delete',
  NOW()
  );

END; $$

DELIMITER ;


-- Example of saving information after a delete occurs on a key value table. The key is StackName and the value can be anything:


DROP TRIGGER IF EXISTS Tag_AD;

DELIMITER $$

CREATE DEFINER=`root`@`%` TRIGGER Tag_AD
AFTER DELETE
   ON Tag FOR EACH ROW

BEGIN

IF (OLD.Key = 'StackName') THEN
  INSERT INTO Audit (
  `Profile`,
  `Identifier`,
  `TableName`,
  `FieldName`,
  `OldValue`,
  `NewValue`,
  `Type`,
  `timestamp`
  )
  VALUES (
  OLD.Profile,
  OLD.Identifier,
  'Tag',
  'StackName',
  OLD.Value,
  '',
  'delete',
  NOW()
  );
END IF;

END; $$

DELIMITER ;


-- Example of saving information from a change to key value. The key is StackName and the Value can be anything.

DROP TRIGGER IF EXISTS Tag_BU;

DELIMITER $$
CREATE DEFINER=`root`@`%` TRIGGER Tag_BU BEFORE UPDATE ON Tag
  FOR EACH ROW BEGIN
  IF (OLD.Key = 'StackName' AND OLD.VALUE <> NEW.VALUE) THEN
        INSERT INTO Audit (
        `Profile`,
        `Identifier`,
        `TableName`,
        `FieldName`,
        `OldValue`,
        `NewValue`,
        `Type`,
        `timestamp`
        )
        VALUES (
        OLD.Profile,
        OLD.Identifier,
        'Tag',
        'StackName',
        OLD.VALUE,
        NEW.VALUE,
        'change',
        NOW()
        );
    END IF;

END$$
DELIMITER ;


-- Now add some data into the tables, make changes and delete some rows.

INSERT INTO `MyCluster` (`Profile`, `DBClusterIdentifier`, `Endpoint`, `EndpointIPAddress`, `ReaderEndpoint`, `ReaderEndpointIPAddress`, `ClusterCreateTime`, `CreateTime`, `UpdateTime`) VALUES ('test', 'test', 'test', '123', NULL, NULL, NULL, CURRENT_TIMESTAMP, '0000-00-00 00:00:00');
UPDATE `MyCluster` SET `EndpointIPAddress` = '456' WHERE `Profile` = 'test' AND `DBClusterIdentifier` = 'test';
DELETE FROM `MyCluster` WHERE (`Profile` = 'test' AND `DBClusterIdentifier` = 'test');

INSERT INTO `Tag` (`Profile`, `Type`, `Identifier`, `Key`, `Value`, `ResourceARN`, `CreateTime`, `UpdateTime`) VALUES ('test', 'Cluster', '123', 'StackName', 'MyTest', NULL, CURRENT_TIMESTAMP, '0000-00-00 00:00:00');
UPDATE `Tag` SET `Value` = 'MyTestIsDone' WHERE `Profile` = 'test' AND `Type` = 'Cluster' AND `Identifier` = '123' AND `Key` = 'StackName';
DELETE FROM `Tag` WHERE (`Profile` = 'test' AND `Type` = 'Cluster' AND `Identifier` = '123' AND `Key` = 'StackName');

Values that were changed or Deleted for the columns that have triggers setup will now appear in the Audit table.


PROS:

1.     Easy to implement.
2.     Very simple triggers
3.     Only one table needed to keep history for any number of tables

CONS

1.     Data type for old and new values is very generic, all data no matter what type it originally was is stored as TEXT
2.     There are no foreign key constraints between the tables. The columns in the change log table can refer to anything. Without  constraints, there is nothing to stop accidental or intentional manipulating of the numbers to values that don’t exist in the source table.
3.     Triggers add additional overhead to the system which could slow performance
4.     Writing queries to revert data is not simple

Setting up consumers - events_statements_history for Aurora instances with performance schema

I am wanting to setup Percona Monitoring Manager (PMM) for a client which uses Aurora. To do so, the documentation says you need to turn on the consumer in the performance schema so that events_statements_history os enabled. There isn't an option in the AWS console to do this and there isn't a parameter group setting to modify this so it must be done directly on the instance.


Here I will show you from the command line:

MySQL [(none)]> show global variables like 'performance_schema';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| performance_schema | ON    |

+--------------------+-------+

1 row in set (0.00 sec)

MySQL [(none)]> use performance_schema

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MySQL [performance_schema]>

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | NO      |

+---------------------------+---------+

1 row in set (0.00 sec)

MySQL [performance_schema]> update setup_consumers set enabled='yes' WHERE name = 'events_statements_history';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | YES     |

+---------------------------+---------+

1 row in set (0.00 sec)

However, after an instance restart, the changes performed to setup_consumers table will be reversed. 

MySQL [performance_schema]> show global variables like 'performance_schema';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| performance_schema | ON    |

+--------------------+-------+

1 row in set (0.00 sec)

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | NO      |

+---------------------------+---------+

1 row in set (0.01 sec)

MySQL [performance_schema]> update setup_consumers set enabled='yes' WHERE name = 'events_statements_history';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [performance_schema]> select * from setup_consumers WHERE name = 'events_statements_history';

+---------------------------+---------+

| NAME                      | ENABLED |

+---------------------------+---------+

| events_statements_history | YES     |

+---------------------------+---------+

1 row in set (0.00 sec)

I could setup a Zabbix trigger that turns it back whenever it detects a server restart or some other script. I think the easier route it to create an event that keeps turning it back on. Here is a simple example:

DROP EVENT IF EXISTS enable_statement_history;

CREATE

DEFINER=`root`@`localhost`

EVENT IF NOT EXISTS enable_statement_history

ON SCHEDULE EVERY 60 SECOND

    STARTS NOW()

DO

   update performance_schema.setup_consumers set enabled='yes' WHERE name = 'events_statements_history';

Encrypting the defaults file for logging into mysql

MySQL has an option to store credentials in a file so that you don't have to enter them at the command line when connecting to MySQL.

For example the "normal" way of connecting to MySQL from the command line would be like this:

# mysql -u<my_user> -p -h<server name>
Enter password:

OR

# mysql -u<my_user> -p<MYPassword>  -h<server name>

In the first example you have to enter in your password which won't work for scripts. In the second example you would have to hard code the password into your script or pull it out of a variable but it would get stored in the command line making it visible by anyone who can see what is running on the system. This is bad from a security perspective.

Instead you can use a defaults file and reference the file to logon to MySQL like this:

mysql --defaults-file=location_of_my_default_file.cnf -h<server name>

The defaults file only needs to contain these three lines:

[client]
user=my_user
password='123#_BLABLA'

You can also add a line for host if you want to limit the file to only be used by one server.

If your password is going to have special characters like # then make sure it is surrounded by single quotes like the above example.

The problem with this is now the password is stored in plain text and the security team at your company is not going to like it. This is better than having it in the command line history and visible in the process list but still too easy to discover. You could lock the permissions down so that only the root user can view it and only people with root access should theoretically ever be able to see it but that may still give several teams the possibility to view it and other indexing applications to easily discover it.

In MySQL 5.6, a new feature was added to encrypt this file with mysql_config_editor.

From the manual (https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html):

The encryption used by mysql_config_editor prevents passwords from appearing in .mylogin.cnf as cleartext and provides a measure of security by preventing inadvertent password exposure. For example, if you display a regular unencrypted my.cnf option file on the screen, any passwords it contains are visible for anyone to see. With .mylogin.cnf, that is not true. But the encryption used will not deter a determined attacker and you should not consider it unbreakable. A user who can gain system administration privileges on your machine to access your files could decrypt the .mylogin.cnf file with some effort.

Here is how you would create the encrypted file:

mysql_config_editor set --login-path=my_encrypted_defaults_file.cnf --host=localhost --user=root --password

And then to use it:

mysql --login-path=my_encrypted_defaults_file.cnf

OR

mysql --login-path=my_encrypted_defaults_file.cnf -h<server name>

You won't be able to view the file at my_encrypted_defaults_file.cnf. This may be "good enough" to satisfy your security team but we can do even better by using GPG. I do something similar to what is described in this Percona blog post using GPG on my laptop. I will leave that for another blog post:

https://www.percona.com/blog/2016/10/12/encrypt-defaults-file/

How to identify AWS Aurora instances with aurora_server_id

Many DBAs like to use "SELECT @@hostname" to identify a MySQL server. This can be problematic for a couple reasons. I've seen servers where the actual host name the result from "SELECT @@hostname" do not match. Someone had configured the MySQL hostname incorrectly  and the result was the hostname of a different server. In my scripts I have to check for this and make sure the resolved hostname matches the value from "SELECT @@hostname" and if it does not then do additional checking.

Furthermore, when using RDS/Aurora, you cannot rely on the value of "SELECT @@hostname" because it will give some other value, such as a private IP which can be the same on multiple systems and is not the hostname you are looking for.

In your scripts you can run:

show global variables like 'aurora_version';

This will let you know that you are on an Aurora instance. Then you can use this query to get the aurora_server_id which is the closest thing to hostname.

show global variables like 'aurora_server_id';

The aurora_server_id is not going to be resolvable unless you add an alias to it in your DNS but you can get the full resolvable server address from the AWS RDS CLI.

If you are using Python you can use the boto3 RDS docs.

Rewriting sub queries to use joins for better performance

For almost every client I have worked with, I have done some amount of profiling on their databases and recommend minor changes to improve performance. Almost every single time I see queries that use sub-selects when it is not necessary. When the data sets are small (in the thousands or less), sub selects performance is typically not an issue. However, when the number of rows in the tables get into the hundreds of thousands to millions, sub-select performance usually tanks. I've seen developers that treat MySQL tables like a queue (not a best practice) and use sub-selects. Performance will appear to be fine until the queue fills up and all of sudden the entire application is broken because they were not expecting 500,000 to a million rows in the table. Sometimes these poorly written sub-select queries will take 2~3 minutes and pile up on each other causing the MySQL server to be overwhelmed and grind to a halt.

A performance gain can almost always be gained by re-writing a query to use a join instead of a sub-select. Additionally, you need to make sure that the columns which are being used for the join have an index. If you read the "High Performance MySQL" book by the experts at Percona you will see this is one of their recommendations.

Here is a simple example I wrote to query a table that contains a list of database servers.

This query uses a sub-select. In the Explain plan's extra column, notice Using temporary; Using filesort and the the type column shows ALL meaning it is looking at all rows in the table:

This query uses a JOIN without the sub-select. If you know how to read MySQL explain plans, this one looks much better! The type columns changes to range meaning the query is no longer doing a full table scan and the filesort and temporary went away.

The data size on this table is pretty small (less than 2,000 rows). The query with a sub-select took about 65 ms while the query with the join took 50 ms.

Your mileage with re-writing queries to use a join will vary based on the data distribution, primary key, aggregation, indexes and such but the larger the table gets, a join will usually give you better performance than a sub-select. I've seen queries go from minutes to seconds or from 5 sec to 2 sec after re-writing them use a join. 

GNU Parallel for speeding up scripts

One of my favorite tools for speeding up work is GNU Parallel.

https://www.gnu.org/software/parallel/

When I write automation scripts, I try to write them to do "one simple task". For example, it will logon to a single database or a single server and so something.

In reality, I usually want the task to be repeated thousands of times and to be done in parallel to finish quickly.

Alternatively, I some times write the script to only print out the statement required to complete the task on the command line.

With GNU parallel I can use it to control my script and have it run in parallel and throttle the process. GNU parallel can limit the script to a certain number of instances of the script running at the same time.

In this example, my "createSomething.py" script only print out the commands to do the work. It will print out hundreds of commands which need to be run.

By piping it into parallel, it will automatically run 10 of those processes at a time and keep running them until they are all done.

 ./createSomething.py <input values> | parallel

In this example the file server_list.txt has multiple columns. Each column in the text file is separated by a tab. This is specified in --colsep '\t' as seen below. The values from each line will appear where is {}.

cat server_list.txt | parallel -j10 --colsep '\t' "my_shell_script.sh {}"

In this example the value which is read from server_list.txt is put into the input values after "my_python_script.py" where  {.} is:

cat server_list.txt | parallel -j10 python my_python_script.py {}

Here is another example running a query on a long list of servers:

contents of long_list_of_servers.txt would look like:
server1.com
server2.com
server3.com

Execute whatever code is in the "execute_this.sql" on every server in long_list_of_servers.txt. I use the -vvv option to log all the commands so they can be reviewed.

cat long_list_of_servers.txt  | parallel -j10 "mysql --defaults-file='my_defaults_file' -h '{}' -vvv < execute_this.sql >> outputfile.log"

If the code in "execute_this.sql" was a grant statement to create a new user or add more permissions on a list of Aurora instances, you could verify that it worked by running this next:

cat long_list_of_servers.txt | parallel -j10 "mysql --defaults-file='my_defaults_file.cnf' -h '{}' -e \"show global variables like 'aurora_server_id'; show grants for myUSER; '\" >> outputfile.log"

Now the log file will have the aurora_server_id for each servers and you can verify that each instance has the correct grants.

REPLACE function

The replace function is another one of my often used data analysis MySQL functions. However, it can create some ugly queries when it is nested many times. Here is an example I how I used it remove some text from server name. I have a client that creates replicas of their servers and names them <servername>_0, _1, _2. etc. I wrote a script to collect database sizes, table sizes, row counts and then each day run some reports on the changes. I only collect the data from the replicas and not the primary server but wanted to remove the replica names and only refer to the "cluster" name.

MySQL REPLACE() replaces all the occurrences of a substring within a string.
https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

Here is my example query that will give me the last 30 days of data:

SELECT
ss.server_name as server_name,
REPLACE(REPLACE(REPLACE(REPLACE(ss.server_name, '_2', ''),'_1',''),'_0',''),'_3','') as server_name,
ss.total_size_mb as total_size_mb,
ss.date_created,
ss.date as date
FROM growth_stats_lmp.schema_stats ss
WHERE ss.minutes_since_last_timestamp IS NOT NULL -- Removes the entries from a first run of growth_stats collection
AND ss.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY server_name,schema_name,date;

The replace function will remove the _2, _1, _0, _3 out of the servername column (if it finds those values) and display only the remaining text that hasn't been "replaced out".

Setting up sys schema for Aurora

Here is a good article on how to setup the sys schema for AWS Aurora:

https://www.datadoghq.com/blog/how-to-collect-aurora-metrics/

These are the steps I followed for my Aurora instances:

git clone https://github.com/mysql/mysql-sys.git

cd mysql-sys

./generate_sql_file.sh -v 56 -b -u root

gsed -i '10486d' gen/sys_1.5.1_56_inline.sql

mysql -u root -p -h <Aurora Server> -P 3306  < gen/sys_1.5.1_56_inline.sql

<enter password at prompt>

After setting up sys schema, you will need a user that can view the reports.

If you plan to use MySQL workbench to view the reports, some won't be available unless the user has EXECUTE on the SYS schema and PROCESS globally in addition to SELECT access to databases.  

GRANT SELECT, SHOW VIEW, EXECUTE ON sys.*  TO 'UserName'@'%';

GRANT SELECT ON peformance_schema.*  TO 'UserName'@'%';

GRANT SELECT, SHOW DATABASES, PROCESS ON *.*  TO 'UserName'@'%';

If you don't give the user SELECT access to *.* because you want to limit permissions to not include the mysql schema then you would need to GRANT SELECT to the non system databases. Also make sure the user has a password hash statement after running the GRANT statements so the user doesn't have an empty password. 

Barracuda format causes Aurora read only instances to restart randomly

As of writing this, have found a problem with using Barracuda table format in Aurora.

Per the MySQL manual, to use Barracuda, you can set ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC.

From: https://dev.mysql.com/doc/refman/5.6/en/innodb-compression-usage.html

Aurora doesn’t actually support compressed tables and will automatically change the format if you try to use ROW_FORMAT=COMPRESSED. 

Per their documentation:

Amazon Aurora doesn't support compressed tables (that is, tables created with ROW_FORMAT=COMPRESSED).

Copied from: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Migrating.RDSMySQL.Import.html

So, if you want to use Barracuda, you are left with ROW_FORMAT=DYNAMIC. I have a client who wanted to use Barracuda and was using it on a physical machine at their data center. After doing the migration to Aurora, everything automatically changed to Antelope because the table create statements were using ROW_FORMAT=COMPRESSED. We went through and changed everything to ROW_FORMAT=DYNAMIC in order to force the tables to use Barracuda. This client has a sharded application spread out over about 30 instances. Each cluster has a writer end point instance and a replica for read only traffic. After we converted the tables to  Barracuda, the read only replicas would randomly restart. The error on the application side was "org.mariadb.jdbc.internal.util.dao.QueryException: unexpected end of stream". Then they would get a number of errors related to the instance not being available. Then it would work fine for a while. 

It was hard to figure it out but eventually we traced it back to the read only replicas using Barracuda. For a while we transferred all the read only traffic to the writer end points and all the problems stopped. 

If I log onto the writer end point, you can see that I've set the innodb_file_format  and innodb_file_format_max to Barracuda (same settings for read only replica also). 

mysql> show global variables like 'innodb_file_%';

+--------------------------+-----------+

| Variable_name            | Value     |

+--------------------------+-----------+

| innodb_file_format       | Barracuda |

| innodb_file_format_check | ON        |

| innodb_file_format_max   | Barracuda |

| innodb_file_per_table    | ON        |

+--------------------------+-----------+

4 rows in set (0.09 sec)

This query shows I'm on the writer end point:

mysql> show global variables like 'innodb_read_only';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| innodb_read_only | OFF   |

+------------------+-------+

1 row in set (0.12 sec)

mysql> show global variables like 'aurora_version';

+----------------+--------+

| Variable_name  | Value  |

+----------------+--------+

| aurora_version | 1.15.1 |

+----------------+--------+

1 row in set (0.12 sec)

mysql>

mysql> use tmp;

Database changed

mysql>

mysql> DROP TABLE IF EXISTS test_table;

Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `test_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `message` varchar(255) NOT NULL,

    ->   `created_at` datetime NOT NULL,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED;

Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'tmp/test_table';

+----------+----------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

|      196 | tmp/test_table |    1 |      6 |   138 | Antelope    | Compact    |             0 |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.09 sec)

Table became Antelope (Aurora silently changes the format for you).

mysql> DROP TABLE IF EXISTS test_table;

Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE `test_table` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `message` varchar(255) NOT NULL,

    ->   `created_at` datetime NOT NULL,

    ->   PRIMARY KEY (`id`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC ;

Query OK, 0 rows affected (0.11 sec)

mysql> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME = 'tmp/test_table';

+----------+----------------+------+--------+-------+-------------+------------+---------------+

| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

|      197 | tmp/test_table |   33 |      6 |   139 | Barracuda   | Dynamic    |             0 |

+----------+----------------+------+--------+-------+-------------+------------+---------------+

1 row in set (0.10 sec)

Now the table is Barracuda. 

The issue we seem to have is something to with Aurora starting up with the setting in the parameter group for innodb_file_format_max as Barracuda. The system tables are using Antelope but when it reads a table using Barracuda, it tries to set the innodb_file_format_max to Barracuda but since it is read only it crashes. 

After the read only replica crashes, it seems to fix itself for a while. And then eventually crashes again. I'm not sure how it gets back into a bad state which allows it to crash again. Reverting all the tables back to Antelope solved the issue. 

Slave skip counter terminates the entire transaction

If you have been an admin for MySQL with replication, at some point you are going to need to skip statements on a replica that passed on the master but are failing on the slave. There are too many reason this can happen and depending on the situation it might be safe to skip or might lead to data inconsistencies on the replica.

When you skip the error, it skips the entire transaction. Even if the first part of the transaction would have been successful because there are multiple statements in the transaction and one of them fails, then all of them are skipped on the slave.

This has been accurately described by Jervin Real in this blog post:

https://www.percona.com/blog/2013/07/23/another-reason-why-sql_slave_skip_counter-is-bad-in-mysql/

Something to note is that skipping errors on Aurora is different than on normal MySQL server:

On a normal MySQL slave you would run this;

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

show slave status;

If you have thousands of errors and want to skip them all then you can put a large number in the SQL_SLAVE_SKIP_COUNTER. If you have that many error then you probably have larger issues and might want to consider rebuilding the slave to prevent data inconsistency problems.

However on Aurora you can only skip one error at a time:

CALL mysql.rds_skip_repl_error;

show slave status;

 

You don't have to stop and start replication but do need to use the stored procedure provided by the Aurora folks.

I have a client that uses Aurora as  a disaster recovery "site". The client has thousands of databases that are being replicated from their local data center into Aurora. If their local data center were to go down, they have all their data in Aurora. However, periodically some statement breaks replication and I have to skip replication if it is safe to do so.

One of the semi frustrating things I have to deal with is the widespread use of the BLACKHOLE storage engine in Aurora by my client. When replicating from the local data center to Aurora, the client does not always want to replicate every single database into Aurora. There might be hundreds of databases on a single server and the client only want to replicate one of them into Aurora. What I do is export and import all the tables for all the database into Aurora. Then I change all the tables for the databases I don't want to replicate to BLACKHOLE. However, eventually people start creating new tables, modifying existing tables on the master server which is located in the local data center and those DDL changes are replicated to the database for which I have set all the tables to BLACKHOLE. Eventually this causes replication to break. Because I don't care about the data in the BLACKHOLE tables I can skip all the errors but sometimes I have to skip hundreds of errors.

How to break replication to Aurora instance slaves

I have a client which is migrating database to the AWS cloud. We are setting up Aurora slaves from the physical data to replicate all data. Today I was updating my permissions for my own user and ran a query like this on all the physical machines:

GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY PASSWORD '*PASSWORDHASH' WITH GRANT OPTION;

That query ran fine on all the physical machine in the data center but it broke replication on every single Aurora instance that was replicating. This is because there are many permissions which are not allowed in Aurora because only the "SUPER" user can have them and that user is controlled by Amazon. When granting permissions or changing password hash, we have to be careful to only run grant statements which will not break replication on Aurora.

Here is an interesting list of other things you can learn from doing migrations to amazon rds:

https://www.percona.com/blog/2014/07/28/what-i-learned-while-migrating-a-customer-mysql-installation-to-amazon-rds/

if else / case statements in MySQL queries

Being able to change what is displayed in the query or to display the results of a different column based on criteria in a WHERE clause is super helpful. Because this is so useful and something I use so frequently I writing a blog post on it.

Here is am example query I wrote. I want to display the Aurora "EndPoint" alias when I provide an IP Address. I've created two tables, one which has RDS/Aurora Cluster details, and one with RDS/Aurora Instance details. I've pulled this information from the AWS API and stored it locally to easily query it in a relational database. If the IP Address turns out to be the writer end point then I want the c.EndPoint column value to be displayed. If the IP Address is for reader end point then I want the c.ReaderEndpoint to be displayed. If there is only one instance in the cluster then this query will always return c.EndPoint (writer end point).


    SELECT
    IF(i.IsClusterWriter = 1,  c.EndPoint,  c.ReaderEndpoint ) AS alias
    FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx';

With a case statement, I could also write it like this:

    SELECT
    (case when (i.IsClusterWriter = 1)
 THEN
      c.EndPoint
 ELSE
      c.ReaderEndpoint
 END)
 as alias
     FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx'

Other examples:

https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query

Clearing the buffer cache before starting MySQL - Flush out the file system cache

Here was as good question I found on stackexchange:

How do you empty the buffers and cache on a Linux system?

http://unix.stackexchange.com/questions/87908/how-do-you-empty-the-buffers-and-cache-on-a-linux-system

If I don't flush out the file system cache when restarting MySQL on prod severs, I frequently get timeouts when restarting. The servers I typically work with have about 250GB of RAM, 40 CPU. Something like this will happen where I have to kill the start command or it just doesn't start.

# service mysql status
MySQL (Percona Server) running (182654)                    [  OK  ]
# service mysql restart
Shutting down MySQL (Percona Server).......................[  OK  ]......................................
Starting MySQL (Percona Server).......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................^C

However, if I run this:

sync && echo 3 > /proc/sys/vm/drop_caches

MySQL starts up quick.

Starting MySQL (Percona Server)............................[  OK  ]....................

Error installing sys schema because performance schema structure is wrong

When attempting to install the sys schema which I downloaded from here, I sometimes I get this error:

mysql -u root -p -h MyServerName.com < ./sys_56.sql

ERROR 1033 (HY000) at line 47 in file: './views/p_s/processlist.sql': Incorrect information in file: './performance_schema/threads.frm'

This is related to a bug after upgrading MySQL. The structure of the performance schema is wrong because it wasn't fixed at the time of upgrade.

The way I typically fix this is by dropping performance schema and re-installing it.

First ssh into the system.

Logon to MySQL:
DROP DATABASE performance_schema;
Exit MySQL and run:

mysql_upgrade -u root -p

mysql_upgrade will re-create the performance schema with the correct structure. 

Now install sys schema. 

You will need to restart MySQL service to actually get the performance schema and sys schema to start working.