Openstack Overcloud MariaDB Backup and Restore Process

Solution Verified - Updated

Environment

  • Red Hat Enterprise Linux 7
  • Red Hat OpenStack Platform 6+

Issue

*Disaster recovery backup and restore the Overcloud database.
*the data directories are corrupted affecting all the cluster data integrity.

  • This is not intended to be used for a single node failure as the normal way is just bootstrapping from a working donor.

Resolution

Backing up an overcloud Galera cluster with mysqldump in OpenStack HA

This article describe steps to backup a running Galera cluster in an OpenStack overcloud, monitored with Pacemaker for high availability. It is assumed that Galera is configured to use rsync for State Snapshot Transfers.

Galera and OpenStack

Galera is a synchronous, multi-master clustering solution for MariaDB. In the context of OpenStack, it is what provides high availability of database service in HA deployments of controller nodes.

On typical 3-nodes controller deployments, OpenStack services (e.g. Nova, Neutron...) access the database via HAProxy over a Virtual IP, which then handles load balancing as follows:

  • All incoming database traffic is balanced to a single node among the available ones. The other 2 nodes are effectively acting as hot-standby nodes, always kept in sync by Galera' synchronous replication.
  • When HAProxy detects that the target node is "unavailable", it will select another Galera node to balance to. All existing DB connections will be migrated to the new node lazily when clients use them. As a side effect, each client will recreate a SQL connection.

One strategy to backup a running Galera cluster is to identify one of the idle nodes as the target of the backup, to avoid any impact on the node that is currently servicing the Openstack services. Using mysqldump, we will use the --single-transaction option which ensures that no locks will be used, so impact is minimal in any case.

Locating an idle node

We will do this by connecting to the Galera cluster through the HAProxy proxy server, and then ask the database what host it's running on. From this, we will then use one of our other Galera nodes.

NOTE: From RHOSP12, openstack and its related services are containerized. A user needs to consider following things.

  1. All configuration files are deployed under /var/lib/config-data/puppet-generated/<service name>/. For example, if you want to find nova.conf for nova container, you need to check /var/lib/config-data/puppet-generated/nova/etc/nova/nova.conf.
  2. mysql or other commands need to be run in its container. So, a user needs to use docker exec to the container.

To find out which node is currently targeted by HAProxy, get the VIP HAProxy is listening to:

# grep -A1 mysql /etc/haproxy/haproxy.cfg
listen mysql
  bind 192.0.2.18:3306

Connect to the DB over the VIP to get the name of the targeted Galera node. Use credentials from a user allowed to connect to this VIP. It's wise to have a dedicated user for that.

# mysql -u youruser -p -h 192.0.2.18 -nNE -e "show variables like 'hostname';"
Enter password: ****
*************************** 1. row ***************************
hostname
Overcloud-controller-0.localdomain

One way to do this is to use nova user and password from /etc/nova/nova.conf

[root@overcloud-controller-0 heat-admin]# grep mysql /etc/nova/nova.conf
connection=mysql+pymysql://nova:xvsZqeaJn2fYwMK8NbscAJ6xG@172.16.2.5/nova

[root@overcloud-controller-0 heat-admin]# mysql -u nova -p -h 172.16.2.5 -nNE -e "show variables like 'hostname';"
Enter password: 
*************************** 1. row ***************************
hostname
overcloud-controller-0.localdomain
[root@overcloud-controller-0 heat-admin]#

In the example above, controller-0 is the one currently targeted by HAProxy. So, we can request a backup from either controller-1 or controller-2, but only if they are currently "connected" to the cluster and "in sync".

The state of a Galera node is exposed over an HTTP service on port 9200. This is how HAProxy determines a node's current availability.

# curl overcloud-controller-1.localdomain:9200 overcloud-controller-2.localdomain:9200
Galera cluster node is synced.
Galera cluster node is synced.

In the example above, both nodes are running and in sync. We will arbitrarily choose to backup controller-1.

Backing up the node

While running mysqldump traditionally involves the use of table locks which can negatively impact a Galera cluster's performance, a simple way to avoid this issue is to use the --single-transaction option for mysqldump which eliminates such locking; --single-transaction only applies to InnoDB tables, however Galera only uses InnoDB tables in any case. The only caveat to running --single-transaction is that it will not consistently capture changes made to database schemas (e.g. DDL). However, such statements are not normally in progress on the overcloud unless the Openstack services themselves are being manually upgraded (e.g. 'nova manage db sync').

To perform the backup, we will use mysqldump such that only the Openstack / InnoDB databases are actually backed up in this step. The only other part of the database we would need to restore for the overcloud are the MySQL user accounts and grants, which make use of the MyISAM storage engine, and not InnoDB. These tables would not be properly restored in Galera if restored from a traditional dumpfile, and will need to be backed up in a separate step.

The example below generates a backup of all databases other than the "mysql" database which include at least one InnoDB table.

mysql -u root -e "select distinct table_schema from information_schema.tables where engine='innodb' and table_schema != 'mysql';" -s -N | xargs mysqldump -u root --single-transaction --databases > openstack_database.sql

NOTE: The taken backup will not include the database for nova-placement. It is an expected result and please refer to Why does nova-placement not use its own database? for more details.

Backup all grants and account info from the database.

mysql -u root -e "SELECT CONCAT('\"SHOW GRANTS FOR ''',user,'''@''',host,''';\"') FROM mysql.user where length(user) > 0"  -s -N | xargs -n1 mysql -u root -s -N -e | sed 's/$/;/' > grants.sql

Make sure that all the backups are kept at a remote location and output files are named by suffixing them with date and time of the backup to retrieve them easily.
NOTE: In some RHOSP version, you will get a following error during backup of all grants.

ERROR 1141 (42000) at line 1: There is no such grant defined for user 'root' on host '<controller node name>'

This messages can be safely ignored because root user doesn't need in the DB.

Restore an overcloud Galera cluster in OpenStack HA

Before restoring the database, it's recommended to verify that the existing database in each Galera node has crashed or missing or corrupted beyond recovery. If database is missing from just one node, it may be easier to initiate a resync/replication of the database to that node from a good node rather than following the restoration steps. If galera is working on any of the three nodes, these steps should never be followed.

Please also note that inability to start galera service on one node or all nodes does not always mean that the database has corrupted beyond recovery and you should not follow this steps. It may be a different issue in the galera or mariadb service or pacemaker service that need to resolved using a different method.

Note: It's always recommended to contact Red Hat support before following the restoration steps to reconfirm that is the right solution for the database problems you are facing.

Note: These steps requires outage to database. No openstack service will be able to connect to the database before finishing the restoration process.

Read this before proceeding
Objects that has been deleted since the last backup, needs to be cleaned from the database manually. Otherwise the database may contain objects which doesn't exist.

Check the cluster

For restoration purpose, we assume we have below galera cluster nodes.

node1.example.com 192.168.1.1
node2.example.com 192.168.1.2
node3.example.com 192.168.1.3

Find out the VIP used for database access by Openstack services. This can be done by running below command from any of the node.

# grep -A1 galera /etc/haproxy/haproxy.cfg
listen galera
  bind 192.168.1.10:3306

Can also grep for mysql if galera is not present 

[root@overcloud-controller-1 heat-admin]# grep -A1 mysql /etc/haproxy/haproxy.cfg
listen mysql
  bind 172.16.2.5:3306 transparent
[root@overcloud-controller-1 heat-admin]#

In this example the VIP is 192.168.1.10

Configure iptables and pacemaker

Insert an iptables rule on each controller node to drop connections to database port via the VIP by running below command.

 iptables -I INPUT -d 192.168.1.10 -p tcp --dport 3306 -j DROP

Change the timeout for galera resource in pacemaker to 300 seconds using below command. This step is required only for OSP6. For OSP7 and onwards this is configured by default. This is to make sure that we do not hit the pacemaker timeout during a database sync process before it finishes as the default timeout is very low.

pcs resource update galera op promote timeout=300s on-fail=block

Take mariadb-galera service out of pacemaker control. This need to be done only on one node and can be any one of the controller node.

pcs resource unmanage galera
Update the configuration

Update /etc/my.cnf.d/galera.cnf to not have wsrep_cluster_address set. This need to be done on all nodes. You need to remember the value in wsrep_cluster_address option which will be recovered in latter part of this solution.

grep wsrep_cluster_address /etc/my.cnf.d/galera.cnf
vi /etc/my.cnf.d/galera.cnf

Note: that an installation done by rhel-osp-installer should not have this parameter configured by default. It's possible that this option is changed manually during a troubleshooting attempt to fix a problem with galera and not reverted. We are making sure that is not the case here.

Stop the mariadb service and prepare directories

Stop mariadb service on all nodes. Make sure that his done on each node.

mysqladmin -u root shutdown

step need in case of the data directories are corrupted and keeping a copy of the originals for the RCA investigation
Move existing mariadb data directories and prepare new data directories on all controller nodes.

mv /var/lib/mysql /var/lib/mysql-save
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
chmod 0755 /var/lib/mysql
mysql_install_db --datadir=/var/lib/mysql --user=mysql
chown -R mysql:mysql /var/lib/mysql/
restorecon -R /var/lib/mysql
Start the mariadb locally and setup accounts

Start mariadb locally and set up base accounts. This need to be done on all nodes one by one.

mysqld_safe --wsrep-provider=none &
mysql -u root -p           # Hit Enter when prompted for a password to input an empty password.
grant all on *.* to <clustercheck_user>@localhost identified by '<clustercheck_password>';
set password for 'root'@'localhost' = password('<password from /root/.my.cnf>');
ctrl-D
mysqladmin -u root shutdown

For the "grant all" command, replace <clustercheck_user> and <clustercheck_password> appropriately from /etc/sysconfig/clustercheck. <user> should be the value of MYSQL_USERNAME and <password> should be the value of MYSQL_PASSWORD.

For the "set password" command, replace the root password from /root/.my.cnf. A deployment created using rhel-osp-installer creates a root password and configures it in this file. If /root/.my.cnf does not have a root password and you don't want to set one, you can ignore grant all on *.* to root@localhost identified by '<password from /root/.my.cnf>'; from above.

Make sure that mysql process is stopped on each controller node.

ps -ef | grep mysql

Recover the value in wsrep_cluster_address option.

grep wsrep_cluster_address /etc/my.cnf.d/galera.cnf
vi /etc/my.cnf.d/galera.cnf

NOTE: A sample of the value is wsrep_cluster_address = gcomm://controller-0,controller-1,controller-2.

Bring the galera cluster up with pacemaker. This need to be done only on one node and it can any node in the cluster.

pcs resource manage galera
pcs resource cleanup galera

Make sure that cluster is running. Verify it using below command.

pcs status | grep -C3 galera

A successful output would look like as below.

Master/Slave Set: galera-master [galera]
     Masters: [ pcmk-node1 pcmk-node2 pcmk-node3 ]
Restore the database

Restore the openstack databases. This need to be done only on one node and will be replicated by galera to other nodes automatically.

mysql -u root < openstack_database.sql
mysql -u root < grants.sql

Test clustercheck locally.

/bin/clustercheck

Test clustercheck via xinetd.d:

curl node1:9200
curl node2:9200
curl node3:9200

Cluster should be up and fully restored. Remove the iptables rule from each node for the services to restore access to database.

iptables -D INPUT -d 192.168.1.10 -p tcp --dport 3306 -j DROP

If there are any openstack services show as failed in pcs status due to problems to connect to database while restoring the database, run pcs resource cleanup <name> to recover those services.

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.