Install and use Mydumper

Last Update:2015-05-18 Source: Internet

Author: User

Tags mysql backup

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Install and use Mydumper

Mydumper is a high-performance multi-thread backup and recovery tool for MySQL and Drizzle. Developers are mainly from MySQL, Facebook, and SkySQL companies. Mydumper has been used online. Although the MySQL database backup tool has its own mysqldump, it is an official mysql backup tool. However, mydumper, a third-party backup tool, is advantageous for more people.

　　1. Install Mydumper

# Yum install glib2-devel mysql-devel zlib-devel pcre-devel

# Wget http://launchpad.net/mydumper/0.5/0.5.1/+download/mydumper-0.5.1.tar.gz

# Tar zxvf mydumper-0.5.1.tar.gz-C ../software/

# Cmake.

# Make

# Make install

2. Create a test database and table in the mysql database (enter the mysql database)

Create database test;

Create table test (a int );

　　Mydumper parameter introduction:

-B, -- database to be backed up

-T, -- tables-list tables to be backed up, separated

-O, -- outputdir output directory

-S, -- statement-size Attempted size of INSERT statement in bytes, default 1000000

-R, -- rows tries to split into many row block tables

-C, -- compress compressed output file

-E, -- build-empty-files: even if the table has no data, an empty file is generated.

-X, -- regex supports regular expressions

-I, -- storage engines ignored by ignore-engines, separated

-M, -- no-schemas does not export the table structure

-K, -- no-locks do not execute temporary shared read lock warning: this will cause inconsistent backups

-L, -- long-query-guard long query, 60 s by default

-- Kill-long-queries kill the query that has been executed for a long time (instead of aborting)

-B, -- binlogs export binlog

-D, -- daemon enable daemon mode

-I, -- snapshot-interval dump snapshot interval. The default value is 60 s. It must be in daemon mode.

-L, -- logfile Log File

-H, -- host

-U, -- user

-P, -- password

-P, -- port

-S, -- socket

-T, -- the number of threads used by threads. The default value is 4.

-C, -- compress-protocol use compression on mysql connections

-V, -- version

-V, -- verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Myloader parameters:

-D, -- directory import Backup directory

-Q, -- queries-per-transaction: number of queries executed each time. The default value is 1000.

-O, -- overwrite-tables: delete a table if the table exists.

-B, -- database to be restored

-E, -- enable-binlog enable binary data recovery

-H, -- host

-U, -- user

-P, -- password

-P, -- port

-S, -- socket

-T, -- the number of threads used by threads. The default value is 4.

-C, -- compress-protocol use compression on the connection

-V, -- version

-V, -- verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

　　Mydumper output file:

Metadata: the metadata records the backup start and end times, and the location of the binlog log file.

Table data: one file for each table

Table schemas: table structure file

Binary logs: After the -- binlogs option is enabled, binary files are stored in the binlog_snapshot directory.

Daemon mode: In this mode, there are five directories: 0, 1, binlogs, binlog_snapshot, and last_dump.

The backup directory is 0 and 1. Backup is performed at intervals. If mydumper fails for some reason, there will still be a good snapshot,

After the snapshot is complete, last_dump points to the backup.

Time mydumper-B test-o/tmp/test2.bak-r 5000

　　Mydumper backup script

#! /Bin/bash

# Ocpyang@126.com

####### Script description

Reading the database.txt file from the current directory and backing up some databases

4.1.2.if database.txt is empty, perform full backup or backup of the specified database

# Set parameter

Mydumper =/usr/local/bin/mydumper # Set it based on your actual situation

Dir_backup =/backup # set according to your actual situation

Mysql_host = localhost # set according to your actual situation

Mysql_port = 33306 # Set the port according to your actual situation

Mysql_user = root # set according to your actual situation

Mysql_pass = 123456 # Set it based on your actual situation

Now_date = 'date + % Y % m % d % H % m'

If [! -D $ dir_backup]; then

Echo-e "\ e [1; 31 m the main directory for saving the backup: $ dir_backup does not exist. New. \ e [0 m" will be created automatically"

Mkdir-p $ {dir_backup}

Export black = '\ 033 [0m'

Export boldblack = '\ 033 [1; 0m'

Export red = '\ 033 [31m'

Export boldred = '\ 033 [1; 31m'

Export green = '\ 033 [32m'

Export boldgreen = '\ 033 [1; 32m'

Export yellow = '\ 033 [33m'

Export boldyellow = '\ 033 [1; 33m'

Export blue = '\ 033 [34m'

Export boldblue = '\ 033 [1; 34m'

Export magenta = '\ 033 [35m'

Export boldmagenta = '\ 033 [1; 35m'

Export cyan = '\ 033 [36m'

Export boldcyan = '\ 033 [1; 36m'

Export white = '\ 033 [37m'

Export boldwhite = '\ 033 [1; 37m'

Cecho ()

# -- Function to easliy print colored text --##

# Color-echo.

# Parameter $1 = message

# Parameter $2 = color

{

Local default_msg = "No message passed ."

Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.

Color = $ {2:-black} # If $1 is not input, the default value is black.

Case $ color in

Black)

Printf "$ black ";;

Boldblack)

Printf "$ boldblack ";;

Red)

Printf "$ red ";;

Boldred)

Printf "$ boldred ";;

Green)

Printf "$ green ";;

Boldgreen)

Printf "$ boldgreen ";;

Yellow)

Printf "$ yellow ";;

Boldyellow)

Printf "$ boldyellow ";;

Blue)

Printf "$ blue ";;

Boldblue)

Printf "$ boldblue ";;

Magenta)

Printf "$ magenta ";;

Boldmagenta)

Printf "$ boldmagenta ";;

Cyan)

Printf "$ cyan ";;

Boldcyan)

Printf "$ boldcyan ";;

White)

Printf "$ white ";;

Boldwhite)

Printf "$ boldwhite ";;

Esac

Printf "% s \ n" "$ message"

Tput sgr0 # tput sgr0 restores the default value

Printf "$ black"

Return

}

Cechon ()

# Color-echo.

# Parameter 1 $1 = message

# Parameter 2 $2 = color

{

Local default_msg = "No message passed ."

# Doesn't really need to be a local variable.

Message =$ {1:-$ default_msg} # If $1 is not input, the default value is default_msg.

Color = $ {2:-black} # If $1 is not input, the default value is black.

Case $ color in

Black)

Printf "$ black ";;

Boldblack)

Printf "$ boldblack ";;

Red)

Printf "$ red ";;

Boldred)

Printf "$ boldred ";;

Green)

Printf "$ green ";;

Boldgreen)

Printf "$ boldgreen ";;

Yellow)

Printf "$ yellow ";;

Boldyellow)

Printf "$ boldyellow ";;

Blue)

Printf "$ blue ";;

Boldblue)

Printf "$ boldblue ";;

Magenta)

Printf "$ magenta ";;

Boldmagenta)

Printf "$ boldmagenta ";;

Cyan)

Printf "$ cyan ";;

Boldcyan)

Printf "$ boldcyan ";;

White)

Printf "$ white ";;

Boldwhite)

Printf "$ boldwhite ";;

Esac

Printf "% s" "$ message"

Tput sgr0 # tput sgr0 restores the default value

Printf "$ black"

Return

}

Judegedate_01 = "judegedate01. 'date too many yymm1_d1_hsf-m=s'.txt"

Schema_judege01 = "select schema_name from information_schema.schemata ;"

Mysql-h $ {mysql_host}-P $ {mysql_port}-u $ {mysql_user}-p $ {mysql_pass}-e "$ {schema_judege01}" >$ {judegedate_01}

Echo-e "\ e [1; 31 m The databases name in current instance is: \ e [0 m"

Awk 'nr = 2, NR = 0 {print $1} '$ {judegedate_01}

Echo ""

# Get the current script path and create a file named database.txt

# In order to save the backup databases name.

Filepath = $ (cd "$ (dirname" $0 ")"; pwd)

If [! -S "$ {filepath}/database.txt"]; then

Echo "databases.txt will be created in the current directory ."

Touch $ {filepath}/database.txt

Echo "# Each line is stored a valid database name" >$ {filepath}/database.txt

Chmod 700 $ {filepath}/database.txt

# Remove the comment line

Awk 'nr = 2, NR = 0 {print $1} '$ {filepath}/database.txt >$ {filepath}/tmpdatabases.txt

# To determine whether a file is empty

If [-s $ {filepath}/tmpdatabases.txt]; then

# Start Time

Started_time = 'date + % s'

Echo "backup Start Time: $ {started_time }"

Db_num00 = 'awk' NR = 1, NR = 0 {print NR} '$ {filepath}/tmpdatabases.txt | tail-n1'

Echo "$ {db_num00} databases will be backed up this time :"

Echo

Or_dbnum = 0

For I in 'awk' NR = 1, NR = 0 {print $1} '$ {filepath}/tmpdatabases.txt ';

(Or_dbnum + = 1 ))

Mysql_databases = $ I

Db_dpname = $ dir_backup/$ {I}. $ {now_date}

Echo-e "\ e [1; 32 m mydumper start to back up the $ {or_dbnum} database $ I... \ e [0 m"

Sleep 2

Echo-e "\ e [1; 32 m mydumper live backup... wait a moment... \ e [0 m"

$ {Mydumper }\

-- Database =$ {mysql_databases }\

-- Host =$ {mysql_host }\

-- Port =$ {mysql_port }\

-- User =$ {mysql_user }\

-- Password =$ {mysql_pass }\

-- Outputdir =$ {db_dpname }\

-- No-schemas \

-- Rows = 50000 \

-- Build-empty-files \

-- Threads = 4 \

-- Compress-protocol \

-- Kill-long-queries

If ["$? "-Eq 0]; then

Echo-e "\ e [1; 32 m mydumper successfully backed up database $ I to: $ {db_dpname}. \ e [0 m"

Echo

Else

Echo-e "\ e [1; 31 m backup ended abnormally. \ e [0 m"

Done

Else

Ipname =''

Read-p "Please input you want to backup database name [a | A: ALL]:" ipname

# Start Loop

# Start Time

Started_time = 'date + % s'

Echo "backup Start Time: $ {started_time }"

If ["$ ipname" = "a"-o "$ ipname" = "A"]; then

Db_num = 'awk' NR = 2, NR = 0 {print NR-1} '$ {judegedate_01} | tail-n1'

Echo "$ {db_num} databases will be backed up this time :"

Echo

Mysql_databases = $ ipname

Or_dbnum = 0

For I in 'awk' NR = 2, NR = 0 {print $1} '$ {judegedate_01 }';

(Or_dbnum + = 1 ))

Mysql_databases = $ I

Db_dpname = $ dir_backup/$ {I}. $ {now_date}

Echo-e "\ e [1; 32 m mydumper start to back up the $ {or_dbnum} database $ I... \ e [0 m"

Sleep 2

Echo-e "\ e [1; 32 m mydumper live backup... wait a moment... \ e [0 m"

$ {Mydumper }\

-- Database =$ {mysql_databases }\

-- Host =$ {mysql_host }\

-- Port =$ {mysql_port }\

-- User =$ {mysql_user }\

-- Password =$ {mysql_pass }\

-- Outputdir =$ {db_dpname }\

-- No-schemas \

-- Rows = 50000 \

-- Build-empty-files \

-- Threads = 4 \

-- Compress-protocol \

-- Kill-long-queries

If ["$? "-Eq 0]; then

Echo-e "\ e [1; 32 m mydumper successfully backed up database $ I to: $ {db_dpname}. \ e [0 m"

Echo

Else

Echo-e "\ e [1; 31 m backup ended abnormally. \ e [0 m"

Done

Else

Echo "the database name for this backup is $ ipname"

Echo

# Start Time

STARTED_TIME = 'date + % s'

Mysql_databases = $ ipname

Db_dpname = $ dir_backup/$ {mysql_databases}. $ {now_date}

Judegedate_02 = "judegedate02. 'date parameter policyymm1_d1_h1_m1_s'.txt"

Schema_judege02 = "select schema_name from information_schema.schemata where schema_name = '$ {ipname }';"

Mysql-h $ {mysql_host}-P $ {mysql_port}-u $ {mysql_user}-p $ {mysql_pass}-e "$ {schema_judege02}" >$ {judegedate_02}

If [! -S "$ {judegedate_02}"]; then

Echo ""

Echo-e "\ e [1; 31 m ************************************** * *************************** \ e [0 m"

Echo-e "\ e [1; 31 m! O (too many) o! The schema_name $ {ipname} not exits, pleae check .~~~~ (>_< )~~~~ \ E [0 m"

Echo-e "\ e [1; 31 m ************************************** * ****************************** \ e [0 m"

Echo ""

Rm-rf $ {judegedate_01}

Rm-rf $ {judegedate_02}

Exit 0

Else

Echo-e "\ e [1; 32 m mydumper start backup please wait... \ e [0 m"

Sleep 2

Echo-e "\ e [1; 32 m mydumper live backup... wait a moment... \ e [0 m"

$ {Mydumper }\

-- Database =$ {mysql_databases }\

-- Host =$ {mysql_host }\

-- Port =$ {mysql_port }\

-- User =$ {mysql_user }\

-- Password =$ {mysql_pass }\

-- Outputdir =$ {db_dpname }\

-- No-schemas \

-- Rows = 50000 \

-- Build-empty-files \

-- Threads = 4 \

-- Compress-protocol \

-- Kill-long-queries

If ["$? "-Eq 0]; then

Echo-e "\ e [1; 32 m mydumper successfully backs up the database to: $ {db_dpname}. \ e [0 m"

Else

Echo-e "\ e [1; 31 m backup ended abnormally. \ e [0 m"

# Loop end

Rm-rf $ {judegedate_01}

Rm-rf $ {judegedate_02}

Rm-rf $ {filepath}/tmpdatabases.txt

Echo "completed at: 'date + % f'' % t' % W '"

The execution result is as follows :#####################

The databases name in current instance is:

Information_schema

Mysql

Performance_schema

Test

Wind

Mpiao

Mpadmin

Mplog

Please input you want to backup database name [a | A: ALL]:

Start Time of backup: 1431574874

Eight databases will be backed up this time:

Mydumper starts to back up 1st databases information_schema .....