MySQL5.7+MHA+Keepalived failover自动更换
发布时间：2022-03-29 09:13:04 所属栏目：MySql教程 来源：互联网
导读：数据库架构：一主两从 master：192.168.8.57 slave1：192.168.8.58 slave2：192.168.8.59 manager：192.168.8.60 MHA工具包： mha4mysql-manager-0.58.tar.gz mha4mysql-node-0.58.tar.gz keepalived-1.4.5.tar.gz 一、环境配置过程如下： http://blog.itp
|
数据库架构：一主两从 master：192.168.8.57 slave1：192.168.8.58 slave2：192.168.8.59 manager：192.168.8.60 MHA工具包： mha4mysql-manager-0.58.tar.gz mha4mysql-node-0.58.tar.gz keepalived-1.4.5.tar.gz 一、环境配置过程如下： http://blog.itpub.net/30135314/viewspace-2217762/ 二、切换测试 1.在192.168.8.57关闭MySQL进程 1 mysqladmin -uroot -pmysql shutdown 2.查看MHA manager日志 From: 192.168.8.57(192.168.8.57:3306) (current master) +--192.168.8.58(192.168.8.58:3306) +--192.168.8.59(192.168.8.59:3306) To: 192.168.8.58(192.168.8.58:3306) (new master) +--192.168.8.59(192.168.8.59:3306) Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 3.3: New Master Recovery Phase.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] Waiting all logs to be applied.. Mon Oct 29 13:55:24 2018 - [info] done . Mon Oct 29 13:55:24 2018 - [info] Getting new master's binlog name and position.. Mon Oct 29 13:55:24 2018 - [info] mysql-bin.000021:415 Mon Oct 29 13:55:24 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST= '192.168.8.58' , MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER= 'repl' , MASTER_PASSWORD= 'xxx' ; Mon Oct 29 13:55:24 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000021, 415, a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-7 Mon Oct 29 13:55:24 2018 - [info] Executing master IP activate script: Mon Oct 29 13:55:24 2018 - [info] /usr/local/bin/master_ip_failover -- command =start --ssh_user=root --orig_master_host=192.168.8.57 --orig_master_ip=192.168.8.57 --orig_master_port=3306 --new_master_host=192.168.8.58 --new_master_ip=192.168.8.58 --new_master_port=3306 --new_master_user= 'root' --new_master_password=xxx Undefined subroutine &main::FIXME_xxx_create_user called at /usr/local/bin/master_ip_failover line 94. Set read_only=0 on the new master. Creating app user on the new master.. Mon Oct 29 13:55:24 2018 - [error][ /usr/lib/perl5/vendor_perl/MHA/MasterFailover .pm, ln1612] Failed to activate master IP address for 192.168.8.58(192.168.8.58:3306) with return code 10:0 Mon Oct 29 13:55:24 2018 - [warning] Proceeding. Mon Oct 29 13:55:24 2018 - [info] ** Finished master recovery successfully. Mon Oct 29 13:55:24 2018 - [info] * Phase 3: Master Recovery Phase completed. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 4: Slaves Recovery Phase.. 3.查看VIP 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link /loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1 /8 scope host lo valid_lft forever preferred_lft forever inet6 ::1 /128 scope host valid_lft forever preferred_lft forever 2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link /ether 08:00:27:4d:70:17 brd ff:ff:ff:ff:ff:ff inet 192.168.8.58 /24 brd 192.168.8.255 scope global noprefixroute enp0s3 valid_lft forever preferred_lft forever inet 192.168.8.88 /24 scope global secondary enp0s3 valid_lft forever preferred_lft forever inet6 fe80::6a31:3e92:8b6f:83c0 /64 scope link noprefixroute valid_lft forever preferred_lft forever inet6 fe80::5198:593b:cdc5:1f90 /64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever 3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000 link /ether 52:54:00:f4:55:bb brd ff:ff:ff:ff:ff:ff inet 192.168.122.1 /24 brd 192.168.122.255 scope global virbr0 valid_lft forever preferred_lft forever 4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000 link /ether 52:54:00:f4:55:bb brd ff:ff:ff:ff:ff:ff VIP192.168.8.88/24已经飘至192.168.8.58机器。 4.查看slave进程 192.168.8.58 mysql> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.58 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 415 Relay_Log_File: slave2-relay-bin.000002 Relay_Log_Pos: 414 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 415 Relay_Log_Space: 622 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 58 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450f Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450b:1-4, a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-7 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 可以看到从库192.168.8.59的主库变成192.168.8.58 5.测试复制 192.168.8.58（主库） mysql> create table t10 (id int (10)); Query OK, 0 rows affected (0.19 sec) mysql> show tables; + ----------------+ | Tables_in_test | + ----------------+ | t1 | | t10 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t8 | | t9 | + ----------------+ 192.168.8.59（从库） mysql> show tables; + ----------------+ | Tables_in_test | + ----------------+ | t1 | | t10 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t8 | | t9 | + ----------------+ 新的主从数据复制正常。 三、将旧主库加入复制环境 修复完192.168.8.57之后，将此节点变成从库加入到环境当中，可以直接change master to （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号