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

標籤：pre   密碼   delete   rop   備份還原   位置   word   column   form   

Database Backup的重要性

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

Database Backup的分類

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 /abctar 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表

  [[email protected] /]# mysql -u root -p Enter password: ···   //省略部分內容mysql> use stady;    //切換到庫Database changedmysql> 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命令恢複表

  [[email protected] /]# mysql -u root -p Enter password: ···   //省略部分內容mysql> use stady;    //切換到庫Database changedmysql> drop table info;   //刪除info表Query OK, 0 rows affected (0.02 sec)mysql> show tables;       //查看庫中表Empty set (0.00 sec)       //庫中為空白mysql> quit               //退出資料庫，切換到linuxBye[[email protected] /]# mysql -u root -p stady < /opt/stady-info.sql   //在linux環境中恢複Enter password: [[email protected] /]# mysql -u root -p   //進入資料庫查看Enter password: ···   //省略部分內容mysql> use stady;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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.cnflog-bin=mysql-bin   //在[mysqld]段插入該語句systemctl restart mysqld.service   //重啟mysql服務,會在/data/目錄下產生新的記錄檔（空檔案）

  
  

• 使用mysqldump備份stady庫

  mkdir /backupmysqldump -u root -p stady > /backup/stady.sqlmysqladmin -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中

  增量恢複
• 類比資料丟失，刪除新添加的兩條記錄

  [[email protected] data]# mysql -u root -p Enter password: mysql> use stady;Database changedmysql> 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命令，恢複資料

  [[email protected] data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -pEnter password: //輸入密碼確認恢複資料[[email protected] data]# mysql -u root -p Enter password:   //輸入密碼登陸系統mysql> use stady;Database changedmysql> 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……

  增量斷點恢複類比環境

  類比插入資料出現誤操作，在插入兩條資料的同時，刪除了一條正確的資料。

  [[email protected] data]# mysql -u root -p Enter password: mysql> use stady;Database changedmysql> 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[[email protected] data]# mysqladmin -u root -p flush-logs  //重新整理產生新的增量檔案，儲存在04中Enter password: 

  增量時間節點恢複
• 解碼匯出增量備份檔案，便於查看和做恢複

  mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /backup/info.txtcd /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 -pmysqlbinlog --no-defaults --start-position=‘1674‘ /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p

  資料完成恢複

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