一、背景
公司的生产环境某些应用的数据库是部署在VMWare平台上的，为了节约成本，领导现考虑将全部数据迁移至居于KVM的CAS平台。之前做的是V2V迁移，但是效率不高，综合了一下时间成本及其他因素，最终选择采用备份与还原的方式进行迁移。源库中有GeekDevOps_gsms、GeekDevOps_list、GeekDevOps_ams等三个属于用户gsms的库。
MySQL
二、环境
2.1备份主机环境
[root@GeekDevOps-DB1 ~]# hostnamectl
Static hostname: GeekDevOps-DB1
Icon name: computer-vm
Chassis: vm
Machine ID: 18bc5992f24f495b84d8c231d63207ee
Boot ID: f8f49625ca5748b8a1cdb9d64d6a547e
Virtualization: vmware
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-327.el7.x86_64
Architecture: x86-64
2.2还原主机环境
[root@GeekDevOps-DB2 ~]# hostnamectl
Static hostname: GeekDevOps-DB2
Icon name: computer-vm
Chassis: vm
Machine ID: a4dbec2c1e1a496290d8f982bb758597
Boot ID: 79abbf46968c475fabb1757b08c214aa
Virtualization: kvm
Operating System: CentOS Linux 7 (Core)
CPE OS Name: cpe:/o:centos:centos:7
Kernel: Linux 3.10.0-327.el7.x86_64
Architecture: x86-64
三、备份过程
3.1停止数据库服务，杀死相关进程。
[root@GeekDevOps-DB1 ~]# systemctl stop mysqld
[root@GeekDevOps-DB1 ~]# ps -ef |grep mysql
3.2备份。
[root@GeekDevOps-DB1 ~]# mysqldump -uroot -p GeekDevOps_gsms>GeekDevOps_gsms_bak_20180323.sql
[root@GeekDevOps-DB1 ~]# mysqldump -uroot -p GeekDevOps_list>GeekDevOps_list_bak_20180323.sql
[root@GeekDevOps-DB1 ~]# mysqldump -uroot -p GeekDevOps_ams>GeekDevOps_ams_bak_20180323.sql
四、还原过程
4.1创建与源库相同的库。
[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./
mysql> CREATE DATABASE `GeekDevOps_gsms` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE `GeekDevOps_list` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE `GeekDevOps_ams` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
4.2为创建好的数据库授权给用户gsms。
mysql> grant all privileges on GeekDevOps_gsms.* to 'gsms'@'%' identified by "GeekDevOps,./";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on GeekDevOps_list.* to 'gsms'@'%' identified by "GeekDevOps,./";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on GeekDevOps_ams.* to 'gsms'@'%' identified by "GeekDevOps,./";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to gsms@localhost identified by 'GeekDevOps,./' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
4.3还原数据库。
[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./ GeekDevOps_gsms<./GeekDevOps_gsms_bak_20180323.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./ GeekDevOps_list<./GeekDevOps_list_bak_20180323.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@GeekDevOps-DB2 ~]# mysql -ugsms -pGeekDevOps,./ GeekDevOps_ams<./GeekDevOps_ams1_bak_20180323.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use GeekDevOps_gsms;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> quit
Bye
五、检查数据完整性
[root@GeekDevOps-DB2 ~]# mysql -u root -p -A
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use GeekDevOps_gsms;
Database changed
mysql> show tables;
mysql> use GeekDevOps_list;
Database changed
mysql> show tables;
mysql> use GeekDevOps_ams;
Database changed
mysql> show tables;
mysql> select User,Db from Db;
+---------------+---------------------------+
| User | Db |
+---------------+---------------------------+
| gsms | GeekDevOps_ams |
| gsms | GeekDevOps_gsms |
| gsms | GeekDevOps_list |
| mysql.session | performance_schema |
| mysql.sys | sys |
+---------------+---------------------------+
5 rows in set (0.00 sec)