Shulou(Shulou.com)06/01 Report--

First, background introduction

MySQL master-slave replication can solve the problem of single point of failure, but there are some hidden dangers in its asynchronous working characteristics, such as the master node has not yet written binlog after the transaction is committed. At this time, after the master node fails, the master node thinks that the transaction has been committed, and the slave node can not get the information of the transaction from the master node. It will cause data loss after promoting the master node. Google introduced semi-synchronous replication for versions of mysql after 5.5

Introduction of the principle

The so-called semi-synchronous replication means that when one master has more than one slave, or one master and one slave, the master node waits for at least one slave node to write to the relay log after the transaction is committed, which ensures that when the master node fails, all operations are saved in the relay log. When the waiting time for the slave node exceeds the set range, it will reply asynchronously.

III. Environmental introduction

This experiment uses CentOS7.4 system, 5.5.56-MariaDB, and the experimental topology is shown in the following figure:

Use a log server (or slave server) as the server to synchronize with the master node, and the other slave servers to synchronize the binlog through the log server. Since the log server itself only needs to record binlog, it does not need the data to be executed on it, and the storage engine can use blackhole

IV. Operation steps

1. Master node server operation

(1) start the master node service, and the configuration file is as follows

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Log_bin=mysql-binlog

Slow_query_log=ON

Server-id=10

Innodb_file_per_table=ON

Binlog_format=ROW

(2) create an account copied by master and slave

MariaDB [mysql] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [mysql] > flush privileges

(3) install and start the semi-synchronous plug-in (writable configuration file is permanently effective)

MariaDB [(none)] > install plugin rpl_semi_sync_master soname 'semisync_master.so'

MariaDB [(none)] > set global rpl_semi_sync_master_enabled=ON

(4) confirm plug-ins and functions to start normally

MariaDB [(none)] > show global variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 10000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

MariaDB [(none)] > show plugins

two。 Log server operation

(1) start the log server. The configuration file is as follows

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Read_only=ON

Relay_log_purge=ON

Slow_query_log=ON

Server-id=20

Innodb_file_per_table=ON

Default_storage_engine=blackhole

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

(2) close read_only, create a master-slave copy account, and then open it.

MariaDB [(none)] > set global read_only=0

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [(none)] > flush privileges

MariaDB [(none)] > set global read_only=1

(3) install and start the semi-synchronous plug-in

MariaDB [(none)] > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

MariaDB [(none)] > set global rpl_semi_sync_slave_enabled=ON

(4) confirm plug-ins and functions to start normally

MariaDB [(none)] > show global variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | ON |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

MariaDB [(none)] > show plugins

(5) specify the master server and the current binlog log and position of the master server

MariaDB [hellodb] > change master to

-> master_host='172.16.10.30'

-> master_user='bak'

-> master_password='bakpass'

-> master_port=3306

-> master_log_file='mysql-binlog.000003'

-> master_log_pos=7805

-> master_connect_retry=10

(6) start the slave node (can refer to start the specified thread type, not all start)

MariaDB [hellodb] > start slave [IO_THREAD | SQL_THREAD]

(7) View the status of slave nodes (Slave_IO_Running and Slave_SQL_Running show that Yes is successful)

MariaDB [(none)] > show slave status\ G

3. Verify that the log server blackhole storage engine is in effect

(1) the master node creates a database and table

MariaDB [(none)] > create database ark

MariaDB [(none)] > use ark

MariaDB [ark] > CREATE TABLE `students` (`StuID` int (10) primary key, `Name` varchar (50))

(2) confirm the binlog log and position of the current primary node

MariaDB [ark] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-binlog.000003 | 8638 | |

+-+

(3) confirm the current binlog log and position of the log server

MariaDB [ark] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-binlog.000003 | 1104 | |

+-+

(4) insert any bar of data on the master server, and observe the binlog log and position of the master node again.

MariaDB [ark] > insert into students (stuid,name) values

MariaDB [ark] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-binlog.000003 | 8818 | |

+-+

(5) View the relay log on the log server to update

MariaDB [ark] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.30

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000003

Read_Master_Log_Pos: 8818

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 1545

Relay_Master_Log_File: mysql-binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Exec_Master_Log_Pos: 8818

(6) and the binlog log on the log server has been updated.

MariaDB [ark] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-binlog.000003 | 1309 | |

+-+

(7) but there is no data in the table at this time, indicating that the blackhole engine is working properly.

4. Operate from node server

The slave node takes the log server as the master node, and all synchronization points to the log server.

(1) start the mysql service of the slave node, start the relaylog log, and change serverid

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Read_only=ON

Relay_log_purge=ON

Slow_query_log=ON

Server-id=30

Innodb_file_per_table=ON

(2) specify the master server and the current binlog log and position of the master server

MariaDB [(none)] > change master to

-> master_host='172.16.10.40'

-> master_user='bak'

-> master_password='bakpass'

-> master_port=3306

-> master_log_file='mysql-binlog.000003'

-> master_log_pos=1309

-> master_connect_retry=10

(3) start the slave node (can refer to start the specified thread type, not all start)

MariaDB [(none)] > start slave [IO_THREAD | SQL_THREAD]

(5) View the status of slave nodes (Slave_IO_Running and Slave_SQL_Running show that Yes is successful)

MariaDB [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.10.40

Master_User: bak

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-binlog.000003

Read_Master_Log_Pos: 1309

Relay_Log_File: mysql-relaylog.000002

Relay_Log_Pos: 532

Relay_Master_Log_File: mysql-binlog.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. Verification of results

(1) View the upstream node on the log server

MariaDB [(none)] > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 20 | | 3306 | 10 | |

+-+

(2) View the upstream node from the node

MariaDB [(none)] > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | |

+-+

| | 30 | | 3306 | 20 | |

+-+

(3) create a database on the main database for verification, and the operation is completed.

MariaDB [(none)] > create database wa

MariaDB [(none)] > show status like'% semi%'

The number of times Rpl_semi_sync_master_no_tx failed to receive slave submissions successfully

Supplementary note:

The 1.blackhole storage engine discards all operations on data, but operations on database structure are performed (such as creating databases, tables).

Future versions of 2.Mariadb5.5 or MySQL5.7 support multiple masters and one slave, and the slave server can become a unified log server for multiple MySQL servers, but it is required that the database names on multiple MySQL servers must be different, and all slave servers use different replication accounts, because each dump thread on the master server needs to be initiated with a different account.

MariaDB [(none)] > show processlist\ G

* 3. Row * *

Id: 7

User: bak

Host: 172.16.10.40:47972

Db: NULL

Command: Binlog Dump

Time: 15921

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Progress: 0.000

* * 5. Row * *

Id: 17

User: qwe

Host: 172.16.10.50:58968

Db: NULL

Command: Binlog Dump

Time: 1866

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Progress: 0.000

3. If master-slave replication is enabled first, and then switch to semi-synchronization, you only need to execute

MariaDB [(none)] > stop slave io_thread

MariaDB [(none)] > start slave io_thread

4. If the server restarts, the plugin plug-in and master-slave replication function will start automatically after starting the MySQL server, but the semi-synchronization feature will not be started. This is the default asynchronous synchronization (or this option will be written to the configuration file after the semi-synchronization is completed). You need to start the semi-synchronization feature and io_thread manually, and execute the command as follows:

Primary node:

MariaDB [(none)] > set global rpl_semi_sync_master_enabled=ON

Slave node:

MariaDB [(none)] > set global rpl_semi_sync_slave_enabled=ON

MariaDB [(none)] > stop slave io_thread

MariaDB [(none)] > start slave io_thread

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.