Mysqldump邏輯備份恢復與binglog日志恢復

前言

環境：cetos7
資料庫：Mysql7.4

Mysqldump簡介：

1. mysqldump 是 MySQL 自帶的邏輯備份工具，
   它的備份原理是通過協議連接到 MySQL 資料庫，將需要備份的資料查詢出來，將查詢出的資料轉換成對應的insert 陳述句，當我們需要還原這些資料時，只要執行這些 insert 陳述句，即可將對應的資料還原，
2. 語法：
   遠程庫： mysqldump -h 服務器 -u資料庫用戶名 -p資料庫密碼 要備份的資料庫名 > 備份檔案.sql
   本地庫： mysqldump -u資料庫用戶名 -p資料庫密碼 要備份的資料庫名 > 備份檔案.sql
3. 命令引數：

-A, --all-databases #備份所有庫

-B, --databases  #備份多個資料庫

-F, --flush-logs #備份之前重繪binlog日志

-set, --default-character #指定匯出資料時采用何種字符集，如果資料表不是采用默認的latin1字符集的話，那么匯出時必須指定該選項，否則再次匯入資料后將產生亂碼問題，

-d, --no-data， #不匯出任何資料，只匯出資料庫表結構，

-f, --force #即使在一個表匯出期間得到一個SQL錯誤，繼續，

--lock-tables #備份前，鎖定所有資料庫表 #此項默認資料庫是開啟的，備份時默認鎖表，

--single-transaction # 此項解鎖 ，資料庫備份時不鎖表，保證資料的一致性和服務的可用性 

備份資料庫

1. 備份所有庫
mysqldump -u資料庫用戶名 -p'資料庫密碼' -A > /opt/$(date +%F).sql # 設定備份檔案名為當前系統時間

2. 備份指定庫
mysqldump -u資料庫用戶名 -p'資料庫密碼' -B 庫名1 庫名2 庫名3.... > /opt/$(date +%F).sql 

恢復備份庫

恢復停止binlog日志，應為這等于是在執行sql陳述句，無需記錄日志
1. 恢復所有庫
mysql -u資料庫名稱 -p'資料庫密碼'  < /備份的庫檔案.sql
2. 恢復指定庫
步驟一.
創建一個新庫，create database new； 假設創建一個new的新庫，
步驟二.
mysql -u資料庫名稱 -p'資料庫密碼' new < /備份的庫檔案.sql

備份資料表

1. 備份指定表
mysqldump -u資料庫用戶名 -p'資料庫密碼'  庫名 表名 > /備份檔案.sql

2. 備份多個表
mysqldump -u資料庫用戶名 -p'資料庫密碼'  庫名 表名1 表名2...  > /備份檔案.sql

3. 只備份表結構
mysqldump -u資料庫用戶名 -p'資料庫密碼' -d 庫名 表名 > /備份檔案.sql

4. 只匯出表資料
步驟一.
 mysql> show variables like "secure_file_priv";  ----查詢匯入匯出的目錄，可修改，修改完要在
 /etc/my.cnf 里[mysqld] 追加secure_file_priv=/新的路徑，然后重啟，
步驟二.
登陸資料查看資料
mysql> show databases;    #找到test庫
mysql> use test    #進入test庫
mysql> select * from t3 into outfile '/sql/test.t3.bak'; #匯出t3表的資料
步驟三.
資料的匯入
mysql> delete from t3;   #先將原來表里面的資料清除掉，保證是空表，只保留表結構
mysql> load data infile '/sql/test.t3.bak' into table t3;
如果將資料匯入別的表，需要創建這個表并創建相應的表結構，

恢復資料表

方法一. 
mysql -u資料庫用戶名 -p'資料庫密碼'  新建一個庫  < 備份的庫.sql

方法二.
創建一個庫
create table nwe;
進入庫
use nwe;
執行
mysql> source 備份的庫;  -------加路徑和備份的檔案 

通過binlog日志恢復

vim /etc/my.cnf                   #修改組態檔，開啟資料庫binlog日志
log-bin=/var/log/sql-bin/mylog
server-id=1

[root@mysql-server ~]# mkdir /var/log/sql-bin    #創建日志目錄
[root@mysql-server ~]# chown mysql.mysql /var/log/sql-bin  #給予日志目錄mysql權限
[root@mysql-server ~]# systemctl restart mysqld     # 重啟資料庫開啟日志
   mysql> flush logs;                          # 重繪binlog日志會截斷產生新的日志檔案

[root@mysql-server sql-bin]# mysqlbinlog mylog.000001  #查看日志檔案 at1  log_pos 20 為結束

恢復 日志中at1 到log_pos 20 的操作
[root@mysql-server sql-bin]# mysqlbinlog --start-position 1 --stop-position 20 mylog.000001 | mysql -u資料庫用戶名 -p'資料庫密碼'


在資料庫中 binlog日志的其他操作：
 1.查看所有binlog日志串列
mysql> show master logs;

2.查看master狀態，即最后(最新)一個binlog日志的編號名稱，及其最后一個操作事件pos結束點(Position)值
mysql> show master status;

3.重繪log日志，自此刻開始產生一個新編號的binlog日志檔案
mysql> flush logs;
注：每當mysqld服務重啟時，會自動執行此命令，重繪binlog日志；在mysqldump備份資料時加 -F 選項也會重繪binlog日志；

4.重置(清空)所有binlog日志
mysql> reset master;

5.查詢第一個(最早)的binlog日志：
mysql> show binlog events;

6.指定查詢 mysql-bin.000002 這個檔案：
mysql> show binlog events in 'mysql-bin.000002';

7.指定查詢 mysql-bin.000002 這個檔案，從pos點:8224開始查起：
mysql> show binlog events in 'mysql-bin.000002' from 8224;

8.指定查詢 mysql-bin.000002 這個檔案，從pos點:8224開始查起，查詢10條
mysql> show binlog events in 'mysql-bin.000002' from 8224 limit 10;

9.指定查詢 mysql-bin.000002 這個檔案，從pos點:8224開始查起，偏移2行，查詢10條
mysql> show binlog events in 'mysql-bin.000002' from 8224 limit 2,10\G;

======================================================================================================================================================================================================================================================================================================
辛苦瀏覽觀看，如果對你有幫助，請順手點個贊吧 (σﾟ?ﾟ)σ…:*☆