MariaDB/ImportTableSpace

Importing table spaces from other hosts with multi source replication

Transportable tablespaces

Since MySQL 5.6 it is posible to use advantage of transportable table spaces to move around .ibd files from one server to another (if file per table is enabled.

This feature provides a fast way of copying data from one host to another over the network using compression and nc for instance.

Exporting the tablespaces

For this example we will use db2055.codfw.wmnet as a source host from where the .ibd files will be copied. And we will use dbstore2001.codfw.wmnet as the host which will import those tablespaces. There are several commands that need to be run on both hosts before the actual copy can start.

First of all, on the target host dbstore2001.codfw.wmnet we need to create the database and the table structure of the database we want to import. Let's assume we are importing enwiki database. So we can use netcat to pass the table and databae information between hosts.

On target:

cd /root
nc -l -p9210 >> enwiki.sql

Make sure the port 9210 is open, if not you might need to place a temporary iptables rule to allow it - make sure you use src SOURCE_IP to only allow connections from that source host. And remember to close that rule once the process is over.

On source we are ready to send the table structure over the network:

mysqldump --no-data --skip-ssl enwiki | nc dbstore2001.codfw.wmnet 9210

Once the data is copied, we can import it on the target host

dbstore2001.codfw.wmnet
mysql --skip-ssl -e "create database enwiki;"
cat enwiki.sql | mysql --skip-ssl enwiki

Once the data is there, we need to make sure we discard that table space on the target host, so we are ready to receive the new one.

dbstore2001.codfw.wmnet
for i in `mysql --skip-ssl enwiki -e "show tables;" -B`; do echo $i; mysql --skip-ssl enwiki -e "set session sql_log_bin=0;alter table $i discard tablespace;";done

On the source host we have to do a few things before we can start moving the data along.

Make sure you have downtimed the host in Icinga and also make sure it is not pooled

We have to stop the slave and note the position as it will be needed on the target host.

db2055.codfw.wmnet
mysql --skip-ssl -e "nopager; stop slave; show slave status\G"

Once we have that noted, we need to prepare the tables to be exported, for that the following command is needed

for i in `mysql --skip-ssl enwiki -e "show tables;" -B | grep -v "Tables_in" `; do echo -n "$i, " ;done >> /tmp/export.sql ; echo "for export; select sleep(86400)" >> /tmp/export.sql ; sed -i "s/, for export/ for export/g" /tmp/export.sql ; sed -i '1 i\flush tables ' /tmp/export.sql ;

Now we are ready to import that file which will give us 24h before releasing the tables lock

db2055.codfw.wmnet
cat /tmp/export.sql | mysql --skip-ssl enwiki

This shouldn't take long, so once this is over we can copy the data via nc This time we are going to transfer data, so we should encrypt it, so please pick a password and use it to replace the PASSWORD

You can check the log to see if all went fine with the flush, you should see entries like this:

161021 15:09:06 [Note] InnoDB: Sync to disk of '"enwiki"."wikilove_log"' started.
161021 15:09:06 [Note] InnoDB: Writing table metadata to './enwiki/wikilove_log.cfg'
161021 15:09:06 [Note] InnoDB: Table '"enwiki"."wikilove_log"' flushed to disk

Another check could also be to check if you have the same amount of .ibd files as .cfg in the datadir /srv/sqldata/enwiki

On target we need to start the nc:

dbstore2001.codfw.mnet
cd /srv/sqldata
nc -l -p 9210 | pv | openssl aes-256-cbc -d -k PASSWORD | pigz -d -c | tar xvf -

And from the source:

cd /srv/sqldata
tar cvf - enwiki/*.{ibd,cfg} | pv | pigz -c | openssl aes-256-cbc -salt -k PASSWORD | nc dbstore2001.codfw.wmnet 9210

Importing the tablespaces

Once the network transfer has finished, we are ready to import the the tables on the target dbstore2001.eqiad.wmnet

Make sure the transfer was done correctly and you can see the .ibd and .cfg files under dbstore2001.codfw.wmnet:/srv/sqldata/enwiki If those are there, you are now ready to import the files:

for i in `mysql --skip-ssl enwiki -e "show tables;" -B`; do echo $i; mysql --skip-ssl enwiki -e "set session sql_log_bin=0;alter table $i import tablespace;";done

You can tail de log to follow its progress:

root@dbstore2001:~# tail -f /srv/sqldata/dbstore2001.err
161022 17:22:37 [Note] InnoDB: Importing tablespace for table 'enwiki/valid_tag' that was exported from host 'db2055'
161022 17:22:37 [Note] InnoDB: Phase I - Update all pages
161022 17:22:37 [Note] InnoDB: Sync to disk
161022 17:22:37 [Note] InnoDB: Sync to disk - done!
161022 17:22:37 [Note] InnoDB: Phase III - Flush changes to disk
161022 17:22:37 [Note] InnoDB: Phase IV - Flush complete

Issues faced

Make sure you do not copy tables between major versions (ie: MariaDB 10.0 vs MariaDB 10.1)
Tables without PK might fail
Make sure you do not copy .frm files, only .ibd and .cfg as this might make the import to fail.
Muti source replication + GTID might corrupt some replication threads after stopping it.
Compressed tables will probably fail if you copy from MariaDB 10.0 to MariaDB 10.1)