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

1.mysqldump備份

語法：

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

列如：

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

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

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

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

mysqldump -uroot -p --databases a --tables a1 a2 > test.sql?#備份a數(shù)據(jù)庫(kù)下a1,a2表結(jié)構(gòu)和數(shù)據(jù)（創(chuàng)建的sql語句沒有use選庫(kù)，恢復(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ù)庫(kù)的所有數(shù)據(jù)導(dǎo)入到host2中的db2數(shù)據(jù)庫(kù)中，db2的數(shù)據(jù)庫(kù)必須存在否則會(huì)報(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ù)庫(kù)，-F：刷新日志，-R：備份存儲(chǔ)過程等，-x：鎖表，--master-data：在備份語句里添加CHANGE MASTER語句以及binlog文件及位置點(diǎn)信息，備份a數(shù)據(jù)庫(kù)，輸出文件名年月日的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ù)庫(kù)時(shí)忽略某個(gè)表

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

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

--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ù)庫(kù)之后，發(fā)出一條FLUSH PRIVILEGES 語句

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

除使用--lock-all-tables或者--master-data外，在這種情況下，日志將會(huì)被刷新一次，相應(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ù)庫(kù)表

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

--lock-all-tables, -x提交請(qǐng)求鎖定所有數(shù)據(jù)庫(kù)中的所有表，以保證數(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會(huì)使用LOCK?TABLES與UNLOCK?TABLES來鎖表，是表級(jí)鎖，分為可讀，可寫。

語法： 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)前線程鎖定的所有表會(huì)自動(dòng)被解鎖。

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

語法：

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ù)庫(kù)。

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

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

binlog簡(jiǎn)介

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

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

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日志的基本文件名，后面會(huì)追加標(biāo)識(shí)來表示每一個(gè)文件

server-id?= 1

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

binlog_do_db = mall

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

?STATEMENT：基于SQL語句的復(fù)制(statement-based replication, SBR) ，日志文件小，節(jié)約IO，提高性能。準(zhǔn)確性差，對(duì)一些系統(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ī)器重名的字符串，如果只有一臺(tái)機(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ù)庫(kù)主機(jī)的服務(wù)號(hào)；

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

查看binlog日志列表

使用

mysql> show master logs;

刪binlog日志

（1）使用reset master,該命令將會(huì)刪除所有日志，并讓日志文件重新從000001開始。

mysql > reset master;

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

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

binlog日志恢復(fù)

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

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

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

在恢復(fù)前了解一下基本知識(shí)，使用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ù)庫(kù)的Binlog模式是row 數(shù)據(jù)引擎innodb，類型說明如下：

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

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

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

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

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

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

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

QUERY? (begin)，TABLE_MAP，WRITE_ROWS，XID

）

Server_id：標(biāo)識(shí)是由哪臺(tái)服務(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ù)庫(kù)名

常用參數(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ù)庫(kù)(一臺(tái)主機(jī)上往往有多個(gè)數(shù)據(jù)庫(kù)，只限本地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í)踐

接下來我對(duì)takeout數(shù)據(jù)庫(kù)users表修改一條數(shù)據(jù)，在把表刪除。

name字段，從as1改成test。連接mysql。

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

mysql> show master status;

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

查看000009日志情況。

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

update_rows修改事件，pos起始位置3787，結(jié)束pos4339，因?yàn)槭且粋€(gè)事務(wù)，會(huì)有其他什么開啟事務(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ù)庫(kù)

修改成功?。ㄒ部梢愿鶕?jù)日志中開始執(zhí)行的時(shí)間來進(jìn)行恢復(fù)）