資料庫的備份與恢複 mysqldump+binlog方式

實驗說明：

（1）mysqldump：邏輯備份工具，适用于所有存儲引擎，可用于溫備，能實作完全備份，部分備份；對InnoDB存儲引擎支援熱備；

        binlog：二進制日志

        二進制日志也叫作變更日志，主要用于記錄修改資料或有可能引起資料改變的mysql語句，并且記錄了語句發生時間、執行時長、操作的資料等等。是以說通過二進制日志可以查詢mysql資料庫中進行了哪些變化。一般大小體積上限為1G

（2）本次實驗使用備份方式:完全備份+增量備份。

（3）musqldump指令的文法格式

mysqldump [OPTIONS] database [tables]：備份單個庫，或庫指定的一個或多個表

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：備份一個或多個庫

mysqldump [OPTIONS] --all-databases [OPTIONS]：備份所有庫

    其他選項

        -x, --lock-all-tables：鎖定所有表

        -l, --lock-tables：鎖定備份的表

        --single-transaction：啟動一個大的單一事務實作備份

        -C, --compress：壓縮傳輸

        -E, --events：備份指定庫的事件排程器

        -R, --routines：備份存儲過程和存儲函數

        --triggers：備份觸發器

        --master-data={0|1|2}

            0：不記錄

            1：記錄CHANGE MASTER TO語句；此語句未被注釋

            2：記錄為注釋語句

            -F，--flush-logs：鎖定表之後執行flush logs指令

1、安裝資料庫

    centos6：  yum  install mysql

    centos7：  yum  install mariadb

2、開啟二進制日志

      在配置檔案修改

        vim  /etc/my.cnf

        [mysqld]   

         log-bin=bin-log  添加改行，采用相對路徑，真實存儲路徑為/var/lib/mysql/下

3、啟動服務

        systemctl start mariadb

        ls /var/lib/mysql

        會看到bin-log.000001 檔案

        每次重新開機mysql服務或運作mysql> flush logs;都會生成一個新的二進制日志檔案，這些日志檔案的number會不斷地遞增。除了生成上述的檔案外還會生成一個為filename.index（bin-log.index）的檔案。這個檔案中存儲所有二進制日志檔案的清單又稱為二進制檔案的索引。

4、登入資料庫并建立需要備份的資料庫

        mysql -uroot -p

   （1）建立資料庫

        create database mariadb  

   （2）建立表

        use mariadb

        create table table1(id int ,name char(20));

        show tables；檢視是否建立表成功

   （3）插入資料

           insert into table1  values (1,'one');

            insert into table1  values (2,'two');

            insert into table1  values (3,'three');

           insert into table1  values (4,'for'); 

            檢視是否插入資料到表中

            select * from table1

5、進行完整備份

    （1）建立要備份的目錄

            mkdir /backup

      （2）備份

       mysqldump --databases mariadb --lock-all-tables --lock-tables  --flush-log --master-data=2 >/backup/`date +%F-%T`-all.sql    

      （3） 檢視是否備份成功

            ls  /backup

6、向表中插入資料，進行增量備份

     （1）檢視 目前使用的二進制檔案及所處位置

            show master status； 

    （2）插入資料

        insert into table1 values (5,five);

7、增量備份

    （1）檢視二進制日志

        mysqlbinlog /var/lib/mysql/bin-log.000002

        選擇備份的位置

    插入資料前，即完全備份的位置在245

    插入資料後，位置在418

   說明：整個事務一般以BEGIN開頭到COMMIT結束，注意一定要看清，很容易出錯。

（2）進行增量備份

        mysqlbinlog --start-position=245 --stop-position=418 /var/lib/mysql/bin-log.000002 > /backup/`date +%F-%T`-add.sql

    ls /backup

 8、繼續插入資料，在沒備份的情況下删除資料庫，模拟誤操作

     inset into table1 values （6，'six'）;

     drop  database mariadb;

9、不要慌張

10、備份未備份的操作

    由于最後我們沒有備份就删除了資料庫，是以我們首先需要保護最後的二進制日志，檢視删除操作之前的position值

    mysqlbinlog /var/lib/mysql/bin-log.000002

   是以備份位置從結束位置在643

     mysqlbinlog  --start-position=445  --stop-position=643 /var/lib/mysql/bin-log.000002 > /backup/`date +%F-%T`-add.sql

    一定不要備份誤删除的位置，不然恢複後，又被删除，就恢複不了。

11、恢複資料庫

    mysql < /backup/2017-11-16-14\:24\:31-all.sql 先恢複完全備份

    mysql < /backup/2017-11-16-14\:53\:33-add.sql  恢複第一個增量備份

    mysql < /backup/2017-11-16-15\:13\:23-add.sql  恢複最後備份

12、登入資料庫檢視

   資料恢複成功。資料位置為完全正确。

本文轉自 hawapple 51CTO部落格，原文連結:http://blog.51cto.com/guanm/1982422