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

This article mainly introduces the detailed steps of building the MHA framework of mysql, the contents of the article are carefully selected and edited by the author, with a certain pertinence, and is of great significance to everyone's reference, the following is to understand the detailed steps of building the MHA framework of mysql with the author.

Experimental ideas:

1. MHA architecture

1) Database installation

2) one master and two slaves

3) MHA building

two。 Fault simulation

1) failure of the main library

2) the alternative master library becomes the main library.

3) Slave Library 2 points the alternate master library as the master library

Case environment

1. This case environment

CVM CentOS7. 4 (64 bit) [MHA manager/192. one hundred and sixty eight。 one hundred and ninety five。 one hundred and twenty eight

Manage the node and install manager. module

CVM Cent0S7. 4 (64 bit) Mysq11/192. one hundred and sixty eight。 one hundred and ninety five。 129Master node, installing node components

CVM Cent0S7. 4 (64 bit) Mysq12/192. one hundred and sixty eight。 one hundred and ninety five。 130 Slave nodes, install node components

CVM Cent0S7. 4 (64 bit) Mysq13/192. one hundred and sixty eight。 one hundred and ninety five。 131Slave node to install node components

The operating system here is the Cent0S7 version, so the download MHA version is 0.57.

two。 Case requirement

This case requires that the MySQL database should be automatically switched in case of failure through MHA monitoring, which does not affect the business.

3. Case realization train of thought

1) install MySQL database

2) configure MySQL one master and two slaves

3) install MHA software

4)

Configure password-free authentication

5) configure MySQL MHA high availability

6) simulate master failover

Install the database on each of the three MySQL nodes. For the MySQL version, please use the 5. 6.36 Magi cmake version.

Use 2.8.6. The following is only in Mysq11. The above is demonstrated, and the installation process is as follows.

Curl-o / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

Wget ftp://192.168.10.250/pub/MHA/*

1. Install the environment on which compilation depends

[root@Mysq11] # yum-y install ncurses-devel gcc-c++ perl-Module-Install

two。

Install the gmake_ compiler

Tar zxvf cmake-2.8.6.tar.gz

Cd cmake-2.8.6

. / configure

Gmake-J6 & & gmake install

3. Install the MySQL_ database

Tar-zxvf mysql-5.6.36.tar.gz

Cd mysql-5.6.36

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci\

-DWITH_EXTRA_CHARSETS=all\

-DSYSCONFDIR=/etc

Make-J12 & & make instal

Cp support-files/my-default.cnf / etc/my.cn

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

Chmod + x / etc/rc.d/init.d/mysqld

Chkconfig-add mysqld

Echo "PATH=$PATH:/usr/local/mysql/bin" > > / etc/profile

Source / etc/profile

Useradd-M-s / sbin/nologin mysql

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

/ usr/local/mysql/scripts/mysql_install_db\

-- basedir=/usr/local/mysql\

-- datadir=/usr/local/mysql/data\

-- user=mysql

4. Modify the main configuration file / etc/my of Master. Cnf file of three CVM

Server-id can't be the same.

Vim / etc/my.cnf

[mysqld]

Server-id = 1

Log-bin = master-bin

Log-slave-updates = true

Configure slave CVM:

Modify or add the following in / etc/my.cnf_.

Vim / etc/my. Cnf

Server-id = 2

Log-bin = master-bin

Relay-log = relay-log-bin

Relay-log-index = slave-relay-bin.index

5. Master, slave1 and slave2 make two soft links respectively.

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

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

/ usr/local/mysql/bin/mysqld_safe-user=mysql &

Systemctl stop firewalld.service

# synchronizing the use of user myslave from the library and the other is using manager to monitor user mha

Mysql > grant replication slave on *. * to 'myslave'@'192.168.1.%' identified by' 123'

Mysql > grant all privileges on *. * to 'mha'@'192.168.1.%' identified by' manager'

# the following three authorizations do not need to be added in theory, but the case experiment environment will report an error when it passes the MHA check

Grant all privileges on *. * to 'mha'@'host1' identified by' manager'

Grant all privileges on *. * to 'mha'@'host2' identified by' manager'

Grant all privileges on *. * to 'mha'@'host3' identified by' manager'

Change master to master_host='192.168.1.101',master_user='myslave'

Master_password='123',master_log_file='master-bin.000004',master_log_pos=245

Set global read_only=1

Curl-o / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

Every CVM needs an environment where Perl is installed.

Yum-y install epel-release-- nogpgcheck

Yum-y install perl-DBD-MySQL\

Perl-Config-Tiny\

Perl-Log-Dispatch\

Perl-Parallel-ForkManager\

Perl-ExtUtils-CBuilder\

Perl-ExtUtils-MakeMaker\

Perl-CPAN

Node is required for all services.

When installing manager CVM, you need to install the node package first.

Tar zxvf mha4mysql-node-0.57.tar.gz

Cd mha4mysql-node-0.57/

Perl Makefile.PL

Make & & make install

Manager CVM

Tar zxvf mha4mysql-manager-0.57.tar.gz

Perl Makefile.PL

Make & & make install

After manager is installed, several tools are generated under / usr/local/bin, mainly including the following:

Masterha_check_ssh checks the SSH configuration status of MHA

Masterha_check_repl checks MySQL replication status

Masterha_manger starts the script for manager

Masterha_check_status detects the current MHA running status

Masterha_master_monitor detects whether master is down.

Masterha_master_switch controls failover (automatic or manual)

Masterha_conf_ host adds or removes configured server information

Masterha_stop shuts down manager

4. After node is installed, several scripts will also be generated under / usr/local/bin (these tools are usually created by MHA

The script trigger of iManager does not require human operation) the main contents are as follows:

Save_binary_logs saves and copies master. Binary log of

Apply_diff_relay_logs identifies differential relay log events and applies their differential events to other slave

Filter_mysqlbinlog removes unnecessary ROLLBACK events (MHA no longer uses this tool)

Purge_relay_logs clears relay logs (does not block SQL threads)

Configure password-less access

Manager CVM

Ssh-keygen

Ssh-copy-id 192.168.1.101

Ssh-copy-id 192.168.1.102

Ssh-copy-id 192.168.1.103

Master CVM

Ssh-keygen

Ssh-copy-id 192.168.1.102

Ssh-copy-id 192.168.1.103

Slave1 CVM

Ssh-keygen

Ssh-copy-id 192.168.1.101

Ssh-copy-id 192.168.1.103

Slave2 CVM

Ssh-keygen

Ssh-copy-id 192.168.1.101

Ssh-copy-id 192.168.1.103

# configure MHA CVM

Copy the relevant scripts on the manager node to the / usr/local/bin directory

Cp-ra / mnt/mha4mysql-manager-0.57/samples/scripts / usr/local/bin/

Copy the script managed by VIP during the above automatic switching to cp / usr/local/bin/scripts/master_ip_failover / usr/local/bin

Vim / usr/local/bin/master_ip_failover

The script is on the FTP CVM

Create the MHA software directory and copy the configuration file

Mkdir / etc/masterha

Cp / mnt/mha4mysql-manager-0.57/samples/conf/app1.cnf / etc/masterha/

Vim / etc/masterha/app1.cnf

# Test whether the SSH of each node can be implemented

Masterha_check_ssh-conf=/etc/masterha/app1.cnf

# check whether mysql can listen

Masterha_check_repl-conf=/etc/masterha/app1.cnf

/ / Note: when configuring for the first time, you need to manually enable virtual IP on master.

/ sbin/ifconfig ens32:1 192.168.1.200/24

# start MHA manually

Nohup masterha_manager-conf=/etc/masterha/app1.cnf-remove_dead_master_conf-ignore_last_failover

/ var/log/masterha/app1/manager.log 2 > & 1 &

# View the current master status

Masterha_check_status-conf=/etc/masterha/app1.cnf

# Monitoring manager status

Tailf / var/log/masterha/app1/manager.log

Grant all on *. * to test@'192.168.1.%' identified by 'test'

After reading the detailed steps of building the MHA framework of mysql, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

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.