1

I have the task of setting up MariaDB (10.0) replication from a master running in a datacenter to a slave running out in an AWS VPC. Reading up on the instructions on Setting Up Replication - MariaDB Knowledge Base there's mention of taking a lock on all tables on the master (FLUSH TABLES WITH READ LOCK) while using mysqldump to transfer the Databases across to the slave. This step has me concerned a slight bit for the following reasons

  1. We have a number of large databases (~20 DBs @ 40G) with some huge ones (~3 @ 100G).
  2. It takes about ~40 minutes to transfer each DB across to the slave. In total, this entire process could take up to 20 hours (assuming good conditions).
  3. We can't keep locks on the master for too long - as we disrupt business while the tables are locked.
  4. Should anything fail in the process of copying the dumps across to the slave - we'll have taken the master down in vain and may need to attempt this again.

What would be a good way to go about setting up replication in this scenario with minimal downtime to production. I'm open to some out of the box thinking - is there a way to do this by breaking things down and doing them in stages so that we can verify each stage?

EDIT I should add that we do take nightly dumps of the DBs as part of the backup strategy. Perhaps there's a way of using these to aid the process?

migrated from serverfault.com Jan 25 '16 at 16:14

This question came from our site for system and network administrators.

0

Use https://github.com/alestic/ec2-consistent-snapshot of your EBS volume using --mysql-master-status-file FILE option.

Create a new volume from that snapshot and attach to new instance or another instance you can use to copy to a remote location if needed.. The db server will only be locked for a couple seconds. I just performed this operation on a server with a 2TB server with millions of tables.

Start the slave db with data files from other server reset the slave and change your master with the correct log position.

0

Backing up with mysqldump is a slow process. Use a backup tool like Mariabackup/Xtrabackup. It takes physical backup of all data. It can take hot backups, so no locking or downtime would be needed.

Mariabackup

Xtrabackup

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.