0

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.

0

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.

  • Thank you. I understand that setting up the server for less painful backups later will require going through pain now. However, I am looking more for advice about these options you mention, like why and how I should take my existing data and set up an LVM partition, then transfer my data over to that partition or why doing a Disk sync is a worse option than creating a backup with Xtrabackup (its blocking of MyISAM is why I posted). I apologize if that wasn't clear from my original post. I've done a fair bit of research about all these options--I am looking for wisdom and experience to guide me – Hman Sep 21 '17 at 20:38
  • Again, I am grateful for the answer, don't get me wrong about that. I'm in need of guidance from those with similar experience to share ideas about what might be the best way to get out of the pickle we are in. Our goal with setting up replication is to take backups from the slave and also to duly upgrade from 5.5 to 5.6 to 5.7 while providing an option for failover in case of disaster. – Hman Sep 21 '17 at 20:55
  • Assuming failover potential is top of the list... (1) set up replication to 5.1 (Xtrabackup or rsync) (2) Change that to Master-Master, Single-writer (3) Now you have some breathing time to decide what to do next. And you have a spare machine with which to do some of the tricky next steps. – Rick James Sep 21 '17 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.