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

The following brings you about how to install and configure MHA architecture in MySQL5.7. I'm sure you've read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I'm sure you'll get something after reading how to install and configure MHA architecture in MySQL5.7.

Preparation in advance

CVM version: CentOS Linux release 7.6.1810 (Core)

Mysql_mha_manager: 192.168.10.215

Mysql_mha_01: 192.168.10.216

Mysql_mha_02: 192.168.10.217

Mysql_mha_03: 192.168.10.218

one。 Install MHA tools

1. Mysql_mha_manager CVM

Installation dependency

Yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm-y

Yum install perl-DBD-MySQL-y

Yum install perl-Config-Tiny-y

Yum install perl-Log-Dispatch perl-Parallel-ForkManager-y

Yum install perl-Parallel-ForkManager-y

Install node

Rpm-ivh mha4mysql-node-0.57-0.el7.noarch.rpm

Install manager

Rpm-ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

2. Mysql_mha_01 CVM

Installation dependency

Yum install perl-DBD-MySQL-y

Install node

Rpm-ivh mha4mysql-node-0.57-0.el7.noarch.rpm

3. Mysql_mha_02 CVM

Installation dependency

Yum install perl-DBD-MySQL-y

Install node

Rpm-ivh mha4mysql-node-0.57-0.el7.noarch.rpm

4. Mysql_mha_03 CVM

Installation dependency

Yum install perl-DBD-MySQL-y

Install node

Rpm-ivh mha4mysql-node-0.57-0.el7.noarch.rpm

two。 Ssh secret-free

1. Mysql_mha_manager

192.168.10.215

Ssh-keygen-t rsa

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.216

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.217

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.218

2. Mysql_mha_01

192.168.10.216

Ssh-keygen-t rsa

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.217

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.218

3. Mysql_mha_02

192.168.10.217

Ssh-keygen-t rsa

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.216

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.218

4. Mysql_mha_03

192.168.10.218

Ssh-keygen-t rsa

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.216

Ssh-copy-id-I / root/.ssh/id_rsa root@192.168.10.217

three。 Install mysql

All machines create accounts

Groupadd mysql

Useradd-g mysql mysql

Initial startup error report

[root@mysql_mha_01 tmp] # service mysqld start

Starting MySQL.2019-03-12T14:27:25.072241Z mysqld_safe error: log-error set to'/ data/mysql/logs/mysql.log', however file don't exists. Create writable for user 'mysql'.

ERROR! The server quit without updating PID file (/ data/mysql/run/mysql.pid).

Solution:

Start mysql:sudo-u mysql service mysqld start for the first time

Start mysql:service mysqld start again | stop | restart

four。 Modify the hosts file

Mysql_mha_01/02/03 、 mysql_mha_manager

Vi / etc/hosts

192.168.10.216 mysql_mha_01

192.168.10.217 mysql_mha_02

192.168.10.218 mysql_mha_03

five。 Mysql master-slave configuration

Mysql_mha_01/02/03, turn off the firewall

Systemctl stop firewalld.service

1. Mysql_mha_01

Grant replication slave,replication client ON *. * to 'repl'@'192.168.10.%' identified by' repl'

Flush privileges

Mysqldump-master-data=2-single-transaction-uroot-p-all-databases > mysql_mha_01.sql

More mysql_mha_01.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448

Scp mysql_mha_01.sql root@192.168.10.217:/tmp

Scp mysql_mha_01.sql root@192.168.10.218:/tmp

2. Mysql_mha_02

Mysql-uroot-p

< mysql_mha_01.sql mysql -uroot -p CHANGE MASTER TO MASTER_HOST='192.168.10.216', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448; start slave; show slave status\G 3. mysql_mha_03 mysql -uroot -p < mysql_mha_01.sql mysql -uroot -p CHANGE MASTER TO MASTER_HOST='192.168.10.216', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448; start slave; show slave status\G 六. mha的manager配置文件 1. mysql_mha_manager云服务器 mkdir -p /etc/mha vi /etc/mha/mha.cnf [server default] manager_workdir=/etc/mha/ manager_log=/etc/mha/manager.log master_binlog_dir=/data/mysql/binlog/ user=root password=123456 ping_interval=1 remote_workdir=/tmp repl_password=repl repl_user=repl secondary_check_script= /usr/bin/masterha_secondary_check -s 192.168.10.217 -s 192.168.10.218 --user=root --master_host=192.168.10.216 --master_ip=192.168.10.216 --master_port=3306 master_ip_failover_script="/etc/mha/scripts/master_ip_failover" #master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change" #shutdown_script="" ssh_user=root [server1] hostname=192.168.10.216 port=3306 candidate_master=1 check_repl_delay=0 [server2] hostname=192.168.10.217 port=3306 candidate_master=1 check_repl_delay=0 [server3] hostname=192.168.10.218 port=3306 七. 检查SSH免密、复制状态 1. 检查SSH免密 masterha_check_ssh --conf=/etc/mha/mha.cnf 2. 检查复制 masterha_check_repl --conf=/etc/mha/mha.cnf 错误1 描述 Tue Mar 12 12:50:46 2019 - [info] Connecting to root@192.168.10.217(mysql_mha_02:22).. Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106. mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/bin/apply_diff_relay_logs line 493. 解决方案 mysql_mha_01/02/03 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog 错误2 描述 Tue Mar 12 13:13:13 2019 - [info] Connecting to root@192.168.10.217(mysql_mha_02:22).. mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4' mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/bin/apply_diff_relay_logs line 493. 解决方案 mysql_mha_01/02/03 修改my.cnf [client] #default-character-set = utf8mb4 错误3 描述 Testing mysql connection and privileges..sh: mysql: command not found mysql command failed with rc 127:0! at /usr/bin/apply_diff_relay_logs line 375. main::check() called at /usr/bin/apply_diff_relay_logs line 497 eval {...} called at /usr/bin/apply_diff_relay_logs line 475 main::main() called at /usr/bin/apply_diff_relay_logs line 120 Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed! Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed. Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48. Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers. Tue Mar 12 13:25:40 2019 - [info] Got exit code 1 (Not master dead). 解决方案 mysql_mha_01/02/03 ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql 八. MHA启动与停止 1. 启动manager nohup masterha_manager --conf=/etc/mha/mha.cnf< /dev/null >

/ etc/mha/manager.log 2 > & 1 &

two。 Stop manager

Masterha_stop-conf=/etc/mha/mha.cnf

3. Check manager

Masterha_check_status-conf=/etc/mha/mha.cnf

nine。 test

1. View statu

[root@mysql_mha_manager mha] # masterha_check_status-- conf=/etc/mha/mha.cnf

Mha (pid:27991) is running (0:PING_OK), master:mysql_mha_01

two。 Shut down the mysql_mha_01 database

Service mysqld stop

3. View log and mha status

Journal

-Failover Report-

Mha: MySQL Master failover mysql_mha_01 (192.168.10.216) to mysql_mha_02 (192.168.10.217) succeeded

Master mysql_mha_01 (192.168.10.216 3306) is down!

Check MHA Manager logs at mysql_mha_manager:/etc/mha/manager.log for details.

Started automated (non-interactive) failover.

The latest slave mysql_mha_02 (192.168.10.217 3306) has all relay logs for recovery.

Selected mysql_mha_02 (192.168.10.217 3306) as a new master.

Mysql_mha_02 (192.168.10.217): OK: Applying all logs succeeded.

Mysql_mha_03 (192.168.10.218): This host has the latest relay log events.

Generating relay diff files from the latest slave succeeded.

Mysql_mha_03 (192.168.10.218): OK: Applying all logs succeeded. Slave started, replicating from mysql_mha_02 (192.168.10.217purl 3306)

Mysql_mha_02 (192.168.10.217): Resetting slave info succeeded.

Master failover to mysql_mha_02 (192.168.10.217 3306) completed successfully.

Status

[root@mysql_mha_manager mha] # masterha_check_status-- conf=/etc/mha/mha.cnf

Mha is stopped (2:NOT_RUNNING).

4. View slave status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.217

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql_mha02-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: mysql_mha_03-relay-bin.000002

Relay_Log_Pos: 326

Relay_Master_Log_File: mysql_mha02-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Do you think it is what you want about how to install and configure MHA architecture in MySQL5.7 above? If you want to know more about it, you can continue to 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.