While working on migrating our databases to a new Galera cluster I ran into many
issues setting up replication with huge amounts of data. The databases in
question totaled roughly 900GB which made both the initial dump from the primary
database server and the import into the Galera cluster excruciatingly slow (9
days and change). Since the Galera cluster already had other databases I wasn’t
able to just copy the backup files created by
/var/lib/mysql, I had to come up with a method to import them without
disrupting the existing data. Luckily MariaDB provides the ability to
discard/import an InnoDB table’s tablespace.
First we need a backup of the primary server. Depending on how much data you have this may take quite a while.
mariabackup --backup --safe-slave-backup --innodb-file-per-table --target-dir /var/lib/mariadb-backup/
Note: I’ve included the
--safe-slave-backupargument here as I am actually taking the backup from another replica and don’t want to break replication in the process. Depending on your specific setup this option may not be required.
Once we have data to work with it needs to be prepare for use.
Files generated by
mariabackup during the
--backup phase are not ready for
use on the server. Before they can be restored they need to be prepared. For
full backups, like the one we just took, they are not point in time consistent
since they were taken at different times. The
--prepare phase will ready the
data to be restored to a MariaDB server.
The use of the
--export parameter here is important. This argument will tell
mariabackup to generate the
.cfg files for each table which are required to
import the table data using
ALTER TABLE ... IMPORT TABLESPACE.
mariabackup --prepare --export --target-dir /var/lib/mariadb-backup/
We will also require the
CREATE TABLE ... statements for each table. This can
be generated with
mysqldump like so, just make sure that the file is in the
backup directory and is named
mysqldump --no-data --databases db1 db2 > /var/lib/mariadb-backup/schema.sql
Now we need to get the backed up files to the replica, I used rsync but feel free to use whatever method is easiest for you.
On the replica start by importing the table schemas.
mysql < /var/lib/mariadb-backup/schema.sql
🚨 If you are moving from a version lower than MySQL 5.6 and your tables have temporal columns you will need to work around the Column precise time mismatch error described below. Check whether or not
mysql56_temporal_formatis enabled. If it is, then disable it.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'mysql56_temporal_format'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | mysql56_temporal_format | ON | +-------------------------+-------+ 1 row in set (0.001 sec) MariaDB [(none)]> SET GLOBAL mysql56_temporal_format=OFF; Query OK, 0 rows affected (0.000 sec)
Make sure to re-enable it when you are done.
To import the table data I wrote a quick bash script that will iterate over the tables in a database and perform the import automatically. This also handles the edge case of importing and swapping partitions which involves a few extra steps.
import_tables.sh takes the following options:
-d|--database: the database containing the tables to import.
-t|--target-dir: the directory containing the exported table data.
-m|--mysql-dir: the MySQL data directory to which the exported table data will be copied. If not specified the default value is taken from
-r|--row-format: The default row format of the old server. If unspecified, then the files’ row format must match this server’s default.
$ wget https://www.steventwheeler.com/assets/article_files/2021-08-06-mariabackup-replication-setup/import_tables.sh $ chmod +x import_tables.sh $ ./import_tables.sh --target-dir /var/lib/mariadb-backup --database db1
Once the tables have been imported the last step is to synchronize the Galera
nodes. This is necessary as importing the tablespace only changes the node on
which the command was run. All of the other nodes will still see the tables as
empty. There are a couple ways to do this. The first and likely safest route is
to just re-run the import script on each node while ensuring that no clients are
modifying the tables. The second is to stop MariaDB on all of the other nodes,
/var/lib/mysql, and restart MariaDB to force them to perform SST
again. Once SST completes the node will contain all of the table data from your
original node. There are several potential pitfalls here. The biggest being that
you will have a single point of failure until the second node completes SST.
Also, if you are using rsync as your SST method the first and only node will be
inaccessible for all queries until SST completes.
Common MySQL Errors
During the tablespace import you may run into a few different common errors due to the table formats changing between MySQL versions.
Table flags don’t match
ERROR 1808 (HY000) at line 1: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT)
The default table format changed to DYNAMIC in MySQL 5.7.9, older versions used COMPACT by default. We can work around this issue by altering our target table. Unfortunately the failed tablespace import likely left our table in an inconsistent state. To fix it we will make a copy, alter the copy, and then replace the original.
MariaDB [db1]> CREATE TABLE tbl_name_copy LIKE tbl_name; Query OK, 0 rows affected, 1 warning (0.042 sec) MariaDB [db1]> ALTER TABLE tbl_name_copy ROW_FORMAT=COMPACT; Query OK, 0 rows affected (0.058 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [db1]> DROP TABLE tbl_name; Query OK, 0 rows affected (0.193 sec) MariaDB [db1]> RENAME TABLE tbl_name_copy TO tbl_name; Query OK, 0 rows affected (0.026 sec)
Alternatively, if all of your tables use a the same format which does not match
the new server’s default you can specify the
--row-format parameter and the
script will automatically convert the tables to that format before importing the
Column precise type mismatch
ERROR 1808 (HY000) at line 1: Schema mismatch (Column created_at precise type mismatch, it's 0X8050C in the table and 0X50C in the tablespace meta file)
The default format for TIME, DATETIME, and TIMESTAMP changed in MySQL 5.6.4. Importing tablesaces created with older versions will have an incompatible format. As with the row format error we can work around this by altering our target table. Again, the failed tablespace import likely left our table in an inconsistent state which can be fixed by copying, altering, and replacing the target table.