MariaDB/ImportTableSpace

This is a subpage of MariaDB section

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 "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`; do echo $i; mysql --skip-ssl enwiki -e "flush table $i for export;";done

Now we are ready to stop MySQL on source

• db2055.codfw.wmnet
• /etc/init.d/mysql stop

Once MySQL is completely stopped, we are ready to start the copy over 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

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 | 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.

However, make sure the source host is back up so it can start catching up with its master.

Source db2055.codfw.wmnet:

• /etc/init.d/mysql start --skip-slave-start

Review the error log to make sure the server started finely /srv/sqldata/db2055.codfw.wmnet and then start mysql

• start slave;

Now we can focus on importing the tables on the target dbstore2001.eqiad.wmnet

Make sure the transfer was done correctly and you can see the .ibd 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 "alter table $i import tablespace;";done

You can tail de log to follow its progress:

root@dbstore2001:~# tail -f /srv/sqldata/dbstore2001.err
161018 10:57:16 [Note] InnoDB: Sync to disk
161018 10:57:16 [Note] InnoDB: Sync to disk - done!
161018 10:57:16 [Note] InnoDB: Phase I - Update all pages
161018 10:57:19 [Note] InnoDB: Sync to disk
161018 10:57:19 [Note] InnoDB: Sync to disk - done!
161018 10:57:19 [Note] InnoDB: Phase III - Flush changes to disk
161018 10:57:19 [Note] InnoDB: Phase IV - Flush complete
161018 11:00:43 [Note] InnoDB: Sync to disk
161018 11:00:43 [Note] InnoDB: Sync to disk - done!
161018 11:00:43 [Note] InnoDB: Phase I - Update all pages

Known issues

• Make sure you do not copy tables between major versions (ie: MariaDB 10.0 vs MariaDB 10.1)