Federated MariaDB Enterprise Spider Topology Backup and Restore
This page is part of MariaDB's Documentation.
The parent of this page is: Federated MariaDB Enterprise Spider Topology Operations
Topics on this page:
Overview
When using Spider in the Federated MariaDB Enterprise Spider topology, backup and restore operations can be performed using MariaDB Backup or MariaDB Dump. These operations must be performed on the Spider Node and the Data Node.
MariaDB Backup
MariaDB Backup can be used to perform backup operations on Spider deployments.
Backup Spider with MariaDB Backup
MariaDB Backup can be used to create a physical backup of a Federated MariaDB Enterprise Spider topology. MariaDB Backup must be used to backup the Spider Node and the Data Node. The Spider Node and Data Node must be locked to guarantee that the backups of the Spider Node and Data Node are consistent.
The backup of the Spider Node contains:
Physical backup for Spider Tables
Physical backup for tables that use InnoDB, Aria, MyISAM, and MyRocks
Physical backup for any other database objects on the Spider Node
The backup of the Data Node contains:
Physical backup for Data Tables
Physical backup for tables that use InnoDB, Aria, MyISAM, and MyRocks
Physical backup for any other database objects on the Data Node
The following procedure shows how to take a consistent backup of a Spider Node and a Data Node deployed in a Spider Federated MariaDB Enterprise Spider topology.
On the Spider Node and on the Data Node, create a user account to perform the backup using the CREATE USER and GRANT statements.
For MariaDB Enterprise Server 10.5 and later:
CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mb_passwd'; GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';
For MariaDB Enterprise Server 10.4 and earlier:
CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mb_passwd'; GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariadb-backup'@'localhost';
On the Data Node, grant the user account Spider uses to operate on the Data Node sufficient privileges to lock the Data Tables using the GRANT statement.
For example, on the
hq_server
Data Node:GRANT LOCK TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.2';
On the Spider Node, acquire a read lock on Spider Tables using the LOCK TABLES statement:
LOCK TABLES spider_federated_sales.invoices READ;
Keep this session open during the rest of the procedure.
The read lock will propagate to the Data Tables on the Data Node. The read locks will prevent the Data Tables from changing during the backup, so the backups of the Spider Node and the Data Node are consistent.
On the Data Node, perform the backup using MariaDB Backup.
With MariaDB Backup 10.4 and later, use the
mariadb-backup
command:$ sudo mariadb-backup --backup \ --target-dir=/data/backups/full \ --user=mariadb-backup \ --password='mb_passwd'
For MariaDB Backup 10.3 and older, use the legacy command:
$ sudo mariabackup --backup \ --target-dir=/data/backups/full \ --user=mariadb-backup \ --password='mb_passwd'
When the Data Node backup is complete, perform the backup on the Spider Node using MariaDB Backup.
With MariaDB Backup 10.4 and later, use the
mariadb-backup
command:$ sudo mariadb-backup --backup \ --target-dir=/data/backups/full \ --user=mariadb-backup \ --password='mb_passwd'
For MariaDB Backup 10.3 and older, use the legacy command:
$ sudo mariabackup --backup \ --target-dir=/data/backups/full \ --user=mariadb-backup \ --password='mb_passwd'
When the Spider Node backup is complete, release the table locks in your original session using the UNLOCK TABLES statement:
UNLOCK TABLES;
On the Spider Node and the Data Node, prepare each of the backups using MariaDB Backup.
With MariaDB Backup 10.4 and later, use the
mariadb-backup
command:$ sudo mariadb-backup --prepare \ --target-dir=/data/backups/full
For MariaDB Backup 10.3 and older, use the legacy command:
$ sudo mariabackup --prepare \ --target-dir=/data/backups/full
The Spider Node and the Data Node now have a complete backup of the data directory. Backups should be tested to confirm they are complete and consistent.
Restoring Spider with MariaDB Backup
MariaDB Backup can restore a Federated MariaDB Enterprise Spider topology from a backup taken with MariaDB Backup.
On the Spider Node and on the Data Node, stop the MariaDB Server process:
$ sudo systemctl stop mariadb
On the Spider Node and on the Data Node, empty the data directory:
$ sudo rm -fr /var/lib/mysql/*
On the Spider Node and on the Data Node, restore the backup for that server using MariaDB Backup.
With MariaDB Backup 10.4 and later, use the
mariadb-backup
command:$ sudo mariadb-backup --copy-back \ --target-dir=/data/backups/full
For MariaDB Backup 10.3 and older, use the legacy command:
$ sudo mariabackup --copy-back \ --target-dir=/data/backups/full
On the Spider Node and on the Data Node, confirm that the restored files are owned by the user that owns the MariaDB Server process:
$ sudo chown -R mysql:mysql /var/lib/mysql
On the Spider Node and on the Data Node, start the MariaDB Server process:
$ sudo systemctl start mariadb
On the Spider Node, query a Spider Table to test it:
SELECT * FROM spider_hq_sales.invoices;
+-----------+------------+-------------+----------------------------+---------------+----------------+ | branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method | +-----------+------------+-------------+----------------------------+---------------+----------------+ | 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD | | 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER | | 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH | +-----------+------------+-------------+----------------------------+---------------+----------------+
MariaDB Dump
MariaDB Dump generates backup files containing the SQL statements necessary to recreate the database. MariaDB Dump is included with MariaDB Enterprise Server and can be used to backup databases in Spider deployments. The MariaDB Client can then be used to restore databases from a MariaDB Dump backup.
Backing Up Spider with MariaDB Dump
MariaDB Dump can be used to create a logical backup of a Federated MariaDB Enterprise Spider topology. MariaDB Dump must be used to backup the Spider Node and the Data Node. The Spider Node and Data Node must be locked to guarantee that the backups of the Spider Node and Data Node are consistent.
The backup of the Spider Node contains:
Table definitions for Spider Tables
Table definitions for all tables
Table data for tables that use InnoDB, Aria, MyISAM, and MyRocks
Definitions for any other user accounts, privileges, views, and triggers on the Spider Node
The backup of the Data Node contains:
Table definitions for Data Tables
Table definitions for all tables
Table data for tables that use InnoDB, Aria, MyISAM, and MyRocks
Definitions for any other user accounts, privileges, views, and triggers on the Data Node
The following procedure shows how to take a consistent backup of a Spider Node and a Data Node deployed in a Federated MariaDB Enterprise Spider topology.
On the Spider Node and on the Data Node, create a user account to perform the backup using the CREATE USER and GRANT statements:
CREATE USER 'mariadb-dump'@'localhost' IDENTIFIED BY 'md_passwd'; GRANT SELECT, INSERT, SHOW VIEW, TRIGGER, CREATE, ALTER, EVENT, RELOAD, LOCK TABLES ON *.* TO 'mariadb-dump'@'localhost';
On the Data Node, grant the user account Spider uses to operate on the Data Node sufficient privileges to lock the Data Tables using the GRANT statement.
For example, on the
hq_server
Data Node:GRANT LOCK TABLES ON hq_sales.* TO 'spider_user'@'192.0.2.2';
On the Spider Node, acquire a read lock on Spider Tables using the LOCK TABLES statement:
LOCK TABLES spider_federated_sales.invoices READ;
Keep this session open during the rest of the procedure.
The read lock will propagate to the Data Tables on the Data Node. The read locks will prevent the Data Tables from receiving any changes during the backup, which ensures the backups of the Spider Node and the Data Node are consistent.
On the Data Node, perform the backup using MariaDB Dump.
With MariaDB Dump 10.4 and later, use the
mariadb-dump
command:$ mariadb-dump \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --all-databases \ --single-transaction \ --default-character-set=utf8mb4 \ > mariadb_dump.sql
For MariaDB Dump 10.3 and older, use the legacy command:
$ mysqldump \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --all-databases \ --single-transaction \ --default-character-set=utf8mb4 \ > mariadb_dump.sql
When the Data Node has been backed up, perform the backup on the Spider Node using MariaDB Dump.
With MariaDB Dump 10.4 and later, use the
mariadb-dump
command:$ mariadb-dump \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --all-databases \ --single-transaction \ --default-character-set=utf8mb4 \ > mariadb_dump.sql
For MariaDB Dump 10.3 and older, use the legacy command:
$ mysqldump \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --all-databases \ --single-transaction \ --default-character-set=utf8mb4 \ > mariadb_dump.sql
When the Spider Node backup is complete, release the table locks in your original session using the UNLOCK TABLES statement:
UNLOCK TABLES;
The Spider Node and the Data Node now each have a mariadb_dump.sql
backup file. This backup file contains the SQL statements needed to recreate the schema on the Spider Node and the schema and data on the Data Node. Backups should be tested to confirm they are complete and consistent.
Restoring Spider from MariaDB Dump
MariaDB Client can restore a Federated MariaDB Enterprise Spider topology from a backup taken with MariaDB Dump backup.
Stop all traffic to the Spider Node and the Data Node.
On the Spider Node, restore the backup for that server using MariaDB Client.
With MariaDB Client 10.4 and later, use the
mariadb
command:$ mariadb \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --default-character-set=utf8mb4 \ < mariadb_dump.sql
For MariaDB Client 10.3 and older, use the legacy command:
$ mysql \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --default-character-set=utf8mb4 \ < mariadb_dump.sql
On the Data Node, restore the backup for that server using MariaDB Client.
With MariaDB Client 10.4 and later, use the
mariadb
command:$ mariadb \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --default-character-set=utf8mb4 \ < mariadb_dump.sql
For MariaDB Client 10.3 and older, use the legacy command:
$ mysql \ --user=mariadb-dump \ --password='md_passwd' \ --ssl-verify-server-cert \ --ssl-ca ~/mariadb_chain.pem \ --default-character-set=utf8mb4 \ < mariadb_dump.sql
On the Spider Node, query a Spider Table to test it:
SELECT * FROM spider_hq_sales.invoices;
+-----------+------------+-------------+----------------------------+---------------+----------------+ | branch_id | invoice_id | customer_id | invoice_date | invoice_total | payment_method | +-----------+------------+-------------+----------------------------+---------------+----------------+ | 1 | 1 | 1 | 2020-05-10 12:35:10.000000 | 1087.23 | CREDIT_CARD | | 1 | 2 | 2 | 2020-05-10 14:17:32.000000 | 1508.57 | WIRE_TRANSFER | | 1 | 3 | 3 | 2020-05-10 14:25:16.000000 | 227.15 | CASH | +-----------+------------+-------------+----------------------------+---------------+----------------+