Replication of MySQL

If you have a typographical disorder, you can view the original address of my personal blog

MySQL Master-slave replication

General website Business is read more write less, and general concurrency system bottleneck in the database, so in this case generally will use read and write separation to reduce the main library read pressure, thereby increasing the system concurrency, now do not say read and write separation related technology, said to achieve read and write separation on the database has a basic requirements, That is, the main library and from the library data consistency problem, even if not absolute strong consistency, at least to achieve relative consistency, here depends on the MySQL database comes with some features and configuration

Schematic diagram

Image is a more image from the Internet to find a picture:

Explanation of principle

The principle is to use Binlog to synchronize, Binlog will record the database write operation, will change the record to Binlog, from the library basic real-time read the main library binlog, and then perform this change in its own database.

Detailed process:

1. Master will change the write to the Binlog file

2. Start salve,salve have an IO (work) line subroutines connect master to get the change

3. Master initiates a binglog dump thread used to "host" the IO thread from the library, which is a daemon thread that reads events from Binlog to the IO thread,注意这里如果是异步复制，那么主库并不会在这里等待而会直接给调用的client返回sql执行结果

4. The IO thread will change to write to Salve's Relaybinlog

5. Salve has a sqlthread thread that reads the Relaybinlog file and executes the write operation in the Salve database to achieve the purpose of data synchronization

6. Optionally, the Savve will also manipulate the Binlog file written to salve, which is used for Salve salve use, and of course it can be turned off

Add:

1. There are 3 ways to record changes in Binlog:

   Statement-based statements are more efficient when the statement is logged to the Binlog when the database executes a DML (INSERT/UPDATE/DELETE) statement

   A row is a more accurate way of recording a row of data into a binlog when a row of data changes

   Blending is a mix of both, giving precedence to statements-based, not guaranteeing accurate replication using row-based

   Pros and cons analysis can be referenced binlog analysis

2. Follow-up procedures in detailed procedure 3

   Then, if the main library is not changed, the Dmup thread goes to sleep until the main library changes Binlog sends the broadcast and wakes up the thread, so there are several Salve link master, then master starts a few dump threads, and when the thread is responsible for the salve of the reception that is disconnected, Theoretically, the thread will exit

3. Also need to understand the binlog knowledge

   Binlog Position: Resolve where to start the synchronization, this property represents the synchronization location, it is not possible to synchronize every time, it is definitely an incremental synchronization

   You can configure which libraries need to be synchronized and which libraries do not need to be my.cnf

   Binlog won't let him expand. Record, can set expiration and size

      #my.cnf中有两个参数设置expire_logs_days = 7      #binlog保留时间7天max_binlog_size = 1G      #binlog大小

4. Two commands

   #查看当前正在写入的binlog文件show master  status;#查看binlog中的事件show binlog events in "mysql-bin.000007";

Common Replication Schema Analysis

A master one from: The general site is used to do hot-spare, but the programmer shook, delete the main library, from the library will be deleted in a few seconds, not reliable, recommended scheduled tasks

One main three from: one from the library is Prince (semi-synchronous), cover architecture is generally used to do read and write separation, to solve the main library read pressure

Dual master: Not recommended, only a large number of written to use (135 write 1 library, 246 write 2 library)

Cascade synchronization: Reduce the pressure of master, at the same time to achieve master-slave synchronization purposes, the drawback is that the copy master of the salve hanging, the structure is collapsed, that there is a single point of problem

Ring Multi-Master: Very not recommended, unless it is really unable to solve the write concurrency

A master more from the building

Configuration Master-slave replication is very simple, I here Linux environment is centos6.6,mysql5.6, follow the steps below, the agent node regardless of him, when using Atlas to read and write separation will be configured to use

Environmental arrangements

First of all to ensure that the network environment can ping each other, the corresponding firewall port (3306) Open

Installing the MySQL Database

First of all to install the MySQL database on two servers, I here in 192.168.16.10 (master), and 192,168.16.20 (from) installed on the mysql5.6 database, installation steps can refer to my previous about the basic environment set up the blog Web Common Environment Building

Configure the main library

Edit the database configuration file to be set as the main library, add the following content, the corresponding configuration meaning has comments

vi /etc/my.cnf

#mysql主从复制-master配置#需要同步的二进制数据库名；binlog-do-db=syndb#不同步的二进制数据库名,如果不设置可以将其注释掉;binlog-ignore-db=information_schemabinlog-ignore-db=mysqlbinlog-ignore-db=personalsitebinlog-ignore-db=test#以下参数可选----------#binlog 格式binlog-format=ROWlog-bin=mysql-master-bin#slave更新时是否记录到日志中；log-slave-updates=true

From the principle point of view, from the library is necessary to connect to the main library, so the main library to assign a database for access from the library account and password, log in to MySQL use the following command is to create an account

#创建一个用户名为slave1密码为123456的账户，授予replication(复制)权限，且只允许该账户在ip地址192.168.16.20上使用grant replication slave,super,reload on *.* to [email protected] identified by ‘123456‘;

Configure from Library

Edit the database configuration file to be set to from the library, add the following, the corresponding configuration meaning has comments

vi /etc/my.cnf

#mysql主从复制-slave配置server-id = 2 #原有的配置文件为1，修改为2#需要同步的二进制数据库名；replicate-do-db=syndb#忽略同步的数据库名称replicate-ignore-db=information_schemareplicate-ignore-db=mysqlreplicate-ignore-db=personalsitereplicate-ignore-db=test#日志名log-bin=mysql-slave-bin

When you configure the main library, the main library provides an account for the link from the library, which is configured to use the Account Link Master library from the library.

change master to master_host=‘192.168.16.10‘, master_user=‘slave1‘, master_password=‘123456‘;

Turn on master and slave copying and attention points

1. Starting from the library does not need to actively create the library to synchronize the main library, otherwise from the library sqlrunning can not start, after the master and slave open from the library will automatically create the library to synchronize

2. Restart the main library and from the library (there are other ways to restart the most direct and simple)

   #前面文件目录可能略有不同，找到自己的，重启/etc/rc.d/init.d/mysqld restart

3. Open from library, view status from library (this step completes, master and slave replication configuration complete)

   #启动slavemysql>start slave;#查看从库状态，只有 Slave_IO_Running和Slave_SQL_Running都为Yes才可以mysql>show slave status\G;

4. Master-slave Copy common commands

   #一些主库命令#查看当前正在写入的binlog文件mysql> show master status#在主库上查看已连接的slave主机mysql> show slave hosts;#查看所有binlog日志mysql> show binary logs;#查看所有binlog 事件mysql> show binlog events in ‘mysql-bin.000003‘ from 145 \G;#一些从库命令#跳过指定数量错误SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;#查看 relaylog 事件show relaylog events in ‘localhost-relay-bin.000019‘

A master more from the question

Intermediate data interruption, data synchronization problem, or delay is not synchronized (after writing immediately to check)

解决方案一: Semi-synchronous (half-sync is described in detail below)

                                 这里异步异步:   插入->master binlog（数据库）--->salve  relayBinlog -->binlog（数据库）                                 这里同步               这里异步半同步  插入->master binlog（数据库）--->salve  relayBinlog -->binlog（数据库）

This scheme means at least one salve receiving the data (guaranteed to reach Relaybinlog) before returning,
In fact, this way is very time-consuming, so a master multi-slave, if the use of semi-synchronous way to ensure consistent data, only one from the library will do semi-synchronous, semi-synchronous salve can be switched to master

Cons: Salve hang up, master will always wait, you can set the timeout time between 1-10s, after the time-out period becomes asynchronous replication, see the Business configuration time, configuration to install a plug-in and 5.1 before support

解决方案二: Parallel Replication (mysql5.7)
I have not used and configured the program, only a simple understanding, you can refer to the following links
http://www.ttlsa.com/mysql/mysql-5-7-enhanced-multi-thread-salve/

解决方案2.5: Agent Read Main Library
The reason is 2.5 is this is not to solve the replication delay problem, but the upward layer of consideration, why not tolerate delay? In general, because you want to read immediately after inserting, there is a delay from reading from the library to master the newly inserted data, you can force read the main library, for example, 360atlas query SQL statement Plus/master/Prefix

The principle of semi-synchronous semi-synchronous

When the dump thread of master sends Binlog to the IO thread of slave, it blocks the result commit of master until slave will change to write Relaybinlog (5.7 can be configured to block transaction commits)
The detailed procedure is as follows:

1. When the slave host is connected to master, it is able to see whether it is in a semi-synchronous replication mechanism.

2. There should be at least one slave to turn on the function of semi-synchronous replication when Master is turned on. At this point, a thread commits a transaction on master that will be blocked until it learns that a slave that has turned on the semi-synchronous copy has received all events for this transaction, or waits for a timeout.

3. When a transaction event has been written to its relay-log and has been flushed to disk, slave will not be informed of the receipt.

4. If you wait for a timeout, that is, master is not told that it was received, Master automatically transitions to the mechanism of asynchronous replication. When at least one half-synchronized slave catches up, master and its slave automatically convert to a semi-synchronous replication mechanism.

5. Semi-synchronous replication will only work if the function of the master,slave is turned on; otherwise, only one side is turned on, and it is still replicated asynchronously.

Semi-synchronous configuration

Semi-synchronous needs to meet a few conditions
To use semi-synchronous replication, the following conditions must be met:

1. MySQL version 5.5 and above

2. Variable have_dynamic_loading is yes

3. Asynchronous replication already exists

Follow the steps below to confirm the satisfaction (no need to restart MySQL in the end)

Main Library Configuration

1. Installing plugins

   #安装插件mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME‘semisync_master.so‘;#查看是否安装成功mysql> select * from mysql.plugin;

2. Setting up the environment

   #开启半同步复制SET GLOBAL rpl_semi_sync_master_enabled = 1;#主节点等待备节点返回确认信息的超时时间单位毫秒，超过这个时间后半同步复制变为异步复制SET GLOBAL rpl_semi_sync_master_timeout = 5000;#查看是否启动(有一个on即可)show status like ‘%semi_sync%‘;

3. Configuration file
   Edit MySQL Configuration Ask Price

    vi /etc/my.cnf

   Append the following to the file:

   #半同步复制-master配置#开启半同步复制rpl_semi_sync_master_enabled=1#主节点等待备节点返回确认信息的超时时间单位毫秒，超过这个时间后半同步复制转变成异步复制rpl_semi_sync_master_timeout=5000

Configure from Library

1. Installing plugins

   #安装插件mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME‘semisync_slave.so‘;#查看是否安装成功mysql> select * from mysql.plugin;

2. Setting up the environment

   #开启半同步复制mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;#重启slave上的IO线程，重启后slave会在master上注册为半同步复制的slave角色mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;##查看是否启动(有一个on即可)mysql>  show status like ‘%semi_sync%‘;

3. Configuration file (complete with this semi-synchronous configuration, no need to restart MySQL)
   Edit MySQL config text price

    vi /etc/my.cnf

   Append the following to the file:

   #半同步复制-slave配置#开启半同步复制rpl_semi_sync_master_enabled=1

Semi-synchronous test

After completing the above configuration in the main library synchronization of the database to delete a data, you can see the execution time is very short, only 0.009s, after the execution of the data from the library has been deleted, indicating that the master-slave replication mode is not a problem

Closing from the library, executing from the library, stop slave; and then deleting a single piece of data in the main library, will find that the execution time is 5.002s, which is the main library waiting for the response from the library to write Relay-log, waiting for 5s (we configured), to asynchronous replication execution

This experiment basically proves that the semi-synchronous opening is successful

Semi-synchronous problem

After the client transaction is committed at the storage engine level, the main library is down during the process of getting confirmation from the library, and there are two possible scenarios

1. The transaction has not been sent to the library

At this point, the client will receive a transaction submission failure information, the client will resubmit the transaction to the new master, when the main library of the outage to rejoin the master-slave structure from the identity of the library, it will be found that the transaction was submitted from the Library two times, one time before the primary, once was synchronized by the new host.

2. The transaction has been sent from the library

At this point, the transaction has been received and applied from the library, but the client will still receive information about the transaction submission failure and resubmit the transaction to the new Lord.

解决方案: Upgrade to mysql5.7
mysql5.7 provides a new semi-synchronous mechanism that blocks until the main library commits a transaction to the storage engine layer until the response information is returned from the library or timed out

补充: mysql5.7 Optimization of replication

1. As above problem, mysql5.7 provides after_sync mode to solve the problem of data inconsistency between master and master crash caused by After_commit in half synchronization

2. Supports asynchronous sending of Binlog and accepting ACK
   5.6 version of the semi-synchronous replication, dump thread undertook two different and very frequent tasks: send Binlog to Slave, but also need to wait for slave feedback information, and these two tasks are serial, dump thread must wait for slave The next events transaction is not delivered until it is returned.
   In the 5.7 version of the semi-synchronous replication, an ACK collector thread is isolated, specifically for receiving feedback from slave. This allows two threads on master to work independently, sending Binlog to slave at the same time, and receiving feedback from slave.

3. Binlog Mutual Exclusion Lock improvement
   The old version of the semi-synchronous copy in the main commit Binlog write session and dump thread read Binlog operation will add a mutex to binlog, resulting in the Binlog file read and write is serialized, there is a problem of concurrency.
   MySQL 5.7 has been optimized for Binlog lock in two ways: 1. Remove the dump thread's mutex to Binlog 2. Added security margin Guarantee Binlog Read security

Reference links

MySQL traditional copy and Gtid copy principle and operation explanation
MySQL Semi-synchronous replication
MySQL5.6 configuration and experiment of semi-synchronous replication
MySQL 5.7 Deep parsing: Semi-synchronous replication technology

Replication of MySQL