Monday, 7 July 2014

Set Up Master Slave Replication in MySQL


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.

For the process to work you will need two IP addresses: one of the master server and and one of the slave.

10.10.0.39- Master Database
10.10.0.40- Slave Database

User with sudo privileges and have MySQL installed.

rpm -qa | grep MySQL
MySQL-client-5.6.12-1.el6.x86_64
MySQL-server-5.6.12-1.el6.x86_64
MySQL-devel-5.6.12-1.el6.x86_64
MySQL-shared-compat-5.6.12-1.el6.x86_64
MySQL-shared-5.6.12-2.el6.x86_64

Configure the Master Database

Open up the mysql configuration file

vi /etc/my.cnf
bind-address = 10.10.0.39
log_bin = /data/var/lib/mysql/mysql-bin.log
binlog_do_db = newdatabase
server-id=1

service mysql restart

Grant privileges to the slave.

mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO '10.10.0.40'@'%' IDENTIFIED BY 'IctdscayK4blr';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> USE newdatabase;
Database changed

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)


mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000032 |      523 | newdatabase  |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

The File and Position details are required while configuring slave.

mysqldump -u root -p --opt newdatabase > newdatabase.sql

mysql> UNLOCK TABLES;

mysql> QUIT;


Configure the Slave Database

mysql -u root -p

mysql> CREATE DATABASE newdatabase;

mysql> QUIT;

Import the database that you previously exported from the master database.

mysql -u root -p newdatabase < /path/to/newdatabase.sql

vi /etc/my.cnf
#Slave
relay-log = /data/var/lib/mysql/mysql-relay-bin.log
log_bin = /data/var/lib/mysql/mysql-bin.log
binlog_do_db = newdatabase
server-id=2

service mysql restart

Enable the replication from within the MySQL shell.

mysql -u root -p

mysql> CHANGE MASTER TO MASTER_HOST='10.10.0.39',MASTER_USER='root', MASTER_PASSWORD='IctdsdkMSK4blr', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  523;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

The above step designates, (1) the current server as the slave of our master server. (2) It provides the server the correct login credentials. (3) Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G;

mysql> QUIT;


If there is an issue in connecting, you can try starting slave with a command to skip over it:

mysql> STOP SLAVE;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

mysql> START SLAVE;

Ref :- https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

No comments:

Post a Comment