MySQL - 主从复制: 基于二进制文件复制详解
前言
主从复制是指把一个 MySQL 的数据库服务器作为主服务器(master), 然后把 master 的数据复制到一个或者多个 MySQL 数据库服务器作为从服务器(slave). 从 master 到 slave 的复制一般是异步复制, 所以从服务器的复制可以随时停止, 也不会影响到主服务器的使用. 可以通过配置来决定只复制哪些数据库或者哪些表的数据.
主从复制的优点
读写分离提高负载: master 服务器不在负载读操作, 只处理写入和更新操作, 可显著提升主服务器的写操作的性能. 而读操作是通过多个 slave 服务器来读取数据, 多个 slave 服务器可以分散读操作的压力, 减少对单机 I/O 和带宽的依赖, 也可以提升读操作的性能.
数据安全: 因为数据是异步复制过来的, 可以在 slave 服务器上进行测试, 或者进行数据分析. 即不会更改 master 服务器的数据, 也不会影响 master 服务器的性能
远程数据分发: 当你需要在本地使用数据时, 可以通过复制功能把数据复制到本地, 这样就不需要访问远程 master 服务器
基于二进制文件 (binary log) 复制
原理介绍
1,master 服务器把对源数据库的写入和更新操作以事件的方式记录到二进制日志文件 (binary log) 中. 不同的操作方式会以不同的日志格式记录到文件中
2,slave 服务器开启一个 I/O 线程连接 master 服务器去请求 binary log, 然后写入到本地的中继文件 (relay log) 中
3,master 的开启一个 log dump 线程读取 binary log 并传送给 slave 的 I/O 线程
4,slave 开启一个 SQL 线程读取 relay log 中的命令, 在 slave 服务器上执行
线程说明参考
环境准备
操作系统 | 数据库 | ip 地址 | 端口 | 主 / 从 |
---|---|---|---|---|
window server 2008 r2 | mysql 8.0.12 | 10.119.173.98 | 3006 | 主 |
window server 2008 r2 | mysql 8.0.12 | 10.119.173.97 | 3006 | 从 |
window server 2008 r2 | mysql 8.0.12 | 10.119.173.96 | 3006 | 从 |
1.Master 配置
1. 需要设置一个唯一的 server-id , 并且设置二进制日志文件 log-bin=[file_name].
MySQL8.0 之前的版本默认 log_bin=OFF 是关闭日志记录的, 设置了 log-bin, 就会开启记录日志 log_bin=ON
编辑 master 的配置文件 my.INI, 在 [mysqld] 下添加如下内容
- [mysqld]
- server-id=1 #服务器 id
- log-bin=MySQL-bin #二进制日志文件的基名字
- # 设置需要写日志的数据库的名称, 不设置默认所有数据库. 一个配置项只能配置一个数据库, 如果要设置写多个数据库, 那么需要写多份配置项, 用逗号来分割多个数据库是无效.
- binlog-do-db=test
- #binlog-do-db=test1
- #binlog-do-db=test2
- # 设置不需要写日志的数据库的名称, 一个配置项只能配置一个数据库, 如果要设置写多个数据库, 那么需要写多份配置项, 用逗号来分割多个数据库是无效.
- binlog-ignore-db=MySQL
- # 日志记录格式, 默认是 ROW 行模式. 还有 STATEMENT 语句模式和 MIXED 混合模式. 不同的记录格式会对 binlog-do-db 和 binlog-ignore-db 产生影响
- #STATEMENT 模式: 记录执行的 SQL 语句
- #Row 模式: 记录语句执行后对单个行做的修改, 而不是记录执行的语句
- binlog-format=ROW
binlog-format 的参考
binlog-do-db 的说明以及和 binlog-format 关系的参考
2, 在 InnoDB 存储引擎时, 为了获得最大持久性和一致性, 需要增加下面两个配置
- # 控制写入二进制日志的频率.
- #0 表示 MySQL 服务器依赖与操作系统来把二进制文件写入到磁盘中, 这种情况下性能最好. 但是在断电或者操作系统崩溃时, 服务器可能提交了未同步到二进制文件的事务.
- #1 表示在提交事务前先把二进制文件写入到磁盘中, 可能会对性能有印象
- sync_binlog=1
- # 控制事物提交时 ACID 的遵守的严格性和性能之间的平衡. 默认 1 时, 表示每次事务提交时记录日志的同时马上写入磁盘
- innodb_flush_log_at_trx_commit=1
sync_binlog 的参考
innodb_flush_log_at_trx_commit 的参考
3, 在 master 上给 slave 创建一个拥有复制权限的账号 repl, 密码是 123456
- CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
- GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
2.Slave 配置
1, 需要给 slave 设置一个唯一的 server-id , 值不要和 master 和其他 slave 重复. 如果需要只复制特定的数据库或者排除特定的数据库, 可以使用 replicate-do-db 和 replicate-ignore-db 配置.
当启用 slave 的 log-bin 日志记录, 配合 log-slave-updates=ON 时, slave 会在复制 master 的数据时也记录到自己的二进制日志中. 这样方便以后的主从切换, 数据恢复. 甚至可以实现更复杂的多级复制拓扑, 比如: master->slave->slave
编辑 slave 的配置文件 my.INI, 在 [mysqld] 下添加如下内容
- [mysqld]
- server-id=2 #服务器 id
- log-bin=MySQL-bin #二进制日志文件的基名字
- # 当开启了 log-bin 时, 并且 log-slave-updates=ON 时 slave 复制 master 的数据时也记录到自己的二进制日志中, 默认值 ON
- log-slave-updates=ON
- # 中继日志的基名称
- relay-log=MySQL-relay
- # 设置需要复制的数据库的名称, 不设置默认所有数据库. 一个配置项只能配置一个数据库, 如果要设置写多个数据库, 那么需要写多份配置项, 用逗号来分割多个数据库是无效, 效果同 binlog-do-db.
- replicate-do-db=test
- # 设置不需要复制的数据库的名称, 一个配置项只能配置一个数据库, 如果要设置写多个数据库, 那么需要写多份配置项, 用逗号来分割多个数据库是无效, 效果同 binlog-ignore-db.
- replicate-ignore-db=MySQL
- # 告诉 slave 服务器在启动时不开启复制功能, 默认值 OFF
- skip-slave-start=OFF
其他 slave 服务器配置一样, 只有 server-id 不同
slave 的配置参考
3. 配置和开启复制功能
1, 查询 master 当前的状态
因为是 InnoDB, 首先在 master 上执行 FLUSH TABLES WITH READ LOCK; 语句, 关闭所有打开的表, 刷新缓存. 并且用全局读锁锁住所有的表.
然后在 master 上执行下面 sql 获取 binary log 的文件名称和当前写入的坐标
- SHOW MASTER STATUS;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | MySQL-bin.000001 | 115 | test | MySQL |
- +------------------+----------+--------------+------------------+
2, 复制 master 库的数据到 slave 库 如果在 slave 开启复制之前, master 的 test 库已经有数据了, 那么需要先把 master 的数据快照 dump 下来, 然后复制到 slave 服务器. 如果是新库没有数据要复制, 可以跳过此步骤到下一步. 使用 mysqldump 工具命令如下:
mysqldump -h[IP 地址] -P[端口号] -u[用户名] -p[密码] --databases test> dump.sql
mysqldump -h10.119.173.98 -P3006 -uroot -p123456 --databases test> dump.sql
复制数据快照参考
3, 释放 master 上的全局读锁 UNLOCK TABLES;
4, 导入 master 的 test 库数据到 slave 服务器, 导入第 2 步中 dump.sql 文件. 如果是新库没有数据导入, 则跳过此步骤, 进行到第 5 步.
MySQL -h[IP 地址] -P[端口号] -u[用户名] -p[密码] <dump.sql
MySQL -h10.119.173.96 -P3006 -uroot -p123456 < dump.sql
5, 在 slave 服务器上配置 slave 要复制的文件和开始的坐标
- CHANGE MASTER TO
- MASTER_HOST='10.119.173.98',
- MASTER_PORT=3006,
- MASTER_USER='repl',
- MASTER_PASSWORD='123456',
- MASTER_LOG_FILE='mysql-bin.000001',
- MASTER_LOG_POS=115;
MASTER_LOG_FILE=3. 配置复制功能第 1 步查询出来的 File
MASTER_LOG_POS=3. 配置复制功能第 1 步查询出来的 Position
CHANGE MASTER TO 参考
6. 开启复制功能
在 slave 服务器上执行 START SLAVE 语句, 开启 slave 的复制功能.
START SLAVE;
7. 查看从库的复制状态信息 SHOW SLAVE STATUS
Slave_IO_Running 和 Slave_SQL_Running 都 = Yes, 就表明复制功能正常运行
Slave_IO_Running: 从 master 读取二进制文件, 写入到 slave 的中继日志的 I/O 线程. yes 表示正常
Slave_SQL_Running: 从中继日志中读取新的命令执行到 slave 的库中的 SQL 线程. yes 表示正常
Master_Log_File: 表示 I/O 线程当前正在读取的 master 的二进制文件名称
Read_Master_Log_Pos: 表示 I/O 线程当前正在读取的 master 的二进制文件中的位置信息
Relay_Log_File: 当前在执行的中继日志名称
Relay_Log_Pos: 当前在执行的中继日志中执行到的位置
Last_Errno: 最后一次复制失败的错误日志号
Last_Error: 最后一次复制失败的错误日志
当 slave 服务器停止了复制功能, 要重新开始时, 要从停止前读取的位置开始继续复制命令. 第 5 步中 MASTER_LOG_FILE=Master_Log_File 的值, MASTER_LOG_POS=Read_Master_Log_Pos 的值
SHOW SLAVE STATUS 参考
这样就配置好了一个 master->slave 的主从复制, 多个从库的配置可以重复[3. 配置和开启复制功能的 4-7 步] 即可.
后记
如果想配置成复杂的多级复制拓扑, 比如 A->B->C, 那么先配置 A 作为 master,B 作为 slave,B 复制 A 的数据. 然后在配置 B 作为 master,C 作为 slave,C 复制 B 的数据.
来源: https://www.cnblogs.com/xyyz120/p/12760725.html