Mysql大數(shù)據(jù)備份及恢復(fù)
【引自攀巖人生的博客】MySQL備份一般采取全庫備份、日志備份;MySQL出現(xiàn)故障后可以使用全備份和日志備份將數(shù)據(jù)恢復(fù)到最后一個(gè)二進(jìn)制日志備份前的任意位置或時(shí)間;mysql的二進(jìn)制日志記錄著該數(shù)據(jù)庫的所有增刪改的操作日志還包括了這些操作的執(zhí)行時(shí)間
Binlog的用途：主從同步、恢復(fù)數(shù)據(jù)庫
使用binlog工具備份
查看binlog是否開啟，因?yàn)槟J(rèn)是關(guān)閉的
從上圖可知off為關(guān)閉狀態(tài)，一般logbin為只讀，在/etc/my.cnf下開啟
重啟數(shù)據(jù)庫
重啟后在目錄下查看是否生成bin日志，黃色表示為日志
創(chuàng)建數(shù)據(jù)庫，表，數(shù)據(jù)
創(chuàng)建表的時(shí)候 auto_increment為自增
重新開始一個(gè)新的日志文件
flush logs; #重新生成新的二進(jìn)制文件
delete from tb1 where id=2; #刪除id列中的序列號(hào)為2的數(shù)據(jù)方便測試
insert into tb1(name) values('tom'); #創(chuàng)建一個(gè)新的數(shù)據(jù)名為tom
查看數(shù)據(jù)
查看mysql上的二進(jìn)制文件日志
查看二進(jìn)制日志事件
- 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)
查看指定二進(jìn)制日志的事件(上面那個(gè)命令只能查看默認(rèn)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)選項(xiàng)，該選項(xiàng)會(huì)將行事件重構(gòu)成被注釋掉的偽SQL語句，如果想看到更詳細(xì)的信息可以將該選項(xiàng)給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內(nèi)容，如
先切換到binlog所在的目錄下
mysqlbinlog mysql-bin.000001
mysqlbinlog -v mysql-bin.000001
mysqlbinlog -vv mysql-bin.000001
-h，-P，-p，-u等，這些參數(shù)僅在指定了--read-from-remote-server后有效。
恢復(fù)導(dǎo)出binlog日志
恢復(fù)完成后查看表的數(shù)據(jù)是否完整
常見的選項(xiàng)有：
--start-datetime #從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之后的日志事件
--stop-datetime #從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之前的日志事件
--start-position #從二進(jìn)制日志中讀取指定position 事件位置作為開始
--stop-position #從二進(jìn)制日志中讀取指定position 事件位置作為事件截至
因?yàn)閭浞莸脮r(shí)候把刪除那項(xiàng)跳過了，所以id為2的zhangsan也恢復(fù)了
使用mysqldump備份恢復(fù)
它是用于備份和數(shù)據(jù)遷移的工具，一般在數(shù)據(jù)量比較小的情況下使用如幾個(gè)G，當(dāng)數(shù)據(jù)比較大的情況下建議不使用;mysqldump可以對單(多)個(gè)表、單(多)個(gè)數(shù)據(jù)庫及所有數(shù)據(jù)庫進(jìn)行導(dǎo)出操作;
- mysqldump [options] db_name [tbl_name ...] #導(dǎo)出指定數(shù)據(jù)庫或單個(gè)表
- mysqldump [options] --databases db_name ... #導(dǎo)出多個(gè)數(shù)據(jù)庫
- mysqldump [options] --all-databases #導(dǎo)出所有
備份數(shù)據(jù)庫test1
- mysqldump -p123456 --flush-logs test1 > /opt/test1.spl
- #備份整個(gè)數(shù)據(jù)庫，并且重新開啟一個(gè)新的binlog
- mysql -p123456 test1 </opt/test1.spl #從備份的目錄中導(dǎo)入
創(chuàng)建數(shù)據(jù)測試mysqldump工具
查看數(shù)據(jù)
創(chuàng)建備份的目錄及備份數(shù)據(jù)庫和cp bin.000001二進(jìn)制文件
清楚二進(jìn)制00000.2之前的日志
刪除數(shù)據(jù)測試
然后在進(jìn)行備份第二個(gè)二進(jìn)制文件
進(jìn)行數(shù)據(jù)庫恢復(fù)
查看表的數(shù)據(jù)是否恢復(fù)
上面數(shù)據(jù)已經(jīng)全部恢復(fù) 希望能幫到你
