MYSQL HA 之02(MYSQL5.7双主配置)

作者: 轻飘飘D | 来源:发表于2019-09-26 10:00 被阅读0次

统一设置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:245 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的进行状态修改不影响从库的状态。

MYSQL HA 之02(MYSQL5.7双主配置)

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读