Restore a MySQL database¶

You may need to restore a MySQL database after a hardware or software failure or if you want to create a clone of the existing database from a backup.

To restore a MySQL database using Xtrabackup:

1. Log in to the Salt Master node.

2. In the cluster/infra/backup/client_mysql.yml file, add the following configuration for the Xtrabackup client. If the file does not exist, edit cluster/openstack/database/init.yml.

   parameters:
     xtrabackup:
       client:
         enabled: true
         restore_full_latest: 1
         restore_from: remote
   

   where:

   • restore_full_latest can have the following values: 1 or 2. 1 means restoring the database from the last complete backup and its increments. 2 means restoring the second latest complete backup and its increments.
   • restore_from can have the following values: local or remote. The remote value uses scp to get the files from the xtrabackup server.

3. Proceed with either automatic restore steps using the Jenkins web UI pipeline or with manual restore steps:

   • Automatic restore steps:

     1. Add the upgrade pipeline to DriveTrain:

        1. Verify that the following lines are present in cluster/cicd/control/leader.yml:

           classes:
           - system.jenkins.client.job.deploy.galera_verify_restore
           

        2. Run the salt -C 'I@jenkins:client' state.sls jenkins.client state.

     2. Log in to the Jenkins web UI.

     3. Open the Verify and Restore Galera pipeline.

     4. Specify the following parameters:

        Parameter	Description and values
        RESTORE_TYPE	Set job execution type. Select ONLY_RESTORE.
        SALT_MASTER_CREDENTIALS	The Salt Master credentials to use for connection, defaults to salt.
        SALT_MASTER_URL	The Salt Master node host URL with the salt-api port, defaults to the jenkins_salt_api_url parameter. For example, http://172.18.170.27:6969.

     5. Click Deploy.

     6. Open the Console Output of the build from the navigation menu to control the deployment progress and manually confirm actions when prompted during the job execution.

     7. Once the Verify and Restore Galera pipeline finishes successfully, revert the changes made in cluster/openstack/database/init.yml in the step 2.

   • Manual restore steps:

     1. Stop the mysql service on the MySQL Galera Database dbs02 and dbs03 nodes:

        salt -C 'I@galera:slave' service.stop mysql
        

     2. Remove the MySQL log files from the MySQL Galera Database dbs02 and dbs03 nodes:

        salt -C 'I@galera:slave' cmd.run 'rm /var/lib/mysql/ib_logfile*'
        

     3. Stop the mysql service on the MySQL Galera Database Master node:

        salt -C 'I@galera:master' service.stop mysql
        

     4. Log in to the MySQL Galera Database Master node.

     5. Replace the wsrep_cluster_address row in /etc/mysql/my.cnf with the following:

        wsrep_cluster_address="gcomm://"
        

     6. Log in to the Salt Master node.

     7. Move the MySQL database files to a new location /root/mysql/mysql.bak/ on the MySQL Galera Database Master node:

        salt -C 'I@galera:master' cmd.run 'mkdir -p /root/mysql/mysql.bak/'
        salt -C 'I@galera:master' cmd.run 'mv /var/lib/mysql/* /root/mysql/mysql.bak'
        salt -C 'I@galera:master' cmd.run 'rm /etc/salt/.galera_bootstrap'
        

     8. Verify that the MySQL database files are removed from /var/lib/mysql/ on the MySQL Galera Database Master node:

        salt -C 'I@galera:master' cmd.run 'ls /var/lib/mysql/'
        salt -C 'I@galera:master' cmd.run 'ls -ld /var/lib/mysql/.?*'
        

     9. Log in to the MySQL Galera Database Master node where the restore operation occurs.

     10. Run the following state that restores the databases and creates a file in /var/backups/mysql/xtrabackup/dbrestored.

         Mirantis recommends running the following command using Linux GNU Screen or alternatives.

         salt-call state.sls xtrabackup.client.restore
         

         If you rerun the state, it will not restore the database again. To repeat the restore procedure, first delete the /var/backups/mysql/xtrabackup/dbrestored file and then rerun the above xtrabackup state again.

     11. Log in to the Salt Master node.

     12. Verify that the MySQL database files are present again on the MySQL Galera Database Master node:

         salt -C 'I@galera:master' cmd.run 'ls /var/lib/mysql/'
         

     13. Start the mysql service on the MySQL Galera Database Master node:

         salt -C 'I@galera:master' service.start mysql
         

         Note

         This process takes a certain amount of time and does not provide an immediate output.

     14. Start the mysql service on the MySQL Galera Database dbs02 and dbs03 nodes from the Salt Master node:

         salt -C 'I@galera:slave' service.start mysql
         

         Note

         This process takes a certain amount of time and does not provide an immediate output.

     15. Verify that all MySQL Galera Database nodes joined the Galera cluster:

         salt -C 'I@galera:master' mysql.status | grep -A1 wsrep_cluster_size
         

     16. Revert the changes made in cluster/openstack/database/init.yml in the step 2 and in /etc/mysql/my.cnf in the step 5.