Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the MHA parameters of MYSQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the MHA parameters of MYSQL".

Local: refers to each configuration block inside. The parameters of the Local function need to be placed under the [server_xxx] block

App: parameters act on master/slave. These parameters need to be configured under [server_default] block.

Global: acting on master/slave, parameters at the Global level are used to manage multiple sets of master/slave structures, and some parameters can be managed uniformly.

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.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Hostname Yes Local Only-hostname=mysql_server1, hostname=192.168.0.1, etc

Ip

The ip address of the MySQL server. It is obtained by default from gethostname ($hostname). By default, you do not need to configure this parameter. MHA can be obtained automatically through hostname. MHA connects to the MySQL server and SSH through the IP address.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ip No Local Only gets ip=192.168.1.3 through gethostbyname ($hostname)

Port

The port number on which the MySQL is running. The default is 3306. MHA uses IP and port number to connect to MySQL

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Port No Local/App/Glbal 3306 port=3306

Ssh_host

(supported after MHA 0.53) MHA requires the MySQL target server on ssh to use hostname or ip address. This parameter is mainly used in environments where multiple VLAN are used. Ssh is not allowed by default for security reasons. By default, this parameter is the same as hostname.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ssh_host No Local Only and hostname are the same ssh_host=mysql_server1, ssh_host=192.168.0.1, etc

Ssh_ip

(supported after MHA 0.53) has the same effect as ssh_host. The default is gethostname ($ssh_host) to get.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ssh_ip No Local Only gethostbyname ($ssh_host) ssh_ip=192.168.1.3

Ssh_port

(supported after MHA 0.53) the port number used by SSH. The default is 22. 0.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ssh_port No Local/App/Global 22 ssh_port=22

Ssh_connection_timeout

(supported after MHA 0.54) the default is 5 seconds. The ssh timeout is written dead before this parameter is added.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ssh_connection_timeout No Local/App/Global 5 ssh_connect_timeout=5

Ssh_options

(support from MHA 0.53) add ssh command line support parameters, such as key support with a special file name, and so on.

Such as:

Example and explanation of whether the parameter name must be scoped by 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 slave, and some hope to upgrade to a new Master in the event of Master failure (for example, Raid1's slave is more suitable for Master than Raid0's slave).

The purpose of this parameter is that when the design candidate_master = 1, the server has a higher priority to upgrade to the new master (also have: turn on binlog, replication without delay). Therefore, when the machine with candidate_master = 1 is set to become the new master when master fails. But this is a useful parameter for setting priority.

If caddidate_master = 1 for multiple machines is set, the priority policy depends on the block name ([server_xxx]). [server_1] the title is higher than [server_2].

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Candidate_master No Local Only 0 candidate_mast=1

No_master

When a server with no_master = 1 is set, the server will never be upgraded to the new master. This parameter data is useful for machines that never expect to be master. For example, you may need to set no_master = 1 on a machine that uses raid0 or you want to run a slave in a remote idc. Note: when there is no machine that can be the new master, the MHA just comes out and stops monitoring and master failover at the same time.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

No_master No Local Only 0 no_master=1

Ignore_fail

By default, MHA manager does not fail over Master in the event of a slave failure (no longer able to connect through SSH/MySQL, or SQL Thread has stopped making an error, for other reasons) (MHA manager exits when slave is not present). But in some cases you expect to fail over if there is a problem with the slave. So when ignore_fail = 1 is set, MHA will fail over when all machines have problems. The default is 0.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ignore_fail No Local Only 0 ignore_fail=1

# skip_init_ssh_check#

Skip the ssh check when MHA manager starts.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Skip_init_ssh_check No Local Only 0 skip_init_ssh_check=1

Skip_reset_slave

Execute RESET SLAVE (ALL) on the new master after Master failover.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Skip_reset_slave No Local/App/Global 0 skip_reset_slave=1

User

The user name used to manage the MySQL. This finally needs the root user, because it needs to execute: stop slave; change master to, reset slave. Default: root

Such as:

Example and explanation of whether the parameter name must be scoped by default value

User No Local/App/Global root user=mysql_root

Password

The password of the administrative user of the MySQL. Empty by default

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Password No Local/App/Global empty password=rootpass

Repl_user

The user used by MySQL for replication is also the user used to generate each slave of CHANGE MASTER TO. This user must have REPLICATION SLAVE permissions on the new Master. By default, repl_user will run show slave status to get it on the machine that will become master.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

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 password used for the current replication. When you use online_master_switch, when using-orig_master_is_new_slave (the original Master becomes a slave of the new Master), synchronization will fail without repl_password. Because the user name and password used for replication on the current master are empty (MHA executes change master to on the original Master without the replication password, although the replication password is set on other slave)

Such as:

Example and explanation of whether the parameter name must be scoped by default value

The password repl_password=replpas currently used for repl_password No Local/App/Global replication

Disable_log_bin

When this parameter is set, no binary log will be generated when the differential relay log is applied in slave. The internal implementation is implemented through the disable-log-bin of mysqlbinlog.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Disable_log_bin No Local/App/Global 0 disable_log_bin=1

Master_pid_file

Specifies the pid file for MySQL. This parameter is useful when running multiple MySQL service processes on a single server.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Master_pid_file No Local/App/Global-master_pid_file=/var/lib/mysql/master1.pid

Ssh_user

MHA Mananger, the user on the MHA node system. This account requires execution permission (Manager- > MySQL) on the remote machine to copy the relay-log of differences between slave members (MySQL- > MySQL).

This user must have access to MySQL's binary/relay log and relay_log.info, as well as write access to the remote_workdir directory on the remote MySQL.

This user must also be able to ssh directly to the remote machine, which is recommended to use ssh pbulic key. The commonly used ssh_user is also the user running manager.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

System user ssh_user=root currently used by ssh_user No Local/App/Global

Remote_workdir

The full path name of the working directory on MHA node. If it does not exist, MHA node is automatically created, and if creation is not allowed, MHA Node automatically exits abnormally. Note that you need to check whether space is available on MHA manager or MHA node, so you need to check this. Generally, by default, remote_workdir is "/ var/tmp"

Such as:

Example and explanation of whether the parameter name must be scoped by default value

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 when the mysql on master is dead and to find the required binary log events. This parameter can be used to help you find the storage location of the binary log after the master process dies.

General: master_binlog_dir is "/ var/lib/mysql/, / var/log/mysql". "/ var/lib/mysql/" is the location for most system distributions, and "/ var/log/mysql" is the location for ubuntu distributions. You can also set multiple storage locations separated by commas.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Master_binlog_dir No Local/App/Gobal / var/lib/mysql master_binlog_dir=/data/mysql1,/data/mysql2

Log_level

Sets the level at which MHA manager records LOG. The default is the info level and in most cases it is appropriate. It can also be set to: debug/info/warning/error.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Log_level No App/Global info log_level=debug

Manager_workdir

Used to specify the full path of the relevant state file generated by mha manager. If not, the default is / var/tmp.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Manager_workdir No App / var/tmp manager_workdir=/var/log/masterha

Manager_log

The file name log file that specifies the absolute path to the mha manager. If MHA Manager is not set, it will print to STDOUT/STDERR. When performing a manual failover (interactive mode switchover), MHA Manager ignores the manager_log setting and outputs the log directly to STDOUT/STDERR.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

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 (more than 100m relay log needs to be applied), MHA will not select this slave as the new master when failing over, because recovery takes a long time. When check_repl_delay = 0 is set, MHA ignores synchronization delays on the selected slave. This option is especially useful when candidate_master = 1 is set to declare that the machine is expected to be master.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Check_repl_delay No App/Golbal 1 check_repl_delay=0

Check_repl_filter

By default, when master and slave set different binary log/replication filtering rules, MHA directly reports errors without 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 in the recovery, set check_repl_filter=0. It is important to note that when check_repl_filter = 0 is used, MHA does not check the relay logs where the filtering rules are applying differences, so a "Table not exists" error may occur. Please handle this parameter carefully when you set it.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Check_repl_filter No App/Global 1 check_repl_filter=0

Latest_priority

By default, the slave closest to Master (an slave gets the most binlog event from Master) has the highest priority to become the new master. If you want to control the switching strategy (for example, choose host2 first, if not, choose host3;host3, choose host4. ) then just set latest_priority = 0.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Latest_priority No App/Global 1 latest_priority=0

Multi_tier_slave

Starting with MHA 0.52, multi-tier replication can be supported. By default, replication configurations with three or more tiers are not supported. If: host2 is copied from host1, host3 is copied from host2. In the default configuration, it is not supported to write host {1JING 2JO 3}, because this is a three-tier copy, MHA Manager will stop reporting errors. When multi_tier_slave is set, MHA Manager will not stop replicating errors at layer 3. But ignore the machines on the third floor. That is, if host1 dies, host2 will become the new master,host3 or will it be copied from host2.

This parameter is supported in versions after MHA Manager 0.52.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Muli_tier_slave No App/Global 0 multi_tier_slave=1

Ping_interval

This parameter sets how long MHA Manager will ping the master (execute some SQL statements). When losing and master try three times, MHA Manager will think that 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, the master will be considered dead without retrying.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Ping_interval No App/Global 3 ping_interval=5

Ping_type

(supported after MHA 0.53) by default, MHA manager and MySQL create a connection to execute "select 1" (ping_type=select) to check whether master is healthy. However, there are some cases: it is better to connect / then disconnect each test, so that the error perception of tcp is faster. Just set ping_type=CONNECT. Pint_type=INSERT has also been added since MHA 0.56.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

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 that are on different routing policies to check whether the MySQL Master is alive. By default, only MHA Manager uses a route to check whether the Master is alive. This is not recommended either. MHA can be checked from multiple routing policies through scripts configured by the external secondary_check_script.

Secondary_check_script = masterha_secondary_check-s remote_host1-s remote_host2

Secondary_check_script is included in the MHA Manager distribution. The secondary_check_script built into MHA works fine in most cases, but this script is not available anywhere.

In the above example, MHA Manager checks for MySQL master survival by Manager- > (A)-> remote_host1- > (B)-> master_host and Manager- > (A)-remote_host2- > (B)-> master_host. If you can all succeed through An and fail through B during the connection process, secondary_\ check_\ script returns 0 and thinks master is dead and fails over. "if you succeed through A, but the return code is: 2, then MHA manager will consider it as a network problem and will not fail over." If A succeeds and B succeeds, masterha_secondary_check exits and returns: 3 then MHA Manager considers MySQL Master to be alive and will not fail over.

Generally speaking, remote_host1 and remote_host2 are located in different network segments from MHA Manager and MySQL Server.

MHA invokes the script declared by secondary_check_script and automatically takes some parameters. Masterha_secondary_check is applicable in many scenarios, but you can also implement this program with more features by yourself.

-user= (SSH user name used on the remote machine. The value of ssh_user will be used

-master_host = (hostname of master)

-master_ip = (ip address of master)

-master_port = (port number of master)

Note: the built-in masterha_secondary_check script relies 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 you need to manager to the remote machine to establish public key trust. In addition, masterha_secondary_check tests whether the Master is alive by establishing a TCP connection with Master, so the max_connections set by mysql does not work. However, each time the TCP connection is successful, the Aborted_connects value of the MySQL is incremented by 1.

Such as:

Example and explanation of whether the parameter name must be scoped by 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

Such as:

Example and explanation of whether the parameter name must be scoped by default value

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, only for switching to master online.

Freeze the process of Master writing:

-command=stop or stopssh

-orig_master_host = (hostname of the current master)

-orig_master_ip = (ip address of the current master)

-orig_master_port = (Port number of the current master)

-orig_master_user = (current master user)

-orig_master_password = (user name of the current master)

-orig_master_ssh_user = (supported from .56, the user name of the ssh of the current master)

-orig_master_is_new_slave = (from .56, whether to change the original Master to the new slave)

The new Master accepts the writing process:

-command=start

-orig_master_host = (machine name of the original master)

-orig_master_ip = (ip of the original master)

-orig_master_port = (port number of the original master)

-new_master_host = (machine name of the new master)

-new_master_ip = (ip of the new master)

-new_master_port = (port number of the new master)

-new_master_user = (user name on the new master)

-new_master_password = (username and password on the new master)

-new_master_ssh_user = (supported from .56, ssh users on the new master)

MHA executes FlUSH TABLES WITH READ LOCK on Master during the freeze write switch process, and there are no writes in this elegant switching process. You can do the same thing as master_ip_failover_script in the process of starting authorizing writing for the new Master. For example: create users and permissions, execute set global read_only=0, update database routing table Zhu. If the script execution exit code is not 0 or 10, the MHA Manager exits abnormally and the master switch does not continue.

By default this parameter is empty, so MHA Manager does nothing by default.

Can be found at (MHA Manager package) / samples/scripts/master_ip_online_change. Find an example script in. The example script is contained in the MHA Manager source file or in a branch of GitHub.

Such as:

Example and explanation of whether the parameter name must be scoped by 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

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Shutdown_script No App/Global null shutdown_script= / usr/local/custom_script/master_shutdown

Report_script

After the Master failure is complete, you may want to send a report (such as email) to report the completion of the handover or the error 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 list of slave machine names, separated by commas)

-subject = (email name)

-body = (body)

By default, these parameters are empty. So by default MHA Manager does nothing.

Example scripts can be found in (MHA Manager package) / samples/scripts/send_report. The example script is contained in the MHA Manager source file or in a branch of GitHub.

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Report_script No App/Global null report_script= / usr/local/custom_script/report

Init_conf_load_script

This parameter is used to avoid setting plaintext in the configuration file (e. G. password dependent). 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_PASS\ n"

Print "repl_password=$REPL_PASS\ n"

Such as:

Example and explanation of whether the parameter name must be scoped by default value

Init_conf_load_script No App/Global null report_script= / usr/local/custom_script/init_conf_loader

Thank you for your reading, the above is the content of "what are the MHA parameters of MYSQL". After the study of this article, I believe you have a deeper understanding of what the MHA parameters of MYSQL have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.