Mysql MHA部署-主从复制

架构说明:

参考：http://www.zhaibibei.cn/mysql/mha/

搭建主从复制

1 Mysql安装

2 rac1(187 主),rac3(223 从)配置异步复制

3 rac1(187 主),rac2(188 从)配置半同步复制

我们根据上面的拓扑建立主从关系，192.168.2.223采用半同步，192.168.2.223采用异步

采用基于GTID的复制，否则建议关闭GTID功能

这里就不多做介绍了，具体见上一个专题

注意在做主从同步的时候建议清理下从库相关信息

reset master ;reset slave all;

时间同步：

[root@rac1 ~]# ntpdate cn.pool.ntp.org

14 Mar 15:37:01 ntpdate[31863]: step time server 203.107.6.88 offset 2.987670 sec

[root@rac1 ~]# date

Sat Mar 14 15:37:17 CST 2020

1 Mysql安装

---rac1(主187),rac2(从188),rac3(从223)分别安装Mysql

1.1 配置Limits

[root@rac1 package]# cat>>/etc/security/limits.conf<<EOF

mysql soft nofile1024

mysql hard nofile65536

mysql soft nproc4095

mysql hard nproc16384

mysql soft stack10240

mysql hard stack32768

EOF

1.2 系统内核参数

shmmax和shmall的设置 shmmax指的是单个内存段的最大值，单位为bytes shmall指的是能使用的最大内存大小，

单位为pages, pages大小可通过 getconf PAGE_SIZE 命令查询，一般操作系统page大小为4096 bytes 如操作系统内存为8G，给80%给Oracle使用，

则 kernel.shmmax=(8 * 0.8 * 1024 * 1024 * 1024 )=6871947673 kernel.shmall=kernel.shmmax/4096=1677721

如默认值比较大 请保持默认值

cat>>/etc/sysctl.conf<<EOF

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

fs.aio-max-nr = 1048576

# vm.min_free_kbytes = 524288

vm.swappiness= 5

# vm.nr_hugepages =1024

# vm.hugetlb_shm_group = 2000

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.ip_local_port_range = 1024 65500

1.3 目录规划

目录名称参数名称路径地址

安装目录basedir/usr/local/mysql

数据文件目录datadir/data/mysql/data

临时文件目录tmpdir/data/mysql/tmp

socket文件目录socket/data/mysql/data/mysql.sock

bin日志文件目录log_bin/datalog/mysql/binlog

relay日志文件目录relay_log/datalog/mysql/relaylog

1.4 MySQL5.7下载

下载地址：

dev.mysql.com/downloads/mysql

这里统一使用5.7.28的版本

[root@rac1 mysql]# pwd

/package/mysql

[root@rac1 mysql]# ll -rth

total 692M

-rw-r--r-- 1 root root 692M Mar 14 11:49 mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

[root@rac1 mysql]#

1.5 新建用户及目录

[root@rac1 ~]# /usr/sbin/groupadd -g 105 mysql

[root@rac1 ~]# /usr/sbin/useradd -u 105 -g mysql mysql

[root@rac1 ~]# echo "123456" |passwd mysql --stdin

[root@rac1 ~]# mkdir -p /data/mysql/software

[root@rac1 ~]# mkdir -p /usr/local/mysql

[root@rac1 ~]# mkdir -p /data/mysql/data

[root@rac1 ~]# mkdir -p /datalog/mysql/binlog

[root@rac1 ~]# mkdir -p /datalog/mysql/relaylog

[root@rac1 ~]# chown -R mysql:mysql /usr/local/mysql

[root@rac1 ~]# mkdir -p /data/mysql/tmp

[root@rac1 ~]# chown -R mysql:mysql /data/mysql

[root@rac1 ~]# chown -R mysql:mysql /datalog/mysql/

1.6 配置环境变量

[root@rac1 ~]# su - mysql

Attempting to create directory /home/mysql/perl5

[mysql@rac1 ~]$ vim .bash_profile

...

export MYSQL_HOME=/usr/local/mysql

export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH

export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$LD_LIBRARY_PATH

[mysql@rac1 ~]$ source .bash_profile

1.7 建立配置文件

[root@rac1 ~]# cp /etc/my.cnf /etc/my.cnf.bak

[root@rac1 ~]# vim /etc/my.cnf

[mysql]

user =

password =

[mysqld]

#-----------------MySQL Basic Setting-----------------#

server-id = 1723161113

port = 3306

user = mysql

pid-file = mysql.pid

character_set_server = utf8mb4

default_storage_engine = InnoDB

skip_name_resolve = 1

lower_case_table_names = 1

explicit_defaults_for_timestamp = 1

open_files_limit = 65535

max_connections = 1000

max_connect_errors = 100000

basedir = /usr/local/mysql

datadir = /data/mysql/data

tmpdir = /data/mysql/tmp

socket = /data/mysql/data/mysql.sock

query_cache_type = 0

query_cache_size = 0

join_buffer_size = 64M

tmp_table_size = 64M

max_allowed_packet = 32M

read_buffer_size = 16M

read_rnd_buffer_size = 32M

sort_buffer_size = 32M

log_error_verbosity=2

log_timestamps=SYSTEM

#-----------------MySQL Log Setting-----------------#

log_error = mysql-error.log

log_bin = /datalog/mysql/binlog/mysql-bin.log

slow_query_log_file = mysql-slow.log

relay_log = /datalog/mysql/relaylog/mysql-relay.log

log_slave_updates = 1

sync_binlog = 1

relay_log_recovery = 1

binlog_format = row

expire_logs_days = 14

slow_query_log = 1

long_query_time = 2

log_queries_not_using_indexes = 1

log_throttle_queries_not_using_indexes = 10

log_slow_admin_statements = 1

log_slow_slave_statements = 1

min_examined_row_limit = 1000

#-----------------MySQL Replication Setting-----------------#

slave_skip_errors = ddl_exist_errors

master_info_repository = TABLE

relay_log_info_repository = TABLE

#gtid_mode = on

#enforce_gtid_consistency = 1

binlog_rows_query_log_events = 1

#-----------------MySQL InnoDB Setting-----------------#

innodb_page_size = 16384

innodb_buffer_pool_size = 25600M

innodb_data_file_path = ibdata1:1G:autoextend

innodb_buffer_pool_instances = 8

innodb_file_per_table = 1

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 5

innodb_io_capacity = 800

innodb_io_capacity_max = 2000

innodb_flush_method = O_DIRECT

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

innodb_flush_neighbors = 1

innodb_log_file_size = 2G

innodb_log_buffer_size = 16777216

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

#-----------------MySQL semi Replication Setting-----------------#

#plugin_dir = /usr/local/mysql/lib/plugin

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#loose_rpl_semi_sync_master_enabled = 1

#loose_rpl_semi_sync_slave_enabled = 1

#loose_rpl_semi_sync_master_timeout = 5000

修改my.cnf权限

[root@rac1 ~]# chown mysql.mysql /etc/my.cnf

1.8 依赖包检查

[root@rac1 ~]# rpm -qa libaio*

libaio-0.3.109-13.el7.x86_64

libaio-devel-0.3.109-13.el7.x86_64

[root@rac1 ~]# rpm -qa lvm2-*

lvm2-libs-2.02.177-4.el7.x86_64

lvm2-python-libs-2.02.177-4.el7.x86_64

1.9 解压文件

[root@rac1 ~]# chown mysql.mysql /package/mysql -R

[mysql@rac1 ~]$ cd /package/mysql/

[mysql@rac1 mysql]$ ls

mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

[mysql@rac1 mysql]$ tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql/ --strip-components=1

mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisam_ftdump

mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisamchk

mysql-5.7.28-linux-glibc2.12-x86_64/bin/myisamlog

......

1.10 配置服务文件

这里将mysql.server文件拷贝值init.d目录使其可以当作服务启停

[root@rac1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

这里修改下面几处

[root@rac1 ~]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql

datadir=/data/mysql/data

lockdir='/data/mysql/data'

mysqld_pid_file_path=/data/mysql/data/mysql.pid

1.11 初始化数据库

[mysql@rac1 mysql]$ /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

[root@rac1 ~]# tail -f /data/mysql/data/mysql-error.log

......

2020-03-14T13:22:12.786017+08:00 1 [Note] A temporary password is generated for root@localhost: 7DO4gs27;YOM

1.12 启动和关闭数据库

[mysql@rac1 mysql]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

[1] 18642

2020-03-14T05:24:05.258268Z mysqld_safe Logging to '/data/mysql/data/mysql-error.log'.

2020-03-14T05:24:05.320993Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

[mysql@rac1 mysql]$ netstat -lntp|grep mysqld

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp6 0 0 :::3306:::*LISTEN 19567/mysqld

后续可以使用如下命令启停数据库

[mysql@rac1 mysql]$ service mysqld stop

Shutting down MySQL....2020-03-14T05:25:16.603331Z mysqld_safe mysqld from pid file /data/mysql/data/mysql.pid ended

[ OK ]

[1]+ Done/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql

[mysql@rac1 mysql]$ service mysqld start

Starting MySQL.........[ OK ]

设置MySQL自启动

chkconfig mysqld on

1.13 连接数据库

默认密码在error文件中有

[mysql@rac1 mysql]$ cat /data/mysql/data/mysql-error.log |grep password

2020-03-14T13:22:12.786017+08:00 1 [Note] A temporary password is generated for root@localhost: 7DO4gs27;YOM

使用如下命令连接

[mysql@rac1 mysql]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

Enter password: 7DO4gs27;YOM

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.28-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user 'root'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

[mysql@rac1 mysql]$ mysql -S /data/mysql/data/mysql.sock -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys|

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

4 rows in set (0.00 sec)

2 rac1(187 主),rac3(223 从)配置异步复制

这节我们的内容为MySQL的复制，MySQL复制有两种形式

基于二进制日志文件位置

基于GTID

这节为第一种基于二进制日志文件位置

2.1 开启二进制日志功能

无论是使用哪种方式我们都需要启用二进制日志功能

如果未开启则需要在my.cnf文件中加入如下参数，需要重启数据库生效

---主库187 rac1

[mysqld]

server-id = 1

binlog_format = row

log_bin = /datalog/mysql/binlog/mysql-bin.log

expire_logs_days = 14

log-slave-updates=ON

---从库223 rac3

[mysqld]

server-id = 3

binlog_format = row

log_bin = /datalog/mysql/binlog/mysql-bin.log

expire_logs_days = 14

log-slave-updates=ON

read_only=1

---重启mysql

[mysql@rac1 ~]$ service mysqld stop

Shutting down MySQL.....[ OK ]

[mysql@rac1 ~]$ service mysqld start

Starting MySQL..............[ OK ]

2.2 查看UUID是否一致

需要注意的是如果从库是由主库克隆而来，这时的uuid是一样的，这样也会报错

该文件位于daadir的auto.cnf文件中

vim /data/mysql/data/auto.cnf

如果一样可删除该文件后重新启动数据库即可，这时会生成一个新的文件

2.3 建立复制账号

接下来我们建立一个独立的用于复制的账号

主库和从库

[mysql@rac1 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

mysql> CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY 'rpl';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.187';

mysql> CREATE USER 'repl'@'192.168.2.223' IDENTIFIED BY 'rpl';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.223';

mysql> flush privileges;

mysql> select host,user from user;

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

| host | user |

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

| 192.168.2.187 | repl |

| 192.168.2.223 | repl |

| localhost| mysql.session |

| localhost| mysql.sys|

| localhost| root |

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

5 rows in set (0.00 sec)

这里我们限制该账号只能从同步的两台服务器上连接

2.4 备份主库

---创建测试数据(模拟生产数据)

mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> use jumptest

Database changed

mysql> source /package/mysql/jumpserver_bak_2020_03_13_22_00_01.sql

---备份

[mysql@rac1 ~]$ mysqldump -S /data/mysql/data/mysql.sock -uroot -p --databases jumptest --single-transaction --master-data=2 --set-gtid-purged=off --triggers --events --routines> /tmp/dumpmaster.sql

Enter password:

[mysql@rac1 ~]$ ll -rth /tmp/dumpmaster.sql

-rw-r--r-- 1 mysql mysql 19M Mar 14 17:04 /tmp/dumpmaster.sql

2.5 文件传输

接下来将主库的dump文件传到备份，之后更改备库的文件权限

主库

[mysql@rac1 ~]$ scp /tmp/dumpmaster.sql root@192.168.2.223:/tmp

从库

[root@rac3 ~]# chown mysql:mysql /tmp/dumpmaster.sql

2.6 备库导入数据

接下来我们将备份的数据导入到备份

[mysql@rac3 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

mysql> use jumptest

Database changed

mysql> source /tmp/dumpmaster.sql

2.7 开始同步

接下来我们开启同步

首先我们查看dumpmaster.sql文件中master的信息

mysql> show master status;

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

| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 | 34751569 | | | c1227971-65b3-11ea-bf67-080027839e5c:1-297 |

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

1 row in set (0.00 sec)

---从库开启同步命令2.223

mysql> change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34751569;

Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

2.8 从库查看同步状态

使用如下命令查看同步是否正常

主要关注如下几点

Slave_IO_Running需要为YES

Slave_SQL_Running需要为YES

Seconds_Behind_Master需要为0

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.187

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 34751569

Relay_Log_File: mysql-relay.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

......

Seconds_Behind_Master: 0

......

2.9 重启和重置复制

使用如下命令关闭重启

mysql>stop slave;

mysql>start slave;

我们可以独立的重启IO进程或者SQL进程

mysql>stop slave sql_thread;

mysql>stop slave io_thread;

mysql>start slave io_thread;

mysql>start slave sql_thread;

使用如下命令重置复制

mysql>reset slave all;

3 rac1(187 主),rac3(188 从)配置半同步复制

3.1 半同步介绍

异步的复制，主库将二进制日志发送到从库后并不需要确认从库是否接受并应用，这时就可能会造成数据丢失。

MySQL 从5.5版本后推出了半同步的功能，相当于Oracle DG的最大保护模式，它要求从库在接收并应用日志后，主库才提交完成，保证了数据。

开启半同步需要如下要求：

MySQL 5.5及以上版本

变量have_dynamic_loading为YES

3.2 188从库初始化主库数据

188：

mysql> CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

mysql> use jumptest

Database changed

mysql> source /tmp/dumpmaster.sql

3.3 创建复制账号

[mysql@rac1 ~]$ mysql -S /data/mysql/data/mysql.sock -uroot -p

mysql> CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY 'rpl';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.187';

mysql> CREATE USER 'repl'@'192.168.2.188' IDENTIFIED BY 'rpl';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.188';

mysql> flush privileges;

mysql> use mysql

---从库188

mysql> select host,user from user;

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

| host | user |

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

| 192.168.2.187 | repl |

| 192.168.2.188 | repl |

| localhost| mysql.session |

| localhost| mysql.sys|

| localhost| root |

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

5 rows in set (0.00 sec)

---主库187

mysql> select host,user from user;

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

| host | user |

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

| 192.168.2.187 | repl |

| 192.168.2.188 | repl |

| 192.168.2.223 | repl |

| localhost| mysql.session |

| localhost| mysql.sys|

| localhost| root |

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

6 rows in set (0.00 sec)

3.4 加载半同步插件

因需执行INSTALL PLUGIN, SET GLOBAL, STOP SLAVE和START SLAVE操作，所以用户需有SUPER权限

主库和从库

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

这里安装半同步的master和slave插件

考虑到后面主从可能需要切换，这里在主从库上都安装

确认是否加载成功

mysql> show plugins;

......

| rpl_semi_sync_master | ACTIVE | REPLICATION| semisync_master.so | GPL|

| rpl_semi_sync_slave| ACTIVE | REPLICATION| semisync_slave.so | GPL|

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

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

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

| PLUGIN_NAME | PLUGIN_STATUS |

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

| rpl_semi_sync_master | ACTIVE|

| rpl_semi_sync_slave | ACTIVE|

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

2 rows in set (0.00 sec)

3.5 启用半同步

首先我们启用半同步插件

主库 187

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

从库 188

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

mysql> change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34752186;

mysql> start slave;

mysql> show slave status \G;

之后需要重启IO线程才能使半同步生效，也可直接重启复制

从库

mysql> STOP SLAVE IO_THREAD;

mysql> START SLAVE IO_THREAD;

如果从库超过一定时间不能和主库进行通信，则会自动降为异步模式

该时间由rpl_semi_sync_master_timeout参数控制，单位为毫秒

3.6 查看半同步状态

我们使用如下命令查看半同步是否正常工作

主库的master和从库的slave都需要为ON

主库

mysql> show status like 'Rpl_semi_sync_master_status';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_status | ON|

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

1 row in set (0.02 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_status | OFF |

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

1 row in set (0.00 sec)

从库

mysql> show status like 'Rpl_semi_sync_slave_status';

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_status | ON|

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

1 row in set (0.00 sec)

mysql> show status like 'Rpl_semi_sync_master_status';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_status | OFF |

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

1 row in set (0.01 sec)

3.7 写入配置文件

接下来我们将命令写在配置文件中以使重启后自动启动

考虑到后面主从可能需要切换，这里在主从库上半同步的master和slave都设置为启动

主库187和从库188

[mysql@rac1 ~]$ vim /etc/my.cnf

#-----------------MySQL semi Replication Setting-----------------#

plugin_dir = /usr/local/mysql/lib/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

rpl_semi_sync_master_timeout = 5000

--从库 188,223

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

主187、从库188 重启生效：

[mysql@rac1 ~]$ service mysqld restart

Shutting down MySQL....[ OK ]

Starting MySQL.........[ OK ]

测试数据同步

主库187

mysql> create table cjc01(id int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into cjc01 values(1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into cjc01 values(2);

Query OK, 1 row affected (0.00 sec)

mysql> insert into cjc01 values(3);

Query OK, 1 row affected (0.01 sec)

mysql> update cjc01 set id=100 where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> delete from cjc01 where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from cjc01;

+------+

| id |

+------+

| 100 |

|3 |

+------+

2 rows in set (0.00 sec)

从库188

mysql> select * from cjc01;

+------+

| id |

+------+

| 100 |

|3 |

+------+

2 rows in set (0.00 sec)

从库223

mysql> select * from cjc01;

+------+

| id |

+------+

| 100 |

|3 |

+------+

2 rows in set (0.00 sec)