目錄

• Mysql備份及主從同步
  • Mysql備份
    • 1. mysqldump全量備份
    • 2.log_bin增量備份
  • Mysql主從同步配置
    • 1.1主從同步過(guò)程
    • 1.2 配置

Mysql備份及主從同步

Mysql備份

1. mysqldump全量備份

1.1 mysqldump備份原理

mysqldump備份數(shù)據(jù)的過(guò)程，實(shí)際上就是把數(shù)據(jù)庫(kù)從mysql庫(kù)里以sql語(yǔ)句的形式直接輸出并保存成文件的過(guò)程,備份的文件/* 到 */都是注釋,其余都是sql語(yǔ)句，故備份恢復(fù)就相當(dāng)于把所有sql語(yǔ)句執(zhí)行一遍

1.2 msyqldump命令詳解

語(yǔ)法：mysqldump -u username -p 數(shù)據(jù)庫(kù)名 [表名]... > 文件路徑
常用選項(xiàng)：

• -A 備份所有數(shù)據(jù)庫(kù)
• -B DBNAME... #能接多個(gè)庫(kù)，并添加創(chuàng)建庫(kù)和切換庫(kù)的語(yǔ)句（使用-B備份的sql文件恢復(fù)時(shí)不需要指定庫(kù)，也不用手動(dòng)創(chuàng)建庫(kù)）
• -F 切割binlog
• --master-data=1 #1表示執(zhí)行從庫(kù)從主庫(kù)某位置的binlog恢復(fù) ，2表示注釋執(zhí)行語(yǔ)句
• -x #鎖表
• -d DBNAME #只備份表結(jié)構(gòu)
• --compact #輸出內(nèi)容減少，用于調(diào)試
• --no-create-info|-t #只備份數(shù)據(jù)
• --single-transaction # 適合innodb事務(wù)數(shù)據(jù)庫(kù)備份

1.2 mysqldump備份示例

1.備份所有庫(kù)并壓縮

mysqldump -u root -p -A > xx.sql

2.備份某個(gè)庫(kù)

mysqldump -u root -p -B DBNAME > xx.sql

3.備份多個(gè)庫(kù)

mysqldump -u root -p -B DBNAME1 DBNAME2 > xx.sql

1.3 備份恢復(fù)

mysql -uroot -p < xxx.sql

2.log_bin增量備份

2.1 開(kāi)啟log_bin功能

修改my.cnf中的[mysqld]：

#添加該選項(xiàng) [mysqld] log_bin=mysqlbin

生成的日志文件存放的是該功能開(kāi)啟后的mysql內(nèi)部增刪改等對(duì)數(shù)據(jù)庫(kù)有更新內(nèi)容的sql語(yǔ)句。

該功能開(kāi)啟前的數(shù)據(jù)并不能通過(guò)這個(gè)恢復(fù)。所以通常先是使用mysqldump全量備份，之后的數(shù)據(jù)通過(guò)log_bin生成的日志文件來(lái)恢復(fù)

若在mysqldump備份時(shí)指定了--master-data=1，會(huì)將備份數(shù)據(jù)最后的位置記錄下來(lái)，則數(shù)據(jù)恢復(fù)時(shí)將大大方便log_bin從合理的位置恢復(fù)

2.2 利用mysqlbinlog命令導(dǎo)出sql文件

mysqlbinlog mysql-bin.00001 > 1.sql #-d DB #只導(dǎo)出指定庫(kù)sql語(yǔ)句，默認(rèn)binlog會(huì)保存所有庫(kù)的操作 mysqlbinlog -d test mysql-bin.00001 > 1.sql

基于指定位置導(dǎo)出sql語(yǔ)句：

mysqlbinlog mysqlbin.00001 --start-position=106 >1.sql #導(dǎo)出從位置106開(kāi)始到文件結(jié)束的sql語(yǔ)句 mysqlbinlog mysqlbin.00001 --stop-position=500 >2.sql #導(dǎo)出從文件開(kāi)始到位置為500處的sql語(yǔ)句 mysqlbinlog mysqlbin.00001 --start-position=106 --stop-position=500 >3.sql #導(dǎo)出從位置106到500的sql語(yǔ)句

基于指定時(shí)間導(dǎo)出：

mysqlbinlog --start-datetime="2019-02-22 16:52:06" mysqlbin.000001 > 1.sql mysqlbinlog --stop-datetime="2019-02-22 16:52:06" mysqlbin.000001 > 2.sql mysqlbinlog --start-datetime="2019-02-22 16:52:06" --stop-datetime="2019-02-22 17:52:06" mysqlbin.000001 > 3.sql

2.3 備份恢復(fù)

mysql -uroot -p < 1.sql #通常先將全量備份的數(shù)據(jù)恢復(fù)，然后用msyqlbinlog導(dǎo)出的文件接著全量的恢復(fù)后面的數(shù)據(jù)

Mysql主從同步配置

1.1主從同步過(guò)程

• 1.從庫(kù)IO線程連接上主庫(kù)，并請(qǐng)求從指定日志文件的指定位置之后的日志內(nèi)容
• 2.主庫(kù)收到來(lái)自從庫(kù)的請(qǐng)求后，負(fù)責(zé)復(fù)制的IO線程根據(jù)請(qǐng)求信息讀取指定日志指定位置之后的日志內(nèi)容，并返回給從庫(kù)的IO線程
• 3.從庫(kù)接收到信息后，將接收到的內(nèi)容依次寫(xiě)到從庫(kù)的Relaylog文件的最末端，并讀取其中主庫(kù)的bin-log文件名和位置記錄到master-info文件中，以便下次讀取時(shí)能清楚的告訴主庫(kù)"我需要從bin-log文件的哪個(gè)位置開(kāi)始往后的內(nèi)容"
• 4.從庫(kù)的SQL線程檢測(cè)到Relaylog中新增的內(nèi)容后，會(huì)馬上解析該文件中的內(nèi)容，形成SQL語(yǔ)句并執(zhí)行，從而完成同步

1.2 配置

主庫(kù)配置

• 1.主庫(kù)開(kāi)啟log_bin 功能，并設(shè)置server_id

[mysqld] server_id=1111 log_bin=mysqlbin

• 2.主庫(kù)創(chuàng)建主從同步的mysql賬號(hào)，并授予replication，slave權(quán)限

mysql -e "GRANT replication slave *.* to 'repl'@'192.168.139.0/255.255.255.0' identified by 'repl'" -uroot -p

• 3.主庫(kù)整庫(kù)鎖表，然后備份已有數(shù)據(jù)生成sql文件，并拷貝至從庫(kù)服務(wù)器，完成后解鎖主庫(kù)

mysql -e " flush table with read lock " -uroot -p123456 mysqldump -uroot -p123456 -A --master-data=1 > repl.sql scp repl.sql root@192.168.139.106:~ mysql -e " unlock tables " -uroot -p123456

從庫(kù)配置

• 1.設(shè)置server_id, 從庫(kù)一般不需要開(kāi)啟log_bin功能，除非做級(jí)聯(lián)，即同時(shí)還作為其他庫(kù)的主庫(kù)

[mysqld] server_id=2222 #需要跟主庫(kù)不一致

• 2.先手動(dòng)復(fù)制主庫(kù)的數(shù)據(jù)到從庫(kù)

mysql -uroot -p123456 < repl.sql

• 3.如果主庫(kù)配置第三步中，備份數(shù)據(jù)時(shí)沒(méi)有指定--master-data=1 ，則需要在從庫(kù)中指定主庫(kù)信息

mysql -e "change master to \ master_host='192.168.139.105', \ master_log_file='mysqlbin.000004',\ master_user='repl',master_password='repl',\ master_log_pos=326 "

• 4.從庫(kù)開(kāi)啟同步開(kāi)關(guān)

mysql -uroot -p123456 -e " start slave;"

轉(zhuǎn)載于:https://www.cnblogs.com/fllf/p/10417431.html

總結(jié)

以上是生活随笔為你收集整理的Mysql数据备份恢复及主从同步的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。