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.