Mysql – Minimizing downtime during MariaDB Replication Setup

mariadbMySQLreplication

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

We have a number of large databases (~20 DBs @ 40G) with some huge ones (~3 @ 100G).
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).
We can't keep locks on the master for too long – as we disrupt business while the tables are locked.
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?

Best Answer

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.

Related Solutions

Mysql – Replication Broken, List Out of Sync Tables

With regard to the first point

check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working (This one has shell scripting I made to do the parsing for you)

Mysql – Restoring MySQL database with db dump and binlogs

They are two things that can be good for you in this instance

GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
GOOD THING #2 : The two MyISAM tables are never changing

If either one of these good things apply, then I have good news for you

STEP01) Zap All Binary Logs on the Master (OPTIONAL)

Run this command on the Master:

mysql> RESET MASTER;

If this part scares you, you could either skip it or make a copy of the binary logs before doing it

STEP02) Create the mysqldump as follows:

echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines ....  >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql

STEP03) Move the `MySQLSlaveReload.sql` to the Slave

STEP04) Load `MySQLSlaveReload.sql` on the Slave

On the Slave, load the script as follows:

mysql -u... -p... -A < MySQLSlaveReload.sql

The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql

head -22 MySQLSlaveReload.sql | tail -1

The mysqldump option --master-data=1 recorded the log file and position as of the start of the mysqldump on line 22.

Give it a Try !!!