This comes up often enough for me that I figured I’d write it down. MySQL support Database Replication to other servers (and in any config you want, like master/slave, circular, one to many, etc, etc). Here’s how you need to set it up:
First, we need to configure the Master server. You’ll want to edit my.cnf (in FreeBSD it’s located in /usr/local/etc/). You’ll want to configure the appropriate lines as follows:
[mysqld] #There lines need to be commented out #skip-networking #bind-address = 127.0.0.1 #add binary logging log-bin=mysql-bin # Add a unique Server ID server-id = 1 # The number doesn't matter, it just needs to be unique from all of the replication members # binary logging automatic cleanup. Not needed, but good practice. max_binlog_size = 100M expire_logs_days = 5 relay-log=mysqld-relay-bin
Then you’ll need to restart MySQL:
Now we’ll need to create a replication slave user to allow the slave server to communication with us:
mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY ''; # You can use any username you want, just remember that you have to use that user when configuring the slave. Also replace the password with something strong!</ FLUSH PRIVILEGES;
At this point, since we’re already inside of MySQL, we’ll want to see what our mast logfile and position is. Make sure to write this down, because we’ll need it to setup the slave.
show master status; quit;
Now that we’re out of MySQL, we’ll need to dump the database, and then transfer it over to the slave system (this is because both databases need to match before we can start replication, or bad things happen).
mysqldump --all-databases --master-data --single-transaction --force -u root -p > mysqlbackup.sql scp mysqlbackup.sql email@example.com:~/
make sure you are now logged into the slave server
At this point, we should have the mysqlbackup.sql file inside of your user’s home folder. You don’t have to SCP it over, however you’ll need the file on the slave system. We’ll need to import it on the slave server:
mysql -u root -p < mysqlbackup.sql
Now we’ll need to configure my.cnf
[mysqld] server-id=2 #again, this is unique. It needs to be different than the master server.
Now we’ll need to log into MySQL and configure the slave. Please make note of the following before executing the following command:
- Master_Host is the IP or DNS name of the master server
- Master_User is the replication username that we setup on the master server
- Master_Password is the password that you set for the replication user
- Master_Log_File is the is the log file that you received when you ran SHOW MASTER STATUS; on the master system
- Master_Log_Pos is the log position that you received when you ran SHOW MASTER STATUS; on the master system
mysql -u root -p CHANGE MASTER TO MASTER_HOST='master.server.com', MASTER_USER='slave_user', MASTER_PASSWORD='', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=430; start slave;
That should be it. All that’s left is to verify that it’s working by running SHOW SLAVE STATUS\G on the slave system and verify that both Slave_IO_Running and Slave_SQL_Running is Yes.
If you want to setup Circular replication all you really need to do is repeat the setups on the opposite systems, except for dumping and importing the database, since it’s already in sync between the two systems. So basically on Server2 you’ll add the appropriate lines in my.cnf, restart mysql, create a replication user, and show the master status. Oh, and don’t forget to issue SLAVE STOP. On Server1, you’ll add the CHANGE MASTER TO lines point to Server2 (remember to add Serve2’s log file and log pos). Issue a SLAVE START on both servers, and verify that the Slave is working on both systems by issuing SHOW SLAVE STATUS \G on both systems.