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

一、背景

公司的生产环境某些应用的数据库是部署在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)