一.Mysql日志分類

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

vim /etc/my.cnf

1.錯誤日志

說明：
在對應的資料目錄中，以主機名+.err命名的檔案，錯誤日志記錄的資訊型別：

• 記錄了服務器運行中產生的錯誤資訊
• 記錄了服務在啟動和停止是所產生的資訊
• 在從服務器上如果啟動了復制行程的時候，復制行程的資訊也會被記錄
• 記錄event錯誤日志

#指定日志的保存位置和檔案名
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.二進制日志（bin log）

說明：

• 用來記錄所有當MySQL啟動、停止或運行時發送的錯誤資訊，
• 默認開啟，精確的記錄了用戶對資料庫中的資料進行操作的命令和操作的資料物件，

二進制日志檔案的作用：

• 提供了增量備份的功能，
• 提供了資料基于時間點的恢復，這個恢復的時間點可以由用戶控制，
• 為mysql的復制架構提供基礎，將這主服務器的二進制日志復制到從服務器上并執行同樣的操作，就可將資料進行同步，

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	            #設定超過5秒執行的陳述句被記錄，預設時為10秒，

二.查看日志是否開啟

1.查看通用日志是否開啟

show variables like 'general%';

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

 show variables like 'log_bin%';

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

show variables like '%slow%';

（1）查看慢查詢時間

 show variables like 'long_query_time';

三.日志備份

1.日志備份的重要性

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

1.程式錯誤
2.認為操作錯誤
3.運算錯誤
4.磁盤故障
5.災難（如火災、地震等不可抗力元素）和盜竊

2.備份分類

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

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

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

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

（2.）從資料庫備份策略角度，備份可分為

1.完全備份：每次對資料庫進行完整的備份
2.差異備份：備份自從上次完全備份之后被修改過的檔案
3.增量備份：只要在上次完全備份或者增量備份后被修改的檔案才會被備份

（3.）常用備份方法

• 物理冷備份

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

• 專用備份工具

1.mysqldump常用的邏輯備份工具
- MySQL自帶的備份工具，可實作對MySQL的備份
- 可以將指定的庫、表匯出為SQL腳本
- 使用命令mysql匯入備份的資料
2.mysqlhotcopy僅擁有備份myisam和archive表

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

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

• 第三工具備份

免費的MySQL熱備份軟體Percona XtraBackup

• 完全備份

1.是對整個資料庫、資料庫結構和檔案結構的備份
2.保存的是備份完成時刻的資料庫
3.是差異備份與增量備份的基礎
優點：備份與恢復操作簡單方便
缺點：1.資料存在大量的重復
2.占用大量的備份空間
3.備份恢復時間長

四.Mysql完全備份與恢復

1.實驗

create database LJ;                        #創建LJ庫
use LJ;                                    #進入LJ庫
create table if not exists LJR1 (          #如果沒有LJR1的表則創建，有則不創建
id int(4) not null auto_increment,         #id最大顯示長度為4位，不為空，自動遞增
name varchar(10) not null,                 #name可變長度欄位最多不能超過10個字符，不為空
sex char(10) not null,                     #sex固定欄位為10個字符，不為空
hobby varchar(50),                         #hobby可變長度欄位最多不能超過20個字符
primary key (id));                         #主鍵為id

insert into LJR1 values(1,'liwei','male','running');       #在LJR1表中插入新的資料，id=1，name=liwei，sex=male，hobby=running
insert into LJR1 values(2,'xiaohao','female','singing');   #在LJR1表中插入新的資料，id=2，name=xiaohao，sex=male，hobby=singing

2.完全備份

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

(1.)物理冷備份與恢復

1.systemctl stop mysqld                                               #關閉資料庫
2.yum -y install xz
3.tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/   #壓縮備份
4.tar Jxvf /opt/mysql_all_2020-2-10.tar.xz -C /usr/local/mysql/data  #解壓恢復
5.systemctl restart mysql                                             #重啟資料庫

(2.)資料庫備份

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

mysqldump -u root -p[密碼] --databases 庫名1 [庫名2] … > /備份路徑/備份檔案名.sql #匯出的就是資料庫腳本檔案
示列：
mysqldump -uroot -p123456 --databases LJ > /opt/LJ.sql                       #完全備份完整的LJ庫到/opt目錄下檔案名為LJ.sql
mysqldump -uroot -p123456 --databases mysql LJ > /opt/mysql-LJ.sql           #備份多個完整的LJ庫到/opt目錄下檔案名為mysql-LJ.sql

2.完全備份服務器中的所有庫

mysqldump -u root -p[密碼] --all-databases > /備份路徑/備份檔案名.sql
示列：
mysqldump -u root -p123456 --all-databases > /opt/all.sql     #備份所有的庫到/opt目錄，檔案名為all.sql

3.完全備份指定庫中的部分表

mysqldump -u root -p[密碼] 庫名 [表名1] [表名2] … > /備份路徑/備份檔案名.sql
示列：
mysqldump -uroot -p123456 LJ LJR1 > 	/opt/LJ_LJR1.sql   #備份LJ庫里的LJR1表到/opt目錄，檔案名為LJ_LJR1.sql
#使用“-d”選項，說明只保存資料庫的表結構
#不使用“-d”選項，說明表資料也進行備份

4.查看備份檔案

grep -v "^--" /opt/[備份檔案名] | grep -v "^/" | grep -v "^$"  #過濾--開頭、/開頭和空格

(3.)資料庫恢復

1.恢復資料庫

mysql -uroot -p123456 -e 'drop database LJ;'    #洗掉LJ庫
mysql -uroot -p123456 -e 'SHOW DATABASES;'      #查看所有的庫
mysql -uroot -p123456 < /opt/LJ.sql           #恢復資料庫，將備份檔案注入資料庫中

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

mysql -uroot -p123456 -e 'drop table  LJ.LJR1;'#洗掉LJ庫中的LJR1表
mysql -uroot -p123456 -e 'show tables from LJ;'  #查看LJ庫中的表
mysql -uroot -p123456 LJ < /opt/LJ_LJR1.sql  #恢復資料表，將備份檔案注入資料庫中

五.Mysql增量備份

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
systemctl restart mysqld       
ls -l /usr/local/mysql/data/mysql-bin.*

2.可每周對資料庫或表進行完全備份

mysqldump -uroot -p123456 LJ LJR1 > /opt/LJ_LJR1_$(date +%F).sql      
mysqldump -uroot -p123456 --all-databases LJ > /opt/LJ_$(date +%F).sql

3.可每天進行增量備份操作

可以生成新的二進制日志檔案(例如 mysql-bin.000002)

mysqladmin -uroot -p123456 flush-logs  #可生成新的二進制檔案

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

mysql -uroot -p123456                                     #進入資料庫
use LJ;                                                   #進入LJ庫
insert into LJR1 values(3,'yuanyuan','female','game');    #插入新的資料記錄id=3，name=yuanyuan，sex=femalehobby=game
insert into LJR1 values(4,'wenlin','male','reading');     #插入新的資料記錄id=4，name=wenlin，sex=male，hobby=reading
 select * from LJ;                                        #查看LJR1標的資料

六.Mysql恢復

1.一般恢復

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

mysql -uroot -p123456         #進入資料庫
use LJ;                       #進入LJ庫
delete from LJR1 where id=3;  #洗掉id=3的資料記錄
delete from LJR1 where id=4;  #洗掉id=4的資料記錄
select * from LJR1;           #查看LJR1表的所有資料記錄
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123456  #日志恢復，將二進制日志檔案注入資料庫
select * from LJ.LJR1;        #再次查看LJR1表中的所有資料記錄

（2.）模擬丟失所有資料的恢復步驟

2.斷點恢復

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

（1.）基于位置恢復

1.mysqlbinlog --no-defaults --stop-position='611' /opt/mysql-bin.000002 | mysql -uroot -p123456       #僅恢復到操作 ID 為“611”之前的資料，即不恢復“user4”的資料
2.mysqlbinlog --no-defaults --start-position='611' /opt/mysql-bin.0.00002 | mysql -uroot -p123456     #僅恢復‘user4’的資料，跳過‘user3’的資料恢復

（2.）基于時間恢復

mysqlbinlog --no-defaults --stop-datetime='2021-2-10 12:19:15' /opt/mysql-bin.000002 |mysql -uroot -p123456        #僅恢復到 12:19:15 之前的資料，即不恢復“user4”的資料
mysqlbinlog --no-defaults --start-datetime='2021-2-10 12:19:15' /opt/mysql-bin.000002 |mysql -uroot -p123456       #僅恢復‘user4’的資料，跳過‘user3’的資料恢復