This article explains how to install MySQL 5.1 on Redhat/CentOS and configure replication to a slave MySQL server. In this article, the master host has the IP address “192.168.122.7” and the slave has IP address “192.168.122.6”. Remember to restart the MySQL server if and when you make changes to the “/etc/my.cf” file. Note that on Debian systems, the my.cf file is located “/etc/mysql/my.cf”.
Install MySQL on both the master and slave hosts:
yum install mysql-server mysql-client chkconfig mysqld on service mysqld start
On both hosts, ensure the firewall is allowing TCP port 3306. Use “system-config-network” or edit the “/etc/sysconfig/iptables” file to include the following
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
And then restart iptables:
service iptables restart
Edit the “/etc/my.cf” file on the master. Add the following to the “[mysqld]” section:
log-bin = mysql-bin server-id = 1 binlog-do-db = my_rep_db
Edit the “/etc/my.cf” file on the slave. Recall that the IP address “192.168.122.7” refers to the master. Add the following to the “[mysqld]” section:
server-id = 2 master-host = 192.168.122.7 master-port = 3306 master-user = agixrep master-password = welcome replicate-do-db = my_rep_db
Create the database (and INSERT a little data into it) on the master and grant access to the (a new user) “agixrep” user:
mysql -u root -p CREATE DATABASE my_rep_db; USE my_rep_db; CREATE TABLE my_table (names VARCHAR(100)); INSERT INTO my_table SET names=andrew; INSERT INTO my_table SET names=sally; GRANT REPLICATION SLAVE ON *.* TO 'agixrep'@'%' IDENTIFIED BY 'welcome'; FLUSH PRIVILEGES;
Dump the database from the master:
mysqldump -u root -p my_rep_db > MyDatabaseBackup.sql
Now import the database into the slave:
mysql -u root -p my_rep_db < myDatabaseBackup.sql
Get the log file name from the master server:
SHOW MASTER STATUSG;
Using the results from the above command, issue the following command on the slave (within MySQL). Change the log file name to match that of the master.
CHANGE MASTER TO MASTER_HOST='192.168.122.7', MASTER_USER='agixrep', MASTER_PASSWORD='welcome', MASTER_PORT=3306, MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;
Start the slave replication from the slave:
mysqladmin start-slave
At this point you should be able to INSERT (for example) more data into the master and expect it to be replicated to the slave. Use the following troubleshooting commands:
Check the master status - on the master host:
SHOW MASTER STATUSG
Check the slave status - on the slave host:
SHOW SLAVE STATUSG
