MySQL Slave Replication in 6 Quick Steps

Update: Apparently the master connection info gets cached in /var/lib/mysql/master.info so whenever you change the settings in my.cnf you need to remove it before you restart mysql.

1.) Make sure you can connect to the designated master instance of MySQL from the slave machine.

2.) Enable slave on the master in /etc/mysql/my.cnf:

server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log #/var/log/ can not be a symlink.
expire_logs_days       = 10
max_binlog_size         = 100M
binlog-format           = ROW
binlog_do_db           = my_database

3.) AppArmor does not follow symlinks so if you’ve symlinked the log dir you need to change all instances of for instance /var/log/ in my.cnf to the directory that /var/log/ actually points to. You also need to add the directory you’re pointing to in /etc/apparmor.d/usr.sbin.mysqld, the new rows should look like the original /var/log/mysql rows. This is very important, MySQL won’t even start otherwise, no errors no nothing.

4.) Restart MySQL (and possibly apparmor): /etc/init.d/apparmor restart and /etc/init.d/mysql restart.

5.) Put this in the slave my.cnf and restart the slave MySQL server:

server-id         = 2
master-host     =ip_to_master
master-connect-retry=60
master-user=master_mysql_user
master-password=master_mysql_password
replicate-do-db=my_database

6.) Make sure you have SSH access from the slave to the master with key and run the following on the slave:

mysql -u slave_mysql_username --password=slave_mysql_password -e 'slave stop;'
mysql -u slave_mysql_username --password=slave_mysql_password -e 'DROP DATABASE my_database; CREATE DATABASE `my_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;'
ssh master_ip 'mysqldump -u master_mysql_username --password=master_mysql_password --opt --master-data my_database > my_database_replica.sql'
scp master_ip:/root/my_database_replica.sql my_database_replica.sql
mysql -u slave_mysql_username --password=slave_mysql_password --default_character_set utf8 my_database < my_database_replica.sql
mysql -u slave_mysql_username --password=slave_mysql_password -e 'slave start;'

Replace with your own details where applicable.

Voila, your’re done, slave is up and running!

Related Posts

Tags: , , ,

This entry was posted on Monday, March 11th, 2013 at 3:42 am and is filed under Database, Linux. Comments are currently closed.