mysql 5.5半同步复制_mysql5.5主从复制-半同步复制

最新推荐文章于 2021-06-25 16:59:56 发布

咯无咯

最新推荐文章于 2021-06-25 16:59:56 发布

阅读量104

点赞数

文章标签： mysql 5.5半同步复制

本文链接：https://blog.csdn.net/weixin_30212109/article/details/113905207

版权

本文详细介绍了MySQL 5.5版本的主从复制配置步骤，包括创建用户、配置my.cnf、查看主从状态、启动复制等。同时，文章还探讨了半同步复制的启用方法，包括在主从服务器上安装插件、设置相关变量，并展示了如何通过监控状态来确保复制的同步性。

摘要由CSDN通过智能技术生成

mysql5.5主从复制

申明：关于mysql5.5的编译，请参考博文http://blog.chinaunix.net/uid-20682026-id-3144341.html

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上产看，

mysql> show master status;

+-------------------+----------+--------------+------------------+

+-------------------+----------+--------------+------------------+

| master-bin.000003 | 106 | | |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

在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(二进制日志)'\G;

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

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选项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 ‘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 |

+------------------------------------+-------+

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

+--------------------------------------------+-------+

下面的变量仅仅在主服务的半复制插件初始化后才有效，参数解释如下：

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

============================

阅读(6045) | 评论(0) | 转发(1) |

咯无咯

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫