mysql5.5中怎么實(shí)現(xiàn)半同步復(fù)制

本篇文章為大家展示了MySQL5.5中怎么實(shí)現(xiàn)半同步復(fù)制，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

創(chuàng)新互聯(lián)建站專注于拜城企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,商城網(wǎng)站定制開發(fā)。拜城網(wǎng)站建設(shè)公司,為拜城等地區(qū)提供建站服務(wù)。全流程按需開發(fā)網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)建站專業(yè)和態(tài)度為您提供的服務(wù)

master:192.168.70.101

slave:192.168.70.100

在master上創(chuàng)建用戶repl(只需要在master上創(chuàng)建repl用戶);

mysql>grant replication slave on *.* to 'repl'@'192.168.70.100' identified by 'repl';

在master上my.cnf配置如下：

[mysqld]
datadir=/data/dbdata
user=mysql
port=3306
innodb_data_home_dir = /data/dbdata
socket          = /usr/local/mysql/tmp/mysql.sock
server-id=2
log-bin=master-bin
log-bin-index=master-bin.index
sync_binlog = 1
binlog_format = row
character-set-server=utf8

在slave的my.cnf中配置如下：

[mysqld]
innodb_data_home_dir = /data/dbdata
socket = /usr/local/mysql/tmp/mysql.sockport=3306
user=mysql
default-storage-engine=innodb
server-id=3
read_only=on
log-bin=slave-bin
relay_log=slave-relay-bin
relay-log-index=slave-relay-bin.index
binlog_format = row
default-storage-engine=InnoDB
character-set-server=utf8

在master上產(chǎn)看，

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

在slave上使用change master to命令將slave指向master。然后使用start slave命令啟動(dòng)復(fù)制。

 mysql> change master to 　　　　

->master_host='192.168.70.101',

->master_port=3306,

->master_user='repl',

->master_password='repl',

->master_log_file='master-bin.000003',

->master_log_pos=106;

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.70.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 106
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 252
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes   　　---成功
            Slave_SQL_Running: Yes 　　 ---成功

flush logs命令強(qiáng)制輪換二進(jìn)制日志，從而可以得到完整的二進(jìn)制日志文件。

使用show binlog events in 'master-bin.000004(二進(jìn)制日志)'\G;

檢查二進(jìn)制日志里有哪些事件

mysql> show binlog events in 'master-bin.000004'\G;
*************************** 1. row ***************************
   Log_name: master-bin.000004
        Pos: 4
 Event_type: Format_desc
  Server_id: 2
End_log_pos: 106
       Info: Server ver: 5.1.56-community-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: master-bin.000004
        Pos: 106
 Event_type: Query
  Server_id: 2
End_log_pos: 200
       Info: use `test`; create table tb1(text char(10))
*************************** 3. row ***************************
   Log_name: master-bin.000004
        Pos: 200
 Event_type: Rotate
  Server_id: 2
End_log_pos: 244
       Info: master-bin.000005;pos=4
3 rows in set (0.00 sec)

//克隆MASTER

[root@server picture]# mysqldump -uroot -pmysql --host=192.168.70.101 --all-databases --master-data=1 > backup-source.sql

--master-data=1選項(xiàng)mysqldump寫change master to 語句，且參數(shù)為二進(jìn)制日志文件及其位置。

然后在slave上恢復(fù)備份：

[root@server picture]#mysql -uroot -pmysql --host=192.168.70.100 < backup-source.sql

//克隆SLAVE

//清除Binlog日志

服務(wù)器自動(dòng)清理舊的binlog文件，需設(shè)置expire-logs-days選項(xiàng)，這個(gè)選項(xiàng)可以作為服務(wù)器變量。如果服務(wù)重啟后，不受影響，需要在my.cnf設(shè)置。

使用purge binary log命令手工清除binlog文件。格式如下：

1，purge binary log before datatime

將清除在給定時(shí)間之前的所有文件。

2,purge binary logs to 'filename'

將清除在給定文件之前的所有文件。

//默認(rèn)情況下，由slave執(zhí)行的事件沒有被記錄到二進(jìn)制日志中，如果這個(gè)slave是master的一個(gè)備份，這時(shí)會(huì)出現(xiàn)問題。

在my.cnf添加log-slave-updates，以確保來自于master并被執(zhí)行的語句會(huì)被寫入slave的二進(jìn)制日志中。

切換基本思路：為了讓slave趕上備份服務(wù)器，并在正確的位置停止，使用start slave until命令。

slave>start slave until master_log_file='master-bin-000006',master_log_pos=700;

slave>select master_pos_wait('master-bin-000006',700);

//在主從服務(wù)器上配置支持半同步復(fù)制

[MASTER]
mysql>install plugin rpl_semi_sync_master soname &lsquo;semisync_master.so&rsquo;;

mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME='rpl_semi_sync_master'\G;
*************************** 1. row ***************************
           PLUGIN_NAME: rpl_semi_sync_master
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: REPLICATION
   PLUGIN_TYPE_VERSION: 1.0
        PLUGIN_LIBRARY: semisync_master.so
PLUGIN_LIBRARY_VERSION: 1.3
         PLUGIN_AUTHOR: He Zhenxing
    PLUGIN_DESCRIPTION: Semi-synchronous replication master
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.00 sec)

mysql> show variables like 'rpl_semi_sync%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

修改rpl_semi_sync_master_enabled=ON,rpl_semi_sync_master_timeout單位時(shí)間為毫秒。

mysql> set global rpl_semi_sync_master_enabled = on;    
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'rpl_semi_sync%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql> show global status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
下面的變量?jī)H僅在主服務(wù)的半復(fù)制插件初始化后才有效，參數(shù)解釋如下：
Rpl_semi_sync_master_clients：表示半復(fù)制從服務(wù)器的數(shù)量。
Rpl_semi_sync_master_net_avg_wait_time：主服務(wù)器等候從服務(wù)器的平均毫秒時(shí)間。(單位為毫秒)
Rpl_semi_sync_master_net_waits：主服務(wù)器等候從服務(wù)器的總毫秒時(shí)間。(單位為毫秒)
Rpl_semi_sync_master_no_times：主服務(wù)器關(guān)閉半復(fù)制的次數(shù)。
Rpl_semi_sync_master_no_tx：從服務(wù)器提交事物后沒有收到ack確認(rèn)成功的次數(shù)。
Rpl_semi_sync_master_status：決定半服務(wù)器是否可以在主服務(wù)器上操作，設(shè)置為ON表示開啟，OFF表示關(guān)閉，為異步模式。
Rpl_semi_sync_master_timefunc_failures:主服務(wù)器調(diào)用時(shí)間函數(shù)失敗的次數(shù)，例如gettimeofday()。
Rpl_semi_sync_master_tx_avg_wait_time：主服務(wù)器等候每個(gè)事物的平均時(shí)間(毫秒)。
Rpl_semi_sync_master_tx_waits：主服務(wù)器等候事物的總次數(shù)。
Rpl_semi_sync_master_wait_pos_backtraverse：
Rpl_semi_sync_master_wait_sessions：正在等候從服務(wù)器回復(fù)的會(huì)話數(shù)。
Rpl_semi_sync_master_yes_tx：從服務(wù)器成功得到答應(yīng)的提交事物次數(shù)。

[SLAVE]
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

Rpl_semi_sync_slave_status：
rpl_semi_sync_slave_enabled：
rpl_semi_sync_slave_trace_level：

mysql> show global variables like 'rpl_semi_sync%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | OFF   |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
6 rows in set (0.00 sec)

mysql> set global rpl_semi_sync_master_enabled = on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global rpl_semi_sync_slave_enabled  = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rpl_semi_sync%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | ON    |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
6 rows in set (0.00 sec)

//rpl_semi_sync_master_enabled = on ：?jiǎn)?dòng)master 支持半同步復(fù)制
//rpl_semi_sync_slave_enabled ：?jiǎn)?dòng)slave支持半同步復(fù)制

當(dāng)在從服務(wù)運(yùn)行時(shí)調(diào)整支持半同步復(fù)制，必須重啟I/O線程。
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

在my.cnf配置文件如下：
在主服務(wù)器上:
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second

在每個(gè)從服務(wù)器上:
[mysqld]
rpl_semi_sync_slave_enabled=1

//關(guān)于mysql5.5存儲(chǔ)引擎后臺(tái)打印
mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
120330 14:37:49 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 367 1_second, 367 sleeps, 35 10_second, 19 background, 19 flush
srv_master_thread log flush and writes: 367
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 32, signal count 32
Mutex spin waits 14, rounds 420, OS waits 0
RW-shared spins 32, rounds 960, OS waits 32
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 853
Purge done for trx's n:o < 77E undo n:o < 0
History list length 63
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 29, OS thread handle 0x49499940, query id 5743 localhost root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
379 OS file reads, 816 OS file writes, 358 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3292715
Log flushed up to   3292715
Last checkpoint at  3292715
0 pending log writes, 0 pending chkp writes
313 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 1809452
Buffer pool size   8191
Free buffers       7328
Database pages     861
Old database pages 297
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 368, created 493, written 1315
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 861, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 3289, id 1228495168, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

上述內(nèi)容就是mysql5.5中怎么實(shí)現(xiàn)半同步復(fù)制，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

網(wǎng)站標(biāo)題：mysql5.5中怎么實(shí)現(xiàn)半同步復(fù)制
文章轉(zhuǎn)載：http://muchs.cn/article48/jcpdhp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、網(wǎng)站導(dǎo)航、App開發(fā)、外貿(mào)建站、虛擬主機(jī)、微信小程序

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都定制網(wǎng)站建設(shè)