Mysql mha High availability configuration and detailed explanation
Three mysql
The network segment is configured as 10.0.0.51 10.0.52 10.0.0.53
Install mysql
1.2.1 installation package preparation
# create a directory for the installation package
[root@mysql-db01 ~] # mkdir / home/cc/tools-p
# enter the directory
[root@mysql-db01 ~] # cd / home/cc/tools/
# upload mysql installation package (mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz)
[root@mysql-db01 tools] # rz-be
1.2.2 installation
# create an installation directory
[root@mysql-db01 tools] # mkdir / application
# decompress mysql binary package
[root@mysql-db01 tools] # tar xf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
# Mobile installation package
[root@mysql-db01 tools] # mv mysql-5.6.16-linux-glibc2.5-x86_64 / application/mysql-5.6.16
# make soft links
[root@mysql-db01 tools] # ln-s / application/mysql-5.6.16/ / application/mysql
# create a mysql user
[root@mysql-db01 tools] # useradd mysql- s / sbin/nologin-M
# enter the mysql initialization directory
[root@mysql-db01 tools] # cd / application/mysql/scripts/
# initialize mysql
[root@mysql-db01 scripts] #. / mysql_install_db\
-- user=mysql\
-- datadir=/application/mysql/data/\
-- basedir=/application/mysql/
# Notes
-- user: specifies the mysql user
-- datadir: specifies the directory where mysql data is stored
-- basedir: specify the mysql base directory
# copy mysql configuration file
[root@mysql-db01] #\ cp / application/mysql/support-files/my-default.cnf / etc/my.cnf
# copy mysql startup script
[root@mysql-db01 ~] # cp / application/mysql/support-files/mysql.server / etc/init.d/mysqld
# modify the default installation directory of mysql (otherwise it cannot be started)
[root@mysql-db01 ~] # sed-I's impulse usr _ server _ etc/init.d/mysqld
[root@mysql-db01 ~] # sed-I's impulse usr _ server _ application/mysql/bin/mysqld_safe
# configure mysql environment variables
[root@mysql-db01 ~] # echo 'export PATH= "/ application/mysql/bin:$PATH" > / etc/profile.d/mysql.sh
# refresh environment variables
[root@mysql-db01 ~] # source / etc/profile
1.2.3 start
# add self-booting
[root@mysql-db01 ~] # chkconfig mysqld on
# start mysql
[root@mysql-db01 ~] # / etc/init.d/mysqld start
Starting MySQL. SUCCESS! # started successfully
1.2.4 configure password
# configure mysql password to cc123
[root@mysql-db01 ~] # mysqladmin-uroot password cc123
I. configure master-slave replication
2.1 prerequisites
Binlog should be enabled for both the master library and the slave library
The master library is different from the slave library server-id
There must be a master-slave replication user.
2.2 main library operation
2.2.1 modify the configuration file
# Editing mysql configuration file
[root@mysql-db01 ~] # vim / etc/my.cnf
# configure under the mysqld tag
[mysqld]
# the master server-id is 1, and the slave library must be greater than 1
Server_id = 1
# enable binlog log
Log_bin=mysql-bin
2.2.2 create a master-slave replication user
# logging in to the database
[root@mysql-db01 ~] # mysql- uroot-pcc123
# create a rep user
Mysql > grant replication slave on *. * to rep@'10.0.0.%' identified by 'cc123'
2.3 Operation from the library
2.3.1 modify the configuration file
# modify mysql-db02 configuration file
[root@mysql-db02 ~] # vim / etc/my.cnf
# configure under the mysqld tag
[mysqld]
# the master server-id is 1, and the slave library must be greater than 1
Server_id = 5
# enable binlog log
Log_bin=mysql-bin
# restart mysql
[root@mysql-db02 ~] # / etc/init.d/mysqld restart
# modify mysql-db03 configuration file
[root@mysql-db03 ~] # vim / etc/my.cnf
# configure under the mysqld tag
[mysqld]
# the master server-id is 1, and the slave library must be greater than 1
Server_id = 10
# enable binlog log
Log_bin=mysql-bin
# restart mysql
[root@mysql-db03 ~] # / etc/init.d/mysqld restart
Note: in the past, if it was a master-slave replication based on binlog logs, you had to remember the master status information of the master database.
Mysql > show master status
+-+ +
| | File | Position |
+-+ +
| | mysql-bin.000002 | 120 | |
+-+ +
2.4 enable GTID
# check the status of GTID before enabling it
Mysql > show global variables like'% gtid%'
+-+ +
| | Variable_name | Value |
+-+ +
| | enforce_gtid_consistency | OFF |
| | gtid_executed |
| | gtid_mode | OFF |
| | gtid_owned |
| | gtid_purged |
+-+ +
# Edit mysql configuration file (both master and slave libraries need to be modified)
[root@mysql-db01 ~] # vim / etc/my.cnf
# add under the [mysqld] tag
[mysqld]
Gtid_mode=ON
Log_slave_updates
Enforce_gtid_consistency
# restart the database
[root@mysql-db01 ~] # / etc/init.d/mysqld restart
# check GTID status
Mysql > show global variables like'% gtid%'
+-+ +
| | Variable_name | Value |
+-+ +
| | enforce_gtid_consistency | ON | # execution of GTID is consistent |
| | gtid_executed |
| | gtid_mode | ON | # enable GTID module |
| | gtid_owned |
| | gtid_purged |
+-+ +
Note: GTID needs to be enabled for both master and slave libraries, otherwise an error will be reported when copying master and slave:
[root@mysql-db02 ~] # mysql- uroot-pcc123
Mysql > change master to
-> master_host='10.0.0.51'
-> master_user='rep'
-> master_password='cc123'
-> master_auto_position=1
If git_mode is not enabled, it is easy to report an error.
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @ @ GLOBAL.GTID_MODE = ON.
2.5 configure master-slave replication
# logging in to the database
[root@mysql-db02 ~] # mysql- uroot-pcc123
# configure replication host information
Mysql > change master to
# main library IP
-> master_host='10.0.0.51'
# copy users in the main library
-> master_user='rep'
# the main library copies the user's password
-> master_password='cc123'
# GTID location Point
-> master_auto_position=1
# enable slave
Mysql > start slave
# View slave status
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 403
Relay_Log_File: mysql-db02-relay-bin.000002
Relay_Log_Pos: 613
Relay_Master_Log_File: mysql-bin.000003
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: 403
Relay_Log_Space: 822
Until_Condition: None
2.6 from the library settings
# Log in to the slave library
[root@mysql-db02 ~] # mysql- uroot-pcc123
# disable automatic deletion of relay log
Mysql > set global relay_log_purge = 0
# set read-only
Mysql > set global read_only=1
# Editing configuration file
[root@mysql-db02 ~] # vim / etc/my.cnf
# add under the mysqld tag
[mysqld]
# disabling automatic deletion of relay log takes effect permanently
Relay_log_purge = 0
II. Deploy MHA
3.1 Environment preparation (all nodes)
# install dependency packages
[root@mysql-db01 ~] # yum install perl-DBD-MySQL-y
# enter the directory where the installation package is stored
[root@mysql-db01 ~] # cd / home/cc/tools/
# upload mha installation package
[root@mysql-db01 tools] # rz-be
Mha4mysql-manager-0.56-0.el6.noarch.rpm
Mha4mysql-manager-0.56.tar.gz
Mha4mysql-node-0.56-0.el6.noarch.rpm
Mha4mysql-node-0.56.tar.gz
# install the node package
[root@mysql-db01 tools] # rpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Preparing... # [100%]
1:mha4mysql-node # # [100%]
# logging in to the database
[root@mysql-db01 tools] # mysql- uroot-pcc123
# add mha management account
Mysql > grant all privileges on *. * to mha@'10.0.0.%' identified by 'mha'
# check whether it has been added successfully
Mysql > select user,host from mysql.user
# create on the master library, and the slave library will be copied automatically (viewed on the slave library)
Mysql > select user,host from mysql.user
3.2 Command soft connection (all nodes)
# if you do not create a command soft connection, an error will be reported when detecting mha replication
[root@mysql-db01] # ln-s / application/mysql/bin/mysqlbinlog / usr/bin/mysqlbinlog
[root@mysql-db01] # ln-s / application/mysql/bin/mysql / usr/bin/mysql
3.3 deployment Management Node (mha-manager)
3.3.1 deploy the management node on mysql-db03
# using epel feeds
[root@mysql-db03 ~] #
Wget-O / etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
# install manager dependency package
[root@mysql-db03 ~] # yum install-y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# install the manager package
[root@mysql-db03 tools] # rpm-ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
Preparing... # [100%]
1:mha4mysql-manager # # [100%]
3.3.2 Editing the profile
# create a configuration file directory
[root@mysql-db03] # mkdir-p / etc/mha
# create a log directory
[root@mysql-db03] # mkdir-p / var/log/mha/app1
# Editing mha configuration file
[root@mysql-db03 ~] # vim / etc/mha/app1.cnf
[server default]
Manager_log=/var/log/mha/app1/manager
Manager_workdir=/var/log/mha/app1
Master_binlog_dir=/application/mysql/data
User=mha
Password=mha
Ping_interval=2
Repl_password=cc123
Repl_user=rep
Ssh_user=root
[server1]
Hostname=10.0.0.51
Port=3306
[server2]
Candidate_master=1
Check_repl_delay=0
Hostname=10.0.0.52
Port=3306
[server3]
Hostname=10.0.0.53
Port=3306
[detailed description of configuration file]
[server default]
# set the working directory of manager
Manager_workdir=/var/log/masterha/app1
# set the log of manager
Manager_log=/var/log/masterha/app1/manager.log
# set the location where master saves binlog so that MHA can find the log of master. What I have here is the data directory of mysql
Master_binlog_dir=/data/mysql
# switching script when setting automatic failover
Master_ip_failover_script= / usr/local/bin/master_ip_failover
# set the switching script for manual switching
Master_ip_online_change_script= / usr/local/bin/master_ip_online_change
# set the password of the root user in mysql. This password is the password that was created earlier for the monitoring user.
Password=123456
# set root for monitoring users
User=root
# set the monitoring master database, the time interval for sending ping packets, and automatically failover when there is no response for three attempts
Ping_interval=1
# set the location where the remote mysql binlog is saved when switching occurs
Remote_workdir=/tmp
# set the password of the replication user
Repl_password=123456
# set the replication user name in the replication environment
Repl_user=rep
# set the script for the alarm sent after the switch occurs
Report_script=/usr/local/send_report
# once there is a problem between MHA and server02 monitoring, MHA Manager will try to log in to server02 from server03
Secondary_check_script= / usr/local/bin/masterha_secondary_check-s server03-s server02-- user=root-- master_host=server02-- master_ip=192.168.0.50-- master_port=3306
# set to close the failed host script after a failure occurs (the main function of this script is to shut down the host and put it in the event of a brain crack, which is not used here)
Shutdown_script= ""
# set login user name for ssh
Ssh_user=root
[server1]
Hostname=10.0.0.51
Port=3306
[server2]
Hostname=10.0.0.52
Port=3306
# set as candidate master. If this parameter is set, this slave database will be promoted to master database after master-slave switching occurs, even if the master database is not the latest slave of events in the cluster.
Candidate_master=1
# by default, if a slave lags behind the relay logs of master 100m, MHA will not select the slave as a new master, because the recovery of this slave will take a long time. Setting check_repl_delay=0,MHA to trigger the switch will ignore the replication delay when selecting a new master. This parameter is very useful for hosts with candidate_master=1 set, because the candidate host must be the new master during the handover.
Check_repl_delay=0
3.4 configure ssh trust (all nodes)
# create a key pair
[root@mysql-db01] # ssh-keygen-t dsa-P'- f ~ / .ssh/id_dsa > / dev/null 2 > & 1
# send the public key, including yourself
[root@mysql-db01] # ssh-copy-id-I / root/.ssh/id_dsa.pub root@10.0.0.51
[root@mysql-db01] # ssh-copy-id-I / root/.ssh/id_dsa.pub root@10.0.0.52
[root@mysql-db01] # ssh-copy-id-I / root/.ssh/id_dsa.pub root@10.0.0.53
3.5 start the test
# Test ssh
[root@mysql-db03] # masterha_check_ssh-- conf=/etc/mha/app1.cnf
# if you see the following words, the test is successful
Tue Mar 7 01:03:33 2017-[info] All SSH connection tests passed successfully.
# Test replication
[root@mysql-db03] # masterha_check_repl-- conf=/etc/mha/app1.cnf
# if you see the following words, the test is successful
MySQL Replication Health is OK.
3.6 start MHA
# start
[root@mysql-db03 ~] #
Nohup masterha_manager-conf=/etc/mha/app1.cnf-remove_dead_master_conf-ignore_last_failover
< /dev/null >/ var/log/mha/app1/manager.log 2 > & 1 &
3.7 switch master test
# logging in to the database (db02)
[root@mysql-db02 ~] # mysql- uroot-pcc123
# check replication
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-db02-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# logging in to the database (db03)
[root@mysql-db03 ~] # mysql- uroot-pcc123
# check replication
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-db03-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# stop the main library
[root@mysql-db01 ~] # / etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
# logging in to the database (db02)
[root@mysql-db02 ~] # mysql- uroot-pcc123
# View slave status
Mysql > show slave status\ G
# slave for db02 is already empty
Empty set (0.00 sec)
# logging in to the database (db03)
[root@mysql-db03 ~] # mysql- uroot-pcc123
# View slave status
Mysql > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.52
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-db03-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.8 promote database restart dropped by dang to slave library
# in actual production, the hardware problem of the database server should be detected and eliminated.
1. Find the master statement
[root@mysql-db03 .ssh] # grep-I "change master" / var/log/mha/app1/manager
Fri May 26 16:59:31 2017-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx'
two。 Insert master into a library that is down
[root@mysql-db03 .ssh] #
Restart the library dropped by dang and copy the change master statement into the disabled library
Mysql > CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='cc123'
Query OK, 0 rows affected, 2 warnings (0.13 sec)
3. Turn on the switch of the slave library
4. Add the label of the dead host on the management machine
[root@mysql-db03 ~] # vim / etc/mha/app1.cnf
[server default]
Manager_log=/var/log/mha/app1/manager
Manager_workdir=/var/log/mha/app1
Master_binlog_dir=/application/mysql/data
User=mha
Password=mha
Ping_interval=2
Repl_password=cc123
Repl_user=rep
Ssh_user=root
[server1]
Hostname=10.0.0.51
Port=3306
[server2]
Candidate_master=1 # gives priority to the main library
Check_repl_delay=0 # can be selected according to actual needs
Hostname=10.0.0.52
Port=3306
[server3]
Hostname=10.0.0.53
Port=3306
5. Turn on mannger
6. Observe the status of the slave library and complete this configuration
