2019-06-20 MySQL复制高级方案应用实践

作者: 阿丧小威 | 来源:发表于2019-06-27 22:54 被阅读0次

2019-06-20 MySQL复制高级方案应用实践
2019-06-12 MySQL主从复制高级方案与应用实践
MySQL 同步复制及高可用方案总结
常见的高可用MySQL解决方案【转】
MySQL-lesson10-MHA高可用技术
Mysql （7）
叙事高级应用实践-集体叙事实践（叙事专业工作者高级）
MySQL复制功能
mysql主从同步
2019-10-12

1. MySQL级联复制

1.1 MySQL级联复制介绍

MySQL级联复制的特点是从（slave）服务器本身除了作为从服务器之外，同时也会作为其下面从服务器的主数据库服务器。级联复制就是型如A==>B==>C的复制形式。
如下图所示就是级联单向复制逻辑架构图，本套架构一般只能在Master服务器A端进行数据写入，工作场景A作为主库，B作为A的从库，同时B又作为C的主库，C作为B的从库，此时中间的B需要进行特殊的设置（开启binlog功能）。

级联复制逻辑图

1.2 MySQL级联复制实现要点

1. 从库开启binlog日志功能常见应用场景说明

1）当前的从库还要同时作为其他从库的主库，例如，级联复制或双主互为主从场景的情况下，就必须在从库上开启binlog日志功能。
2）将从库作为数据库全备的服务器，此时也要开启binlog日志用于全备恢复之后的增量数据恢复。

2. 从库记录binlog日志方法

在从库的my.cnf中加入如下参数，然后重启服务器即可生效：

log_bin
log_slave_updates    ---必须要有这个参数，否则不会记录Binlog记录
expire_logs_days = 7    ---相当于find /path -type f -name "oldboy-bin.000*" -mtime +7 | xargs rm -f

3. MySQL级联复制的实现说明

除了作为新主库的从库需要开启binlog日志功能之外，级联复制的其他实现步骤与普通的主从复制是相同的，没有任何差别。

1.3 MySQL级联复制的应用场景

1）作为主库的级联从库，在大并发场景下，可以减轻主库下面的直接从库过多带来的数据复制压力，同时把级联从库作为一级从库的一个物理备份（从库宕机热备可快速补充从库节点）。
2）级联从库用于数据备份、数据分析、企业内部等对数据实时性要求不是很高的业务应用。

2. MySQL主主复制

MySQL主主复制是级联复制的特殊形式，主主复制是A和B对等的双向复制结构。
下图为双向主主复制逻辑架构，此架构可以在Master1端或Master2端进行数据写入，或者在两端同时写入数据（需要进行特殊设置）。

双向主主复制逻辑图

2.1 MySQL主主复制介绍

MySQL主主复制也是使用mysql replication的复制方式，只不过复制的方向是双向的，因此，复制过程与主从复制大同小异，只是在配置参数上多了几个参数。

2.2 MySQL主主复制能够解决的企业问题

很多企业设计MySQL主主复制架构的可能原因是希望增加写并发的能力，但其实双主是否真的能够增加写并发，还有待实验检验，因为一个库写入时，另一个库也会同时写入（由复制进程写入），写入量较大时，从库的写压力也是不小的。
如果要切实增加大量用户的写并发请求，那么MySQL主主复制架构可能并不是最好的选择，以下有更优选择可供参考。
1）分库：例如原本一个数据库服务器里有www/bbs/blog等多个库，那么可以将每个产品库都做成一套集群，这样就可以提高写的压力了，这样的拆分简单有效，DBA或运维人员自己就可以实现，几乎不需要业务程序做任何改动。
2）分表：a）横拆——将最常使用的小字段放在一个表里，将不常使用的大字段放在单独的表里，等需要查询大字段表时，再通过连表进行查询；b）纵拆——根据表的记录进行拆分，例如，1000万条记录的一张表，可以根据key或uid将1_{100000条放在第一张表，1000001}2000000条放在第二张表，依此类推拆成10张表，然后前端根据key或uid进行调度决定去访问哪张表对应的数据。这里的横拆和纵拆需要开发人员的或者数据库研发人员的配合才能完成。
3）百度千台数据库集群拆分案例：当单表达到指定的记录数时，自动化扩容拆表，自动化收缩合表。
建议：即使配置了MySQL双主复制，最好也只单写，配置双主的目的并不是为了增加写并发，而是为了实现在主库宕机后角色能够快速切换提供服务。
正常主库宕机切换到从库，需要开启binlog功能，取消read-only参数，同时还需要更改Web用户授权等操作，而配置双主就不需要这些操作了，只要进行VIP的切换即可。

2.3 MySQL主主复制的企业级实现方案

1. 通过MySQL的参数配置使表主键自增的方案

即通过为MySQL的表主键ID字段增加auto_increment功能，实现主键的自动增长（默认步长为1），ID的增长具体如下。

M1库：每张表的主键都为奇数，例如：1、3、5……。
M2库：每张表的主键都为偶数，例如：2、4、6……。
解决主键自增长ID冲突，M1库与M2库的参数配置如下。
Master1的参数配置如下。
auto_increment_increment = 2 ---自增ID的间隔，如1 3 5，间隔为2
auto_increment_offset = 1 ---ID的初始位置
（所有库里表的ID都将形成1、3、5、7...序列）
Master2的参数配置如下、
auto_increment_increment = 2 ---自增ID的间隔，如2 4 6，间隔为2
auto_increment_offset = 2 ---ID的初始位置
（所有库里表的ID都将形成2、4、6、8...序列）

该方法的优缺点：
缺点：该方法会导致表的ID号不连续。例如，写数据时ID的特征为：当M1中的数据写入ID为1、3、5，此时如果数据写入的连续ID为6、8、10，此时M1开始写，则会从11开始写。结论：总是以前表里最大的ID值为基础递增开始写ID。
优点：前端网络程序不需要做任何修改，就可以实现双主的架构。
应用：尽量不要双写，而是单写，其作用可以作为主库宕机的备用切换选择（不用更改配置即可进行角色切换）。

2. 使用序列服务实现MySQL双主方案

使用序列（sequence）服务，由序列服务提供ID（就像银行的取号机器发号一样），当用户通过程序写数据库时，由序列服务分配ID，然后按照分发的ID号顺序调度到不同的数据库写入，以确保写入不同双主数据库的ID是不同的，并确保ID是值连接的。
优点：表记录的ID号是连续的。
缺点：增加了序列服务或服务器，引入了单点，同时，程序若要改动，则实现会比较复杂。
总的建议，尽可能不要用双写模式。

2.4 主主复制实践（自增ID）准备

1. 主主复制数据库实战环境准备

MySQL主主复制实践对环境的要求比较简单，与主从复制环境一致即可。

2. 定义主主复制需要的服务器角色

主库及从库IP、端口信息

3. 检查数据库的当前状态

分别执行命令检查主主数据库的启动状态：

[root@db01 ~]# lsof -i :3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  18830 mysql   10u  IPv6  78945      0t0  TCP *:mysql (LISTEN)
[root@db02 ~]# lsof -i :3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  18807 mysql   10u  IPv6  78880      0t0  TCP *:mysql (LISTEN)

2.5 在主库Master（132）上执行操作配置

1. 设置主主复制的相关参数

1）修改主库1的配置文件。执行vi /etc/my.cnf，编辑MySQL的配置文件，两个参数按如下内容进行修改：

[mysqld]
server_id = 1
log_bin
log_slave_updates    ---从库记录binlog的必备参数
expire_logs_days = 7    ---自动清除7天前的binlog日志文件
auto_increment_increment  = 2    ---自增ID的间隔，如 1 3 5，间隔为2
auto_increment_offset  = 1    ---ID的初始位置
slave-skip-errors = 1032,1062,1007,1008,1146,1049    ---忽略一些不影响业务的复制错误提示

2）检查参数配置之后的结果：

[root@db01 ~]# egrep "server_id|log|auto" /etc/my.cnf
server_id = 1
log_bin
log_slave_updates
expire_logs_days = 7
auto_increment_increment  = 2
auto_increment_offset  = 1
log-error = /application/mysql/logs/oldboy.err

3）重启主库1的MySQL服务：

[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

2. 在主库1上建立用于主主复制的帐号

登录主数据库1（192.168.150.132）,建立用于主库2复制的帐号及对应的权限：

mysql> grant replication slave on *.* to 'rep'@'192.168.150.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3. 备份导出主库1的数据

1）备份导出主库1的数据，也可以半夜在主库1上通过定时任务执行如下命令：

mysqldump -A -B -x --master-data=1 | gzip > /opt/bak1_$(date +%F).sql.gz    ---备份
scp -rp /opt/bak1_$(date +%F).sql.gz root@192.168.150.133:/opt/    ---复制到从库

2.6 在主库2Master（133）上执行操作配置

1. 设置主库2上复制的相关参数

1）修改主库2的配置文件。执行vi /etc/my.cnf，编辑MySQL的配置文件，两个参数按如下内容进行修改：

[mysqld]
server_id = 2
log_bin
log_slave_updates
expire_logs_days = 7
auto_increment_increment = 2
auto_increment_offset = 2
slave-skip-errors = 1032,1062,1007,1008,1146,1049

2）检查参数配置之后的结果：

[root@db02 ~]# egrep "server_id|log|auto" /etc/my.cnf
server_id = 2
log_bin
log_slave_updates
expire_logs_days = 7
auto_increment_increment = 2
auto_increment_offset = 2
log-error = /application/mysql/logs/oldboy.err

3）重启主库2的MySQL服务：

[root@db02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

2. 在需要做复制的主库2上导入全备做复制

提示：主库2上不需要再设置主库1用于复制的帐号了，因为备份恢复到数据库，主库2就有了帐号了，导入命令如下：

zcat /opt/bak1_$(date +%F).sql.gz | mysql
mysql << EOF
CHANGE MASTER TO
MASTER_HOST='192.168.150.132',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123';
EOF
---这里忽略了MASTER_LOG_FILE和MASTER_LOG_POS，这是因为在备份的时候使用了“--master-data=1”，在导入数据库时，这两个参数已经自动设置好了

3. 启动从库同步开关并测试主主复制

1）启动从库主主复制开关，并查看复制状态：

[root@db02 opt]# mysql -e "start slave;"
[root@db02 opt]# mysql -e "show slave status\G"
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.132
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db01-bin.000001
          Read_Master_Log_Pos: 418
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 580
        Relay_Master_Log_File: db01-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
---省略若干---

主主复制是否配置成功了，最关键的是如下3项参数：

[root@db02 opt]# mysql -e "show slave status\G" | egrep "IO_Running|Slave_SQL_Running:|_Behind_Master"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

注意：在首次开启复制的过程中，如果出现复制错误，可以执行如下语句：

stop slave;
set global sql_slave_skip_counter = 1;    ---将同步指针向下移动一个，如果多次移动仍不同步，则可以重复操作
start slave;

最后记录下主库2的binlog状态，用于在主库1上进行复制的binlog复制的位置点：

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| db02-bin.000001 |   647320 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.7 在主库1（132）上执行复制配置

[root@db01 ~]# mysql << EOF
CHANGE MASTER TO
MASTER_HOST='192.168.150.133',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE='db02-bin.000001',
MASTER_LOG_POS=647320;
EOF
[root@db01 ~]# mysql -e "start slave;"
[root@db01 ~]# mysql -e "start slave status\G"
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.150.133
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db02-bin.000001
          Read_Master_Log_Pos: 647320
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 282
        Relay_Master_Log_File: db02-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
---省略若干---

至此，主库1和主库2就实现相互同步了。

2.8 在主库1和主库2进行测试

1）在主库1上建库建表：

mysql> create database two_master;
Query OK, 1 row affected (0.00 sec)
mysql> use two_master;
Database changed
mysql> CREATE TABLE test(id bigint(12) NOT NULL auto_increment COMMENT '主键',name varchar(12) NOT NULL COMMENT '姓名',PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)

2）然后插入数据：

mysql> insert into test(name) values('jeacen'),('alex'),('老男孩');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | jeacen    |
|  3 | alex      |
|  5 | 老男孩    |
+----+-----------+
3 rows in set (0.00 sec)
---ID都是奇数

3）此时登录主库2（133）查看数据：

mysql> use two_master;
Database changed
mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | jeacen    |
|  3 | alex      |
|  5 | 老男孩    |
+----+-----------+
3 rows in set (0.00 sec)
---ID都是奇数

4）登录主库2（133）继续插入数据：

mysql> insert into test(name) values('卧底'),('wu_sir'),('小曹');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | jeacen    |
|  3 | alex      |
|  5 | 老男孩    |
|  6 | 卧底      |
|  8 | wu_sir    |
| 10 | 小曹      |
+----+-----------+
6 rows in set (0.00 sec)

新插入的数据是从当前表中最大数字偶数6的基础上开始进行插入的，至此，双主复制测试成功。

2019-06-20 MySQL复制高级方案应用实践
1. MySQL级联复制 1.1 MySQL级联复制介绍 MySQL级联复制的特点是从（slave）服务器本身除了...
2019-06-12 MySQL主从复制高级方案与应用实践
1. MySQL集群企业级架构方案在实际生产环境中，一主多从的数据库环境是最常采用的数据库架构，那么应该怎么分配...
MySQL 同步复制及高可用方案总结
MySQL 同步复制及高可用方案总结 1.前言 mysql作为应用程序的数据存储服务，要实现mysql数据库的高可...
常见的高可用MySQL解决方案【转】
1. 主从复制解决方案主从复制解决方案是MySQL自身提供的一种高可用解决方案，数据同步方法采用的是MySQL复制...
MySQL-lesson10-MHA高可用技术
MySQL-lesson10-MHA高可用技术 1. 主从复制架构演变介绍 1.1 基本结构 1.2 高级应用架构...
Mysql （7）
Mysql 的主从复制 1 复制概述Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分...
叙事高级应用实践-集体叙事实践（叙事专业工作者高级）
叙事高级应用实践工作坊--集体叙事实践 2017-8-7 9:28 叙事高级应用实践工作坊--集体叙事实践培训目...
MySQL复制功能
mysql复制功能 MySQL 内建的复制功能是构建大型，高性能应用程序的基础。将 MySQL 的数据分布到到多...
mysql主从同步
mysql中为了读写分离，高可用时，都会用到mysql的主从同步方案。 MySQL的主从复制是一个异步的复制过程（...
2019-10-12
利用LVS+Keepalived搭建Mysql双主复制高可用负载均衡环境应用背景： MySQL复制（主主，主从....