Restoring Backup Data from an ApsaraDB RDS for MySQL 5.7 Instance to a User-created Database Instance

By Tian Jie

Recently, some users reported issues in restoring backup data from an ApsaraDB RDS for MySQL 5.7 instance to a user-created database instance. This article aims to address all such issues by demonstrating a restoration example.

1) Local Environment

1.1 Install MySQL DB

To facilitate migration and installation, this article employs the independent TAR version of the Linux OS, as shown in the following figure. Click here to download mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz.

Consider the following installation procedure.

root@ecs01# groupadd -g 500 mysqlroot@ecs01# useradd -u 500 -g 500 -G disk -m -d /home/mysql mysqlroot@ecs01# passwd mysqlroot@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/mysql57roto@ecs01# rm -f mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz root@ecs01# su - mysqlmysql@ecs01$ vi .bashrc# add below 2 lines 
export PATH=$BASE_PATH:/usr/local/mysql57/binmysql@ecs01$ . .bashrc# Please configure the variable BASE_PATH according to your local environment

1.2 Install XtraBackup

Restoring data from an ApsaraDB RDS for MySQL 5.7 instance to a user-created database instance requires XtraBackup 2.4. Click here to download Percona-XtraBackup-2.4.13-r3e7ca7c-el6-x86_64-bundle.tar, as shown in the following figure.

Follow the installation procedure shown below.

root@ecs01# yum -y install libevroot@ecs01# yum -y install rsyncroot@ecs01# yum -y install perl-DBD-MySQLroot@ecs01# cd /data/soft/percona/xtraback/2.4/root@ecs01# tar xpf Percona-XtraBackup-2.4.13-r3e7ca7c-el6-x86_64-bundle.tarroot@ecs01# rpm -Uvh percona-xtrabackup-24-2.4.13-1.el6.x86_64.rpm

2) Restoration Procedure

2.1 Restore Backup Sets

The ApsaraDB for RDS console provides backup sets for ApsaraDB for RDS (RDS) instances, which are available for download over a public network or standard internal network.

This article uses an ApsaraDB RDS for MySQL 5.7 High-availability instance and the hins5937443_data_20190122042427.tar.gz file.

The following snippet shows the specific procedure.

mysql@ecs01$ cd /data/db_data; mkdir hins5937443mysql@ecs01$ wget -c 'http://xxx/hins5937443_data_20190122042427.tar.gz?OSSAccessKeyxxxxI%3D' -O hins5937443_data_20190122042427.tar.gzmysql@ecs01$ tar -izxvf hins5937443_data_20190122042427.tar.gz -C ./hins5937443mysql@ecs01$ cd hins5937443mysql@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!
# The "completed OK!" means the backup set restoration finishes successfully.
# Please be noted all steps are conducted as mysql OS user, which is the default OS user of mysqld process.
# The "mkdir" step has to be performed as mysql OS user, to ensure mysqld will be able to access the folder

2.2 Start an Instance

After restoring the backup set file, pull up the MySQL process and log on to the MySQL instance.

Adjust the parameter configuration file, create the root@localhost super permission account, and set a password for this account.

Now, my.cnf as shown below.

# This MySQL options file was generated by innobackupex.# The MySQL server
# 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/
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
# Replication
server-id = 3701
# paramters from RDS
#innodb_log_checksum_algorithm=strict_crc32# RDS parameter
#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
#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
# Some RDS specific variables introduced by backup-my.cnf need to be commented out
# The variables can be set according to your needs.The configuration we provide here is for testing only, not an example of tuning
# As multiple instances of different versions are running on the physical box, the pid file and socket file are all under the path specified by the variable "datadir"

ApsaraDB RDS for MySQL does not itself provide super permissions for application-side database accounts. Therefore, even if a high-permission account named root (root@'%') is created in the ApsaraDB for RDS console, it's still require creating a local user with super permission to restore data to a user-created database instance.

The following snippet shows the procedure for creating a local user with super permissions.

mysql@ecs01$ cd /data/db_data/hins5937443mysql@ecs01$ vi auto.cnf
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# Do not take the "--init-file" opinion with next instance startup.

3) Postscript

This article is a detailed supplement to Use a physical backup file to restore data of an ApsaraDB RDS MySQL instance to a user-created database.

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store