龙空技术网

从理论到实战,彻底搞懂MySQL主从复制原理

linux爱好者 92

前言:

眼前看官们对“mysql主从复制原理详解”可能比较着重,兄弟们都需要学习一些“mysql主从复制原理详解”的相关知识。那么小编在网络上汇集了一些有关“mysql主从复制原理详解””的相关资讯,希望看官们能喜欢,姐妹们快快来了解一下吧!

前言

MySQL 主从复制也称为 MySQL 主从同步,是构建数据库高可用集群架构的基础,我们通过为服务器配置一个或多个备库的方式来进行数据同步,复制功能不仅有利于构建高性能应用,同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。

复制的基本问题是解决不同服务器的数据保持同步,一台主库的数据可以同步到多台备库上,备库本身也可以被配置为另外一台服务器的主库,主库和备库之间可以有多种不同的组合方式。

常见主从架构模式单向主从双向主从级联主从多主一从

主从原理

在实战前,先理解主从复制的原理更为重要。主从复制过程中有三个线程,主库有一个工作线程 I/O dump thread,备库有两个工作线程,I/O thread 和 SQL thread

主库接收SQL请求记录到自己的 binlog 中备库的 I/O thread 去请求主库的 binlog主库的 I/O dump thread 给备库 I/O thread 传送 binlog备库将得到的 binlog 写到自己的 relay log 中备库的 SQL thread 读取 relay log 执行 SQL基于语句复制(STATEMENT)

在 MySQL5.0 以前只支持基于语句的复制。基于语句的复制模式下,主库会记录那些造成数据更改的操作,当备库读取并重放这些操作时,实际上只是把主库上的SQL执行一遍。好处是实现简单,简单的记录并执行这些语句,能让主备保持同步。

但实际上基于语句的复制方式有时会出问题。因为主库上的数据更新除了执行的语句外,可能还依赖于其他因素,例如,同一条 SQL 在主库和备库上的执行时间可能稍有不同,因此在传输带 binlog 中,还包括一些元数据信息,如当前的时间戳,还存在着一些无法被正确复制的 SQL,例如,CURRENT_USER() 函数的语句。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。

基于行复制(ROW)

MySQL5.1开始支持基于行复制,这种方式会将实际的数据记录在 binlog 中,跟其他数据库的实现很像。基于行复制的模式有优点,也有缺陷。好处是可以正确的复制每一行,一些语句可以被更加有效的复制。

也有一些情况,基于行复制的代价会比较大,例如:

update tb_user set age=10;

由于这条 SQL 会更新全表,使用基于行的开销会很大,因为每一行的数据都会记录到 binlog 中,这使得 binlog 文件庞大,并且会给主库增加额外的负载。

混合模式(MIXED)

以上两种模式的混合使用,一般的复制使用 STATEMENT 模式保存 binlog,对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog,MySQL 会根据执行的 SQL 语句选择日志保存方式。

因为两种模式各有优缺点以及使用的场合,所以 MySQL 支持在这两种复制模式中动态切换(MIXED模式),MySQL8.0 默认使用基于行复制的方式,理论上基于行的复制模式在整体上更优,且在实际应用中适用于大多数场景。,当然也可以使用参数 binlog_format 手动指定复制的模式。

主从实战

本文操作实战环境:MySQL 8.0.26 + centos7

一主一从

1、两台服务器分别部署 MySQL,两台服务器 ip 为:

主:192.168.96.95从:192.168.96.82

2、在 my.cnf 中设置 server_id:

主:10000从:10001

注:更改 server_id 后需重启服务

3、开启 GTID 模式

在主库 my.cnf 中配置如下参数:

gitd_mode=onenforce_gtid_consistency=onlog_bin=on复制代码

备库中要配置:

gitd_mode=onenforce_gtid_consistency=onlog_slave_updates=1复制代码

4、检查主库是否开启了 log_bin 参数(MySQL8.0默认开启)

mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------+| Variable_name                   | Value                       |+---------------------------------+-----------------------------+| log_bin                         | ON                          || log_bin_basename                | /var/lib/mysql/binlog       || log_bin_index                   | /var/lib/mysql/binlog.index || log_bin_trust_function_creators | OFF                         || log_bin_use_v1_row_events       | OFF                         || sql_log_bin                     | ON                          |+---------------------------------+-----------------------------+6 rows in set (0.01 sec)复制代码

5、将 binlog 格式设置为基于行复制的格式(MySQL8.0 默认为 ROW)

mysql> show variables like '%binlog_format%';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.00 sec)复制代码

6、创建主从复制账号

# 账号:bak;密码:123456;在 96 段可用mysql> create user 'bak'@'192.168.96.%' identified by '123456';Query OK, 0 rows affected (0.04 sec)mysql> grant replication slave on *.* to 'bak'@'192.168.96.%';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)复制代码

7、主库上查询状态

mysql> show master status;+---------------+----------+--------------+------------------+-------------------------------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------------------------------+| binlog.000003 |      908 |              |                  | 62cd056a-e9f1-11eb-9218-0242ac110002:1-16 |+---------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.00 sec)复制代码

8、在备库上配置主从

mysql>change master to MASTER_HOST='192.168.96.95',MASTER_USER='bak',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=908;Query OK, 0 rows affected, 8 warnings (0.03 sec)复制代码

其中,参数如下:

MASTER_HOST:指定主库IPMASTER_USER:之前创建的复制用户MASTER_PASSWORD:用户密码MASTER_LOG_FILE:从备份文件中获取的当前 binlogMASTER_LOG_POS:从备份文件中获取的 position 号

其中,MASTER_LOG_FILE 和 MASTER_LOG_POS 参数可以指定为当前 主库中的 binlog 文件的 pos,可以先做主库的全量备份,再从主库中指定的 binlog 的 pos 开始同步。

9、备库上开启主从同步

mysql> start slave;Query OK, 0 rows affected, 1 warning (0.00 sec)复制代码

10、查看备库状态

mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                  Master_Host: 192.168.96.95                  Master_User: root                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000004          Read_Master_Log_Pos: 2898               Relay_Log_File: 0981bb088bd0-relay-bin.000002                Relay_Log_Pos: 1093        Relay_Master_Log_File: binlog.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:          Replicate_Ignore_DB:           Replicate_Do_Table:       Replicate_Ignore_Table:      Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:                   Last_Errno: 0                   Last_Error:                 Skip_Counter: 0          Exec_Master_Log_Pos: 2898              Relay_Log_Space: 1309              Until_Condition: None               Until_Log_File:                Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:           Master_SSL_CA_Path:              Master_SSL_Cert:            Master_SSL_Cipher:               Master_SSL_Key:        Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:  Replicate_Ignore_Server_Ids:             Master_Server_Id: 10000                  Master_UUID: 62cd056a-e9f1-11eb-9218-0242ac110002             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:      Last_IO_Error_Timestamp:     Last_SQL_Error_Timestamp:               Master_SSL_Crl:           Master_SSL_Crlpath:           Retrieved_Gtid_Set: 62cd056a-e9f1-11eb-9218-0242ac110002:32            Executed_Gtid_Set: 267c5d14-e9f4-11eb-a424-0242ac110002:1-12,62cd056a-e9f1-11eb-9218-0242ac110002:32                Auto_Position: 0         Replicate_Rewrite_DB:                 Channel_Name:           Master_TLS_Version:       Master_public_key_path:        Get_master_public_key: 0            Network_Namespace:1 row in set, 1 warning (0.00 sec)复制代码

其中,一些重要的参数:

Slave_IO_Running:I/O thread 状态Slave_SQL_Running:SQL thread 状态Master_Log_File:当前主库的 binlogRead_Master_Log_Pos:正在读取主库当前 binlog 的 pos 位置Exec_Master_Log_Pos:执行到主库 binlog 的 pos 位置

Slave_IO_Running 和 Slave_SQL_Running 两个参数都为 yes 时,代表从节点配置正确。

11、验证主从同步

在主库中执行 insert 语句,可以看到备库中成功完成了同步。

主库中 user 表记录:

备库中 user 表记录:

12、slave 设置为 read-only

mysql> show variables like '%read_only%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_read_only      | OFF   || read_only             | OFF   || super_read_only       | OFF   || transaction_read_only | OFF   |+-----------------------+-------+4 rows in set (0.01 sec)mysql> set global read_only=1;Query OK, 0 rows affected (0.00 sec)复制代码

一主一从的架构模式下,一般备库用来读,建议在从服务商启动 read-only 选项,这样保证从服务器上的数据仅与主服务器进行同步,避免其他线程修改数据。在启用 read-only 后,如果操作从服务器的用户没有 super 权限,则对从服务器进行任何的修改会抛出错误(read-only 对拥有 super 权限的账号是不生效的)

双向主从

双向主从架构与单向主从架构的区别是,主库支持写操作,备库去做主库同步;而双向架构,是两台主库,每台都支持写操作,其中一台更新了数据,另外一台去做同步操作,始终保持两台服务器数据一致。

1、保证两台服务器 server_id 不同

2、检查两台是否都开启了 log_bin 参数

3、检查两台 binlog 是否设置基于行复制的格式

4、创建主从复制账号

5、在两台库上分别查询当前 binlog 和 pos

6、在两台库上分别配置主从(互为主从)

7、在两台库上开启主从同步,查看备库状态信息等

8、插入数据验证双向主从架构是否实现互相数据同步

双向主从架构模式与单向主从的配置方式相似,不做赘述。

注:双向主从架构模式不得设置 read-only 为 ON

级联主从

级联主从在单向主从架构的基础上,在第二个 slave 中设置第一个 slave 为 master 开启主从即可,配置过程不再赘述。

多主一从

多主一从,也称多源复制,就是把多台主库的数据同步到一个备库上,备库会创建通往每个主库的管道。在 MySQL 5.7以前,只能实现 一主一从、一主多从或多主多从的架构模式。

在 slave 上配置多个 master 时指定 channel 名称,同时在 start 时也根据 channel 名称开启同步即可,例如:

# 配置主从mysql>CHANGE MASTER to MASTER_HOST='192.168.96.177',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000020',MASTER_LOG_POS=1998 for channel 'master1';Query OK, 0 rows affected, 8 warnings (0.03 sec)# 开启同步mysql>start slave for channel 'master1';Query OK, 0 rows affected, 1 warnings (0.03 sec)复制代码

多主一从架构中,对任意一个 master 做增删改操作时,slave 都会同步此操作。而一般情况下为了保持主从数据一致 slave 只做读操作即可。

多源复制的优点:

可以集中备份,在备库上备份,不会影响线上数据库正常运行数据汇总在一起,方便做数据统计半同步复制

MySQL 复制默认的方式是异步复制,也就是上面实操的,当主库把操作写入 binlog 后,并不知道备库是否已经接受并写入 relay log 了,这种异步复制的方式,如果在高可用集群架构下做主备切换,就会造成新的主库丢失数据的情况。

MySQL5.5后引入了半同步复制功能,主从服务器必须同时安装半同步复制插件,才能开启该复制功能。在半复制功能下,确保备库接收完主库传递的 binlog 并写入自己的 relay log 了,才会通知主库上面的等待线程,该操作完毕。如果等待超时,超过 rpl_semi_sync_master_timeout 参数设置的时间,则关闭半同步复制,并自动转换为异步复制模式,直到至少有一台备库通知主库已经接收到 binlog 了为止。

半同步复制原理如图:

半同步复制提升了主从之间数据的一致性,让复制更加安全可靠。

实战

1、在主库中安装半同步复制插件并开启半同步复制功能

# 安装半同步复制插件mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected, 1 warning (0.07 sec)# 开启半同步复制mysql> set global rpl_semi_sync_master_enabled=on;Query OK, 0 rows affected (0.00 sec)# 查询是否开启半同步复制功能mysql> show variables like '%semi%';+-------------------------------------------+------------+| Variable_name                             | Value      |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled              | ON         || rpl_semi_sync_master_timeout              | 10000      || rpl_semi_sync_master_trace_level          | 32         || rpl_semi_sync_master_wait_for_slave_count | 1          || rpl_semi_sync_master_wait_no_slave        | ON         || rpl_semi_sync_master_wait_point           | AFTER_SYNC |+-------------------------------------------+------------+6 rows in set (0.03 sec)复制代码

2、在备库中安装半同步复制插件并开启半同步复制功能

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> set global rpl_semi_sync_slave_enabled=on;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%semi%';+-------------------------------------------+------------+| Variable_name                             | Value      |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled              | OFF        || rpl_semi_sync_master_timeout              | 10000      || rpl_semi_sync_master_trace_level          | 32         || rpl_semi_sync_master_wait_for_slave_count | 1          || rpl_semi_sync_master_wait_no_slave        | ON         || rpl_semi_sync_master_wait_point           | AFTER_SYNC || rpl_semi_sync_slave_enabled               | ON         || rpl_semi_sync_slave_trace_level           | 32         |+-------------------------------------------+------------+8 rows in set (0.00 sec)复制代码

如果想要开机自启动半复制功能,可以将 rpl_semi_sync_master_enabled 和 rpl_semi_sync_slave_enabled 参数写到 my.cnf 中。

3、在备库中重启 I/O 线程即可激活半同步复制。

mysql> stop slave io_thread;Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave io_thread;Query OK, 0 rows affected, 1 warning (0.02 sec)复制代码

4、在主库中查看半同步复制功能是否正常正常运行

mysql> show global status like '%semi%';+--------------------------------------------+-------+| Variable_name                              | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients               | 1     || Rpl_semi_sync_master_net_avg_wait_time     | 0     || Rpl_semi_sync_master_net_wait_time         | 0     || Rpl_semi_sync_master_net_waits             | 0     || Rpl_semi_sync_master_no_times              | 0     || Rpl_semi_sync_master_no_tx                 | 0     || Rpl_semi_sync_master_status                | ON    || Rpl_semi_sync_master_timefunc_failures     | 0     || Rpl_semi_sync_master_tx_avg_wait_time      | 0     || Rpl_semi_sync_master_tx_wait_time          | 0     || Rpl_semi_sync_master_tx_waits              | 0     || Rpl_semi_sync_master_wait_pos_backtraverse | 0     || Rpl_semi_sync_master_wait_sessions         | 0     || Rpl_semi_sync_master_yes_tx                | 0     |+--------------------------------------------+-------+14 rows in set (0.01 sec)复制代码

其中,Rpl_semi_sync_master_clients 参数代表有一个备库连接到了主库,并且是半同步复制方式。

5、经验证,在主库中操作数据,备库同步数据失败时,会导致主库插入数据缓慢,代表正在等待备库的响应结果,等待超时了,此时查看半同步复制状态,被切换为了异步复制方式。而想从异步复制方式切换为半同步复制方式,需要重启备库的 I/O thread 才行。

GTID

GTID(Global Transaction ID)是一个已提交事务的编号,并且是一个全局唯一的编号,MySQL5.6以后在主从复制类型上新增了 GTID 复制。是由 server_uuid 和事务 id 组成的,即 GTID=server_uuid:transaction_id,server_uuid 是在数据库启动过程中自动生成的,每台机器的 server_uuid 都不同,而 transaction_id 就是事务提交时由系统顺序分配的一个不会重复的序列号。

GTID 和异步复制、半同步复制类似,只不过不再利用传统复制模式的 binlog 文件和 position 号了,而是在备库 “change master to” 时使用 master_auto_position=1 的方式进行搭建,这就让操作变的更加方便和可靠。

使用 GTID 模式搭建过程时,主库my.cnf中要配置以下参数:

gitd_mode=onenforce_gtid_consistency=onlog_bin=on复制代码

备库中要配置:

gitd_mode=onenforce_gtid_consistency=onlog_slave_updates=1复制代码

配置好参数后,如果是新搭建的主从环境,就可以直接在库中之心 change master to 语句了,如果是运行了一段期间的主库,还需要利用备份方式从主库 dump 出数据到备库,先完成基于某个点的 GTID 复制,备库再从那个点之后再开始同步。前面实战的配置方式就是使用的 GTID 模式。

作者:_沸羊羊_

链接:

来源:掘金

著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

标签: #mysql主从复制原理详解 #mysql主从模式原理 #mysql 主从切换的原理 #mysql数据库主从复制原理 #mysql 主从复制原理