Environmental Science:

• 192.168.205.17: as master server
• 192.168.205.27: as middle server
• 192.168.205.37: as slave server

Edition:

• OS: centos 7 1810 with mini install
• mariadb-5.5.60

Purpose:

If there may be a master server and a read server in the production environment, and there may be multiple slave servers, but the performance of the master server will decrease with the increase of the slave server, so it is better for the master server to have only one slave and the other slave servers to replicate from the slave server. Pressure on the primary server so that the intermediate server replicates exclusively to minimize the impact on the performance of the primary server

Steps:

1. Configure the master server
2. Backup intermediate server
3. Configure the slave server
4. test

Configure the master server

1. Install three servers

   [root@centos7 ~]#yum install mariadb-server
   [root@centos7 ~]#mkdir /data/{mysql,logs}
   [root@centos7 ~]#chown mysql:mysql /data/{mysql,logs}  

2. Modify the configuration file of the primary server

   [root@master ~]#vi /etc/my.cnf
   [mysqld]
   log-bin=/data/logs/bin
   datadir=/data/mysql
   server-id=17     

3. Restart service

   [root@master ~]#systemctl start mariadb

4. Loading a test database

   [root@master ~]#mysql < hellodb_innodb.sql 

5. Create a hatted account

   MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';

6. Back up the database and copy it to the intermediate node

   [root@master ~]#mysqldump -A --single-transaction --master-data=1 > /data/all.sql
   [root@master ~]#scp /data/all.sql 192.168.205.27:/data 

   Configuration of intermediate cascade servers

7. Setting up intermediate servers

   [root@middle ~]#vi /etc/my.cnf           
   [mysqld]
   datadir=/data/mysql
   log-bin=/data/logs/bin
   server-id=27
   read-only
   log_slave_updates

8. Modify backup data

   [root@middle ~]#vi /data/all.sql 
   CHANGE MASTER TO
   MASTER_HOST='192.168.205.17',
   MASTER_USER='repluser',
   MASTER_PASSWORD='centos',                   
   MASTER_PORT=3306,

9. Import data

   MariaDB [(none)]> source /data/all.sql

10. Start threads

    MariaDB [(none)]> start slave;
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.205.17
                      Master_User: repluser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: bin.000003
              Read_Master_Log_Pos: 7973
                   Relay_Log_File: mariadb-relay-bin.000004
                    Relay_Log_Pos: 604
            Relay_Master_Log_File: bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

11. Back up the data of the intermediate server and copy it to the slave server

    [root@middle ~]#mysqldump -A --single-transaction --master-data=1 > /data/middle.sql
    [root@middle ~]#scp /data/middle.sql 192.168.205.37:/data

    Modify slave server

12. Modify the slave server configuration file

    [root@slave ~]#vi /etc/my.cnf
    [mysqld]
    datadir=/data/mysql
    server-id=37
    read_only

13. Modify the backup file middle.sql

    [root@slave ~]#vi /data/middle.sql 
    CHANGE MASTER TO 
    MASTER_HOST='192.168.205.27', 
    MASTER_USER='repluser', 
    MASTER_PASSWORD='centos', 
    MASTER_PORT=3306,

14. Import data and start services

    [root@slave ~]#mysql < /data/middle.sql   
    [root@slave ~]#systemctl start mariadb

15. Start threads

    MariaDB [(none)]> start salve; 
    MariaDB [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.205.27
                      Master_User: repluser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: bin.000005
              Read_Master_Log_Pos: 326
                   Relay_Log_File: mariadb-relay-bin.000003
                    Relay_Log_Pos: 604
            Relay_Master_Log_File: bin.000005
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

test

1. Testing to build libraries on the primary server

    MariaDB [(none)]> create database zhaoli;
    Query OK, 1 row affected (0.00 sec)

1. View the database separately in the middle and slave nodes, and the synchronization is successful

   MariaDB [(none)]> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | db1                |
   | db2                |
   | hellodb            |
   | mysql              |
   | performance_schema |
   | test               |
   | zhaoli             |
   +--------------------+
   8 rows in set (0.00 sec)