All Linux HowTo's

MySQL Database Replication Example

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

Similar Posts: