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

This article demonstrates how to restore backup data from an ApsaraDB RDS for MySQL 5.7 instance to a user-created database instance using a comprehensive example.

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

#	Item	Description
1	Operating system (OS)	Official system image in your Elastic Compute Service (ECS) instance: CentOS 6.8 64-bit Note: You must use a Linux system to restore data to a user-created database instance.
2	MySQL DB	MySQL Community Server 5.7.24 (64-bit) Linux - Generic (glibc 2.12) (x86, 64-bit), Compressed TAR Archive
3	XtraBackup	Percona XtraBackup 2.4.13 (Red Hat Enterprise Linux, CentOS, or Oracle Linux 6)

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 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

# 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 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) 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 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!

# 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
[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

# 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/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

# 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.

Community

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

1) Local Environment

1.1 Install MySQL DB

1.2 Install XtraBackup

2) Restoration Procedure

2.1 Restore Backup Sets

2.2 Start an Instance

3) Postscript

Read next post:

Jack008

You may also like

Comments

Jack008

Related Products

Data Lake Storage Solution

Tair

Time Series Database (TSDB)

Database Overview