Article Image
Article Image

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 mariabackup into /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-backup argument 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 schema.sql.

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_format is 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. 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 @@datadir.
  • -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
$ chmod +x
$ ./ --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, clear out /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 tablespace.

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.

Blog Logo

Steven Wheeler



Steven Wheeler

A repository of things I occasionally need to look up. Hopefully, it helps others save time too.

Back to Overview