云迁移--mysql双主同步优化加实战mysql数据库迁移

     本部分内容在上一篇《vsphere集群应用部署之--搭建mysql-5.7高可用主主-双从-HA》的基础上进行了大量改进，启用了新功能并实战将一个业务数据库导入到我们新建的mysql双主集群，实现mysql数据库数据迁移。

一、主要流程介绍

1、mysql数据库存储位置更改（通过mysql配置文件实现）

2、开启mysql-Gtid复制模式，实现无主键冲突风险的复制并且配置主从复制时不需要查找position和binlog 了

3、采用Gtid复制后需重点关注的位置

4、为keepalived服务开启firewalld，增强系统网络安全

5、使用mysqldump导出业务系统指定数据库数据

6、使用mysql导入上一步导出的备份数据库文件，实现业务数据库数据迁移

二、mysql存储位置更改

上一篇采用软连接方式更改在实际测试中发生了问题，发现导入的数据依然存储在/var/lib/mysql目录下，这里采用更加彻底的方法修改mysql配置文件实现

1、将mysql源数据库目录同步到新挂载的位置

使用rsync可以将原目录权限等信息同步到新的目录中

rsync -av /var/lib/mysql /home/data

同步完成后将原目录改名，确认新的目标位置存储成功后可以删除掉原目录下的所有内容
mv /var/lib/mysql /var/lib/mysql.bak

2、更改mysql配置文件

[mysqld]

datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock

[client]
port=3306
socket=/home/data/mysql/mysql.sock

3、更改完成后重启mysql数据库，进入数据库后执行select @@datadir;查看现在的数据目录位置

三、开启mysql的Gtid复制模式

1、Gtid简介：

    全局事务标识符的基于事务的复制 （GTIDs）。使用GTID时，可以在原始服务器上提交并由任何从属应用时识别和跟踪每个事务; 这意味着在启动新从站或故障转移到新主站时使用GTID来引用日志文件或这些文件中的位置是不必要的，这极大地简化了这些任务。由于基于GTID的复制完全基于事务，因此很容易确定主服务器和从服务器是否一致; 只要在主服务器上提交的所有事务也在从服务器上提交，两者之间的一致性就得到保证。

gtid_executed，已经执行的gtid集合（gtid-sets）。该变量在数据库工作期间一直变化。

gtid_purged，已经清除的gtid集合。

gtid_purged与gtid_executed都是通过遍历binlog文件来计算的。

当gtid_executed 非空的时候，不能设置gtid_purged， 当gtid_executed 为空的时候（即刚刚备份好的镜像，刚搭建的mysql），可以直接SET @@GLOBAL.GTID_PURGED='server-uuid:trans-id';

2、修改/etc/my.cnf

如果您的环境中没有配置主从复制，还需要在下面两个修改项基础上新增log_bin=mysql-bin。

gtid_mode=on
enforce_gtid_consistency=on

主库与从库的配置一样，只要从库的uuid与server-id不要和主库配置一样就可以了。 

3、修改完后重启mysql服务

show variables like '%gtid%';

查看gtid运行状态，发现下图中红框里的内容已经变成了ON状态，如果前面没有修改这里默认是OFF状态

4、停掉slave复制，重新修改为GTID方式的复制；

stop slave;

CHANGE MASTER TO  MASTER_HOST = host,  MASTER_PORT = port,  MASTER_USER = user,  MASTER_PASSWORD = password,  MASTER_AUTO_POSITION = 1; 

注意上面对应的字段需要填入与你实际环境相关的内容，然后在启动复制线程。start slave；

5、如何查看gtid的值

MySQL5.7版本之后，gtid_executed这个值持久化了。在MySQL库下新增了一张表gtid_executed

首先通过查看mysql数据目录下的auto.cnf文件确认主库与从库的uuid

上图是master1的uuid，下图是master2的uuid 

在master1上执行show master status\G查看gtid的使用情况，可以清楚的看到，从备库发到主库的同步数据只有7条，这是我测试主备库倒换时测试的几条命令，其他完全是从主库master1上同步到master2上的近60万条数据。 

也可以直接通过查询mysql下的gtid_executed表查看gtid同步情况，与上面执行show master status\G效果一样 

四、开启系统firewalld服务并配置高级策略运行vrrp组播

前两条是允许组播报文，这两条是为了keepalived服务的vrrp协议开启的，必须打开，第三条是运行那些目标地址访问你的mysql服务，这里可以定义多个，我只给了一条示例数据

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0   --in-interface ens192 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0  --out-interface ens192 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
firewall-cmd --permanent --zone=public --add-rich-rule="rule family="ipv4" source address="10.37.0.0/16" service name="mysql" accept"

添加完毕后必须要重新加载一遍，否则配置不生效。
firewall-cmd --reload

五、使用mysqldump导出业务系统指定数据库到本地文件

1、在master1数据库主机上网络可达原业务数据库主机

2、在master1主机上执行mysqldump导出命令

源数据库用户wangli，密码123456，使用的监听端口为3300，业务数据库名为myspring

3、mysqldump -e 使用包括几个VALUES列表的多行INSERT语法; --max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小; --net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行

注意：max_allowed_packet和net_buffer_length不能比目标数据库的配置数值大，否则可能出错。

mysqldump -u wangli -h 133.3.5.70 -P 3300 -p123456 --databases myspring  -e --max_allowed_packet=1048576 --net_buffer_length=16384> myspring.sql

这里需要注意的一点是，mysql5.6默认的数据是大小写不敏感而mysql5.7默认大小写敏感，这意味着原来的数据库表不论大小写在5.6数据库中都可以访问，但是原来的程序是严格区分大小写的，将原来不区分大小写的数据库导出后数据表基本全部是小写字母，在5.7中极有可能程序访问出错。

比如源数据库中数据表userinfo，程序通过spring标准化映射后实际访问的表名是userInfo，在5.6数据库中自动把程序的请求全部转成小写userinfo，但是把这个表导入到5.7数据库中，程序按照userInfo去访问userinfo根本找不到对应的数据库表。

有两种解决方案，一种是修改mysql配置文件，将5.7的数据库变成大小写不敏感。

lower_case_table_names=1 （1不区分大小写，0区分大小写，默认为0）

还有一种是我采用的方法，手工将表名改成实际程序请求的表名，因为现在的表名基本都是大小写混合的，大小写不敏感不是今后的趋势，表格更改方法如下：

ALTER TABLE table_name RENAME TO new_table_name; 

举例如下：

alter table userinfo rename to userInfo;

六、将业务数据库导出myspring.sql导入到主master1数据库中，完成数据库数据迁移

1、导入数据前优化操作步骤

MySQL中运行一个大文件的导入。我不希望它写入二进制日志，因为导入将花费很长时间，并且会非常占用数据库存储空间，一般是实际导入数据库的3倍空间占用。

您可以使用sql-log-bin会话变量来关闭当前会话的日志记录。只需输入：

SET sql_log_bin =0

并且当前会话中的所有查询都不会发送到二进制日志。如果要重新打开二进制日志记录，请运行：

SET sql_log_bin = 1

这仅适用于您当前正在运行的会话。来自所有其他连接的所有其他查询仍将继续记录。此外，您必须具有SUPER权限才能使用此功能。

通过将该命令与原来已经导出的sql文件进行合并，关键把关闭日志命令合并到新生成文件的第一条，保证首先执行，具体合并命令如下：

(echo "SET SESSION SQL_LOG_BIN=0;"; cat myspring.sql) > myspring_nobinlog.sql

2、执行导入数据命令

 mysql -u root -p < myspring_nobinlog.sql 

3、加快导入速度之优化二

使用max_allowed_packet属性

客户端/服务器之间通信的缓存区的最大大小,适量调大可以加快mysql导入数据的速度。

查看当前大小：

show VARIABLES like '%max_allowed_packet%';

七、GTID复制与传统模式主从复制故障处理对比

• 传统模式

跳过出错事物 SQL>STOP SLAVE; SQL> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SQL> START SLAVE;

• GTID模式

必须要查询出gtid的事物id SQL>STOP SLAVE; SQL> SET GTID_NEXT='b9b4712a-df64-11e3-b391-60672090eb04:7'; --设置需要跳过的gtid event SQL> BEGIN;COMMIT; SQL> SET GTID_NEXT='AUTOMATIC'; SQL> START SLAVE;

    对于新建数据库启用主从复制,不论是传统模式还是GTID模式在不考虑数据丢失的情况下都非常简单，实在不行可以使用

reset slave或者reset master复位前面的配置。