詳細說說-Mysql日志管理、備份與恢復

一、Mysql日志管理基礎命令

Mysql默認日志保存位置為 usr/local/mysql/data

日志開啟方式有兩種：通過組態檔或者是通過命令
通過命令修改開啟的日志是臨時的，關倍訓重啟服務后就會關閉

(一)、Mysql常用日志型別及開啟

vim /etc/my.cnf
[mysqld]
......

1、錯誤日志

用來記錄當Mysql啟動、停止或運行時發生的錯誤資訊，默認已開啟

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

2、通過查詢日志

通過查詢日志，用來記錄Mysql的所有連接和陳述句，默認是關閉的

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

3、二進制日志（binlog）

用來記錄所有更新了資料或者已經潛在更新了資料的陳述句，記錄了資料的更改，可用于資料恢復，默認已開啟

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

4、慢查詢日志
用來記錄所有執行時間超過long_query_time秒的陳述句，可以找到哪些查詢陳述句執行時間長，以便于優化，默認是關閉的

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5	   		

systemctl restart mysqld    

二、查看日志狀態

1、查看通用查詢日志是否開啟

mysql -u root -p
show variables like 'general%';		

2、查看二進制日志是否開啟

show variables like 'log_bin%';

3、查看慢查詢日功能是否開啟

show variables like '%slow%';	

查看慢查詢時間設定

show variables like 'long_query_time';

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

set global slow_query_log=ON;
該方法重啟服務失效

三、Mysql備份與恢復

(一)、資料備份的重要性

?備份的主要目的是災難恢復

?在生產環境中，資料的安全性至關重要
?任何資料的丟失都可能產生嚴重的后果
?造成資料丟失的原因

• 程式錯誤
• 人為操作錯誤
• 運算錯誤
• 磁盤故障
• 災難（如火災、地震）和盜竊

（二）、資料備份的分類

1、從物理與邏輯的角度，備份可分為

• 物理備份：對資料庫作業系統的物理檔案（如資料檔案、日志檔案等）的備份

物理備份方法
1、冷備份（脫機備份）:是在關閉資料庫的時候進行的
2、熱備份（聯機備份）:資料庫處于運行狀態，依賴于資料庫的日志檔案
3、溫備份：資料庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作

• 邏輯備份：對資料庫邏輯組件（如：表等資料庫物件）的備份

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

1、完全備份：每次對資料庫進行完整的備份

完全備份是對整個資料庫、資料庫結構和檔案結構的備份，
保存的是備份完成時刻的資料庫，
是差異備份與增量備份的基礎，
相當于基石，

2、差異備份：備份自從上次完全備份之后被修改過的檔案

3、增量備份：只要在上次完全備份或者增量備份后被修改的檔案才會被備份

（三）、常見的備份方法

1、物理冷備

備份時資料庫處于關閉狀態，直接打包資料庫檔案
備份速度快，恢復時也是最簡單的
關閉MySQL資料庫
使用tar命令直接打包資料庫檔案夾
直接替換現有的MySQL目錄即可

2、專用備份工具mydump或者mysqlhotcopy

mysqldump常用的邏輯備份工具
MySQL自帶的備份工具，可實作對MySQL的備份
可以將指定的庫、表匯出為SQL腳本
使用命令mysql匯入備份的資料

mysqlhotcopy僅擁有備份myisam和archive表

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

進行增量備份，需要重繪二進制日志

4、第三方工具備份

免費的MySQL熱備份軟體Percona XtraBackup

四、MySQL完全備份與恢復

準備作業：

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('123123');##修改mysql密碼為123123

MySQL完全備份

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

1、物理冷備份與恢復

systemctl stop mysqld
yum -y install xz

壓縮備份

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

解壓恢復

tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data

systemctl restart mysql

2．mysqldump 備份與恢復

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

#匯出的備份檔案就是資料庫腳本檔案
mysqldump -u root -p[密碼] --databases 庫名1 [庫名2] … > /備份路徑/備份檔案名.sql
例：
mysqldump -u root -p --databases panrj > /opt/panrj.sql
mysqldump -u root -p --databases mysql panrj > /opt/mysql-panrj.sql

（2）、完全備份MySQL服務器中所有的庫

```c
mysqldump -u root -p[密碼] --all-databases > /備份路徑/備份檔案名.sql
例：
mysqldump -u root -p --all-databases > /opt/all.sql

（3）、完全備份指定庫中的部分表

mysqldump -u root -p[密碼] [-d] 庫名 [表名1] [表名2] … > /備份路徑/備份檔案名.sql
#使用“ -d ”選項，說明只保存資料庫的表結構
#不使用“ -d ”選項，說明表資料也進行備份
例：
mysqldump -u root -p panrj class > /opt/panrj_class.sql

（4）、查看備份檔案

cat /opt/備份的檔案 |grep -v "^--" | grep -v "^/" | grep -v "^$"

例：
cat /opt/panrj_CLASS1.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

完全備份恢復

1、恢復資料庫

#“-e”選項，用于指定連接 MySQL 后執行的命令，命令執行完后自動退出
mysql -u root -p -e 'drop database panrj;'
mysql -u root -p -e 'show databases;'

mysql -u root -p < /opt/panrj.sql
mysql -u root -p -e 'show databases;'

2、恢復資料表

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

mysql -u root -p -e 'drop table school.class;'
mysql -u root -p -e 'show tables from school;'

mysql -u root -p school < /opt/school_class.sql
mysql -u root -p -e 'show tables from school;'

MySQL增量備份與回復的方法

（一）、MySQL增量備份

1、開啟二進制日志

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


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

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

2、可每周對資料庫和資料表進行完全備份

• 這種定時任務可以結合crontab -e 計劃性任務來執行

mysqldump -uroot -p123123 panrj CLASS1 > /opt/panrj_CLASS1_$(date +%F).sql
mysqldump -uroot -p123123 --all-databases panrj > /opt/panrj_$(date +%F).sql

3、可以每天進行增量備份，生成新的二進制日志檔案(例如 mysql-bin.000008)

mysqladmin -u root -p flush-logs

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

mysql -u root -p
use panrj;
insert into class values (insert into CLASS1 values(1,'zhangsan','男','singing'););
insert into class values (insert into CLASS1 values(2,'yunyang','女','dance'););

5、再次生成新的二進制日志檔案(例如 mysql-bin.000009)

mysqladmin -u root -p flush-logs
#之前的步驟4的資料庫操作會保存到mysql-bin.000008檔案中，之后資料庫資料再發生變化則保存在mysql-bin.000009檔案中

6、查看二進制日志檔案的內容

cp /usr/local/mysql/data/mysql-bin.000008 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000008
#--base64-output=decode-rows：使用64位編碼機制去解碼并按行讀取
#-v：顯示詳細內容

（二）、MySQL增量恢復

1、 一般恢復

1）、模擬丟失更改的資料的恢復步驟

mysql -u root -p
use panrj;
delete from class where id=2;
delete from class where id=4;
select * from class;
quit

mysqlbinlog --no-defaults /opt/mysql-bin.000010 | mysql -u root -p
mysql -u root -p -e "select * from panrj.CLASS1;"

2）、模擬所有丟失資料的回復步驟

mysql -u root -p
use school;
drop table class;
show tables;
quit

mysql -uroot -p school < /opt/school_class_2021-02-06.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p
mysql -u root -p -e "select * from school.class;"

2、斷點恢復

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

#部分二進制檔案的內容
......
BEGIN
/*!*/;
##-------------解釋：at xxx 表示位置點------------------------------------------------
# at 302
##--------------解釋：開頭210224 15:45:53表示時間，其他的現在用不到-----------------------------------
#210224 15:45:53 server id 1  end_log_pos 449 CRC32 0xe972def7 	Query	thread_id=6	exec_time=0	error_code=0
##--------------解釋：這里是執行的操作陳述句---------------------
use `school`/*!*/;        <-------------use school;使用資料庫
SET TIMESTAMP=1612597553/*!*/; <------------建立時間戳
insert into class values ('6','qqq','223366','666666','nanjing') <-------向表中插入資料
/*!*/;
##---------------------------------------------------------------
# at 449
#210224 15:45:53 server id 1  end_log_pos 480 CRC32 0x5efde826 	Xid = 446
COMMIT/*!*/;
# at 480
#210224 15:45:54 server id 1  end_log_pos 545 CRC32 0x11768895 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 545
#210224 15:45:54 server id 1  end_log_pos 628 CRC32 0x778ea5fa 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1612597554/*!*/;
##-------------------------------插入第二個資料--------------------------
BEGIN
/*!*/;
# at 628
#210206 15:45:54 server id 1  end_log_pos 775 CRC32 0x66e3bb53 	Query	thread_id=6	exec_time=0	error_code=0
SET TIMESTAMP=1612597554/*!*/;
insert into class values ('7','www','666555','777777','changzhou')
/*!*/;
# at 775
#210224 15:45:54 server id 1  end_log_pos 806 CRC32 0x7b972395 	Xid = 447
COMMIT/*!*/;
# at 806
#210224 15:48:52 server id 1  end_log_pos 853 CRC32 0x0d77c456 	Rotate to mysql-bin.000003  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
.......

（1）基于位置恢復

僅恢復到位置點為“628”之前的資料，即不恢復“id=7”的資料

#模擬資料丟失
mysql -uroot -p123456 school < /opt/school_class_2021-02-24.sql
mysql -uroot -p123456 -e "select * from school.class;"
#到位置點628停止恢復資料
mysqlbinlog --no-defaults --stop-position='628' /opt/mysql-bin.000002 | mysql -uroot -p123456
#查看class表的資料
mysql -uroot -p123456 -e "select * from school.class;"

僅恢復“id=7”的資料，跳過“id=6”的資料

#模擬資料丟失
mysql -uroot -p123456 school < /opt/school_class_2021-02-24.sql
mysql -uroot -p123456 -e "select * from school.class;"
#從位置點628開始恢復資料
mysqlbinlog --no-defaults --start-position='628' /opt/mysql-bin.000002 | mysql -uroot -p123456
#查看class表的資料
mysql -uroot -p123456 -e "select * from school.class;"

（2）基于時間點恢復

僅恢復到210224 15:45:54之前的資料，即不恢復“id=7”的資料

#模擬資料丟失
mysql -uroot -p123456 school < /opt/school_class_2021-02-24.sql
mysql -uroot -p123456 -e "select * from school.class;"
#到2021-02-24 15:45:54截止恢復資料
mysqlbinlog --no-defaults --stop-datetime='2021-02-24 15:45:54' /opt/mysql-bin.000002 | mysql -uroot -p123456
#查看class表的資料
mysql -uroot -p123456 -e "select * from school.class;"

僅恢復“id=7”的資料，跳過“id=6”的資料恢復

#模擬資料丟失
mysql -uroot -p123456 school < /opt/school_class_2021-02-24.sql
mysql -uroot -p123456 -e "select * from school.class;"
#從2021-02-24 15:45:54開始恢復資料
mysqlbinlog --no-defaults--start-datetime='2021-02-24 15:45:54' /opt/mysql-bin.000002 | mysql -uroot -p123456
#查看class表的資料
mysql -uroot -p123456 -e "select * from school.class;"

總結：斷點恢復
如果恢復某條SQL陳述句之前的所有資料，就stop在這個陳述句的位置節點或者時間點
如果恢復某條SQ陳述句以及之后的所有資料，就從這個陳述句的位置節點或者時間點start

mysqlbinlog --no-defaults --start-position='449' --stop-position='806' /opt/mysql-bin.000002 | mysql -uroot -p #恢復位置從449到806之間的資料