8.5 Backing Up and Restoring the Database

It is good practice to create regular backups of your OpenStack databases so that you can recover from a failure. You should also perform a backup:

  • Before you update to a new release of Oracle OpenStack for Oracle Linux.

  • Before you redeploy OpenStack services following a configuration change, such as a change of passwords, a change to IP addresses, or a change to the layout of the deployment.

The indications that there might be issues with a database include:

  • When you update to a new release of Oracle OpenStack for Oracle Linux, Ansible errors are displayed.

  • When you deploy Openstack services, there are bootstrap_servicename containers still running on the controller nodes and the log files for these containers contain SQL error messages.

  • When OpenStack services become unresponsive during normal operation and the log files from the services' containers contain MySQL Cluster error messages.

In an OpenStack deployment, the nodes in the control group run the MySQL API and management services (the mysqlcluster_api and mysqlcluster_mgmt containers), and the nodes in the database group run the MySQL NDB Cluster services (the mysqlcluster_ndb container). Depending on how your deployment is configured, the controller nodes may also run the MySQL NDB Cluster. To check the layout of your deployment, use the kollacli group listhosts command on the master node.

Backing up all the OpenStack Databases

  1. Log in to one of the controller nodes in your OpenStack deployment.

    To check that the MySQL API and management containers are running on the selected node: 

    # docker ps -a -f name=mysqlcluster

  2. Stop the mysqlcluster_api container. 

    # docker stop mysqlcluster_api
mysqlcluster_api

  3. Display the status of the MySQL cluster and make a note of the ID of the shut down API node.

    Make a note of the ID of the API node on which you shut down the mysqlcluster_api container. In the following example, the [mysqld(API)] section shows that the API node ID is 203. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @192.0.2.207 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=2 @192.0.2.208 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
[ndb_mgmd(MGM)] 3 node(s)
id=101 @192.0.2.201 (mysql-5.6.28 ndb-7.4.10)
id=102 @192.0.2.202 (mysql-5.6.28 ndb-7.4.10)
id=103 @192.0.2.203 (mysql-5.6.28 ndb-7.4.10)
[mysqld(API)] 3 node(s)
id=201 @192.0.2.201 (mysql-5.6.28 ndb-7.4.10)
id=202 @192.0.2.202 (mysql-5.6.28 ndb-7.4.10)
id=203 (not connected, accepting connect from 192.0.2.203)

  4. Enter single user mode, specifying that only the node ID of the shut down API node is granted access to the database. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'enter single user mode API-node-ID'

    For example: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'enter single user mode 203' 
Connected to Management Server at: localhost:1186 
Single user mode entered 
Access is granted for API node 203 only.

    To check that the database is now in single user mode: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 1: single user mode (mysql-5.6.28 ndb-7.4.10)
Node 2: single user mode (mysql-5.6.28 ndb-7.4.10)

  5. Start the backup, specifying an unused backup ID.

    The backup ID is an integer greater than or equal to 1. The backup ID is optional. If you omit it, the next available number is used automatically. If the backup ID is already used (a backup with this ID exists on an NDB node), the backup fails. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'start backup backup-ID'

    For example: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'start backup 567' 
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 1: Backup 567 started from node 103
Node 1: Backup 567 started from node 103 completed
StartGCP: 53020 StopGCP: 53023
#Records: 17862 #LogRecords: 0
Data: 15880936 bytes Log: 0 bytes

    The backups are created in the /var/lib/kolla/usr/local/mysql/data/BACKUP directory on every NDB node.

    To check that the backup exists on an NDB node: 

    # ls /var/lib/kolla/usr/local/mysql/data/BACKUP/
BACKUP-567
# ls /var/lib/kolla/usr/local/mysql/data/BACKUP/BACKUP-567/
BACKUP-567-0.1.Data BACKUP-567.1.ctl BACKUP-567.1.log

    Note that the backup file names include the NDB node ID.

  6. Copy the backups from each NDB node to a secure location.

    A shared file system accessible from the controller or database nodes is a good location. 

    # scp -r database1:/var/lib/kolla/usr/local/mysql/data/BACKUP/BACKUP-567 .
BACKUP-567.1.log 100% 52 0.1KB/s 00:00
BACKUP-567.1.ctl 100% 1717KB 1.7MB/s 00:00
BACKUP-567-0.1.Data 100% 7727KB 7.6MB/s 00:00 
# scp -r database2:/var/lib/kolla/usr/local/mysql/data/BACKUP/BACKUP-567 .
BACKUP-567.2.ctl 100% 1717KB 1.7MB/s 00:00
BACKUP-567.2.log 100% 52 0.1KB/s 00:00
BACKUP-567-0.2.Data 100% 7811KB 7.6MB/s 00:00

  7. Exit single user mode. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'exit single user mode'
Connected to Management Server at: localhost:1186
Exiting single user mode in progress.
Use ALL STATUS or SHOW to see when single user mode has been exited.

    To check that the database is no longer in single user mode: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 1: started (mysql-5.6.28 ndb-7.4.10)
Node 2: started (mysql-5.6.28 ndb-7.4.10)

  8. Start the mysqlcluster_api container. 

    # docker start mysqlcluster_api
mysqlcluster_api

Restoring a Database From a Backup

Before you restore a database:

  • Ensure that you know the name of the database you want to restore.

    By default, the databases are named after the OpenStack service they support, for example the Nova database is named nova. Check the service_database_name property for the name of the service database.

  • Ensure that you can access the backup files that you will use to restore the database.

    The backup files must be accessible from one of the controller nodes.

  • Ensure that you know the password for the root user in the MySQL cluster.

    The password is stored in database_password property the /etc/kolla/passwords.yml file on the master node. Access to this file is restricted.

  1. Log in to one of the controller nodes in your OpenStack deployment.

    To check that the MySQL API and management containers are running on the selected node: 

    # docker ps -a -f name=mysqlcluster

  2. Drop the faulty OpenStack database and create a new one. 

    # docker exec -ti mysqlcluster_api mysql -u root -p -h host-name \
 -e "drop database database-name"
# docker exec -ti mysqlcluster_api mysql -u root -p -h host-name \
 -e "create database database-name"

    where host-name is the host name or IP address of the controller node you are logged in to, and database-name is the name of the OpenStack database you want to restore. You are prompted for the database password.

    For example: 

    # docker exec -ti mysqlcluster_api mysql -u root -p -h control3 -e "drop database nova"
Enter password:
# docker exec -ti mysqlcluster_api mysql -u root -p -h control3 -e "create database nova"
Enter password:

  3. Stop the mysqlcluster_api container. 

    # docker stop mysqlcluster_api
mysqlcluster_api

  4. Display the status of the MySQL cluster and make a note of the node IDs.

    Make a note of the IDs of the NDB nodes. In the following example, the [ndbd(NDB)] section shows that the NDB node IDs are 1 and 2. The first NDB node is the NDB node ID with the lowest number.

    Make a note of the ID of the API node on which you shut down the mysqlcluster_api container. In the following example, the [mysqld(API)] section shows that the API node ID is 203. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @192.0.2.207 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=2 @192.0.2.208 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
[ndb_mgmd(MGM)] 3 node(s)
id=101 @192.0.2.201 (mysql-5.6.28 ndb-7.4.10)
id=102 @192.0.2.202 (mysql-5.6.28 ndb-7.4.10)
id=103 @192.0.2.203 (mysql-5.6.28 ndb-7.4.10)
[mysqld(API)] 3 node(s)
id=201 @192.0.2.201 (mysql-5.6.28 ndb-7.4.10)
id=202 @192.0.2.202 (mysql-5.6.28 ndb-7.4.10)
id=203 (not connected, accepting connect from 192.0.2.203)

  5. Enter single user mode, specifying that only the node ID of the shut down API node is granted access to the database. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'enter single user mode API-node-ID'

    For example: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'enter single user mode 203' 
Connected to Management Server at: localhost:1186 
Single user mode entered 
Access is granted for API node 203 only.

    To check that the database is now in single user mode: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 1: single user mode (mysql-5.6.28 ndb-7.4.10)
Node 2: single user mode (mysql-5.6.28 ndb-7.4.10)

  6. Copy the directory containing the backup you want to restore to the root of the mysqlcluster_mgmt container. 

    # docker cp BACKUP-backup-ID mysqlcluster_mgmt:/

    For example: 

    # docker cp BACKUP-567 mysqlcluster_mgmt:/

    To check that the backup directory is available in the mysqlcluster_mgmt container: 

    # docker exec -ti mysqlcluster_mgmt /usr/bin/ls /
BACKUP-567  dev   lib64              mysqlcluster-base-source  run          sys
...
# docker exec -ti mysqlcluster_mgmt /usr/bin/ls /BACKUP-567
BACKUP-567-0.1.Data  BACKUP-567.1.ctl  BACKUP-567.2.ctl
BACKUP-567-0.2.Data  BACKUP-567.1.log  BACKUP-567.2.log
...

  7. Restore the database from the backup.

    For the first NDB node only, run the following command: 

    # docker exec -ti mysqlcluster_mgmt ndb_restore -m -r -b backup-ID -n NDB-node-ID \
--restore-privilege-tables --include-databases database-name -c management-IP /BACKUP-backup-ID

    For each of the other NDB nodes, run the following command (omit the -m option flag to recreate the schema): 

    # docker exec -ti mysqlcluster_mgmt ndb_restore -r -b backup-ID -n NDB-node-ID \
--restore-privilege-tables --include-databases database-name -c management-IP /BACKUP-backup-ID

    where backup-ID is the number of the backup you want to restore, NDB-node-ID is the node ID of the NDB node, and management-IP is the IP address of the controller node you are logged in to.

    For example: 

    # docker exec -ti mysqlcluster_mgmt ndb_restore -m -r -b 567 -n 1 \
--restore-privilege-tables --include-databases nova -c 192.0.2.203 /BACKUP-567
Backup Id = 567
Nodeid = 1
backup path = /BACKUP-567
Including Databases: nova
Opening file '/BACKUP-567/BACKUP-567.1.ctl'
...
Restored 27 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK
# docker exec -ti mysqlcluster_mgmt ndb_restore -r -b 567 -n 2 \
--restore-privilege-tables --include-databases nova -c 192.0.2.203 /BACKUP-567
Backup Id = 567
Nodeid = 2
backup path = /BACKUP-567
Including Databases: nova
Opening file '/BACKUP-567/BACKUP-567.2.ctl'
...
Restored 69 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK

  8. Exit single user mode. 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'exit single user mode'
Connected to Management Server at: localhost:1186
Exiting single user mode in progress.
Use ALL STATUS or SHOW to see when single user mode has been exited.

    To check that the database is no longer in single user mode: 

    # docker exec -ti mysqlcluster_mgmt ndb_mgm -e 'all status'
Connected to Management Server at: localhost:1186
Node 1: started (mysql-5.6.28 ndb-7.4.10)
Node 2: started (mysql-5.6.28 ndb-7.4.10)

  9. Start the mysqlcluster_api container. 

    # docker start mysqlcluster_api
mysqlcluster_api

Copyright © 2016, 2017 Oracle and/or its affiliates. All rights reserved. Legal Notices