MariaDB – How to migrate quickly MySQL to MariaDB over different hosts with mydumper/myloader and ncat

A lot of possibilities exist to migrate MySQL to MariaDB. In this blog I would like to show a scenario where I migrate MySQL 5.7.19 with a lot of databases to MariaDB 10.2.7, which is on a different host. The tools I am using are mydumper/myloader and ncat.

Please be aware that mydumper does not come out of the box. It has to be installed beforehand, like explained in my previous blog: https://www.dbi-services.com/blog/mariadb-speed-up-your-logical-mariadb-backups-with-mydumper/

The same applies to ncat. It might not be on your system. However, the installation of ncat is quite simple. Just run

# yum install nmap-ncat

Ncat is just a networking utility which reads and writes data across networks from the command line. A quite underestimated tool from my point view. And please take care, ncat does not encrypt your network traffic per default. That’s why it might be faster than coping it via scp/sftp. Ncat operates in one of two primary modes: connect mode and listen mode. So, we need to install it on both hosts. In my case on node mysql01 (connect mode) and on mysql02 (listen mode). If you want to learn more about ncat, take a look at the following web site. http://nmap.org/ncat

Ok. Let’s get started. First of all, we take a look at the current MySQL instance. As you can see in the output below, I do have a lot of databases that I want to migrate, e.g. air, dbi and a couple of sakila databases.

mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] u | grep -A 1 mysqld6
mysqld6 is running        : 5.7.19 (mysql-5.7.19)
Port                      : 33006

mysqld6-(root@localhost) [(none)]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)

mysqld6-(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| air                |
| dbi                |
| mysql              |
| performance_schema |
| sakila             |
| sakila01           |
| sakila02           |
| sakila03           |
| sakila04           |
| sakila05           |
| sakila06           |
| sys                |
| wrs                |
+--------------------+
14 rows in set (0.00 sec)

MyDumper comes with a quite cool parameter, called –regex. By using the –regex parameter, we can dump out all databases except the ones the we don’t want in an elegant way. e.g. we might not want to dump (mysql|test|information_schema|sys|performance_schema)

mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld6/.my.cnf 
--threads=6 --regex '^(?!(mysql|test|information_schema|sys|performance_schema))' 
--outputdir=/u99/mysqlbackup/mysqld6/mydumper_mysqld6

Ok. We got now all databases dumped out and we can copy it over to the MariaDB host with ssh, ftp, rsync or other tools. Or we do it via ncat directly. For doing so, we need to start the ncat in listen mode (-l) on the destination host mysql02.

-- On host mysql02:

mysql@mysql02:/u00/app/mysql/ [mysqld6] cd /u99/mysqlbackup/mysqld6/mydumper_mysqld6
mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6]
mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] nc -4 -l 33333 | tar -xzvp
...
this host is waiting now for network packets

On our source node, we start mydumper, pipe it to tar and send it to ncat.

On host mysql01:

mysql@mysql01:/u00/app/mysql/ [mysqld6] cd /u99/mysqlbackup/mysqld6/mydumper_mysqld6
mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6]
mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld6/.my.cnf 
--threads=6 --regex '^(?!(mysql|test|information_schema|sys|performance_schema))' | tar -czv -f - .  | nc --send-only mysql02 33333
./
./export-20170809-062635/
./export-20170809-062635/air-schema-create.sql
./export-20170809-062635/dbi-schema-create.sql
./export-20170809-062635/sakila-schema-create.sql
./export-20170809-062635/sakila01-schema-create.sql
./export-20170809-062635/sakila02-schema-create.sql
./export-20170809-062635/sakila03-schema-create.sql
./export-20170809-062635/sakila04-schema-create.sql
./export-20170809-062635/sakila05-schema-create.sql
./export-20170809-062635/sakila06-schema-create.sql
./export-20170809-062635/wrs-schema-create.sql
./export-20170809-062635/metadata
./export-20170809-062635/dbi.dbi_t.sql
./export-20170809-062635/sakila05.category.sql
./export-20170809-062635/sakila.actor.sql
./export-20170809-062635/sakila.address.sql
./export-20170809-062635/sakila.category.sql
./export-20170809-062635/sakila.city.sql
./export-20170809-062635/sakila.country.sql
./export-20170809-062635/sakila.customer.sql
./export-20170809-062635/sakila.film.sql
./export-20170809-062635/sakila.film_actor.sql
./export-20170809-062635/sakila.film_category.sql
./export-20170809-062635/sakila.film_text.sql
./export-20170809-062635/sakila.inventory.sql
./export-20170809-062635/sakila.language.sql
./export-20170809-062635/sakila.payment.sql
./export-20170809-062635/sakila.rental.sql
./export-20170809-062635/sakila.staff.sql
./export-20170809-062635/sakila.store.sql
./export-20170809-062635/sakila01.actor.sql
./export-20170809-062635/sakila01.address.sql
...
...

On the other terminal, you can see that the files are being received.

On host mysql02:

mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] nc -4 -l 33333 | tar -xzvp
./
./export-20170809-062635/
./export-20170809-062635/air-schema-create.sql
./export-20170809-062635/dbi-schema-create.sql
./export-20170809-062635/sakila-schema-create.sql
./export-20170809-062635/sakila01-schema-create.sql
./export-20170809-062635/sakila02-schema-create.sql
./export-20170809-062635/sakila03-schema-create.sql
./export-20170809-062635/sakila04-schema-create.sql
./export-20170809-062635/sakila05-schema-create.sql
./export-20170809-062635/sakila06-schema-create.sql
./export-20170809-062635/wrs-schema-create.sql
./export-20170809-062635/metadata
./export-20170809-062635/dbi.dbi_t.sql
./export-20170809-062635/sakila05.category.sql
./export-20170809-062635/sakila.actor.sql
./export-20170809-062635/sakila.address.sql
./export-20170809-062635/sakila.category.sql
./export-20170809-062635/sakila.city.sql
./export-20170809-062635/sakila.country.sql
./export-20170809-062635/sakila.customer.sql
./export-20170809-062635/sakila.film.sql
./export-20170809-062635/sakila.film_actor.sql
./export-20170809-062635/sakila.film_category.sql
./export-20170809-062635/sakila.film_text.sql
./export-20170809-062635/sakila.inventory.sql
./export-20170809-062635/sakila.language.sql
./export-20170809-062635/sakila.payment.sql
./export-20170809-062635/sakila.rental.sql
./export-20170809-062635/sakila.staff.sql
./export-20170809-062635/sakila.store.sql
./export-20170809-062635/sakila01.actor.sql
./export-20170809-062635/sakila01.address.sql
...
...
...

mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] ls -l
total 20
drwx------ 2 mysql mysql 16384 Aug  9 06:26 export-20170809-062635

That’s it. We got now all the files on our destination host mysql02. Now it’s time to import the data via myloader into the new MariaDB 10.2.7 which is empty at the moment.

mysql@mysql02:/u00/app/mysql/ [mysqld6] u | grep -A 1 mysqld6
mysqld6 is running        : 10.2.7-MariaDB (mariadb-10.2.7)
Port                      : 33006

mysql@mysql02:/u00/app/mysql/product/tools/mydumper-0.9.2/bin/ [mysqld6] mq
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 14
Server version: 10.2.7-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysqld6-(root@localhost) [(none)]> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 10.2.7-MariaDB-log |
+--------------------+
1 row in set (0.00 sec)

Per default, the myloader tool starts 4 parallel threads, and runs in verbose mode 2 which means that only warning messages are shown. In case you want to have it more verbose, you can specify –verbose=3

mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] /u00/app/mysql/product/tools/mydumper/bin/myloader 
--defaults-file=/u00/app/mysql/admin/mysqld6/.my.cnf --threads=6 --verbose=3 
--directory /u99/mysqlbackup/mysqld6/mydumper_mysqld6/export-20170809-062635
** Message: 6 threads created
** Message: Creating database `air`
** Message: Creating table `air`.`muc`
** Message: Creating database `dbi`
** Message: Creating table `dbi`.`dbi_t`
** Message: Creating table `dbi`.`dbi_t2`
** Message: Creating database `sakila`
** Message: Creating table `sakila`.`actor`
** Message: Creating table `sakila`.`address`
** Message: Creating table `sakila`.`category`
** Message: Creating table `sakila`.`city`
** Message: Creating table `sakila`.`country`
** Message: Creating table `sakila`.`customer`
** Message: Creating table `sakila`.`film`
...
...

If you take a look at the process list, you should see 6 threads doing the work.

mysqld6-(root@localhost) [(none)]> show processlist;
...
...
| 30 | root        | localhost | sakila01 | Query   |    0 | Opening tables          | INSERT INTO `city` VALUES
(1,"A Corua (La Corua)",87,"2006-02-15 03:45:25"),
(2,"Abha",82,"2006-02-1 |    0.000 |
| 31 | root        | localhost | sakila   | Query   |    6 | update                  | INSERT INTO `film_actor` VALUES
(1,1,"2006-02-15 04:05:03"),
(1,23,"2006-02-15 04:05:03"),
(1,25,"20 |    0.000 |
| 32 | root        | localhost | sakila   | Query   |    4 | update                  | INSERT INTO `payment` VALUES
(1,1,1,76,2.99,"2005-05-25 11:30:37","2006-02-15 21:12:30"),
(2,1,1,573 |    0.000 |
| 33 | root        | localhost | sakila   | Query   |    3 | update                  | INSERT INTO `rental` VALUES
(1,"2005-05-24 22:53:30",367,130,"2005-05-26 22:04:30",1,"2006-02-15 20: |    0.000 |
| 34 | root        | localhost | sakila01 | Query   |    2 | update                  | INSERT INTO `address` VALUES
(1,"47 MySakila Drive",NULL,"Alberta",300,"","",">n2]c |    0.000 |
| 35 | root        | localhost | sakila   | Query   |    4 | update                  | INSERT INTO `inventory` VALUES

That’s it. We got now the data migrated to MariaDB 10.2.7.

Conclusion

Many ways do exist for migrating MySQL to MariaDB. Using mydumper/myloader in combination with ncat is just one of those. However, from my point of view, a quite cool one.