Restoring Data to a Self-Managed MySQL Database

Scenarios

Prerequisites

Procedure

1. Download the qpress RPM file qpress-11-1.el7.x86_64.rpm. Enterprise Linux 7 (including CentOS 7, RHEL 7, Rocky Linux 7, and AlmaLinux 7) is used as an example.

   For details about RPM files of other OSs, see https://repo.percona.com/yum/release/.

2. Upload the qpress RPM file to the ECS.
3. Install qpress on the ECS.

   rpm -ivh qpress-11-1.el7.x86_64.rpm

4. Download XtraBackup from the website, for example, percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm.
   Notice

   • For MySQL 5.6 and 5.7, download XtraBackup 2.4.9 or later.
   • For MySQL 8.0, download XtraBackup 8.0.*.

5. Upload XtraBackup to the ECS.
6. Install XtraBackup on the ECS.

   rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm --nodeps --force

7. On the ECS, decompress the full backup file that has been downloaded.
   1. Create a temporary directory, for example, mysqlbackup.

      mkdir mysqlbackup

   2. Decompress the file to the temporary directory.
      • For MySQL 5.6 and 5.7:

        xbstream -x -p 4 < ./Full_backup_file.qp -C ./mysqlbackup/

        innobackupex --parallel 4 --decompress ./mysqlbackup

      • For MySQL 8.0:

        xbstream -x -p 4 < ./Full_backup_file.qp -C ./mysqlbackup/

        xtrabackup --parallel 4 --decompress --target-dir=./mysqlbackup

   3. Delete the .qp backup file.

      find ./mysqlbackup/ -name '*.qp' | xargs rm -f

8. Apply the log.
   • For MySQL 5.6 and 5.7, run innobackupex --apply-log ./mysqlbackup.
   • For MySQL 8.0, run xtrabackup --prepare --target-dir=./mysqlbackup.

9. Back up data.
   1. Stop MySQL database services.

      service mysql stop

      Note

      For MySQL 5.7, run the following command to stop MySQL database services:

      /bin/systemctl stop mysqld.service

   2. Back up the original database directory.

      mv /var/lib/mysql/data/var/lib/mysql/data_bak

   3. Create a new database directory and change the permissions.

      mkdir /var/lib/mysql/data

      chown mysql:mysql /var/lib/mysql/data

10. Copy the full backup file and change directory permissions.
    • For MySQL 5.6 and 5.7, run innobackupex --defaults-file=/etc/my.cnf --copy-back ./mysqlbackup.
    • For MySQL 8.0, run xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=./mysqlbackup.

    chown -R mysql:mysql /var/lib/mysql/data

11. Start the self-managed MySQL server.

    service mysql start

    Note

    • For MySQL 5.7, run /bin/systemctl start mysqld.service to start the MySQL server.

12. Log in to the database and view the restoration result.

    mysql -u root

    show databases

    Figure 1 Viewing the restoration result

    
    
    Note

    If you forget the password, shut down the server. Add skip_grant_tables=ON to the my.cnf file and restart the server. In this way, you can log in to the database without entering the password. After the login, you need to reset the password.

    After the password is reset, shut down the server again, delete skip_grant_tables=ON from the my.cnf file, and restart the server.

13. If you need to upgrade the server to a higher community version, perform the following operations to delete the following stored procedures from the self-managed database after the restoration is complete to avoid compatibility issues:
    1. Log in as user rdsAdmin without a password by referring to 12. Run the following command to grant privileges to user root:

       GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
       
       

    2. Log in as user root and do as follows:

       use mysql;
       DROP PROCEDURE IF EXISTS rebuild_sql_filter_rules;
        DROP PROCEDURE IF EXISTS add_sql_filter;
        DROP PROCEDURE IF EXISTS add_sql_select_filter;
        DROP PROCEDURE IF EXISTS add_sql_update_filter;
        DROP PROCEDURE IF EXISTS add_sql_delete_filter;
        DROP PROCEDURE IF EXISTS add_sql_insert_filter;
        DROP PROCEDURE IF EXISTS drop_sql_filter;
        DROP PROCEDURE IF EXISTS drop_sql_select_filter;
        DROP PROCEDURE IF EXISTS drop_sql_update_filter;
        DROP PROCEDURE IF EXISTS drop_sql_delete_filter;
        DROP PROCEDURE IF EXISTS drop_sql_insert_filter;
        DROP PROCEDURE IF EXISTS add_index_hint;
        DROP PROCEDURE IF EXISTS drop_index_hint;
        DROP PROCEDURE IF EXISTS drop_index_hints;