Mydumper multithreading fast backup in Linux, restore MySQL database

Mydumper Installation

1, first install CMake, because this software to use CMake compile

The code is as follows	Copy Code
# Apt-get Install CMake

2, the next installation Mydumper compatibility package, this is very critical, because according to the official website documents, Centos/redhat and other systems need the package name is different, according to the actual system to check the official website information:

The code is as follows	Copy Code
# apt-get Install Libglib2.0-dev Libmysqlclient15-dev zlib1g-dev Libpcre3-dev

3, download the source code installation package:

#wget http://launchpad.net/mydumper/0.2/0.2.3/+download/mydumper-0.2.3.tar.gz

4, unpack the package

The code is as follows	Copy Code
#tar XZVF mydumper-0.2.3.tar.gz

5, into the decompression directory, ready to start installation

The code is as follows	Copy Code
#cd mydumper-0.2.3/

6, with CMake to compile, note that the following character "." (In fact, the end of English), don't fall down

The code is as follows	Copy Code
#cmake.

7, installation

The code is as follows	Copy Code
#make #make Install

The installation is complete.

Simple backup-recovery test

Backup

The code is as follows	Copy Code
Mydumper–kill-long-queries-u root-b dbname-t db-tables-name-p ' password '-v 2-c-T 8–outputdir=/home/backup/2012061 5/

Restore Backup

The code is as follows	Copy Code
myloader-d/home/backup/20120615/-o-b dbname-u root-p ' passwd '-t 4 & Myloader most of the parameters are the same as Mydumper –directory,-d The data directory to restore –overwrite-tables, O Drop any existing the tables when restoring schemas

The following are the common parameter explanations:

–host, h-connected MySQL server
–user,-u user backed up connection user
–password, p
–port,-P connection port
–socket,-S connection socket file
–database,-B database to be backed up
–table-list, t need to back up the table, use, separate
–outputdir, the directory of-O output
–build-empty-files,-e If the table data is empty or produces an empty file, the default is infinite, only the table structure file
–regex,-X supports regular expressions such as Mydumper–regex ' ^ (?! (mysql|test)) '
–ignore-engines,-I ignored storage engine
–no-schemas,-m does not export table structure
–long-query-guard long query, default 60s, over is passed Mydumper
–kill-long-queries, you can set the kill length query
–verbose,-V 0 = silent, 1 = errors, 2 = warnings, 3 = info, default is 2
–binlogs,-B export Binlog www.111cn.net

–daemon,-D Enable daemon mode
–snapshot-interval,-I dump snapshot time interval, default 60s
–logfile,-l mysqldumper log output, typically used in daemon mode

dynamic Backup of MySQL shell script

The basic functions of the script are as follows (please add your own off-site/heterogeneous disaster preparedness if allowed):

1, backup database;

2, plus the expiration of the code, retained 7 days of backup, so as to avoid the hard drive was burst, there is demand, please increase or decrease time;

3, through the comparison can know how long the database backup used.

The code is as follows

Copy Code

#!/bin/bash #This is a shellscript for auto Db-backup #201204 by Szy #Setting Dbname=test Dbuser=root Dbpasswd=password Www.111cn.net backuppath=/home/backup/db Logfile=/home/backup/log/dbbackup.log Newfile= "$BackupPath"/"$DBName 1"/$ (date +%y%m%d) Oldfile= "$BackupPath"/"$DBName 1"/$ (date +%y%m%d--date= ' 7 days ago ') echo "----------------Backup Start-------------------" >> $LogFile echo $ (date + "%y-%m-%d%h:%m:%s") >> $LogFile echo "-----------------------------------------------------" >> $LogFile #Delect Old File If [-D $OldFile] Then RM-RF $OldFile >> $LogFile 2>&1 echo "----------[$OldFile]delete old File Success!----------" >> $LogFile Else echo "----------[$DBName]no old Backup File!------------->> $LogFile Fi #Backup echo "----------------------$DBName----------------------" >> $LogFile echo $ (date + "%y-%m-%d%h:%m:%s") >> $LogFile If [-D $NewFile] Then echo "----------[$NewFile]the backup File is Exists,can ' t backup!----------' >> $LogFile Else mkdir $NewFile Mydumper--kill-long-queries-u $DBUser-B $DBName-P $DBPasswd-v 2-c-T 8--outputdir= $NewFile Fi echo $ (date + "%y-%m-%d%h:%m:%s") >> $LogFile echo "--------------This shellscript end-------------------" >> $LogFile