阿里云服务 + 关注
手机版

RDS for MySQL 5.7 备份恢复为本地实例

  1. 云栖社区>
  2. 阿里云服务>
  3. 博客>
  4. 正文

RDS for MySQL 5.7 备份恢复为本地实例

田杰 2019-01-24 17:05:38 浏览6017 评论1

摘要: RDS for MySQL 5.7 备份恢复为本地实例详细步骤

RDS for MySQL 5.7 备份恢复为本地实例

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%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 参数


3. 后记

参考文档: RDS for MySQL 物理备份文件恢复到自建数据库,本文是对该文档的细节补充。

【云栖快讯】一站式开发者服务,海量学习资源免费学  详情请点击

网友评论