作者：田杰
RDS for MySQL 5.7 备份恢复为本地实例
近期同学咨询反馈 RDS for MySQL 5.7 备份恢复本地实例的问题比较多，提供一个恢复样例。
- 本地环境
| * | 项目 | 说明 |
|---|---|---|
| 1 | OS | ECS 官方系统镜像 - CentOS 6.8 64 位。注意：要求恢复本地必须是 Linux 系统 |
| 2 | MySQL DB | MySQL 官方社区 5.7.24 64位 Linux - Generic (glibc 2.12) (x86, 64-bit), Compressed TAR Archive |
| 3 | Xtrabackup | Percona-Xtrabackup-2.4.13 (Redhat Enterprise Linux / CentOS / Oracle Linux 6) |
1.1 MySQL DB 程序安装
为了便于移植和安装，安装介质采用的是独立具体 LInux OS 的 TAR 版本，具体请参考下图，下载地址：
下载后是 mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz，下面是安装步骤：
root@ecs01# groupadd -g 500 mysql
root@ecs01# useradd -u 500 -g 500 -G disk -m -d /home/mysql mysql
root@ecs01# passwd mysql
root@ecs01# cd /data; mkdir db_data; chown -R mysql:mysql ./db_data/
root@ecs01# cp -rp /data/soft/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz /usr/local/
root@ecs01# cd /usr/local; tar zxpf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
root@ecs01# ln -s /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql57
roto@ecs01# rm -f mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
root@ecs01# su - mysql
mysql@ecs01$ vi .bashrc
# add below 2 lines
BASE_PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/jre/bin:/data/soft/redis/redis-3.2.6/src/:/usr/local/mongodb/bin
export PATH=$BASE_PATH:/usr/local/mysql57/bin
mysql@ecs01$ . .bashrc
# BASE_PATH 请根据本地环境配置
1.2 Xtrabackup 程序安装
RDS for MySQL 5.7 恢复需要 Xtrabackup 2.4 版本，[下载地址]
下载后是 Percona-XtraBackup-2.4.13-r3e7ca7c-el6-x86_64-bundle.tar，下面是安装步骤。
root@ecs01# yum -y install libev
root@ecs01# yum -y install rsync
root@ecs01# yum -y install perl-DBD-MySQL
root@ecs01# cd /data/soft/percona/xtraback/2.4/
root@ecs01# tar xpf Percona-XtraBackup-2.4.13-r3e7ca7c-el6-x86_64-bundle.tar
root@ecs01# rpm -Uvh percona-xtrabackup-24-2.4.13-1.el6.x86_64.rpm- 恢复步骤
2.1 恢复备份集
RDS for MySQL 控制台提供 RDS 实例的备份集，支持 公网 和 标准内网的下载，这里用 RDS for MySQL 5.7 HA 高可用版本实例做例子，文件 hins5937443_data_20190122042427.tar.gz 。
下面是操作步骤：
mysql@ecs01$ cd /data/db_data; mkdir hins5937443
mysql@ecs01$ wget -c 'http://xxx/hins5937443_data_20190122042427.tar.gz?OSSAccessKeyxxxxI%3D' -O hins5937443_data_20190122042427.tar.gz
mysql@ecs01$ tar -izxvf hins5937443_data_20190122042427.tar.gz -C ./hins5937443
mysql@ecs01$ cd hins5937443
mysql@ecs01$ innobackupex --defaults-file=/data/db_data/hins5937443/backup-my.cnf --apply-log /data/db_data/hins5937443
.....
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 21190110248
190122 15:10:10 completed OK!
# 看到上面的 completed OK! 就说明备份集恢复成功了
# 请注意这些操作用户使用的是 mysql，也就是 mysqld 进程运行使用的默认用户
# 第一步创建 datadir (/data/db_data/hins5937443) 比较重要，保证 mysqld 对 datadir 有正确的权限访问
2.2 启动实例
备份集文件恢复完毕后，需要考虑拉起 MySQL 进程并且登录的问题，涉及到参数配置文件的调整和 super 权限的 root@localhost 账户的创建和密码设置。
下面是测试使用的 my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
# Basic paramters
user = mysql
port = 3701
character_set_server = utf8mb4
skip-character-set-client-handshake = 1
basedir = /usr/local/mysql57
datadir = /data/db_data/hins5937443
pid-file = /data/db_data/hins5937443/mysql.pid
socket = /data/db_data/hins5937443/mysql.sock
lower_case_table_names = 1
# Logging
log_error = error.log
log_queries_not_using_indexes = 0
long_query_time = 1
slow_query_log = 1
slow_query_log_file = mysql-slow.log
# Binary Logging
log_bin = binlog
binlog_format = row
binlog_row_image = FULL
expire_logs_days=0
sync_binlog=1
# Replication
server-id = 3701
# paramters from RDS
innodb_checksum_algorithm=crc32
#innodb_log_checksum_algorithm=strict_crc32 # RDS parameter
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000
#innodb_fast_checksum=false # comment out according to RDS doc
#innodb_page_size=16384 # comment out according to RDS doc
#innodb_log_block_size=512 # comment out according to RDS doc
innodb_undo_directory=./
innodb_undo_tablespaces=0
#server_id=2270864671
#redo_log_version=1 # RDS parameter
#server_uuid=8653b93d-e776-11e8-b243-506b4b2adfce # auto.cnf parameter
#master_key_id=0 # RDS parameter
# InnoDB Parameters
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = ON
innodb_adaptive_max_sleep_delay = 150000
innodb_api_bk_commit_interval = 5
innodb_api_disable_rowlock = OFF
innodb_api_enable_binlog = OFF
innodb_api_enable_mdl = OFF
innodb_api_trx_level = 0
innodb_autoextend_increment = 64
innodb_autoinc_lock_mode = 1
innodb_buffer_pool_dump_at_shutdown = OFF
innodb_buffer_pool_dump_now = OFF
innodb_buffer_pool_filename = ib_buffer_pool
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_abort = OFF
innodb_buffer_pool_load_at_startup = OFF
innodb_buffer_pool_load_now = OFF
innodb_buffer_pool_size = 2G
innodb_change_buffer_max_size = 25
innodb_change_buffering = all
innodb_checksums = ON
innodb_cmp_per_index_enabled = OFF
innodb_commit_concurrency = 0
innodb_compression_failure_threshold_pct = 5
innodb_compression_level = 6
innodb_compression_pad_pct_max = 50
innodb_concurrency_tickets = 5000
innodb_disable_sort_file_cache = ON
innodb_doublewrite = ON
innodb_fast_shutdown = 1
innodb_file_format = Barracuda
innodb_file_format_check = ON
innodb_file_format_max = Barracuda
innodb_file_per_table = ON
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 1
innodb_flushing_avg_loops = 30
innodb_force_load_corrupted = OFF
innodb_force_recovery = 0
innodb_ft_cache_size = 8000000
innodb_ft_enable_diag_print = OFF
innodb_ft_enable_stopword = ON
innodb_ft_max_token_size = 84
innodb_ft_min_token_size = 1
innodb_ft_num_word_optimize = 2000
innodb_ft_result_cache_limit = 2000000000
innodb_ft_sort_pll_degree = 2
innodb_ft_total_cache_size = 640000000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_large_prefix = ON
innodb_lock_wait_timeout = 1000
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 1048576
innodb_log_compressed_pages = ON
innodb_lru_scan_depth = 1024
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 0
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 0
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_online_alter_log_max_size = 134217728
innodb_open_files = 300
innodb_optimize_fulltext_only = OFF
innodb_print_all_deadlocks = ON
innodb_purge_batch_size = 300
innodb_purge_threads = 1
innodb_random_read_ahead = OFF
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_read_only = OFF
innodb_replication_delay = 0
innodb_rollback_on_timeout = OFF
innodb_rollback_segments = 128
innodb_sort_buffer_size = 1048576
innodb_spin_wait_delay = 30
innodb_stats_auto_recalc = ON
innodb_stats_method = nulls_equal
innodb_stats_on_metadata = OFF
innodb_stats_persistent = ON
innodb_stats_persistent_sample_pages = 20
innodb_stats_sample_pages = 8
innodb_stats_transient_sample_pages = 8
innodb_status_output = OFF
innodb_status_output_locks = OFF
innodb_strict_mode = OFF
innodb_support_xa = ON
innodb_sync_array_size = 1
innodb_sync_spin_loops = 100
innodb_table_locks = ON
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000
innodb_undo_logs = 128
innodb_use_native_aio = OFF
innodb_write_io_threads = 4
# Caches & Limits
max_connections = 200
# 这里需要注意的是 来自 backup-my.cnf 中的部分 RDS 特有参数需要注释掉
# InnoDB 的参数大家可以根据自己需要设置，我们这里提供的是测试值，不是调优后的例子
# 由于物理机上要起动多个不同版本的实例，因此 端口、pid 文件、socket 文件均在 datadir 指定的路径下由于 RDS for MySQL 本身不提供 super 权限给应用侧数据库账户，因此即使在 RDS 控制台上创建了名为 root 的高权限账户（root@'%'），恢复到本地仍然需要创建一个拥有 super 权限的本地用户：
下面是创建步骤：
mysql@ecs01$ cd /data/db_data/hins5937443
mysql@ecs01$ vi auto.cnf
[auto]
server_uuid=8653b93d-e776-11e8-b243-506b4b2adfce
mysql@ecs01$ vi mysql-init
grant all privileges on *.* to 'root'@'localhost' identified by 'xxxx' with grant option;
mysql@ecs01$ nohup mysqld_safe --defaults-file=/data/db_data/hins5937443/my.cnf --init-file=/data/db_data/hins5937443/mysql-init &
mysql@ecs01$ mysql -uroot -h127.0.0.1 -pxxxx -P3701
# 创建了这个有 super 权限的本地账户后，就可以按业务需要做其他操作了
# 下次启动不需要带 --init-file 参数- 后记
参考文档： RDS for MySQL 物理备份文件恢复到自建数据库，本文是对该文档的细节补充。
