Merging MySQL databases with xtrabackup

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi,

I have two MySQL 8.4 databases:

Server 1: schema: com
Server 2: schema: cli

Using xtrabackup, is it possible to merge the two databases into a server 3?

2

Hello @CharlesR,
Yes, this is possible, but it will be extremely tedious, and will require the schema from all tables. Basically the steps will be like this:

  1. Take backup of server 1 and specify ‘–databases=com’.
  2. Restore this backup using the normal methods and flags
  3. Take a backup of server 2, ‘–databases=cli’
  4. During the prepare phase of restore, use --export. This prepares all .ibd files for importing into another server. You will have .ibd, and .cfg files for each table.
  5. In MySQL, created in #2, create database cli.
  6. For each table in cli, CREATE TABLE …; ALTER TABLE … DISCARD TABLESPACE; In shell, copy .ibd and .cfg to $datadir/cli/ chown mysql:mysql .ibd/.cfg file. In MySQL, ALTER TABLE … IMPORT TABLESPACE. SELECT * FROM … to verify data. Repeat for remaining tables.

Depending on the size of these datasets, you might have an easier, and less error prone time by using mydumper/myloader.

2 Likes