记录一次线上Mysql数据库迁移方案制定与实施

前言

系统运行有很多年了。
数据库服务器也运行很多年了，性能比较老旧，SSD硬盘容量只有250G，在各种需求下，当前服务器的磁盘即将不足。所以开启了本次数据库的迁移行动。

需求

1. 迁移的数据量高达130多G, 保证数百万用户的数据安全。
2. 不能影响线上正常运行或将影响降到最低
3. 保证迁移前后的数据安全与精确还原。

思路

1 .先备份数据
2. 以现有数据库为主数据库搭建从数据库（从主复制）。
3. 检查从数据库数据是否与主数据库完全同步一致
4. 关闭网站，将从数据库切换为主数据库，恢复网站。

实施

为了保险起见，在技术研究过程中，所有的操作先在测试服上跑一遍。

一、备份

第一步就是备份数据，毋庸置疑。涉及到数据库的操作犹如刀尖上的舞者，如果不备份，那么就是成则为仁，败则跑路，多年的数据积累毁于一旦。
如果要备份导出数据库，又不能影响线上数据读写，怎么办？这个时候只能进行热备份。

如何进行热备份？

mysqldump是mysql自带的备份还原工具，它包含很多参数。具体参数了解请转到：
mysqldump参数详解 如果链接失效，自己也可以在网上查一下。
通过使用以下两个参数来进行：
–master-data
–single-transaction

master-data主要使用在主从数据库的master端，该选项将二进制的位置和文件名追加到输出文件中，它会导致全局锁表，使整个数据库不可访问，然后将数据导出来，这显然不满足线上读写的要求，所以master-data一般需要和single-transaction一起使用，single-transaction可以单独开启一个事务，将事务进行隔离，从而互不影响。

但是有个注意事项：
single-transaction只能在InnoDB引擎上生效。
mysql又MYSIAM和InnoDB两种引擎，如果该表或者库不是采用InnoDB引擎，则在导出该表时无法进行事务隔离。

所以当使用mysqldump采用–master-data和–single-transaction两个参数进行热备份时，前提是要开启binlog二进制日志，让mysql的每个操作都写在日志上，然后–single-transaction会进行短暂的全局锁表，把记录mysql当前操作的二进制日志的偏移位置记录下来，然后开启单独的事务进行导出。

有了以上的备份思路，首先将要迁移的数据库开启binlog二进制日志。

1. 开启binlog二进制

vi	/etc/my.cnf

设置唯一id
server-id=82
开启二进制
log-bin=mysql-bin
log-bin-index=mysql-bin.index
// 针对的数据库
binlog_do_db=my_database
// 设置单个数据包容量和重做日志大小，根据自己的实际情况设置，尽量大点，这两个参数影响mysqldump能否成功
max_allowed_packet=1024M
innodb_log_file_size = 256M

配好了就重启mysql

service mysqld restart

验证是否开启

show variables like '%log_bin%';

		log_bin                                 | ON  

show master logs;

		mysql-bin.000001 |      120

2. mysqldump备份

我这里使用的命令是：

mysqldump -uMyUser -pMyPassword --hex-blob --force --flush-privileges --max_allowed_packet=1024M 
--master-data=2 --single-transaction  my_database | gzip > /home/uptry`date +%Y-%m-%d_%H%M%S`.sql.gz

这里做个参数的简单解释：

 mysqldump -uMyUser -pMyPassword    这一部分是mysqldump命令和登录mysql的账号密码
 --hex-blob   因为我们数据库有BLOB数据，所以要用十六进制转换
 --force --flush-privileges 是基本配置
 --max_allowed_packet  这个值取决于单个实体数据量大不大，因为我们有BLOB数据，默认的max_allowed_packet不够传输某些实体数据，所以设置大一点，避免造成传输中断，从而备份失败。
 --master-data=2   用于记录开始备份时的二进制日志位置，以日志方式存于该备份文件开头。（该操作将会导致锁表操作）
 --single-transaction  让备份操作以单独事务运行，保证数据导出时的一致性。这是进行热备份的重要操作。（如果不开启，将会执行master-data锁表操作造成整个数据库阻塞。）
 
 后面的gzip是压缩备份命令。可以节约空间。

我这里操作进行了约2个小时， 时间比较久。如果时间比较久，最好开启linux异步执行，以免当前会话中断。

二、mysql还原

数据备份完成后，被压缩成了20多个G，通过传输工具Multcloud将sql压缩包传输到从服务器后，开启了还原。

解压sql二进制日志包，通过命令可以查看到日志的前50行内有一句：

	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000416', MASTER_LOG_POS=116911744;

这个就是告诉我们，该日志保存的是master端文件mysql-bin.000416偏移116911744个字节前的数据。我们到时候做主从同步，只需要从这个位置往后同步就行了。
然后
还原之前先在从服务器上安装好mysql，创建一个和主数据库一样字符类型的库。
然后使用命令：

mysql -uMyUser -pMyPassword  my_database < uptry2099-09-19_030001.sql 

这个时候就开始还原了。
等待还原结束后，开始搭建master-slave主从同步。

三、 主从搭建

主从搭建还需要配置一下从服务器。
从服务器不需要配置binlog，但是要进行还原配置relay-log。

// 从服务器的唯一id
server-id=91
// 开启relay-log中继日志
relay-log=relay-log
relay-log-index=relay-log.index

binlog是记录mysql的写操作，而relay-log是master-slave之间连接关键的中继日志。
具体原理不在这里赘述。

重启生效

service mysqld restart

授权slave权限账户：

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO  ‘slave账号’@‘slave的ip’ IDENTIFIED BY ‘slave密码’;

连接主从数据库：

change master to master_host='10.248.33.118', master_user='slave账号',
master_password='slave密码', master_log_file='mysql-bin.000416',
master_log_pos=116911744;

让从数据库通过这个账户拉取master端二进制日志，从mysql-bin.000416偏移116911744开始。

查看从数据库状态：

show slave status \G

Slave_IO_Running和Slave_SQL_Running都是Yes才为成功。

聊聊这小节的问题

1. Slave_IO_Running是No，但Slave_SQL_Running是Yes。
   这多半是change命令有问题，多半是账号密码错误，或者找不到对应的binlog日志。我之前两种情况都遇到过，至于找不到binlog日志，是因为备份还原的消耗时间太久，导致记录的binlog已经过期了。可以通过在master端设置expire_logs_days长一点的时间，这样可以保留时间长一点，但也要注意服务器硬盘空间是否足够。
2. slave同步出现异常，提示出现重复的键或者id或者key。
   这一情况，可能是由于master端的数据库或者表不是InnoDB引擎，导致热备份时single-transaction事务失效，将当前正在读写的数据也记录到了binlog里面，所以，导出的备份sql里面已经有这个数据了，同步时又拿到了这个数据，导致数据重复。解决方法就是重新备份之前，将该表的引擎修改为InnoDB，注意：修改表的引擎时将会导致锁住该表，出现阻塞。

四、数据检查

如果仅仅是搭建完成主从同步就以为两边的数据已经保持一致了， 这是很危险的思维。
甚至可能导致数据丢失再也找不回来。
根据主从同步原理，可以理解为在备份二进制日志上的增量实时同步，或者从数据库删除了数据，或者在恢复时跳过了错误数据，导致主从并不一致，都是有可能的。
唯一保险的方法就是进行数据检查。

pt-table-checksum
也是mysql的工具之一，mysql真的几乎把所有情况都涉及到了，很成熟的数据库。
通过该工具可以实现主从之间的数据库一致性检验。

检验结果将会保存在一个新的表里面。然后通过pt-table-sync工具将该表的数据转化，来修复主从间的不一致。
使用pt-table-checksum的注意事项就是，主从数据库要配置一套同样的账号密码，才能通过pt-table-checksum进行检查。

pt-table-checksum --nocheck-replication-filters --recursion-method=hosts  
--replicate=mc_test.checksums --databases=mc_test --tables=userinfo 
h=127.0.0.1,u=root,p=MyPassword,P=3306

然后会在路径mc_test.checksums下自动创建checksums表，将异常数据写进去。

pt-table-sync
该工具可以将pt-table-checksum检查出来不一致的数据，进行修复。让主从数据保持一致。
使用pt-table-sync的注意事项：

1. 主从数据库要配置一套同样的账号密码，才能通过pt-table-sync进行恢复。
2. 该操作会锁表

命令

pt-table-sync --replicate=mc_test.checksums --no-check-child-tables --no-foreign-key-checks 
--databases=mc_test --tables=userinfo h=127.0.0.1,u=root,p=MyPassword,P=3306 --execute

五、 主从切换

前面一切铺垫好后
现在要做的，就是将数据源切换为新数据库。

1. 在新数据库上创建旧有数据库的账号密码，创建应用集群白名单访问权限，并进行连接测试。
2. 修改各个应用程序的数据库IP访问地址
3. 网站首页切换为网站维护通告
4. 配置从库的binlog日志，重启生效。
5. 开始切换操作

主从切换主要防止数据丢失。
首先停止master端写入

该命令锁住范围较大，根据自己业务情况使用。
FLUSH TABLES WITH READ LOCK

其次等待slave端IO和SQL线程都已完成等待最新状态同步状态。这一步很重要！
然后停止salve。

stop slave;

重置从数据库的master

reset master;

重置从数据库的slave

reset all slaves;

查看从数据库master当前binlog位置：

show master status;

授权新账号给主数据库

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO  ‘master账号’@‘master的ip’ IDENTIFIED BY ‘master密码’;

主数据库进行操作，
连接从数据库

change master to master_host='10.248.33.118', master_user='master账号',
master_password='master密码', master_log_file='mysql-bin.000001',
master_log_pos=120;

开启slave同步

	start slave;

查看主数据库同步状态：

show slave status \G

切换成功后，
此时原来的主数据库变成了新的从数据库。
原来的从数据库变成了新的主数据库。

六、解除

启动应用程序连接，解除网站维护通告。