一、Mysql日志管理

1、日志的默認(rèn)位置及配置文件

1.1Mysql日志默認(rèn)保存在/usr/local/mysql/data下

1.2配置文件的位置在/etc/my.cnf文件中的[mysqld]內(nèi)

2、日志分類

2.1錯誤日志

用來記錄Mysql的錯誤信息，默認(rèn)開啟的

log-error=/usr/local/mysql/data/mysql_error.log

2.2通用查詢?nèi)罩?/h4>

用來記錄Mysql所有的連接和語句，默認(rèn)是關(guān)閉的

general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

2.3二進(jìn)制日志

用來記錄所有更新的數(shù)據(jù)或者已經(jīng)潛在更新數(shù)據(jù)的語句，記錄了數(shù)據(jù)的更改，可用于數(shù)據(jù)恢復(fù)，默認(rèn)是開啟的

log_bin=mysql-bin
或 log-bin=mysql-bin

2.4慢查詢?nèi)罩?/h4>

用來記錄所有執(zhí)行時間超過long_query_time秒的語句，可以找到哪些查詢語句執(zhí)行時間長，以便于優(yōu)化，默認(rèn)也是關(guān)閉的。

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 單位時間為s，缺省時為10s

2.5中繼日志

一般情況下，在Mysql主從復(fù)制，讀寫分離集群的從節(jié)點開啟，主節(jié)點一般不需要這個日志

3、日志配置

在配置文件中修改

#錯誤日志
log-error=/usr/local/mysql/data/mysql_error.log	 
#通用查詢?nèi)罩?
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二進(jìn)制日志
log-bin=mysql-bin	
#慢查詢?nèi)罩?
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#配置文件添加完后需要重啟MySQL
systemctl restart mysql

修改my.cnf配置文件，在mysqld中添加如下內(nèi)容

然后再重啟mysql服務(wù)即可

4、日志查詢

進(jìn)入mysql數(shù)據(jù)庫，查詢?nèi)罩臼欠耖_啟

#variables 表示變量
#like 表示模糊查詢
#%xxx，以什么結(jié)尾，xxx%以什么開頭，%xxx%包含
#查看通用查詢?nèi)罩臼欠耖_啟
show variables like 'general%';	
#查看二進(jìn)制日志是否開啟
show variables like 'log_bin%';	
#查看慢查詢?nèi)展δ苁欠耖_啟
show variables like '%slow%';		
#查看慢查詢時間設(shè)置
show variables like 'long_query_time';
#在數(shù)據(jù)庫中設(shè)置開啟慢查詢的方法
set global slow_query_log=ON;		

4.1查詢通用日志是否開啟

show variables like ‘general%'; 

4.2查詢二進(jìn)制日志是否開啟

show variables like ‘log_bin%';

4.3查看慢查詢?nèi)罩臼欠耖_啟

show variables like ‘long_query_time';

4.4查詢慢查詢?nèi)罩境瑫r時間

show variables like ‘long%';

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

1、數(shù)據(jù)備份的概念

數(shù)據(jù)備份的主要目的是災(zāi)難恢復(fù)，而且還可以用來測試應(yīng)用，回滾數(shù)據(jù)，查詢歷史數(shù)據(jù)等

2、數(shù)據(jù)備份的必要性

在生產(chǎn)環(huán)境中，數(shù)據(jù)很重要，數(shù)據(jù)保障了企業(yè)業(yè)務(wù)的正常運行。因此數(shù)據(jù)的安全性及數(shù)據(jù)的可靠性是運維工作的重中之重，任何數(shù)據(jù)的丟失都可能是對企業(yè)產(chǎn)生十分嚴(yán)重的后果。

2.1數(shù)據(jù)丟失的原因：

①程序的錯誤

②人為操作錯誤

③運算錯誤

④磁盤故障

⑤災(zāi)難（自然災(zāi)害，以及人為盜竊等）

3、數(shù)據(jù)備份的類型

3.1數(shù)據(jù)庫備份從物理與邏輯的角度分類可以分為物理備份和邏輯備份

3.1.1物理備份

①物理備份是對數(shù)據(jù)庫操作系統(tǒng)的物理文件(如數(shù)據(jù)文件、日志文件等)的備份。

②物理備份適用于在出現(xiàn)問題時需要快速恢復(fù)的大型重要數(shù)據(jù)庫。

③物理備份又可以分為冷備份(脫機(jī)備份)、熱備份(聯(lián)機(jī)備份)和溫備份。

3.1.2、邏輯備份

①邏輯備份是對數(shù)據(jù)庫邏輯組件(如表等數(shù)據(jù)庫對象)的備份,表及數(shù)據(jù)庫結(jié)構(gòu)(CREATE DATABASE, CREATETABLEia)和內(nèi)容(INSERT語句或分隔文本文件)的信息。

②邏輯備份適用于可以編輯數(shù)據(jù)值或表結(jié)構(gòu)較小的數(shù)據(jù)量,或者在不同的機(jī)器體系結(jié)構(gòu)上重新創(chuàng)建數(shù)據(jù)

3.1.3冷備份：在數(shù)據(jù)庫關(guān)閉的狀態(tài)下進(jìn)行備份操作

3.1.4熱備份：在數(shù)據(jù)庫處于運行狀態(tài)時進(jìn)行備份操作，該備份方法依賴數(shù)據(jù)庫的日志文件。

3.1.5溫備份：數(shù)據(jù)庫鎖定表格（不可寫入但可讀）的狀態(tài)下進(jìn)行別分操作。

3.2從數(shù)據(jù)庫的備份策略角度分類

3.2.1完全備份：

每次對數(shù)據(jù)進(jìn)行完整的備份，即對整個數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份，保存的時備份完成時刻的數(shù)據(jù)庫，是差異備份與增量備份的基礎(chǔ)。完全備份的備份與恢復(fù)操作都非常簡單方便，但是數(shù)據(jù)存在大量的重復(fù)，并且會占用大量的磁盤空間，備份的時間也很長。

3.2.2差異備份：

備份那些上次完全備份之后被修改過的所有文件，備份的時間節(jié)點是從上次完整備份，備份數(shù)據(jù)量會越來越大。恢復(fù)數(shù)據(jù)時，只需要恢復(fù)上次的完全備份與最近的一次差異備份即可。

3.2.3增量備份：

只有那些在上次完全備份或者增量備份后被修改的文件才會被備份。以上次完整備份或上次增量備份的時間為時間點，僅備份這之間的數(shù)據(jù)變化，因而備份的數(shù)據(jù)量小，占用空間小，備份速度快。但恢復(fù)時，需要從上一次的完整備份開始到最后一次增量備份之的所有增量依次恢復(fù)，如中間某次的備份數(shù)據(jù)損壞，將導(dǎo)致數(shù)據(jù)的丟失。

4、備份方法

數(shù)據(jù)庫的備份可以采用很多種方式，如直接打包數(shù)據(jù)庫文件（物理冷備份）、專用備份工具（mysqldump）、二進(jìn)制日志增量備份，第三方工具備份等

4.1物理冷備份

①物理冷備份時需要在數(shù)據(jù)庫出入關(guān)閉狀態(tài)下，能夠較好的保證數(shù)據(jù)庫的完整性。

②物理冷備份一般用于非核心業(yè)務(wù)，這類業(yè)務(wù)一般都允許中斷

③物理冷備份的特點就是速度快，恢復(fù)時也是最簡單的

④通常通過直接打包數(shù)據(jù)庫文件夾（usr/local/mysql/data）來實現(xiàn)備份

4.2專用備份工具mysqldump或mysqlhotcopy

①mysqldump程序和musqlhotcopy都可以備份

②mysqldump是客戶端常用邏輯備份程序，能夠產(chǎn)生一組被執(zhí)行以后再現(xiàn)原始數(shù)據(jù)庫對象定義和表數(shù)據(jù)的SQL語句。它可以轉(zhuǎn)儲一個到多個mysql數(shù)據(jù)庫，對其進(jìn)行備份或傳輸?shù)竭h(yuǎn)程SQL服務(wù)器mysqldump更為通用，因為它可以備份各種表。

③mysqlhotcopy僅適用于存儲引擎

4.3通過二進(jìn)制日志進(jìn)行增量備份

①支持增量備份，進(jìn)行增量備份時必須啟用二進(jìn)制日志

②二進(jìn)制日志文件為用戶提供復(fù)制，對執(zhí)行備份點后進(jìn)行的數(shù)據(jù)庫更改所需要的信息進(jìn)行恢復(fù)

③如果進(jìn)行增量備份（包含自上次完全備份或增量備份以來發(fā)生的數(shù)據(jù)修改），需要刷新二進(jìn)制日志。

4.4通過第三方工具備份

第三方工具percona xtrabackup是一個免費的mysql熱備份軟件，支持在線熱備份innodb和xtradb，也可以支持mysql表備份，不過myisam表的備份要在表鎖的情況下進(jìn)行。

三、Mysql完全備份

是對整個數(shù)據(jù)庫，數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份，保存的是備份完成時刻的數(shù)據(jù)庫，是差異備份與增量備份的基礎(chǔ)。

1、完全備份的優(yōu)缺點：

①優(yōu)點：備份與恢復(fù)操作簡單方便

②缺點：數(shù)據(jù)存在大量的重復(fù)，占用大量的備份空間，備份與恢復(fù)的時間長

2、數(shù)據(jù)庫完全備份的分類

2.1物理冷備份與恢復(fù)

①關(guān)閉mysql數(shù)據(jù)庫

②使用tar命令直接打包數(shù)據(jù)庫文件夾

③直接替換現(xiàn)有的mysql目錄即可

2.2mysqldump備份與恢復(fù)

①mysql自帶的備份工具，可方便實現(xiàn)對mysql的備份

②可以將指定的庫，表導(dǎo)出為SQL腳本

③使用命令mysql導(dǎo)入備份的數(shù)據(jù)

四、Mysql完全備份與恢復(fù)

1、物理冷備份與恢復(fù)

systemctl stop mysqld
yum -y install xz
 
#壓縮備份
cd /usr/local/mysql/data
tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#模擬故障，刪除數(shù)據(jù)庫
drop database kgc;
 
#解壓恢復(fù)
tar jxvf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./

前期準(zhǔn)備：

模擬故障，刪除kgc

恢復(fù)故障

2、mysqldump完全備份與恢復(fù)（溫備份）

2.1完全備份一個或多個完整的庫（包括其中所有的表）

mysqldump -u root -p[密碼] --databases 庫名1 [庫名2] ... > /備份路徑/備份文件名.sql   
#導(dǎo)出的就是數(shù)據(jù)庫腳本文件
 
例:
 
mysqldump -u root -p --databases ky35 > /opt/kgc.sql       #備份一個kgc庫
mysqldump -u root -p --databases mysql kgc > /opt/mysql-kgc.sql    #備份mysql與 kgc兩個庫

備份kgc庫

2.2完全備份mysql服務(wù)器中所有的庫

mysqldump -u root -p[密碼] --all-databases > /備份路徑/備份文件名.sql
 
例:
 
mysqldump -u root -p --all-databases > /opt/all.sql

2.3完全別分指定庫中的部分表

mysqldump -u root -p[密碼] 庫名 [表名1] [表名2] ... > /備份路徑/備份文件名.sql
 
例:
 
mysqldump -u root -p [-d] kgc ky35 ky36 > /opt/kgc_ky.sql
 
#使用“-d”選項，說明只保存數(shù)據(jù)庫的表結(jié)構(gòu)
 
#不使用“-d"選項，說明表數(shù)據(jù)也進(jìn)行備份
#做為一個表結(jié)構(gòu)模板

2.4查看備份文件

grep -v "^--" /opt/kgc_ky35.sql | grep -v "^/" | grep -v "^$"

3、恢復(fù)數(shù)據(jù)庫

3.1方法一：source恢復(fù)

使用mysqldump導(dǎo)出的文件，可使用導(dǎo)入的方法
 
source命令  進(jìn)入數(shù)據(jù)庫  
 
mysql> source  /opt/kgc.sql ------>備份文件路徑
 
登錄到MySQL數(shù)據(jù)庫，執(zhí)行source備份sql腳本的路徑

3.2方法二：mysql恢復(fù)

mysql -uroot -p123456 < /opt/kgc.sql

4、恢復(fù)表（依據(jù)上面的備份表來進(jìn)行）

4.1方法一：source恢復(fù)

4.2方法二：mysql恢復(fù)

5、注意：

mysqldump嚴(yán)格來說屬于溫備份，會對表進(jìn)行寫入的鎖定

在全量備份與恢復(fù)實驗中，有kgc庫，kgc庫中有ky35表，此時需要注意

①當(dāng)備份時加--databases，表示針對kgc庫

#備份命令
mysqldump -uroot -p123456 --databases kgc > /opt/ky35.sql 備份庫后
 
#恢復(fù)命令過程為：
mysql -uroot -p123123
drop database kgc;
exit
mysql -uroot -p123123 < /opt/ky35.sql

②當(dāng)備份時不加--databases時，表示針對kgc庫下的所有表

當(dāng)備份時不加 --databases，表示針對school庫下的所有表
 
#備份命令
mysqldump -uroot -p123456 kgc > /opt/ky35.sql
 
#恢復(fù)過程：
mysql -uroot -p123456
drop database kgc;
create database kgc;
exit
mysql -uroot -p123456 kgc < /opt/ky35.sql

此時只能先進(jìn)入mysql中創(chuàng)建kgc才能將數(shù)據(jù)表恢復(fù)

二者的差別就是在于--databases，①會先創(chuàng)建databases，開始，而②時直接針對表進(jìn)行操作。

③在實際生產(chǎn)環(huán)境中，可以使用shell腳本來實現(xiàn)自動定時備份。

0 1* * 7 /usr/local/mysql/bin/mysqldump -uroot -p123456 kgc info1 > ./kgc_infol_$(date +%Y%m%d).sql ;
/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

五、Mysql增量備份與恢復(fù)

1、恢復(fù)的方式

1.1一般恢復(fù)

就是將所有備份的二進(jìn)制日志內(nèi)容全部恢復(fù)

1.2基于位置恢復(fù)

數(shù)據(jù)庫在某一時間點可能既有錯誤也有正確的操作

可以基于精準(zhǔn)的位置跳過錯誤的操作

發(fā)生錯誤節(jié)點之前的一個節(jié)點上，上一次正確操作的位置點停止

1.3基于時間恢復(fù)

跳過某個發(fā)生錯誤的時間點實現(xiàn)數(shù)據(jù)恢復(fù)

在錯誤時間點停止，在下一個正確時間點開始

2、二進(jìn)制文件介紹

2.1、開啟二進(jìn)制日志功能

vim /etc/my.cnf
 
[mysqld]
 
log-bin=mysql-bin
 
binlog_format = MIXED      
#可選，指定二進(jìn)制日志(binlog)的記錄格式為MIXED（混合輸入）
 
server-id = 1              
#可加可不加該命令
 
systemctl restart mysqld

2.2二進(jìn)制日志的三種不同的記錄格式

①statement（基于SQL語句）

每一條涉及到被修改的sql都會被記錄在binlog中

缺點：日志量過大，如sleep()函數(shù)，last_insert_id()>，以及user-defined fuctions（udf）、主從復(fù)制等架構(gòu)記錄日志時會出現(xiàn)問題

②row（基于行）

只記錄變動的記錄，不記錄sql的上下文環(huán)境

缺點：如果遇到update......set....where true 那么binlog的數(shù)據(jù)量會越來越大

③mixed（推薦使用）

一般的語句使用statement，函數(shù)使用row方式存儲

2.3查看二進(jìn)制日志文件的內(nèi)容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
 
mysqlbinlog --no-defaults  /opt/mysql-bin.000002
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
 
#--base64-output=decode-rows：使用64位編碼機(jī)制去解碼（decode）并按行讀取（rows）
#-v: 顯示詳細(xì)內(nèi)容
#--no-defaults : 默認(rèn)字符集（不加會報UTF-8的錯誤）
PS: 可以將解碼后的文件導(dǎo)出為txt格式，方便查閱
 
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

2.4二進(jìn)制日志中需要關(guān)注的部分

①at：開始的位置點

②end_log_pos：結(jié)束的位置

③時間戳：240325 19：00

④SQL語句

3、增量備份過程（全備+增備）

使用mysqldump

可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件(例如:mysql-bin.000006)
 
mysqladmin -u root -p flush-logs

插入新數(shù)據(jù)，以模擬數(shù)據(jù)的增加或變更

注意：在第一次完全備份之后刷新二進(jìn)制文件，在第二個二進(jìn)制文件中記載著“增量備份的數(shù)據(jù)”

在kgc庫中創(chuàng)建ky36，并插入數(shù)據(jù)

創(chuàng)建njzb庫，并復(fù)制kgc中的ky36表，命名為ky01

再次生成新的二進(jìn)制日志文件(例如:mysql-bin.000007)
 
mysqladmin -u root -p flush-logs
 
#之前的步驟創(chuàng)建kgck庫中的sss表和創(chuàng)建NJ庫以及庫中的ZB表的操作會保存到mysql-bin.000006文件中

4、增量恢復(fù)

全部恢復(fù)

模擬所有數(shù)據(jù)丟失，直接刪除kgc跟njzb庫

先恢復(fù)完全備份

基于mysql-bin.000006恢復(fù)增量，恢復(fù)所有
 
mysqlbinlog --no-defaults / usr/local/mysql/data/mysql-bin.000006 | mysql -uroot -p123456

5、斷點恢復(fù)

#查看日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000006

基于位置點恢復(fù)
#僅恢復(fù)到操作 ID 為“623"之前的數(shù)據(jù)，即不恢復(fù)"user4"的數(shù)據(jù)
 
mysqlbinlog --no-defaults --stop-position='1793' /opt/mysql-bin.000002 | mysql -uroot -p密碼
#僅恢復(fù)"user4"的數(shù)據(jù)，跳過"user3"的數(shù)據(jù)恢復(fù)
 
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p      
#恢復(fù)從位置為400開始到位置為623為止

6、基于時間點恢復(fù)

#僅恢復(fù)到16:41:24 之前的數(shù)據(jù)，即不恢復(fù)"user4"的數(shù)據(jù)
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
 
#僅恢復(fù)"user4"的數(shù)據(jù)，跳過"user3"的數(shù)據(jù)恢復(fù)
mysqlbinlog --no-defaults --start-datetime='2020-11-2216:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
 
如果恢復(fù)某條SQL語之前的所有數(shù)據(jù)，就stop在這個語句的位置節(jié)點或者時間點
如果恢復(fù)某條SQL語句以及之后的所有數(shù)據(jù)，就從這個語句的位置節(jié)點或者時間點start

六、小結(jié)：

①在增量備份恢復(fù)時，應(yīng)該先從完全備份恢復(fù)，然后再到二進(jìn)制日志逐一恢復(fù)

②在恢復(fù)某條sql語句之前的所有數(shù)據(jù)，就stop在這個語句的位置節(jié)點或者時間點

③如果恢復(fù)某個sql語句以及之后的所有數(shù)據(jù)，就從這個語句的位置節(jié)點或者時間節(jié)點start

④全備庫source針對庫mysql針對庫中的表，備份時使用--databases使得恢復(fù)時source和mysql效果一致。

⑤只保存數(shù)據(jù)庫表結(jié)構(gòu)時在加-d，不加-d時默認(rèn)將表以及結(jié)構(gòu)都保存。

到此這篇關(guān)于Mysql數(shù)據(jù)庫的日志管理、備份與回復(fù)的文章就介紹到這了,更多相關(guān)Mysql日志管理、備份與回復(fù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論