阿里云RDS数据库数据恢复到自建数据库（MySQL5.7）

最新推荐文章于 2022-11-01 11:18:09 发布

置顶 SU_Devops

最新推荐文章于 2022-11-01 11:18:09 发布

阅读量397

点赞数 1

文章标签： mysql rds

本文链接：https://blog.csdn.net/SU_Devops/article/details/107559790

版权

阿里云RDS数据库数据恢复到自建数据库（MySQL5.7）

系统安装

新装虚拟机CentOS Linux release 7.7.1908 (Core)

关闭防火墙与selinux

安装基础软件包与编译安装mysql所需依赖包

yum install -y bash-completion ntp vim net-tools wget
yum install -y cmake gcc-c++ gcc ncurses-devel perl-Data-Dumper boost boost-doc boost-devel

软件包准备

MySQL 5.7版本（与RDS数据库版本一致）

MySQL官网下载软件包，此处下载5.7.16

https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.16.tar.gz

MySQL 5.7版本需要安装 Percona XtraBackup 2.4

参考文档：

https://www.percona.com/doc/percona-xtrabackup/2.4/installation.html?spm=a2c4g.11186623.2.16.70e92d2fX6pYCu

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install qpress
yum list | grep percona
yum install -y percona-xtrabackup-24.x86_64

安装mysql5.7

安装前卸载mariadb相关软件

rpm -qa | grep mariadb
rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps

创建mysql用户

useradd mysql -s /sbin/nologin

解压下载好的MySQL安装包

 cd /usr/local
 wget -c https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.16.tar.gz
 tar xf mysql-boost-5.7.16.tar.gz

编写cmake.sh，并编译安装

cd mysql-5.7.16
vim cmake.sh

cmake . \
-DCMAKE_INSTALL_PREFIX=/home/mysqld \
-DMYSQL_DATADIR=/home/mysqld/data \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DEXTRA_CHARSETS=all \
-DWITH_BOOST=/usr/local/mysql-5.7.16/boost \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci

保存退出
chmod +x cmake.sh
./cmake.sh
 make && make install

配置文件生成

cp support-files/my-default.cnf /etc/my.cnf
vim /etc/my.cnf
[mysqld]
basedir = /home/mysqld
datadir = /home/mysqld/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
log-error=/home/mysqld/data/error.log
log_bin=mysql-bin
pid-file=/home/mysqld/data/mysql.pid

设置添加到系统服务并设置开机启动

cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on

chown mysql. -R mysqld/

添加环境变量

vim /etc/profile
添加  export PATH=/home/mysqld/bin/:$PATH 

source /etc/profile

初始化MySQL

/home/mysqld/bin/mysqld --initialize --user=mysql --basedir=/home/mysqld --datadir=/home/mysqld/data

启动数据库

service mysqld start 
Starting MySQL.. SUCCESS!

下载RDS物理备份

登录[RDS管理控制台]
在页面左上角，选择实例所在地域。
找到目标实例，单击实例ID。
在左侧导航栏中单击备份恢复。
选择数据备份标签页。
选择查询的时间范围，然后单击查询。
在数据备份列表中，找到要下载的数据备份，并单击其右侧的下载。
在实例备份文件下载窗口，单击复制外网地址旁的复制获取数据备份文件外网下载地址。
登录云服务器ECS。
执行如下命令，下载数据备份文件。

wget -c '<数据备份文件外网下载地址>' -O <自定义文件名>

-c：启用断点续传模式。
-O：将下载的结果保存为指定的文件（使用URL中包含的文件名后缀 .tar.gz 、.xb.gz 或 _qp.xb）。

11.执行如下命令，解压已下载的数据备份文件。

目前物理备份集文件有3种格式

tar 压缩包（.tar.gz 后缀）
xbstream 压缩包（.xb.gz 后缀）
xbstream 文件包（_qp.xb 后缀）

对于tar 压缩包（.tar.gz 后缀），使用命令：

tar -izxvf <数据备份文件名>.tar.gz -C /home/data

对于xbstream 压缩包（.xb.gz 后缀），使用命令：

gzip -d -c <数据备份文件名>.xb.gz | xbstream -x -v -C /home/data

对于xbstream 文件包（_qp.xb 后缀），使用命令：

## 解包
cat <数据备份文件名>_qp.xb | xbstream -x -v -C /home/data
## MySQL 5.6/5.7解压
innobackupex --decompress --remove-original /home/data
## MySQL 8.0解压
xtrabackup --decompress --remove-original --target-dir=/home/data

说明 -C：指定文件要解压到的目录。可选参数，若不指定就解压到当前目录。

准备恢复数据

恢复物理备份

停止MySQL服务，备份原数据库data目录，并将解压好的data数据移到mysqld下当做数据库data目录

 service mysqld stop
 cd /home/mysqld/
 mv data databak
 mv /home/data /home/mysqld/data

执行恢复命令

## MySQL 5.6/5.7
innobackupex --defaults-file=/home/mysqld/data/backup-my.cnf --apply-log /home/mysqld/data

## MySQL 8.0
xtrabackup --prepare --target-dir=/home/mysqld/data
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data

##看到如下信息则表示恢复成功
200724 09:50:16 completed OK!

恢复成功过程中有提示（片段）
## 表示了数据库目前恢复到的位置
InnoDB: xtrabackup: Last MySQL binlog file position 11444197, file name mysql-bin.000249

## 若是上面没有输出的话,也可以从xtrabackup信息里查看,如下:
more xtrabackup_info

binlog_pos = filename 'mysql-bin.000249', position 11444197, GTID of the last change '4584d4c1-c965-11e9-b52e-506b4b416a64:1-2096410,

为了避免版本问题，修改backup-my.cnf参数，

vim /home/mysqld/data/backup-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
#server_id


剩余参数复制粘贴到/etc/my.cnf下，并修改innodb_undo_directory参数为绝对路径
cat backup-my.cnf | grep -v  ^#
[mysqld]
innodb_checksum_algorithm=crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_page_size=16384
innodb_undo_directory=/home/mysqld/data/
innodb_undo_tablespaces=0


目前配置文件参数如下
cat /etc/my.cnf  | grep -Ev '^#|^$'

[mysqld]
basedir = /home/mysqld
datadir = /home/mysqld/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
log-error=/home/mysqld/data/error.log
log_bin=mysql-bin
pid-file=/home/mysqld/data/mysql.pid
innodb_checksum_algorithm=crc32
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
innodb_page_size=16384
innodb_undo_directory=/home/mysqld/data/
innodb_undo_tablespaces=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

修改属主属组

 chown mysql. -R /home/mysqld/

启动数据库，使用RDS账号密码登录，登录成功

service mysqld start
Starting MySQL.... SUCCESS!

通过binlog恢复增量

通过RDS管理后台，日志备份下载binlog，下载方法一样

补充几条查看binlog相关命令

分析RDS binlog日志

wget -c "http://mysql-bin" -O mysql-bin

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000214 > mysql-bin.000214.txt

/mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db  --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00'  mysql-bin.000007 | more

恢复
mysqlbinlog --no-defaults  --skip-gtids  --database=paas2  mysql-bin.000872 | mysql -uroot -p123456 paas2

下载完毕先根据之前回复物理备份得到的信息，恢复第一个binlog

[root@localhost binlog]# ll /home/binlog/
总用量 1841344
-rw-r--r-- 1 root root  20848127 7月  24 10:06 mysql-bin.000249
-rw-r--r-- 1 root root 226667232 7月  24 10:06 mysql-bin.000250
-rw-r--r-- 1 root root 465732198 7月  24 10:06 mysql-bin.000251
-rw-r--r-- 1 root root 295782950 7月  24 10:07 mysql-bin.000252
-rw-r--r-- 1 root root  20664514 7月  24 10:07 mysql-bin.000253
-rw-r--r-- 1 root root 170182207 7月  24 10:07 mysql-bin.000254
-rw-r--r-- 1 root root 265103449 7月  24 10:07 mysql-bin.000255
-rw-r--r-- 1 root root 208427874 7月  24 10:08 mysql-bin.000256
-rw-r--r-- 1 root root  20665466 7月  24 10:08 mysql-bin.000257
-rw-r--r-- 1 root root 189574682 7月  24 10:08 mysql-bin.000258


mysqlbinlog --no-defaults --skip-gtids mysql-bin.000249 --start-position='11444197' | mysql -u -p
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 7: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

报错原因是所用账号密码没有超级权限，而RDS账号权限不是root账号权限，又无法得知root账号密码，此时修改该账号权限为所有权限

解决用户权限问题

mysql> show grants for 用户@'%';
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO '用户'@'%' WITH GRANT OPTION |

可以看出该用户没有所有权限

mysql> select * from mysql.user where user='用户' \G
*************************** 1. row ***************************
                  Host: %
                  User: 用户
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: N
          Process_priv: Y
             File_priv: N
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: N
            Super_priv: N
 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: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: ***********
      password_expired: N
 password_last_changed: 2020-04-01 15:35:04
     password_lifetime: NULL
        account_locked: N

可以看出有部分权限是N

mysql> grant all privileges on *.* to 用户@'%';
ERROR 1045 (28000): Access denied for user '用户'@'%' (using password: YES)
用户权限不足，无法赋予所有权限

试着直接修改mysql.user下字段值
mysql> UPDATE mysql.user SET Shutdown_priv='Y',File_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tablespace_priv='Y' WHERE Host='%' AND User='用户';
ERROR 1064 (42000): Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user_list''

这个报错是因为恢复数据后，忽略了触发器造成的，需要删除触发器

mysql> select trigger_schema,trigger_name from information_schema.triggers;
+----------------+----------------------------+
| trigger_schema | trigger_name               |
+----------------+----------------------------+
| sys            | sys_config_insert_set_user |
| sys            | sys_config_update_set_user |
+----------------+----------------------------+

mysql> drop trigger sys.sys_config_insert_set_user;
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

尝试删除触发器又报错权限不足，这不就返回开头了吗，有解决办法
MySQL配置文件添加
skip-grant-tables

重启MySQL
mysql> drop trigger sys.sys_config_insert_set_user;
Query OK, 0 rows affected (0.13 sec)

mysql> drop trigger sys.sys_config_update_set_user;
Query OK, 0 rows affected (0.02 sec)

此外还应该彻底删除触发器文件
找到自己数据恢复的目录，查找以“.trg”为结尾的触发器文件，这里数据恢复目录为/home/mysqld/data，执行find查找口令:
find /home/mysqld/data/ -iname *.trg

/home/mysqld/data/mysql/user.TRG
/home/mysqld/data/mysql/proxies_priv.TRG
查询到2个触发器的文件，更改后缀名
mv /home/mysqld/data/mysql/user.TRG /home/mysqld/data/mysql/user.TRG.bak
mv /home/mysqld/data/mysql/proxies_priv.TRG /home/mysqld/data/mysql/proxies_priv.TRG.bak


删除刚加的配置参数,重启MySQL
此时已经可以更新用户权限了
mysql>  UPDATE mysql.user SET Shutdown_priv='Y',File_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tablespace_priv='Y' WHERE Host='%' AND User='用户';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再次查看用户权限已经拥有所有权限，搞定。
mysql> show grants for 用户@'%';
+--------------------------------------------------------------+
| Grants for 用户@%                                           |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO '用户'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+

此时继续恢复binlog日志

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000249 --start-position='11444197' | mysql -u -p

已经可以正常恢复
查看数据库数据目录下binlog日志在不断增加，说明已经在恢复数据。
恢复完可继续恢复其他binlog日志

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000251 | mysql -u -p

恢复过程中错误日志会报错

 [ERROR] InnoDB: Column table_name in table `mysql`.`innodb_table_stats` is VARCHAR(597) NOT NULL but should be VARCHAR(192) NOT NULL (length mismatch).

此时修改两个表结构

innodb_index_stats 与 innodb_table_stats table_name字段修改为varchar(64)

table_name varchar(64) COLLATE utf8_bin NOT NULL

SU_Devops

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
阿里云RDS数据库数据恢复到自建数据库（MySQL5.7）

阿里云RDS数据库数据恢复到自建数据库（MySQL5.7）系统安装新装虚拟机CentOS Linux release 7.7.1908 (Core)关闭防火墙与selinux安装基础软件包与编译安装mysql所需依赖包yum install -y bash-completion ntp vim net-tools wgetyum install -y cmake gcc-c++ gcc ncurses-devel perl-Data-Dumper boost boost-doc boost-de
复制链接

扫一扫