數(shù)據(jù)備份

1.mysqldump備份

語法：

mysqldump -uroot -p [database name] > [dump file]

列如：

mysqldump -uroot -p --all-databases? > test.sql? #備份所有數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)

mysqldump -uroot -p --databases a b > test.sql?#備份a教翩、b數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)

mysqldump -uroot -p a > test.sql?? #備份a數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)涕滋，但是生成的sql文件沒有CREATE DATABASE 和USE語句（不推薦）

mysqldump -uroot -proot --no-data --databases a >test.sql? #備份a數(shù)據(jù)庫的結(jié)構(gòu)

mysqldump -uroot -p --databases a --tables a1 a2 > test.sql?#備份a數(shù)據(jù)庫下a1,a2表結(jié)構(gòu)和數(shù)據(jù)（創(chuàng)建的sql語句沒有use選庫，恢復(fù)的時(shí)候得先登錄尖啡，在use dbname;source test.sql;）

mysqldump -hhost1 -uroot -proot --databases db1 |mysql -hhost2 -uroot -proot db2? #跨服務(wù)器導(dǎo)出導(dǎo)入數(shù)據(jù)莽红，將host1服務(wù)器中的db1數(shù)據(jù)庫的所有數(shù)據(jù)導(dǎo)入到host2中的db2數(shù)據(jù)庫中返弹，db2的數(shù)據(jù)庫必須存在否則會報(bào)錯(cuò)（可以加上 -C參數(shù)可以啟用壓縮傳遞）

mysqldump -uroot -p -B -F -R -x --master-data=2 a|gzip >ops_$(date +%F).sql.gz ? ? #-B：指定數(shù)據(jù)庫凹联，-F：刷新日志，-R：備份存儲過程等赏半，-x：鎖表贺归，--master-data：在備份語句里添加CHANGE MASTER語句以及binlog文件及位置點(diǎn)信息，備份a數(shù)據(jù)庫除破，輸出文件名年月日的gz格式

其余參數(shù)：

-no-create-info, -t 只導(dǎo)出數(shù)據(jù)牧氮，而不添加CREATE TABLE 語句

no-create-db, -n 只導(dǎo)出數(shù)據(jù)，而不添加CREATE DATABASE 語句

--ignore-table 導(dǎo)出數(shù)據(jù)庫時(shí)忽略某個(gè)表

--force瑰枫，-f 在導(dǎo)出過程中忽略出現(xiàn)的SQL錯(cuò)誤踱葛，當(dāng)出現(xiàn)錯(cuò)誤時(shí)仍然繼續(xù)后面的操作

--add-drop-database 每個(gè)數(shù)據(jù)庫創(chuàng)建之前添加drop數(shù)據(jù)庫語句

--add-drop-table 每個(gè)數(shù)據(jù)表創(chuàng)建之前添加drop數(shù)據(jù)表語句丹莲，默認(rèn)為打開狀態(tài)，使用–skip-add-drop-table取消選項(xiàng)

--add-locks在每個(gè)表導(dǎo)出之前增加LOCK TABLES并且之后UNLOCK TABLE尸诽，默認(rèn)為打開狀態(tài)甥材，使用–skip-add-locks取消選項(xiàng)

--default-character-set 設(shè)置默認(rèn)字符集，默認(rèn)值為utf8

--comments 附加注釋信息性含，默認(rèn)為打開洲赵，可以--skip-comments取消

--compact導(dǎo)出更少的輸出信息(用于調(diào)試)，去掉注釋和頭尾等結(jié)構(gòu)商蕴，（可以使用選項(xiàng) –skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys）

--events, -E 導(dǎo)出事件

--flush-privileges 在導(dǎo)出mysql數(shù)據(jù)庫之后叠萍，發(fā)出一條FLUSH PRIVILEGES 語句

--flush-logs 開始導(dǎo)出之前刷新日志，請注意绪商，假如一次導(dǎo)出多個(gè)數(shù)據(jù)庫(使用選項(xiàng)–databases或者–all-databases)苛谷，將會逐個(gè)數(shù)據(jù)庫刷新日志

除使用--lock-all-tables或者--master-data外，在這種情況下格郁，日志將會被刷新一次腹殿，相應(yīng)的表同時(shí)被鎖定，因此例书，如果打算同時(shí)導(dǎo)出和刷新日志應(yīng)該使用–lock-all-tables 或者–master-data 和–flush-logs

--delayed-insert 采用延時(shí)插入方式（INSERT DELAYED）

--comments 添加注釋信息

--compact 壓縮模式锣尉，產(chǎn)生更少的輸出

--complete-insert 輸出完成的插入語句

--default-character-set 指定默認(rèn)字符集

--lock-tables 備份前，鎖定所有數(shù)據(jù)庫表

--obt 建表語句包含drop table if exists tableName,insert之前包含一個(gè)鎖表語句lock tables tableName write决采，insert之后包含unlock tables

--lock-all-tables, -x提交請求鎖定所有數(shù)據(jù)庫中的所有表自沧，以保證數(shù)據(jù)的一致性，這是一個(gè)全局讀鎖织狐，并且自動(dòng)關(guān)閉–single-transaction 和–lock-tables 選項(xiàng)

--debug 輸出debug信息暂幼，用于調(diào)試（默認(rèn)值為d:t:o,/tmp/mysqldump.trace）例 mysqldump -uroot -p –all-databases –debug=” d:t:o,/tmp/debug.trace”

在導(dǎo)入sql語句的時(shí)候筏勒，mysql會使用LOCK?TABLES與UNLOCK?TABLES來鎖表移迫，是表級鎖，分為可讀管行，可寫厨埋。

語法： LOCK?TABLES tablename READ | WRITE；

如果一個(gè)線程獲得在一個(gè)表上的一個(gè)READ鎖捐顷，該線程和所有其他線程只能從表中讀荡陷。

如果一個(gè)線程獲得一個(gè)表上的一個(gè)WRITE鎖，那么只有持鎖的線程READ或WRITE表迅涮，其他線程被阻塞废赞。

UNLOCK?TABLES釋放被當(dāng)前線程持有的任何鎖。

當(dāng)線程發(fā)出另外一個(gè)LOCK?TABLES時(shí)叮姑，或當(dāng)服務(wù)器的連接被關(guān)閉時(shí)唉地，當(dāng)前線程鎖定的所有表會自動(dòng)被解鎖据悔。

2.從備份文件恢復(fù)數(shù)據(jù)庫

語法：

mysql -uroot -p? < [backup file name]

列如：

1.在Shell命令下：

mysql –uroot –p? < test.sql

2,在mysql命令下，用source命令導(dǎo)入備份文件：

mysql>? source test.sql;????????? //已登錄mysql耘沼，用source命令

如果備份文件中不包含CREATE DATABASE和USE語句极颓，那么在恢復(fù)的時(shí)候必須先創(chuàng)建數(shù)據(jù)庫。

3.mysqlbinlog二進(jìn)制日志增量備份

從mysqldump備份文件恢復(fù)數(shù)據(jù)會丟失掉從備份點(diǎn)開始的更新數(shù)據(jù)群嗤，所以還需要結(jié)合mysqlbinlog二進(jìn)制日志增量備份菠隆。

binlog簡介

binlog日志由配置文件的 log-bin 選項(xiàng)負(fù)責(zé)啟用，MySQL服務(wù)器將在數(shù)據(jù)根目錄創(chuàng)建兩個(gè)新文 件XXX-bin.001和xxx-bin.index狂秘，若配置選項(xiàng)沒有給出文件名骇径，Mysql將使用主機(jī)名稱命名這兩個(gè)文件，其中.index文件包含一份全體日志文件的清單者春。

Mysql會把用戶對所有數(shù)據(jù)庫的內(nèi)容和結(jié)構(gòu)的修改情況記入XXX-bin.n文件既峡，而不會記錄 SELECT和沒有實(shí)際更新的UPDATE語句。

當(dāng)MySQL數(shù)據(jù)庫停止或重啟時(shí)碧查，服務(wù)器會把日志文件記入下一個(gè)日志文件运敢，Mysql會在重啟時(shí)生成一個(gè)新的binlog日志文件，文件序號遞增忠售，此外传惠，如果日志文件超過max_binlog_size系統(tǒng)變量配置的上限時(shí)，也會生成新的日志文件稻扬。

3.1開啟binlog日志

修改 MySQL 的配置文件my.cnf 如下：

[mysqld]?

log-bin = ON

binlog_format = row?

log-bin-index =/var/lib/mysql/data/mysql-bin.index

#指定的是binlog文件的索引文件卦方，這個(gè)文件管理了所有的binlog文件的目錄

log-bin-basename =/var/lib/mysql/data/mysql-bin

#binlog日志的基本文件名，后面會追加標(biāo)識來表示每一個(gè)文件

server-id?= 1

#需要記錄binlog數(shù)據(jù)庫

binlog_do_db = mall

binlog_format的幾種格式：（STATEMENT泰佳，ROW和MIXED）

?STATEMENT：基于SQL語句的復(fù)制(statement-based replication, SBR) 盼砍，日志文件小，節(jié)約IO逝她，提高性能浇坐。準(zhǔn)確性差，對一些系統(tǒng)函數(shù)不能準(zhǔn)確復(fù)制或不能復(fù)制黔宛，如now()近刘、uuid()等 ?

ROW：基于行的復(fù)制(row-based replication, RBR)? ，準(zhǔn)確性強(qiáng)臀晃，能準(zhǔn)確復(fù)制數(shù)據(jù)的變更觉渴。日志文件大，較大的網(wǎng)絡(luò)IO和磁盤IO徽惋。?

MIXED：混合模式復(fù)制(mixed-based replication, MBR)案淋。準(zhǔn)確性強(qiáng)，文件大小適中险绘，有可能發(fā)生主從不一致問題踢京。

5.7.3以后版本必須配置server-id回右，隨機(jī)指定一個(gè)不能和其他集群中機(jī)器重名的字符串，如果只有一臺機(jī)器漱挚，那就可以隨便指定了翔烁。

可以省略log-bin-index 和log-bin-basename參數(shù)，把log-bin = ON 換成log-bin = /var/lib/mysql/data/mysql-bin即可旨涝。

查看binlog開啟情況

登錄mysql蹬屹；show variables like '%log_bin%'；查看

查看binlog日志

mysqlbinlog /var/lib/mysql/data/mysql-bin.000001? #一般的statement格式的二進(jìn)制文件

mysqlbinlog -v /var/lib/mysql/data/mysql-bin.000001? #如果是row格式白华，加上-v或者-vv參數(shù)就行

如下：

server id 13453 ： 數(shù)據(jù)庫主機(jī)的服務(wù)號慨默；

end_log_pos 535： sql結(jié)束時(shí)的pos節(jié)點(diǎn)；

查看binlog日志列表

使用

mysql> show master logs;

刪binlog日志

（1）使用reset master,該命令將會刪除所有日志弧腥，并讓日志文件重新從000001開始厦取。

mysql > reset master;

（2）使用命令：PURGE{BINARY|MASTER}LOGS{TO'log_name'|BEFOREdatetime_expr }

mysql> purge master logs to "binlog_name.00000X"? #將會清空00000X之前的所有日志文件

binlog日志恢復(fù)

這個(gè)binlog二進(jìn)制binlog日志包括兩類文件：

* 索引文件（文件名后綴為.index）用于記錄哪些日志文件正在被使用

* 日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫所有的DDL和DML(除了數(shù)據(jù)查詢語句)語句事件。

在恢復(fù)前了解一下基本知識管搪，使用mysqlbinlog 可以查看操作虾攻，但是不直觀，下面介紹一種mysql中經(jīng)常使用的更鲁。

語法：mysql>show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

參數(shù)解釋：

IN 'log_name' ：指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)

FROM pos? ?? ：指定從哪個(gè)pos起始點(diǎn)開始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開始算)

LIMIT [offset,] ：偏移量(不指定就是0)

row_count? ? ? ：查詢總條數(shù)(不指定就是所有行)

列如：

mysql> show binlog events in 'mysql-bin.000001'\G;? #指定查詢 mysql-bin.000001這個(gè)文件霎箍。

mysql> show binlog events in 'mysql-bin.000001' from 294\G; #指定查詢 mysql-bin.000001這個(gè)文件，從pos點(diǎn):294開始查起澡为。

mysql> show binlog events in 'mysql-bin.000001' from 294 limit 10\G; #指定查詢 mysql-bin.000001這個(gè)文件漂坏，從pos點(diǎn):294開始查起，查詢10條（即10條語句）媒至。

mysql> show binlog events in 'mysql-bin.000001' from 294 limit 2,10\G; #指定查詢 mysql-bin.000001這個(gè)文件顶别，從pos點(diǎn):294 開始查起，偏移2行（即中間跳過2個(gè)）拒啰，查詢10條

查詢數(shù)據(jù)含義：

Log_name：查詢的binlog日志文件名

Pos：pos起始點(diǎn)

Event_type：事件類型（

我這數(shù)據(jù)庫的Binlog模式是row 數(shù)據(jù)引擎innodb驯绎，類型說明如下：

1) QUERY：與STATEMENT模式處理相同，存儲的是SQL图呢，主要是一些與數(shù)據(jù)無關(guān)的操作条篷，eg: begin、drop table蛤织、truncate table 等；

2)? ? ? TABLE_MAP：記錄了下一條事件所對應(yīng)的表信息鸿染，在其中存儲了數(shù)據(jù)庫名和表名指蚜；(test.a_view)

3)? ? ? WRITE_ROWS：操作類型為insert；（insert）

4)? ? ? UPDATE_ROWS：操作類型為update涨椒；（update）

5)? ? ? DELETE_ROWS：操作類型為delete摊鸡；（delete）

6)? ? ? XID绽媒， 用于標(biāo)識事務(wù)提交。

以一條insert語句為例免猾，包含4個(gè)事件：

QUERY? (begin)是辕，TABLE_MAP，WRITE_ROWS猎提，XID

）

Server_id：標(biāo)識是由哪臺服務(wù)器執(zhí)行的

End_log_pos：pos結(jié)束點(diǎn)(即：下行的pos起始點(diǎn))

Info：說明

進(jìn)入正題获三。

恢復(fù)語法格式：

mysqlbinlog mysql-bin.0000xx | mysql -u用戶名 -p密碼 數(shù)據(jù)庫名

常用參數(shù)選項(xiàng)解釋：
????????? --start-position #起始pos點(diǎn)

? ? ? ? ? --stop-position? #結(jié)束pos點(diǎn)

? ? ? ? ? --start-datetime="xxxx-xx-xx xx:xx:xx"? #起始時(shí)間點(diǎn)

? ? ? ? ? --stop-datetime="xxxx-xx-xx xx:xx:xx"? #結(jié)束時(shí)間點(diǎn)

? ? ? ? ? --database=zyyshop? ? ? ? ? ? ? ? ? ? 指定只恢復(fù)zyyshop數(shù)據(jù)庫(一臺主機(jī)上往往有多個(gè)數(shù)據(jù)庫，只限本地log日志)

?????????? -u --user=name? ? ? ? ? ? ? 連接到遠(yuǎn)程主機(jī)的用戶名

??????? ?? -p --password[=name]? ? ? ? 連接到遠(yuǎn)程主機(jī)的密碼

????? ?? ? -h --host=name? ? ? ? ? ? ? 從遠(yuǎn)程主機(jī)上獲取binlog日志

?????? ? ? --read-from-remote-server? 從某個(gè)MySQL服務(wù)器上讀取binlog日志

實(shí)踐

接下來我對takeout數(shù)據(jù)庫users表修改一條數(shù)據(jù)锨苏，在把表刪除疙教。

name字段，從as1改成test伞租。連接mysql贞谓。

查看現(xiàn)在寫入的是哪個(gè)日志

mysql> show master status;

現(xiàn)在是mysql-bin.000009，把mysql-bin.000009備份葵诈，刷新日志寫入裸弦，保證接下來的日志不會寫入000009。

查看000009日志情況作喘。

mysql> show binlog events in 'mysql-bin.000009'\G;

update_rows修改事件烁兰，pos起始位置3787，結(jié)束pos4339徊都，因?yàn)槭且粋€(gè)事務(wù)沪斟，會有其他什么開啟事務(wù)啊，提交事務(wù)什么的暇矫；我們要恢復(fù)修改之前數(shù)據(jù)主之，所以結(jié)束pos點(diǎn)為3787。

[root@UsaOfficeLuke data]# mysqlbinlog --stop-position=3787 mysql-bin.000009 | mysql -uroot -p -v takeout

然后看數(shù)據(jù)庫

修改成功＠罡（也可以根據(jù)日志中開始執(zhí)行的時(shí)間來進(jìn)行恢復(fù)）