Moving data from MySQL into Aurora/RDS

Migrating databases from your data center to Amazon RDS or Amazon Aurora can be fun. Here are some tips to help.

If you data set is larger than a few GB, use mydumper and myloader instead of a normal MySQL dump. It will go a lot faster because mydumper and myloader is multi-threaded. Using these tools does introduce some caveats though.

Aurora will not let the user doing the restore create definers for other users. If you try this you will get this misleading error:

ERROR 1227 (42000) at line 1902: Access denied; you need (at least one of) the SUPER privilege(s) for this

If you are migrating a schema with routines (functions or stored procedures), events, triggers, or views then you are going to have definers for the code in each of those. You can either change all the definers in your schemas before you dump so they are standardized or change them after the dump but before importing into AWS.

This is the process I am using to dump and restore from a client's data center to Aurora. My client has a long running query killer on their master servers so I generally perform the dumps on a read-only replica (slave). If I must perform the dump on the master, then I must disable the event that performs the query killing during the duration of the dump.

# 1. Create a dump of objects with no data excluding stored procedures, functions, events of views.
# 2. Restore dump on new server.
# 3. Re-create the users on new server but not the grants for any stored procedures, functions, or events. Remove/replace any grants which are not compatible with RDS/Aurora.
# 4. Create a dump of the the objects without data (a second time) but this time include stored procedures, functions, events and  views.
# 5. Get a list of the unique definers on the source system. Do a find and replace of all the unique definer users and replace with standard definer.
# 6. Restore the dump on the new server as the same user which is the definer.
# 7. Dump the grants from the source.
# 8. Remove/replace any grants which are not compatible with RDS/Aurora.
# 9. Apply all the grants including stored procedures, functions, events.
# 10. Do a full dump and restore of all data and all objects

The reason for this multi stop process is you cannot create objects which have a definer if the user has not been created yet. You cannot grant permissions to objects if the object has not been created yet. This is why you cannot always do a straight dump on a new machine. Restoring to RDS adds in more steps because the definers must be the same user which is doing the restore.

Here is a great white paper written by the Aurora team lead at Amazon:

https://d0.awsstatic.com/whitepapers/Migration/amazon-aurora-migration-handbook.pdf

What happened to deep db?

When I was at Percona Live last year, there seemed to be a lot of excitement around what DeepDB could do for MySQL users. Their sales people kept hounding me to try it out but I didn't have time. I saw some news articles that they have open sourced the technology and now are being called Deep Software Foundation (http://www.deepis.org).

They had a lot of good blog posts and I really want to see how this technology progresses. Here are some interesting blog posts:

http://dev.deepis.com.473elmp01.blackmesh.com/blog/innodb-writing-challenges
http://dev.deepis.com.473elmp01.blackmesh.com/insights/blog/understanding-science-databases
http://dev.deepis.com.473elmp01.blackmesh.com/insights/blog/reimaging-science-databases
http://dev.deepis.com.473elmp01.blackmesh.com/blog/mysql-keep-grumbling

References to news articles:

https://finance.yahoo.com/news/deep-information-sciences-goes-open-130000559.html

https://www.businesswire.com/news/home/20170502005138/en/Deep-Information-Sciences-Open-Source-Relaunches-Deep

Disabling selinux on CentOS

If you try to run MySQL with selinux running on CentOS, you will get vague permission denied error like the following. It is frustaring to figure out why MySQL will not start because it does not point to SElinux being the problem.

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2017-04-20 21:01:43 20164 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create

Starting MySQL (Percona Server). ERROR! The server quit without updating PID file


rm: cannot remove `/var/lib/mysql/SERVERNAME.pid': Permission denied
 ERROR! The server quit without updating PID file (/var/lib/SERVERNAME.pid).

Check your log file to see what errors MySQL is generating during the start up process.

You can turn it off like this:

echo 0 >/selinux/enforce

Credit to:

https://www.cyberciti.biz/faq/howto-turn-off-selinux/

More things to know about events in MySQL

Events in MySQL are very useful for having some action kick off on a regular basis. I've used them for adding/removing partitions, killing long running queries and archiving data. However, when working with replication, they can some times be tricky. The data charmer wrote a blog post a few years ago which is useful:

http://datacharmer.blogspot.com/2009/03/something-to-know-about-event-scheduler.html

This is also a good reference:
http://anothermysqldba.blogspot.com/2013/05/using-mysql-event-scheduler.html

In MySQL only the master has running events. The event itself is replicated to the slave but is "SLAVESIDE_DISABLED". The tricky part is when you want to promote a slave to be a master. The event does not become ENABLED until you run an alter command like this:

ALTER EVENT <schema_name>.<event_name> ENABLE;

Also turning on the event scheduler does not enable individual events.

Your failover process could include a script that checks the slave for all events that are SLAVESIDE_DISABLED and then changes them to ENABLE when promoting a slave to a master. One of the tricky parts I have also experienced is when adding a new slave-replica to a cluster. I would copy over a previous backup from XtraBackup and then my script would add the slave into the cluster. If the backup contained events all the events on the SLAVE would be ENABLED. If the event was doing some thing like adding or removing partitions then I would notice that replication would break because the slave was doing what the master was also doing and when the statements were replicated to the slave, they would fail. I needed to add a check to my script which adds new slaves to a cluster and make all the events SLAVESIDE_DISABLED by running this on each event on the new replica:

ALTER EVENT zabbix.RotatePartitionedTables disable on slave;

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Today I was trying to restore a backup onto a server and then add that new server into a replication cluster. I have a script that automatically does this by capturing the binary log position and log file that the slave should use when attaching to the master. Then the slave can use the binary log and relay file to catch up.

Each time I attempted this I would get this error:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

I was wondering, who the heck keeps purging the binary logs before my new slave has time to replicate the data?

I checked the my.cnf file for the master and the logs are set to expire after 14 days. I was using a backup taken from this morning.

I checked the root's crontab and there was a little bash script running every hour doing this:

#!/bin/bash
time_string=$(date +"%Y-%m-%d %H:%M:%S" -d  "1 hour ago"); mysql -uUSERNAME --password=PASSWORD -e "purge binary logs before '${time_string}'"

That little bash script is purging all the binary logs from an hour before it starts so my script wasn't able to attach to the master and replicate because the log file which my backup was referencing files that had already been purged.

This is an extremely busy server that generates a lot of logs so I can see why this was here.

I temporary commented out the entry, started a new backup and finished adding my new slave into the server.

I also added an FYI to /etc/motd so that when someone logs in to the serverr via SSH they get a message indicating that this cron job is running.

pt-deadlock-logger

There are a number of options to run the deadlock logging tool but there is an actual example:

pt-deadlock-logger h=my_logging_server.com --user=USERNAME --password=PASSWORD --dest h=my_server.com,D=percona_schema,t=deadlocks,u=USERNAME,p=PASSWORD --create-dest-table --daemonize

This is the most useful free way I've found to capture deadlocks in MySQL.

Yet another meaningless MySQL error: Incorrect key file for table

I was running a data collection query against all the MySQL servers for a client and I kept getting an error with a message like this: "Incorrect key file for table '/tmp/#sql_4d51_0.MYI'; try to repair it"

I was thinking...what? That is entirely meaningless to me. Initially, I wasn't even sure what was causing that error. Every time I gather data from a server, I import it to different server for safe keeping. I thought it was the import process that had "incorrect key". After some troubleshooting, I narrowed it down to a single server which I was running the query on.

I found this answer here: http://stackoverflow.com/questions/11805793/incorrect-key-file-for-table-tmp-sql-3c51-0-myi-try-to-repair-it

However, the accepted answer was wrong for my case. The next answer was more correct but still not exactly what had happened. I logged onto the server and /tmp wasn't full. The data drive where the MySQL data files are stored was full! I don't know why but this particular server was not alerting in the monitoring system but I happened to find this problem indirectly by trying to run a query on the MySQL instance.

I wish that MySQL error like this were more clear about what are some of the potential causes of this error message.