mydumper and myloader

I've been doing a lot of testing with datasets and was getting very frustrated by how slow using mysqldump and then restoring the data is. I started searching for a better way and discovered mydumper. I tested it and it is much, much faster! I created a couple of very simple bash scripts below and have been using it with much success for my testing. Restoring a backup on MySQL 5.6 was so much slower than restoring the same backup on MySQL 5.1. Thankfully with mydimper/myloader I can achieve much better speeds.


1. Download mydumper from https://launchpad.net/mydumper
2. Compile, see here: http://www.serveradminblog.com/2014/11/mydumper-centos-howto/


Example from percona to dump multiple databases or tables:

https://www.percona.com/blog/2014/09/26/logical-mysql-backup-tool-mydumper-0-6-2-now-available/

There isn't a whole lot of documentation as this isn't supported by a large company like Oracle. For example this page has some decent documentation: http://centminmod.com/mydumper.html. However, it isn't entirely correct. I wanted to dump only a specific list of tables and that site's documentation has the command as "--table-list" but it is actually "--tables-list".

It also won't automatically write to the binary log unless you specify this ( --enable-binlog, -e) which can bite you if you are writing to a master-master or master-slave server and you "assume" the data is being replicated.

Another annoying thing I've seen with mydumper is it does a check table status on every table on the server. I'm not sure why this is needed. It really slows down a backup if the server has a lot of tables (hundreds of thousands).

Simple bash script to dump and restore a single database using mydumper:


#!/bin/bash
# Source credentials
source_user="root"
source_password="$uperS3krit"
source_host="db1.wherever.com"
# Target credentials
target_user="root"
target_password="$uperS3krit"
target_host="db2.wherever.com"
# database to dump/restore
db_name='mydb1'
restore_db_name='mydb1'
# Env settings
DUMPER_HOME="/opt/mydumper-0.9.1/"
# mkdir backup dir
mkdir -p ./backup/${db_name}
# Other options
backup_path="./backup/${db_name}"
# Set default file permissions
umask 177
echo "${source_host}"
echo "START BACKUP TIME" `date`
# Dump database
time ${DUMPER_HOME}/mydumper --no-locks --user=${source_user} --password=${source_password} --host=${source_host} --outputdir=${backup_path}  --compress --database=${db_name} --threads=12
echo "END BACKUP TIME" `date`
echo ""
# Load database
echo "${target_host}"
echo "START IMPORT TIME" `date`
time ${DUMPER_HOME}/myloader --user=${target_user} --password=${target_password} --host=${target_host} --enable-binlog --directory=${backup_path} --overwrite-tables --database=${restore_db_name} --threads=12
echo "END IMPORT TIME" `date`

Simple bash script to dump and restore several database using mydumper. This script works to do the entire server and then exclude the databases you don't want.


#!/bin/bash
# Source credentials
source_user="root"
source_password="$uperS3krit"
source_host="db1.wherever.com"
# Target credentials
target_user="root"
target_password="$uperS3krit"
target_host="db2.wherever.com"
# folder to dump/restore
# You could change this value from source_host to whatever you want
backup_dir_name=${source_host}
# Env settings
DUMPER_HOME="/opt/mydumper-0.9.1/"
# mkdir backup dir
mkdir -p ./backup/${backup_dir_name}
# Other options
backup_path="./backup/${backup_dir_name}"
# Set default file permissions
umask 177
echo "${source_host}"
echo "START BACKUP TIME" `date`
# Dump databases
time ${DUMPER_HOME}/mydumper --user=${source_user} --password=${source_password} --host=${source_host} --outputdir=${backup_path}  --compress  --regex '^(?!(mysql|test|performance_schema|information_schema))' --threads=4
echo "END BACKUP TIME" `date`
echo ""
# Load databases
echo "${target_host}"
echo "START IMPORT TIME" `date`
time ${DUMPER_HOME}/myloader --user=${target_user} --password=${target_password} --host=${target_host} --enable-binlog --directory=${backup_path} --overwrite-tables --threads=4
echo "END IMPORT TIME" `date`