How to setup MySQL replication with minimal downtime

master-slavemysql-replication

Basically we have a large MySQL database and we're looking to do replication to a slave (master slave setup). Do you guys have any step-by-step guide on how to do this with MINIMAL downtime on the database? Backing up the database and transferring the backup to the slave server (even through a private network) takes like 40 minutes or so. Quite a long downtime.

Best Answer

I assume you use InnoDB as a storage engine. If so, you need to turn on bin-logging. If it's not on now, you need to restart MySQL after modifying my.cnf. It is the only downtime, after which you can take dump of the database with binlog position without blocking the database:

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A

Based on this backup, restore data on the slave. After this, you can follow any MySQL replication tutorial and let slave catch up/run together with the master.

Related Solutions

Mysql Replication– restricting drop database statements

You can prevent certain commands from being written to the binlog, and thus prevent them from being replicatd, by doing

SET sql_log_bin = 1

in the current session. See more at:

http://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html

Make sure the connection you do this on is not used for other purposes (eg from within a connection pool), since no commands in it will be written do the binlog until you do

SET sql_log_bin = 0

or the connection is reestablished.

Mysql – Setting up MySQL database replication [without restarting thesql]

Lots of settings in my.cnf can be applied from within MySQL with SET GLOBAL. For instance:

mysql> SET GLOBAL log_slow_queries = On;

But some are read-only from within MySQL; log-bin is one of these.

mysql> set global log_bin = foo;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

Best Answer

Related Solutions

Mysql Replication– restricting drop database statements

Mysql – Setting up MySQL database replication [without restarting thesql]

Related Topic