Mysql 主从复制

2023-04-22
上海
本文字数：4123 字
阅读完需：约 14 分钟

Mysql 主从复制 Mysql 主从复制

一、mysql 主从同步原理

Mysql 主从复制也可以称为 Mysql 主从同步，它是构建数据库高可用集群架构的基础。它通过将一台主机的数据复制到其他一台或者多台主机上，并重新应用日志（realy log）中的 SQL 语句来实现复制功能。Mysql 支持单向，双向，链式级联，异步复制，复制过程中一台服务器充当主库（master），而一个或者多个服务器充当从库（slave）

1.1、主从复制功能

主从复制原理：master 服务器上工作线程 I/O dump thread，从服务器上两个工作线程，一个是 I/O thread，另一个是 SQL thread。主库把外界接收到的 SQL 请求记录到自己的 binlog 日志中，从库的 I/O thread 去请求主库的 binlog 日志，并将得到的 binlog 日志写到自己的 Realy log（中继日志）文件中。然后在从库上重做应用中继日志中的 SQL 语句。主库通过 I/O dump thread 给从库 I/O thread 传送 binlog 日志。

1.2、复制中的参数详解

log-bin：搭建主从复制，必须开启二进制日志
server-id：mysql 在同一组主从结构中的唯一标识
sever-uuid：存放在数据目录中的 auto.cnf 中
read only：设置从库为只读转态
binglog_format: 二进制日志的格式，使用 row 模式
log_salve_updates: 将 master 服务器上获取的数据信息记录到从服务器的二进制日志文件中
binglog-db-db：选择性复制数据库（在主库上使用）
binglog-ignore-db：忽略某个库的复制
gtid_mode: gtid 模式是否开启，使用 gtid 模式，设置 gtid_mode=on
enforce-gtid-consistency: 使用 gtid 复制，开启，enforce-gtid-consistency=on

二、mysql 主从复制（binlog）

2.1、修改主库配置文件

vim /etc/my.cnf[mysqld]####: for binlogserver-id=1binlog_format                       =row                          #     rowlog-bin                             =/data/mysqlData/binlog/mysql-binlog-bin-index                       =/data/mysqlData/binlog/mysql-bin.index                      #      offbinlog_rows_query_log_events        =on                             #   offlog_slave_updates                   =on                             #   offexpire_logs_days                    =7                              #   0binlog_cache_size                   =65536                          #   65536(64k)#binlog_checksum                    =none                           #  CRC32sync_binlog                         =0                              #   1slave-preserve-commit-order         =ON                             #

复制代码

2.2、主库上执行操作

# 创建主从复制账号create user 'repl'@'192.168.5.%' identified by 'repl@2019#pl';grant replication slave on *.* to 'repl'@'192.168.5.%';flush privileges;# 导出主库数据mysqldump --single-transaction -uroot -proot123 --master-data=2 --flush-logs --events --triggers --routines -A > all.sql# 记录binlog文件和position号head -n 30 all.sql | grep "MASTER_LOG_FILE"head -n 30 all.sql  | grep "MASTER_LOG_POS"# 备份文件传递到从服务器上scp all.sql root@slave:/root/

复制代码

2.3、修改从库的配置文件

server_id                           = 2binlog-ignore-db                    =mysqlbinlog_format                       =rowlog-bin =                           =/data/mysqlData/binlog/slave1-binlog-bin-index                       =/data/mysqlData/binlog/salve1-bin.indexlog-slave-updates                   =onexpire_logs_days                    =7sync_binlog                         = 0relay_log                           =/data/mysqlData/relaylog/relay-binlog_slave_updates                   =1

复制代码

2.4、配置主从

# 导入数据mysql -uroot -proot123 < all.sql# 重置主从reset slave all# 数据库命名执行配置CHANGE MASTER TOMASTER_HOST='192.168.248.137',MASTER_USER='repl',MASTER_PASSWORD='repl@2019#pl',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=3034;# 开启主从start salve# 查看主从复制状态show slave status\G

复制代码

三、mysql 主从复制（gtid）

3.1、修改主库配置文件

vim /etc/my.cnf[mysqld]####: for binlogserver-id=1binlog_format                       =row                          #     rowlog-bin                             =/data/mysqlData/binlog/mysql-binlog-bin-index                       =/data/mysqlData/binlog/mysql-bin.index                      #      offbinlog_rows_query_log_events        =on                             #   offlog_slave_updates                   =on                             #   offexpire_logs_days                    =7                              #   0binlog_cache_size                   =65536                          #   65536(64k)#binlog_checksum                    =none                           #  CRC32sync_binlog                         =0                              #   1slave-preserve-commit-order         =ON                             #####: gitdgtid-mode = ONenforce-gtid-consistency = ON

复制代码

3.2、主库上执行操作

# 创建主从复制账号create user 'repl'@'192.168.5.%' identified by 'repl@2019#pl'grant replication slave *.* to 'repl'@'192.168.5.%'flush privileges# 导出主库数据mysqldump --single-transaction -uroot -proot123 --opt --master-data=2 --flush-logs --events --triggers --routines -A > all.sql

复制代码

3.3、修改 mysql 从服务器配置

server_id                           = 2binlog-ignore-db                    =mysqlbinlog_format                       =rowlog-bin =                           =/data/mysqlData/binlog/slave-binlog-bin-index                       =/data/mysqlData/binlog/salve-bin.indexlog-slave-updates                   =onexpire_logs_days                    =7sync_binlog                         = 0relay_log                           =/data/mysqlData/relaylog/relay-binread_only                           =1log_slave_updates                   =1####: gitdgtid-mode = ONenforce-gtid-consistency = ON

复制代码

3.4、配置主从

# 清空 gtid_executedreset master# 数据导入mysql -uroot -proot123 < all.sql# 配置主从CHANGE MASTER TOMASTER_HOST='192.168.248.137',MASTER_USER='repl',MASTER_PASSWORD='repl@2019#pl',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1；# 开启主从start slave# 查看主从复制状态show slave status\G

复制代码

3.5、跳过事务

stop slaveset gtid_next='f75ae43f-3f5e-11e7-9b98-001c4297532a:20'begincommitset gtid_next='AUTOMATIC'start slave

复制代码

四、mysql 从传统模式改为 gtid

4.1、修改全局变量

1、修改enforce_gtid_consistency为warnset global enforce_gtid_consistency=warn;2、修改enforce_gtid_consistency为onset global enforce_gtid_consistency=on;3、修改gtid模式为off_permissiveset global gtid_mode=off_permissive;4、修改gtid模式为on_permissiveset global gtid_mode=on_permissive;5、确认从库的onging_anonymous_transaction_count参数是否为0show global status like '%ongoing_anonymous_%';6、开启gtidset global gtid_mode=on;7、开启主从复制stop slavechange master to master_auto_position=1;start slave

复制代码

4.2、修改 my.cnf 配置文件

# 主库添加配置gtid_mode=onenforce_gtid_consistency=on# 主库添加配置gtid_mode=onenforce_gtid_consistency=onlog_slave_updates=1

复制代码

4.3、数据导出导入

# 主库数据导出mysqldump --single-transaction -uroot -proot123 --opt --master-data=2 --flush-logs --events --triggers --routines -A > all.sql# 从库数据导入systemctl restart mysqldreset mysql -uroot -p < all.sql

复制代码

4.4、从库开启主从

reset master# 配置msater主机信息CHANGE MASTER TOMASTER_HOST='192.168.0.12',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_AUTO_POSITION = 1;# 开启主从start slave

复制代码

4.5、gtid 跳过事件

方法一

# 查看gtid_next的值show variables like '%next%';# 停止从库stop slave;# 修改gtid为下一个值set gtid_next='6a5a698f-18eb-11e9-afa0-6c92bf45c92e:17';begincommitSET GTID_NEXT="AUTOMATIC";start slave;show slave status;

复制代码

方法二

# 重置masterstop slave;reset master;SET @@GLOBAL.GTID_PURGED ='8f9e146f-0a18-11e7-810a-0050568833c8:1-4;START SLAVE;

复制代码

方法三

# pt 忽略错误码pt-slave-resetart -S /var/lib/mysql/mysql.sock —error-numbers=1062 --user=root --password='bc.123456'# pt 忽略错误信息pt-slave-resetart -S /var/lib/mysql/mysql.sock —error-numbers=1062 --user=root --password='bc.123456'

复制代码

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/ed058d82182c2bf837d4a29af】。文章转载请联系作者。

乌龟哥哥

关注

正在努力寻找offer的大四小菜鸟 2021-03-16 加入

擅长 Hbuilder、VS Code、MyEclipse、AppServ、PS 等软件的安装与卸载精通 Html、CSS、JavaScript、jQuery、Java 等单词的拼写熟悉 Windows、Linux、等系统的开关机看–时间过得多快，不说了，去搬砖了

发布

暂无评论

创作场景

Mysql 主从复制

一、mysql 主从同步原理

1.1、主从复制功能

1.2、复制中的参数详解

二、mysql 主从复制（binlog）

三、mysql 主从复制 （gtid）

3.1、修改主库配置文件

3.2、主库上执行操作

3.3、修改 mysql 从服务器配置

3.4、配置主从

3.5、跳过事务

四、mysql 从传统模式改为 gtid

4.1、修改全局变量

4.2、修改 my.cnf 配置文件

4.3、数据导出导入

4.4、从库开启主从

4.5、gtid 跳过事件

方法一

方法二

方法三

乌龟哥哥

评论

三、mysql 主从复制（gtid）