mysql backup table_MySQL Backup--使用mysqldump依次备份所有数据库

#!/bin/bash

##===========================================================================##

## use mysqldump to backup all user database, keey5days.

##===========================================================================##

## mysql_backup_database option:

## ONE_BACKUP: backup all user database into one zip file

## MORE_BACKUP: backup user database into different zip files.

## DATABASE_NAME: only backup the specified database.

##===========================================================================##

## mysql backup config

mysql_exe="/export/servers/mysql/bin/mysql"mysqldump_exe="/export/servers/mysql/bin/mysqldump"mysql_backup_folder="/export/data/mysql/dumps/"mysql_backup_log="${mysql_backup_folder}mysql_dump_log.txt"mysql_backup_log_his="${mysql_backup_folder}mysql_dump_log_his.txt"mysql_backup_host="127.0.0.1"mysql_backup_port=3306mysql_backup_user="root"mysql_backup_password="rootpwd"mysql_backup_database="ONE_BACKUP"mysql_backup_table_name_list=""mysql_backup_keep_days=5##====================================================##

##getmysql version

##====================================================##

function get_mysql_version()

{

master_version_tmp=`${mysql_exe} \--host="${mysql_backup_host}" --port=${mysql_backup_port} \--user="${mysql_backup_user}" --password="${mysql_backup_password}"\-e "select @@version;"`if [[ master_version_tmp == 5.5.*]]

then

mysql_version="mysql55"elif [[ master_version_tmp== 5.6.*]]

then

mysql_version="mysql56"

elsemysql_version="mysql57"fi

}

##===========================================================================##

## remove expired backup file

## keep the backup file of the last N days

function remove_expired_file()

{

echo"$(date"+%Y-%m-%d %H:%M:%S") start to remove expired backup file." >>${mysql_backup_log}

echo"keep days: ${mysql_backup_keep_days}" >>${mysql_backup_log}

find"${mysql_backup_folder}" -mtime +${mysql_backup_keep_days} -name "*" -exec rm -rf {} \;

echo"$(date"+%Y-%m-%d %H:%M:%S") start to mysqldump." >>${mysql_backup_log}

}

##===========================================================================##

## backup single database

function backup_single_database()

{

current_database_name=$1mysql_backup_file_path="${mysql_backup_folder}""${current_database_name}-`date -I`.sql.gz"echo"$(date"+%Y-%m-%d %H:%M:%S") start to backup database ${current_database_name} to ${mysql_backup_file_path}" >>${mysql_backup_log}

($mysqldump_exe \--host="${mysql_backup_host}"\--port=$mysql_backup_port \--user="${mysql_backup_user}"\--password="${mysql_backup_password}"\--databases "${current_database_name}"\--set-gtid-purged=OFF \--single-transaction \--hex-blob --opt --quick \--events --routines --triggers \--default-character-set="utf8"\--master-data=2\|gzip > "${mysql_backup_file_path}"\

)1>>${mysql_backup_log} 2>>${mysql_backup_log}if [ $? = 0]

then

echo"$(date"+%Y-%m-%d %H:%M:%S") backup database ${current_database_name} success." >>${mysql_backup_log}elseecho"$(date"+%Y-%m-%d %H:%M:%S") backup database ${current_database_name} failed." >>${mysql_backup_log}

fi

}

function backup_more_databases()

{for database_name in${database_name_list};do

if [ "$database_name" == ""]

then

echo"database name can be empty"

elsebackup_single_database"${database_name}"fi

done

}

function backup_all_databases()

{

mysql_backup_file_path="${mysql_backup_folder}""full-backup-`date -I`.sql.gz"echo"$(date"+%Y-%m-%d %H:%M:%S") start to backup all databases to ${mysql_backup_file_path}" >>${mysql_backup_log}

($mysqldump_exe \--host="${mysql_backup_host}"\--port=$mysql_backup_port \--user="${mysql_backup_user}"\--password="${mysql_backup_password}"\--all-databases \--ignore-table='mysql.*'\--ignore-table='information_schema.*'\--ignore-table='performance_schema.*'\--ignore-table='sys.*'\--set-gtid-purged=OFF \--single-transaction \--hex-blob --opt --quick \--events --routines --triggers \--default-character-set="utf8"\--master-data=2\|gzip > "${mysql_backup_file_path}"\

)1>>${mysql_backup_log} 2>>${mysql_backup_log}if [ $? = 0]

then

echo"$(date"+%Y-%m-%d %H:%M:%S") backup database ${current_database_name} success." >>${mysql_backup_log}elseecho"$(date"+%Y-%m-%d %H:%M:%S") backup database ${current_database_name} failed." >>${mysql_backup_log}

fi

}

function backup_databases()

{if [ "${mysql_backup_database}" == "ONE_BACKUP"];

then

backup_all_databases

elif ["${mysql_backup_database}" == "MORE_BACKUP"];

then

database_name_list=`${mysql_exe} --host="${mysql_backup_host}" --port=$mysql_backup_port --user="${mysql_backup_user}" --password="${mysql_backup_password}" -Ne "show databases;" |egrep -v "(mysql|sys|test|information_schema|performance_schema|dada_call_center)"`

backup_more_databaseselsedatabase_name_list=${mysql_backup_database}

backup_more_databases

fi

}

##====================================================##

##1. dump user script on mysql

##2. this script only can be used on mysql 5.7##====================================================##

function dump_user_script_5_7()

{

script_file_path="${mysql_backup_folder}""user-script-`date -I`.sql"echo"$(date"+%Y-%m-%d %H:%M:%S") start backup user script." >>${mysql_backup_log}

(echo"select concat('show create user ''',user,'''@''',host, ''';

','show grants for ''',user,'''@''',host,''';')

from mysql.user where user <>'root' and user<>'' and host <> '' "| \

${mysql_exe} --host="${mysql_backup_host}" --port=${mysql_backup_port} \--user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |\

${mysql_exe}--host="${mysql_backup_host}" --port=${mysql_backup_port} \--user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |\

sed"s/$/;/" >> ${script_file_path}) 1>>${mysql_backup_log} 2>>${mysql_backup_log}if [ $? = 0]

then

echo"$(date"+%Y-%m-%d %H:%M:%S") backup user script success." >>${mysql_backup_log}elseecho"$(date"+%Y-%m-%d %H:%M:%S") backup user script failed." >>${mysql_backup_log}

fi

}

##====================================================##

##1. dump user script on mysql

##2. this script only can be used on mysql 5.5##====================================================##

function dump_user_script_5_5()

{

script_file_path="${mysql_backup_folder}""user-script-`date -I`.sql"echo"$(date"+%Y-%m-%d %H:%M:%S") start backup user script." >>${mysql_backup_log}

(echo"select concat('show grants for ''',user,'''@''',host, ''';')

from mysql.user where user <>'root' and user<>'' and host <> '' "| \

${mysql_exe} --host="${mysql_backup_host}" --port=${mysql_backup_port} \--user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |\

${mysql_exe}--host="${mysql_backup_host}" --port=${mysql_backup_port} \--user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |\

sed"s/$/;/" >> ${script_file_path}) 1>>${mysql_backup_log} 2>>${mysql_backup_log}if [ $? = 0]

then

echo"$(date"+%Y-%m-%d %H:%M:%S") backup user script success." >>${mysql_backup_log}elseecho"$(date"+%Y-%m-%d %H:%M:%S") backup user script failed." >>${mysql_backup_log}

fi

}

##===========================================================================##

function backup_mysql_user()

{if [[ mysql_version == "mysql55"]]

then

dump_user_script_5_5elsedump_user_script_5_7

fi

}

##===========================================================================##

function mysql_backup()

{

echo>${mysql_backup_log}

get_mysql_version

remove_expired_file

backup_databases

backup_mysql_user

cat ${mysql_backup_log}>${mysql_backup_log_his}

}

mysql_backup