mysql全備份+增量備份筆記總結

標籤：備份   mysql   基礎知識   二進位   events   

備份基礎知識

冷備（cold backup）：需要關mysql服務，讀寫請求均不允許狀態下進行；

溫備（warm backup）： 服務線上，但僅支援讀請求，不允許寫請求；

熱備（hot backup）：備份的同時，業務不受影響。

這種類型的備份，取決於業務的需求，而不是備份工具

MyISAM不支援熱備，InnoDB支援熱備，但是需要專門的工具

完全備份：full backup，備份全部字元集。

增量備份: incremental backup 上次完全備份或增量備份以來改變了的資料，不能單獨使用，要藉助完全備份，備份的頻率取決於資料的更新頻率。

差異備份：differential backup 上次完全備份以來改變了的資料。

建議的恢複策略：

完全+增量+二進位日誌

完全+差異+二進位日誌

全備份

MyISAM：

mysql -uroot  -p‘123456‘ -A  -B -F   –flush-privileges  –master-data=2 –x -–events | gzip > /opt/x_$(date +%F).sql.gz

InnoDB:

mysqldump    -uroot -p123456  --single-transaction   -A -B -F  --events | gzip > /server/backup/x_$(date +%F).sql.gz

--single-transaction    MySIM直接選-x(--lock-all-tables)參數鎖表，InnoDB選擇這個參數來保證備份的一致性。相當於設定一個隔離等級，REPEATABLE READ ，以確保本次會話dump時，不會看到其它會話已經提交的資料。

--master-data[=#]     自動找到binlog的位置  This causes the binary log position andfilename to be

   如： --master-data=1     可以不用重新整理binlog了，做增量備份的時候很有用

       --master-data=2    後會加個注釋，好找些

-E, --events        Dump events.

-A, --all-databases Dump  匯出所有資料，一般加-B 共用+--events all

-B,  --databases    指定多個庫名備份    直觀看，加上-B參數作用是增加建立資料庫和串連資料庫的命令，生產環境備份必用。

-F, --flush-logs    重新整理，切割binlog

補充：上訴兩種備份如果資料庫有預存程序和觸發器還得加兩個參數：

--triggers  –routines  --hex-blob，   一般公司無這三個參數。

觸發器       預存程序   如果你庫中有blob欄位,而你又沒加這個參數 ,那你的blog大欄位資料就會丟失

分庫備份

mysql  -uroot -p‘123456‘ -e "show databases;" | grep -Evi "Database|infor|perf" | sed -r ‘s#^([a-z].*$)#mysqldump -uroot-p‘123456‘  --events -B  \1 | gzip > /tmp/logs/\1.sql.gz#g‘      | bash

-t,--no-create-info     如果希望只匯出表資料

-d, --no-data           只備份表結構No row information.

報錯

mysqlbinlog:unknownvariable‘default-character-setutf8‘解決方案

mysqlbinlog  --no-defaults   ./mysql-bin.000007   

mysql全量匯出時碰到如下警示:                     預設是不備份事件表的,只有加了--events 才會不警告

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly

解決辦法:

--events --ignore-table=mysql.event

ERROR 1046 (3D000) at line 22: No database selected

修改.sql   在22行前面加上   use   庫名字;

恢複

首先恢複全備

mysql    -uroot  -p123456    <  /server/backup/x.sql

多分庫檔案恢複

for name in `ls *.sql| sed ‘s#.sql##g‘ `; do mysql -uroot -p123456    < ${name}.sql ; done

然後

mysqladmin -uroot -p123456 flush-log       //切割日誌

匯總所有的binlog, 把錯誤的刪除，

剩下的轉成sql語句

cp mysql-bin.000016  /server/backup/

mysqlbinlog  --no-defaults  -d user mysql-bin.000016   > bin.sql

mysql  -uroot -p123456  < bin.sql

執行 | mysql

根據binlog位置和時間回複

mysqlbinlog --start-postion=107  --stop-position=1000    -d   庫名       二進位檔案          

mysqlbinlog --start-datetime=‘2013-09-10 00:00:00‘ --stop-datetime=‘2013-09-10 01:01:01‘ -d   庫名 二進位檔案

egrep -v "#|\*|--|^$"   可以過濾查看備份內容

rsync配合定時任務

rsync -avz   /data/3306/mysql-bin.000*   [email protected]::backup  --password-file=/etc/rsync.passsword

一主多從，一個從 做備份

vim  .my.cnf        //設定登陸

[client]

user=root

host=localhost

password=123456

mysql> show  variables  like "character_set%";   //查看字元集

[client]                   //設定字元集

default-character-set=utf8

[mysqld]

character-set-server=utf8     //5.5

default-character-set=utf8   //5.1

[mysql]

default-character-set=utf8

開啟bin-log，存在一份全備份，及所有增量binlog檔案備份

mysql> show  full   processlist; //串連情況

mysql> show variables like "%log_bin%";

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |

| log_bin_trust_function_creators | OFF   |

| sql_log_bin                     | ON    |

+---------------------------------+-------+

mysql> flush  table with read lock;   //讀鎖

mysql> unlock  tables;  //解鎖

replicate_wild_ignore_table=mysql.%     //可以加萬用字元

--skip-name-resolve    選項啟動mysqld來禁用DNS主機名稱尋找

slave-skip-errors = 1032,1062   //排除1032，1062的保持。

lower_case_table_names=1  //讓MySQL不區分大小寫！  //慎用，會影響原來的表名字

[mysqld]

read-only  //唯讀，root不受限

主從不同步

SQL=NO的情況，可以用下面的命令跳過報錯

在slave處於stop狀態下，執行 set global sql_slave_skip_counter=N以跳過命令  N=1

slave 開啟從庫記錄binlog               級聯同步        當做Database Backup

log-bin = /data/3307/mysql-bin

log-slave-updates

expire_logs_days = 7     //保留7天

本文出自 “何全” 部落格，請務必保留此出處http://hequan.blog.51cto.com/5701886/1775333

mysql全備份+增量備份筆記總結