MySQL 5 Replication
Monday, January 14th, 2008I recently setup MySQL 5 replication between two RHEL servers. I made a small writeup on what I had to do to get things working. In my configuration I am loading the initial database from the master, as opposed to manually copying it (via SCP). I’m assuming you have two working MySQL servers. Both with the mysql root password set, and “skip-networking” etc. commented in the configuration. I also assume open communication (iptables or otherwise) between servers. Remember you can test communication like this: telnet your-server 3306
Note: The slave server has to be equal or greater than the master version.
Do the following on the Master:
1) Insert this into your /etc/my.cnf (this is the most common location for this file, yours may be elsewhere):
[mysqld]
log-bin=mysql-bin
server-id=12) Open a mysql connection:
mysql -u root -p
mysql> GRANT ALL ON replicationdb.* TO slave_user@'%' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave_user@'%' IDENTIFIED BY 'slave_password';
mysql> GRANT RELOAD ON *.* TO slave_user@'%' IDENTIFIED BY 'slave_password';
mysql> QUIT;3) Replace “replicationdb” with the database you want to replicate. Use * for all. Replace “slave_password” with a password.
4) Assuming your init scripts are setup correctly, restart the MySQL service:
/etc/init.d/mysql restart
If you don’t have an init script in place, use this:
mysqladmin shutdown; ./bin/safe_mysqld&
Do the following on the Slave:
1) Insert this into your /etc/my.cnf (this is the most common location for this file, yours may be elsewhere):
[mysqld]
log-bin=slave-relay-bin
# Each server must have a unique ID.
server-id=2
# The hostname or IP of your master.
master-host=192.168.5.200
# This user should have the necessary privileges on the database(s).
master-user=slave_user
# The password generated for the slave user.
master-password=slave_password
# The database to be replicated.
# By default all databases will be replicated.
# Omit this to replicate everything, including the "mysql" database (users and privileges).
# Use multiple lines for multiple databases.
replicate-do-db=replicationdb2) Assuming your init scripts are setup correctly, restart the MySQL service:
/etc/init.d/mysql restart
If you don’t have an init script in place, use this:
mysqladmin shutdown; ./bin/safe_mysqld&
3) Open a mysql connection:
mysql -u root -p
mysql> LOAD DATA FROM MASTER;
mysql> QUIT;4) To check status of the master:
mysql> SHOW MASTER STATUS;
5) To check status of the slave:
mysql> SHOW SLAVE STATUS;
