1. <mark id="s2zkv"><ol id="s2zkv"></ol></mark>
      <mark id="s2zkv"></mark>

        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
        相关文章
        相关标签/搜索
        最准三码中特2020年香港六合马会开奖结果现场直播开奖历史资料记录在线查询网二四六天天好彩免费资料大全