1. 本地环境
2. 恢复步骤
3. 后记
近期同学咨询反馈 RDS for MySQL 5.7 备份恢复本地实例的问题比较多, 提供一个恢复样例.
1. 本地环境
# | 项目 | 说明 |
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. 恢复步骤
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=' -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 参数
3. 后记
参考文档: RDS for MySQL 物理备份文件恢复到自建数据库, 本文是对该文档的细节补充.
来源: https://yq.aliyun.com/articles/688805