What problems can MHA failover help us solve? 01/30 Update SLTechnology News&Howtos

What problems can MHA failover help us solve?

2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Let's learn what problems MHA failover can help us solve. I believe you will benefit a lot after reading it. I hope MHA failover can help us solve what problems this short article is you want.

MHA is a set of Mysql failover scheme to ensure the high availability of the database. Its function is to achieve master Mysql failover (failover) within 0-30s. MHA failover can help us solve the problem of data consistency of slave database and maximize the consistency of data after failure.

Operating system version: Red Hat Enterprise Linux Server release 6.9 (Santiago)

192.168.1.11 master

192.168.1.12 slave01

192.168.1.13 slave02

192.168.1.14 manager

The installation begins below:

1. Prepare to install the package, FTP to / usr/local/

1.1 extract the installation package

Tar zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz

Rename

Mv mysql-5.6.39-linux-glibc2.12-x86_64 mysql

1.2 install the auxiliary software yum-y install perl perl-devel

two。 Add user

Groupadd mysql

Useradd-r-g mysql mysql

3. Modify directory permissions

Chown-R mysql.mysql / usr/local/mysql

Mkdir-p / data/mysql

Chown-R mysql.mysql / data/mysql

4. Initialize the database

/ usr/local/mysql/scripts/mysql_install_db-user=mysql--basedir=/usr/local/mysql-datadir=/usr/local/mysql/data

5. Registration service

Cp / usr/local/mysql//support-files/mysql.server/etc/rc.d/init.d/mysql

Cp / usr/local/mysql/support-files/my-default.cnf / etc/my.cnf

Chkconfig-add mysql

Chkconfig mysql on

6. Start the service

Service mysql start

7. Log in without a password for ssh

# Host: master executes commands

[root@master] # ssh-keygen-t rsa

[root@master] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@manager

[root@master] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@slave01

[root@master] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@slave02

# Host: slave01 executes commands

[root@slave01] # ssh-keygen-t rsa

[root@slave01] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@manager

[root@slave01] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@master

[root@slave01] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@slave02

# Host: slave02 executes commands

[root@slave02] # ssh-keygen-t rsa

[root@slave02] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@manager

[root@slave02] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@master

[root@slave02] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@slave01

# Host: manager executes commands

[root@manager] # ssh-keygen-t rsa

[root@manager] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@master

[root@manager] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@slave01

[root@manager] # ssh-copy-id-I ~ / .ssh/id_rsa.pub root@slave02

8. Configure Mysql CVM

# master configuration file / usr/local/mysql/etc/my.cnf configuration is as follows:

Basedir = / usr/local//mysql

Datadir = / data/mysql

Port = 3306

Server_id = 241

Socket = / tmp/mysql.sock

Log-bin=mysql-bin

Log-slave-updates

Expire_logs_days = 10

# slave01 configuration file / usr/local/mysql/etc/my.cnf configuration is as follows:

Basedir = / usr/local/mysql

Datadir = / data/mysql

Port = 3306

Server_id = 242

Socket = / tmp/mysql.sock

Log-bin=mysql-bin

Log-slave-updates

Expire_logs_days = 10

# slave02 configuration file / usr/local/mysql/etc/my.cnf configuration is as follows:

Basedir = / usr/local//mysql

Datadir = / data/mysql

Port = 3306

Server_id = 243,

Socket = / tmp/mysql.sock

Log-bin=mysql-bin

Log-slave-updates

Expire_logs_days = 10

Read_only = 1

9. Configure master-slave replication between master, slave01, and slave02

In the master-slave configuration of MySQL5.6, the masterside also needs to open two important options, server-id and log-bin, and the option server-id is unique in the global architecture and cannot be used by other hosts. Here, the last bit of the host ip address is used as the value of server-id, and the slave side needs to enable relay-log.

# Host: master executes commands

[root@master ~] # egrep "log-bin | server_id" / usr/local/lnmp/mysql/etc/my.cnf

Server_id = 11

Log-bin=mysql-bin

# Host: slave01 executes commands

[root@slave01 ~] # egrep "log-bin | server_id" / usr/local/lnmp/mysql/etc/my.cnf

Server_id = 12

Log-bin=mysql-bin

# Host: slave02 executes commands

[root@slave02 ~] # egrep "log-bin | server_id" / usr/local/lnmp/mysql/etc/my.cnf

Server_id = 13

Log-bin=mysql-bin

10. Create master-slave synchronous accounts on master and slave01. Slave01 is the backup master, which also requires the establishment of an authorized user.

[root@master] # mysql-uroot-p123456-e "grant replication slave on. To 'backup'@'192.168.1.%' identified by' backup';flush privileges;"

[root@slave01] # mysql-uroot-p123456-e "grant replication slave on. To 'backup'@'192.168.1.%' identified by' backup';flush privileges;"

11. Execute commands on master to view master status information

[root@master] # mysql-uroot-p123456-e 'show master status;'

Warning: Using a password on the command line interface can be insecure.

+-+

+-+

| | mysql-bin.000004 | 118 | |

+-+

twelve。 Perform master-slave synchronization on slave01 and slave02

# slave01 configuration Master / Slave

[root@slave01] # mysql-uroot-p123456

Mysql > change master to

-> master_host='192.168.1.11',master_user='backup',master_password='backup',master_port=3306,master_log_file='mysql-bin.000004',master_log_pos=118

Query OK, 0 rows affected, 2 warnings (.56 sec)

Mysql > start slave

Query OK, 0 rows affected (0.05 sec)

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.11

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 118

Relay_Log_File: slave01-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

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: 118

Relay_Log_Space: 458

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: 11

Master_UUID: cb4af7bc-d600-11e5-8101-26c537b62ad9

Master_Info_File: / data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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:

Auto_Position: 0

1 row in set (0.00 sec)

# slave02 configuration Master / Slave

[root@slave02] # mysql-uroot-p123456

Mysql > change master to

-> master_host='192.168.1.11',master_user='backup',master_password='backup',master_port=3306,master_log_file='mysql-bin.000004',master_log_pos=118

Query OK, 0 rows affected, 2 warnings (.56 sec)

Mysql > start slave

Query OK, 0 rows affected (0.05 sec)

Mysql > show slave status\ G

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.11

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 118

Relay_Log_File: slave01-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

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: 118

Relay_Log_Space: 458

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: 11

Master_UUID: cb4af7bc-d600-11e5-8101-26c537b62ad9

Master_Info_File: / data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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:

Auto_Position: 0

1 row in set (0.00 sec)

13. Start the construction of MHA

Create an account. Create an account on every database (master, slave01, slave02). Here, take master as an example.

[root@master ~] # mysql-uroot-p123456-e "grant all privileges on. To 'mha_rep'@'192.168.1.%' identified by' 1234456" flush privileges; "

Mysql > select host,user from user

+-+ +

| | host | user |

+-+ +

| | 192.168.1% | backup | |

| | 192.168.1% | mha_rep | |

| | localhost | root |

+-+ +

3 rows in set (0.00 sec)

Install the mha4mysql-node package on three hosts (master, slave01 and slave02). Here, take master as an example, and the same applies to other hosts.

[root@master ~] # yum install perl-DBD-MySQL-y

[root@master ~] # rpm-ivh https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm

Install mha4mysql-manager and mha4mysql-node packages on manager

[root@manager ~] # yum install perl cpan perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Net-Telnet-y

[root@manager ~] # rpm-ivh https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm

[root@manager ~] # wget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.56.tar.gz

[root@manager ~] # tar zvxf mha4mysql-manager-0.56.tar.gz

[root@manager ~] # cd mha4mysql-manager-0.56

[root@manager ~] # perl Makefile.PL

[root@manager mha4mysql-manager-0.56] # make & & make install

[root@manager mha4mysql-manager-0.56] # mkdir-p / usr/local/mha/scripts

[root@manager mha4mysql-manager-0.56] # cp samples/conf/app1.cnf / usr/local/mha/mha.cnf

[root@manager mha4mysql-manager-0.56] # cp samples/scripts/* / usr/local/mha/scripts/

13.4. Modify the configuration file of mha on the manager side, as follows

[root@manager ~] # cat / usr/local/mha/mha.cnf

[server default]

User=mha_rep # user name of the MHA management mysql

Password=123456 # MHA manages the password for mysql

Working directory of manager_workdir=/usr/local/mha # MHA

Log path of manager_log=/usr/local/mha/manager.log # MHA

Ssh_user=root # user name for private key login

Repl_user=backup # Master-Slave copy account is used to synchronize data between master and slave

Repl_password=backup

Ping_interval=1 # ping interval, which is used to check whether the master is normal

[server1]

Hostname=192.168.1.11

Port=3306

Master_binlog_dir=/data/mysql/

Candidate_master=1

[server2]

Hostname=192.168.1.12

Port=3306

Master_binlog_dir=/data/mysql/

Candidate_master=1

[server3]

Hostname=192.168.1.13

Port=3306

Master_binlog_dir=/data/mysql/

No_master=1

Check whether the ssh is unblocked

[root@manager mha4mysql-manager-0.56] # masterha_check_ssh-- conf=/usr/local/mha/mha.cnf

Can't locate Params/Validate.pm in @ INC (@ INC contains: / usr/local/lib64/perl5 / usr/local/share/perl5 / usr/lib64/perl5/vendor_perl / usr/share/perl5/vendor_perl / usr/lib64/perl5/ usr/share/perl5.) At / usr/share/perl5/vendor_perl/Log/Dispatch.pm line 9.

BEGIN failed--compilation aborted at / usr/share/perl5/vendor_perl/Log/Dispatch.pm line 9.

Compilation failed in require at / usr/local/share/perl5/MHA/SSHCheck.pm line 29.

BEGIN failed--compilation aborted at / usr/local/share/perl5/MHA/SSHCheck.pm line 29.

Compilation failed in require at / usr/local/bin/masterha_check_ssh line 25.

BEGIN failed--compilation aborted at / usr/local/bin/masterha_check_ssh line 25.

Resolve this issue by installing the package

[root@manager mha4mysql-manager-0.56] # yum install perl-Params-Validate

Loaded plugins: product-id, refresh-packagekit, search-disabled-repos, security, subscription-manager

This system is not registered with an entitlement server. You can use subscription-manager to register.

Setting up Install Process

Resolving Dependencies

-> Running transaction check

-> Package perl-Params-Validate.x86_64 0RU 0.92-3.el6 will be installed

-> Finished Dependency Resolution

Dependencies Resolved

Package Arch Version Repository Size

Installing:

Perl-Params-Validate x8634 0.92-3.el6 Server 75k

Transaction Summary

Install 1 Package (s)

Total download size: 75 k

Installed size: 175 k

Is this ok [y/N]: y

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Warning: RPMDB altered outside of yum.

* * Found 5 pre-existing rpmdb problem (s), 'yum check' output follows:

Perl-Log-Dispatch-2.27-1.el6.noarch has missing requires of perl (MIME::Lite)

Perl-Log-Dispatch-2.27-1.el6.noarch has missing requires of perl (Mail::Send)

Perl-Log-Dispatch-2.27-1.el6.noarch has missing requires of perl (Mail::Sender)

Perl-Log-Dispatch-2.27-1.el6.noarch has missing requires of perl (Mail::Sendmail)

Perl-Log-Dispatch-2.27-1.el6.noarch has missing requires of perl (Params::Validate)

Installing: perl-Params-Validate-0.92-3.el6.x86_64 1 Compact 1

Verifying: perl-Params-Validate-0.92-3.el6.x86_64 1 Compact 1

Installed:

Perl-Params-Validate.x86_64 0RO 0.92-3.el6

Complete!

[root@manager] # masterha_check_ssh-- conf=/usr/local/mha/mha.cnf

Thu May 17 18:42:22 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Thu May 17 18:42:22 2018-[info] Reading application default configurations from / usr/local/mha/mha.cnf..

Thu May 17 18:42:22 2018-[info] Reading server configurations from / usr/local/mha/mha.cnf..

Thu May 17 18:42:22 2018-[info] Starting SSH connection tests..

Thu May 17 18:42:25 2018-[debug]

Thu May 17 18:42:22 2018-[debug] Connecting via SSH from root@192.168.1.11 (192.168.1.11) to root@192.168.1.12 (192.168.1.12).

Thu May 17 18:42:24 2018-[debug] ok.

Thu May 17 18:42:24 2018-[debug] Connecting via SSH from root@192.168.1.11 (192.168.1.11) to root@192.168.1.13 (192.168.1.13).

Thu May 17 18:42:25 2018-[debug] ok.

Thu May 17 18:42:26 2018-[debug]

Thu May 17 18:42:23 2018-[debug] Connecting via SSH from root@192.168.1.12 (192.168.1.12) to root@192.168.1.11 (192.168.1.11).

Thu May 17 18:42:24 2018-[debug] ok.

Thu May 17 18:42:24 2018-[debug] Connecting via SSH from root@192.168.1.12 (192.168.1.12) to root@192.168.1.13 (192.168.1.13).

Thu May 17 18:42:26 2018-[debug] ok.

Thu May 17 18:42:26 2018-[debug]

Thu May 17 18:42:23 2018-[debug] Connecting via SSH from root@192.168.1.13 (192.168.1.13) to root@192.168.1.11 (192.168.1.11).

Thu May 17 18:42:25 2018-[debug] ok.

Thu May 17 18:42:25 2018-[debug] Connecting via SSH from root@192.168.1.13 (192.168.1.13) to root@192.168.1.12 (192.168.1.12).

Thu May 17 18:42:26 2018-[debug] ok.

Thu May 17 18:42:26 2018-[info] All SSH connection tests passed successfully.

13.6. Masterha_check_repl tool checks whether mysql master-slave replication is successful

[root@manager mha] # masterha_check_repl-- conf=/usr/local/mha/mha.cnf

Tue May 15 09:46:42 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Tue May 15 09:46:42 2018-[info] Reading application default configurations from / usr/local/mha/mha.cnf..

Tue May 15 09:46:42 2018-[info] Reading server configurations from / usr/local/mha/mha.cnf..

Tue May 15 09:46:42 2018-[info] MHA::MasterMonitor version 0.56.

Tue May 15 09:46:42 2018-[info] Dead Servers:

Tue May 15 09:46:42 2018-[info] Alive Servers:

Tue May 15 09:46:42 2018-[info] 192.168.1.11 (192.168.1.11 3306)

Tue May 15 09:46:42 2018-[info] 192.168.1.12 (192.168.1.12 3306)

Tue May 15 09:46:42 2018-[info] 192.168.1.13 (192.168.1.13 3306)

Tue May 15 09:46:42 2018-[info] Alive Slaves:

Tue May 15 09:46:42 2018-[info] 192.168.1.12 (192.168.1.12 Version=5.6.39-log (oldest major version between slaves) log-bin:enabled

Tue May 15 09:46:42 2018-[info] Replicating from 192.168.1.11 (192.168.1.11 Replicating from 3306)

Tue May 15 09:46:42 2018-[info] Primary candidate for the new Master (candidate_master is set)

Tue May 15 09:46:42 2018-[info] 192.168.1.13 (192.168.1.13 Version=5.6.39-log (oldest major version between slaves) log-bin:enabled

Tue May 15 09:46:42 2018-[info] Replicating from 192.168.1.11 (192.168.1.11 Replicating from 3306)

Tue May 15 09:46:42 2018-[info] Not candidate for the new Master (no_master is set)

Tue May 15 09:46:42 2018-[info] Current Alive Master: 192.168.1.11 (192.168.1.11 Current Alive Master 3306)

Tue May 15 09:46:42 2018-[info] Checking slave configurations..

Tue May 15 09:46:42 2018-[info] read_only=1 is not set on slave 192.168.1.12 (192.168.1.12 read_only=1 is not set on slave 3306)

Tue May 15 09:46:42 2018-[warning] relay_log_purge=0 is not set on slave 192.168.1.12 (192.168.1.12 relay_log_purge=0 is not set on slave 3306)

Tue May 15 09:46:42 2018-[warning] relay_log_purge=0 is not set on slave 192.168.1.13 (192.168.1.13)

Tue May 15 09:46:42 2018-[info] Checking replication filtering settings..

Tue May 15 09:46:42 2018-[info] binlog_do_db=, binlog_ignore_db=

Tue May 15 09:46:42 2018-[info] Replication filtering check ok.

Tue May 15 09:46:42 2018-[info] Starting SSH connection tests..

Tue May 15 09:46:46 2018-[info] All SSH connection tests passed successfully.

Tue May 15 09:46:46 2018-[info] Checking MHA Node version..

Tue May 15 09:46:47 2018-[info] Version check ok.

Tue May 15 09:46:47 2018-[info] Checking SSH publickey authentication settings on the current master..

Tue May 15 09:46:48 2018-[info] HealthCheck: SSH to 192.168.1.11 is reachable.

Tue May 15 09:46:49 2018-[info] Master MHA Node version is 0.54.

Tue May 15 09:46:49 2018-[info] Checking recovery script configurations on the current master..

Tue May 15 09:46:49 2018-[info] Executing command: save_binary_logs-command=test-start_pos=4-binlog_dir=/data/mysql/-output_file=/var/tmp/save_binary_logs_test-manager_version=0.56-start_file=mysql-bin.000004

Tue May 15 09:46:49 2018-[info] Connecting to root@192.168.1.11 (192.168.1.11)..

Creating / var/tmp if not exists.. Ok.

Checking output directory is accessible or not..

Ok.

Binlog found at / data/mysql/, up to mysql-bin.000004

Tue May 15 09:46:49 2018-[info] Master setting check done.

Tue May 15 09:46:49 2018-[info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..

Tue May 15 09:46:49 2018-[info] Executing command: apply_diff_relay_logs-- command=test-- slave_user='mha_rep'-- slave_host=192.168.1.12-- slave_ip=192.168.1.12-- slave_port=3306-- workdir=/var/tmp-- target_version=5.6.39-log-manager_version=0.56-- relay_log_info=/data/mysql/relay-log.info-- relay_dir=/data/mysql/-- slave_pass=xxx

Tue May 15 09:46:49 2018-[info] Connecting to root@192.168.1.12 (192.168.1.12)..

Checking slave recovery environment settings..

Opening / data/mysql/relay-log.info... Ok.

Relay log found at / data/mysql, up to slave01-relay-bin.000002

Temporary relay log file is / data/mysql/slave01-relay-bin.000002

Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'mha_rep'@'slave01' (using password: YES)

Mysql command failed with rc 1:0!

At / usr/bin/apply_diff_relay_logs line 367

Main::check () called at / usr/bin/apply_diff_relay_logs line 486

Eval {...} called at / usr/bin/apply_diff_relay_logs line 466

Main::main () called at / usr/bin/apply_diff_relay_logs line 112

Tue May 15 09:46:50 2018-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln201] Slaves settings check failed!

Tue May 15 09:46:50 2018-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln390] Slave configuration failed.

Tue May 15 09:46:50 2018-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln401] Error happend on checking configurations. At / usr/local/bin/masterha_check_repl line 48

Tue May 15 09:46:50 2018-[error] [/ usr/local/share/perl5/MHA/MasterMonitor.pm, ln500] Error happened on monitoring servers.

Tue May 15 09:46:50 2018-[info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

Solution:

[root@master bin] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 56

Server version: 5.6.39-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > select user,host from user

+-+ +

| | user | host |

+-+ +

| | root | 127.0.0.1 | |

| | backup | 192.168.1% |

| | mha_rep | 192.168.1% |

| | root |:: 1 |

| | localhost |

| | mha_rep | localhost |

| | root | localhost |

| | master |

| | root | master |

+-+ +

9 rows in set (0.03 sec)

Mysql > grant all privileges on. To mha_rep@'192.168.1.12' identified by '123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > grant all privileges on. To mha_rep@'192.168.1.13' identified by '123456'

Query OK, 0 rows affected (0.02 sec)

Mysql > grant all privileges on. To mha_rep@'192.168.1.11' identified by '123456'

Query OK, 0 rows affected (0.00 sec)

Mysql >

Run the monitoring script masterha_check_repl-conf=/usr/local/mha/mha.cnf again and report an error

Can't exec "mysqlbinlog": No such file or directory at / usr/local/share/perl5/MHA/BinlogManager.pm line 99.

Mysqlbinlog version not found!

Lack of soft connection, let's create a soft connection in all three libraries

Ln-s / usr/local/mysql/bin/mysql / usr/bin/mysql

Ln-s / usr/local/mysql/bin/mysqlbinlog / usr/local/bin/mysqlbinlog

The test passed again.

14.mha experimental simulation

1. Every time we do the mha experiment, we'd better execute the following command to do the test.

[root@manager] # masterha_check_ssh-- conf=/usr/local/mha/mha.cnf

[root@manager] # masterha_check_repl-- conf=/usr/local/mha/mha.cnf

# make sure that the return results of both commands are without exception, and then start the mha service

2. Start the mha service on the manager side and monitor the output changes of log files all the time.

[root@manager] # nohup masterha_manager-- conf=/usr/local/mha/mha.cnf > / tmp/mha_manager.log 2 > & 1 &

[root@manager ~] # ps-ef | grep masterha | grep-v 'grep'

3. After the test master goes down, it will switch automatically.

# check the master-slave synchronization of slave01,slave02 before testing

# slave01

[root@slave01 ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# slave02

[root@slave02 ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# stop the mysql service of master

[root@master ~] # service mysql stop

Shutting down MySQL. [OK]

# Let's check the master-slave synchronization information of slave02

[root@slave02 ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Master_Host | Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Master_Host: 192.168.1.12 # indicates that the new ip has been transferred

Slave_IO_Running: Yes # Master / Slave normal

Slave_SQL_Running: Yes

4. Restore master service

# Delete a failover file

[root@manager mha] # rm-rf / usr/local/mha/mha.failover.complete

# restart mysql service

[root@master ~] # service mysqld start

# find the sql statement synchronized by master and slave in the date file of manager

[root@manager mha] # grep MASTER_HOST / usr/local/mha/manager.log

Tue May 15 10:08:54 2018-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.12', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=2294, MASTER_USER='backup', MASTER_PASSWORD='xxx'

# start master-slave synchronization on master with a password of backup

Mysql > change master to

-> master_host='192.168.1.12'',master_user='backup',master_password='backup',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=2294

Query OK, 0 rows affected, 2 warnings (0.75 sec)

Mysql > start slave

Query OK, 0 rows affected (0.05 sec)

# execute on master and slave02 to check whether master-slave synchronization is normal. Here, take master as an example, and slave02 is the same.

[root@master ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Master_Host | Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Master_Host: 192.168.1.12

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. Start the manager service of MHA again and stop slave01

[root@manager] # nohup masterha_manager-- conf=/usr/local/mha/mha.cnf > / tmp/mha_manager.log 2 > & 1 &

# disable the mysql service of slave01

[root@slave01 ~] # service mysqld stop

Shutting down MySQL... SUCCESS

[root@manager mha] # tail-f / usr/local/mha/manager.log

# check master-slave synchronization on slave02

[root@slave02 ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Master_Host | Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Master_Host: 192.168.1.12

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

6. Restore slave01 service

# Delete a failover file

[root@manager mha] # rm-rf / usr/local/mha/mha.failover.complete

# restart mysql service

[root@slave01 ~] # service mysqld start

Starting MySQL... SUCCESS!

# find the sql statement synchronized by master and slave in the date file of manager

[root@manager mha] # grep MASTER_HOST / usr/local/mha/manager.log

Tue May 15 10:25:51 2018-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.11', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120, MASTER_USER='backup', MASTER_PASSWORD='xxx'

# start master-slave synchronization on slave01 with a password of backup

Mysql > change master to

-> master_host='192.168.1.11',master_user='backup',master_password='backup',master_port=3306,master_log_file='mysql-bin.000005',master_log_pos=120

Query OK, 0 rows affected, 2 warnings (0.61 sec)

Mysql > start slave

Query OK, 0 rows affected (0.05 sec)

# execute on slave01 and slave02 to check whether the master-slave synchronization is normal. Here, take slave01 as an example, and slave02 is the same.

[root@slave01 ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Master_Host | Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Master_Host: 192.168.1.11

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

7. Start the manager service of MHA again

[root@manager] # nohup masterha_manager-- conf=/usr/local/mha/mha.cnf > / tmp/mha_manager.log 2 > & 1 &

15. High availability of mysql through vip

1. Modify / usr/local/mha/mha.cnf

[root@manager mha] # more cat / usr/local/mha/mha.cnf

Cat: No such file or directory

/ usr/local/mha/mha.cnf

[server default]

User=mha_rep

Password=123456

Manager_workdir=/usr/local/mha

Manager_log=/usr/local/mha/manager.log

Master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover # Custom perl script

Ssh_user=root

Repl_user=backup

Repl_password=backup

Ping_interval=1

[server1]

Hostname=192.168.1.11

Port=3306

Master_binlog_dir=/data/mysql/

Candidate_master=1

[server2]

Hostname=192.168.1.12

Port=3306

Master_binlog_dir=/data/mysql/

Candidate_master=1

[server3]

Hostname=192.168.1.13

Port=3306

Master_binlog_dir=/data/mysql/

No_master=1

2. Modify script / usr/local/mha/scripts/master_ip_failover

#! / usr/bin/env perl

Use strict

Use warnings FATAL = > 'all'

Use Getopt::Long

My (

$command, $ssh_user, $orig_master_host, $orig_master_ip

$orig_master_port, $new_master_host, $new_master_ip, $new_master_port

);

My $vip = '192.168.1.240'

My $key ='1'

My $ssh_start_vip = "/ sbin/ifconfig eth0:$key $vip"

My $ssh_stop_vip = "/ sbin/ifconfig eth0:$key down"

GetOptions (

'command=s' = >\ $command

'ssh_user=s' = >\ $ssh_user

'orig_master_host=s' = >\ $orig_master_host

'orig_master_ip=s' = >\ $orig_master_ip

'orig_master_port=i' = >\ $orig_master_port

'new_master_host=s' = >\ $new_master_host

'new_master_ip=s' = >\ $new_master_ip

'new_master_port=i' = >\ $new_master_port

);

Exit & main ()

Sub main {

Print "\ n\ nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"; if ($command eq "stop" | | $command eq "stopssh") {my $exit_code = 1; eval {print "Disabling the VIP on old master: $orig_master_host\ n"; & stop_vip (); $exit_code = 0;}; if ($@) {warn "Got Error: $@\ n"; exit $exit_code } exit $exit_code;} elsif ($command eq "start") {my $exit_code = 10; eval {print "Enabling the VIP-$vip on the new master-$new_master_host\ n"; & start_vip (); $exit_code = 0;}; if ($@) {warn $@; exit $exit_code;} exit $exit_code } elsif ($command eq "status") {print "Checking the Status of the script.. OK\ n "; exit 0;} else {& usage (); exit 1;}

}

Sub start_vip () {

Ssh $ssh_user\ @ $new_master_host\ "$ssh_start_vip\"

}

A simple system call that disable the VIP on the old_master

Sub stop_vip () {

Ssh $ssh_user\ @ $orig_master_host\ "$ssh_stop_vip\"

}

Sub usage {

"Usage: master_ip_failover-- command=start | stop | stopssh | status-- orig_master_host=host-- orig_master_ip=ip-- orig_master_port=port-- new_master_host=host-- new_master_ip=ip-- new_master_port=port\ n"

}

3. Simulate the failure to switch over

# stop the mysql service of master

[root@master ~] # service mysqld stop

Shutting down MySQL... SUCCESS!

# View the synchronization information of slave02

[root@slave02 ~] # mysql-uroot-p123456-e 'show slave status\ G' | egrep' Master_Host | Slave_IO_Running: | Slave_SQL_Running:'

Warning: Using a password on the command line interface can be insecure.

Master_Host: 192.168.1.12

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# View the IP information of slave01

Eth0 Link encap:Ethernet HWaddr 00:0C:29:EB:86:1F

Inet addr:192.168.1.12 Bcast:192.168.1.255 Mask:255.255.255.0

Inet6 addr: fe80::20c:29ff:feeb:861f/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:59533 errors:0 dropped:0 overruns:0 frame:0

TX packets:27157 errors:0 dropped:0 overruns:0 carrier:0

Collisions:0 txqueuelen:1000

RX bytes:63732835 (60.7 MiB) TX bytes:4660626 (4.4 MiB)

Eth0:1 Link encap:Ethernet HWaddr 00:0C:29:01:46:B7

Inet addr:192.168.1.240 Bcast:192.168.1.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

4. Restore master service in the same way as above.

PS: do not add comments to the configuration file, or there will be unexpected problems, such as:

In the mha.cnf configuration file, if we add the # custom perl script above, we will report an error when starting MHA. Ps-ef looks at the mha process and finds that it is not there. Check the log file and report an error as follows

Tail: / tmp/mha_manager.log:

Nohup: ignoring input

Thu May 17 15:14:46 2018-[warning] Global configuration file / etc/masterha_default.cnf not found. Skipping.

Thu May 17 15:14:46 2018-[info] Reading application default configurations from / usr/local/mha/mha.cnf..

Thu May 17 15:14:46 2018-[info] Reading server configurations from / usr/local/mha/mha.cnf..

Use of uninitialized value $command in string eq at / usr/local/mha/scripts/master_ip_failover line 29.

16. Daily maintenance order

1. Check statu

Masterha_check_status-conf=/usr/local/mha/mha.cnf

two。 Stop it

Masterha_stop masterha_check_status-conf=/usr/local/mha/mha.cnf

After reading this article on what MHA failover can help us solve, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.