What is mysql master-slave synchronization?-Mysql Tutorial-php.cn

mysql master-slave synchronization means backup. The master library (Master) synchronizes the writes in its own library to its slave library (Slave). When some unpredictable situations occur in the master library, the entire When the server is unavailable, since there is also a copy of the data in the slave database, the data can be quickly restored without causing or reducing data loss.

What is mysql master-slave synchronization?

When the data in the master (main) library changes, the changes will be synchronized to the slave (slave) library in real time.

Data is a vital part of an application. Starting from the purpose, master-slave synchronization has some meaning of backup. The master library (Master) synchronizes the writes in its own library to its slave library (Slave) at the same time. When something unpredictable happens in the master library When the entire server becomes unusable due to a situation, since there is also a copy of data in the slave database, the data can be quickly restored without causing or reducing data loss.

Of course, this is only the first level. If the role of the master-slave library is limited to this, then I personally think there is no need to divide it into two databases. It only needs to regularly send the database contents as snapshots to Wouldn't it be nice to use another server, or send the written content to another server in real time every time it is written? This will not only save resources, but also serve the purpose of disaster recovery and backup.

Of course, the role of master-slave synchronization cannot be limited to this. Once we configure the master-slave structure, we usually will not let the slave node just serve as a backup database. We should also configure it accordingly. Read and write separation (You can use MyCat or other middleware, you can learn about it yourself. I will talk about this in the next blog about MyCat. The length may be a bit long, so I will write another article).

In the actual environment, the number of read operations on the database is much greater than the number of write operations on the database, so we can let the Master only provide the write function, and then move all read operations to the slave database. This is We usually talk about the separation of reading and writing. This can not only reduce the pressure on the Master, but also provide disaster recovery backup, killing two birds with one stone.

What are the benefits of master-slave synchronization?

Horizontally expand the load capacity of the database.
Fault tolerance, high availability. Failover/High Availability
Data backup.

The principle of MySQL master-slave synchronization

After talking about the concept of master-slave synchronization, let’s talk about the principle of master-slave synchronization. In fact, the principle is also very simple. There is no Redis There are so many concepts in clusters.

In fact, after we configure the master-slave in MySQL, as long as we perform a write operation on the Master node, this operation will be saved to the binary-log (bin-log) log of MySQL. When the slave When connecting to the master, the master machine will open the binlog dump thread for the slave. When the master's binlog changes, the master's dump thread will notify the slave and send the corresponding binlog content to the slave. When the master-slave synchronization is turned on, the Slave node will create two threads, an I/O thread and a SQL thread. This can be seen with our own eyes in the subsequent construction.

I/0 thread: This thread is linked to the master machine. When the binlog of the master machine is sent to the slave, the IO thread will write the log content locally. in the relay log.
SQL thread: This thread reads the contents of the relay log and performs corresponding operations on the Slave database based on the contents of the relay log.
Possible problems: When there are a lot of write requests, the Slave data and the Master data may be inconsistent. This is because of the log transfer process. This is caused by a short delay in the system, a large number of write commands, or a mismatch in system speed.

This is roughly the principle of MySQL master-slave synchronization. What really plays a role in it are actually these two log files, binlog and relay log.

Manually build MySQL master-slave synchronization

Environment preparation

The environment for setting up master-slave synchronization this time: CentOS 7, MySQL 8.0. 18 (installed using binary package).

Scenario Introduction

This time, MySQL master-slave synchronization will be built, with one Master and two Slaves.

Master:IP :192.168.43.201 Port:3306
Slave1:IP:192.168.43.202 Port:3306
Slave2:IP:192.168.43.203 Port:3306

Copy after login

Start building

Modify the configuration file

After we install MySQL, there will be a my.cnf file in the /etc/ directory. Open the file and add the following Content (don’t forget to make a backup before modifying):

#该配置为Master的配置
server-id=201 #Server id 每台MySQL的必须不同
log-bin=/var/lib/mysql/mysql-bin.log #代表开启binlog日志
expire_logs_days=10 #日志过期时间
max_binlog_size=200M #日志最大容量
binlog_ignore_db=mysql #忽略mysql库，表示不同步此库

Copy after login

#该配置为Slave的配置,第二台Slave也是这么配置，不过要修改一下server-id
server-id=202
expire_logs_days=10 #日志的缓存时间
max_binlog_size=200M #日志的最大大小
replicate_ignore_db=mysql #忽略同步的数据库

Copy after login

New Slave user

Open the client of the Master node, mysql -u root -p password

Create user create user 'Slave'@'%' identified by '123456';

Empower the newly created user: grant replication slave on '*.*' to 'Slave'@'%';

Check Master node status

After the above operations are no problem, we Enter show master status in the terminal to view the master's binlog log.

配置两个Slave节点

打开两个Slave节点客户端，在我们的另外两个Slave节点中输入如下命令：

change master to master_user=&#39;Slave&#39;,master_password=&#39;123456&#39;,master_host=&#39;192.168.43.201&#39;,master_log_file=&#39;mysql-bin.000005&#39;,master_log_pos=155,get_master_public_key=1;
#注意，这里的master_log_file，就是binlog的文件名，输入上图中的mysql-bin.000005,每个人的都可能不一样。
#注意，这里的master_log_pos是binlog偏移量，输入上图中的155，每个人的都可能不一样。

Copy after login

配置完成后，输入start slave;开启从节点，然后输入show slave status\G;查看从节点状态

可以看到，在两台Slave的状态中，我们能亲眼看到IO线程和SQL线程的运行状态，这两个线程必须都是yes，才算配置搭建完成。

搭建完成

通过上述步骤，就完成了MySQL主从同步的搭建，相对Redis而言MySQL配置相当简单。下面我们可以进行测试。

先看看三个MySQL的数据库状态：SHOW DATABASES;

可以看到现在数据库都是初始默认状态，没有任何额外的库。

在Master节点中创建一个数据库，库名可以自己设置。

CREATE DATABASE testcluster;

<img alt="" class="has" src="https://img.php.cn/upload/article/000/000/024/fefa55750f9a4ddaec29168c6cc022e9-4.png">

可以看到，在Slave中也出现了Master中创建的数据库，说明我们的配置没有问题，主从搭建成功。这里就不再创建表了，大家可以自己试试，创建表再往表中插入数据，也是没有任何问题的。

注意事项

如果出现IO线程一直在Connecting状态，可以看看是不是三台机器无法相互连接，如果可以相互连接，那么有可能是Slave账号密码写错了，重新关闭Slave然后输入上面的配置命令再打开Slave即可。

如果出现SQL线程为NO状态，那么有可能是从数据库和主数据库的数据不一致造成的，或者事务回滚，如果是后者，先关闭Slave，然后先查看master的binlog和position，然后输入配置命令，再输入set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;，再重新start slave;即可，如通过是前者，那么就排查一下是不是存在哪张表没有被同步，是否存在主库存在而从库不存在的表，自己同步一下再重新配置一遍即可。

结语

在写这篇文章之前自己也被一些计算机领域的“名词”吓到过，相信有不少同学都有一样的体会，碰上某些高大上的名词总是先被吓到，例如像“分布式”、“集群”等等等等，甚至在没接触过nginx之前，连”负载均衡“、”反向代理“这样的词都让人觉得，这么高达上的词，肯定很难吧，但其实自己了解了nginx、ribbon等之后才发现，其实也就那么回事吧，没有想象中的那么难。

所以写这篇文章的初衷是想让大家对集群化或者分布式或者其他的一些技术或者解决方案不要有一种望而却步的感觉（感觉计算机领域的词都有这么一种特点，词汇高大上，但是其实思想是比较好理解的），其实自己手动配置出一个简单的集群并没有那么难。

如果学会docker之后再来配置就更加简单了，但是更希望不要只局限于会配置，配置出来的东西只能说你会配置了，但是在这层配置底下是前人做了相当多的工作，才能使我们通过简单配置就能实现一些功能，应该要深入底层，了解配置下面的工作原理，这个才是最重要的，也是体现一个程序员水平的地方。

推荐教程：mysql视频教程

The above is the detailed content of What is mysql master-slave synchronization?. For more information, please follow other related articles on the PHP Chinese website!