这篇文章给大家介绍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<<EOF
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<<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)
关于Mysql MHA部署中什么是主从复制就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。