MySQL Backup--使用mysqldump依次备份所有数据库
某些场景下需要将数据库分开备份，有些场景又需要将所有数据库合在一起备份，特此整理此备份脚本
#!/bin/bash ##===========================================================================## ## use mysqldump to backup all user database, keey 5 days. ##===========================================================================## ## 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=3306 mysql_backup_user="root" mysql_backup_password="rootpwd" mysql_backup_database="ONE_BACKUP" mysql_backup_table_name_list="" mysql_backup_keep_days=5 ##====================================================## ## get mysql 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" else mysql_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=$1 mysql_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} else echo "$(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" else backup_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} else echo "$(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_databases else database_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} else echo "$(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} else echo "$(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_5 else dump_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
