记一次生产环境MySQL数据库的备份与还原-阿里云开发者社区

开发者社区> 数据库> 正文

记一次生产环境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)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章