Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the difference between Mysql asynchronous replication, synchronous replication and semi-synchronous replication". In daily operation, I believe many people have doubts about the difference between Mysql asynchronous replication, synchronous replication and semi-synchronous replication. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "the difference between Mysql asynchronous replication, synchronous replication and semi-synchronous replication". Next, please follow the editor to study!

I. the difference between asynchronous replication, synchronous replication and semi-synchronous replication

Asynchronous replication: in the master-slave architecture, if the client sends a UPDATE statement, the thread thread on the master server writes the binary log to the binlog file and then returns the client result, regardless of whether the slave server has been synchronized to the relaylog of the slave server, the performance is the best. The downtime of the master server is easy to cause data inconsistency. Asynchronous replication is used by default in Mysql.

Synchronous replication: when the master server completes the update and synchronizes to all slave servers, the successful result is returned, which is inefficient and affects performance.

Semi-synchronous replication: compromise between the two methods. When the primary server completes the update, at least one of the updates is received and completed from the server, and the primary server returns success.

II. Concrete realization

Semi-synchronous requires at least 3 hosts. There is no difference between 2 and asynchronous. You need to use Mysql5.5 or above. Semi-sync replication is achieved through plug-ins in the plug-in Mysql package.

1. Build master-slave replication

Primary server

Vi / etc/ my.cnf [mysqld] datadir=/var/lib/mysqlserver-id=57 # add unique id log-bin # Open binary log creation account MariaDB [(none)] > grant replication slave on *. * to repluser@'192.168.12.%' identified by '12345log; view the current binary log location show master logs

The slave server configuration method is similar, and it is not repeated.

Vi / etc/ my.cnf [mysqld] datadir=/var/lib/mysqlserver-id=27 # add unique id log-bin # enable binary log

2. Semi-synchronous replication configuration

Primary server

MariaDB [(none)] > show plugins; # can view the installed plug-ins

Install the plug-in for the master node

MariaDB [(none)] > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; # install plug-in MariaDB [(none)] > SET GLOBAL rpl_semi_sync_master_enabled=1; # enable MariaDB [(none)] > SET GLOBAL rpl_semi_sync_master_timeout = 10000; # 10000ms is out of sync, and the master server responds directly to the client. Default 10000MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% semi%'; # to view the status of the master node

MariaDB [(none)] > SHOW GLOBAL STATUS LIKE'% semi%'; # View status variables

The configuration of two slave servers is the same.

MariaDB [(none)] > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; # install slave node plug-in MariaDB [(none)] > SET GLOBAL rpl_semi_sync_slave_enabled=1; # enable MariaDB [(none)] > SHOW GLOBAL VARIABLES LIKE'% semi%'; MariaDB [(none)] > stop slave; # Slave node configuration requires thread restart to take effect MariaDB [(none)] > start slave # you can see a slave node in the master server status variable after startup

III. Verification

Create a database on the master server, and both slave nodes can synchronize

Stop one of the slave server's Mariadb services. Synchronization is not affected

When the two slave server services are turned off, the master server will wait until the rpl_semi_sync_master_timeout time is up.

At this point, the study of "the difference between Mysql asynchronous replication, synchronous replication and semi-synchronous replication" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.