What is MySql master-slave replication? How to configure implementation?

The content of this article is to introduce what is MySql master-slave replication? How to configure implementation? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. What is MySQL master-slave replication

MySQL master-slave replication is one of its most important functions. Master-slave replication means that one server acts as the master database server, and another or multiple servers act as slave database servers. The data in the master server is automatically copied to the slave servers. For multi-level replication, the database server can act as either a master or a slave. The basis of MySQL master-slave replication is that the master server records binary logs of database modifications, and the slave server automatically performs updates through the binary logs of the master server.

2. Types of Mysq master-slave replication

1. Statement-based replication:

The statements executed on the master server are executed again on the slave server, and in MySQL- Supported after version 3.23.

Existing problems: The time may not be completely synchronized, causing deviations, and the user executing the statement may also be a different user.

2. Row-based replication:

Copy the adapted content on the main server directly, without caring about which statement caused the content change. In MySQL-5.0 Will be introduced in a later version.

Existing problems: For example, there are 10,000 users in a salary table. If we set the salary of each user as 1,000, then row-based replication will copy the content of 10,000 rows. The resulting overhead is relatively Large, statement-based replication only requires one statement.

3. Mixed types of replication:

MySQL uses statement-based replication by default. When statement-based replication causes problems, row-based replication will be used, and MySQL will automatically make the selection. .

In the MySQL master-slave replication architecture, read operations can be performed on all servers, while write operations can only be performed on the master server. Although the master-slave replication architecture provides expansion for read operations, if there are more write operations (multiple slave servers have to synchronize data from the master server), the master server will inevitably become a performance bottleneck in the replication of the single-master model.

3. The working principle of Mysql master-slave replication

1、Statement-based replication: The statements executed on the master server are on the slave server Execute it again. It is supported in MySQL-3.23 and later versions.

Existing problems: The time may not be completely synchronized, causing deviations, and the user executing the statement may also be a different user.

2, row-based replication: directly copy the adapted content on the main server, without caring about which statement caused the change in the content , introduced after MySQL-5.0 version.

Existing problems: For example, there are 10,000 users in a salary table. If we set the salary of each user as 1,000, then row-based replication will copy the content of 10,000 rows. The resulting overhead is relatively Large, statement-based replication only requires one statement.

3, Mixed type replication: MySQL uses statement-based replication by default. Row-based replication will be used when statement-based replication causes problems. , MySQL will automatically select it.

In the MySQL master-slave replication architecture, read operations can be performed on all servers, while write operations can only be performed on the master server. Although the master-slave replication architecture provides expansion for read operations, if there are more write operations (multiple slave servers have to synchronize data from the master server), the master server will inevitably become a performance bottleneck in the replication of the single-master model.

Three MySQL master-slave replication working principles

As shown in the figure below:

Any modifications on the main server will be saved in the binary log In the Binary log, an I/O thread is started from the server (actually a client process of the main server), connected to the main server to request to read the binary log, and then writes the read binary log to a local Really log inside. Start a SQL thread from the server to check the Realy log regularly. If any changes are found, immediately execute the changed content on the local machine.

If there is one master and multiple slaves, then the master library will be responsible for both writing and providing binary logs for several slave libraries. At this time, you can make a slight adjustment and give the binary log to only a certain slave. This slave will then enable the binary log and send its own binary log to other slaves. Or simply this one never records and is only responsible for forwarding binary logs to other slaves. In this way, the performance of the architecture may be much better, and the delay between data should also be slightly better. The working principle diagram is as follows:

In fact, in the old version of MySQL master-slave replication, the Slave side is not completed by two processes, but by one process. However, it was later discovered that there were greater risks and performance problems in doing so, mainly as follows:

First of all, a process will make the process of copying bin-log logs and parsing logs and executing them on its own a serial process. Performance is subject to certain limitations, and the delay of asynchronous replication will also be relatively long.

In addition, after the Slave side obtains the bin-log from the Master side, it needs to parse the log content and then execute it on its own. During this process, a large number of changes may have occurred on the Master side and a large number of new logs may have been added. If there is an irreparable error in the storage on the Master side at this stage, all changes made at this stage will never be retrieved. If the pressure on the Slave is relatively high, this process may take longer.

In order to improve the performance of replication and solve existing risks, later versions of MySQL will transfer the replication action on the Slave side to two processes. The person who proposed this improvement plan is "Jeremy Zawodny", an engineer at Yahoo! This not only solves the performance problem, but also shortens the asynchronous delay time, and also reduces the amount of possible data loss.

Of course, even after switching to the current two thread processing, there is still the possibility of slave data delay and data loss. After all, this replication is asynchronous. These problems will exist as long as the data changes are not in one transaction. If you want to completely avoid these problems, you can only use MySQL cluster to solve them. However, MySQL's cluster is a solution for in-memory databases. All data needs to be loaded into memory, which requires very large memory, and is not very practical for general applications.

Another thing to mention is MySQL's Replication Filters. Replication filters allow you to copy only a part of the data in the server. There are two types of replication filtering: filtering events in the binary log on the Master; filtering events in the relay log on the Slave. As follows:

Configure Master’s my.cnf file (key configuration)/etc/my.cnf

log-bin=mysql-bin

server-id   = 1

binlog-do-db=icinga

binlog-do-db=DB2     //如果备份多个数据库，重复设置这个选项即可

binlog-do-db=DB3   //需要同步的数据库，如果没有本行，即表示同步所有的数据库

binlog-ignore-db=mysql  //被忽略的数据库

配置Slave的my.cnf文件(关键性的配置)/etc/my.cnf

log-bin=mysql-bin

server-id=2

master-host=10.1.68.110

master-user=backup

master-password=1234qwer

master-port=3306

replicate-do-db=icinga

replicate-do-db=DB2

replicate-do-db=DB3   //需要同步的数据库，如果没有本行，即表示同步所有的数据库

replicate-ignore-db=mysql   //被忽略的数据库

Netizens said replicate-do-db There may be some problems during use (http://blog.knowsky.com/19696...), I have not tested it myself. I guess the binlog-do-db parameter is used in the main server to filter out the databases that are not allowed to be copied in the configuration file by filtering the Binary Log, that is, not writing operation logs that do not allow data to be copied to the Binary Log; and replicate-do -db is used from the server to filter out databases or tables that are not allowed to be copied by filtering the Relay Log, that is, when executing the actions in the Relay Log, those unauthorized modification actions will not be performed. In this case, in the case of multiple slave database servers: some slave servers not only copy data from the master server, but also act as the master server to copy data to other slave servers, then binlog-do- should be able to exist in its configuration file at the same time. The two parameters db and replicate-do-db are correct. Everything is my own prediction. The specific usage of binlog-do-db and replicate-do-db still needs to be explored a little bit in actual development.

It is said on the Internet that the operation of ignoring certain databases or tables during replication is best not to be performed on the master server, because after the master server ignores it, it will no longer write to the binary file, but on the slave server Although some databases are ignored, the operation information on the master server will still be copied to the relay log on the slave server, but it will not be executed on the slave server. I think this means that it is recommended to set up replicate-do-db on the slave server instead of binlog-do-db on the master server.

In addition, whether it is a blacklist (binlog-ignore-db, replicate-ignore-db) or a whitelist (binlog-do-db, replicate-do-db), just write one. If you use them at the same time Then only the whitelist takes effect.

4. The process of MySQL master-slave replication

There are two situations of MySQL master-slave replication: synchronous replication and asynchronous replication. Most of the actual replication architecture is asynchronous replication.

The basic process of replication is as follows:

1. The IO process on the Slave connects to the Master and requests the specified log file from the specified location (or from the beginning) log).

2. After the Master receives the request from the Slave's IO process, the IO process responsible for replication will read the log information after the specified position of the log according to the request information and return it to the Slave's IO process. In addition to the information contained in the log, the returned information also includes the name of the bin-log file and the location of the bin-log in which the returned information has been sent to the Master.

3. After the Slave's IO process receives the information, it will add the received log content to the end of the relay-log file on the Slave side, and will read the bin-log on the Master side. The file name and location of the log are recorded in the master-info file so that the next time it is read, the Master can be clearly told "from which location in a certain bin-log do I need to start the next log content? Please send it to me."

4. After the Slave Sql process detects the newly added content in the relay-log, it will immediately parse the content of the relay-log and become the executable content when it is actually executed on the Master side. and execute on itself.

五、Mysql主从复制的具体配置

复制通常用来创建主节点的副本，通过添加冗余节点来保证高可用性，当然复制也可以用于其他用途，例如在从节点上进行数据读、分析等等。在横向扩展的业务中，复制很容易实施，主要表现在在利用主节点进行写操作，多个从节点进行读操作，MySQL复制的异步性是指：事物首先在主节点上提交，然后复制给从节点并在从节点上应用，这样意味着在同一个时间点主从上的数据可能不一致。异步复制的好处在于它比同步复制要快，如果对数据的一致性要求很高，还是采用同步复制较好。

最简单的复制模式就是一主一从的复制模式了，这样一个简单的架构只需要三个步骤即可完成：

（1）建立一个主节点，开启binlog，设置服务器id；

（2）建立一个从节点，设置服务器id；

（3）将从节点连接到主节点上。

下面我们开始操作，以MySQL 5.5为例，操作系统Ubuntu12.10，Master 10.1.6.159 Slave 10.1.6.191。

apt-get install mysql-server

Master机器

Master上面开启binlog日志，并且设置一个唯一的服务器id，在局域网内这个id必须唯一。二进制的binlog日志记录master上的所有数据库改变，这个日志会被复制到从节点上，并且在从节点上回放。修改my.cnf文件，在mysqld模块下修改如下内容：

[mysqld]
server-id   = 1
log_bin     = /var/log/mysql/mysql-bin.log

log_bin设置二进制日志所产生文件的基本名称，二进制日志由一系列文件组成，log_bin的值是可选项，如果没有为log_bin设置值，则默认值是：主机名-bin。如果随便修改主机名，则binlog日志的名称也会被改变的。server-id是用来唯一标识一个服务器的，每个服务器的server-id都不一样。这样slave连接到master后，会请求master将所有的binlog传递给它，然后将这些binlog在slave上回放。为了防止权限混乱，一般都是建立一个单独用于复制的账户。

binlog是复制过程的关键，它记录了数据库的所有改变，通常即将执行完毕的语句会在binlog日志的末尾写入一条记录，binlog只记录改变数据库的语句，对于不改变数据库的语句则不进行记录。这种情况叫做基于语句的复制，前面提到过还有一种情况是基于行的复制，两种模式各有各的优缺点。

Slave机器

slave机器和master一样，需要一个唯一的server-id。

[mysqld]
server-id = 2

连接Slave到Master

在Master和Slave都配置好后，只需要把slave只想master即可

change master to master_host='10.1.6.159',master_port=3306,master_user='rep',
master_password='123456';
start slave;

接下来在master上做一些针对改变数据库的操作，来观察slave的变化情况。在修改完my.cnf配置重启数据库后，就开始记录binlog了。可以在/var/log/mysql目录下看到一个mysql-bin.000001文件，而且还有一个mysql-bin.index文件，这个mysql-bin.index文件是什么？这个文件保存了所有的binlog文件列表，但是我们在配置文件中并没有设置改值，这个可以通过log_bin_index进行设置，如果没有设置改值，则默认值和log_bin一样。在master上执行show binlog events命令，可以看到第一个binlog文件的内容。

注意：上面的sql语句是从头开始复制第一个binlog，如果想从某个位置开始复制binlog，就需要在change master to时指定要开始的binlog文件名和语句在文件中的起点位置，参数如下：master_log_file和master_log_pos。

mysql> show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.28-0ubuntu0.12.10.2-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 107
 Event_type: Query
  Server_id: 1
End_log_pos: 181
       Info: create user rep
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 181
 Event_type: Query
  Server_id: 1
End_log_pos: 316
       Info: grant replication slave on *.* to rep identified by '123456'
3 rows in set (0.00 sec)

• Log_name 是二进制日志文件的名称，一个事件不能横跨两个文件

• Pos 这是该事件在文件中的开始位置

• Event_type 事件的类型，事件类型是给slave传递信息的基本方法，每个新的binlog都已Format_desc类型开始，以Rotate类型结束

• Server_id 创建该事件的服务器id

• End_log_pos 该事件的结束位置，也是下一个事件的开始位置，因此事件范围为Pos~End_log_pos-1

• Info 事件信息的可读文本，不同的事件有不同的信息

示例

在master的test库中创建一个rep表，并插入一条记录。

create table rep(name var);
insert into rep values ("guol");
flush logs;

flush logs命令强制轮转日志，生成一个新的二进制日志，可以通过show binlog events in 'xxx'来查看该二进制日志。可以通过show master status查看当前正在写入的binlog文件。这样就会在slave上执行相应的改变操作。

上面就是最简单的主从复制模式，不过有时候随着时间的推进，binlog会变得非常庞大，如果新增加一台slave，从头开始复制master的binlog文件是非常耗时的，所以我们可以从一个指定的位置开始复制binlog日志，可以通过其他方法把以前的binlog文件进行快速复制，例如copy物理文件。在change master to中有两个参数可以实现该功能，master_log_file和master_log_pos，通过这两个参数指定binlog文件及其位置。我们可以从master上复制也可以从slave上复制，假如我们是从master上复制，具体操作过程如下：

（1）为了防止在操作过程中数据更新，导致数据不一致，所以需要先刷新数据并锁定数据库：flush tables with read lock。

（2）检查当前的binlog文件及其位置：show master status。

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

（3）通过mysqldump命令创建数据库的逻辑备分：mysqldump --all-databases -hlocalhost -p >back.sql。

（4）有了master的逻辑备份后，对数据库进行解锁：unlock tables。

（5）把back.sql复制到新的slave上，执行：mysql -hlocalhost -p 把master的逻辑备份插入slave的数据库中。

（6）现在可以把新的slave连接到master上了，只需要在change master to中多设置两个参数master_log_file='mysql-bin.000003'和master_log_pos='107'即可，然后启动slave：start slave，这样slave就可以接着107的位置进行复制了。

change master to master_host='10.1.6.159',master_port=3306,master_user='rep',
master_password='123456',master_log_file='mysql-bin.000003',master_log_pos='107';
start slave;

有时候master并不能让你锁住表进行复制，因为可能跑一些不间断的服务，如果这时master已经有了一个slave，我们则可以通过这个slave进行再次扩展一个新的slave。原理同在master上进行复制差不多，关键在于找到binlog的位置，你在复制的同时可能该slave也在和master进行同步，操作如下：

（1）为了防止数据变动，还是需要停止slave的同步：stop slave。

（2）然后刷新表，并用mysqldump逻辑备份数据库。

（3）使用show slave status查看slave的相关信息，记录下两个字段的值Relay_Master_Log_File和Exec_Master_Log_Pos，这个用来确定从后面哪里开始复制。

（4）对slave解锁，把备份的逻辑数据库导入新的slave的数据库中，然后设置change master to，这一步和复制master一样。

六、深入了解Mysql主从配置

1、一主多从

由一个master和一个slave组成复制系统是最简单的情况。Slave之间并不相互通信，只能与master进行通信。在实际应用场景中，MySQL复制90%以上都是一个Master复制到一个或者多个Slave的架构模式，主要用于读压力比较大的应用的数据库端廉价扩展解决方案。

在上图中，是我们开始时提到的一主多从的情况，这时主库既要负责写又要负责为几个从库提供二进制日志。这种情况将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从，或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。PS：这些前面都写过了，又复制了一遍。

2、主主复制

上图中，Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。在这种复制架构中，各自上运行的不是同一db，比如左边的是db1,右边的是db2，db1的从在右边反之db2的从在左边，两者互为主从，再辅助一些监控的服务还可以实现一定程度上的高可以用。

3、主动—被动模式的Master-Master(Master-Master in Active-Passive Mode)

上图中，这是由master-master结构变化而来的，它避免了M-M的缺点，实际上，这是一种具有容错和高可用性的系统。它的不同点在于其中只有一个节点在提供读写服务，另外一个节点时刻准备着，当主节点一旦故障马上接替服务。比如通过corosync+pacemaker+drbd+MySQL就可以提供这样一组高可用服务，主备模式下再跟着slave服务器，也可以实现读写分离。

4、带从服务器的Master-Master结构(Master-Master with Slaves)

The advantage of this structure is that it provides redundancy. With a geographically distributed replication structure, there is no single node failure problem, and read-intensive requests can also be placed on the slave.

5. MySQL-5.5 supports semi-synchronous replication

Earlier MySQL replication could only be implemented based on asynchronous implementation. Starting from MySQL-5.5, semi-automatic replication is supported. In the previous asynchronous replication, the main database did not control the progress of the standby database after executing some transactions. If the standby database is lagging behind, and unfortunately the main database crashes (for example, downtime), the data in the standby database will be incomplete. In short, when the main database fails, we cannot use the standby database to continue to provide data consistent services. Semisynchronous Replication (semi-synchronous replication) guarantees to a certain extent that the submitted transaction has been transmitted to at least one standby database. In semi synchronous, it only ensures that the transaction has been delivered to the standby database, but it does not ensure that it has been completed on the standby database.

In addition, there is another situation that may cause the primary and secondary data to be inconsistent. In a session, after a transaction is submitted on the main database, it will wait for the transaction to be transferred to at least one standby database. If the main database crashes during this waiting process, the standby database and the main database may be inconsistent, which is very fatal. If the active and standby networks fail or the standby database is down, the primary database will wait for 10 seconds (the default value of rpl_semi_sync_master_timeout) after the transaction is submitted before continuing. At this time, the main library will change back to its original asynchronous state.

After MySQL loads and enables the Semi-sync plug-in, each transaction must wait for the standby database to receive the log before returning it to the client. If you are doing a small transaction and the delay between the two hosts is small, Semi-sync can achieve zero data loss with little performance loss.

The above is the entire content of this article, I hope it will be helpful to everyone's study. For more exciting content, you can pay attention to the relevant tutorial columns of the PHP Chinese website! ! !

The above is the detailed content of What is MySql master-slave replication? How to configure implementation?. For more information, please follow other related articles on the PHP Chinese website!