Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve concurrent backup in versions below mysqldump 5.7". The content is simple and easy to understand, and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn this article "how to achieve concurrent backup in versions below mysqldump 5.7".

mysqldump5.7 and below multithreaded backup single table [background description]

mysqldump is useful for backing up single tables or libraries of an order of magnitude smaller. Under normal circumstances innobackupex backup orders of magnitude of large libraries, the speed is very fast. But the bottleneck is that if the business needs to migrate multi-instance partial objects to new instances, this situation cannot be met. (MyQLdumper is not discussed here).

Here is a brief list of scenarios where mysqldump applies:

Backup multiple single tables

Backup one or more libraries

Backup stored procedures, custom functions, or events

Backup data only, not table structure

Backup table structure only, no data

other

Although mysqldump is flexible to use, it cannot achieve concurrent backup, so this article describes how to achieve concurrent backup with mysqldump.

[Description of train of thought]

Take one or more libraries to be backed up and extract all the tables below these libraries for backup one by one: in this way, scripts can be used to backup these single tables in multiple threads, thus realizing concurrent backup at the library level.

[Specific Script]

Click here to fold or open

#!/ bin/bash

#Comment: mysqldump multithreaded backup multitable

#Auther:cyt

#date:2016-06-23

#According to multi-instance loop framework

function instance()

{

for port in `ps -ef | grep -v -E "mysqld_safe|awk" | awk '/mysqld /,/port=/''{for(i=1;i'$BACKUP_DIR'/'${a}'/'${j}'.sql'>>$BACKUP_DIR/cyt.log;

done

done

}

#Call function log, view log Call concurrent function to implement multithreaded backup

function dumpAllTable()

{

local schemaFile="${BACKUP_DIR}/cyt.log"

#Largest table backed up first (due to multiple concurrent processes, the shortest completion time depends on the completion of the largest table)

allTable=`cat $schemaFile | wc -l`

i_import=0

declare -a array_cmds

i_array=0

while read file; do

i_import=`expr $i + 1`

array_cmds[i_array]="${file}"

i_array=`expr ${i_array} + 1`

done

< ${BACKUP_DIR}/cyt.log execConcurrency "${threadsNum}" "${array_cmds[@]}" } #并发函数 function execConcurrency() { #并发数据量 local thread=$1 #并发命令 local cmd=$2 #定义管道，用于控制并发线程 tmp_fifofile="/tmp/$$.fifo" mkfifo $tmp_fifofile #输入输出重定向到文件描述符6 exec 6$tmp_fifofile rm -f $tmp_fifofile #向管道压入指定数据的空格 for ((i=0;i&6 #遍历命令列表 while [ "$cmd" ]; do #从管道取出一个空格（如无空格则阻塞，达到控制并发的目的） read -u6 #命令执行完后压回一个空格 { eval $2;echo >

&6; } & #> /dev/null 2>&1 &

shift

cmd=$2

done

#Wait for all background child processes to end

wait

#Close df6

exec 6>&-

}

#Compress backup files

function tardump()

{

#Use tar compression

if [ -d ${BACKUP_DIR} ] && [ -n ${port} ]

then

echo "-----Start backup of mysql instance with compressed port number '$port': start time'`date '+%Y-%m-%d %H:%M:%S'`

cd $BACKUP_DIR;

for b in `find $BACKUP_DIR -maxdepth 1 -type d ! -iname "${port}*" ! -iname '*.sql' ! -iname '*tar.gz' `

do

c=`basename $b`

tar -zcvf $c'_'$(date +%F_%H-%M).tar.gz $c --remove-files > /dev/null

done

else echo "No files available for compression";

fi;

echo "----Compress backup file of mysql instance with port number '$port': end time'`date '+%Y-%m-%d %H:%M:%S'`

}

#Main function

function main()

{

#Get local IP address

host=`ifconfig | grep 'inet addr:' | grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}'`

DATE=`date +%F`

#mysqldump --host --socket If this backup is a local user backup, it is recommended to remove host;(multi-instance local user password problem should be noted)

#Database users

DB_USER='cyt'

#Database user password

DB_PASSWORD='cyt'

#Record start time

BEGIN=`date "+%Y-%m-%d %H:%M:%S"`

BEGIN_T=`date -d "$BEGIN" +%s`

echo '-----------------Start concurrent backup by table: Start time is'$BEGIN

#Set the number of threads for concurrent backups

threadsNum=10

#Call instance function

instance

echo '--------------backup all database successfully!!! End time:' `date "+%Y-%m-%d %H:%M:%S"`

}

main

[Script Description]

Since the script is backed up in parallel, in case the dump connection is not available due to busy, this parameter is increased (the database version is 5.6.19, and the script will be decreased after the backup is completed)

mysql -u$DBUSER -p$DBPASSWORD --host= --socket=$sock --host=$host -BN -e "SET GLOBAL netwritetimeout=1800";

Since you want to take advantage of concurrent functions, import the commands to be used into the ${BACKUP_DIR}/cyt. log, and then achieve the purpose of this script through the concurrent function execConcurrency and the array dumpAllTable

This script can implement multi-instance backup. If the username and password of multi-instance backup are different, you can use case command. The following is a simple example.

Click here to fold or open

if [ $port -eq 3306 ]; then

case $IP in

'10.240.5.11')

DB_USER='CYT1'

DB_PASSWORD='1'

;;

'10.240.5.12')

DB_USER='CYT2'

DB_PASSWORD='2'

;;

'10.240.5.13')

DB_PASSWORD='3'

;;

esac

else

DB_PASSWORD='4'

fi

The above is "mysqldump5.7 version below how to achieve concurrent backup" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.