Environmental Science:
- 192.168.205.17: as master server
- 192.168.205.27: as slave server
- 192.168.205.37: as slave server
Edition:
- OS: centos 7 1810 with mini install
- mariadb-5.5.60
Purpose:
By default, the replication function of MySQL is asynchronous. Asynchronous replication can provide the best performance. The main library sends the binlog log log to the slave library and ends without verifying whether the slave library has received it. This means that when the primary server or the slave server fails, it is possible that the binlog logs sent from the primary server are not received from the primary server, which will result in inconsistency between the data of the primary server and the slave server, and even loss of data during recovery. Semi-synchronization solves the problem of performance and data security integration. When stepping from the server, as long as one confirms that the synchronization is completed from the server, the result is returned without waiting for the complete replication. This solves the problem of waiting for all the results to be returned from the server when the data synchronization replication is completed, and achieves the best combination of performance and data security.
Steps:
- Install the database on all servers, initialize and run the following scripts
[root@master ~]#cat /data/maridb_yum.sh rpm -q mariadb-server ||yum install -y mariadb-server mkdir /data/{mysql,logs} chown mysql:mysql /data/{mysql,logs} sed -i '/\[mysqld\]/a log-bin=/data/logs/bin' /etc/my.cnf sed -i '/\[mysqld\]/a innodb_file_per_table = on' /etc/my.cnf sed -i '/\[mysqld\]/a skip_name_resolve = on' /etc/my.cnf sed -i 's@(datadir=).*@\1/data/mysql@' /etc/my.cnf - Modify configuration files
[root@master ~]#vi /etc/my.cnf [mysqld] server-id=17 [root@slave1 ~]#vi /etc/my.cnf [mysqld] server-id=27 [root@slave2 ~]#vi /etc/my.cnf [mysqld] server-id=37
- Setting up synchronous account number on master server and recording location or backup master server restore slave server
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos'; MariaDB [(none)]> show master logs; +------------+-----------+ | Log_name | File_size | +------------+-----------+ | bin.000001 | 30373 | | bin.000002 | 1038814 | | bin.000003 | 406 | +------------+-----------+ 3 rows in set (0.00 sec)
-
Modify change master to from the server and enable slave
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.205.17', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='bin.000003', -> MASTER_LOG_POS=406; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) 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: 406 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes - To enable master-slave semi-synchronous replication, you need to install a plug-in
[root@centos7 data]#rpm -ql mariadb-server /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so
- You can see which plug-ins are installed in the database
MariaDB [(none)]> show plugins;
- Install plug-ins on the primary server
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show plugins; ... | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | +--------------------------------+----------+--------------------+--------------------+---------+ 43 rows in set (0.00 sec)
- View the status of semi-synchronization
MariaDB [(none)]> show global variables like '%semi%' ; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | OFF | #Semi-synchronous default off | rpl_semi_sync_master_timeout | 10000 | #Time-out milliseconds, 10 seconds | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec)
- Enable semi-synchronization
MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on; Query OK, 0 rows affected (0.00 sec)
- View semi-synchronous status information
MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
- Install slave semi plug-ins on all slave nodes;
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show plugins; ... | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | +--------------------------------+----------+--------------------+-------------------+---------+ 43 rows in set (0.00 sec)
-
To view slave's semi-synchronous state and enable it, you need to restart the thread, and then see that the semi-synchronous state is on.
MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec) MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec) MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global status like '%semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.00 sec)
- View the status of the primary node at this point
MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
- Test to create a library on the primary node
MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.00 sec)
- Synchronization can be seen from the node
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
- In this way, we stop slave1 and create a database in the main node, which shows that it can be successfully copied to the slave2 node only.
[root@slave1 ~]#systemctl stop mariadb MariaDB [(none)]> create database db2; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
- If all slave nodes are stopped and a database is created from the master node, it will take 10 seconds to display the success.
[root@slave2 ~]#systemctl stop mariadb; MariaDB [(none)]> create database db3; Query OK, 1 row affected (10.00 sec)
- When the two servers are restarted, the view data is synchronized
[root@slave1 ~]#systemctl start mariadb MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec)