mysql 全量备份和增量备份

来源：互联网发布：劲舞团解压数据错误编辑：程序博客网时间：2024/04/28 03:24

mysql 全量备份脚本

#!/bin/bashData_back="/data/data_backup/";Date=`date +"%Y_%m_%d"`mysqldump -uroot -proot  --quick --events --flush-logs  --single-transaction  XXXX> ${Data_back}XXXX_${Date}.sql

mysql 增量备份脚本

#!/bin/bashBinlogdir="/usr/local/mysql/var/"Binlogbackup="/data/data_binlog/"Date=`date +"%Y_%m_%d"`Binloglis=(`ls ${Binlogdir} | grep mysql-bin.0`)binlognum=${#Binloglis[@]}start_time=`date +%Y-%m-%d_%H:%M:%S`mysql -uroot -proot -e "flush logs;"echo -e "\n\n\n"echo "-------------binlog_back_up start at "${start_time}"-----------------------------------"for((i=0;i<$binlognum;i++));doif [ ${i} == `expr ${binlognum} - 1` ]then        echo ${Binloglis[i]}": last file"else        backupfilename=${Binlogbackup}${Binloglis[i]}        backfilename=${Binlogdir}${Binloglis[i]}        if [ -e $backupfilename ]        then                echo ${backupfilename}": backup file exist"        else                cp $backfilename $backupfilename                if [ -e $backupfilename ]                then                        echo ${backupfilename}": backup success"                else                        echo ${backupfilename}": backup fail"                fi        fifidoneend_time=`date +%Y-%m-%d_%H:%M:%S`echo "-------------binlog_back_up start at "${end_time}"-----------------------------------"

binlog 备份恢复操作

mysqlbinlog --no-defaults --start-position="4" --stop-position="1285" E:\Mysql57BinLog\binlog.000006  | mysql -u root -p  恢复数据从一个节点到另一个节点的数据。a、提取指定的binlog日志# mysqlbinlog /opt/data/APP01bin.000001  # mysqlbinlog /opt/data/APP01bin.000001|grep insert  /*!40019 SET @@session.max_insert_delayed_threads=0*/;insert into tb values(2,'jack')b、提取指定position位置的binlog日志# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001  c、提取指定position位置的binlog日志并输出到压缩文件# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz  d、提取指定position位置的binlog日志导入数据库# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p  e、提取指定开始时间的binlog并输出到日志文件# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql  f、提取指定位置的多个binlog日志文件# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more  g、提取指定数据库binlog并转换字符集到UTF8# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql  h、远程提取日志，指定结束时间# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more  i、远程提取使用row格式的binlog日志并输出到本地文件# mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql  ~

阅读全文

'); })();