一、binlog日志恢複
MySQL的二進制日志記錄着該資料庫所有增删改的記錄檔(前提是需要自己開啟binlog),還包括了這些操作的執行時間,binlog的使用場景無外乎就是主從同步以及恢複資料庫。開啟binlog功能,需要編輯MySQL的主配置檔案,如下:
1、檢視二進制功能是否開啟(如下,值為OFF,則表示未開啟):
mysql>show VARIABLES like ‘log_bin‘;
2、開啟二進制日志功能:
[[email protected] ~]# vim /etc/my.cnf #在mysqld字段下寫入下面配置,以便開啟二進制日志并指定二進制檔案名
#開啟二進制日志,需要指定server-id,否則服務将會啟動失敗
log-bin=/usr/local/mysql/data/bin_log
server-id=1
[[email protected] ~]# systemctl restart mysqld
#重新開機後,将在指定的目錄下生成兩個檔案,如下:
[[email protected] data]# pwd
/usr/local/mysql/data
[[email protected] data]# ls | grep bin_log
bin_log.000001 #每次重新開機mysql服務或執行flush logs指令,都會生成一個新的這樣的檔案,依次為000001、000002......
bin_log.index #這個檔案存儲所有二進制檔案的索引
開啟二進制日志功能後,所有增删改的操作都會記錄到二進制日志檔案當中,注意,是增删改的操作,不包括查操作。
3、确定二進制日志功能已開啟
mysql>show variables like ‘log_bin‘;
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL4cDN2IzN5MTNzITM4AjNwAjMwIzLcZDMwIDMy8CXvZmbp9CXt92YuUGZvNWatFWbuU2Zh1Wavw1LcpDc0RHaiojIsJye.png)
4、執行增删改以便測試bin_log是否有記錄
mysql> reset master; #清空所有的二進制檔案,從00001開始
#建立一個庫,并在庫中建立一個表
mysql> create database test1;
mysql> use test1;
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
#向表中插入兩條資料
mysql> insert into tb1(name) values(‘zhangsan‘);
mysql> insert into tb1(name) values(‘lisi‘);
#重新開始一個新的日志檔案再執行操作。注意,此時上面所有的操作寫入的是第一個二進制日志檔案
mysql> flush logs;
mysql> delete from tb1 where name=‘lisi‘; #删除插入的第二條資料
mysql> insert into tb1(name) values(‘tom‘); #再插入一條新的資料
#以上的操作是寫入了第二個日志檔案
5、MySQL中檢視二進制日志檔案及檔案内容
檢視二進制日志檔案:
mysql>show binary logs;
檢視二進制日志檔案内容:
完整的指令格式如下:
SHOW BINLOG EVENTS[IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]
# in:指定要檢視的二進制檔案;
# from:指定從哪個“pos”位置開始檢視
# limit:限制傳回的行數,offset是指跳過多少行再顯示
注:如果不指定二進制檔案名,那麼預設顯示第一個二進制日志檔案中的事件,檔案内容中包含了日志檔案名、事件的開始位置、事件類型、結束位置、資訊等内容。
mysql>show binlog events in ‘bin_log.000001‘;
其他指令:
show master logs:也是檢視二進制日志檔案;
PURGE BINARY LOGS:用于删除二進制檔案;
.
例子:
PURGE BINARY LOGS TO ‘mysql-bin.00010‘; #把這個檔案之前的其他檔案都删除掉
PURGE BINARY LOGS BEFORE ‘2016-08-28 22:46:26‘; #把指定時間之前的二進制檔案删除了
6、使用mysqlbinlog本地檢視二進制日志内容
[[email protected] data]# pwd
/usr/local/mysql/data
[[email protected] data]# mysqlbinlog bin_log.000001
#使用-v選項可以檢視出日志檔案中的詳細資訊,兩個v可以檢視出更詳細的資訊,但是三個vvv也不會有什麼作用了
[[email protected] data]# mysqlbinlog -v bin_log.000001
[[email protected] data]# mysqlbinlog -vv bin_log.000001
7、通過二進制日志恢複資料
假設在開始删除lisi記錄的那條sql語句是誤操作,現在要通過二進制日志來恢複資料。
1)首先需要找到删除lisi記錄的sql語句在二進制日志中的位置,每條sql語句都是一個事務,是以需要從其begin到commit,才算是完整的sql語句。如下:
[[email protected] data]# mysqlbinlog -v bin_log.000002 | tail -35
從上面可以看出,delete事件發生position是387,事件結束是650。
2)事件恢複流程:直接用bin-log日志将資料庫恢複到删除位置219前,然後跳過故障點,再進行恢複下面所有的操作,具體恢複流程如下:
導出相關binlog檔案(将二進制檔案轉換為sql語句生成新的檔案):
[[email protected] data]# mysqlbinlog bin_log.000001 > /tmp/01.sql
[[email protected] data]# mysqlbinlog --stop-position=387 bin_log.000002 > /tmp/387.sql
[[email protected] data]# mysqlbinlog --start-position=650 bin_log.000002 > /tmp/650.sql
上述指令中,第一條比較好了解,無非就是使用msyqlbinlog檢視第一個二進制檔案,并生成新檔案,後面兩條指令呢,--stop-postition意思是檢視時到219這個位置不檢視,一直到393才又開始接着檢視。最後的結果就是新生成的檔案中不會包含删除lisi記錄的sql語句。
3)删除資料庫
mysql> drop database test1;
4)利用binlog恢複資料
[[email protected] data]# mysql -uroot -p123 < /tmp/01.sql
[[email protected] data]# mysql -uroot -p123 < /tmp/387.sql
[[email protected] data]# mysql -uroot -p123 < /tmp/650.sql
5)确定資料已恢複
mysql> select schema();
mysql> select * from tb1;
二、mysqldump備份工具
mysqldump是mysql用于備份和資料轉移的一個工具。主要産生一系列的SQL語句,可以封裝到檔案,該檔案包含有所有重建資料庫所需要的 SQL指令,如CREATE DATABASE,CREATE TABLE,INSERT等等。可以用來實作輕量級的快速遷移或恢複資料庫。 mysqldump 是将資料表導成 SQL 腳本檔案,在不同的 MySQL 版本之間更新時相對比較合适,這也是最常用的備份方法。 mysqldump一般在資料量很小的時候(幾個G)可以用于備份。當資料量比較大的情況下,就不建議用mysqldump工具進行備份了。
mysqldump可以針對單個表、多個表、單個資料庫、多個資料庫、所有資料庫進行導出的操作。
mysqldump使用示例:
1、備份某一個表
[[email protected] backup]# mysqldump -uroot -p123 mysql user > mysql-user.sql #備份mysql庫中的user表
[[email protected] backup]# ls
mysql-user.sql
2、恢複mysql資料庫中的user表
[[email protected] backup]# mysql -uroot -p123 mysql < mysql-user.sql
3、備份mysql庫
[[email protected] backup]# mysql -uroot -p123 --databases mysql > mysql.sql
[[email protected] backup]# ls
mysql.sql mysql-user.sql
4、恢複mysql庫
[[email protected] backup]# mysql -uroot -p123 < mysql.sql
5、備份所有的庫(當導出的資料量較大時,可以添加“--opt”選項以優化執行速度)
[[email protected] backup]# mysqldump -uroot -p123 --opt --all-databases > all-data.sql
[[email protected] backup]# ls
all-data.sql