Mysql實(shí)現(xiàn)企業(yè)級(jí)日志管理、備份與恢復(fù)的實(shí)戰(zhàn)教程

1、通過(guò)配置文件my.cnf中的log-slow-queries選項(xiàng)可以開啟慢查詢?nèi)罩荆?/span>

形式如下：

vim /etc/my.cnf

[mysqld]

slow-query-log = ON

slow-query-log-file = /var/log/mariadb/slow.log

long-query-time = 0.01

其中，DIR參數(shù)指定慢查詢?nèi)罩镜拇鎯?chǔ)路徑；filename參數(shù)指定日志的文件名，生成日志文件的完成名稱為filename-slow.log。如果不指定存儲(chǔ)路徑，慢查詢?nèi)罩灸J(rèn)存儲(chǔ)到mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)文件下，如果不指定文件名，默認(rèn)文件名為hostname-slow.log

2、通過(guò)登錄mysql服務(wù)器直接定義

方式如下：

首先要有全局權(quán)限；然后執(zhí)行mysql>set global slow_query_log=1;（臨時(shí)生效，sql語(yǔ)句執(zhí)行時(shí)間超過(guò)1s就被成為慢查詢?nèi)罩荆?/p>

時(shí)間默認(rèn)超過(guò)多少的稱為慢查詢?nèi)罩荆?/p>

一般都是通過(guò)long_query_time選項(xiàng)來(lái)設(shè)置這個(gè)時(shí)間值，時(shí)間以秒為單位，可以精確到微秒。如果查詢時(shí)間超過(guò)了這個(gè)時(shí)間值（默認(rèn)為10秒），這個(gè)查詢語(yǔ)句將被記錄到慢查詢?nèi)罩局?。查看服?wù)器默認(rèn)時(shí)間值方式如下：

注釋：其中這個(gè)慢查詢時(shí)間并不是只表示語(yǔ)句自身執(zhí)行超過(guò)10秒還包含由于其他資源被征用造成阻塞的查詢執(zhí)行時(shí)間或其他原因等都被記錄到慢查詢中。所以這個(gè)慢查的時(shí)長(zhǎng)表示從查詢開始到查詢結(jié)束中間包含可能的任何原因所經(jīng)歷的所有時(shí)間。

查看慢查詢?nèi)罩緝?nèi)容

Mysql事務(wù)日志

事務(wù)：事務(wù)就是一系列操作的集合，一系列操作之后需要提交，提交之后，這一系列操作才能被稱為事務(wù)。(要么操作都執(zhí)行，要么都不執(zhí)行)

事務(wù)日志（InnoDB特有的日志）可以幫助提高事務(wù)的效率。使用事務(wù)日志，存儲(chǔ)引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝，再把改修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤。事務(wù)日志采用追加的方式，因此寫日志的操作是磁盤上一小塊區(qū)域內(nèi)的順序I/O，而不像隨機(jī)I/O需要在磁盤的多個(gè)地方移動(dòng)磁頭，所以采用事務(wù)日志的方式相對(duì)來(lái)說(shuō)要快得多。事務(wù)日志持久以后，內(nèi)存中被修改的數(shù)據(jù)在后臺(tái)可以慢慢的刷回到磁盤。目前大多數(shù)的存儲(chǔ)引擎都是這樣實(shí)現(xiàn)的，我們通常稱之為預(yù)寫式日志，修改數(shù)據(jù)需要寫兩次磁盤。

mysql基于事務(wù)的操作，會(huì)直接把對(duì)應(yīng)內(nèi)存中的數(shù)據(jù)給改掉，改完之后，去查看，都已經(jīng)生效，但是并沒(méi)有網(wǎng)磁盤上去寫，他是先寫到事務(wù)日志里面，然后再定期的往磁盤上去刷（事務(wù)日志采用追加的方式，往磁盤上寫，是按照順序來(lái)寫的，大大提高了事務(wù)的效率）

如果數(shù)據(jù)的修改已經(jīng)記錄到事務(wù)日志并持久化，但數(shù)據(jù)本身還沒(méi)有寫回磁盤，此時(shí)系統(tǒng)崩潰，存儲(chǔ)引擎在重啟時(shí)能夠自動(dòng)恢復(fù)這部分修改的數(shù)據(jù)。具有的恢復(fù)方式則視存儲(chǔ)引擎而定。

innodb引擎是支持事務(wù)的引擎

查看事務(wù)日志的定義

show global variables like '%log%';

Mysql二進(jìn)制日志

二進(jìn)制日志也叫作變更日志，主要用于記錄修改數(shù)據(jù)或有可能引起數(shù)據(jù)改變的mysql語(yǔ)句，并且記錄了語(yǔ)句發(fā)生時(shí)間、執(zhí)行時(shí)長(zhǎng)、操作的數(shù)據(jù)等等。所以說(shuō)通過(guò)二進(jìn)制日志可以查詢mysql數(shù)據(jù)庫(kù)中進(jìn)行了哪些變化。一般大小體積上限為1G

show global variables like '%log%';

sql_log_bin ={ON|OFF} #用于控制會(huì)話級(jí)別（連上mysql執(zhí)行一個(gè)操作語(yǔ)句，這就是會(huì)話級(jí)別的，比如說(shuō)直接用一個(gè)文件導(dǎo)入mysql，這就不算會(huì)話級(jí)別的）二進(jìn)制日志功能的開啟或關(guān)閉。默認(rèn)為ON，表示啟用記錄功能。用戶可以在會(huì)話級(jí)別修改此變量的值，但其必須具有SUPER權(quán)限。

binlog_cache_size =32768 #默認(rèn)值32768 Binlog Cache用于在打開了二進(jìn)制日志（binlog）記錄功能的環(huán)境，是MySQL 用來(lái)提高binlog的記錄效率而設(shè)計(jì)的一個(gè)用于短時(shí)間內(nèi)臨時(shí)緩存binlog數(shù)據(jù)的內(nèi)存區(qū)域。一般來(lái)說(shuō)，如果我們的數(shù)據(jù)庫(kù)中沒(méi)有什么大事務(wù)，寫入也不是特別頻繁，2MB～4MB是一個(gè)合適的選擇。但是如果我們的數(shù)據(jù)庫(kù)大事務(wù)較多，寫入量比較大，可與適當(dāng)調(diào)高binlog_cache_size。同時(shí)，我們可以通過(guò)binlog_cache_use 以及binlog_cache_disk_use來(lái)分析設(shè)置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內(nèi)存大小不夠而使用臨時(shí)文件（binlog_cache_disk_use）來(lái)緩存了。

log_bin = mysql-bin #指定binlog的位置，默認(rèn)在數(shù)據(jù)目錄下。

binlog-format= {ROW|STATEMENT|MIXED} #指定二進(jìn)制日志的類型，建議為MIXED。如果設(shè)定了二進(jìn)制日志的格式，卻沒(méi)有啟用二進(jìn)制日志，則MySQL啟動(dòng)時(shí)會(huì)產(chǎn)生警告日志信息并記錄于錯(cuò)誤日志中。

row:不記錄每條sql語(yǔ)句的上下文關(guān)系，而僅僅記錄每條數(shù)據(jù)被修改了

statement：每一條會(huì)修改數(shù)據(jù)的sql語(yǔ)句都會(huì)被記錄

mixed：表示前兩者混合

sync_binlog = 10 #設(shè)定多久同步一次二進(jìn)制日志至磁盤文件中，0表示不同步，任何正數(shù)值都表示對(duì)二進(jìn)制每多少次寫操作之后同步一次。當(dāng)autocommit的值為1時(shí)，每條語(yǔ)句的執(zhí)行都會(huì)引起二進(jìn)制日志同步，否則，每個(gè)事務(wù)的提交會(huì)引起二進(jìn)制日志同步

通過(guò)編輯my.cnf中的log-bin選項(xiàng)可以開啟二進(jìn)制日志；形式如下：

其中，DIR參數(shù)指定二進(jìn)制文件的存儲(chǔ)路徑；filename參數(shù)指定二級(jí)制文件的文件名，其形式為filename.number，number的形式為000001、000002等。每次重啟mysql服務(wù)或運(yùn)行mysql> flush logs;都會(huì)生成一個(gè)新的二進(jìn)制日志文件，這些日志文件的number會(huì)不斷地遞增。除了生成上述的文件外還會(huì)生成一個(gè)名為filename.index的文件。這個(gè)文件中存儲(chǔ)所有二進(jìn)制日志文件的清單又稱為二進(jìn)制文件的索引。

每重啟一次數(shù)據(jù)庫(kù)服務(wù)，就會(huì)生成一個(gè)二進(jìn)制日志文件

查看二進(jìn)制日志：

二進(jìn)制日志的定義方式為二進(jìn)制格式；使用此格式可以存儲(chǔ)更多的信息，并且可以使寫入二進(jìn)制日志的效率更高。但是不能直接使用查看命令打開并查看二進(jìn)制日志。

小擴(kuò)展：二進(jìn)制日志的記錄位置,通常為上一個(gè)事件執(zhí)行結(jié)束時(shí)間的位置，每一個(gè)日志文件本身也有自己的元數(shù)據(jù)所以說(shuō)對(duì)于當(dāng)前版本的mysql來(lái)說(shuō)二進(jìn)制的開始位置通常為107；

連接mysql，輸入幾條可以修改數(shù)據(jù)的sql語(yǔ)句，從而生成二進(jìn)制日志

查看指定二進(jìn)制日志信息

命令行下查看二進(jìn)制日志：

由于無(wú)法使用cat等方式直接打開并查看二進(jìn)制日志；所以必須使用mysqlbinlog命令。但是當(dāng)正在執(zhí)行mysql讀寫操作時(shí)建議不要使用此打開正在使用的二進(jìn)制日志文件；若非要打開可flushlogs。mysqlbinlog命令的使用方式：

導(dǎo)出此數(shù)據(jù)庫(kù)的信息：

[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql

導(dǎo)入此數(shù)據(jù)庫(kù)的信息：

[root@stu18 data]#mysql a.sql

刪除二進(jìn)制日志信息：

二進(jìn)制日志會(huì)記錄大量的信息（其中包含一些無(wú)用的信息）。如果很長(zhǎng)時(shí)間不清理二進(jìn)制日志，將會(huì)浪費(fèi)很多的磁盤空間。但是，刪除之后可能導(dǎo)致數(shù)據(jù)庫(kù)崩潰時(shí)無(wú)法進(jìn)行恢復(fù)，所以若要?jiǎng)h除二進(jìn)制日志首先將其和數(shù)據(jù)庫(kù)備份一份，其中也只能刪除備份前的二進(jìn)制日志，新產(chǎn)生的日志信息不可刪(可以做即時(shí)點(diǎn)還原)。也不可在關(guān)閉mysql服務(wù)器之后直接刪除因?yàn)檫@樣可能會(huì)給數(shù)據(jù)庫(kù)帶來(lái)錯(cuò)誤的。若非要?jiǎng)h除二進(jìn)制日志需要做如下操作：導(dǎo)出備份數(shù)據(jù)庫(kù)和二進(jìn)制日志文件進(jìn)行壓縮歸檔存儲(chǔ)。刪除二進(jìn)制文件的方法如下：

使用RESET MASTER語(yǔ)句可以刪除所有的二進(jìn)制日志。該語(yǔ)句的形式如下：

mysql> reset master;

Query OK, 0 rowsaffected (0.17 sec)

mysql> show binary logs;

Mysql備份工具

mysqldump: 邏輯備份工具，適用于所有存儲(chǔ)引擎，可用于溫備，能實(shí)現(xiàn)完全備份，部分備份；對(duì)InnoDB存儲(chǔ)引擎

支持熱備；

cp, tar等文件系統(tǒng)工具：物理備份工具，適用于所有存儲(chǔ)引擎；用于冷備，能實(shí)現(xiàn)完全備份，部分備份；

lvm2的快照：幾乎熱備；借助于文件系統(tǒng)工具實(shí)現(xiàn)物理備份；

mysqlhotcopy: 幾乎冷備；僅適用于MyISAM存儲(chǔ)引擎；

Mysql備份方案①mysqldump+binlog: （ 推薦）

完全備份，通過(guò)備份二進(jìn)制日志實(shí)現(xiàn)增量備份

②xtrabackup:

對(duì)InnoDB：熱備，支持完全備份和增量備份

對(duì)MyISAM：溫備，只支持完全備份

③lvm2快照+binlog：

幾乎熱備，物理備份

mysqldump+binlog命令的語(yǔ)法格式

mysqldump [OPTIONS] database [tables]：備份單個(gè)庫(kù)，或庫(kù)指定的一個(gè)或多個(gè)表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2DB3...]：備份一個(gè)或多個(gè)庫(kù)

mysqldump [OPTIONS] --all-databases [OPTIONS]：備份所有庫(kù)

其他選項(xiàng)

-x, --lock-all-tables：鎖定所有表

-l, --lock-tables：鎖定備份的表

--single-transaction：?jiǎn)?dòng)一個(gè)大的單一事務(wù)實(shí)現(xiàn)備份

-C, --compress：壓縮傳輸

-E, --events：備份指定庫(kù)的事件調(diào)度器

-R, --routines：備份存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)

--triggers：備份觸發(fā)器

--master-data={0|1|2}

0：不記錄

1：記錄CHANGE MASTER TO語(yǔ)句；此語(yǔ)句未被注釋

2：記錄為注釋語(yǔ)句

-F，--flush-logs：鎖定表之后執(zhí)行flush logs命令

mysqldump+binlog備份與恢復(fù)1.修改mysql配置文件，開啟二進(jìn)制日志

vim /etc/my.cnf

log-bin = master-log

然后重啟mysql

systemctl restart mariadb

在進(jìn)入mysql查看是否生成二進(jìn)制日志

2、準(zhǔn)備備份目錄

3、準(zhǔn)備備份數(shù)據(jù)庫(kù)及表

mysql

create database test;

use magedu;

create table m(id int,name char(20));

4、進(jìn)行完整備份

mysqldump --all-databases --lock-all-tables --flush-log --master-data=2 > /backup/`date +%F_%T`-all.sql

5、向表中插入數(shù)據(jù)

mysql

use magedu;

show master status;

insert into m26 (id,name) values(1,'fuming'),(2,'zhangmeng');

6、進(jìn)行增量備份，備份二進(jìn)制日志

mysqlbinlog --start-position=245 --stop-position=479 /var/lib/mysql/master-log.000002 > /backup/binlog/binlog-`date +%F_%T`.sql

判斷position的start和stop

show master logs;

show binlog events in 'master-log.000002';

結(jié)束要包含commit提交。

7、繼續(xù)插入數(shù)據(jù)，在沒(méi)備份的情況下刪除數(shù)據(jù)庫(kù)，模擬誤操作

8、數(shù)據(jù)恢復(fù)，由于最后我們沒(méi)有備份就刪除了數(shù)據(jù)庫(kù)，所以我們首先需要保護(hù)最后的二進(jìn)制日志,如果這些二進(jìn)制丟了，那就真的恢復(fù)不了了，查看刪除操作之前的position值mysqlbinlog /var/lib/mysql/master-log.000002

9、將最后操作的二進(jìn)制日志備份

mysqlbinlog --start-position=467 --stop-position=677 /var/lib/mysql/master-log.000002 > /backup/binlog/binlog-`date +%F_%T`.sql

ls /backup/binlog/

10.導(dǎo)入之前的所有備份

mysql /backup/2017-12-07_20\:20\:04-all.sql 導(dǎo)入完整備份

mysql /backup/binlog/binlog-2017-12-07_20\:45\:17.sql 導(dǎo)入增量備份

mysql /backup/binlog/binlog-2017-12-07_21\:05\:42.sql 導(dǎo)入刪掉數(shù)據(jù)庫(kù)之前的增量備份

11.查看數(shù)據(jù)庫(kù)及數(shù)據(jù)

xtrabackup

Xtrabackup是由percona提供的mysql數(shù)據(jù)庫(kù)備份工具，據(jù)官方介紹，是一款開源能夠?qū)nnodb和xtradb數(shù)據(jù)庫(kù)進(jìn)行熱備的工具。

特點(diǎn)：

(1)備份過(guò)程快速、可靠

(2)備份過(guò)程不會(huì)打斷正在執(zhí)行的事務(wù)

(3)能夠基于壓縮等功能節(jié)約磁盤空間和流量

(4)自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)

(5)還原速度快

實(shí)驗(yàn)步驟：（1）xtrabackup的安裝

yum install percona-xtrabackup -y

（2）完全備份

innobackupex --user=root /backup

（3）添加數(shù)據(jù)

mysql -uroot

create database magedu;

use magedu

create table m26 (id int,name char(20));

insert into m26 values (007,'fuming'),(008,'zhangmeng')

（4）增量備份

innobackupex --incremental /backup/ --incremental-basedir=/backup/2017-11-16_16-53-4

（5）數(shù)據(jù)恢復(fù)準(zhǔn)備
1.執(zhí)行操作（完全備份）：

innobackupex --apply-log --redo-only BASE-DIR（BASE-DIR是完全備份的目錄）

例如：innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=/backup/2017-11-16_17-17-52/

2.接著執(zhí)行（增量）：

innobackupex --apply-log --redo-only BASE-DIR --incrementaldir=INCREMENTAL-DIR-1（INCREMENTAL-DIR-1是增量備份的目錄）

例如：innobackupex --apply-log --redo-only /backup/2017-11-16_16-53-43 --incrementaldir=/backup/2017-11-16_17-17-52/

（6）恢復(fù)階段，還原數(shù)據(jù)

mv /var/lib/mysql /var/lib/mysql.bak 模擬刪除數(shù)據(jù)庫(kù)

mkdir /var/lib/mysql

cd /var/lib/mysql

innobackupex --copy-back /backup/2017-11-16_16-53-43 恢復(fù)完全備份

lvm2快照+binlog

做實(shí)驗(yàn)之前我們先回顧一下lvm2-snapshot的知識(shí)

LVM快照簡(jiǎn)單來(lái)說(shuō)就是將所快照源分區(qū)一個(gè)時(shí)間點(diǎn)所有文件的元數(shù)據(jù)進(jìn)行保存，如果源文件沒(méi)有改變，那么訪問(wèn)快照卷的相應(yīng)文件則直接指向源分區(qū)的源文件，如果源文件發(fā)生改變，則快照卷中與之對(duì)應(yīng)的文件不會(huì)發(fā)生改變?？煺站碇饕糜谳o助備份文件。

實(shí)驗(yàn)步驟：

1、添加硬盤，并劃分磁盤類型為lvm類型

echo '- - -' > /sys/class/scsi_host/host2/scan

2.分區(qū)

t 8e 就是lvm

partx -a /dev/sdb 使內(nèi)核識(shí)別新磁盤

3.pvcreate /dev/sdb1 添加物理卷

4.vgcreate myvg /dev/sdb1 添加卷組

5.lvcreate -n mydata -L 5G myvg 添加邏輯卷

6、mkfs.ext4 /dev/mapper/myvg-mydata 格式化邏輯卷

7、掛載mount /dev/mapper/myvg-mydata /lvm_data 使用

8、修改Mysql配置，使得數(shù)據(jù)文件在邏輯卷上 datadir=/lvm_data

9、service mysqld restart 啟動(dòng)Mysql服務(wù)

10、創(chuàng)建數(shù)據(jù)庫(kù)，進(jìn)行操作

11、mysql> FLUSH TABLES WITH READ LOCK; #鎖定表

12、lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvgmydata

#創(chuàng)建快照卷 Logical volume "mydata-snap" created.

13、mysql> UNLOCK TABLES; #解鎖所有表

14、 mount /dev/myvg/mydata-snap /lvm_snap/ #掛載snap

15、tar cvf /tmp/mysqlback.tar ./* #打包物理備份

16、umount /lvm_snap/ #卸載snap

17、lvremove myvg mydata-snap #刪除snap

18、刪除mysql數(shù)據(jù) rm -rf /lvm_data/*

19、 解壓恢復(fù)刪除數(shù)據(jù) tar xvf /tmp/mysqlback.tar ./

20、驗(yàn)證數(shù)據(jù)庫(kù)數(shù)據(jù)是否正確恢復(fù)

總結(jié)