一.MySQL 日志管理

MySQL 的日志默認保存位置為 /usr/local/mysql/data
MySQL 的日志配置文件為/etc/my.cnf ，里面有個[mysqld]項
修改配置文件：

vim /etc/my.cnf
[mysqld]

1、錯誤日志

##錯誤日志，用來記錄當(dāng)MySQL啟動、停止或運行時發(fā)生的錯誤信息，默認已開啟

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

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

##通用查詢?nèi)罩?，用來記錄MySQL的所有連接和語句，默認是關(guān)閉的

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

3、二進制日志

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

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

##慢查詢?nèi)罩荆脕碛涗浰袌?zhí)行時間超過long_query_time秒的語句，可以找到哪些查詢語句執(zhí)行時間長，以便于優(yōu)化，默認是關(guān)閉的

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #設(shè)置超過5秒執(zhí)行的語句被記錄，缺省時為10秒

systemctl restart mysqld

mysql -u root -p

5、查看日志

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

show variables like 'log_bin%'; #查看二進制日志是否開啟

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

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

6、實例操作

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ù)庫備份的重要性與分類

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

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

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

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

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

程序錯誤

人為操作錯誤

運算錯誤

磁盤故障

不可控因素

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

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

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

物理備份方法：

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

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

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

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

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

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

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

三、常見的備份方法

1、物理冷備

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

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

2、專用備份工具mydump或mysqlhotcopy

• myaqldump常用的邏輯備份工具

• mysqlhotcopy僅擁有備份MyISM和ARCHIVE表

3、啟用二進制日志進行增量備份

• 進行增量備份，需要刷新二進制日志

4、第三方工具備份

• 免費MySQL熱備份軟件Percona XtraBackup

四、MySQL完全備份

1、完全備份的概念

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

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

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

2、優(yōu)點

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

3、缺點

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

• 占用大量的備份空間

• 備份與恢復(fù)時間長

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

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

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

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

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

4.2 mysqldump備份與恢復(fù)

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

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

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

五、MySQL增量備份

1、使用mysqldump進行完全備份存在的問題

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

• 備份時間與恢復(fù)時間過長

2、增量備份的概念

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

3、增量備份的特點

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

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

4、增量備份的方法

MySQL沒有提供直接的增量備份方法

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

5、MySQL二進制日志對備份的意義

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

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

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

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

6.1 一般恢復(fù)

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

6.2 基于位置恢復(fù)

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

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

6.3 基于時間點恢復(fù)

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

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

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

• 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ù)庫bbc，測試備份能否恢復(fù)

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

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

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

例：

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

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

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

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

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

#不使用“-d”選項，說明表數(shù)據(jù)也進行備份

2.4 查看備份文件

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

2.5 開啟服務(wù)

systemctl start mysqld 

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

mysql -u root -p -e 'drop database bbc;'
#“-e”選項，用于指定連接 MySQL 后執(zhí)行的命令，命令執(zhí)行完后自動退出
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)建的庫的語句時，執(zhí)行導(dǎo)入操作時必須指定庫名，且目標(biāo)庫必須存在。

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、開啟二進制日志功能

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

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

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

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

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

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、可每天進行增量備份操作，生成新的二進制日志文件(例如 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、再次生成新的二進制日志文件(例如 mysql-bin.000003)

mysqladmin -u root -p flush-logs　

1.6、查看二進制日志文件的內(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位編碼機制去解碼并按行讀取
#-v：顯示詳細內(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恢復(fù)

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

BEGIN
/*!*/;
# at 831104   #斷點
#210831 11:19:18 server id 1  end_log_pos 831220 CRC32 0xa8f834ba   Query   thread_id=55    exec_time=0 error_code=0 #時間
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ù)，跳過“5”的數(shù)據(jù)恢復(fù)
mysqlbinlog --no-defaults --start-position='831681' /opt/mysql-bin.000009 | mysql -uroot -p

（2）基于時間點恢復(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ù)，跳過“5”的數(shù)據(jù)恢復(fù)
mysqlbinlog --no-defaults--start-datetime='2021-08-31 11:20:05' /opt/mysql-bin.000009 |mysql -uroot -p

加粗樣式

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