Mydumper: MySQL multi-thread logical backup and recovery

MySQL multi-thread logical backup

• About Mydumper

  Mydumper is a multi-threaded MySQL logical backup tool.

  • Fast logical backup (compared with mysqldump)
  • Consistent backup of myisam and innodb tables
  • If the myisam table is not used, there is almost no "Lock" (no locking)
  • Each table is exported to an independent file.
  • Quick compression. Generally, mydumper files are 3 to 3 smaller than XtraBackup files ~ 5 times
• Install Mydumper

  # yum install cmake glib2-devel zlib zlib-devel pcre-devel gcc-c++# wget -c https://launchpad.net/mydumper/0.5/0.5.2/+download/mydumper-0.5.2.tar.gz# tar zxvf mydumper-0.5.2.tar.gz# cd mydumper-0.5.2# cmake .# make && make install

• Use Mydumper

  Mydumper uses multiple threads to dump multiple tables or a part of a table at the same time. It has many option parameters.mydumper --help.
  The following are three important parameters:

  -- Database: Specifies the name of the database to export the backup -- threads: specifies the number of threads used. it can usually be set to the number of CPU cores, and sometimes more. -- rows: specifies the number of rows (Records) of each chunk. the specific value depends on the number of records in the data table.

  Example of a backup script dbdump. sh:

  #!/bin/bash MYDUMPER=`which mydumper`DIR_BACKUP=/backupDB_HOST=192.168.1.190DB_PORT=3306DB_NAME=sakilaDB_USER=rootDB_PASS=123456FDAY=`date "+%F"`DB_DUMP=$DIR_BACKUP/$DB_NAME.$FDAY$MYDUMPER \    --database=$DB_NAME \    --host=$DB_HOST \    --port=$DB_PORT \    --user=$DB_USER \    --password=$DB_PASS \    --outputdir=$DB_DUMP \    --rows=500000 \    --compress \    --build-empty-files \    --threads=2 \    --compress-protocol \    --kill-long-queries

  Note: Mydumper does not process MySQL views, triggers, and stored procedures. Therefore, it is not reliable to use mydumper to export the schema.
  In the actual production environment, we recommend that you use mydumper to export data only (Use--no-schemas), And use mysqldump to export the schema.

MySQL multi-thread logical recovery

• Myloader

  Myloader is also installed when Mydumper is installed.

  Dbload. sh:

  #!/bin/bash MYLOADER=`which myloader`DB_HOST="192.168.1.190"DB_PORT=3306DB_NAME="sakila2"DB_USER="root"DB_PASS="123456"DB_DUMP="/data/backup/sakila.2013-04-29"$MYLOADER \    --database=$DB_NAME \    --host=$DB_HOST \    --port=$DB_PORT \    --user=$DB_USER \    --password=$DB_PASS \    --directory=$DB_DUMP \    --queries-per-transaction=50000 \    --threads=6 \    --compress-protocol \    --verbose=3

Recommended reading:

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server