Mysql大數(shù)據(jù)備份及恢復
【引自攀巖人生的博客】MySQL備份一般采取全庫備份、日志備份;MySQL出現(xiàn)故障后可以使用全備份和日志備份將數(shù)據(jù)恢復到最后一個二進制日志備份前的任意位置或時間;mysql的二進制日志記錄著該數(shù)據(jù)庫的所有增刪改的操作日志還包括了這些操作的執(zhí)行時間
Binlog的用途：主從同步、恢復數(shù)據(jù)庫
使用binlog工具備份
查看binlog是否開啟，因為默認是關閉的
從上圖可知off為關閉狀態(tài)，一般logbin為只讀，在/etc/my.cnf下開啟
重啟數(shù)據(jù)庫
重啟后在目錄下查看是否生成bin日志，黃色表示為日志
創(chuàng)建數(shù)據(jù)庫，表，數(shù)據(jù)
創(chuàng)建表的時候 auto_increment為自增
重新開始一個新的日志文件
flush logs; #重新生成新的二進制文件
delete from tb1 where id=2; #刪除id列中的序列號為2的數(shù)據(jù)方便測試
insert into tb1(name) values('tom'); #創(chuàng)建一個新的數(shù)據(jù)名為tom
查看數(shù)據(jù)
查看mysql上的二進制文件日志
查看二進制日志事件
- mysql> show binlog events;
- +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
- +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
- | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver: 4 |
- | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
- | mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql-bin.000001 | 219 | Query | 1 | 316 | create database test1 |
- | mysql-bin.000001 | 316 | Anonymous_Gtid | 1 | 381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql-bin.000001 | 381 | Query | 1 | 525 | use `test1`; create table tb1(id int primary key auto_increment,name varchar(20)) |
- | mysql-bin.000001 | 525 | Anonymous_Gtid | 1 | 590 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql-bin.000001 | 590 | Query | 1 | 663 | BEGIN |
- | mysql-bin.000001 | 663 | Table_map | 1 | 713 | table_id: 108 (test1.tb1) |
- | mysql-bin.000001 | 713 | Write_rows | 1 | 758 | table_id: 108 flags: STMT_END_F |
- | mysql-bin.000001 | 758 | Xid | 1 | 789 | COMMIT /* xid=10 */ |
- | mysql-bin.000001 | 789 | Anonymous_Gtid | 1 | 854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql-bin.000001 | 854 | Query | 1 | 927 | BEGIN |
- | mysql-bin.000001 | 927 | Table_map | 1 | 977 | table_id: 108 (test1.tb1) |
- | mysql-bin.000001 | 977 | Write_rows | 1 | 1026 | table_id: 108 flags: STMT_END_F |
- | mysql-bin.000001 | 1026 | Xid | 1 | 1057 | COMMIT /* xid=11 */ |
- | mysql-bin.000001 | 1057 | Rotate | 1 | 1104 | mysql-bin.000002;pos=4 | # 此處為日志輪換事件，執(zhí)行flush logs引起的
- +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
- 17 rows in set (0.00 sec)
查看指定二進制日志的事件(上面那個命令只能查看默認1的)
- mysql> show binlog events in 'mysql-bin.000002';
- +------------------+-----+----------------+-----------+-------------+---------------------------------------+
- | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
- +------------------+-----+----------------+-----------+-------------+---------------------------------------+
- | mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver: 4 |
- | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
- | mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql-bin.000002 | 219 | Query | 1 | 292 | BEGIN |
- | mysql-bin.000002 | 292 | Table_map | 1 | 342 | table_id: 108 (test1.tb1) |
- | mysql-bin.000002 | 342 | Delete_rows | 1 | 391 | table_id: 108 flags: STMT_END_F |
- | mysql-bin.000002 | 391 | Xid | 1 | 422 | COMMIT /* xid=14 */ |
- | mysql-bin.000002 | 422 | Anonymous_Gtid | 1 | 487 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
- | mysql-bin.000002 | 487 | Query | 1 | 560 | BEGIN |
- | mysql-bin.000002 | 560 | Table_map | 1 | 610 | table_id: 108 (test1.tb1) |
- | mysql-bin.000002 | 610 | Write_rows | 1 | 654 | table_id: 108 flags: STMT_END_F |
- | mysql-bin.000002 | 654 | Xid | 1 | 685 | COMMIT /* xid=15 */ |
- +------------------+-----+----------------+-----------+-------------+---------------------------------------+
- 12 rows in set (0.00 sec)
使用mysqlbinlog工具的-v(--verbose)選項，該選項會將行事件重構成被注釋掉的偽SQL語句，如果想看到更詳細的信息可以將該選項給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內容，如
先切換到binlog所在的目錄下
mysqlbinlog mysql-bin.000001
mysqlbinlog -v mysql-bin.000001
mysqlbinlog -vv mysql-bin.000001
-h，-P，-p，-u等，這些參數(shù)僅在指定了--read-from-remote-server后有效。
恢復導出binlog日志
恢復完成后查看表的數(shù)據(jù)是否完整
常見的選項有：
--start-datetime #從二進制日志中讀取指定時間戳或者本地計算機時間之后的日志事件
--stop-datetime #從二進制日志中讀取指定時間戳或者本地計算機時間之前的日志事件
--start-position #從二進制日志中讀取指定position 事件位置作為開始
--stop-position #從二進制日志中讀取指定position 事件位置作為事件截至
因為備份得時候把刪除那項跳過了，所以id為2的zhangsan也恢復了
使用mysqldump備份恢復
它是用于備份和數(shù)據(jù)遷移的工具，一般在數(shù)據(jù)量比較小的情況下使用如幾個G，當數(shù)據(jù)比較大的情況下建議不使用;mysqldump可以對單(多)個表、單(多)個數(shù)據(jù)庫及所有數(shù)據(jù)庫進行導出操作;
- mysqldump [options] db_name [tbl_name ...] #導出指定數(shù)據(jù)庫或單個表
- mysqldump [options] --databases db_name ... #導出多個數(shù)據(jù)庫
- mysqldump [options] --all-databases #導出所有
備份數(shù)據(jù)庫test1
- mysqldump -p123456 --flush-logs test1 > /opt/test1.spl
- #備份整個數(shù)據(jù)庫，并且重新開啟一個新的binlog
- mysql -p123456 test1 </opt/test1.spl #從備份的目錄中導入
創(chuàng)建數(shù)據(jù)測試mysqldump工具
查看數(shù)據(jù)
創(chuàng)建備份的目錄及備份數(shù)據(jù)庫和cp bin.000001二進制文件
清楚二進制00000.2之前的日志
刪除數(shù)據(jù)測試
然后在進行備份第二個二進制文件
進行數(shù)據(jù)庫恢復
查看表的數(shù)據(jù)是否恢復
上面數(shù)據(jù)已經全部恢復 希望能幫到你
