Monday 14 July 2014

MySQL on-premise to Amazon RDS migration tips

Things to watch and do when migrating MySQL databases from ‘on-premise’ to Amazon AWS RDS
  1. Not all versions of databases can be migrated to RDS. Especially if you want to do a 0 downtime migration. Make sure you know which versions are possible, at this writing Amazon announced that it will support any old version of MySQL 5.1 and above. 
  2. In a zero downtime migration to Amazon RDS you work with mysqldump or mydumper to import the baseline data and and then you use MySQL Replication and the binary_log position to apply the additional records created during the import, the delta. That is it is possible to create a MySQL slave in the Amazon AWS Clouds! 
  3. So when you have confirmed the on-premise MySQL that you have is compatible you can then use mysqldump with the --master-data parameter to export your data including the binlog position coordinates at the time of the export. You can use mydumper if yor database is big to do this with parallel streams. You will use the coordinates and MySQL replication to catch-up with the on-premise master database when creating the MySQL slave in RDS. 
  4. Use different database parameters for different databases. 
  5. As you load the RDS database using myloader or mysql the operation might take long time depending on the size of your database. If this is the case, disable backups, it stops logging, try using one of the better spec RDS Instance classes and IOPS for the duration of the operation. You can always downsize the RDS instance after you have completed the initial load. 
  6. After you have completed the initial load, use Multi AZ which is a synchronous standby (in Oracle parlour) and schedule the backups immediately before you open your applications to the database, as initial backup requires a reboot. 
  7. Beware there is no SSH access to RDS, that means you have no access to the file system. 
  8. Get the DB Secuirty groups right and make sure your applications can access the RDS instances