I am not in the best situation. I inherited an Ubuntu 14.04 8 GB RAM, 8 CPU MySQL 5.5 database server with almost 400 GB of business-critical data (stored on external SSD) contained within several thousand different databases. My database administration skills and experience are nascent. I want to create a backup of this data to set up MySQL Replication, but I need to create the backup with minimized impact and downtime.
These databases are individually backed up with mysqldump about every four hours. This unfortunately means that I have no single, point-in-time, logical or raw backup of the entire database server and to top it off, binary logging is not enabled on that server. But I do have the capability to individually restore these backups.
In total, there about 250,000 tables in the database server. Of those tables, about 90,000 use the myisam engine and about 160,000 use the innodb engine.
I know there will be some downtime but I would really just like to avoid having downtime of an unknown duration during which I am obliged fully backup the data and deploy replication at the same time.
In testing, I've given thought to or tried various approaches:
- using Percona Xtrabackup
- using mysqldump with a single transaction (for innodb) and no locks for the myisam tables
- rsync'ing the mysql data directory, then gracefully shutting down the MySQL server, and rsync'ing the flushed out changes
- converting the myisam tables to innodb, then doing a mysqldump or using xtrabackup
- using my existing backups to start replication, then letting the slave catch up
- restoring my existing backups, then syncing the changes with pt-table-checksum and pt-table sync
- and the list can go on...
Without me providing excessive detail about my testing methods and results, I would like to know how you would approach this situation.
EDIT: In essence, my question is: With the goal of minimal downtime and given my scenario, how would you create a backup of the database server in anticipation of setting up MySQL Replication?
I would appreciate any advice, opinions, services, or resources you may have. Thank you.