A backup and restore of MySQL database in production environment

Background

The company's production environment some application databases are deployed on the VMWare platform. In order to save costs, leaders are now considering migrating all data to the CAS platform living in KVM. The V2V migration was done before, but the efficiency is not high. It combines the time cost and other factors, and finally chooses the backup and restore method for migration. In the source database, there are three databases belonging to the user's gsms, including geekdevops ﹣ gsms, geekdevops ﹣ list, and geekdevops ﹣ AMS.

Two, environment

2.1 backup host environment
[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 restore host environment
[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, Backup process

3.1 stop database service and kill related processes.

[root@GeekDevOps-DB1 ~]# systemctl stop mysqld
[root@GeekDevOps-DB1 ~]# ps -ef |grep mysql

3.2 backup.

[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, Restore process

4.1 create the same library as the source library.

[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 authorize gsms to users for the created database.

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 restore the database.

[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

5, Check data integrity

[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)

Tags: MySQL Database CentOS SQL

Posted on Sat, 04 Apr 2020 16:31:01 -0700 by bob_marely