写点什么

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
用户头像

乌龟哥哥

关注

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

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

评论

发布
暂无评论
Mysql主从复制_三周年连更_乌龟哥哥_InfoQ写作社区