this project is no longer maintained, all of the features are merged to https://github.com/arstercz/mha4mysql-manager. read more from wiki.
Switch MySQL replication with custom scripts by use MHA, read more from blog.
note:
- all dependecy Perl modules are the same with
MHA, and all of the scripts are based onMHA 0.56version. - the
event_schedulerfeature can be used inMHA 0.58version, otherwise you must give a big enough value to the--running_updates_limitand--running_seconds_limitoption. read more from pull-44 and you can apply this patch into your low versionMHA.
the full code structure:
mha_switch
├── bin
│ ├── init_conf_loads
│ ├── master_ip_failover
│ └── master_ip_online_change
├── LICENSE
├── log
│ └── switch.log
├── masterha
│ ├── app_56.conf
│ ├── app_default.cnf
│ ├── masterha-script.cnf
│ ├── masterha-script.pm
│ └── mha_sudoer
└── README.md
-
the
master_ip_failoverandmaster_ip_online_changeare refer tomha4mysql-manager/samples/scripts/. these path should be the same as the option values in the global default fileapp_default.cnf. -
init_conf_loadscontains the MySQlroot userpassword which inbase64format. -
masterha-script.pmis our custum module that support the following features:
parse masterha-script.cnf file
virtual ip switch,
block/release mysql user
proxysql switch
read more from proxysql.
mha_sudoershould be copy to all of the MySQL Host's/etc/sudoers.d, so thatmha_switchcan switch virtual ip address with normal user(default ismhauser). note: you must comment theDefaults requirettyso that avoid sudo errorsorry, you must have a tty to run sudo.
-
app_56.confis one MySQL replication instances. -
masterha-script.cnfis the MySQL replication info which refers toapp_56.conf, you can specify multigroup if you have multiple MySQL replications. theproxysqlis optional option, you can set multiple proxysqls(master/backup) which split by comma symbol. eg:
# vip and proxysql are optional option, you can ignore if you do not use vip or proxysql
10.0.21.7:3308 10.0.21.17:3308
vip 10.0.21.97
block_user ^percona$|^proxysqlmon$
block_host ^10\.0\.21\.%$
proxysql admin2:admin2@10.0.21.5:6032:w1:r2,admin2:admin2@10.0.21.7:6032:w1:r2
the 10.0.21.7:3308 10.0.21.17:3308 is the master, slave ip address and port, you must specify multi ip:port if you have many slaves; vip 10.0.21.97 means that master services to application by the virtual ip address, MHA will switch the vip address when you use MHA switch the replication, this means the application will do nothing to connect new master only when it has the retry mechanism, vip is optional option, you can ignore this if you do not use vip address; block_user and block_host means MHA will block the user which in old master instance, read more from blocking-user-accounts; the last line proxysql option is optional, you can setup if you use proxysql, the value means there are two proxysqls, include the following proxysql administration info:
proxysql1:
username: admin2
password: admin2
ip: 10.0.21.5
port: 6032 # admin port
write group: 1 # begin with w
read group: 2 # begin with r
proxysql2:
username: admin2
password: admin2
ip: 10.0.21.7
port: 6032 # admin port
write group: 1 # begin with w
read group: 2 # begin with r
the master_ip_failover and master_ip_online_change refer the path /etc/masterha/masterha-script.pm and /etc/masterha/masterha-script.cnf by default, if your masterha dirs not in /etc, then you should change the value in the two scripts.
if we have the following structure:
+------------+ +------------+
| proxysql 1 | 10.0.21.5 | proxysql 2 | 10.0.21.7
+------------+ +------------+
| |
| |
| |
| |
+----------+-------------------------+----------------------+
| |
| +--------+ +-------+ |
| | master | 10.0.21.17:3308 | slave | 10.0.21.7:3308 |
| +--------+ +-------+ |
| |
+-----------------------------------------------------------+
before mha the proxysql runtime_mysql_servers is:
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 10.0.21.17 | 3308 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | |
| 2 | 10.0.21.17 | 3308 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | |
| 2 | 10.0.21.7 | 3308 | ONLINE | 1000 | 0 | 2000 | 30 | 0 | 0 | |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
Execute the following command to switch MySQL recplication and change the proxysql setting:
# masterha_master_switch --master_state=alive --global_conf=/etc/masterha/app_default.cnf --conf=/etc/masterha/app_56.conf --orig_master_is_new_slave
...
...
Thu Nov 16 15:52:00 2017.490641 delete proxysql repl group on 10.0.21.5:6032 ok!
Thu Nov 16 15:52:00 2017.497134 set read_only on proxysql 10.0.21.5:6032 ok!
...
...
Thu Nov 16 15:52:06 2017.306978 Delete old proxysql write group 10.0.21.17:3308 with group 1 ok!
Thu Nov 16 15:52:06 2017.307767 Insert new proxysql write group 10.0.21.7:3308 with group 1 ok!
Thu Nov 16 15:52:06 2017.308202 Insert new proxysql read group 10.0.21.7:3308 with group 2 ok!
Thu Nov 16 15:52:06 2017.312171 Insert orig master as new proxysql read group 10.0.21.17:3308 with group 2 ok!
Thu Nov 16 15:52:06 2017.312908 insert proxysql repl group on 10.0.21.5:6032 ok!
Thu Nov 16 15:52:06 2017.321330 proxysql load server to runtime ok!
Thu Nov 16 15:52:06 2017.348377 proxysql save server to disk ok!
Thu Nov 16 15:52:06 2017.352720 set proxysql 10.0.21.7:6032 readwrite ok!
you can read the log/switch.log to get more message.
after mha the proxysql runtime_mysql_servers is:
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 10.0.21.7 | 3308 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | |
| 2 | 10.0.21.7 | 3308 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | |
| 2 | 10.0.21.17 | 3308 | ONLINE | 1000 | 0 | 2000 | 30 | 0 | 0 | |
+--------------+------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
there is no 10.0.21.17 entries if no --orig_master_is_new_slave option in MHA execute.
MIT / BSD