mysql5.5中怎么实现半同步复制

56次阅读

没有评论

共计 10782 个字符，预计需要花费 27 分钟才能阅读完成。

本篇文章为大家展示了 mysql5.5 中怎么实现半同步复制，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

master:192.168.70.101

slave:192.168.70.100

在 master 上创建用户 repl(只需要在 master 上创建 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 上产看，

在 slave 上使用 change master to 命令将 slave 指向 master。然后使用 start slave 命令启动复制。

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 命令强制轮换二进制日志，从而可以得到完整的二进制日志文件。

使用 show binlog events in master-bin.000004(二进制日志)

检查二进制日志里有哪些事件

mysql show binlog events in master-bin.000004
*************************** 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 选项 mysqldump 写 change master to 语句，且参数为二进制日志文件及其位置。

然后在 slave 上恢复备份：

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

// 克隆 SLAVE

// 清除 Binlog 日志

服务器自动清理旧的 binlog 文件，需设置 expire-logs-days 选项，这个选项可以作为服务器变量。如果服务重启后，不受影响，需要在 my.cnf 设置。

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

1，purge binary log before datatime

将清除在给定时间之前的所有文件。

2,purge binary logs to filename

将清除在给定文件之前的所有文件。

// 默认情况下，由 slave 执行的事件没有被记录到二进制日志中，如果这个 slave 是 master 的一个备份，这时会出现问题。

在 my.cnf 添加 log-slave-updates，以确保来自于 master 并被执行的语句会被写入 slave 的二进制日志中。

切换基本思路：为了让 slave 赶上备份服务器，并在正确的位置停止，使用 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);

// 在主从服务器上配置支持半同步复制

[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
*************************** 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 单位时间为毫秒。

mysql set global rpl_semi_sync_master_enabled = on;
Query OK, 0 rows affected (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 |
+——————————————–+——-+
下面的变量仅仅在主服务的半复制插件初始化后才有效，参数解释如下：
Rpl_semi_sync_master_clients：表示半复制从服务器的数量。
Rpl_semi_sync_master_net_avg_wait_time：主服务器等候从服务器的平均毫秒时间。(单位为毫秒)
Rpl_semi_sync_master_net_waits：主服务器等候从服务器的总毫秒时间。(单位为毫秒)
Rpl_semi_sync_master_no_times：主服务器关闭半复制的次数。
Rpl_semi_sync_master_no_tx：从服务器提交事物后没有收到 ack 确认成功的次数。
Rpl_semi_sync_master_status：决定半服务器是否可以在主服务器上操作，设置为 ON 表示开启，OFF 表示关闭，为异步模式。
Rpl_semi_sync_master_timefunc_failures: 主服务器调用时间函数失败的次数，例如 gettimeofday()。
Rpl_semi_sync_master_tx_avg_wait_time：主服务器等候每个事物的平均时间 (毫秒)。
Rpl_semi_sync_master_tx_waits：主服务器等候事物的总次数。
Rpl_semi_sync_master_wait_pos_backtraverse：
Rpl_semi_sync_master_wait_sessions：正在等候从服务器回复的会话数。
Rpl_semi_sync_master_yes_tx：从服务器成功得到答应的提交事物次数。

[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：启动 master 支持半同步复制
//rpl_semi_sync_slave_enabled：启动 slave 支持半同步复制

当在从服务运行时调整支持半同步复制，必须重启 I / O 线程。
mysql STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

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

在每个从服务器上:
[mysqld]
rpl_semi_sync_slave_enabled=1

// 关于 mysql5.5 存储引擎后台打印
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
============================