主从复制配置步骤
鉴于相关术语及其汉译有点乱，这里先列举一下
- 主复制服务器，也简称主库，英文称为master, source, primay
- 从复制服务器，也简称备库，英文称slave, replica, secondary
1. 主复制服务器的配置
主复制服务器，有时会不规范的称为主库；在MySQL 8.x 中称为复制源，这个名称更合理，不过旧名称的使用更普遍。
主服务器MySQ配置参数，参数主要依据《高性能MySQL 第三版》；从服务器配置与此稍有不同，主要是增加几个参数，如后节。
#replication server-id=100 log-bin=mysql-bin expire_logs_days=30 sync_binlog=1 relay_log=mysql-relay-bin log_slave_updates=1 #replication safe for innodb engine innodb_flush_logs_at_commit innodb_support_xa=1
服务器id号；设置二进制日志文件名（隐含启用）；binlog文件过期天数（最大允许99，设为0则不过期）；每次写入binlog后同步到磁盘。接下几个参数实际是slave上使用的（后文slave的配置有述），写在这里可以让主从配置参数保持一致，避免出错，也方便实现级联式的复制拓扑。innodb_… 的参数可以增强 innodb存储引擎数据可靠性，代价是更高的磁盘负载。
2. 添加复制用户
只需要赋两个全局的权限: REPLICATION SLAVE, REPLICATION CLIENT
CREATE USER 'repl'@'192.168.10.132' IDENTIFIED BY '***'; GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'repl'@'192.168.10.132' IDENTIFIED BY '***';
3. 备份主库，备份中加入binlog及位置
全面备份主库，备份前需要已经启用二进制日志。
mysqldump -hlocalhost --opt --master-data=1 --all-databases --max_allowed_packet=8M --net_buffer_length=128K -uroot -pyour-password >all.sql
为了获得一致性的备份，考虑加入参数–single-transaction 或 –lock-all-tables，分别针对事务型引擎及非事务型引擎，但它们不能同时使用。
注意其中–master-data=1参数，它将binlog及位置信息生成到备份文件中，大概如下一行
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000004′, MASTER_LOG_POS=8747725;
4.iptables放行从复制服务器连入
允许从服务器ip到主服务器3306端口的连接
iptables -I INPUT 7 -s 192.168.10.132/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
5. 配置从复制服务器
从复制服务器，也称为备库或从库，它从其上级源取得二进制日志并写为中继日志，然后读取事件并回放。
server-id=201 log-bin=mysql-bin expire_logs_days=30 sync_binlog=1 relay_log=mysql-relay-bin log_slave_updates=1 # actually only for B in A->B->C read_only=1
前四个与主配置一样；中继日志写入binlog （默认是不写的，这实在有点出乎意料）；中继日志文件名；数据只读（针对非root用户，root则不受此限）
6. 在从复制服务器上还原数据
mysql -uroot -p < /path/to/bakup.sql;
6. 配置从复制服务器，连接到主库
事实上下面的log_file,log_pos可以不用再指定，因为当初的备份里已经有该信息。当然这里再指定一次也没负作用。（该命令在MySQL 8.x上已改名为CHANGE REPLICATION SOURCE TO，原名名仍然可用）
change master to master_host='192.168.10.100', master_user='repl', master_password='repl-password', master_log_file='mysql-bin.000004', master_log_pos=8747725;
7.从复制服务器上启动从服务
start slave
顺利的话即完成。
如报 Failed to open the relay log 的错误，先停止slave的mysql服务，删除 /var/lib/mysql/ 下的中继日志相关文件，主要是 *-relay-bin.* 及 relay-log.info，再启动重新start slave
配置中的一些注意事项
- 关于binlog_format 参数。该参数的默认值很可能是STATEMENT。它会造成一些问题，比如 insert into tablename select … limit n 这样的语句，执行时会报warning错误，原因是limit结果在一致性上是非安全的。所以可以加入 binlog_format=MIXED 或者 binlog_format=ROW 的配置参数 。
- 至少在 Mariadb 5.5 上，RESET SLAVE 似乎并不起效，而要写成RESET SLAVE ALL; 才行。
- 如果是同时是主与从的服务器上，必须加入参数 log_slave_updates ，因为，MySQL默认不把中继日志写入二进制日志里（结果就是下级从库收不到相关binlog）。这实在出乎意料。
奇怪的问题
SELinux，有可能造成奇怪的问题，比如，master端口非3306，造成IO线程起不来，报错 error connecting to master … Can’t connect to MySQL server on … (13 “Permission denied”) ，但使用mysql客户端连接都是正常的。（因为使用ssl隧道连接到远端master，所以非标准端口）
日常管理维护相关
查看二进制日志文件
即，查看 data 目录下都有哪些binlog文件，通过 SHOW BINARY LOGS 或 SHOW MASTER LOGS 语句，二者等价。还有中继日志，这个好像没有相关语句，要看只能到磁盘目录里看了。
查看二进制日志内容
可以通过SHOW BINLOG EVENTS语句，或者mysqlbinlog工具进行查看日志内容，即是每条binlog事件干了什么
> SHOW BINLOG EVENTS IN 'mysql-bin.000015' LIMIT 0, 30 # 这是sql语句 $ mysqlbinlog -vv mysql-bin.000016 # 注意，这是个可执行程序，跟 mysqldump 类似安装在bin目录中
刷新二进制日志
（这里flush的翻译为刷新不好，叫刷写或滚动日志文件更利于理解。）终结当前的binlog二进制日志文件的写入，并开启一个新的二进制日志文件 FLUSH BINARY LOGS; 。
如果要刷新中继日志，则 FLUSH RELAY LOGS;（中继日志，一般不需要理它，它在 chagne master … 时会自动重设，一般不需要手动干预）
自动定时清理binlog
通过参数 expire_logs_days 指定过期天数，过期自动清理；最大只允许99天，具体天数据按实际情况定（空间与同步延迟等）。该变量还可运行时设定。
手工清理binlog
mysql的root用户登录客户端，执行purge命令。
> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE 'date'
其中 log_name是show master logs; 显示的日志文件名，如 mysql-bin.000001. 应用举例：
> PURGE MASTER LOGS TO 'mysql-bin.000003'; //清除mysql-bin.000003(含)之前的日志 > PURGE MASTER LOGS BEFORE '2016-11-05 10:00:00'; //清除2016-11-05 10:00:00前的日志 > PURGE MASTER LOGS BEFORE DATE_SUB(NOW(),INTERVAL 3 DAY); //清除3天前日志，使用BEFORE函数计算日期，变量的date自变量还可以为'YYYY-MM-DD hh:mm:ss'格式。详查手册
清理二进制日志的影响。如果当前服务器有一个活跃的从属服务器，该从服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。（参考）
正常运行若干时间后的复制失败后的维护
如果正常复制运行一段时间以后，出现了异常（通常时进程异常结束、断电等），复制故障了，这里通过 SHOW SLAVE STATUS 可以看到 slave状态里有报执行错误的语句，可以据此解决，要注意可能有数据不一致的情况，需要手工核实修正。这时，重新配置slave可能是最简单的处理方法
重新配置slave
事实上这与首次配置是一样的步骤：
stop slave; SOURCE /path/to/bakup.sql; RESET SLAVE ALL; change master to ... show slave status; #检查状态，确认主库及连接账号等 START SLAVE;
手工修正sql线程错误
出现的错误是sql线程执行的错误，可以检查相关表结构、数据等情况，手工同步起来，再继续。或者评估一下相关错误影响如何，认可以无视相关错误而继续往后执行，则这时可以设置变量 SET GLOBAL sql_slave_skip_counter = N 跳N个binlog事件。这里有个小技巧，可以一次性把N设置得大一些，如果后面的事件可以正常执行了，再把这这个变量归0（要先停掉 slave ，再设0，再启动slave）。{这是瞎JB扯的，纯粹的想当然；这个参数并非只跳过错误，而是连续的跳过，根本不执行而一股脑儿的跳过！} 不过 还有个 slave_skip_errors 的参数（新版本8.0.26起已改名为 replica_skip_errors ），应该是忽略复制回放时的错误，可以尝试使用。
手工同步IO线程二进制日志
对于A->B->C 这样多级复制，如果C的IO线程报错，binlog有损坏，可以让它从A复制，但这时同步binlog位置就是个麻烦的事情，可以根据出错的binlog位置 SHOW BINLOG EVENTS IN ‘mysql-bin.000050’ FROM 500600 LIMIT 0, 30 ，通过类似往前找一个标志性的事件（比如INSERT或DELETE事件，有时间戳或主键ID），然后到A服务器上找该时间前后的binlog文件，通过 mysqlbinlog 命令，类似于
mysqlbinlog –database=foodb -vv mysql-bin.500850 |grep -B10 -A 10 “a:19:{i:1733449185;”
找到对应的事件，并找到已经复制重放过的事件位置，即找到文件与位置。这里最大的麻烦就是，SHOW BINLOG EVENT 里找错误位置比较麻烦，这里的FROM参数不能胡乱指定（必须是每条事件的开始位置），所以，应该先不指定FROM参数，而是通过LIMIT 0,30起后找翻页，比如每次加个0，出错后再减小折半并继续尝试。
深入二进制日志
binlog的传递
假定有三台服务器，设置成级联的主从复制，A->B->C，有一条binlog事件在A生成。通过SHOW BINLOG EVENTS语句查看binlog日志内容时，可以看到其中的一列“Server_id”；亦即配置文件里的server-id。该条事件在经过B传递给C，其中的服务器id是保持不变的；事件信息Info也是不变的，整个事件原封不动地写入binlog，并传给下级replica。（是原封不动地传递，这里并不是说：原事件在B上重放时，再生成一次，然后把新生成的事件传递下去）。也正是因此，才可以把B配置成binlog日志服务器，专用做binlog日志分发（即把B上的表设置成blackhole引擎，这样B上的表里没有数据，而只有binlog）。
当然，B收到事件时，会检查是否符合过滤器（即replicate-do…、replicate-ignore… 等参数），不符合的事件将直接忽略掉，是不会写入binlog并往下传递的。
binlog的内容及存储占用
事实上ROW格式的binlog事件也一样可以认为是相关的sql语句，主要ddl 及 insert, update, delete等，但深入探查一下，会发现 update 的相关的binlog 里，同时包含了更新前后的所有字段的值，也就是整行数据。所以，字段多且长的表，如果频繁update，binlog将占用更多的磁盘空间。类似的，删除行的delete操作，也记录了完整的数据。（参看手册）
这也很出乎意料，可能我们原本会以为，只需要通过主键追踪有更新的字段即可，却并不是。大概，这是为了适应主键不一致或没有主键的情况，主库生成binlog时不要做判断，只管无脑的做记录，当slave重放时由sql线程决定是否用主键。
不同主键情况的影响表现
经实验表明（也就是说，手册中没有找到相关的官方说明），slave的sql进程在重放事件时，对于update与delete两类事件，相当于执行一条update或delete语句，其中where条件包括了所有列，即，所有字段都同时匹配的行。这很自然。但，如果本地表有主键或惟一键，那么只按其做更新，而不管其他列。这也可以理解，毕竟有惟一键就不用全表扫描了。
不过，深入想一下，如果主从库的惟一键并不一致，那么，就会造成一些奇异的表现。有时或许当“特性”而hack利用。
Last Updated on 2024/12/27