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.
Best Answer
Common question, common answer. The first time you set it up it is painful. But, if you do it one of several 'right' ways, the subsequent backups will be less painful.
LVM -- requires OS involvement. But backup takes about 1 minute, regardless of data size.
Replication -- requires a full copy. Xtrabackup can do it relatively painless for InnoDB, but blocks for MyISAM. Corollary: You should move away from MyISAM.
Disk sync (via rsync or whatever) -- takes long for 400GB, but it is clean. You must shutdown MySQL during the operation. (LVM would make it short.)
If you have an adequate backup, and can build a Slave from it, then that may be your best bet. In the future, take backups from the Slave. (Even better, clone one Slave from anothor.)
Yes, once a Slave exists, pt-table-checksums and pt-table-sync are a good idea.
Also, you need to move from 5.5 to 5.6 to 5.7. It won't be long before 5.5 is no longer supported.