MysqlMHA部署中什么是主从复制

这篇文章给大家介绍MySQL MHA部署中什么是主从复制，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

公司主营业务：网站制作、网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出濮阳县免费做网站回馈大家。

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<

mysql   soft   nofile    1024

mysql   hard   nofile    65536

mysql   soft   nproc     4095

mysql   hard   nproc     16384

mysql   soft   stack     10240

mysql   hard   stack     32768

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<

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)

关于Mysql MHA部署中什么是主从复制就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

新闻名称：MysqlMHA部署中什么是主从复制
转载注明：http://jkwzsj.com/article/ppppge.html