一.MySQL 日志管理

MySQL 的日志默認(rèn)保存位置為 /usr/local/mysql/data
MySQL 的日志配置文件為/etc/my.cnf ，里面有個(gè)[mysqld]項(xiàng)
修改配置文件：

vim /etc/my.cnf
[mysqld]

1、錯(cuò)誤日志

##錯(cuò)誤日志，用來(lái)記錄當(dāng)MySQL啟動(dòng)、停止或運(yùn)行時(shí)發(fā)生的錯(cuò)誤信息，默認(rèn)已開啟

log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名

2、通用查詢?nèi)罩?/h3>

##通用查詢?nèi)罩荆脕?lái)記錄MySQL的所有連接和語(yǔ)句，默認(rèn)是關(guān)閉的

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

3、二進(jìn)制日志

##二進(jìn)制日志(binlog)，用來(lái)記錄所有更新了數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)的語(yǔ)句，記錄了數(shù)據(jù)的更改，可用于數(shù)據(jù)恢復(fù)，默認(rèn)已開啟
log-bin=mysql-bin #也可以 log_bin=mysql-bin

4、慢查詢?nèi)罩?/h3>

##慢查詢?nèi)罩?，用?lái)記錄所有執(zhí)行時(shí)間超過(guò)long_query_time秒的語(yǔ)句，可以找到哪些查詢語(yǔ)句執(zhí)行時(shí)間長(zhǎng)，以便于優(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 #設(shè)置超過(guò)5秒執(zhí)行的語(yǔ)句被記錄，缺省時(shí)為10秒

systemctl restart mysqld

mysql -u root -p

5、查看日志

show variables like 'general%'; #查看通用查詢?nèi)罩臼欠耖_啟

show variables like 'log_bin%'; #查看二進(jìn)制日志是否開啟

show variables like '%slow%'; #查看慢查詢?nèi)展δ苁欠耖_啟
show variables like 'long_query_time'; #查看慢查詢時(shí)間設(shè)置

set global show_query_log=ON; #在數(shù)據(jù)庫(kù)中設(shè)置開啟慢查詢的方法

6、實(shí)例操作

6.1 修改配置文件并重啟服務(wù)

[root@www ~]# tail -10 /etc/my.cnf
 
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
log-bin=mysql-bin
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

[root@www ~]# systemctl restart mysqld.service

6.2 查詢?nèi)罩?/p>

6.3 開啟以及關(guān)閉慢查詢的方法

二、數(shù)據(jù)庫(kù)備份的重要性與分類

1、數(shù)據(jù)備份的重要性

• 備份的主要目的是災(zāi)難恢復(fù)

• 在生產(chǎn)環(huán)境中，數(shù)據(jù)的安全性至關(guān)重要

• 任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果

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

程序錯(cuò)誤

人為操作錯(cuò)誤

運(yùn)算錯(cuò)誤

磁盤故障

不可控因素

2、從物理與邏輯的角度，備份分為：

• 物理備份： 對(duì)數(shù)據(jù)庫(kù)操作系統(tǒng)的物理文件（如數(shù)據(jù)文件、日志文件等）的備份

• 邏輯備份：對(duì)數(shù)據(jù)庫(kù)邏輯組件（如：表等數(shù)據(jù)庫(kù)對(duì)象）的備份

物理備份方法：

? 冷備份（脫機(jī)備份）：是在關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候進(jìn)行的

? 熱備份（聯(lián)機(jī)備份）：數(shù)據(jù)庫(kù)處于運(yùn)行狀態(tài)，依賴于數(shù)據(jù)庫(kù)的日志文件

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

3、從數(shù)據(jù)庫(kù)的備份策略角度，備份可分為：

• 完全備份：每次對(duì)數(shù)據(jù)庫(kù)進(jìn)行完整的備份

• 差異備份：備份自從上次完全備份之后被修改過(guò)的文件

• 增量備份：只有在上次完全備份或者增量備份后被修改的文件才會(huì)被備份

三、常見的備份方法

1、物理冷備

• 備份時(shí)數(shù)據(jù)庫(kù)處于關(guān)閉狀態(tài)，直接打包數(shù)據(jù)庫(kù)文件

• 備份速度快，恢復(fù)時(shí)也是最簡(jiǎn)單的

2、專用備份工具mydump或mysqlhotcopy

• myaqldump常用的邏輯備份工具

• mysqlhotcopy僅擁有備份MyISM和ARCHIVE表

3、啟用二進(jìn)制日志進(jìn)行增量備份

• 進(jìn)行增量備份，需要刷新二進(jìn)制日志

4、第三方工具備份

• 免費(fèi)MySQL熱備份軟件Percona XtraBackup

四、MySQL完全備份

1、完全備份的概念

是對(duì)整個(gè)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)結(jié)構(gòu)和文件結(jié)構(gòu)的備份

保存的是備份完成時(shí)刻的數(shù)據(jù)庫(kù)

是差異備份與增量備份的基礎(chǔ)

2、優(yōu)點(diǎn)

• 備份與恢復(fù)操作簡(jiǎn)單方便

3、缺點(diǎn)

• 數(shù)據(jù)存在大量的重復(fù)

• 占用大量的備份空間

• 備份與恢復(fù)時(shí)間長(zhǎng)

4、數(shù)據(jù)庫(kù)完全備份分類

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

• 關(guān)閉MySQL數(shù)據(jù)庫(kù)

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

• 直接替換現(xiàn)有MySQL目錄即可

4.2 mysqldump備份與恢復(fù)

• Mysql自帶的備份工具，可方便實(shí)現(xiàn)對(duì)MySQL的備份

• 可以將指定的庫(kù)、表導(dǎo)出為SQL腳本

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

五、MySQL增量備份

1、使用mysqldump進(jìn)行完全備份存在的問(wèn)題

• 備份數(shù)據(jù)中有重復(fù)數(shù)據(jù)

• 備份時(shí)間與恢復(fù)時(shí)間過(guò)長(zhǎng)

2、增量備份的概念

是自上一次備份后增加/變化的文件或者內(nèi)容

3、增量備份的特點(diǎn)

• 沒(méi)有重復(fù)數(shù)據(jù)，備份不大，時(shí)間短

• 恢復(fù)需要上次完全備份及完全備份之后所有的增量備份才能恢復(fù)，且要對(duì)所有增備份進(jìn)行逐個(gè)反推恢復(fù)

4、增量備份的方法

MySQL沒(méi)有提供直接的增量備份方法

可通過(guò)MySQL提供的二進(jìn)制日志間接實(shí)現(xiàn)增量備份

5、MySQL二進(jìn)制日志對(duì)備份的意義

• 二進(jìn)制日志保存了所有更新或者可能更新數(shù)據(jù)庫(kù)的操作

• 二進(jìn)制日志在啟動(dòng)MySQL服務(wù)器后開始記錄，并在文件達(dá)到max_ binlog_ size所設(shè) 置的大小或者接收到flush logs命令后重新創(chuàng)建新的日志文件

• 只需定時(shí)執(zhí)行flush logs方法重新創(chuàng)建新的日志，生成二進(jìn)制文件序列，并及時(shí)把這些日志保存到安全的地方就完成了-一個(gè)時(shí)間段的增量備份

6、MySQL數(shù)據(jù)庫(kù)增量恢復(fù)

6.1 一般恢復(fù)

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

6.2 基于位置恢復(fù)

• 數(shù)據(jù)庫(kù)在某一時(shí)間點(diǎn)可能既有錯(cuò)誤的操作也有正確的操作

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

6.3 基于時(shí)間點(diǎn)恢復(fù)

• 跳過(guò)某個(gè)發(fā)生錯(cuò)誤的時(shí)間點(diǎn)實(shí)現(xiàn)數(shù)據(jù)恢復(fù)

六、MySQL 完全備份與恢復(fù)

InnoDB存儲(chǔ)引擎的數(shù)據(jù)庫(kù)在磁盤上存儲(chǔ)成三個(gè)文件：

• db.opt(表屬性文件)
• 表名.frm(表結(jié)構(gòu)文件)
• 表名.ibd(表數(shù)據(jù)文件)

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

systemctl stop mysqld
yum -y install xz
 
cd /usr/local/mysql
#壓縮備份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz ./data
#解壓恢復(fù)
tar Jxvf /opt/mysql_all_2021-8-30.tar.xz　　

1.1 備份data目錄

1.2 刪除數(shù)據(jù)庫(kù)bbc，測(cè)試備份能否恢復(fù)

2、mysqldump 備份與恢復(fù)

2.1 完全備份一個(gè)或多個(gè)完整的庫(kù)（包括其中所有的表）

mysqldump -u root -p[密碼] --databases 庫(kù)名1 [庫(kù)名2] … > /備份路徑/備份文件名.sql #導(dǎo)出的就是數(shù)據(jù)庫(kù)腳本文件

例：

mysqldump -u root -p --databases bbc > /opt/bbc.sql
mysqldump -u root -p --databases mysql bbc > /opt/mysql-bbc.sql

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

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

mysqldump -u root -p --all-databases > /opt/all.sql　

#使用“-d”選項(xiàng)，說(shuō)明只保存數(shù)據(jù)庫(kù)的表結(jié)構(gòu)

#不使用“-d”選項(xiàng)，說(shuō)明表數(shù)據(jù)也進(jìn)行備份

2.4 查看備份文件

grep -v “^–” /opt/bbc_test1.sql | grep -v “^/” | grep -v “^$”

2.5 開啟服務(wù)

systemctl start mysqld 

2.6 恢復(fù)數(shù)據(jù)庫(kù)

mysql -u root -p -e 'drop database bbc;'
#“-e”選項(xiàng)，用于指定連接 MySQL 后執(zhí)行的命令，命令執(zhí)行完后自動(dòng)退出
mysql -u root -p -e 'SHOW DATABASES;'
 
mysql -u root -p < /opt/bbc.sql
mysql -u root -p -e 'SHOW DATABASES;'

2.7 恢復(fù)數(shù)據(jù)表

當(dāng)備份文件中只包含表的備份，而不包含創(chuàng)建的庫(kù)的語(yǔ)句時(shí)，執(zhí)行導(dǎo)入操作時(shí)必須指定庫(kù)名，且目標(biāo)庫(kù)必須存在。

mysqldump -u root -p bbc test1 > /opt/bbc_test1.sql
 
mysql -u root -p -e 'drop table bbc.test1;'
mysql -u root -p -e 'show tables from bbc;'
 
mysql -u root -p bbc < /opt/bbc_test1.sql
mysql -u root -p -e 'show tables from bbc;'

七、MySQL 增量備份與恢復(fù)

1、MySQL 增量備份

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

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED   指定二進(jìn)制日志(binlog)的記錄格式為 MIXED
server-id = 1　　

#二進(jìn)制日志(binlog)有3種不同的記錄格式：STATEMENT(基于SQL語(yǔ)句)、ROW(基于行)、MIXED(混合模式)，默認(rèn)格式是STATEMENT

只要重啟服務(wù)就會(huì)生成二進(jìn)制文件

systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*　　

1.2、可每周對(duì)數(shù)據(jù)庫(kù)或表進(jìn)行完全備份

mysqldump -u root -p bbc test1 > /opt/bbc_test1_$(date +%F).sql
mysqldump -u root -p --all-databases> /opt/all_$(date +%F).sql

1.3、可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件(例如 mysql-bin.000002)

mysqladmin -u root -p flush-logs

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

use bbc;
insert into test1 values(4,‘nancy’,‘女’,23);
insert into test1 values(5,‘jack’,‘男’,26);

1.5、再次生成新的二進(jìn)制日志文件(例如 mysql-bin.000003)

mysqladmin -u root -p flush-logs　

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

cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002　　

#–base64-output=decode-rows：使用64位編碼機(jī)制去解碼并按行讀取
#-v：顯示詳細(xì)內(nèi)容

2、MySQL 增量恢復(fù)

2.1．一般恢復(fù)

（1）模擬丟失更改的數(shù)據(jù)的恢復(fù)步驟

use bbc;
delete from test1 where id=4;
delete from test1 where id=5;
 
mysqlbinlog --no-defaults /opt/mysql-bin.000005 | mysql -u root -p

（2）模擬丟失所有數(shù)據(jù)的恢復(fù)步驟

use bbc;
drop table test1;
 
mysql -u root -p bbc < /opt/bbc_test1_2021-8-31.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000005| mysql -u root -p

2.2．?dāng)帱c(diǎn)恢復(fù)

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000009 #查看二進(jìn)制日志文件
例：

BEGIN
/*!*/;
# at 831104   #斷點(diǎn)
#210831 11:19:18 server id 1  end_log_pos 831220 CRC32 0xa8f834ba   Query   thread_id=55    exec_time=0 error_code=0 #時(shí)間
SET TIMESTAMP=1630379958/*!*/;
insert into test1 values(4,'nancy',20,'女') #插入數(shù)據(jù)
/*!*/;
# at 831220
#210831 11:19:18 server id 1  end_log_pos 831251 CRC32 0xe3304ac7   Xid = 1393
COMMIT/*!*/;
 
BEGIN
/*!*/;
# at 831393
#210831 11:19:41 server id 1  end_log_pos 831508 CRC32 0x54ef1c18   Query   thread_id=55    exec_time=0 error_code=0
SET TIMESTAMP=1630379981/*!*/;
insert into test1 values(5,'jack',23,'男')
/*!*/;
# at 831508
#210831 11:19:41 server id 1  end_log_pos 831539 CRC32 0x0284ebb0   Xid = 1394
COMMIT/*!*/;
 
BEGIN
/*!*/;
# at 831681
#210831 11:20:05 server id 1  end_log_pos 831796 CRC32 0x3a5b528d   Query   thread_id=55    exec_time=0 error_code=0
SET TIMESTAMP=1630380005/*!*/;
insert into test1 values(6,'lucy',21,'女')
/*!*/;
# at 831796
#210831 11:20:05 server id 1  end_log_pos 831827 CRC32 0xe10e3da2   Xid = 1395
COMMIT/*!*/;　　

（1）基于位置恢復(fù)

#僅恢復(fù)到操作 ID 為“4”之前的數(shù)據(jù)，即不恢復(fù)“5”的數(shù)據(jù)
mysqlbinlog --no-defaults --stop-position='831393' /opt/mysql-bin.00009 | mysql -uroot -p

#僅恢復(fù)“6”的數(shù)據(jù)，跳過(guò)“5”的數(shù)據(jù)恢復(fù)
mysqlbinlog --no-defaults --start-position='831681' /opt/mysql-bin.000009 | mysql -uroot -p

（2）基于時(shí)間點(diǎn)恢復(fù)

#僅恢復(fù)到 11∶19∶41 之前的數(shù)據(jù)，即不恢復(fù)“5”的數(shù)據(jù)
mysqlbinlog --no-defaults --stop-datetime='2021-08-31 11:19:41'/opt/mysql-bin.000009 |mysql -uroot -p

#僅恢復(fù)“6”的數(shù)據(jù)，跳過(guò)“5”的數(shù)據(jù)恢復(fù)
mysqlbinlog --no-defaults--start-datetime='2021-08-31 11:20:05' /opt/mysql-bin.000009 |mysql -uroot -p

加粗樣式

以上就是MySQL日志管理和備份與恢復(fù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL日志管理備份與恢復(fù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論