Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve semi-synchronous replication in mysql5.5, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Master:192.168.70.101

Slave:192.168.70.100

Create a user repl on master (just create a repl user on master)

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

On master, my.cnf is configured as follows:

[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

The configuration in my.cnf of slave is as follows:

[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

Give birth on master.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | master-bin.000003 | 106 | |

+-+

1 row in set (0.00 sec)

Use the change master to command on slave to point slave to master. Then use the start slave command to start replication.

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-success

Slave_SQL_Running: Yes-success

The flush logs command forces the binary log to be rotated so that the complete binary log file can be obtained.

Use show binlog events in 'master-bin.000004 (binary log)'\ G

Check what events are in the binary log

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)

/ / Clone MASTER

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

The master-data=1 option mysqldump writes the change master to statement, and the argument is the binary log file and its location.

Then restore the backup on 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)

/ / configure to support semi-synchronous replication on the master-slave server

[MASTER]

Mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so'

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 |

+-+ +

Modify the rpl_semi_sync_master_enabled=ON,rpl_semi_sync_master_timeout unit time to milliseconds.

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

+-+ +

The following variables are valid only after the semi-replication plug-in of the main service is initialized. The parameters are explained as follows:

Rpl_semi_sync_master_clients: represents the number of semi-replicated slave servers.

Rpl_semi_sync_master_net_avg_wait_time: the average millisecond time the master server waits for the slave server. (in milliseconds)

Rpl_semi_sync_master_net_waits: the total millisecond time the master server waits for the slave server. (in milliseconds)

Rpl_semi_sync_master_no_times: the number of times the primary server shuts down half replication.

Rpl_semi_sync_master_no_tx: the number of times an ack confirmation has not been successful since the transaction was submitted from the server.

Rpl_semi_sync_master_status: determines whether the semi-server can operate on the master server. Setting it to ON means to enable it, and OFF means to turn it off in asynchronous mode.

Rpl_semi_sync_master_timefunc_failures: the number of times the master server failed to call the time function, such as gettimeofday ().

Rpl_semi_sync_master_tx_avg_wait_time: the average time (in milliseconds) that the master server waits for everything.

Rpl_semi_sync_master_tx_waits: the total number of times the primary server waits for things.

Rpl_semi_sync_master_wait_pos_backtraverse:

Rpl_semi_sync_master_wait_sessions: the number of sessions waiting to reply from the server.

Rpl_semi_sync_master_yes_tx: the number of times a transaction has been successfully committed from the server.

[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: starts master to support semi-synchronous replication

/ / rpl_semi_sync_slave_enabled: starts slave to support semi-synchronous replication

When you adjust to support semi-synchronous replication while the slave service is running, you must restart the Ithumb O thread.

Mysql > STOP SLAVE IO_THREAD; START SLAVE IO_THREAD

The my.cnf configuration file is as follows:

On the primary server:

[mysqld]

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000 # 1 second

On each slave server:

[mysqld]

Rpl_semi_sync_slave_enabled=1

/ / about mysql5.5 storage engine background printing

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 NRO < 77e undo NRO < 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

-

Waiting for O thread 0 state: waiting for iUnip o request (insert buffer thread)

I thread 1 state: waiting for I request (log thread)

I thread 2 state: waiting for I request (read thread)

I thread 3 state: waiting for I request (read thread)

Waiting for O thread 4 state: waiting for iUnip o request (read thread)

I thread 5 state: waiting for I request (read thread)

Waiting for O thread 6 state: waiting for iUnip o request (write thread)

Waiting for O thread 7 state: waiting for iUnip o request (write thread)

Waiting for O thread 8 state: waiting for iUnip o request (write thread)

I thread 9 state: waiting for I 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

= =

The above is how to achieve semi-synchronous replication in mysql5.5. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.