Mysql master slave replication on centos

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

1. Working Linux OS (CentOS 6.5 – in my case)
2. Master and Slave are CentOS 6.5 Linux Servers.
3. Master IP Address is: 192.168.100.1.
4. Slave IP Address is: 192.168.100.2.

Configure Master Server for Replication

If you do not have mysql, you can install it using YUM command. If you already have MySQL installation, you can skip this step.

# yum install mysql-server mysql

Configure a MySQL in Master Server

Open my.cnf configuration file with nano editor.

# nano /etc/my.cnf

Add the following entries under [mysqld] section and don’t forget to replace lintut with database name that you would like to replicate on Slave.

# [mysqld] section
# Start Modification
# First line is probably already there
server-id = 1
binlog-do-db=lintut
expire-logs-days=7
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql-bin.log

Restart the MySQL service.

# service mysqld restart

Now we need to tell MySQL where we are replicating to and what user we will do it with. Login into MySQL as root user and create the slave user and grant privileges for replication. Replace slave_user with user and your_password with password.

# mysql -u root -p
mysql -u root -p
mysql> STOP SLAVE;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 451228 | lintut | |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.00 sec)

Write down the File, Position number, as this is where we will start the replication from.
Here it is a good idea to do a dump of your master database(s) and pipe it into your slave server.

mysqldump -u root --all-databases --single-transaction --master-data=1 > /home/datadump.sql

Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.

mysql> UNLOCK TABLES;
mysql> quit;

Upload teh database dump to slave server(coping using scp command):

scp datadump.sql root@192.168.100.2:/root

Master server suscefuly configured.

Configure slave server

First install mysql server(using yum commad):

yum install mysql mysql-server -y

Import sql dump file into MySQL:

mysql -u root -p < datadump.sql

Now we’ll do about the same thing on the slave server

nano /etc/my.cnf

# [mysqld] section
# Start Modification
# First line is probably already there
server-id = 2
master-host=192.168.100.1
master-connect-retry=60
master-user=slave_user
master-password=yourpassword
replicate-do-db=lintut
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
# Stop Modification

Now we’ll set the slave to read from the master server, starting at the record position we wrote down earlier. Make sure you use the MASTER_LOG_FILE and MASTER_LOG_POS from a few steps back.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUSG;

Verifying MySql replication

Creating test database on master server

mysql> create database lintut;
mysql> use lintut;
mysql> CREATE TABLE test (item int);
mysql> INSERT INTO test (item) VALUES (testitem);
mysql> SELECT * FROM test;

+------------+
| item |
+------------+
| testitem |
+------------+
1 row in set (0.00 sec)

On slave server run command:

mysql> use lintut;
mysql> SELECT * FROM test;

+------------+
| item |
+------------+
| testitem |
+------------+
1 row in set (0.00 sec)

If you don’t, check the MySQL error log to find out what is causing the problem