MySQL Backup – Back up all databases in turn using mysqldump

In some scenarios, the databases need to be backed up separately, and in some scenarios, all the databases need to be backed up together. Hereby we organize this backup script

#! /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< /span>"

mysqldump_exe="/export/servers/mysql/bin/mysqldump< /span>"

mysql_backup_folder="/export/data/mysql/dumps/ span>"

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;" span>`

 if [[ master_version_tmp == 5.5.*< span style="color: #000000;"> ]]

 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}< span style="color: #800000;">" >> ${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=" span>${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} < span style="color: #800080;">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=" span>${mysql_backup_password}" --all-databases --ignore-table='< span style="color: #800000;">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}" == "< span style="color: #800000;">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<>< span style="color: #800000;">'' and host <> '' " | \

 ${mysql_exe} --host="${mysql_backup_host}< span style="color: #800000;">" --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} span>" --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<>< span style="color: #800000;">'' and host <> '' " | \

 ${mysql_exe} --host="${mysql_backup_host}< span style="color: #800000;">" --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} span>" --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

#!/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< /span>"

mysqldump_exe="/export/servers/mysql/bin/mysqldump< /span>"

mysql_backup_folder="/export/data/mysql/dumps/ span>"

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