作者：田傑
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 實體備份檔案恢複到自建資料庫，本文是對該文檔的細節補充。