• 2019-05-04 06:36:11

CentOS MySQL Master-Slave Replication - Step by Step

 

MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves).

I would assume that you have MySQL installed on both servers, so we would get straight into the Master-Slave setup.

I would use the following two servers:

192.168.1.1 - Master
192.168.1.2 - Slave

1. We are going to start with the master:

- SSH to the master server
- Edit the /etc/my.cnf file

vi /etc/my.cnf

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

server-id = 1
binlog-do-db=my_database
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/my_database.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

- Restart mysql

service mysql restart

- Create the replication user (change the Pass-Goes-Here part with your new pass):
- Note this goes on the slave, so that the master could then connect to the slave server

  GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'Pass-Goes-Here';
  FLUSH PRIVILEGES;

New we have to export the database that we would like to replicate.

- Open a second SSH session

In the first session run:

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

You would see something like:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 11128001 | my_database  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take a note of the the File (mysql-bin.000003) and Position (11128001) numbers

While the tables have been locked with the read lock - go ahead and export the database in question via the Second SSH session without closing the first one!

mysqldump my_database > /root/my_database.sql 

Once that has been done unlock the tables via the first ssh session

  UNLOCK TABLES;
  quit;

Now we are ready to start with the slave setup. Go ahead and copy the dump from the master to the slave.

scp /root/my_database.sql [email protected]:/root/

Then SSH to the slave server.

- Edit the my.cnf:

vi /etc/my.cnf

Under the [mysqld] section add the following and update the details accordingly:

server-id = 2
replicate-do-db=my_database
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/my_database.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

Now import the dump file that we exported in earlier command and restart the MySQL service.

mysql -u my_database -p < /root/my_database.sql

Note you might need to create the database before importing the data:

mysql
CREATE DATABASE my_database;

---

Login into MySQL as root user and stop the slave:

stop slave;

Then in order to start the replication run the following and make sure th update the details accordingly:

CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='Pass-Goes-Here', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11128001;

start slave;
show slave status\G

Then when you run the show slave status command you should see something like this:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 441654154
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: my_database
             Master_Server_Id: 1
             Master_Info_File: /var/lib/mysql/mysql-master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
                Auto_Position: 0
1 row in set (0.00 sec)

To fully test the replication you can try creating a table on the master and then check if it was successfully replicated over to the slave.

This is pretty much it. :)