mysql 主從復(fù)制 基于binlog 簡單實(shí)踐

主從復(fù)制使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫服務(wù)器復(fù)制到其他服務(wù)器上唠摹，在復(fù)制數(shù)據(jù)時(shí)绵估，一個(gè)服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器（slave）卡骂。因?yàn)閺?fù)制是異步進(jìn)行的国裳，所以從服務(wù)器不需要一直連接著主服務(wù)器，從服務(wù)器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務(wù)器全跨。通過配置文件缝左，可以指定復(fù)制所有的數(shù)據(jù)庫，某個(gè)數(shù)據(jù)庫浓若，甚至是某個(gè)數(shù)據(jù)庫上的某個(gè)表渺杉。
原文地址：代碼匯個(gè)人博客 http://www.codehui.net/info/64.html

主從同步機(jī)制

Mysql服務(wù)器之間的主從同步是基于二進(jìn)制日志機(jī)制，主服務(wù)器使用二進(jìn)制日志來記錄數(shù)據(jù)庫的變動(dòng)情況七嫌，從服務(wù)器通過讀取和執(zhí)行該日志文件來保持和主服務(wù)器的數(shù)據(jù)一致少办。

代碼匯

在使用二進(jìn)制日志時(shí)，主服務(wù)器的所有操作都會(huì)被記錄下來诵原，然后從服務(wù)器會(huì)接收到該日志的一個(gè)副本英妓。從服務(wù)器可以指定執(zhí)行該日志中的哪一類事件（譬如只插入數(shù)據(jù)或者只更新數(shù)據(jù)），默認(rèn)會(huì)執(zhí)行日志中的所有語句绍赛。

每一個(gè)從服務(wù)器會(huì)記錄關(guān)于二進(jìn)制日志的信息：文件名和已經(jīng)處理過的語句蔓纠，這樣意味著不同的從服務(wù)器可以分別執(zhí)行同一個(gè)二進(jìn)制日志的不同部分，并且從服務(wù)器可以隨時(shí)連接或者中斷和服務(wù)器的連接吗蚌。

主服務(wù)器和每一個(gè)從服務(wù)器都必須配置一個(gè)唯一的ID號（在my.cnf文件的[mysqld]模塊下有一個(gè)server-id配置項(xiàng)）腿倚，另外，每一個(gè)從服務(wù)器還需要通過CHANGE MASTER TO語句來配置它要連接的主服務(wù)器的ip地址蚯妇，日志文件名稱和該日志里面的位置（這些信息存儲(chǔ)在主服務(wù)器的數(shù)據(jù)庫里）

使用主從同步的好處：

1敷燎、通過增加從服務(wù)器來提高數(shù)據(jù)庫的性能，在主服務(wù)器上執(zhí)行寫入和更新箩言，在從服務(wù)器上向外提供讀功能硬贯，可以動(dòng)態(tài)地調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個(gè)數(shù)據(jù)庫的性能陨收。
2饭豹、提高數(shù)據(jù)安全-因?yàn)閿?shù)據(jù)已復(fù)制到從服務(wù)器，從服務(wù)器可以終止復(fù)制進(jìn)程务漩，所以拄衰，可以在從服務(wù)器上備份而不破壞主服務(wù)器相應(yīng)數(shù)據(jù)
3、在主服務(wù)器上生成實(shí)時(shí)數(shù)據(jù)饵骨，而在從服務(wù)器上分析這些數(shù)據(jù)翘悉，從而提高主服務(wù)器的性能

硬件要求

主從服務(wù)器操作系統(tǒng)版本和位數(shù)一致，主從數(shù)據(jù)庫的版本要一致居触。

測試環(huán)境：centos7.6,mysql5.5,可以使用虛擬機(jī)或者docker安裝妖混，這里用的是docker環(huán)境

主服務(wù)器： 192.168.73.130
從服務(wù)器： 192.168.73.131

主服務(wù)器配置

1包吝、修改配置文件

可以通過如下命令查看mysql讀取的配置文件，順序排前的優(yōu)先

root@ba586179fe4b:/# mysql --help|grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
root@ba586179fe4b:/# vi /etc/my.cnf

首先檢查你的主服務(wù)器上的my.cnf文件中是否已經(jīng)在[mysqld]模塊下配置了log-bin和server-id

[mysqld]
# 設(shè)置server_id源葫，一般設(shè)置為IP,注意要唯一
server-id=1
# 開啟二進(jìn)制日志功能诗越，名字可以隨便取
log-bin=mysql-bin

注意上面的log-bin和server-id的值都是可以改為其他值的，如果沒有上面的配置息堂，首先關(guān)閉mysql服務(wù)器嚷狞，然后添加上去，接著重啟服務(wù)器

2荣堰、創(chuàng)建用戶床未，每一個(gè)從服務(wù)器都需要用到一個(gè)賬戶名和密碼來連接主服務(wù)器，可以為每一個(gè)從服務(wù)器都創(chuàng)建一個(gè)賬戶振坚，也可以讓全部服務(wù)器使用同一個(gè)賬戶薇搁。下面就為同一個(gè)ip網(wǎng)段的所有從服務(wù)器創(chuàng)建一個(gè)只能進(jìn)行主從同步的賬戶渡八。

首先登陸mysql屎鳍，然后創(chuàng)建一個(gè)用戶名為rep，密碼為123456的賬戶卖宠，該賬戶可以被192.168.73網(wǎng)段下的所有ip地址使用眷昆，且該賬戶只能進(jìn)行主從同步

root@ba586179fe4b:/# mysql -u root -p
...
mysql > grant replication slave on *.* to 'rep'@'192.168.73.131' identified by '123456';

3、獲取二進(jìn)制日志的信息并導(dǎo)出數(shù)據(jù)庫攘滩，步驟：

首先登陸數(shù)據(jù)庫女揭，然后刷新所有的表袍嬉，同時(shí)給數(shù)據(jù)庫加上一把鎖箍土，阻止對數(shù)據(jù)庫進(jìn)行任何的寫操作

mysql > flush tables with read lock;

然后執(zhí)行下面的語句獲取二進(jìn)制日志的信息弓柱，

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      352 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

File的值是當(dāng)前使用的二進(jìn)制日志的文件名，Position是該日志里面的位置信息，記錄二進(jìn)制文件名(mysql-bin.000001)和位置(352)，會(huì)在下面配置從服務(wù)器時(shí)用到。

這時(shí)可以對數(shù)據(jù)庫解鎖刹淌，恢復(fù)對主數(shù)據(jù)庫的操作

mysql > unlock tables;

從服務(wù)器配置

1古程、修改配置文件

root@ba586179fe4b:/# vi /etc/my.cnf

設(shè)置server-id荤懂，必須唯一粟耻，如果有多個(gè)從服務(wù)器上，那么每個(gè)服務(wù)器上配置的server-id都必須不一致谈喳。從服務(wù)器上沒必要配置log-bin册烈，當(dāng)然也可以配置log-bin選項(xiàng)，因?yàn)榭梢栽趶姆?wù)器上進(jìn)行數(shù)據(jù)備份和災(zāi)難恢復(fù)婿禽，或者某一天讓這個(gè)從服務(wù)器變成一個(gè)主服務(wù)器

[mysqld]
server-id=2

配置同步參數(shù)赏僧，登陸mysql，輸入如下信息：(以下依次是主服務(wù)器ip扭倾、主服務(wù)器mysql端口淀零、主服務(wù)器上配置過用來主從的用戶名和密碼、剛才記錄的二進(jìn)制文件名稱和位置)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.73.130',
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=352;
# 或者
mysql> CHANGE MASTER TO MASTER_HOST='192.168.73.130', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=352;

啟動(dòng)主從同步進(jìn)程

mysql > start slave;

檢查狀態(tài)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.73.130
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.0.000001
          Read_Master_Log_Pos: 352
               Relay_Log_File: aa0fcaec42a1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.0.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 352
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql>

當(dāng)Slave_IO_Running和Slave_SQL_Running都為YES的時(shí)候就表示主從同步設(shè)置成功了膛壹。但是上面Slave_IO_Running為No了驾中，說明沒有啟動(dòng)成功，網(wǎng)上搜的錯(cuò)誤原因：

1模聋、主服務(wù)器的網(wǎng)絡(luò)不通肩民，或者主服務(wù)器的防火墻拒絕了外部連接3306端口
2、在配置從服務(wù)器時(shí)链方，輸錯(cuò)了ip地址和密碼持痰，或者主服務(wù)器在創(chuàng)建用戶時(shí)寫錯(cuò)了用戶名和密碼
3、在配置從服務(wù)器時(shí)祟蚀，輸錯(cuò)了主服務(wù)器的二進(jìn)制日志信息

我們發(fā)現(xiàn)Last_IO_Error里面輸出了錯(cuò)誤信息:

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
從二進(jìn)制日志中讀取數(shù)據(jù)時(shí)工窍，來自master的致命錯(cuò)誤1236：'無法在二進(jìn)制日志索引文件中找到第一個(gè)日志文件名'

檢查發(fā)現(xiàn)從服務(wù)器配置同步參數(shù)時(shí)MASTER_LOG_FILE多寫了一個(gè)0, 這塊還是要注意的。
然后通過stop slave;關(guān)閉同步進(jìn)程重新配置前酿。
發(fā)現(xiàn)Slave_IO_Running和Slave_SQL_Running都為No了患雏，最終解決辦法:

mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

然后兩個(gè)都為Yes了,就可以測試一下效果了。

測試主從復(fù)制

主服務(wù)器操作
# 創(chuàng)建庫codehui
mysql> create database codehui; 
Query OK, 1 row affected (0.00 sec)
mysql> use codehui
Database changed

# 創(chuàng)建表demo
mysql> CREATE TABLE `demo` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

# 添加一條數(shù)據(jù)
mysql> INSERT INTO `demo` (`name`) VALUES ('代碼匯');

Query OK, 1 row affected (0.00 sec)
從服務(wù)器查看
# 查看數(shù)據(jù)庫薪者，codehui自動(dòng)生成了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| codehui            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use codehui;
Database changed

# 查看表纵苛，demo表也自動(dòng)創(chuàng)建
mysql> show tables;
+-------------------+
| Tables_in_codehui |
+-------------------+
| demo              |
+-------------------+
1 row in set (0.00 sec)

# 查看表剿涮，記錄成功
mysql> select * from demo;
+----+------+
| id | name |
+----+------+
|  1 | 111  |
+----+------+
1 row in set (0.00 sec)

到這主從復(fù)制就完成了言津。

其他相關(guān)配置

主服務(wù)器

master開啟二進(jìn)制日志后默認(rèn)記錄所有庫所有表的操作攻人，可以通過配置來指定只記錄指定的數(shù)據(jù)庫甚至指定的表的操作，具體在mysql配置文件的[mysqld]可添加修改如下選項(xiàng)：

# 不同步哪些數(shù)據(jù)庫  
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema

# 只同步哪些數(shù)據(jù)庫悬槽，除此之外怀吻，其他不同步
binlog-do-db = game
從服務(wù)器
# 停止主從同步
mysql> stop slave;

# 連接斷開時(shí)，重新連接超時(shí)時(shí)間
mysql> change master to master_connect_retry=50;

# 開啟主從同步
mysql> start slave;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末初婆，一起剝皮案震驚了整個(gè)濱河市蓬坡，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌磅叛，老刑警劉巖屑咳，帶你破解...
    沈念sama閱讀 218,036評論 6贊 506
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異弊琴，居然都是意外死亡兆龙，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,046評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門敲董，熙熙樓的掌柜王于貴愁眉苦臉地迎上來紫皇，“玉大人，你說我怎么就攤上這事腋寨〈掀蹋” “怎么了？”我有些...
    開封第一講書人閱讀 164,411評論 0贊 354
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵萄窜，是天一觀的道長铃剔。 經(jīng)常有香客問我，道長查刻，這世上最難降的妖魔是什么番宁？ 我笑而不...
    開封第一講書人閱讀 58,622評論 1贊 293
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮赖阻，結(jié)果婚禮上蝶押，老公的妹妹穿的比我還像新娘。我一直安慰自己火欧，他們只是感情好棋电，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,661評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著苇侵，像睡著了一般赶盔。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上榆浓，一...
    開封第一講書人閱讀 51,521評論 1贊 304
  • 城市分裂傳說
    那天于未，我揣著相機(jī)與錄音，去河邊找鬼。 笑死烘浦，一個(gè)胖子當(dāng)著我的面吹牛抖坪，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播闷叉，決...
    沈念sama閱讀 40,288評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼擦俐，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了握侧？” 一聲冷哼從身側(cè)響起蚯瞧，我...
    開封第一講書人閱讀 39,200評論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎品擎，沒想到半個(gè)月后埋合，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 45,644評論 1贊 314
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡萄传，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,837評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年饥悴，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片盲再。...
    茶點(diǎn)故事閱讀 39,953評論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡西设，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出答朋，到底是詐尸還是另有隱情贷揽，我是刑警寧澤，帶...
    沈念sama閱讀 35,673評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布梦碗，位于F島的核電站禽绪，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏洪规。R本人自食惡果不足惜印屁，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,281評論 3贊 329
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望斩例。 院中可真熱鬧雄人，春花似錦、人聲如沸念赶。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,889評論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽叉谜。三九已至旗吁，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間停局，已是汗流浹背很钓。 一陣腳步聲響...
    開封第一講書人閱讀 33,011評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工香府， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人码倦。 一個(gè)月前我還...
    沈念sama閱讀 48,119評論 3贊 370
  • 代替公主和親
    正文 我出身青樓企孩，卻偏偏與公主長得像，于是被迫代替她去往敵國和親叹洲。 傳聞我的和親對象是個(gè)殘疾皇子柠硕，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,901評論 2贊 355