Detailed explanation of MHA parameters of MySQL high availability component

MHA is currently a relatively mature solution for MySQL high availability. It was developed by the Japanese and is an excellent MySQL high availability environment. High-availability software for failover and master-slave promotion. MHA provides a series of configuration parameters. An in-depth understanding of the specific meaning of each parameter is very important for optimizing configuration and rational use of MHA. Many high availability are also achieved by properly configuring some parameters. Below we will introduce in detail some parameters that may be used when using MHA~
Local: refers to the inside of each configuration block. The parameters of the Local function need to be placed under the [server_xxx] block
App: the parameters act on master/slave, these parameters need to be configured under the [server_default] block
Global: act on the master/slave, Global level parameters It is used to manage multiple sets of master/slave structures and can manage some parameters in a unified manner.
Hostname: Configure the machine name or IP address of the MySQL server. This configuration item is required and can only be configured under the [server_xxx] block.
For example: Parameter name Is it required? Scope Default value Example and explanation
hostname Yes Local Only – hostname=mysql_server1, hostname=192.168.0.1, etc
ip: The ip address of the MySQL server. By default, it is obtained from gethostname($hostname). By default, this parameter does not need to be configured. MHA can be automatically obtained through hostname. MHA connects to the MySQL server and SSH connection through the IP address.
For example:
Is the parameter name required? Scope Default value Example and description
ip No Local Only Obtained through gethostbyname($hostname) ip=192.168.1.3
port: The port number where MySQL runs. The default is 3306. MHA uses IP and port number to connect to MySQL
For example:
Whether the parameter name must be scoped and default value example and explanation
port No Local/App/Glbal 3306 port=3306
ssh_host: MHA requires ssh to use the hostname or IP address of the MySQL target server. This parameter is mainly used in environments using multiple VLANs. For security reasons ssh is not allowed by default. By default, this parameter is the same as hostname.
For example:
Whether the parameter name must be scoped, default value example and explanation
ssh_host No Local Only Same as hostname ssh_host=mysql_server1, ssh_host=192.168.0.1, etc
ssh_ip: (from MHA 0.53) Starting to support) has the same function as ssh_host. The default is gethostname($ssh_host).
For example:
Whether the parameter name must be scoped, default value example and explanation
ssh_ip No Local Only gethostbyname($ssh_host) ssh_ip=192.168.1.3
ssh_port: (Supported since MHA 0.53) SSH The port number used, the default is 22.
For example:
Whether the parameter name must be scoped, default value example and description
ssh_port No Local/App/Global 22 ssh_port=22
ssh_connection_timeout: (from MHA Supported after 0.54) The default is 5 seconds. Before adding this parameter, the ssh timeout was hard-coded.
For example:
Whether the parameter name must be scoped, default value example and description
ssh_connection_timeout No Local/App/Global 5 ssh_connect_timeout=5
ssh_options: (Supported from MHA 0.53) Add ssh command line Support parameters, such as support for keys with special file names, etc.
For example:
Examples and explanations of whether the parameter name must be scoped and default value
ssh_options No Local/App/Global “” Empty ssh_options="-i /root/.ssh/id_dsa2″
candidate_master : You may have different plans for the same group of slaves, and some hope to be promoted to a new Master when the Master fails (for example: the slave of Raid1 is more suitable to be the master than the slave of Raid0)
The function of this parameter is When candidate_master = 1 is designed, this server has a higher priority and is promoted to the new master (it must also have: binlog is turned on, and there is no delay in replication). So when the machine with candidate_master = 1 is set, it will inevitably become the new master when the master fails. But this is a very useful parameter to set priority.
If caddidate_master = 1 is set for multiple machines, the priority strategy depends on the block name ([server_xxx]). [server_1] has a higher priority than [server_2].
For example:
Is the parameter name required? Scope default value examples and explanations
candidate_master No Local Only 0 candidate_mast＝1
no_master
When a server with no_master = 1 is set, this server will never be promoted to a new master. This parameter data is always Useful for machines that are not expected to be masters. For example: You may need to set no_master = 1 on the machine using raid0 or you want to run a slave in the remote IDC. Note: When there is no machine that can become the new master, MHA will exit directly and stop monitoring and master. Failover.
For example:
Parameter name Whether it is required Scope Default value Example and explanation
no_master No Local Only 0 no_master=1
ignore_fail: By default, MHA manager will not perform Master failover when the slave is faulty (it can no longer connect through SSH/MySQL, or the SQL Thread has stopped erroring, or other reasons). MHA manager will also exit when slave exists). But in some cases you want to perform failover even if there is a problem with the slave. So when ignore_fail = 1 is set, MHA will failover when all machines have problems. The default is 0.
For example:
Is parameter name required? Scope Default value Example and description
ignore_fail No Local Only 0 ignore_fail=1
skip_init_ssh_check: Skip ssh check when MHA manager starts.
For example:
Whether the parameter name must be scoped and default value example and description
skip_init_ssh_check No Local Only 0 skip_init_ssh_check=1
skip_reset_slave: (Supported from MHA 0.56) New master after Master failover Execute RESET SLAVE(ALL).
For example:
Whether the parameter name must be scoped and default value example and description
skip_reset_slave No Local/App/Global 0 skip_reset_slave=1
user: used to manage MySQL username. It is best to use the root user for this, because it needs to execute: stop slave; change master to, reset slave. Default: root
For example:
Whether the parameter name must be scoped, default value example and explanation
user No Local /App/Global root user=mysql_root
password: The password of the MySQL administrative user. The default is empty
For example:
Whether the parameter name must be scoped, default value example and description
password No Local/App/Global empty password=rootpass
repl_user: MySQL user used for replication, It is also used to generate CHANGE MASTER TO users used by each slave. This user must have REPLICATION SLAVE permissions on the new Master. By default, repl_user will be obtained by running show slave status on the machine that will become the master in the future.
For example:
Parameter name Whether it is required Scope Default value Example and description
repl_user No Local/App/Global From show slave status repl_user=repl
repl_password: The password of the repl_user user in MySQL. The default is the current password used for copying. When you use online_master_switch, when you use –orig_master_is_new_slave (the original Master becomes a slave of the new Master), synchronization will fail if repl_password is not turned on. Because the username and password used for replication on the current master are empty (MHA does not have a replication password when executing change master to on the original Master, although replication passwords are set on other slaves)
For example:
Whether the parameter name must be scoped, default value example and explanation
repl_password No Local/App/Global The current password for replication repl_password=replpas
disable_log_bin: When this parameter is set, the differential relay log will not be applied to the slave. Binary logs will be generated. The internal implementation is implemented through disable-log-bin of mysqlbinlog.
For example:
Parameter name Whether it is required Scope Default value Example and description
disable_log_bin No Local/App/Global 0 disable_log_bin=1
master_pid_file: Specify the pid file of MySQL. This parameter is very useful when running multiple MySQL service processes on a server.
For example:
Example and description of whether the parameter name must be scoped and default value
master_pid_file No Local/App/Global – master_pid_file=/var/lib/mysql/master1.pid
ssh_user: MHA Mananger, User on the MHA node system. This account needs to have execution permissions on the remote machine (Manager->MySQL), and copy the relay-log of differences between slave members (MySQL->MySQL)
This user must have binary/relay to read MySQL Permissions for logs and relay_log.info also require write permissions for the remote_workdir directory on the remote MySQL.
This user must also be able to ssh directly to the remote machine. It is recommended to use ssh pbulic key. The generally used ssh_user is also the user who runs the manager.
For example:
Whether the parameter name must be scoped, default value example and explanation
ssh_user No Local/App/Global The currently used system user ssh_user=root
remote_workdir: the full path of the working directory on the MHA node name. If it does not exist, the MHA node will be automatically created. If creation is not allowed, the MHA Node will automatically exit abnormally. It should be noted that the MHA manager or MHA node needs to check whether the space is available, so you need to pay attention to check this. Generally, by default, remote_workdir is "/var/tmp"
For example:
Whether the parameter name must be scoped, default value example and explanation
remote_workdir No Local/App/Global /var/tmp remote_workdir=/var/ log/masterha/app1
master_binlog_dir: The full path used to store binary logs on the master. This parameter is used to connect to the mysql server through ssh after mysql on the master dies, and find the required binary log events. This parameter can help when the binary log storage location cannot be found after the master process dies.
General: master_binlog_dir is "/var/lib/mysql/, /var/log/mysql". "/var/lib/mysql/" is the storage location of most system distributions, "/var/log/mysql " is the storage location of the ubuntu distribution version. You can also set multiple storage locations separated by commas.
For example:
Whether the parameter name must be scoped, default value example and description
master_binlog_dir No Local/App/Gobal /var/lib/mysql master_binlog_dir=/data/mysql1,/data/mysql2
log_level: Set the level at which MHA manager records LOG . The default is info level and is more suitable in most situations. It can also be set to: debug/info/warning/error.
For example:
Whether the parameter name must be scoped and default value example and description
log_level No App/Global info log_level=debug
manager_workdir: use Generate the full path of the relevant status file for the specified mha manager. If not set, the default is /var/tmp
For example:
Whether the parameter name must be scoped, default value example and explanation
manager_workdir No App /var/tmp manager_workdir=/var/log/masterha
manager_log : Specify the file name of the absolute path of the mha manager log file. If MHA Manager is not set, it will print to STDOUT/STDERR. When manually performing failover (interactive mode switch), MHA Manager will ignore the manager_log setting and directly output the log to STDOUT/STDERR.
For example:
Whether the parameter name must be scoped and default value example and explanation
manager_log No App STDERR manager_log=/var/log/masterha/app1.log
check_repl_delay: By default, when a slave synchronization delay exceeds 100M relay log (need to apply more than 100M relay log), MHA will not perform failover when doing failover This slave is selected as the new master because recovery takes a long time. When check_repl_delay = 0 is set, MHA will ignore the synchronization delay on the selected slave. This option is particularly useful when setting candidate_master = 1 specifically states that this machine is expected to become the master.
For example:
Whether the parameter name must be scoped, default value example and explanation
check_repl_delay No App/Golbal 1 check_repl_delay=0
check_repl_filter: By default, when the master and slave settings are different, the binary When filtering rules in log/replication, MHA will directly report an error and will not perform monitoring and failover. These will lead to some unexpected errors "Table not exists". If you are 100% sure that different filtering rules will not cause errors during recovery, set check_repl_filter=0. Note: When check_repl_filter = 0 is used, MHA will not check the relay log of application differences of filter rules, so "Table not exists" errors may occur. Please handle it with care when you set this parameter.
For example:
Whether the parameter name must be scoped, default value example and explanation
check_repl_filter No App/Global 1 check_repl_filter=0
latest_priority: By default, the slave closest to the Master (a slave Obtained the most binlog event from the Master) has the highest priority to become the new master. If you want to control the switching strategy (for example: select host2 first, if that doesn't work, select host3; if host3 doesn't work, select host4...), then set latest_priority = 0.
For example:
Whether the parameter name must be scoped, default value example and explanation
latest_priority No App/Global 1 latest_priority=0
multi_tier_slave:
Starting from MHA 0.52, multi-tier replication can be supported . By default, replication configurations with three or more layers are not supported. If: host2 copies from host1, host3 copies from host2. In the default configuration, writing host{1,2,3} is not supported because this is a three-layer replication and MHA Manager will stop reporting errors. When multi_tier_slave is set, MHA Manager will not stop with an error at three-tier replication. But the machines on the third level will be ignored. That is to say, if host1 fails, host2 will become the new master, and host3 will still copy from host2.
This parameter is supported in MHA Manager versions after 0.52.
For example:
Whether the parameter name must be scoped, default value example and description
muli_tier_slave No App/Global 0 multi_tier_slave=1
ping_interval: This parameter sets how long it takes for MHA Manager to ping the master (execute Some SQL statements). After three attempts with the master are lost, the MHA Manager will think that the MySQL Master is dead. In other words, the maximum failover time is 4 ping_interval times, and the default is 3 seconds.
If MHA Manager receives multiple connection errors or authentication errors when creating a connection with MySQL, it will think that the master has died without retrying.
For example:
Whether the parameter name must be scoped, default value example and explanation
ping_interval No App/Global 3 ping_interval=5
ping_type: (Supported from MHA 0.53 onwards) By default, MHA The manager and MySQL create a connection and execute "select 1" (ping_type=select) to check whether the master is healthy. But there are some situations: It would be better to connect/then disconnect every time, so that TCP errors can be detected faster. Just set ping_type=CONNECT. Since MHA 0.56 pint_type=INSERT has also been added.
For example:
Is the parameter name required? Scope Default value Example and description
ping_type No App/Global SELECT ping_type=CONNECT
secondary_check_script: Generally speaking, it is highly recommended to use more machines on the network with different routing strategies to check whether MySQL Master is alive. By default, only the MHA Manager checks whether the Master is alive through a route. This is also not recommended. MHA can check multiple routing policies through the external secondary_check_script configured script.
secondary_check_script = masterha_secondary_check -s remote_host1 -s remote_host2
secondary_check_script is included in the MHA Manager distribution package. The built-in secondary_check_script in MHA works well in most cases, but it is not available everywhere.
In the above example, MHA Manager passes Manager->(A)->remote_host1->(B)->master_host and Manager->(A)-remote_host2->(B)- >master_host to check whether the MySQL master is alive. If everything through A can succeed during the connection process, but everything through B fails, the secondary_check_script returns 0, and the master is considered dead and a failover is performed. If A is passed successfully, but the return code is: 2, the MHA manager may think it is a network problem and will not perform failover. If A succeeds and B also succeeds, masterha_secondary_check exits and returns: 3. Then MHA Manager considers MySQL Master to be alive and will not perform failover.
Generally speaking, remote_host1 and remote_host2 are located in different network segments from MHA Manager and MySQL Server.
MHA will call the script declared by secondary_check_script and automatically bring some parameters. masterha_secondary_check is applicable in many scenarios, but you can also implement this program yourself with more functions.
–user=(SSH username to use on the remote machine. The value of ssh_user will be used)
–master_host = (master’s hostname)
–master_ip = (master’s ip address)
–master_port = (master’s port number)
masterha_secondary_check: The script depends on Perl’s IO::Socket::INET (this module is supported by default in Perl 5.6.0). masterha_secondary_check needs to connect to the remote machine through ssh, so the manager needs to establish public key trust on the remote machine. In addition, masterha_secondary_check tests whether the Master is alive by establishing a TCP connection with the Master, so the max_connections set by mysql does not work. But every time the TCP connection is successful, the Aborted_connects value of MySQL will be increased by 1.
For example:
Example and description of whether the parameter name must be scoped and default value
secondary_check_script No App/Global null secondary_check_script= masterha_secondary_check -s remote_dc1 -s remote_dc2
master_ip_failover_script:
For example:
Whether the parameter name must be scoped, default value example and description
master_ip_failover_script No App/Global null master_ip_failover_script=/usr/local/custom_script/master_ip_failover
master_ip_online_changes_script: This parameter is somewhat similar to master_ip_failover_script, but this parameter is not used for master failover Note that only switching to master online is used.
Freeze the Master writing process:
–command=stop or stopssh
–orig_master_host = (the host name of the current master)
–orig_master_ip = (the ip address of the current master)
–orig_master_port = (Port number of the current master)
–orig_master_user = (User of the current master)
–orig_master_password = (User name of the current master)
–orig_master_ssh_user = (Supported from 0.56, ssh user of the current master name)
–orig_master_is_new_slave = (From 0.56, whether to change the original Master to a new slave)
The new Master accepts the writing process:
–command=start
–orig_master_host = (Original master’s Machine name)
–orig_master_ip = (original master’s ip)
–orig_master_port = (original master’s port number)
–new_master_host = (new master’s machine name)
–new_master_ip = (new master ip)
–new_master_port = (port number of new master)
–new_master_user = (user name on new master)
–new_master_password = (user name and password on new master)
– new_master_ssh_user = (Supported from 0.56, ssh user on new master)
MHA will execute Flush TABLES WITH READ LOCK on the Master during the freeze write switching process. There will be no writes during this graceful switching process. When the new Master starts authorizing writing, you can do the same thing as master_ip_failover_script. For example: Create users and permissions, execute set global read_only=0, and update the database routing table. If the script execution exit code is not 0 or 10, MHAManager exits abnormally and will not continue to switch to the master.
By default this parameter is empty, so MHA Manager does nothing by default.
Can be found in (MHA Manager package)/samples/scripts/master_ip_online_change. Find the example script here. Example scripts are included in the MHA Manager source files or the GitHub fork.
For example:
Example and description of whether the parameter name must be scoped and default value
master_ip_online_change_script No App/Global null master_ip_online_change_script= /usr/local/custom_script/master_ip_online_change
shutdown_script:
For example:
Whether the parameter name must be in scope, default value, examples and explanations
shutdown_script No App/Global null shutdown_script= /usr/local/custom_script/master_shutdown
report_script:
After the Master failure is completed, you may want to send one. Report (such as email) the completion of the switch or any errors that occurred. report_script can do the job. MHA Manager can be used with the following parameters:
–orig_master_host = (dead master machine name)
–new_master_host　= (new master machine name)
–new_slave_hosts = (new slave machine name list, use commas separated)
–subject = (email name)
–body = (text)
By default, these parameters are empty. So the default MHA Manager does nothing.
The sample script can be found in (MHA Manager package)/samples/scripts/send_report. Example scripts are included in the MHA Manager source files or the GitHub fork.
For example:
Whether the parameter name must be scoped, default value example and explanation
report_script No App/Global null report_script= /usr/local/custom_script/report
init_conf_load_script: This parameter is used if you do not want to set clear text In the configuration file (eg: password related). Just return a value like "name=value". This can be used to override some values in the global configuration. An example script is as follows.
#!/usr/bin/perl
print “password=$ROOT_PASSn”;
print “repl_password=$REPL_PASSn”;
For example:
Example of whether the parameter name must be scoped and default value And description
init_conf_load_script No App/Global null report_script= /usr/local/custom_script/init_conf_loader

Detailed explanation of MHA parameters of MySQL high availability component_PHP tutorial

Detailed explanation of MHA parameters of MySQL high availability component