完美的mysqldump数据备份

mysqldump也值得写一批分享？不是一条命令就搞定了么？我只能说呵呵了。当你遇到上百G的数据库，还原的时候只能给你提供一个16G内存的服务器，你怎么解决呢？嗯这种鬼故事我就遇到了，分享一下我的解决方案，本方案要实现的目标：

1. 表结构和数据能分开备份
2. 数据在还原的时候，可以被切分成大小合适的子文件
3. 顺带加了一下自动备份的shell脚本

表结构和数据分开备份的脚本

#!/bin/bash
source /etc/profile
backup_base_dir=/data/mysql_backup/
yesterday=$(date -d "-1 day" +%Y%m%d)
backup_date=$(date +%Y%m%d)
db_name=$1

args_num=$#
params=($*)
ignore_tables=''
if(($args_num > 1))
then
        for((i=2;i<=$args_num;i++));
        do
            ignore_tables=("${ignore_tables} --ignore-table=$1.${params[$i-1]}")
        done
fi

backup_db(){
        #创建备份日期目录,如20160831
        backup_date_dir=$backup_base_dir/$backup_date/
        error_log_dir=$backup_base_dir/err_log/
        mkdir -p $backup_date_dir
        mkdir -p $error_log_dir

        #判读数据库是否可用,可用就进行备份,不可用则打印错误日志
	echo "数据库:${db_name}"
        mysql -u user -p****  -h 127.0.0.1 -P 3306 -e "use ${db_name}"
	
        if [  $? -eq 0 ];then
		echo "开始备份:${db_name}"
		mysqldump -u user -p****  -h 127.0.0.1 -P 3306 --lock-tables=false -d -R ${db_name} > ${backup_date_dir}${db_name}_structure.sql
		
		mysqldump -u user -p****  -h 127.0.0.1 -P 3306 --opt --default-character-set=utf8  --lock-tables=false ${ignore_tables}   --routines --hex-blob --verbose  --triggers --skip-extended-insert --skip-comments --compact  --skip-add-locks -t ${db_name} > ${backup_date_dir}${db_name}_data.sql

		echo "备份完成:${db_name}"
                cd ${backup_date_dir}
                zip -P'压缩密码' ${db_name}_${backup_date}.sql.zip ./${db_name}*
                rm ${db_name}_data.sql
		rm ${db_name}_structure.sql
                if [ $? -ne 0 ];then
                        #验证备份是否完全成功
                        echo "$backup_date"" 备份失败" >> ${error_log_dir}error_${db_name}${backup_date}.log
                fi
        else
                echo "$backup_date"" 数据库连接不上" >> ${error_log_dir}error_${db_name}${backup_date}.log
        fi
}
backup_db

#!/bin/bash
echo ==============$(date +%Y%m%d)
backup_base_dir=/data/mysql_backup/
yesterday=$(date -d "-1 day" +%Y%m%d)
error_log_dir=$backup_base_dir/err_log/
today=$(date -d "-0 day" +%Y%m%d)

cd $backup_base_dir

#删除配置天数之前的备份
find . -type f -mtime +7 -name '*.tar' | xargs rm -rf

#删除60天之前的错误日志
cd $error_log_dir
find . -type f -mtime +3 -name 'error_*.log' | xargs rm -rf

while read line
do
        echo back up $line...
        /home/bisadm/shell/db_div_back.sh $line
done < /home/bisadm/shell/dblist_4_div_back.txt

cd $backup_base_dir
tar -cvf ${today}_div.tar $today
rm -rf $today

在dblist_4_div_back.txt 文件中，每行代表一个需要被备份的数据库名称，后面可以跟上多个允许被忽略备份的表，例如某个数据库中的日志表可以不被备份

数据文件切割代码

   /**
     * 按行分割文件
     * @param sourceFilePath 为源文件路径
     * @param targetDirectoryPath 文件分割后存放的目标目录
     * @param rows 为多少行一个文件
     */
    public static int splitFileByLine(String sourceFilePath, String targetDirectoryPath, int rows) {
        String sourceFileName = sourceFilePath.substring(sourceFilePath.lastIndexOf(File.separator) + 1, sourceFilePath.lastIndexOf("."));//源文件名
        String splitFileName = targetDirectoryPath + File.separator + sourceFileName + "-%s.sql";//切割后的文件名
        File targetDirectory = new File(targetDirectoryPath);
        if (!targetDirectory.exists()) {
            targetDirectory.mkdirs();
        }
 
        PrintWriter pw = null;//字符输出流
        String tempLine;
        int lineNum = 0;//本次行数累计 , 达到rows开辟新文件
        int splitFileIndex = 1;//当前文件索引
 
        try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(sourceFilePath)))) {
            pw = new PrintWriter(String.format(splitFileName, splitFileIndex));
            pw.write("set FOREIGN_KEY_CHECKS = 0;\n");
            pw.write("START TRANSACTION;\n");
            while ((tempLine = br.readLine()) != null) {
                if (lineNum > 0 && lineNum % rows == 0) {//需要换新文件
                        pw.write("COMMIT;\n");
                        pw.write("set FOREIGN_KEY_CHECKS = 1;");
                    pw.flush();
                    pw.close();
                    pw = new PrintWriter(String.format(splitFileName , ++splitFileIndex));
                    pw.write("set FOREIGN_KEY_CHECKS = 0;\n");
                    pw.write("START TRANSACTION;\n");
                }
                pw.write(tempLine + "\n");
                lineNum++;
            }
            pw.write("COMMIT;\n");
            pw.write("set FOREIGN_KEY_CHECKS = 1;");
            pw.flush();
            pw.close();
            return splitFileIndex;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }finally {
            if (null != pw) {
                pw.flush();
                pw.close();
            }
        }
    }
    public static void main(String ...args) {
            splitFileByLine("finance_data.sql","finance_data_split",200000);
    }