真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

mysql大數(shù)據(jù)備份及恢復(fù)(一)

  MySQL大數(shù)據(jù)備份和恢復(fù)一

十年的長泰網(wǎng)站建設(shè)經(jīng)驗(yàn),針對設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對一服務(wù),響應(yīng)快,48小時(shí)及時(shí)工作處理。全網(wǎng)營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同,自動(dòng)調(diào)整長泰建站的顯示方式,使網(wǎng)站能夠適用不同顯示終端,在瀏覽器中調(diào)整網(wǎng)站的寬度,無論在任何一種瀏覽器上瀏覽網(wǎng)站,都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì),從而大程度地提升瀏覽體驗(yàn)。創(chuàng)新互聯(lián)公司從事“長泰網(wǎng)站設(shè)計(jì)”,“長泰網(wǎng)站推廣”以來,每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

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)閉的

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

從上圖可知off為關(guān)閉狀態(tài),一般logbin為只讀,在/etc/my.cnf下開啟

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

 

重啟數(shù)據(jù)庫
mysql大數(shù)據(jù)備份及恢復(fù)(一)

重啟后在目錄下查看是否生成bin日志,×××表示為日志

創(chuàng)建數(shù)據(jù)庫,表,數(shù)據(jù)

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

創(chuàng)建表的時(shí)候 auto_increment為自增

重新開始一個(gè)新的日志文件

flush logs;                            #重新生成新的二進(jìn)制文件     

delete from tb1 where id=2;             #刪除id列中的序列號為2的數(shù)據(jù)方便測試

insert into tb1(name) values('tom');       #創(chuàng)建一個(gè)新的數(shù)據(jù)名為tom

查看數(shù)據(jù)

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

查看mysql上的二進(jìn)制文件日志

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

查看二進(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日志

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

恢復(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 事件位置作為事件截至

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

因?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

創(chuàng)建數(shù)據(jù)測試mysqldump工具

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

查看數(shù)據(jù)

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

創(chuàng)建備份的目錄及備份數(shù)據(jù)庫和cp  bin.000001二進(jìn)制文件

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

清楚二進(jìn)制00000.2之前的日志

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

刪除數(shù)據(jù)測試

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

然后在進(jìn)行備份第二個(gè)二進(jìn)制文件

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

進(jìn)行數(shù)據(jù)庫恢復(fù)

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

mysql大數(shù)據(jù)備份及恢復(fù)(一) 

查看表的數(shù)據(jù)是否恢復(fù)

mysql大數(shù)據(jù)備份及恢復(fù)(一)

上面數(shù)據(jù)已經(jīng)全部恢復(fù) 希望能幫到你


文章名稱:mysql大數(shù)據(jù)備份及恢復(fù)(一)
URL分享:http://weahome.cn/article/jpgojs.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220(工作日)

18980820575(7×24)

提交需求

返回頂部