MySQL master-slave semi-synchronous mode and database synchronization filtering

In MySQL master-slave architecture, the default is the asynchronous mode, that is, in master to save the data in the database, and then write the operation to Bin-log in response to the client. As to whether the slave synchronized the binaries and whether the local operation was completed, Master was not aware of it. The asynchronous mode is able to respond to the client at the fastest speed, reduce the user's waiting time, but in some data synchronization, high security scenarios, require the data in the slave to the maximum ability and master consistency, then the semi-synchronous mode can be used.

MySQL's semi-synchronous mode is provided by Google in the form of plugins. The main file is under ${mysql_home}/lib/plugin. The file consists of two semisync_master.so and semisync_slave.so. From the name can be seen semisync_master.so main master provided plug-ins,semisync_slave.so mainly for slave provided plug-ins. The following is the implementation of MySQL's semi-synchronous mode.

1. Environment preparation

Os:centos 6.4

mysql-version:5.5.28

slave:192.168.1.108

Master:192.168.1.101

2, MySQL master-slave installation

Reference: http://crazytechnology.blog.51cto.com/6906973/1640197

3. Installation of semisync_master.so and semisync_slave.so

In master:

mysql> Install plugin rpl_semi_sync_master soname ' semisync_master.so ';

See if you have installed

Mysql> show global variables like '%rpl% ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/1B/wKioL1VAVGmjcuNEAAESmxEcZG8419.jpg "title=" 3.png " alt= "Wkiol1vavgmjcuneaaesmxeczg8419.jpg"/>

rpl_semi_sync_master_enbled: Whether to allow semi-synchronous

rpl_semi_sync_master_timeout: Waiting for slave time-out

RPL_SEMI_SYNC_MASTER_TRACE_LEVEL: Tracking Level

Rpl_semi_sync_master_wait_no_slave: If there is no slave, wait

Start the semi-synchronous mode

Set global rpl_semi_sync_master_enbled = 1;

#如果在永久有效, configure it in the MY.CNF configuration file

[Mysqld]

rpl_semi_sync_master_enbled=1

rpl_semi_sync_master_timeout=1000# Setting time-out is 1s

In the Slave:

Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';

Start the semi-synchronous mode

Set global rpl_semi_sync_slave_enbled = 1;

#如果在永久有效, configure it in the MY.CNF configuration file

the Io_thread to restart Slave will not take effect.

Stop slave Io_thread;

Start slave io_thread;

View in Master

Mysql> show global status like '%rpl% ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6C/1F/wKiom1VAVQ-iloSNAAIevrly8sQ590.jpg "title=" 4.png " alt= "Wkiom1vavq-ilosnaaievrly8sq590.jpg"/>

You can see that the value of rpl_semi_sync_master_clients is 1, indicating that a salve has been connected.

4. Testing

In master

mysql> user leedb;

Mysql> CREATE TABLE leetest (ID int, name varchar (16));

You can see that the execution of the SQL statement takes a little longer, mainly due to waiting for Io_thread to synchronize the binary logs.

Stop Io_thread in slave.

mysql> stop slave Io_thread;

And then execute it in master.

mysql> CREATE TABLE leetest2 (ID int, name varchar (+));

You can see that master is in block state at this time

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/1C/wKioL1VAWKLD3l_3AABhyjvb6mM777.jpg "title=" 5.png " alt= "Wkiol1vawkld3l_3aabhyjvb6mm777.jpg"/>

After waiting for 10s to complete the operation, this 10s is set above the timeout time.

One thing to note is that MySQL automatically sets the semi-synchronous mode to asynchronous when slave Io_thread stops running

Mode. It will be very quick to execute other statement statements at this time.

MySQL's slave-master also supports database and table synchronization filtering functions. Filtering can be set in master, or it can be slave, but is recommended in slave. Because if the library in master, the table filter, then the bin log file will be incomplete, in extreme cases, some of the data will not be in the bin log for instant point recovery.

Database, table filtering is mainly through the slave in the following several settings

RELICATE-DO-DB: White list of database synchronization

Replicate-ignore-db: Blacklist of database synchronization

Replicate-do-table: White List of table synchronization

Replicate-ignore-table: blacklist of table synchronization

Replicate-wild-do-table: White list of tables (can support wildcard characters)

Replicate-wild-ignore-table: blacklist of tables (can support wildcard characters)

1, in the slave of the/ETC/MY.CNF configuration

[Msyqld]

relicate-do-db=syncdb

Replicate-ignore-db=ignoredb

Save the exit, and then restart the mysqld process.

2. Create two databases in master

mysql> CREATE DATABASE syncdb;

mysql> CREATE DATABASE Ignoredb;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/20/wKiom1VAXcvRlPDJAABqwxzb6yQ416.jpg "title=" 6.png " alt= "Wkiom1vaxcvrlpdjaabqwxzb6yq416.jpg"/>

View in Slave:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/1C/wKioL1VAX02xVeZYAACGZ0Hnu7M821.jpg "title=" 7.png " alt= "Wkiol1vax02xvezyaacgz0hnu7m821.jpg"/>

It can be seen that ingoredb is not synchronized with slave, logic.

You can also control a table in a database, which is no longer demonstrated here.

This article is from the "flying to the Sky" blog, please be sure to keep this source http://crazytechnology.blog.51cto.com/6906973/1640253

MySQL master-slave semi-synchronous mode and database synchronization filtering