MySQL数据库升级迁移填坑记

因业务侧在*.*.101.73/74 mysql数据库服务器上部署了java应用程序、Hadoop+Hbase数据库等大数据环境，导致主机内存突然暴增告急，经双方排查，发现数据库进程本身才占用内存8.5%，大部分都是由应用缓存占用了内存。经与局方及业务侧沟通，局方敦促业务侧将数据库服务器从73/74服务器迁移到*.*.110.46/47服务器上，我方负责实施数据库的迁移操作。

本次迁移使用的MySQL自带的备份工具mysqldump从原库双主（*.*.101.73/74）导出数据，通过nfs共享文件系统上传到资源池新库双主（*.*.110.46/47）。

在资源池新库分别将73、74数据库的备份文件导入 46、47新库，并启动双主复制进程：

mysql> change master to master_host=*.*.110.46,master_user=repl,master_password=xxxxxx,master_port=3306,master_auto_position=1;

结果报错如下：

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

从报错信息来看，起初以为是执行复制的语句重复制账号信息有误，然后核对了repl账号的口令是正确的，并查看了复制账号repl的权限信息：

mysql>show grants for ‘repl’@’*.*.110.%’;

结果显示没有repl用户的权限信息记录。接着查看系统表user中数据信息，竟然没有导入数据前创建的repl用户记录，哦，奇怪。

突然想到，由于我们备份的是原库中所有表（--all-databases），导出的dump文件中包含有重新创建表结构的语句，所以马上在资源池双主库新建复制账号repl：

grant  replication  slave  on *.* to  repl@*.*.110.%  identified by  xxxxxx;

flush privileges;

然后重新执行复制语句并开启复制进程依然报刚才的错。然后就想到此次迁移是从Suse 12.4  MySQL-5.7.29 迁移到CentOS7.7 MySQL-5.7.30, 以为是版本不兼容。

接着将资源池46/47的MySQL版本降为 mysql 5.7.29。分别重新导入数据到新库46/47上，导入数据库的过程中46服务器导入正常，而发现47库上通过source导入时非常的慢，每条执行返回10-30秒，当时没有查具体原因，有可能是网络卡顿吧。

最后查看原库74/74的数据库配置文件，返现没有开启GTID全局复制方式（说明，目前这边项目MySQL数据库几乎都使用的基于GTID全局事务复制协议做的同步），而我执行的复制语句中有“master_auto_position=1”，原来新库上执行的复制机制跟原库不一致，这就是刚才开启复制进程报错的根本原因。

通过以上分析，我们得知，既然原库使用的是binlog和pos做的同步，那么我们新库也同样按照这个方式来配置复制。其次由于刚才使用mysql内置工具导入数据时很缓慢，所以我们准备采用percona提供的xtrabackup 工具来做数据备份和恢复。

4.1、首先检查新旧库上是否有创建备份账号，结果现实没有新新建

  create user bkuser@localhost identified by xxxxxx;

  grant reload,lock tables,replication client,process on *.* to bkuser@localhost;

  flush privileges;

4.2、原库上使用xtrabackup备份双主数据

分别在原库73/74上使用xtrabackup做全量备份。

73服务器上：

innobackupex --defaults-file=/home/mysql/my_cnf/my.cnf --host=*.*.101.73 --user=bkuser --password=xxxxxx --port=3306 --socket=/app/gzyd/data/mysql/tmp/mysql.sock --no-timestamp /mysqlbackup/73_xtra_base_20200623

74服务器上：

innobackupex --defaults-file=/home/mysql/my_cnf/my.cnf --host=*.*.101.74 --user=bkuser --password=xxxxxx --port=3306 --socket=/app/gzyd/data/mysql/tmp/mysql.sock --no-timestamp /mysqlbackup/74_xtra_base_20200623

4.3、新库上恢复双主数据

1）导入数据前记录binlog文件及同步位置（master_log_pos和master_log_file）

# 46/47库上执行

mysql> flush table with read lock;

mysql> show master status;

注：记得记录下master状态信息，后面执行复制的时候要用到。

mysql> unlock table;

4.4、全量恢复

分别在原库73/74上使用xtrabackup做全量恢复

1）在46库上执行恢复操作

innobackupex --defaults-file=/home/mysql/my_cnf/my.cnf  --use-memory=2G --apply-log  /mysqlbackup/73_xtra_base_20200623

mysqladmin  --login-path=myconn shutdown immediate

mv /data/mysql/data /data/mysql/data-bak20200624

mkdir /data/mysql/data

innobackupex --defaults-file=/home/mysql/my_cnf/my.cnf  --copy-back /mysqlbackup/73_xtra_base_20200623

chown -R mysql.mysql   /data/mysql/data   

mysqld_safe  --defaults-file=/home/mysql/my_cnf/my.cnf  &

2）在47库上恢复操作同上

4.5、新库上配置双主复制

1）在46/47服务器上新建复制账号

注：由于在原库导出的是所有库，备份文件中含有重新创建表结构的语句，所以在新库恢复数据后需要重新创建复制账号：

grant  replication  slave  on *.* to  repl@*.*.110.%  identified by  xxxxxx;

flush privileges;

2）配置46->47方向主从

 登录47服务器，执行复制语句：

stop slave; 

change master to master_host=*.*.110.46,master_user=repl,master_password=xxxxxx,master_port=3306,master_log_file=bin.000001,master_log_pos=448;

start slave;

show slave statusG;

3）配置47->46方向主从

 登录46服务器，执行复制语句：

stop slave;

change master to master_host=*.*.110.47,master_user=repl,master_password=repQAv2wsx@gzydxk,master_port=3306,master_log_file=bin.000001,master_log_pos=1066;

start slave;

show slave statusG;  

4.6、新库双主测试

1）主主库46上试着写入测试数据

mysql> create database chg;

mysql> use chg;

mysql> create table t1(id int, name varchar(30));

mysql> insert into t1(id,name) values(1,zhangsan);

mysql> insert into t1(id,name) values(2,lisi);

然后到重复47上查看新插入的两条数据是否同步过来：

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| chg                |

| mysql              |

| performance_schema |

| smzrz              |

| sys                |

+--------------------+

6 rows in set (0.00 sec)

mysql> use chg;

mysql> show tables;

+---------------+

| Tables_in_chg |

+---------------+

| t1            |

+---------------+

1 row in set (0.00 sec)

mysql> select  * from t1;

+------+----------+

| id   | name     |

+------+----------+

|    1 | zhangsan |

|    2 | lisi     |

+------+----------+

2 rows in set (0.00 sec)

2）主主库46上试着写入测试数据

mysql> create database chg2;

mysql> use chg2;

mysql> create table t2(id int,name varchar(20));

mysql> insert into t2(id,name) values(1,derek);

mysql> insert into t2(id,name) values(2,john);

然后到重复47上查看新插入的两条数据是否同步过来：

mysql> use chg2;

mysql> show tables;

+----------------+

| Tables_in_chg2 |

+----------------+

| t2             |

+----------------+

1 row in set (0.00 sec)

mysql> select * from t2;

+------+-------+

| id   | name  |

+------+-------+

|    1 | derek |

|    2 | john  |

+------+-------+

MySQL数据库类似的升级迁移操作注意事项：

①升级迁移操作前仔细检查当前数据库配置文件(my,cnf)，关注关键性的参数配置。

②自此检查数据库的架构，如：具体使用哪种复制模式等。

③升级迁移变更前做好充分的数据测试。