About mysqlpump and Its Advantages

You might already know about “mysqlpump”, but I used this tool as an experiment and I made some suggestions for people who haven’t used it yet!

The usage of basic options are the same as mysqldump. It's easy for people who are used to mysqldump. I recommend you to try mysqlpump.

The fact that it’s included in MySQL is a high point.

Progress Indication

The database I used was only about 200MB so it didn’t get much of the benefits, but mysqlpump can show the progress status of each dump.

mysqlpump -uroot -p --databases xxxxx  > xxxxx_20XX0X0X.sql
Enter password:
Dump progress: 1/6 tables, 0/352 rows
Dump progress: 73/145 tables, 551291/1274767 rows
Dump completed in 2073 milliseconds

The basic command’s output looks like this.

I tried mysqldump for the same database and it seems like mysqlpump is slightly slower than mysqldump.

That made me a little bit disappointed but it was just 0.2 seconds difference for this capacity, so I guess this is acceptable.

It has the option to compress output files

Most of the time, dump files are compressed whether they are for backups or transferred to another server as a copy.

The mysqlpump has the option to output the files as a compressed file that no process is required after.

mysqlpump -uroot -p --compress-output=LZ4 --databases xxxxx  > xxxxx_20XX0X0X.lz4

You can choose a format with --compress-output option.

However, the supported formats are only LZ4 and openssl zlib, which are rarely seen.

I wonder what openssl zlib is, so I looked it up, and it’s just zip files.

It would be boring if I just used a zip file that I chose LZ4, but then I wonder how I can decompress it.

Then I found this MySQL document.

LZ4 format is the compression format supported by Linux that has a command for decompression provided.

lz4_decompress xxxxx_20XX0X0X.lz4 xxxxx_20XX0X0X.sql

You might feel this command is too long.

But you can import the decompressed file just with the mysql command.

mysql -uroot -p < xxxxx_20XX0X0X.sql

When it comes to file compression, a compression ratio is a concern, but there is no benefit in compression ratio for LZ4. The compression speed seems to be the benefit.

Parallel Processing

The major difference from mysqldump is the concept of parallel processing.

However, in the documentation, it seems hard to use this effectively if you don’t have knowledge of the dump process.

Specify number of threads with --default-parallelism option. The default is 2.

mysqlpump -uroot -p \
  --default-parallelism=4 \
  --databases xxxxx \
  > xxxxx_20XX0X0X.sql

Create cues using --parallel-schemas option and specify which cues process which tables.

mysqlpump -uroot -p \
  --default-parallelism=4 \
  --parallel-schemas=xxxxx \
  --parallel-schemas=yyyyy \
  > xxxxxyyyyy_20XX0X0X.sql

In the example above, two cues are created and each cue is processed with four threads.

It’s better to remember cueing by a table because it saves processing time if it can distribute the amount of process, I suppose.

You may think you can just create dumps while you are calculating, but it is just like having huge tables and small tables processed in separate queues.

The --parallel-schemas option can specify the number of threads to activate at the same time that it is possible to bias the number of threads

mysqlpump -uroot -p \
  --parallel-schemas=2:xxxxx \
  --parallel-schemas=4:yyyyy \
  > xxxxxyyyyy_20XX0X0X.sql

This command means two threads for xxxxx table’s dump and four threads for yyyyy table’s dump.

Got weird errors when I tried to dump

The first time I ran the command, I got weird errors.

mysqlpump -uroot -p --databases xxxxx  > xxxxx_20XX0X0X.sql
Enter password:
mysqlpump: [WARNING] (1356) View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.host_summary_by_file_io' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
.
.
.
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysqlpump: [WARNING] (1356) View 'sys.x$schema_table_statistics_with_buffer' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I had no idea what I did was wrong, but apparently it’s a mysql table compatibility issue.

The solution is simple. You just need to upgrade the system table.

I referred to this blog.

[FIXED] mysqlpump: [WARNING] (1356) – Biesma.net

It simply said just mysql_upgrade, but I think it has to be a root user.

So I tried a command the following

mysql_upgrade -uroot -p

It showed me an output that looked like it was repairing tables and then finished the process successfully.

Summary

I introduced mysqlpump, the new version of mysqldump command.

I think even just the progress display and compression options are useful enough.

Plus, it can do parallel processing.

I’d like to try dumping bigger databases but I haven’t found a good target yet.

The mysqlpump is included for newer than MySQL 5.7.8 version.

This blog post is translated from a blog post written by Yoichi Mandai on our Japanese website Beyond Co..