old and busted: mysqldump, new hotness: mydumper

I’m late to the mydumper party – https://github.com/maxbube/mydumper

Multi-threaded, lightning quick, exports to files per table structure and data.

In the Ubuntu world (and likely Debian too) it’s available as a precompiled package: sudo apt update && sudo apt install mydumper

Backup everything in a database:

mydumper \
  --triggers \
  --routines \
  --events \
  --database name_of_source_database

It’ll create a date stamped directory with two files for each table. One for the schema, the other with the data.

If you want to import everything in one go, here’s a little bit of bash to help:

# 1st - import all of the schema files to create the
#   tables, triggers, routines, and events
for i in `ls *-schema.sql`; do
    echo -e "\nImporting schema file: ${i}"
    pv ${i} | mysql name_of_target_database
done

# 2nd - import all of the table data
for i in `ls *.sql | grep -v '\-schema.sql'`; do
    echo -e "\nImporting data file: ${i}"
    pv ${i} | mysql name_of_target_database
done

The above uses pv (pipe viewer – http://ivarch.com/programs/pv.shtml) to send the content of the files into mysql. This means that we get to see the progress of importing files into MySQL.

If you haven’t got pv I suggest you get it: sudo apt update && sudo apt install pv.