mysql dump 导出表_MySQL数据库升级迁移填坑记

原库：*.*.101.73/74    

   系统环境: Suse 12.4

   MySQL: 5.7.29

新库：*.*.110.46/47

   系统环境：CentOS7.7 64位

   MySQL版本: 5.7.30

[一、数据库升级迁移场景]

因业务侧在*.*.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 status\G;

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 status\G;  

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)，关注关键性的参数配置。

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

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