<address id="rphpx"><listing id="rphpx"></listing></address>

<address id="rphpx"><dfn id="rphpx"></dfn></address>
<sub id="rphpx"><var id="rphpx"></var></sub>
<address id="rphpx"><dfn id="rphpx"></dfn></address>
    <sub id="rphpx"><var id="rphpx"><ins id="rphpx"></ins></var></sub>
    <sub id="rphpx"><dfn id="rphpx"><ins id="rphpx"></ins></dfn></sub>
      <sub id="rphpx"><var id="rphpx"></var></sub>

      <sub id="rphpx"><var id="rphpx"></var></sub>

        <sub id="rphpx"><var id="rphpx"><ins id="rphpx"></ins></var></sub>

        <thead id="rphpx"><var id="rphpx"><output id="rphpx"></output></var></thead>

        <sub id="rphpx"><listing id="rphpx"></listing></sub>

          <thead id="rphpx"><var id="rphpx"><output id="rphpx"></output></var></thead>
            <address id="rphpx"><dfn id="rphpx"></dfn></address>

            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
            相关文章
            相关标签/搜索
            白小姐四不像必中一肖+正版+网址930