MYSQL HA 之02(MYSQL5.7双主配置)
2019-09-26 本文已影响0人
轻飘飘D
- 统一设置hosts(兩台)
[root@XAG137 usr]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.40.137 XAG137
192.168.40.138 XAG138
- 配置SSH无密码登录认证
[root@XAG137 ~]# ssh-keygen -t rsa
#一路回车，直到完成
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.138
[root@XAG138 /]# ssh-keygen -t rsa
#一路回车，直到完成
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.40.137
#測試免密
[root@XAG137 ~]# ssh XAG138
Are you sure you want to continue connecting (yes/no)? yes
[root@XAG138 ~]# exit
logout
[root@XAG137 ~]# ssh XAG138
Last login: Thu Sep 19 17:36:05 2019 from 192.168.40.137
[root@XAG138 /]# ssh XAG137
Are you sure you want to continue connecting (yes/no)? yes
[root@XAG137 ~]# exit
logout
[root@XAG138 /]# ssh XAG137
Last login: Thu Sep 19 17:36:58 2019 from 192.168.40.138
- 时间同步
[root@XAG137 ~]# ssh XAG138 date;
发现时间不同步，进行时间同步，依据当前时间为准
[root@XAG137 ~]# ssh XAG138 "date -s '2019-09-19 17:42:00'";
- 修改配置(兩台)
shell> echo "fs.file-max=65535" >> /etc/sysctl.conf
shell> sysctl -p
shell># mkdir /usr/local/mysql/iblog
shell># chown mysql:mysql /usr/local/mysql/iblog
shell># chown mysql.mysql -R /usr/local/mysql/
- 在各个节点安装半同步插件
在192.168.40.137、138上：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled=on;
6.創建測試DB(主DB--137)
root@127.0.0.1 : (none)【05:52:23】4 SQL->create database if not exists test default charset utf8 collate utf8_general_ci;
root@127.0.0.1 : (none)【06:02:10】5 SQL->show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
root@127.0.0.1 : (none)【09:17:02】17 SQL->use test;
create table test_scheduler
(
sch_seq int auto_increment not null,
sch_name varchar(50),
sch_createtime datetime,
primary key(sch_seq)
) auto_increment=1001;
root@127.0.0.1 : test【09:17:35】19 SQL->insert into test_scheduler(sch_name,sch_createtime) values('sch01',now());
root@127.0.0.1 : test【09:17:57】20 SQL->insert into test_scheduler(sch_name,sch_createtime) values('sch01',now());
root@127.0.0.1 : test【09:18:00】21 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-09-20 09:17:57 |
| 1003 | sch01 | 2019-09-20 09:18:00 |
+---------+----------+---------------------+
7.修改 /etc/my.cnf配置
137上新加如下內容：
#雙主配置新加配置項
#定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连
slave_net_timeout=15
#信任子程序的创建者（解決mysql主從同步時错误1418）
log-bin-trust-function-creators=1
#步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=2
#起始值。一般填第n台主MySQL。此时为第一台主MySQL
auto_increment_offset=1
#要同步的数据库，默认所有库
replicate-do-db=test
#二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7
#跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
#如： 1062 错误是指一些主键重复， 1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062,1032,1007,1008,1050,1051,1054,1060,1061,1068,1094,1146
#开启半同步复制 否则自动切换主从的时候会报主键错误
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 500
# slave --MySQL 5.7开启Enhanced Multi-Threaded 在Slave的my.cnf配置：
#变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式
slave-parallel-type=LOGICAL_CLOCK
#提交的事务都是可以并行回放(配合binary log group commit);
slave-parallel-workers=16
#将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
master_info_repository=TABLE
relay_log_info_repository=TABLE
#启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_recovery=ON
relay_log_purge=ON
#GTID
gtid_mode = on
enforce_gtid_consistency = 1
#做为从库时，数据库的修改也会写到bin-log里
log_slave_updates = 1
138上新加如下內容：
#雙主配置新加配置項
#定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连
slave_net_timeout=15
#信任子程序的创建者（解決mysql主從同步時错误1418）
log-bin-trust-function-creators=1
#步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_increment=2
#起始值。一般填第n台主MySQL。此时为第一台主MySQL
auto_increment_offset=2
#要同步的数据库，默认所有库
replicate-do-db=test
#二进制日志自动删除/过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7
#跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
#如： 1062 错误是指一些主键重复， 1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062,1032,1007,1008,1050,1051,1054,1060,1061,1068,1094,1146
#开启半同步复制 否则自动切换主从的时候会报主键错误
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 500
# slave --MySQL 5.7开启Enhanced Multi-Threaded 在Slave的my.cnf配置：
#变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式
slave-parallel-type=LOGICAL_CLOCK
#提交的事务都是可以并行回放(配合binary log group commit);
slave-parallel-workers=16
#将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
master_info_repository=TABLE
relay_log_info_repository=TABLE
#启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_recovery=ON
relay_log_purge=ON
#GTID
gtid_mode = on
enforce_gtid_consistency = 1
#做为从库时，数据库的修改也会写到bin-log里
log_slave_updates = 1
#slave_skip_errors 錯誤碼說明
ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）
一些error code代表的错误如下：
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1050：数据表已存在，创建数据表失败
1051：数据表不存在，删除数据表失败
1054：字段不存在，或程序文件跟数据库有冲突
1060：字段重复，导致无法插入
1061：重复键名
1068：定义了多个主键
1094：位置线程ID
1146：数据表缺失，请恢复数据库
1053：复制过程中主服务器宕机
1062：主键冲突 Duplicate entry '%s' for key %d
- 在主库创建同步复制用户
[root@XAG137 ~]# mysql.login
root@127.0.0.1 : (none)【06:58:46】1 SQL->CREATE USER 'repl'@'%' IDENTIFIED BY 'rep123';
root@127.0.0.1 : (none)【06:58:57】2 SQL->grant replication slave on *.* to 'repl'@'%' identified by 'rep123';
root@127.0.0.1 : (none)【06:59:09】3 SQL->show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | utf8mb4 |
| collation_server | utf8mb4_general_ci |
| innodb_ft_server_stopword_table | |
| server_id | 137 |
| server_id_bits | 32 |
| server_uuid | 3e7915f5-daab-11e9-ac9b-000c2929f2d3 |
+---------------------------------+--------------------------------------+
- reset (兩台)
root@127.0.0.1 : test【09:18:12】22 SQL->reset master;
root@127.0.0.1 : test【09:19:55】23 SQL->show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 154 | | | |
+---------------+----------+--------------+------------------+-------------------+
- 在192.168.40.137 主库上执行备份
[root@XAG137 ~]# cd /usr/local/src
[root@XAG137 src]# ls
mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
[root@XAG137 src]# mysqldump -uroot -p --master-data=2 --single-transaction --default-character-set=utf8 -R --triggers -A >all2.sql
Enter password:
[root@XAG137 src]# ls
all2.sql mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
#查看上备份时刻Binlog的名称和位置，MASTER_LOG_FILE 和 MASTER_LOG_FILE
[root@XAG137 src]# head -n 30 all2.sql|grep -i "CHANGE MASTER TO"
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154;
or
root@127.0.0.1 : test【09:20:18】24 SQL->show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 154 | | | |
+---------------+----------+--------------+------------------+-------------------+
- 在备库上执行恢复及同步
#将all.sql拷贝到 192.168.40.138 备库机器上
[root@XAG137 src]# scp all2.sql root@192.168.40.138:/home
[root@XAG138 /]# cd /home
[root@XAG138 home]# mysql -uroot -p<all2.sql
[root@XAG138 home]# mysql.login
#如开启GTID后用下行，可不用下下行(下下行会报错)
change master to master_host='192.168.40.137',master_user='repl',master_password='rep123',master_port=3306,master_auto_position=1;
mysql> change master to master_host='192.168.40.137',master_user='repl',master_password='rep123',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=154,MASTER_HEARTBEAT_PERIOD=10,MASTER_CONNECT_RETRY=10, MASTER_RETRY_COUNT=86400;
#slave_net_timeout（全局变量）：MySQL5.7.7之后，默认改成60秒。该参数定义了从库从主库获取数据等待的秒数，
#超过这个时间从库会主动退出读取，中断连接，并尝试重连。
#master_heartbeat_period：复制心跳的周期。默认是slave_net_timeout的一半。Master在没有数据的时候，
#每master_heartbeat_period秒发送一个心跳包，这样 Slave 就能知道 Master 是不是还正常。
#slave_net_timeout是设置在多久没收到数据后认为网络超时，之后 Slave 的 IO 线程会重新连接 Master 。
#结合这两个设置就可以避免由于网络问题导致的复制延误。master_heartbeat_period 单位是秒，可以是个带上小数，如 10.5，最高精度为 1 毫秒。
#重试策略为：
#备库过了slave-net-timeout秒还没有收到主库来的数据，它就会开始第一次重试。然后每过 master-connect-retry 秒，备库会再次尝试重连主库。
#直到重试了 master-retry-count 次，它才会放弃重试。如果重试的过程中，连上了主库，那么它认为当前主库是好的，
#又会开始 slave-net-timeout 秒的等待。slave-net-timeout 的默认值是 60 秒， master-connect-retry 默认为 60 秒，
# master-retry-count 默认为 86400 次。也就是说，如果主库一分钟都没有任何数据变更发送过来，备库才会尝试重连主库。
OR 通过如下命令修改
stop slave;
change master to master_connect_retry=10;
change master to MASTER_HEARTBEAT_PERIOD =8;
start slave;
#启动B库上的主从复制
mysql> start slave;
mysql> show slave status\G
----------------------------------------------
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
root@127.0.0.1 : (none)【09:56:28】2 SQL->stop slave;
root@127.0.0.1 : (none)【10:00:10】3 SQL->reset slave;
root@127.0.0.1 : (none)【10:00:18】4 SQL->start slave;
root@127.0.0.1 : (none)10:00:245 SQL->show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#记下下面两个参数的值，后面会在A库执行
root@127.0.0.1 : (none)【09:42:39】5 SQL->show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 828749 | | | |
+---------------+----------+--------------+------------------+-------------------+
- 在A库192.168.40.137上执行同步
mysql> change master to master_host='192.168.40.138',master_port=3306,master_user='repl',master_password='rep123',master_log_file='binlog.000001',master_log_pos=828749,MASTER_HEARTBEAT_PERIOD=10,MASTER_CONNECT_RETRY=10, MASTER_RETRY_COUNT=86400;
mysql> start slave;
mysql> show slave status\G
----------------------------------------------
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- 備庫測試
root@127.0.0.1 : test【10:29:27】14 SQL->insert into test_scheduler(sch_name,sch_createtime) values('sch02',now());
root@127.0.0.1 : test【10:29:31】15 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-09-20 09:17:57 |
| 1003 | sch01 | 2019-09-20 09:18:00 |
| 1004 | sch02 | 2019-09-20 10:29:31 |
+---------+----------+---------------------+
14.主庫測試
root@127.0.0.1 : test【10:28:03】32 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-09-20 09:17:57 |
| 1003 | sch01 | 2019-09-20 09:18:00 |
| 1004 | sch02 | 2019-09-20 10:29:31 |
+---------+----------+---------------------+
root@127.0.0.1 : test【10:31:05】33 SQL->insert into test_scheduler(sch_name,sch_createtime) values('sch01',now());
root@127.0.0.1 : test【10:31:34】34 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-09-20 09:17:57 |
| 1003 | sch01 | 2019-09-20 09:18:00 |
| 1004 | sch02 | 2019-09-20 10:29:31 |
| 1005 | sch01 | 2019-09-20 10:31:34 |
+---------+----------+---------------------+
- 備庫查詢
root@127.0.0.1 : test【10:29:42】16 SQL->select * from test_scheduler;
+---------+----------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+----------+---------------------+
| 1001 | sch01 | 2019-09-20 09:17:57 |
| 1003 | sch01 | 2019-09-20 09:18:00 |
| 1004 | sch02 | 2019-09-20 10:29:31 |
| 1005 | sch01 | 2019-09-20 10:31:34 |
+---------+----------+---------------------+
- 开启事件调度器(兩台)
在 my.cnf 的 mysqld 下配置 event_scheduler=1
[root@XAG138 binlog]# cat /etc/my.cnf | grep event_scheduler
event_scheduler=1
#重啟MYSQL
[root@XAG137 src]# service mysqld restart
root@127.0.0.1 : (none)【10:54:45】1 SQL->SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
root@127.0.0.1 : (none)【10:54:47】2 SQL->SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
- 创建存储过程（主機137）
#存储过程
DROP PROCEDURE IF EXISTS proc_test_scheduler;
DELIMITER $$
create procedure proc_test_scheduler()
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
begin
delete from test_scheduler where sch_createtime<date_sub(now(),interval 5 minute);
insert into test_scheduler(sch_name,sch_createtime) values(uuid(),now());
end$$
DELIMITER ;
#存储过程
DROP PROCEDURE IF EXISTS proc_test_scheduler2;
DELIMITER $$
create procedure proc_test_scheduler2()
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
begin
delete from test_scheduler where sch_createtime<date_sub(now(),interval 5 minute);
insert into test_scheduler(sch_name,sch_createtime) values(@@server_id,now());
end$$
DELIMITER ;
- 主備都調用 存儲過程測試
root@127.0.0.1 : test【10:57:42】18 SQL-> call proc_test_scheduler();
#兩邊都調用後查詢
root@127.0.0.1 : test【10:58:35】19 SQL->select * from test_scheduler;
+---------+--------------------------------------+---------------------+
| sch_seq | sch_name | sch_createtime |
+---------+--------------------------------------+---------------------+
| 1007 | 89aedcbc-db52-11e9-87ce-000c2929f2d3 | 2019-09-20 10:58:35 |
| 1008 | 917cd212-db52-11e9-aa58-000c29c345d6 | 2019-09-20 10:58:48 |
+---------+--------------------------------------+---------------------+
- 创建事件(主庫)
#每5秒调用一次 proc_test_scheduler()
DROP EVENT IF EXISTS even_test_scheduler;
CREATE EVENT IF NOT EXISTS even_test_scheduler
ON SCHEDULE EVERY 5 SECOND
DO call proc_test_scheduler();
19.1 创建事件(備庫)
#每6秒调用一次 proc_test_scheduler2()
DROP EVENT IF EXISTS even_test_scheduler2;
CREATE EVENT IF NOT EXISTS even_test_scheduler2
ON SCHEDULE EVERY 6 SECOND
DO call proc_test_scheduler2();
19.2 查詢事件狀態
#主庫（137）
root@127.0.0.1 : test【11:21:17】14 SQL->SELECT EVENT_SCHEMA,EVENT_NAME,EVENT_DEFINITION,INTERVAL_FIELD,STATUS,LAST_EXECUTED FROM information_schema.EVENTS\G
*************************** 1. row ***************************
EVENT_SCHEMA: test
EVENT_NAME: even_test_scheduler
EVENT_DEFINITION: call proc_test_scheduler()
INTERVAL_FIELD: SECOND
STATUS: ENABLED
LAST_EXECUTED: 2019-09-20 11:22:53
*************************** 2. row ***************************
EVENT_SCHEMA: test
EVENT_NAME: even_test_scheduler2
EVENT_DEFINITION: call proc_test_scheduler2()
INTERVAL_FIELD: SECOND
STATUS: SLAVESIDE_DISABLED
LAST_EXECUTED: NULL
#備庫（138）
root@127.0.0.1 : test【11:22:57】10 SQL->SELECT EVENT_SCHEMA,EVENT_NAME,EVENT_DEFINITION,INTERVAL_FIELD,STATUS,LAST_EXECUTED FROM information_schema.EVENTS\G
*************************** 1. row ***************************
EVENT_SCHEMA: test
EVENT_NAME: even_test_scheduler
EVENT_DEFINITION: call proc_test_scheduler()
INTERVAL_FIELD: SECOND
STATUS: SLAVESIDE_DISABLED
LAST_EXECUTED: NULL
*************************** 2. row ***************************
EVENT_SCHEMA: test
EVENT_NAME: even_test_scheduler2
EVENT_DEFINITION: call proc_test_scheduler2()
INTERVAL_FIELD: SECOND
STATUS: ENABLED
LAST_EXECUTED: 2019-09-20 11:23:57
20.事件總結
新创建的event在master上的状态是ENABLED，在slave上的状态是SLAVESIDE_DISABLED。
另外，在主库上修改event的status状态也不会改变slave上面的状态。
总结一下主从对event_scheduler的影响：
1. 在主库上新建event，在slave上event的状态为SLAVESIDE_DISABLED没有影响。
2. 通过xtrabackup恢复出来的从库，如果有events那么需要在slave上把event_scheduler设置为off，并且检查 events的status状态,如果是enable，则需要关闭事件调度器。
3. 当主从发生切换时，需要人工（或者有配套的机制）来维护event的状态。新主库
# 新主库
set global event_scheduler=on;
alter event e_insert enable;
# 新从库
set global event_scheduler=off;
alter event e_insert disable on slave;
4. 在主库上对event的进行状态修改不影响从库的状态。
