一，為什么要做資料備份？

• 在生產環境中，資料的安全性非常重要，任何資料的丟失都可能會產生嚴重的后果，
• 造成資料丟失的原因：
  程式錯誤
  人為操作失誤
  運算錯誤
  磁盤故障
  災難
  等等…
• 所以說備份資料庫是非常重要的，對咱們的資料安全性也就能大大的提高

二，備份的分類

• 物理備份
  對資料庫作業系統的物理檔案（資料檔案，日志檔案等…）備份
  物理備份方法：

冷備份（脫機備份）：需要關閉資料庫，才能進行
熱備份（聯機備份）：資料庫處于運行狀態，依賴資料庫的日志檔案
溫備份：資料庫鎖定表格（不可寫入但可讀）的狀態下進行備份操作

• 邏輯備份：對資料庫邏輯組件（資料庫中的表…）的備份

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

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

三，常見的備份方法

1.物理冷備份
tar命令

2.專用備份工具
mysqldump
mysqlhotcopy

3.二進制日志

4.第三方工具備份
Percona XtraBackup
Xtrabackup、innobackupex、xbstream

四，mysql完全備份操作

1.物理備份與恢復

備份：
 systemctl  stop mysqld                                     #先關閉資料庫服務
 mkdir /bakup                                                    #創建備份目錄
 tar zcvf /bakup/mysql_all-$(date +%F).tar.gz  /usr/local/mysql/data/       #壓縮目錄備份
 cd /backup/                                                        #查看備份


模擬故障：
mkdir /bak                                                         #創建目錄模擬故障
 mv /usr/local/mysql/data/  /bak                        #移動資料檔案目錄
cd /usr/local/mysql/                                           #查看模擬故障是否成功
 ll

恢復：
mkdir /restore                                                                              #創建恢復存放目錄
tar zxvf /bakup/mysql_all-2020-09-13.tar.gz  -C  /restore/          #將之前壓縮備份的目錄，解壓到指定的新建恢復存放目錄
 mv /restore/usr/local/mysql/data/  /usr/local/mysql/                   #將解壓的資料檔案目錄data，移動到mysql下
systemctl  start  mysqld                                                               #啟動服務
systemctl status mysqld                                                               #查看資料庫開啟正常
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-09-13 06:03:42 EDT; 3h 56min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 9819 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 9801 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 9822 (mysqld)

2.mysqldump備份和恢復

mysqldump 可以將指定的庫，表，或全部的庫匯出為sql腳本；

備份的幾種操作：

• mysqldump備份需要和mysql進行資料互動，如果關閉mysql 則無法備份和恢復
• mysqldump -u 用戶 -p [選項] 庫名 [表名1] [表名2] … > /備份路徑/備份檔案名
  mysqldump -u 用戶 -p [選項] --databases 庫名1 [庫名2] … > /備份路徑/備份檔案名
• mysqldump -u 用戶 -p [選項] --all-databases > /備份路徑/備份檔案名

示例：
mysqldump -u root -p mysql user >mysql-user.sql #備份資料路mysql中的user表 ，檔案保存在當前操作目錄下面
Enter password: ###輸入密碼abc123
mysqldump -u root -p --databases auth >auth.sql #備份auth資料庫 檔案保存在當前操作目錄下面
Enter password: ####輸入密碼abc123

操作：
準備環境：
創建資料庫—創建表—表內增加點資料

1.create database wow;

2.use wow;

3.create talbe lol(name CHAR(16) NOT NULL,password CHAR(48) 
DEFAULT'',primary key (name));

4.mysql> insert into lol(name,password) 
    -> values
    -> ('xiaohong',password('123456'));
    
5.mysql> insert into lol values ('lisi',password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)

對資料庫的表備份：
 mysqldump -u root -p  wow  lol >lol.sql                             #對資料庫中的lol表做備份
Enter password:                                                                    #輸入密碼


恢復：
mysql -u root -p uou < lol.sql                                              #uou是之前存在的空庫，將備份表lol匯入到uou庫中；
Enter password: 

mysql -u root -p -e 'show tables from uou'                           #查看資料表lol是否匯入成功
Enter password: 
+---------------+
| Tables_in_uou |
+---------------+
| lol           |
| user          |
+---------------+



對資料庫做備份：
mysqldump -u root -p --databases wow >wow.sql
Enter password: 


模擬資料庫故障：
[root@localhost ~]# mysql -u root -p -e 'drop database wow'        #將庫wow洗掉，模擬故障（高危操作哦！！！！）
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'show databases'            #查看下是否被洗掉
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| uou                |
+--------------------+

恢復：
[root@localhost ~]# mysql -u root -p < wow.sql                          #將庫wow匯入資料庫中
Enter password: 

[root@localhost ~]# mysql -u root -p -e 'show databases'          #查看下資料庫中的庫
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| uou                |
| wow                |
+--------------------+

五，增量備份和恢復

1、增量備份的特點

與完全備份完全不同，增量備份沒有重復資料，備份量不大，時間短；但其恢復麻煩，需要上次完成完全備份及備份之后的所有增量備份才能恢復，

2、MySQL資料庫二進制日志對備份的意義

二進制日志保存了所有更新或者可能更新資料的操作，二進制日志在啟動mysql服務器后開始記錄，并在檔案達到二進制日志所設定的最大值
或者接受到flush logs命令后重新創建新的日志檔案，生成二進制的檔案序列，并及時把這些日志檔案保存到安全的存盤位置，即可完成一個時間段
的增量備份

方法:

• 一般恢復： （一般丟什么資料，找什么資料）
  mysqlbinlog [–no-defaults] 增量備份檔案 | mysql -u 用戶名 -p

• 基于位置恢復：
  1、恢復資料到指定位置
  mysqlbinlog --stop-position=’操作 id’ 二進制日志 |mysql -u 用戶名 -p 密碼

2、從指定的位置開始恢復資料
mysqlbinlog --start-position=’操作 id’ 二進制日志 |mysql -u 用戶名 -p 密碼

• 基于時間恢復：
  1、從日志開頭截止到某個時間點的恢復
  mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小時:分鐘:秒’ 二進制日志 | mysql -u 用戶名 -p 密碼

2、從某個時間點到日志結尾的恢復
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小時:分鐘:秒’ 二進制日志 | mysql -u 用戶名 -p 密碼

3、從某個時間點到某個時間點的恢復
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小時:分鐘:秒’ --stop-datetime=’年-月-日小時:分鐘:秒’ 二進制日志 | mysql -u 用戶名 -p 密碼

一般恢復：

準備環境：
生成二進制檔案--常見庫--表
1.vi /etc/my.cnf  在[mysqld]配置內，添加
log-bin=/usr/local/mysql/data/mysql-bin 
2.systemctl  restart mysqld      #重啟服務
3.ll  /usr/local/mysql/data            #查看時候有二進制檔案日志生成
....省略部分...
-rw-r-----. 1 mysql mysql      154 Sep 13 11:50 mysql-bin.000001
-rw-r-----. 1 mysql mysql       39 Sep 13 11:50 mysql-bin.index
....省略部分...

在這里插入代碼片創建測驗環境：
mysql> create database cl;
Query OK, 1 row affected (0.01 sec)

mysql> use cl;
Database changed
create table cls(工號 char(20) not null,姓名 char(10) not null,工資 char(6) not null,部門 char(5));
Query OK, 0 rows affected (0.01 sec)

insert into cls values ('001','熏無空','18888',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cls values ('002','沙屋靜','16666',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cls values ('003','竹巴結','14444',3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from cls;
+--------+-----------+--------+--------+
| 工號   | 姓名      | 工資   | 部門   |
+--------+-----------+--------+--------+
| 001    | 熏無空    | 18888  | 1      |
| 002    | 沙屋靜    | 16666  | 2      |
| 003    | 竹巴結    | 14444  | 3      |
+--------+-----------+--------+--------+
3 rows in set (0.00 sec)

mysql> show master logs;                  #查看是否生成二進制日志檔案，記錄著操作
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1802 |
+------------------+-----------+
1 row in set (0.00 sec)

進行一次完全備份：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

 mysqldump -u root -p cl cls >/mysql-bak/cls-$(date +%F).sql
Enter password: 

ll /mysql-bak/
total 4
-rw-r--r--. 1 root root 1952 Sep 13 12:22 cls-2020-09-13.sql

 mysqladmin -u root -p flush-logs
Enter password: 

 ll /usr/local/mysql/data/
...省略部分...
-rw-r-----. 1 mysql mysql     1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql      154 Sep 13 12:24 mysql-bin.000002
-rw-r-----. 1 mysql mysql       78 Sep 13 12:24 mysql-bin.index
...省略部分...

錄入新的內容并進行一次增量備份：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> use cl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into cls values ('004','滕森','10000',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into cls values ('005','金嬌','10000',5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from cls;
+--------+-----------+--------+--------+
| 工號   | 姓名      | 工資   | 部門   |
+--------+-----------+--------+--------+
| 001    | 熏無空    | 18888  | 1      |
| 002    | 沙屋靜    | 16666  | 2      |
| 003    | 竹巴結    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
| 005    | 金嬌      | 10000  | 5      |
+--------+-----------+--------+--------+
5 rows in set (0.00 sec)


mysqladmin -u root -p flush-logs;            #再次查看二進制日志檔案，看操作是否被記錄生成檔案
Enter password: 

ll /usr/local/mysql/data/
...省略部分...
-rw-r-----. 1 mysql mysql     1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql      753 Sep 13 12:30 mysql-bin.000002
-rw-r-----. 1 mysql mysql      154 Sep 13 12:30 mysql-bin.000003
-rw-r-----. 1 mysql mysql      117 Sep 13 12:30 mysql-bin.index
...省略部分...

 cp /usr/local/mysql/data/mysql-bin.000002  /mysql-bak/          #將二進制日志檔案復制到咱們創建的備份目錄下



模擬失誤操作洗掉cls表；
 mysql -u root -p -e 'drop table cl.cls'
Enter password: 

mysql -u root -p -e 'select * from cl.cls'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'cl.cls' doesn't exist


恢復操作：（完全備份恢復）
mysql -u root -p cl < /mysql-bak/cls-2020-09-13.sql 
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工號   | 姓名      | 工資   | 部門   |
+--------+-----------+--------+--------+
| 001    | 熏無空    | 18888  | 1      |
| 002    | 沙屋靜    | 16666  | 2      |
| 003    | 竹巴結    | 14444  | 3      |
+--------+-----------+--------+--------+

增量備份恢復：
 mysqlbinlog --no-defaults /mysql-bak/mysql-bin.000002 |mysql -u root -p Enter password: 


 mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工號   | 姓名      | 工資   | 部門   |
+--------+-----------+--------+--------+
| 001    | 熏無空    | 18888  | 1      |
| 002    | 沙屋靜    | 16666  | 2      |
| 003    | 竹巴結    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
| 005    | 金嬌      | 10000  | 5      |
+--------+-----------+--------+--------+

基于位置恢復：

模擬洗掉表cls：
洗掉表cls---恢復下完全備份cl.2020.9.13.sql--查看二進制日志檔案內想要恢復的位置

mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002              ####--base64-output=decode-rows    64解碼    ###  -v   換行顯示

.........省略部分...........
#at 289
#200913 12:27:59 server id 1  end_log_pos 344 CRC32 0x25b12913  Table_map: `cl`.`cls` mapped to number 225
#at 344
#200913 12:27:59 server id 1  end_log_pos 399 CRC32 0xbd609658  Write_rows: table id 225 flags: STMT_END_F
### INSERT INTO `cl`.`cls`
### SET
###   @1='004'
###   @2='滕森'
###   @3='10000'
###   @4='4'
# at 399
#200913 12:27:59 server id 1  end_log_pos 430 CRC32 0x3561ca6a  Xid = 74
COMMIT/*!*/;
# at 565
#200913 12:28:26 server id 1  end_log_pos 620 CRC32 0x5e09ca65  Table_map: `cl`.`cls` mapped to number 225
# at 620
#200913 12:28:26 server id 1  end_log_pos 675 CRC32 0x9a65af81  Write_rows: table id 225 flags: STMT_END_F
### INSERT INTO `cl`.`cls`
### SET
###   @1='005'
###   @2='金嬌'
###   @3='10000'
###   @4='5'
# at 675
#200913 12:28:26 server id 1  end_log_pos 706 CRC32 0x510d9e90  Xid = 75
COMMIT/*!*/;
.........省略部分...........

咱們只想恢復 滕森的資料！操作如下：

從檔案可以得知，at289- at565  這位置范圍是滕森

 mysqlbinlog  --no-defaults --stop-position='430' /mysql-bak/mysql-bin.000002 |mysql -u root -p       #位置在操作節點附近即可
Enter password: 
[root@localhost ~]# mysql -u root -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工號   | 姓名      | 工資   | 部門   |
+--------+-----------+--------+--------+
| 001    | 熏無空    | 18888  | 1      |
| 002    | 沙屋靜    | 16666  | 2      |
| 003    | 竹巴結    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
+--------+-----------+--------+--------+

另一種操作方法：start 跟上面差不多操作

注意： 基于位置恢復的操作，節點不要選擇太靠近操作的節點，否則容易恢復失敗，

基于時間戳恢復

比如咱們通過時間戳恢復金嬌的資料
20200913 12:28:26 # 日志檔案中金嬌所在的時間戳

 mysqlbinlog --no-defaults --start-datetime='2020-09-13 12:28:26' /mysql-bak/mysql-bin.000002  |mysql -u root -p 
Enter password: 

[root@localhost ~]# mysql -u root -p -e 'select * from cl.cls'
Enter password: 
+--------+-----------+--------+--------+
| 工號   | 姓名      | 工資   | 部門   |
+--------+-----------+--------+--------+
| 001    | 熏無空    | 18888  | 1      |
| 002    | 沙屋靜    | 16666  | 2      |
| 003    | 竹巴結    | 14444  | 3      |
| 004    | 滕森      | 10000  | 4      |
| 005    | 金嬌      | 10000  | 5      |
+--------+-----------+--------+--------+