一、mysql常用日志

1、概述

• 日志檔案在資料庫進行備份和恢復時起到了很重要的作用
• 常用的日志檔案默認保存在 /usr/local/mysql/data 目錄下
• 可在 /etc/my.cnf 組態檔中的 [mysqld] 中進行日志的路徑修改、開啟、關閉等操作

①、錯誤日志

• 用于記錄 mysql 啟動、停止或運行時產生的錯誤資訊
• 可通過一下欄位進行更新
  • log-error=/usr/local/mysql/data/mysql_error.log （指定日志的保存位置和檔案名）

②、二進制日志

• 二進制日志，用來記錄所有更新的資料或者已經潛在更新了資料的陳述句，記錄了資料的更改，可用于資料恢復
• 開啟方式：
  • log-bin=mysql-bin 或者 log_bin=mysql-bin

③、中繼日志

• 一般情況下，它在 mysql 主從同步（復制）、讀寫分離集群的從節點上才開啟，
• 主節點一般不需要這個日志，

④、慢查詢日志

• 慢查詢日志，用來記錄所有執行時間超過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秒）

2、資料庫中查詢日志狀態

①、查看二進制日志開啟狀態

show variables like '%log_bin%';

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

show variables like '%slow%';

③、查看慢查詢時間設定

show variables like 'long_query_time';

④、在資料庫中設定開啟慢查詢的辦法（臨時）

set global slow_query_log=ON;

二、備份

1、概述

• 備份的主要目的是災難恢復
• 還可以用來測驗應用、回滾資料修改、查詢歷史資料、審計等
• 在生產環境中，資料的安全性至關重要
• 任何資料的丟失都可能產生嚴重的后果

2、備份的重要性

• 在企業中，資料的價值至關重要，資料保障了企業業務的正常運行，
• 因此，資料的安全性及資料的可靠性是運維的重中之重，任何資料的吊事都可能對企業產生嚴重的后果，
• 通常情況下，造成資料丟失的原因有一下幾種：
  • 程式錯誤
  • 人為操作錯誤
  • 運算錯誤
  • 磁盤故障
  • 災難（火災、地震、盜竊等）

3、備份型別

①、從物理與邏輯的角度分類（邏輯備份、物理備份）

• 資料庫備份可以分為物理備份和邏輯備份，
• 邏輯備份是對資料庫邏輯組件(如表等資料庫物件)的備份,表輯資料庫結構(CREATE DATABASE, CREATETABLEia)和內容(INSERT陳述句或分隔文本檔案)的資訊，
• 邏輯備份適用于可以編輯資料值或表結構較小的資料量,或者在不同的機器體系結構上重新創建資料
• 物理備份是對資料庫作業系統的物理檔案(如資料檔案、日志檔案等)的備份，
• 物理備份適用于在出現問題時需要快速恢復的大型重要資料庫，
• 物理備份又可以分為冷備份(脫機備份)、熱備份(聯機備份)和溫備份，
  • 冷備份:在資料庫關閉狀態下進行備份操作，（tar）
  • 熱備份:在資料庫處于運行狀態時進行備份操作,該備份方法依賴資料庫的日志檔案，（mysqldump）
  • 溫備份:資料庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作，

②、從資料庫的備份策略角度分類（完全備份、差異備份、增量備份）

• 從資料庫的備份策略角度，資料庫的備份可分為完全備份、差異備份和增量備份(面試點)，
  • 完全備份：每次對資料進行完整的備份，即對整個資料庫、資料庫結構和檔案結構的備份，保存的是備份完成時刻的資料庫，是差異備份與增量備份的基礎，完全備份的備份與恢復操作都非常簡單方便，但是資料存在大量的重復，并且會占用大量的磁盤空間，備份的時間也很長，
  • 差異備份：備份那些自從上次完全備份之后被修改過的所有檔案，備份的時間節點是從上次完整備份起，備份資料量會越來越大，恢復資料時，只需恢復上次的完全備份與最近的一次差異備份，
  • 增量備份：只有那些在上次完全備份或者增量備份后被修改的檔案才會被備份，以上次完整備份或上次增量備份的時間為時間點，僅備份這之間的資料變化，因而備份的資料量小，占用空間小，備份速度快，但恢復時，需要從上一次的完整備份開始到最后一次增量備份之的所有增量依次恢復，如中間某次的備份資料損壞，將導致資料的丟失，

4、備份方法

• 資料庫的備份可以采用很多種方式，如直接打包資料庫檔案（物理冷備份）、專用備份工具（mysqldump）、二進制日志增量備份、第三方工具備份等

①、物理冷備份

• 物理冷備份時需要在資料庫處于關閉狀態下，能夠較好地保證資料庫的完整性，
• 物理冷備份一般用于非核心業務，這類業務一般都允許中斷，
• 物理冷備份的特點就是速度快，恢復時也是最為簡單的，
• 通常通過直接打包資料庫檔案夾(/usr/loc.al/mysql/data)來實作備份，

②、專用備份工具 mysqldump 或 mysqlhotcopy

• mysqldump程式和mysqlhotcopy都可以做備份，
• mysqldump是客戶端常用邏輯備份程式，能夠產生一組被執行以后再現原始資料庫物件定義和表資料的SQL陳述句，它可以轉儲一個到多個MySQL資料庫，對其進行備份或傳輸到遠程SQL服務器，mysqldump更為通用，因為它可以備份各種表，
• mysqlhotcopy僅適用于某些存盤引擎（MyISAM和ARCHIVE），

③、通過啟用二進制日志進行增量備份

• 支持增量備份，進行增量備份時必須啟用二進制日志，
• 二進制日志檔案為用戶提供復制，對執行備份點后進行的資料庫更改所需的資訊進行恢復，
• 如果進行增量備份(包含自上次完全備份或增量備份以來發生的資料修改) ，需要重繪二進制日志，

④、通過第三方工具備份

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

5、備份實驗

①、創建表結構、表資料

mysql -u root -p
create database meeting;
use meeting;
create table if not exists working (
id int(4) not null auto_increment,
name varchar(10) not null,
primary key (id));

insert into working values(1,'張三');
insert into working values(2,'李四');

②、完全備份

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

1)物理冷備份與恢復

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

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

解壓恢復
tar Jxvf /mnt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/data/

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

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

mysqldump -uroot -p[密碼] --databases 庫名 > /備份路徑/備份檔案名.sql
匯出的就是資料庫腳本檔案 
 
如：
mysqldump -uroot -p010230 --databases meeting > /opt/meeting.sql
mysqldump -uroot -p010230 --databases mysql meeting > /opt/mysql-meeting.sql

• 備份所有的庫

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

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

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

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

如：
mysqldump -uroot -p010230 [-d] meeting working > /opt/meeting_working.sql
#使用“-d”選項，說明只保存資料庫的表結構
#不使用“-d”選項，說明表資料也進行備份

• 查看備份檔案

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

③、恢復資料庫

先洗掉
mysql -uroot -p010230 -e 'drop database meeting;'
#“-e”選項，用于指定連接 MySQL 后執行的命令，命令執行完后自動退出

查看庫
mysql -uroot -p010230 -e 'show databases;'

進行恢復操作
mysql -uroot -p010230 < /opt/meeting.sql
mysql -uroot -p010230 -e 'show databases;'

總結：

備份的型別：

1、從物理與邏輯的角度分類

• ①、物理備份：
• 對資料庫作業系統的物理檔案（如資料檔案、日志檔案等）的備份
• 冷備份（脫機備份）： 是在關閉資料庫的時候進行的（tar）
• 熱備份（聯機備份）： 資料庫處于運行狀態，依賴于資料庫的日志檔案（mysqldump）
• 溫備份： 資料庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作
• ②、邏輯備份： 對資料庫邏輯組件（如：表等資料庫物件）的備份

2、從資料庫的備份策略角度分類

• ①、完全備份： 每次對資料庫進行完整的備份（盡量時間間隔在合適的范圍內，如一周）
• 優點：備份與恢復操作簡單方便
• 缺點：資料存在大量的重復，并且會占用大量的磁盤空間，備份的時間也很長，
• ②、差異備份： 備份自從上次完全備份之后被修改過的檔案（）
• 優點：恢復資料時，只需恢復上次的完全備份與最近的一次差異備份，
• 缺點：備份的時間節點是從上次完整備份起，備份資料量會越來越大，
• ③、增量備份： 只有在上次完全備份或者增量備份后被修改的檔案才會被備份
• 優點：以上次完整/增量備份為時間點，僅備份這之間的資料變化，備份的資料量小，占用空間小，備份速度快
• 缺點：恢復時，需要所有增量備份從頭依次恢復，如中間某次的備份資料損壞，將導致資料的丟失，

備份方法：

1、物理冷備份

特點：

• 服務關閉，保證資料庫的完整性，
• 適用于非核心業務
• 備份速度快，tar 打包 /usr/loc.al/mysql/data即可
• 恢復即為解壓覆寫 /usr/loc.al/mysql/data 目錄

2、專用備份工具 mysqldump 或 mysqlhotcopy

特點：

• mysqldump是客戶端自帶的備份工具，更為通用，因為它可以備份各種表，
• mysqlhotcopy僅適用于某些存盤引擎（MyISAM和ARCHIVE），

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

特點：

• 二進制日志檔案為用戶提供復制，對執行備份點后進行的資料庫更改所需的資訊進行恢復，
• 如果進行增量備份，需要重繪二進制日志，

4、第三方工具備份

特點：

• Percona xtraBackup是一個免費的MysQL熱備份軟體，支持在線熱備份Innodb和xtraDB，也可以支持MySQL表備份，不過MyISAM表的備份要在表鎖的情況下進行，