MySQL日誌管理，備份和恢復

MySQL日誌管理，備份和恢復

一、日誌管理

MySQL的預設日誌儲存位置為

/usr/local/mysql/data

1.

日誌開啟方式有兩種：透過配置檔案或者是透過命令

透過命令修改開啟的日誌是臨時的，關閉或重啟服務後就會關閉

Ⅰ、常用日誌型別及開啟

vim /etc/my.cnf

[mysqld]

......

1.

2.

3.

欄位  含義

xxx%  匹配以xxx開頭的欄位

%xxx  匹配以xxx結尾的欄位

%xxx%  包含xxx的欄位

xxx  精準匹配

1、錯誤日誌

用來記錄當MySQL啟動、停止或執行時發生的錯誤資訊，預設已開啟

指定日誌的儲存位置和檔名

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

1.

2.

2、通用查詢日誌

用來記錄MySQL的所有連線和語句，預設是關閉的

general_log=ON

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

1.

2.

3、二進位制日誌（binlog）

用來記錄所有當MySQL啟動、停止或執行時傳送的錯誤訊息，預設是關閉

log-bin=mysql-bin

##或者

log_bin=mysql-bin

1.

2.

3.

4、慢查詢日誌

用來記錄所有執行時間超過long_query_time秒的語句，可以找到哪些查詢語句執行時間長，以便於最佳化，預設時關閉的

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5      

1.

2.

3.

systemctl restart mysqld.service      ###修改完配置檔案後重啟MySQL服務

1.

Ⅱ、檢視日誌狀態

1、檢視通用查詢日誌是否開啟

mysql -u root -p

show variables like 'general%';

1.

2.

2、檢視二進位制日誌是否開啟

show variables like 'log_bin%';

1.

3、檢視慢查詢日誌功能是否開啟

show variables like '%slow%'；

1.

檢視慢查詢時間設定

show variables like 'long_query_time';

1.

在資料庫中設定開啟慢查詢的方法

set global slow_query_log=ON;

該方法重啟服務失效

1.

2.

二、備份和恢復

Ⅰ、資料備份的重要性

備份的主要目的是災難恢復；在生產環境中，資料的安全性至關重要；任何資料的丟失都可能產生嚴重的後果

丟失的原因有：

程式錯誤

人為操作錯誤

運算錯誤

磁碟故障

災難（火災、地震、盜竊等）

Ⅱ、備份型別

1、從物理和邏輯的角度分類（邏輯備份、物理備份）

物理備份：對資料庫作業系統的物理檔案（如資料檔案、日誌檔案等）的備份；物理備份適用於在出現問題時需要快速恢復的大型重要資料庫。

其方法：

冷備份（離線備份）：是在關閉資料庫的時候進行的

熱備份（聯機備份）：資料庫處於執行狀態，依賴於資料庫的日誌檔案

溫備份：資料庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作

邏輯備份：對資料庫邏輯元件（如：表等資料庫物件）的備份，適用於可以編輯資料值或表結構較小的資料量，或者在不同的機器體系結構上重新建立資料。

2、從資料庫的備份策略角度，備份可分為

完全備份:==每次對資料庫進行完整的備份，對整個資料庫、資料庫結構和檔案結構的備份，儲存的是備份完成時刻的資料庫，是差異備份與增量備份的基礎。==完全備份的備份與恢復操作都非常簡單方便，但是資料存在大量的重複，並且會佔用大量的磁碟空間，備份的時間也很長。

完全備份是對整個資料庫、資料庫結構和檔案結構的備份。

儲存的是備份完成時刻的資料庫。

是差異備份與增量備份的基礎。

相當於基石。

差異備份：==備份自從上次完全備份之後被修改過的所有檔案，備份的時間節點是從上次完整備份起，備份資料量會越來越大。==恢復資料時，只需恢復上次的完全備份與最近的一次差異備份。

增量備份：==只要在上次完全備份或者增量備份後被修改的檔案才會被備份。==以上次完整備份或上次增量備份的時間為時間點，僅備份這之間的資料變化，因而備份的資料量小，佔用空間小，備份速度快。但恢復時，需要從上一次的完整備份開始到最後一次增量備份之的所有增量依次恢復，如中間某次的備份資料損壞，將導致資料的丟失。

3、常見備份方法

物理冷備份

物理冷備份時需要在資料庫處於關閉狀態下，能夠較好地保證資料庫的完整性。

物理冷備份一般用於非核心業務，這類業務一般都允許中斷。

物理冷備份的特點就是速度快，恢復時也是最為簡單的。

通常透過直接打包資料庫資料夾(/usr/loc.al/mysql/data)來實現備份。

專用備份工具mysqldump 或 mysqlhotcopy

mysqldump程式和mysqlhotcopy都可以做備份。

mysqldump是客戶端常用邏輯備份程式，能夠產生一組被執行以後再現原始資料庫物件定義和表資料的SQL語句。它可以轉儲一個到多個MySQL資料庫，對其進行備份或傳輸到遠端SQL伺服器。mysqldump更為通用，因為它可以備份各種表。

mysqlhotcopy僅適用於某些儲存引擎（MyISAM和ARCHIVE）。

透過啟用二進位制日誌進行增量備份

支援增量備份，進行增量備份時必須啟用二進位制日誌。

二進位制日誌檔案為使用者提供複製，對執行備份點後進行的資料庫更改所需的資訊進行恢復。

如果進行增量備份(包含自上次完全備份或增量備份以來發生的資料修改) ，需要重新整理二進位制日誌。

透過第三方工具備份

第三方工具Percona xtraBackup是一個免費的MysQL熱備份軟體，支援線上熱備份Innodb和xtraDB，也可以支援MySQL表備份，不過MyISAM表的備份要在表鎖的情況下進行。

4、備份實驗

1）建立表結構、表資料

mysql -u root -p

create database SCHOOL;

use SCHOOL;

create table if not exists CLASS1 (

id int(4) not null auto_increment,

name varchar(10) not null,

sex char(10) not null,

hobby varchar(50),

primary key (id));

insert into CLASS1 values(1,'user1','male','running');

insert into CLASS1 values(2,'user2','female','singing');

set password = password('123');

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

2）完全備份

InnoDB儲存引擎的資料庫在磁碟上儲存成三個檔案：db.opt(表屬性檔案)、表名.frm(表結構檔案)、表名.ibd(表資料檔案)。

物理冷備份與恢復

systemctl stop mysqld

yum -y install xz   # xz 為一種壓縮工具

1.

2.

壓縮備份

tar Jcvf /mnt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

1.

解壓恢復

tar Jxvf /mnt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/data/

1.

完全備份一個或多個完整庫

mysqldump -u root -p[密碼] --databases 庫名1 [庫名2] … > /備份路徑/備份檔名.sql #匯出的就是資料庫指令碼檔案

例：

mysqldump -uroot -p123--databases SCHOOL > /opt/SCHOOL.sql

mysqldump -uroot -p123 --databases mysql SCHOOL > /opt/mysql-SCHOOL.sql

1.

2.

3.

4.

5.

3）備份所有的庫

mysqldump -uroot -p[密碼] --all-databases > /備份路徑/備份檔名.sql

例：

mysqldump -uroot -p[密碼] --all-databases > /備份路徑/all.sql

1.

2.

3.

4）完全備份指定庫中的部份表

mysqldump -u root -p[密碼] 庫名 [表名1] [表名2] … > /備份路徑/備份檔名.sql

例：

mysqldump -uroot -p010230 [-d] meeting working > /opt/meeting_working.sql

#使用“-d”選項，說明只儲存資料庫的表結構

#不使用“-d”選項，說明表資料也進行備份

1.

2.

3.

4.

5.

5）檢視備份檔案

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

1.

6）完全備份恢復

恢復資料庫

mysql -uroot -p123 -e 'drop database SCHOOL;'

1.

“-e”選項，用於指定連線MySQL後執行的命令，命令執行完後自動退出

mysql -uroot -p123 -e 'SHOW DATABASES;'

mysql -uroot -p123 < /opt/SCHOOL.sql

mysql -uroot -p123 -e 'SHOW DATABASES;'

1.

2.

3.

4.

恢復資料表

當備份檔案中只包含表的備份，而不包含建立的庫的語句時，執行匯入操作時必須指定庫名，且目標庫必須存在。

mysql -uroot -p123 -e 'drop table SCHOOL.CLASS1;'

mysql -uroot -p123 -e 'show tables from SCHOOL;'

mysql -uroot -p123 SCHOOL < /opt/SCHOOL_CLASS1.sql

mysql -uroot -p123 -e 'show tables from SCHOOL;'

1.

2.

3.

4.

5.

三、MySQL增量備份和恢復

1、增量備份需要開啟二進位制日誌功能

vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format = MIXED  （可選項） 

server-id = 1

1.

2.

3.

4.

5.

三種記錄格式：

STATEMENT（基於SQL語句，預設）  ROW（基於行）  MIXED（混合模式）

每一條涉及到被修改的 sql 都會記錄在 binlog 中  只記錄變動的記錄，不記錄 sql 的上下文  一般語句使用 STATEMENT，函式使用 ROW

日誌量過大，如函式、主從複製等構架記錄日誌時會出現問題  如果遇到 updata…set…where true，那麼日誌資料量就會很大  推薦使用

2、可每週對資料庫或表進行完全備份

mysqldump -uroot -p123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1_$(date +%F).sql

mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql

1.

2.

3、可每天進行增量備份操作，生成新的二進位制日誌檔案(例如 mysql-bin.000002)

先完成完全備份（在建立好表和庫的基礎上）

systemctl restart mysqld.service

mysqldump -uroot -p meeting working > /mnt/meeting_working_$(date +%F).sql

mysqldump -uroot -p meeting > /mnt/meeting_$(date +%F).sql

生成新的二進位制檔案(可每天進行增量備份操作)

mysqladmin -uroot -p flush-logs

1.

2.

3.

4.

5.

6.

7.

4、插入新資料，以模擬資料的增加或變更

mysql -uroot -p123

use SCHOOL;

insert into CLASS1 values(3,'user3','male','game');

insert into CLASS1 values(4,'user4','female','reading');

1.

2.

3.

4.

5、檢視二進位制日誌檔案的內容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

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

1.

2.

6、一般回覆 --模擬丟失更改的資料的恢復步驟

mysql -uroot -p123

use SCHOOL;

delete from CLASS1 where id=3;

delete from CLASS1 where id=4;

select * from CLASS1;

quit

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123

mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"