记一次生产环境MySQL数据库的备份与还原

一、背景

公司的生产环境某些应用的数据库是部署在VMWare平台上的，为了节约成本，领导现考虑将全部数据迁移至居于KVM的CAS平台。之前做的是V2V迁移，但是效率不高，综合了一下时间成本及其他因素，最终选择采用备份与还原的方式进行迁移。源库中有GeekDevOps_gsms、GeekDevOps_list、GeekDevOps_ams等三个属于用户gsms的库。

二、环境

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)