Set Up Replication in MySQL

MySQL replication is a process that allows you to easily maintain multiple copies of MySQL data by having them copied automatically from the main-server to a Replica-server database. This can be helpful for many reasons including facilitating 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 main-server and one of the Replica-server.

10.10.0.39- main-server Database
10.10.0.40- Replica-server 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 Main-Server 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 Replica-Server Database

mysql -u root -p

mysql> CREATE DATABASE newdatabase;

mysql> QUIT;

Import the database that you previously exported from the main-server 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 main 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 main-server 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 :- digitalocean.com