1 MySQL 双主复制
MySQL双主复制是级联复制的特殊形式,A <-> B对等的双向复制。
双主复制企业级方案
方案 | 优势 | 劣势 |
表主键自增 | 应用程序无需修改 | 表ID不连续,总是以当前表最大ID为基础递增 |
序列服务 | 表ID连续 | 需部署序列服务,程序需要改动,架构复杂 |
注:无论使用何种方案,尽可能不使用双写模式。
2 MySQL双主复制配置
2.1 基础环境
角色 | hostname | eth0(业务) | eth1(主从同步) |
master1 | node3 | 10.11.12.3 | 172.16.33.3 |
master2 | node4 | 10.11.12.4 | 172.16.33.4 |
2.2 Master1 配置参数&备份数据
--- 配置my.cnf
[mysqld]
server-id = 3
log_bin = /app/mysql5.7/logs/ocean-bin
log_slave_updates
expire_logs_days = 7
auto_increment_increment = 2
auto_increment_offset = 1
slave_skip_errors = 1007,1008,1032,1049,1062,1146
--- 核验my.cnf配置
egrep "server_id | log | auto " /etc/my.cnf
--- 重启MySQL服务
/etc/init.d/mysqld restart
--- 创建复制账户
grant replication slave on *.* to 'rep'@'10.11.12.%' identified by 'root1234';
flush privilieges;
--- 主库导出数据
mysqldump -A -B -x --master-data=1 | gzip > /bak/ocean_$(date +%F).sql.gz
2.3 Master2 配置参数&开启复制
--- 配置my.cnf
[mysqld]
server_id = 4
log_bin = /app/mysql5.7/logs/ocean-bin
log_slave_updates
expire_logs_days = 7
auto_increment_increment = 2
auto_increment_offset = 2
slave_skip_errors = 1007,1008,1032,1049,1062,1146
--- 核验my.cnf配置
egrep "server_id | log | auto" /etc/my.cnf
--- 重启MySQL服务
/etc/init.d/mysqld restart
--- Master2导入全备
zcat /bak/ocean_$(date +%F).sql.gz | mysql
--- 配置Master2参数
CHANGE MASTER TO MASTER_HOST='10.11.12.3',MASTER_POST=3306,MASTER_USER='rep',MASTER_PASSWORD='root1234';
--- 启动复制
start slave;
--- 查看复制状态
show slave status\G;
mysql -e "show slave status\G;"
--- 查看关键配置项状态
mysql -e "show slave status\G;" | egrep "IO_Running | SQL_Running | _Behind_Master"
--- 首次同步若报错,执行命令:复制指针向后移动一跳
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
--- 查看Master2的binlog位置,用于Master1复制
show master status;
2.4 Master1 开启复制
--- 配置复制参数
CHANGE MASTER TO MASTER_HOST='10.11.12.4',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='root1234',MASTER_LOG_FILE='ocean-bin.000006',MASTER_LOG_POS=65535;
注:"MASTER_LOG_FILE='ocean-bin.000006',MASTER_LOG_POS=65535"是Master2执行"show master status"的结果
--- 启动复制
start slave;
--- 查看复制状态
show slave status\G;
附录
双主复制my.cnf参数对比 | |
Master1 | Master2 |
[client] user = root password = root1234 [mysqld] server_id = 3 basedir = /app/mysql5.7 datadir = /app/mysql5.7/data log_bin = /app/mysql5.7/logs/ocean-bin log_slave_updates expire_logs_days = 7 auto_increment_increment = 2 [mysqld_safe] log-error = /app/mysql5.7/logs/ocean.err sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_STABLES | [client] user = root password = root1234 [mysqld] server_id = 4 basedir = /app/mysql5.7 datadir = /app/mysql5.7/data log_bin = /app/mysql5.7/logs/ocean-bin log_slave_updates expire_logs_days = 7 auto_increment_increment = 2 [mysqld_safe] log-error = /app/mysql5.7/logs/ocean.err sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_STABLES |
相关链接