MySQL完全、增量的備份與恢復

數據庫備份的重要性

1. 提高系統的高可用性和災難可恢復性，在數據庫系統崩潰時，沒有數據備份就沒法找到數據。
2. 使用數據庫備份還原數據庫，是數據庫崩潰時提供數據恢復最小代價的最優方案。
3. 沒有數據庫就沒有一切，數據庫備份是一種防范災難的強力手段。

數據庫備份的分類

1. 物理備份：指對數據庫操作系統的物理文件（例如數據文件、日志文件等）的備份。物理備份游客分為脫機備份（冷備份）和聯機備份（熱備份）。
   冷備份：在關閉數據庫時進行的備份操作，能夠較好地保證數據庫的完整性。
   熱備份：在數據庫運行狀態中進行操作，這種備份方法依賴于數據庫的日志文件。
2. 邏輯備份：指對數據庫邏輯組件（如表等數據庫對象）的備份。從數據庫的備份策略角度，備份可分為完全備份、差異備份和增量備份。
   完全備份：每次對數據庫進行完整的備份。可以備份整個數據庫，包含用戶表、系統表、索引、視圖和存儲過程等所有數據庫對象。
   差異備份：備份那些自從上次完全備份之后被修改過的文件，只備份數據庫的部分內容。
   增量備份:只有那些在上次完全備份或者增量備份后被修改的文件才會被備份。

完全備份（在linux模式中進行備份操作）

完全物理備份

使用tar打包文件夾備份

• 數據庫壓縮時會使用壓縮率較大的xz格式壓縮。

  rpm -q xz    //檢查xz軟件包是否安裝
  yum -y install xz   //安裝xz軟件包

• 對數據庫文件夾（/usr/local/mysql/data/）進行打包操作

  tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/
       //備份數據庫文件夾，并以年月日的形式命名
  ls /opt/
  mysql-2018-07-02.tar.xz

  

• 對數據庫文件進行恢復操作
  只需將數據庫文件的壓縮包，解壓縮即可，即使整個/data/目錄都丟失仍可找回

  mkdir /abc
  tar Jxf mysql-2018-07-03.tar.xz -C /abc/

  

  完全邏輯備份

  使用mysqldump工具備份

• 備份某個數據庫

  mysqldump -u root -p stady > /opt/stady.sql
  //備份stady庫到/opt目錄下，-p之后可跟密碼，也可不寫。不寫就在回車之后輸入密碼驗證。 **備份文件后綴都為.sql,前面名稱見名知意即可**

  

• 查看備份文件的內容
  
  1. 備份多個數據庫

     mysqldump -u root -p --databases mysql stady > /opt/mysql-stady.sql

     

  2. 備份所有數據庫

     mysqldump -u root -p --opt --all-databases > /opt//all.sql

  3. 備份數據庫中的表

     mysqldump -u root -p stady info > /opt/stady-info.sql   //險些庫名，再接表名

  4. 備份數據庫的表結構

     mysqldump -u root -p -d stady info > /opt/decribe-info.sql
     // -d  庫名+表名

     數據恢復

     登陸MySQL，使用source命令恢復

• 恢復”備份數據庫中的表“步驟中的info表

  [root@centos7-6 /]# mysql -u root -p 
  Enter password: 
  ···   //省略部分內容
  mysql> use stady;    //切換到庫
  Database changed
  mysql> drop table info;   //刪除info表
  Query OK, 0 rows affected (0.02 sec)
  mysql> show tables;       //查看庫中表
  Empty set (0.00 sec)       //庫中為空
  mysql> source /opt/stady-info.sql   //恢復info表，格式是source+備份文件路徑和文件名稱
  Query OK, 0 rows affected (0.02 sec)
  ···   //省略部分內容
  mysql> show tables;    //查看庫中表
  +-----------------+
  | Tables_in_stady |
  +-----------------+
  | info            |   //info表已經成功恢復
  +-----------------+
  1 row in set (0.00 sec)

• 使用mysql命令恢復表

  [root@centos7-6 /]# mysql -u root -p 
  Enter password: 
  ···   //省略部分內容
  mysql> use stady;    //切換到庫
  Database changed
  mysql> drop table info;   //刪除info表
  Query OK, 0 rows affected (0.02 sec)
  mysql> show tables;       //查看庫中表
  Empty set (0.00 sec)       //庫中為空
  mysql> quit               //退出數據庫，切換到linux
  Bye
  [root@centos7-6 /]# mysql -u root -p stady < /opt/stady-info.sql   //在linux環境中恢復
  Enter password: 
  [root@centos7-6 /]# mysql -u root -p   //進入數據庫查看
  Enter password: 
  ···   //省略部分內容
  mysql> use stady;
  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> show tables;
  +-----------------+
  | Tables_in_stady |
  +-----------------+
  | info            |   //info表已恢復
  +-----------------+
  1 row in set (0.00 sec)

  恢復庫和恢復表的方式相同，但在恢復數據表時要注意，如果備份文件中只備份了表文件，此時mysql中又沒有庫時，若直接恢復，會失敗，必須先創建庫，然后再進行恢復。

  增量備份

  在MySQL數據庫中創建stady庫，創建info表，并插入數據

  mysql> use stady;
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  +----------+-------+
  2 rows in set (0.00 sec)

• 開啟二進制日志

  vim /etc/my.cnf
  log-bin=mysql-bin   //在[mysqld]段插入該語句
  systemctl restart mysqld.service   //重啟mysql服務,會在/data/目錄下生成新的日志文件（空文件）

  
  

• 使用mysqldump備份stady庫

  mkdir /backup
  mysqldump -u root -p stady > /backup/stady.sql
  mysqladmin -u root -p flush-logs  //刷新生成新的日志文件，存在01中，新存在的02為空文件

  

• 插入新數據，生成新的增量日志文件

  mysql> insert into info (name,score) values ('chen',90);//進入數據庫，向表中插入數據
  mysqladmin -u root -p flush-logs   //刷新生成新的增量文件，增量備份保存在02中
  mysql> insert into info (name,score) values ('chen01',96);//再次進入數據庫，向表中插入數據
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  | chen     | 90.00 |
  | chen01   | 96.00 |
  +----------+-------+
  4 rows in set (0.00 sec)
  mysqladmin -u root -p flush-logs //再次刷新，增量備份保存在03中

  

  增量恢復

• 模擬數據丟失，刪除新添加的兩條記錄

  [root@centos7-6 data]# mysql -u root -p 
  Enter password: 
  mysql> use stady;
  Database changed
  mysql> delete from info where name='chen';
  Query OK, 1 row affected (0.00 sec)
  mysql> delete from info where name='chen01';
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  +----------+-------+
  2 rows in set (0.00 sec)

• 使用mysqlbinlog命令，恢復數據

  [root@centos7-6 data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
  Enter password: //輸入密碼確認恢復數據
  [root@centos7-6 data]# mysql -u root -p 
  Enter password:   //輸入密碼登陸系統
  mysql> use stady;
  Database changed
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  | chen     | 90.00 |//增量備份的02數據成功恢復
  +----------+-------+
  3 rows in set (0.00 sec)

  使用mysqlbinlog查看增量備份文件的內容

  mysqlbinlog --no-defaults --base64-output-decode-rows -v /usr/local/mysql/data/mysql-bin.000002
  //--base64-output=decode-rows使用64位編碼機制解碼，按行讀取 -v 顯示出來
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  ···    //省略部分內容
  # at 345
  #180703 21:12:11 server id 1  end_log_pos 389 CRC32 0xb0bc2cb1  Write_rows: table id 118 flags: STMT_END_F
  ### INSERT INTO `stady`.`info`
  ### SET
  ###   @1='chen'
  ###   @2=90.00
  ···//省略部分內容

  在開啟二進制日志之后就會在/data/目錄下生成空的mysql-bin.000001文件，在使用該命令mysqladmin -u root -p flush-logs 后會將增量備份寫入mysql-bin.000001，同時生成一個新的mysql-bin.000002空文件，等待下次刷新寫入，同時再創建mysql-bin.000003……

  增量斷點恢復

  模擬環境

  模擬插入數據出現誤操作，在插入兩條數據的同時，刪除了一條正確的數據。

  [root@centos7-6 data]# mysql -u root -p 
  Enter password: 
  mysql> use stady;
  Database changed
  mysql> insert into info (name,score) values ('test01',88);
  Query OK, 1 row affected (0.00 sec)
  mysql> delete from info where name='chen';
  Query OK, 1 row affected (0.00 sec)
  mysql> insert into info (name,score) values ('test02',88);
  Query OK, 1 row affected (0.01 sec)
  mysql> select * from info;
  +----------+-------+
  | name     | score |
  +----------+-------+
  | zhangsan | 80.00 |
  | lisi     | 90.00 |
  | test01   | 88.00 |
  | test02   | 88.00 |
  +----------+-------+
  4 rows in set (0.00 sec)    //插入了兩條test01和test02，但是chen被操作刪除
  mysql> quit   //退出數據庫
  Bye
  [root@centos7-6 data]# mysqladmin -u root -p flush-logs  //刷新生成新的增量文件，保存在04中
  Enter password: 

  增量時間節點恢復

• 解碼導出增量備份文件，便于查看和做恢復

  mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /backup/info.txt
  cd /backup

  記錄錯誤操作的開始時間，以及正確操作的開始時間，恢復時跳過該時段的操作。
  

• 根據時間點恢復被誤操作的數據

  mysql> drop table info;   //刪除被誤操作的表
  mysql -u root -p stady < /backup/stady.sql  //恢復完全備份的stady庫
  mysqlbinlog --no-defaults --stop-datetime='2018-07-03 21:57:08' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p  //恢復誤操作前的內容
  mysqlbinlog --no-defaults --start-datetime='2018-07-03 21:57:14' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢復誤操作之后的內容

• 根據位置標記號恢復數據
  

  mysql> drop table info;   //刪除被誤操作的表
  mysql -u root -p stady < /backup/stady.sql  //恢復完全備份的stady庫
  mysqlbinlog --no-defaults --stop-position='1408' /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p
  mysqlbinlog --no-defaults --start-position='1674' /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p

  數據完成恢復