Mysql主主复制
主主复制有两种模式，主备复制模式和主主复制模式，建议使用主备复制模式，因为主主模式可能会产生数据冲突而造成复制链路中断。本篇主要讲主备复制模式。
注意：主备数据库需要采用相同的mysql版本，以免因版本差异出现问题
注意：某些参数配置后需要重启服务器才能生效，建议上线之前都把主从复制参数配置好
主备复制模式
主备复制模式中只有一台会对外提供服务，只有对外提供服务的机器不可用时，另一台才会对外提供服务。
- 注意事项
1.只有一台主服务能对外提供服务，另一台主服务处于只读状态并且只作为热备使用
2.在对外提供服务的主库出现故障或是计划性维护时才会进行切换
3.使用原来的备库作为主库，而原来的主库需设置成新的备库，同时需设置为只读或下线状态，待维护完成后再重新上线
4.两台服务器上的初始数据需相同
5.两台服务器都需要启动binlog，并配置不同的server_id
6.两台服务器上都要启用log_slave_updates参数
7.备库在初始化时需启用read_only
8.需修改两个库的自增参数
主主复制模式
主主复制模式中，两台都会对外提供服务，这种模式并不会分担主库的写负载， 因为无论写到哪一个主服务上，都会复制到另一个主服务上重新执行，所以这种模式并不能分担写负载。因为主主模式可能会产生数据冲突而造成复制链路中断，.不建议使用主主复制模式 。
如何避免数据冲突？
1.两个主服务中最好存放不同的表 ，这样一个表只在一个库中能很好的避免冲突
2.修改自增ID的生成方式，因为有两台主服务，所以自增量可以设置为2，一个起始点设为1，
另一个设为2，这样生成的id就不会冲突，对应的设置参数如下：
主服务1：会生成1,3,5，7,9…
auto_increment_increment = 2
auto_increment_offset =1
主服务2：会生成2,4,6,8,10…
auto_increment_increment = 2
auto_increment_offset =2
主备复制模式架构图
下面是主备复制的灵魂示意图，该架构下，同一时间只有一个主库可提供服务，另一台主库作为热备使用。
如下图，当主1不可用时，会切换到主2作为主库提供服务，主1作为备库，同时所有从库重新和主2建立主从复制关系。
主备复制模式流程
说明：本篇复制是基于二进制日志的
- 两个主库都需开启binlog日志，记录所有修改操作到binlog日志文件中
- 两个库的IO进程相互读取对方的binlog日志并存到该从库的中继日志Relay log中
- mysql会开启一个sql线程，定时读取Relay log日志，如果发现有更新会立即把更新的内容在本机的数据库上面执行一遍
配置主备复制模式步骤
一、配置主备复制参数
二、在主库上创建用于复制的账号
三、对主库上的数据进行全备
四、对备库上的数据进行初始化，使主备数据一致
五、启动备库对主库的复制链路
六、启动主库对备库的复制链路
一、配置主备复制参数
配置主库服务
编辑/etc/my.cnf文件，修改和添加配置，然后重启服务
注意：如果是mysql5.7及以上版本，还需查看auto.cnf下server-uuid的是否一样，如果一样主从复制会出现问题，需删除掉server-uuid，然后重启服务重新生成server-uuid，auto.cnf存储的路径可能不同，我的路径在/var/lib/mysql/
- 编辑/etc/my.cnf文件
vim /etc/my.cnf
- 修改和添加配置
#每台服务的server_id都不能一样，建议server_id设置成ip的最后一段
server_id = 118
#master意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原先master上冲突
#修改自增ID的生成方式，因为有两台主服务，所以自增量可以设置为2，一个起始点设为1，另一个设为2，这样生成的id就不会冲突
#会生成1,3,5，7,9..
auto_increment_increment = 2
auto_increment_offset =1
#开启binlog，并存到该目录，需配置属主和属组
log_bin = /var/lib/mysql/binlogs/binlog.
#把中继日志relay log存到该目录，需配置属主和属组
relay_log = /var/lib/mysql/relaylogs/relaylog
#binlog日志文件大小的最大值，当达到设定大小后，会使用新生一个binlog日志
max_binlog_size = 1000M
#以row格式来记录binlog，仅保存记录被修改细节，不记录 SQL 语句上下文相关信息，能非常清晰的记录下每行数据的修改细节，
#不需要记录上下文相关信息，因此不会发生某些特定情况下的 procedure、function、及 trigger 的调用触发无法被正确复制的问题，
#任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性。
binlog_format = row
#设置过期时间，超过过期时间会被自动删除，但是有触发条件的，触发条件时：1.binlog大小超过max_binlog_size，
#2.手动执行flush logs，3.重新启动时(MySQL会新生产一个文件用于记录binlog)
expire_logs_days = 7
#sync_binlog=1，表示每次事务提交，MySQL都会把binlog刷下去，是最安全最能保证一致性，但是性能损耗最大
sync_binlog = 1
#在重启时，跳过开启复制链路，即启动复制链路，默认是启动的，这时如果有问题，会造成主从复制链路中断，所以需手动启动复制链路
skip_slave_start = on
#值设为on时，从库binlog才会记录主库同步的操作日志
log_slave_updates=on
#master_info_repository和relay_log_info_repository参数表示，把主从复制的信息存到innodb表中，默认是存在文件系统中的，
#一旦slave宕机，可能会出现文件中的信息和实际的信息不一致的情况，存在表中就可以利用innodb的恢复机制（redo log）保证一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE
- 创建配置中的文件夹，并配置属主和属组
mkdir -p /var/lib/mysql/binlogs
mkdir -p /var/lib/mysql/relaylogs
chown -R mysql:mysql /var/lib/mysql/binlogs/
chown -R mysql:mysql /var/lib/mysql/relaylogs/
- 重启master服务
如果不能重启，可使用show variables like ‘参数%’ 进行查看，set global 进行修改
service mysqld restart
配置备库服务
编辑/etc/my.cnf文件，修改和添加配置，然后重启服务
注意：如果是mysql5.7及以上版本，还需查看auto.cnf下server-uuid的是否一样，如果一样主从复制会出现问题，需删除掉server-uuid，然后重启服务重新生成server-uuid，auto.cnf存储的路径可能不同，我的路径在/var/lib/mysql/
- 编辑/etc/my.cnf文件
vim /etc/my.cnf
- 修改和添加配置
#每台服务的server_id都不能一样，建议server_id设置成ip的最后一段
server_id = 119
#master意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原先master上冲突
#修改自增ID的生成方式，因为有两台主服务，所以自增量可以设置为2，一个起始点设为1，另一个设为2，这样生成的id就不会冲突
#会生成2,4,6,8,10...
auto_increment_increment = 2
auto_increment_offset =2
#开启binlog，并存到该目录，需配置属主和属组
log_bin = /var/lib/mysql/binlogs/binlog
#把中继日志relay log存到该目录，需配置属主和属组
relay_log = /var/lib/mysql/relaylogs/relaylog
#使本库只有读权限。不提供写权限，保证只有一个库对外提供写服务
read_only = on
#在重启时，跳过开启复制链路，即启动复制链路，默认是启动的，这时如果有问题，会造成主从复制链路中断，所以需手动启动复制链路
skip_slave_start = on
#值设为on时，从库binlog才会记录主库同步的操作日志
log_slave_updates=on
#master_info_repository和relay_log_info_repository参数表示，把主从复制的信息存到innodb表中，默认是存在文件系统中的，
#一旦slave宕机，可能会出现文件中的信息和实际的信息不一致的情况，存在表中就可以利用innodb的恢复机制（redo log）保证一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE
#binlog日志文件大小的最大值，当达到设定大小后，会使用新生一个binlog日志
max_binlog_size = 1000M
#以row格式来记录binlog，仅保存记录被修改细节，不记录 SQL 语句上下文相关信息，能非常清晰的记录下每行数据的修改细节，
#不需要记录上下文相关信息，因此不会发生某些特定情况下的 procedure、function、及 trigger 的调用触发无法被正确复制的问题，
#任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性。
binlog_format = row
#设置过期时间，超过过期时间会被自动删除，但是有触发条件的，触发条件时：1.binlog大小超过max_binlog_size，
#2.手动执行flush logs，3.重新启动时(MySQL会新生产一个文件用于记录binlog)
expire_logs_days = 7
#sync_binlog=1，表示每次事务提交，MySQL都会把binlog刷下去，是最安全最能保证一致性，但是性能损耗最大
sync_binlog = 1
- 创建配置中的文件夹，并配置属主和属组
mkdir -p /var/lib/mysql/binlogs
mkdir -p /var/lib/mysql/relaylogs
chown -R mysql:mysql /var/lib/mysql/binlogs/
chown -R mysql:mysql /var/lib/mysql/relaylogs/
- 重启slave服务
如果不能重启，可使用show variables like ‘参数%’ 进行查看，set global 进行修改
service mysqld restart
二、在主库上创建用于复制的账号
#创建用户，%表示在该网段下都能访问
create user 'repluser'@'192.168.3.%' identified by '密码';
#赋权，只赋予replication slave权限就行
grant replication slave on *.* to 'repluser'@'192.168.3.%';
三、对主库上的数据进行全备
- 参数解释：
-u：为指定用户名
-p：为指定密码
--single-transaction：Innodb下使用，为在备份之前启动一个事务，来保证数据的一致性，
不过要确保没有其他DDL操作在执行，因为Innodb隔离级别无法对ddl进行隔离
--master-data=1：生成的备份文件中change master不会被注释，记录了binlog文件和日志节点的位置
--routines：备份数据库中存在的存储过程
--triggers：备份数据库中所存在的触发器
--events：备份数据库中的调度事件
- 全备脚本：
mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > allData.sql
四、对备库上的数据进行初始化，使主备数据一致
- 把主库上全量备份的数据复制到备库上
scp -r root@192.168.3.118:/usr/local/backupData/ /usr/local/backupData/
- 初始化备库
mysql -uroot -p < /usr/local/backupData/allData.sql
- 进入mysql，查看是否初始化成功
mysql -uroot -p
show databases;
五、启动备库对主库的复制链路
- 查看change master上的binlog文件和日志节点的位置
more allData.sql
- 配置复制链路
需要先进入备库的mysql命令行，然后执行change master命令，最后启动复制链路
#进入slave的mysql服务
mysql -uroot -p
#master_host为从那台服务复制，即master的ip
#master_log_file和master_log_pos为告诉slave服务从哪个binlog文件的哪个日志点开始复制
change master to
master_host = '192.168.3.118',
master_user = 'repluser',
master_password = '密码',
master_log_file = 'binlog.000004',
master_log_pos = 154;
#启动复制链路
start slave;
#查看复制链路是否启动成功，IO线程为Yes和SQL线程为Yes即为启动成功
show slave status \G
六、启动主库对备库的复制链路
- 在备库上查看binlog文件及日志节点位置
show master status \G
- 配置复制链路
需要先进入主库的mysql命令行，然后执行change master命令，最后启动复制链路
#进入slave的mysql服务
mysql -uroot -p
#master_host为从那台服务复制，即master的ip
#master_log_file和master_log_pos为告诉slave服务从哪个binlog文件的哪个日志点开始复制
change master to
master_host = '192.168.3.119',
master_user = 'repluser',
master_password = '密码',
master_log_file = 'binlog.000005',
master_log_pos = 1334;
#启动复制链路
start slave;
#查看复制链路是否启动成功，IO线程为Yes和SQL线程为Yes即为启动成功
show slave status \G
七、可能出现的问题：IO线程和SQL线程不全是Yes，即为不成功
1.可能是防火墙问题，
可使用firewall-cmd --zone=public --list-ports查看端口是否开通，
如果未开通防火墙，使用此命令进行开通firewall-cmd --zone=public --add-port=3306/tcp --permanent，
最后重载防火墙firewall-cmd --reload
2.复制用户创建错误
在创建用户时未指定可访问的网络，只指定了localhost能访问，可通过select host,user from msyql.user查询
3.配置change master to 时指定的binlog文件和日志节点偏移量不对
619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
