All Products
Search
Document Center

Restore data from a physical backup file of an apsaradb RDS for MySQL instance in the Ubuntu operating system

Last Updated: Dec 10, 2020

Overview

This article describes how to restore apsaradb RDS for MySQL physical backup files in a Ubuntu 16.04 system.

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.

During the recovery operation, check whether the data directory is /var/lib/mysql. The following are the recovery operations for two situations.

The data directory is /var/lib/mysql.

Perform the following steps to restore data from a backup file:

Note: switch to the root user before running the command in the instance.

  1. Log on to the instance and run the following command to add your personal software package document to the source list:
    sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe'
    Note: You can also directly put deb http://archive.ubuntu.com/ubuntu The trusty Union field is added to the /etc/apt/sources.list file.
  2. Run the following command to update the apt-get Database:
    apt-get update
  3. Run the following command to unload mysql-common software package:
    apt-get autoremove mysql-common
  4. Run the following commands to verify that MySQL 5.6 version does not exist in the system:
    apt-cache search mysql | grep mysql-server
  5. Run the following command to install mysql-server-5.6 software package.
    apt-get install mysql-server-5.6
  6. Run the following command to check whether a MySQL process exists in the system and record the process PID if it does.
    ps -ef |grep mysql
  7. Run the following command to terminate the MySQL process:
    kill -9 [$MySQL_PID]
    Note:[$MySQL_PID] is the PID of the MySQL process you obtained in the previous step.
  8. Run the following command to delete the data directory of the database:
    rm -rf /var/lib/mysql/*
    Note: if the server already uses this directory as the database Directory, do not use this server for recovery operations.
  9. Run the following command to download the installation package.
    wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
  10. Run the following command to install the installation package.
    sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
  11. Run the following command to install the percona-xtrabackup-24 file.
    apt-get install percona-xtrabackup-24
  12. Run the following command to download the installation package.
    wget -c '[$Bak_File]' -O [$Custom_File].tar.gz
    Note:
    • [$Bak_File] is the external download URL data backup files.
    • [$Custom_File] is the custom file name.
  13. Run the following command to edit the my5.6.cnf file in text format:
    vi /etc/mysql/conf.d/my5.6.cnf
  14. Add the following content to the my5.6.cnf file.
    [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=999098802
    skip-grant-tables=1
    sql_mode=''
    socket=/var/lib/mysql/mysql.sock
    log-error=/var/lib/mysql/error1.log
    explicit_defaults_for_timestamp=true
  15. Run the following command to decompress the package to the specified directory.
    tar -zxvf a.tar.gz -C /var/lib/mysql
    Note: assume that the a.tar.gz package is downloaded to the /root directory.
  16. Run the following commands in sequence to restore the decompressed backup file.
    cd /var/lib/mysql   
    innobackupex --defaults-file=/etc/mysql/conf.d/my5.6.cnf --apply-log /var/lib/mysql
  17. Run the following command to change the owner of the backup-my.cnf file to a user with permissions to manage the user-created MySQL instance:
    chown -R mysql:mysql .
  18. Run the following commands to verify that the MySQL process can be started:
    mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql
    Note: the configuration file my5.6.cnf contains the skip-grant-tables=1 parameter. Therefore, a password is not required to start the MySQL process.
  19. Run the following command to go to the MySQL interactive page.
    mysql --socket=/var/lib/mysql/mysql.sock
  20. Run the following SQL statement to refresh the database permission table:
    flush privileges;
  21. Run the following SQL statements to set the username and password.
    set PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('[$Password]');
    Note: [$Password] is the Password you set for the root user.
  22. Run the following SQL statement to exit the database:
    exit
  23. Run the following command to obtain information about the MySQL process:
    ps -ef |grep mysql
  24. Run the following command to terminate the MySQL process:
    kill -9 [$PID]
    Note:[$PID] is the PID of the MySQL process obtained in the previous step.
  25. Run the following command to edit the my5.6.cnf file in text format:
    vi /etc/mysql/conf.d/my5.6.cnf
  26. On the text editing page, comment out the skip-grant-tables=1 field.
  27. Run the following command to start the MySQL process:
    mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql
  28. Run the following command and enter the password and account to confirm that you can log on to the MySQL interactive interface.
    mysql -uroot -h127.0.0.1 -p[$Password] --socket=/var/lib/mysql/mysql.sock

The data directory is not /var/lib/mysql.

Perform the following steps to restore data from a backup file:

Note: switch to the root user before running the command in the instance.

  1. Log on to the instance and run the following command to add your personal software package document to the source list:
    sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe'
    Note: You can also directly put deb http://archive.ubuntu.com/ubuntu The trusty Union field is added to the /etc/apt/sources.list file.
  2. Run the following command to update the apt-get Database:
    apt-get update
  3. Run the following command to unload mysql-common software package:
    apt-get autoremove mysql-common
  4. Run the following commands to verify that MySQL 5.6 version does not exist in the system:
    apt-cache search mysql | grep mysql-server
  5. Run the following command to install mysql-server-5.6 software package.
    apt-get install mysql-server-5.6
  6. Run the following command to check whether a MySQL process exists in the system and record the process PID if it does.
    ps -ef |grep mysql
  7. Run the following command to terminate the MySQL process:
    kill -9 [$MySQL_PID]
    Note:[$MySQL_PID] is the PID of the MySQL process you obtained in the previous step.
  8. Run the following command to delete the specified directory.
    rm -rf /var/lib/mysql
  9. Run the following command to download the installation package.
    wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
  10. Run the following command to install the installation package.
    sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
  11. Run the following command to install the percona-xtrabackup-24 file.
    apt-get install percona-xtrabackup-24
  12. Run the following command to download the installation package.
    wget -c '[$Bak_File]' -O [$Custom_File].tar.gz
    Note:
    • [$Bak_File] is the external download URL data backup files.
    • [$Custom_File] is the custom file name.
  13. Run the following command to create a data directory.
    mkdir /data/mysql -p
  14. Run the following command to change the directory owner and group.
    chown -R mysql:mysql /data/mysql
  15. Run the following command to authorize the Data Directory:
    chmod 700 /data/mysql
  16. Run the following command to edit the my5.6.cnf file in text format:
    vi /etc/mysql/conf.d/my5.6.cnf
  17. Add the following content to the my5.6.cnf file.
    [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=/data/mysql/
    basedir=/usr
    datadir=/data/mysql
    innodb_undo_tablespaces=0
    server_id=999098802
    skip-grant-tables=1
    sql_mode=''
    socket=/data/mysql/mysql.sock
    log-error=/data/mysql/error1.log
    explicit_defaults_for_timestamp=true
  18. Run the following command to decompress the package to the specified directory.
    tar -zxvf a.tar.gz -C /data/mysql
    Note: assume that the a.tar.gz package is downloaded to the /root directory.
  19. Run the following commands in sequence to restore the decompressed backup file.
    cd /data/mysql   
    innobackupex --defaults-file=/etc/mysql/conf.d/my5.6.cnf --apply-log /data/mysql
  20. Run the following command to change the owner of the backup-my.cnf file to a user with permissions to manage the user-created MySQL instance:
    chown -R mysql:mysql .
  21. Run the following command to create a short-lived connection:
    ln -s /data/mysql /var/lib/mysql
  22. Run the following command to edit the specified file.
    vi /etc/apparmor.d/tunables/alias
  23. On the edit page, add the following code.
    alias /var/lib/mysql/ -> /data/mysql/,
  24. Run the following command to reload the configuration file:
    sudo /etc/init.d/apparmor reload
  25. Run the following commands to verify that the MySQL process can be started:
    mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql
    Note: the configuration file my5.6.cnf contains the skip-grant-tables=1 parameter. Therefore, a password is not required to start the MySQL process.
  26. Run the following command to go to the MySQL interactive page.
    mysql --socket=/data/mysql/mysql.sock
  27. Run the following SQL statement to refresh the database permission table:
    flush privileges;
  28. Run the following SQL statements to set the username and password.
    set PASSWORD FOR 'root'@'127.0.0.1'=PASSWORD('[$Password]');
    Note: [$Password] is the Password you set for the root user.
  29. Run the following SQL statement to exit the database:
    exit
  30. Run the following command to obtain information about the MySQL process:
    ps -ef |grep mysql
  31. Run the following command to terminate the MySQL process:
    kill -9 [$PID]
    Note:[$PID] is the PID of the MySQL process obtained in the previous step.
  32. Run the following command to edit the my5.6.cnf file in text format:
    vi /etc/mysql/conf.d/my5.6.cnf
  33. On the text editing page, comment out the skip-grant-tables=1 field.
  34. Run the following command to start the MySQL process:
    mysqld --defaults-file=/etc/mysql/conf.d/my5.6.cnf --user=mysql
  35. Run the following command and enter the password and account to confirm that you can log on to the MySQL interactive interface.
    mysql -uroot -h127.0.0.1 -p[$Password] --/data/mysql/mysql.sock

Application scope

  • ApsaraDB RDS for MySQL