mysql5.6物理备份_阿里云RDS MySQL 5.6物理备份文件恢复到自建数据库

基本按照阿里云官方文档操作即可。坑点在于阿里云RDS官方文档中对于root密码重置描述的是参考MySQL官网，但由于RDS没有root用户因此实际此方法并不适用。

首先MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3：

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb

apt-get update

apt-get install percona-xtrabackup

1

2

3

4

wgethttps://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb

sudodpkg-ipercona-release_0.1-6.$(lsb_release-sc)_all.deb

apt-getupdate

apt-getinstallpercona-xtrabackup

然后安装阿里云的解压工具qpress：

wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"

tar xvf qpress-11-linux-x64.tar

chmod 775 qpress

cp qpress /usr/bin

1

2

3

4

wget"http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"

tarxvfqpress-11-linux-x64.tar

chmod775qpress

cpqpress/usr/bin

之后解压从阿里云下载的备份文件，注意扩展名：

mkdir -p /data/mysql/data

cat dump_qp.xb |xbstream -x -v -C /data/mysql/data/

innobackupex --decompress --remove-original /data/mysql/data

1

2

3

mkdir-p/data/mysql/data

catdump_qp.xb|xbstream-x-v-C/data/mysql/data/

innobackupex--decompress--remove-original/data/mysql/data

解压完后需要修改得到的backup-my.cnf，添加如下参数：

lower_case_table_names=1

1

lower_case_table_names=1

注释掉如下自建数据库不支持的参数：

#innodb_log_checksum_algorithm

#innodb_fast_checksum

#innodb_log_block_size

#innodb_doublewrite_file

#innodb_encrypt_algorithm

#rds_encrypt_data

#redo_log_version

#master_key_id

#server_uuid

1

2

3

4

5

6

7

8

9

#innodb_log_checksum_algorithm

#innodb_fast_checksum

#innodb_log_block_size

#innodb_doublewrite_file

#innodb_encrypt_algorithm

#rds_encrypt_data

#redo_log_version

#master_key_id

#server_uuid

由于数据库比较大，因此需要修改数据库文件的路径。先停掉服务：

service mysql stop

1

servicemysqlstop

然后修改配置文件，把：

/etc/mysql/mysql.conf.d/mysqld.cnf

1

/etc/mysql/mysql.conf.d/mysqld.cnf

中的：

datadir = /data/var/lib/mysql

log-error = /data/var/log/mysql/error.log

1

2

datadir=/data/var/lib/mysql

log-error=/data/var/log/mysql/error.log

改成目标路径，然后把原文件夹挪到目标路径即可：

mkdir -p /data/var/lib

cp -R /var/lib/mysql /data/var/lib/mysql

mkdir -p /data/var/log/mysql/

cp /var/log/mysql/error.log /data/var/log/mysql/error.log

1

2

3

4

mkdir-p/data/var/lib

cp-R/var/lib/mysql/data/var/lib/mysql

mkdir-p/data/var/log/mysql/

cp/var/log/mysql/error.log/data/var/log/mysql/error.log

注意给mysql:mysql用户访问权限：

chown -R mysql:mysql /data/mysql/data

chown -R mysql:mysql /data/var/log/mysql/

1

2

chown-Rmysql:mysql/data/mysql/data

chown-Rmysql:mysql/data/var/log/mysql/

修改AppArmor设置，否则会报一个打不开文件的诡异报错：

apt install -y apparmor-utils

aa-complain /usr/sbin/mysqld

1

2

aptinstall-yapparmor-utils

aa-complain/usr/sbin/mysqld

接下来新建root用户并设置密码，新建一个mysql-init文件，改文件会被通过–init-file参数传入，其中包含的SQL会被执行：

use mysql;

insert into user (user,host,password,ssl_type,ssl_cipher,x509_issuer,x509_subject) values ('root','%',password('CHANGE_ME'),'','','','');

update user set

Select_priv='Y'

, Insert_priv='Y'

, Update_priv='Y'

, Delete_priv='Y'

, Create_priv='Y'

, Drop_priv='Y'

, Reload_priv='Y'

, Shutdown_priv='Y'

, Process_priv='Y'

, File_priv='Y'

, Grant_priv='Y'

, References_priv='Y'

, Index_priv='Y'

, Alter_priv='Y'

, Show_db_priv='Y'

, Super_priv='Y'

, Create_tmp_table_priv='Y'

, Lock_tables_priv='Y'

, Execute_priv='Y'

, Repl_slave_priv='Y'

, Repl_client_priv='Y'

, Create_view_priv='Y'

, Show_view_priv='Y'

, Create_routine_priv='Y'

, Alter_routine_priv='Y'

, Create_user_priv='Y'

, Event_priv='Y'

, Trigger_priv='Y'

, Create_tablespace_priv='Y'

where user='root' and host='%';

insert into user (user,host,password,ssl_type,ssl_cipher,x509_issuer,x509_subject) values ('root','localhost',password('CHANGE_ME'),'','','','');

update user set

Select_priv='Y'

, Insert_priv='Y'

, Update_priv='Y'

, Delete_priv='Y'

, Create_priv='Y'

, Drop_priv='Y'

, Reload_priv='Y'

, Shutdown_priv='Y'

, Process_priv='Y'

, File_priv='Y'

, Grant_priv='Y'

, References_priv='Y'

, Index_priv='Y'

, Alter_priv='Y'

, Show_db_priv='Y'

, Super_priv='Y'

, Create_tmp_table_priv='Y'

, Lock_tables_priv='Y'

, Execute_priv='Y'

, Repl_slave_priv='Y'

, Repl_client_priv='Y'

, Create_view_priv='Y'

, Show_view_priv='Y'

, Create_routine_priv='Y'

, Alter_routine_priv='Y'

, Create_user_priv='Y'

, Event_priv='Y'

, Trigger_priv='Y'

, Create_tablespace_priv='Y'

where user='root' and host='localhost';

flush privileges;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

usemysql;

insertintouser(user,host,password,ssl_type,ssl_cipher,x509_issuer,x509_subject)values('root','%',password('CHANGE_ME'),'','','','');

updateuserset

Select_priv='Y'

,Insert_priv='Y'

,Update_priv='Y'

,Delete_priv='Y'

,Create_priv='Y'

,Drop_priv='Y'

,Reload_priv='Y'

,Shutdown_priv='Y'

,Process_priv='Y'

,File_priv='Y'

,Grant_priv='Y'

,References_priv='Y'

,Index_priv='Y'

,Alter_priv='Y'

,Show_db_priv='Y'

,Super_priv='Y'

,Create_tmp_table_priv='Y'

,Lock_tables_priv='Y'

,Execute_priv='Y'

,Repl_slave_priv='Y'

,Repl_client_priv='Y'

,Create_view_priv='Y'

,Show_view_priv='Y'

,Create_routine_priv='Y'

,Alter_routine_priv='Y'

,Create_user_priv='Y'

,Event_priv='Y'

,Trigger_priv='Y'

,Create_tablespace_priv='Y'

whereuser='root'andhost='%';

insertintouser(user,host,password,ssl_type,ssl_cipher,x509_issuer,x509_subject)values('root','localhost',password('CHANGE_ME'),'','','','');

updateuserset

Select_priv='Y'

,Insert_priv='Y'

,Update_priv='Y'

,Delete_priv='Y'

,Create_priv='Y'

,Drop_priv='Y'

,Reload_priv='Y'

,Shutdown_priv='Y'

,Process_priv='Y'

,File_priv='Y'

,Grant_priv='Y'

,References_priv='Y'

,Index_priv='Y'

,Alter_priv='Y'

,Show_db_priv='Y'

,Super_priv='Y'

,Create_tmp_table_priv='Y'

,Lock_tables_priv='Y'

,Execute_priv='Y'

,Repl_slave_priv='Y'

,Repl_client_priv='Y'

,Create_view_priv='Y'

,Show_view_priv='Y'

,Create_routine_priv='Y'

,Alter_routine_priv='Y'

,Create_user_priv='Y'

,Event_priv='Y'

,Trigger_priv='Y'

,Create_tablespace_priv='Y'

whereuser='root'andhost='localhost';

flushprivileges;

启动MySQL进程：

mysqld --defaults-file=/data/mysql/data/backup-my.cnf --init-file=/data/mysql/data/mysql-init --user=mysql --datadir=/data/mysql/data &

1

mysqld--defaults-file=/data/mysql/data/backup-my.cnf--init-file=/data/mysql/data/mysql-init--user=mysql--datadir=/data/mysql/data&

此进程直接在后台运行，只能启动一个。如果有什么报错的话，kill掉对应的PID即可，mysqld会优雅关闭。

然后登陆验证即可：

mysql -h 127.0.0.1 -u root -p -P 3306

1

mysql-h127.0.0.1-uroot-p-P3306

参考文档：1、官方文档：https://help.aliyun.com/knowledge_detail/41817.html?spm=5176.2020520165.120.d41817.3d127029dUBlNV

2、MySQL官方重置root密码：https://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html，由于RDS没有root用户因此实际此方法并不适用

3、https://blog.csdn.net/iris_xuting/article/details/88311410