当前位置：首页 > 教育百科 > 学历/技能 > 正文

MySQL主从、主主实战

：2025年01月22日

linux运维之路

本文探讨了MySQL主从复制的工作原理，包括Master和Slave之间的IO线程和SQL线程。主从同步是异步过程，Master通过IO线程请求 slave，slave通过IO线程接收 master 的bin-log日志，最后mast...

本文用docker的方式做MySQL主从试验，方便理解原理，生产环境不建议使用docker部署数据库。

MySQL主从复制的用途：确保数据安全，做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。

MySQL主从复制的工作原理：MySQL主从数据同步是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，bin-log日志用于记录在Master库中执行的增、删、修改、更新操作的sql语句。整个过程需要开启3个线程，分别是Master开启IO线程，Slave开启IO线程和SQL线程，

具体主从同步原理详解如下：（也可以跳过这段废话）qSlave上执行slave start，Slave IO线程会通过在Master创建的授权用户连接上至Master，并请求master从指定的文件和位置之后发送bin-log日志内容；qMaster接收到来自slave IO线程的请求后，master IO线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的IO线程。q返回的信息中除了bin-log日志内容外，还有master最新的binlog文件名以及在binlog中的下一个指定更新position点；qSlave IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求；qSlave Sql线程检测到relay-log中内容有更新，会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句，将解析的SQL语句并在Slave里执行，执行成功后，Master库与Slave库保持数据一致。

MySQL主从复制实施注意事项：主从服务器操作系统版本和位数一致；Master 和 Slave 数据库的版本要一致；Master 和 Slave 数据库中的数据要一致；Master 开启二进制日志，Master 和 Slave 的 server_id 在局域网内必须唯一。

MySQL主从复制结构图：

MySQL主从复制实战：

由于我电脑硬件配置低，无法跑2个虚拟机，本实验用docker容器的方法：
环境搭建：docker pull mysql8.0镜像：

docker pull mysql:8.0

docker run --name master -p8888:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

分别测试登陆master和slave

mysql -u root -p123456 -P 8888 -h 127.0.0.1
mysql -u root -p123456 -P 9999 -h 127.0.0.1

复制容器里的my.cnf文件到宿主机当前位置：

docker cp master:/etc/my.cnf ./

在my.cnf文件中[mysqld]段中加入如下代码，cp回master容器原位置，重启MYSQL服务：server-id = 1 log-bin = mysql-bin

登录master数据库，创建tongbu用户及密码并设置权限，执行如下命令，查看bin-log文件及position点：

CREATE USER 'tongbu'@'%' IDENTIFIED WITH mysql_native_password BY '123456';GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%';show master status;

修改my.cnf文件，在[mysqld]段中加入如下代码，cp回slave容器原位置，重启MYSQL服务：server-id = 2

Slave指定Master IP、用户名、密码、bin-log文件名（ binlog.000003）及position（656），代码如下：这里重点注意，因为是用容器做的实验环境，要注意以下参数master_host='192.168.101.6',master_port=8888

change master to master_host='192.168.101.6',master_port=8888,master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=157;

在slave启动slave start，并执行show slave status\G查看Mysql主从状态：show slave status\G;

#后面要加\G,则看起来很乱。

#要确保以下两个地方显示是Yes，如果不是Yes，需要检查前面的步骤是否有错误的地方；

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#如果显示Slave_IO_Running：Connecting 的问题，要从以下几点查找：

#网络不通、防火墙端口未开放、mysql账户密码错误、mysql主从机配置文件写错、配置从机连接语法错误、主机未开放账户连接权限；

#例如本次实验因为没注意容器的问题，master_host='192.168.101.6',写成了127.0.0.1导致一直无法连接。

测试在master上创建一个数据库：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> create database test_db;

Query OK, 1 row affected (0.03 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test_db |

+--------------------+

5 rows in set (0.00 sec)

在slave上查看是否同步过来：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test_db |

+--------------------+

5 rows in set (0.00 sec)

mysql>

可以看到，测试增加的test_db已经自动同步过来，主从复制实验完成。

再做个试验：构建MySQL主主复制，实现在主库操作增删改，从库能够同步，在从库操作增删改，主库也能同步数据。MySQL主主复制是指两台机器（master和slave）互为主从。

搭建MySQL主主复制环境：启动两个MySQL8.0容器，name分别为node001（端口3333），node002（端口4444）：

docker run --name node001 -p3333:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

docker run --name node002 -p4444:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

利用docker cp node001/node002:/etc/my.cnf，复制出my.cnf配置文件并修改后利用docker cp my.cnf node001/node002:/etc/传回容器内部，修改如下：

在node001的[mysqld]字段上添加

server-id = 1

log_bin = mysql-bin

在node002的[mysqld]字段上添加

server-id = 2

log_bin = mysql-bin

重启两个容器（达到重启mysql服务的作用）：

docker restart node001 node002

node001

node002

在node001上添加用户并授权：

CREATE USER 'tongbu'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%';

在node002上添加用户并授权：

CREATE USER 'tongbu'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%';

在node001要查看master信息：

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000001

Position: 656

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.01 sec)

ERROR:

No query specified

mysql>

在node002上设置从节点，进行以下操作，注意master_log_file='mysql-bin.000001',master_log_pos=656;要与上一步查看到的node001信息一致：最后执行 start slave;

change master to

master_host='192.168.101.6',master_port=3333,master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=656;

start slave;

查看node002的slave状态（确保标红处的两个Yes)：

show slave status \G

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.101.6

Master_User: tongbu

Master_Port: 3333

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 656

Relay_Log_File: 914fdc4fdb40-relay-bin.000002

Relay_Log_Pos: 326

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

在node002要查看master信息：

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000001

Position: 656

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

在node001上设置从节点，进行以下操作，注意master_log_file='mysql-bin.000001',master_log_pos=656;要与上一步查看到的node001信息一致：最后执行 start slave;

change master to

master_host='192.168.101.6',master_port=4444,master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=656;

start slave;

查看node001的slave状态（确保标红处的两个Yes)：

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.101.6

Master_User: tongbu

Master_Port: 4444

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 656

Relay_Log_File: f061023a72b4-relay-bin.000002

Relay_Log_Pos: 326

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试node001增加一个test数据库：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.01 sec)

mysql> create database test;

Query OK, 1 row affected (0.03 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

5 rows in set (0.00 sec)

mysql>

查看node002上的数据库，已经与node001同步：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

5 rows in set (0.00 sec)

mysql>

在node002上删除test数据库：

mysql> drop database test;

Query OK, 0 rows affected (0.02 sec)

mysql>

在node001上查看数据库，已经与node002同步：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql>

在node002上增加一个数据库test002：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> create database test002;

Query OK, 1 row affected (0.04 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test002 |

+--------------------+

5 rows in set (0.01 sec)

mysql>

查看node001上的数据库，已经与node002同步：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test002 |

+--------------------+

5 rows in set (0.00 sec)

mysql>

原文来源：https://mp.weixin.qq.com/s/DgplLEGFfcoAWharuZA86Q

[编辑：王振袢 &发表于江苏]

阅读全文

[我要纠错]

来源：本文内容搜集或转自各大网络平台，并已注明来源、出处，如果转载侵犯您的版权或非授权发布，请联系小编，我们会及时审核处理。
声明：江苏教育黄页对文中观点保持中立，对所包含内容的准确性、可靠性或者完整性不提供任何明示或暗示的保证，不对文章观点负责，仅作分享之用，文章版权及插图属于原作者。