RDS MySQL物理备份文件恢复到自建数据库5.6版本实操
程序员文章站
2022-05-27 09:39:28
...
关于RDS MySQL物理备份恢复到本地自建库的官方介绍可以参详：阿里云RDS MySQL数据库恢复，本文是5.6版本的实操记录。
关于本文的背景说明：上周某项目上线数据上线操作失误覆盖了几个表的数据，想要用阿里云自有的备份恢复，然后就尴尬了，阿里云提供XtraBackup物理备份但不提供客户自主恢复功能，要恢复要走流程申请，官方来操作，然后我们等待官方用3小时来恢复简单的150多条数据，无力吐槽，只怪自己， 可长点心吧！
事后总结：
- 严控数据操作流程，严格按照checklist执行数据管理；
- 数据管理千万条，备份（mysqldump备份到本地或者拉取阿里云的xtrabackup物理备份到本地）永远是第一条！！！我想说这次事故我是做了备份的，但是……没有“但是”，没有解决问题的“但是”都是废话，o(╥﹏╥)o
- 详细了解平台服务功能，万万没想到的事情往往逃脱不了墨菲定律。
言归正传，RDS MySQL物理备份文件恢复到自建数据库5.6版本实操。
- 查看对应的阿里云MySQL数据库版本，本文为MySQL5.6.34。
这里顺带罗列一**意事项：
- 操作系统中已安装数据恢复工具Percona XtraBackup；
- 自建MySQL数据库安装在64位的Linux系统中，且与云数据库MySQL版的版本相同；
- MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3；
- MySQL 5.7版本需要安装 Percona XtraBackup 2.4；
- MySQL 8.0版本需要安装 Percona XtraBackup 8.0；
- 2019年2月20日后创建的MySQL 5.6实例，数据备份文件的格式为xbstream文件包（_qp.xb后缀）。
- yum安装MySQL数据库，本地暂无对应版本的数据库，重新安装。
[[email protected] ~]# rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpmRetrieving http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
[[email protected] ~]# yum repolist enabled | grep “mysql.-community.”
[[email protected] ~]# yum -y install mysql-community-server
[root@slave ~]# rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpmRetrieving http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-release-el7-5 ################################# [ 50%]
Cleaning up / removing...
2:mysql-community-release-el6-5 ################################# [100%]
[root@slave ~]# yum repolist enabled | grep "mysql.*-community.*"
Existing lock /var/run/yum.pid: another copy is running as pid 2417.
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 119 M RSS (461 MB VSZ)
Started: Fri Oct 16 08:16:32 2020 - 00:05 ago
State : Running, pid: 2417
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 109 M RSS (451 MB VSZ)
Started: Fri Oct 16 08:16:32 2020 - 00:07 ago
State : Running, pid: 2417
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 109 M RSS (451 MB VSZ)
Started: Fri Oct 16 08:16:32 2020 - 00:09 ago
State : Running, pid: 2417
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 111 M RSS (457 MB VSZ)
Started: Fri Oct 16 08:16:32 2020 - 00:11 ago
State : Uninterruptible, pid: 2417
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 111 M RSS (457 MB VSZ)
Started: Fri Oct 16 08:16:32 2020 - 00:13 ago
State : Uninterruptible, pid: 2417
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 111 M RSS (457 MB VSZ)
Started: Fri Oct 16 08:16:32 2020 - 00:15 ago
State : Uninterruptible, pid: 2417
Existing lock /var/run/yum.pid: another copy is running as pid 2441.
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 51 M RSS (394 MB VSZ)
Started: Fri Oct 16 08:16:47 2020 - 00:02 ago
State : Sleeping, pid: 2441
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 51 M RSS (394 MB VSZ)
Started: Fri Oct 16 08:16:47 2020 - 00:04 ago
State : Running, pid: 2441
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 52 M RSS (394 MB VSZ)
Started: Fri Oct 16 08:16:47 2020 - 00:06 ago
State : Sleeping, pid: 2441
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 52 M RSS (394 MB VSZ)
Started: Fri Oct 16 08:16:47 2020 - 00:08 ago
State : Sleeping, pid: 2441
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 52 M RSS (394 MB VSZ)
Started: Fri Oct 16 08:16:47 2020 - 00:10 ago
State : Sleeping, pid: 2441
!mysql-connectors-community/x86_64 MySQL Connectors Community 137
!mysql-tools-community/x86_64 MySQL Tools Community 93
!mysql56-community/x86_64 MySQL 5.6 Community Server 606
[root@slave ~]# yum -y install mysql-community-server
Loaded plugins: fastestmirror, langpacks
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
mysql-connectors-community | 2.5 kB 00:00:00
mysql-tools-community | 2.5 kB 00:00:00
mysql56-community | 2.5 kB 00:00:00
percona-release-noarch | 2.9 kB 00:00:00
percona-release-x86_64 | 2.9 kB 00:00:00
prel-release-noarch | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
zabbix | 2.9 kB 00:00:00
zabbix-non-supported | 951 B 00:00:00
(1/3): mysql-connectors-community/x86_64/primary_db | 62 kB 00:00:00
(2/3): mysql56-community/x86_64/primary_db | 265 kB 00:00:00
(3/3): mysql-tools-community/x86_64/primary_db | 76 kB 00:00:00
Loading mirror speeds from cached hostfile
1. base: mirrors.aliyun.com
2. extras: mirrors.aliyun.com
3. updates: mirrors.ustc.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.6.49-2.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.6.49-2.el7 for package: mysql-community-server-5.6.49-2.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 5.6.10 for package: mysql-community-server-5.6.49-2.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.6.49-2.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 5.6.10 for package: mysql-community-client-5.6.49-2.el7.x86_64
---> Package mysql-community-common.x86_64 0:5.6.49-2.el7 will be installed
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.44-2.el7.centos will be obsoleted
---> Package mysql-community-libs.x86_64 0:5.6.49-2.el7 will be obsoleting
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================
Package Arch Version Repository Size
==========================================================================================
Installing:
mysql-community-libs x86_64 5.6.49-2.el7 mysql56-community 2.2 M
replacing mariadb-libs.x86_64 1:5.5.44-2.el7.centos
mysql-community-server x86_64 5.6.49-2.el7 mysql56-community 67 M
Installing for dependencies:
mysql-community-client x86_64 5.6.49-2.el7 mysql56-community 21 M
mysql-community-common x86_64 5.6.49-2.el7 mysql56-community 289 k
Transaction Summary
==========================================================================================
Install 2 Packages (+2 Dependent packages)
Total download size: 90 M
Downloading packages:
No Presto metadata available for mysql56-community
(1/4): mysql-community-common-5.6.49-2.el7.x86_64.rpm | 289 kB 00:00:00
(2/4): mysql-community-libs-5.6.49-2.el7.x86_64.rpm | 2.2 MB 00:00:00
(3/4): mysql-community-client-5.6.49-2.el7.x86_64.rpm | 21 MB 00:00:07
(4/4): mysql-community-server-5.6.49-2.el7.x86_64.rpm | 67 MB 00:00:14
------------------------------------------------------------------------------------------
Total 5.7 MB/s | 90 MB 00:00:15
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : mysql-community-common-5.6.49-2.el7.x86_64 1/5
Installing : mysql-community-libs-5.6.49-2.el7.x86_64 2/5
Installing : mysql-community-client-5.6.49-2.el7.x86_64 3/5
Installing : mysql-community-server-5.6.49-2.el7.x86_64 4/5
warning: /etc/my.cnf created as /etc/my.cnf.rpmnew
Erasing : 1:mariadb-libs-5.5.44-2.el7.centos.x86_64 5/5
warning: file /usr/lib64/mysql/libmysqlclient.so.18.0.0: remove failed: No such file or directory
Verifying : mysql-community-server-5.6.49-2.el7.x86_64 1/5
Verifying : mysql-community-client-5.6.49-2.el7.x86_64 2/5
Verifying : mysql-community-common-5.6.49-2.el7.x86_64 3/5
Verifying : mysql-community-libs-5.6.49-2.el7.x86_64 4/5
Verifying : 1:mariadb-libs-5.5.44-2.el7.centos.x86_64 5/5
Installed:
mysql-community-libs.x86_64 0:5.6.49-2.el7 mysql-community-server.x86_64 0:5.6.49-2.el7
Dependency Installed:
mysql-community-client.x86_64 0:5.6.49-2.el7
mysql-community-common.x86_64 0:5.6.49-2.el7
Replaced:
mariadb-libs.x86_64 1:5.5.44-2.el7.centos
Complete!
3. 启动MySQL
配置文件默认再/etc目录下，根据需要更改：[[email protected] ~]# vim /etc/my.cnf
自建数据库不支持如下参数，需要注释掉。
#innodb_log_checksum_algorithm
#innodb_fast_checksum
#innodb_log_block_size
#innodb_doublewrite_file
#rds_encrypt_data
#innodb_encrypt_algorithm
#redo_log_version
#master_key_id
#server_uuid
如果自建数据库使用的是MyISAM引擎，和阿里云的InnoDB不兼容，需要多注释掉如下参数并增加skip-grant-tables参数：
#innodb_log_checksum_algorithm=strict_crc32
#redo_log_version=1
skip-grant-tables
启动数据库：[[email protected] ~]# systemctl status mysqld
[[email protected] ~]# systemctl start mysqld
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# systemctl status mysqld
● mysqld.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead)
[root@slave ~]# systemctl start mysqld
[root@slave ~]# systemctl status mysqld
● mysqld.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2020-10-16 08:40:20 CST; 19s ago
Process: 2910 ExecStartPost=/usr/bin/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Process: 2841 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 2909 (mysqld_safe)
Tasks: 23
CGroup: /system.slice/mysqld.service
├─2909 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─3075 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir...
Oct 16 08:40:18 slave mysql-systemd-start[2841]: 2020-10-16 08:40:18 2885 [Note] Inno.....
Oct 16 08:40:19 slave mysql-systemd-start[2841]: 2020-10-16 08:40:19 2885 [Note] Inno...87
Oct 16 08:40:19 slave mysql-systemd-start[2841]: PLEASE REMEMBER TO SET A PASSWORD FO... !
Oct 16 08:40:19 slave mysql-systemd-start[2841]: To do so, start the server, then iss...s:
Oct 16 08:40:19 slave mysql-systemd-start[2841]: /usr/bin/mysqladmin -u root password...d'
Oct 16 08:40:19 slave mysql-systemd-start[2841]: /usr/bin/mysqladmin -u root -h slave...d'
Oct 16 08:40:19 slave mysql-systemd-start[2841]: Alternatively you can run:
Oct 16 08:40:20 slave mysqld_safe[2909]: 201016 08:40:20 mysqld_safe Logging to '/var...'.
Oct 16 08:40:20 slave mysqld_safe[2909]: 201016 08:40:20 mysqld_safe Starting mysqld ...ql
Oct 16 08:40:20 slave systemd[1]: Started MySQL Community Server.
Hint: Some lines were ellipsized, use -l to show in full.
4. yum安装Percona XtraBackup 2.3
[[email protected] ~]# yum install percona-xtrabackup
[root@slave ~]# yum install percona-xtrabackup
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
1. base: mirrors.aliyun.com
2. extras: mirrors.aliyun.com
3. updates: mirrors.ustc.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup.x86_64 0:2.3.10-1.el7 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-2.3.10-1.el7.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================
Package Arch Version Repository Size
==========================================================================================
Installing:
percona-xtrabackup x86_64 2.3.10-1.el7 percona-release-x86_64 5.0 M
Installing for dependencies:
perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k
Transaction Summary
==========================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 5.1 M
Installed size: 5.3 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): perl-DBD-MySQL-4.023-6.el7.x86_64.rpm | 140 kB 00:00:00
(2/2): percona-xtrabackup-2.3.10-1.el7.x86_64.rpm | 5.0 MB 00:00:07
------------------------------------------------------------------------------------------
Total 731 kB/s | 5.1 MB 00:00:07
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : perl-DBD-MySQL-4.023-6.el7.x86_64 1/2
Installing : percona-xtrabackup-2.3.10-1.el7.x86_64 2/2
Verifying : percona-xtrabackup-2.3.10-1.el7.x86_64 1/2
Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 2/2
Installed:
percona-xtrabackup.x86_64 0:2.3.10-1.el7
Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.023-6.el7
Complete!
- 拉取阿里云物理备份并恢复到本地
拉备份到本地就不多说了，备份文件为<数据备份文件名>.tar.gz格式，解压缩到指定目录：
[[email protected] ~]# tar -izxvf hins48858_data_20201014121329.tar.gz -C /var/lib/mysql/
[[email protected] ~]# systemctl stop mysqld
[[email protected] ~]# mv /var/lib/mysql/ /var/lib/mysqlbak
[[email protected] ~]# mkdir /var/lib/mysql
[[email protected] ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log /var/lib/mysql
[root@slave ~]# innobackupex --defaults-file=/etc/my.cnf --apply-log /var/lib/mysql
201016 08:46:28 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.3.10 based on MySQL server 5.6.24 Linux (x86_64) (revision id: bd0d4403f36)
xtrabackup: cd to /var/lib/mysql/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(723800829)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1630314 and 1630314 in ibdata files do not match the log sequence number 723800829 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 723800829
xtrabackup: Last MySQL binlog file position 458207, file name mysql-bin.000386
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 723804783
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:200M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 4294967296
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 4096 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000
InnoDB: Setting log file ./ib_logfile1 size to 4096 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=723804783
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 723805196
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 723805206
201016 08:48:26 completed OK!
- 验证恢复情况
修改MySQL数据目录权限：[[email protected] ~]# chown -R mysql:mysql /var/lib/mysql
[[email protected] ~]# systemctl start mysqld
root初始密码查看：[[email protected] ~]# grep ‘password’ /var/log/mysqld.log |head -n 1
[root@slave ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.49 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rds_sdad_sc |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use rds_sdad_sc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------------+
| Tables_in_rds_zxxcx_sc |
+------------------------+
| tb_ad |
| tb_hotsearch |
| tb_liveroom |
| tb_place |
| tb_recommendcollection |
| tb_recommendcontent |
+------------------------+
6 rows in set (0.00 sec)
mysql> select count(*) from tb_ad;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.05 sec)
上一篇： [Pytorch] loss