You are browsing a read-only backup copy of Wikitech. The primary site can be found at wikitech.wikimedia.org
This is a subpage of MariaDB section
Importing table spaces from other hosts with multi source replication
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
DO NOT USE THIS METHOD AS WE ARE CURRENTLY NOT SURE IF IT WORKS
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
So we can use
netcat to pass the table and databae information between hosts.
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
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.
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.
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
sed -i '1 i\flush tables ' /tmp/export.sql ; 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
Now we are ready to import that file which will give us 24h before releasing the tables lock
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
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
On target we need to start the
nc -l -p 9210 | pv | openssl aes-256-cbc -d -k PASSWORD | pigz -d -c | tar xvf -
And from the source:
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.
/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
Now we can focus on importing the tables on the target
Make sure the transfer was done correctly and you can see the
.ibd files under
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
- Make sure you do not copy tables between major versions (ie: MariaDB 10.0 vs MariaDB 10.1)