All Products
Search
Document Center

How to build a secondary RDS Database on an ECS instance

Last Updated: Dec 28, 2020

Overview

This article describes how to build a backup database on an ECS instance by using the physical backup files of RDS MySQL 5.6/5.7.

Detail

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

Usage notes

  • This topic describes how to use the CentOS 7 operating system and MySQL 5.6 as an example.
  • The version of the user-created local MySQL database must be the same as that of the apsaradb RDS for MySQL database.
  • The ECS instance must be able to access the RDS instance, and we recommend that you use the internal IP address of the RDS instance for building.

Install the MySQL database

  1. Log on to the ECS instance and run the following command to obtain and install the MySQL source installation package:
    wget  https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
    yum localinstall mysql80-community-release-el7-1.noarch.rpm
    Note: for CentOS 6, you need to download and install the mysql80-community-release-el7-1.noarch.rpm installation package.
  2. Run the following commands in sequence to configure and install the Yum source for MySQL 5.6.
    yum -y install yum-utils
    yum-config-manager --disable mysql80-community
    yum-config-manager --enable mysql56-community
    Note:
    • The Yum source that is used to install MySQL 8.0 is installed by default. You need to run the following command to disable the Yum source where MySQL 8.0 is installed by default.
      yum-config-manager --disable mysql80-community
    • If you want to configure the Yum source for installing MySQL 5.7, run the following command.
      yum-config-manager --enable  mysql57-community
  3. Run the following command to install MySQL Server:
    yum -y install mysql-community-server
  4. Edit the /etc/my.cnf file and replace the file content with the following content.
    Note: the server-id cannot be the same as the apsaradb for RDS instance.
    [mysqld]
    innodb_checksum_algorithm=crc32
    innodb_data_file_path=ibdata1:200M:autoextend
    innodb_log_files_in_group=2
    innodb_log_file_size=524288000
    innodb_undo_directory=/var/lib/mysql/
    basedir=/usr datadir=/var/lib/mysql
    innodb_undo_tablespaces=0
    server-id=55555
    log_bin=mysql-log
    gtid_mode=on
    enforce_gtid_consistency=on
    log-slave-updates=1
    relay_log=relay-log
    sql_mode=''
    binlog_format=row
    skip-grant-tables=1
  5. Run the following commands in sequence to configure the Yum source.
    yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
    yum update percona-release
  6. Run the following commands in sequence to view and select the software to be installed.
    yum list | grep percona
    yum -y install percona-xtrabackup.x86_64

Migrate the physical backup files of an apsaradb RDS for MySQL instance to user-created MySQL databases

Notes:

  • We recommend that you use the latest physical backup file. If you use the previous physical backup file, your binary logs may have been cleared, causing the failure to obtain binary logs.
  • For RDS MySQL 5.6 instances created after February 20, 2019, their data backup files are in xbstream format, with qp.xb as the suffix.
  • For more information about migrating physical backup files and operations, see restore data from a physical backup file of an apsaradb RDS for MySQL instance to a user-created database.
  1. Run the following command to download the RDS backup file and rename it to bak_qp.xb.
    wget -c '[$RDS_Backup]' -O bak_qp.xb
    Note:
    • [$RDS_Backup] is the download address of the physical backup file.
    • If the download fails, you must check whether the internal IP address of the current ECS instance is added to the whitelist of the RDS instance.
    • The download address must be enclosed in single quotation marks, otherwise a 403 error will be reported.
  2. Run the following command to unpack the Java package:
    cat bak_qp.xb | xbstream -x -v -C [$DIR]
    Note:[$DIR] is the absolute path of the data directory of the user-created MongoDB databases.
  3. Run the following command to decompress the. Zip file:
    innobackupex --decompress --remove-original [$DIR]
  4. Run the following command to restore the decompressed backup file.
    innobackupex --defaults-file=[$DIR]/backup-my.cnf --apply-log [$DIR]
  5. Run the following command to modify the permissions.
    chown -R mysql:mysql /var/lib/mysql
  6. Run the following command to start MySQL:
    systemctl start mysql
  7. Run the following mysql command to go to the MySQL command-line interface.
  8. Run the following SQL statements and confirm whether users with the user of root and host of 127.0.0.1 exist:
    Note: For the MySQL 5.7 version, confirm that the aliyun_root account exists.
    select user,host from mysql.user;
  9. Run the following SQL statement to update the password of the root user:
    UPDATE user SET password=PASSWORD("[$Password]") WHERE user='root' and host = '127.0.0.1';
    Note:[$Password] is your custom Password.
  10. Run the following SQL statement to refresh the permissions:
    flush privileges;
  11. Exit the database command-line interface and delete the /etc/my.cnf parameter in the skip-grant-tables=1 configuration file.
  12. Run the following command to restart MySQL and make sure that you can log on to the database as the logon account of the RDS instance.
    systemctl restart mysql

Set up a secondary database

  1. Run the following command to access the MySQL command-line interface as the root user:
    mysql -uroot -h127.0.0.1 -p[$Password]
  2. Run the following SQL statements in sequence to delete five tables:
    use mysql; 
    drop table slave_master_info;
    drop table slave_relay_log_info;
    drop table slave_worker_info;
    drop table innodb_index_stats;
    drop table innodb_table_stats;
  3. Run the following SQL statement to generate table data:
    source /usr/share/mysql/mysql_system_tables.sql; 
  4. Then, exit the database command-line interface and restart MySQL.
  5. View and record the logs statement in the set global gtid_purged.
  6. Use the root account to go to the MySQL command-line interface and execute the resetmaster; SQL statement, and then run the set global gtid_purged SQL statement recorded in the previous step.
  7. Run the following SQL statement to reset the slave:
    reset slave;
  8. Run the following SQL statement to configure master-slave replication:
    change master to master_host = '[$Host]', master_port = [$Port], master_user = '[$User]', master_password='[$Password]', master_auto_position = 1;
    Note:
    • [$Host] is the IP address of the RDS instance.
    • [$Port] is the Port number of the RDS instance.
    • [$User] is the account for the RDS instance.
    • [$Password] is the Password used to log on to the apsaradb for RDS instance.
  9. Run the following SQL statements in sequence to confirm that the values of Slave_IO_Running and Slave_SQL_Running are both Yes:
    start slave;
    show slave status\G
    The following command output is returned.

Application scope

  • ApsaraDB RDS instance
  • Elastic Compute Service