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.
| # | 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) |
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 environmentRestoring 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.rpmThe 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
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.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.
Migrating a Self-built MySQL Database to Alibaba Cloud RDS for MySQL with Minimal Downtime
Alibaba Clouder - February 1, 2021
ApsaraDB - February 19, 2020
Alibaba Clouder - February 25, 2021
Alibaba Clouder - January 27, 2021
ApsaraDB - February 19, 2020
Alibaba Clouder - August 1, 2019
Data Lake Storage Solution
Build a Data Lake with Alibaba Cloud Object Storage Service (OSS) with 99.9999999999% (12 9s) availability, 99.995% SLA, and high scalability
Learn More
Tair
Tair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.
Learn More
Time Series Database (TSDB)
TSDB is a stable, reliable, and cost-effective online high-performance time series database service.
Learn More
Alibaba Cloud Linux
Alibaba Cloud Linux is a free-to-use, native operating system that provides a stable, reliable, and high-performance environment for your applications.
Learn More