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

Configure MySQL High availability Cluster +

Host role:

Client client50

Database server mysql51 to mysql55

Manage host mgm56

VIP address 192.168.4.100

Topology:

Client50 | mysql51 Master |

| | |

Mysql52 mysql53 mysql54 mysql55 mgm56

From managing the cluster

Backup master

+

Configure MySQL master-slave synchronization structure: one master and multiple slaves

Configure the main library 51

Configure standby slave 52

Configure standby slave library 53

Configure pure slave library 54

Configure pure slave library 55

51-55 will be carried out.

Install MySQL [yum-y install mysql-community-*]

Mysql-5.7.17.tartar-xf

Mysql-community-client-5.7.17-1.el7.x86_64.rpm

Mysql-community-common-5.7.17-1.el7.x86_64.rpm

Mysql-community-devel-5.7.17-1.el7.x86_64.rpm

Mysql-community-embedded-5.7.17-1.el7.x86_64.rpm

Mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm

Mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm

Mysql-community-libs-5.7.17-1.el7.x86_64.rpm

Mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

Mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm

Mysql-community-server-5.7.17-1.el7.x86_64.rpm

Mysql-community-test-5.7.17-1.el7.x86_64.rpm

Install mha_node software [mha4mysql-node-0.56-0.el6.noarch.rpm] on all servers 51-56

] # yum-y install perl-DBD-mysql

] # rpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Configure that all database hosts can authenticate and log in to each other by means of ssh key pairs [password-free]

Configure manager56 host password-free ssh login to all database hosts [password-free]

3. Configure master-slave synchronization. The requirements are as follows:

51 open semi-synchronous replication of main library

52 open semi-synchronous replication from the library (standby primary database)

53 semi-synchronous replication from the library (standby primary library)

54 the slave library does not act as a standby primary database, so there is no need to open semi-synchronous replication.

55 the slave library does not act as a backup primary database, so there is no need to open semi-synchronous replication.

Configure semi-synchronous replication mode

Check whether the current database service supports dynamic loading of modules

Mysql > show variables like 'have_dynamic_loading'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

Install the module using the command

Mysql >

Mysql > INSTALL PLUGIN rpl_semi_sync_master

-> SONAME 'semisync_master.so'

Mysql > INSTALL PLUGIN rpl_semi_sync_slave

-> SONAME 'semisync_slave.so'

View module status

Mysql >

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS

WHERE PLUGIN_NAME LIKE'% semi%'

+-+ +

| | PLUGIN_NAME | PLUGIN_STATUS |

+-+ +

| | rpl_semi_sync_master | ACTIVE |

| | rpl_semi_sync_slave | ACTIVE |

+-+ +

Enable semi-synchronous replication

Mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

Check to see if it is enabled successfully

Mysql > show variables like 'rpl_semisync%_enabled'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_slave_enabled | ON |

+-+ +

Write the configuration into the configuration file to make it permanent

3. Master51 configuration:

Vim / etc/my.cnf

[mysqld]

# enable plug-in to load semi-synchronous replication

Plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Rpl-semi-sync-master-enabled = 1

Rpl-semi-sync-slave-enabled = 1

Relay_log_purge=off; / / does not allow automatic deletion of relay log files [mysql > set global relay_log_purge=off;]

Server_id=51

Log-bin=master51

Binlog-format= "mixed"

: wq

# systemctl restart mysqld

# mysql-uroot-p123abcxyz

Mysql > grant replication slave on. To repluser@ "" identified by "123abcxyz"

Mysql > show master status

Note: repluser users are also required on backup 52-53.

3.2.Configuration of standby master52

Vim / etc/my.cnf

[mysqld]

Plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Rpl-semi-sync-master-enabled = 1

Rpl-semi-sync-slave-enabled = 1

Relay_log_purge=off; / / does not allow automatic deletion of relay log files [mysql > set global relay_log_purge=off;]

Server_id=52

Log-bin=master52

Binlog-format= "mixed"

: wq

# systemctl restart mysqld

] # ls / var/lib/mysql/master52.*

/ var/lib/mysql/master52.000001 / var/lib/mysql/master52.index

# mysql-uroot-p123abcxyz

Mysql > change master to

-> master_host= "192.168.4.51"

-> master_user= "repluser"

-> master_password= "123abcxyz"

-> master_log_file= "master51.000001"

-> master_log_pos=441

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

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.3.Configuration of standby master53

] # vim / etc/my.cnf

[mysqld]

Plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Rpl-semi-sync-master-enabled = 1

Rpl-semi-sync-slave-enabled = 1

Relay_log_purge=off; / / does not allow automatic deletion of relay log files [mysql > set global relay_log_purge=off;]

Server_id=53

Log-bin=master53

Binlog-format= "mixed"

: wq

] # systemctl restart mysqld

] # ls / var/lib/mysql/master53.*

] # mysql-uroot-p123abcxyz

Mysql > change master to master_host= "192.168.4.51", master_user= "repluser", master_password= "123abcxyz", master_log_file= "master51.000001", master_log_pos=441

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

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.4. Configure slave server 54

] # vim / etc/my.cnf

[mysqld]

Server_id=54

: wq

] # systemctl restart mysqld

] # mysql-uroot-p123abcxyz

Mysql > change master to master_host= "192.168.4.51", master_user= "repluser", master_password= "123abcxyz", master_log_file= "master51.000001", master_log_pos=441

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

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.5. Configure slave server 55

] # vim / etc/my.cnf

[mysqld]

Server_id=55

: wq

] # systemctl restart mysqld

] # mysql-uroot-p123abcxyz

Mysql > change master to master_host= "192.168.4.51", master_user= "repluser", master_password= "123abcxyz", master_log_file= "master51.000001", master_log_pos=441

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

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

[configuration 51-55 master-slave synchronization completed]

An is saved on all database servers 51-55 to authorize monitoring users

Mysql >

Grant all on. To root@'%' identified by '123abcxyz'

+ +

Prepare the cluster environment:

1 install the perl package in the shared directory on all hosts (51-56)

Common configuration: install software packages on all hosts

Perl-Config-Tiny-2.14-7.el7.noarch.rpm

Perl-Mail-Sender-0.8.23-1.el7.noarch.rpm

Perl-MIME-Types-1.38-2.el7.noarch.rpm

Perl-Email-Date-Format-1.002-15.el7.noarch.rpm

Perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm

Perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

Perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm

Perl-MIME-Lite-3.030-1.el7.noarch.rpm

] # yum-y install perl-.rpm

2 install the mha_node package on all servers 51-56

] # cd mha-soft-student/

] # yum-y install perl-DBD-mysql

] # rpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

4 install the mha_manager package on the management host 56

] # yum-y install perl-ExtUtils- perl-CPAN-*

] # tar-zxf mha4mysql-manager-0.56.tar.gz

] # cd mha4mysql-manager-0.56

] # perl Makefile.pl / / [yum-y install perl-ExtUtils- perl-CPAN- can be obtained by using the yum list | grep-I keyword for the required dependency package]

] # make

] # make install

Configure MHA:

Create administrative commands on administrative host 56

] # mkdir / root/bin

] # cd mha4mysql-manager-0.56/bin

] # cp * / root/bin/

Create the master configuration and edit

] # mkdir / etc/mha

] # cp mha4mysql-manager-0.56/samples/conf/app1.cnf / etc/mha/

] # vim / etc/mha/app1.cnf

[server default] # default configuration of service

Manager_workdir=/etc/mha # working directory

Manager_log=/etc/mha/manager.log # Log File

Master_ip_failover_script=/etc/mha/master_ip_failover # failover script

# ssh service username and port

Ssh_user=root

Ssh_port=22

# when master-slave replication, connect the username and password of the master library from the slave library

Repl_user=repluser

Repl_password=123abcxyz

# user and password to connect to the server when monitoring the database

User=root

Password=123abcxyz

# specify database server ip and role

[server1]

Hostname=192.168.4.51

Candidate_master=1

Port=3306

[server2]

Hostname=192.168.4.52

Candidate_master=1

Port=3306

[server3]

Hostname=192.168.4.53

Candidate_master=1

Port=3306

[server4]

Hostname=192.168.4.54

No_master=1

Port=3306

[server5]

Hostname=192.168.4.55

No_master=1

Port=3306

: wq

Create a failover script and edit [normally under the mha4mysql-manager-0.56 directory]

] # cd mha-soft-student

] # cp master_ip_failover / etc/mha/

] # chmod + x / etc/mha/master_ip_failover

] # vim / etc/mha/master_ip_failover

35 my $vip = '192.168.4.100 pound 2400; # Virtual IP

36 my $key = "1"

37 my $ssh_start_vip = "/ sbin/ifconfig eth0:$key $vip"

38 my $ssh_stop_vip = "/ sbin/ifconfig eth0:$key down"

: wq

Manually deploy the ip address specified in the script on the current main library 51.

Mysql51] # ifconfig eth0:1 192.168.4.100/24

Mysql51] # ifconfig eth0:1

Start the service

1 check ssh connection

Mgm56] # masterha_check_ssh-- conf=/etc/mha/app1.cnf

2 check the master-slave synchronization configuration

Mgm56] # masterha_check_repl-- conf=/etc/mha/app1.cnf

3 start the management service

Mgm56] #

Masterha_manager-- conf=/etc/mha/app1.cnf\

-remove_dead_master_conf-ignore_last_failover

4 View status

] # masterha_check_status-- conf=/etc/mha/app1.cnf

App1 (pid:9541) is running (0:PING_OK), master:192.168.4.51

Test configuration

Connect to vip 192.168.4.100 on the client 50 host to access the database service

1 main library for user authorization

Mysql51 > create database db3;create table db3.a (id int)

Mysql51 > grant select,insert on db3. To test@ "%" identified

By "123abcxyz"

2 the client connects with an authorized user

Client50] # mysql-h292.168.4.100-utest-p123abcxyz db3

Mysql >

Mysql > insert into db3.a values

Query OK, 1 row affected (0.07 sec)

Mysql > select from db3.a

Test highly available configuration

Stop the database service of the main database 51

The client can still connect to the VIP address 192.168.4.100 to access the database service

Mysql51] # systemctl stop mysqld

Client50] # ping-c 2 192.168.4.100

Client50] # mysql-h292.168.4.100-uplj-p123abcxyz db3

Mysql > select @ @ hostname

Mysql > select * from db3.a

Mysql > insert into db3.a values

There is no configuration of the main library 51 in the / etc/mha/app1.cnf file

Database 53-55 points the main library address to the newly elected main library ip address

Add the broken database server 51 to the cluster.

MySQL51] # systemctl start mysqld

MySQL51] # mysql-uroot-p123abcxyz

Mysql > change master to master_host= "192.168.4.52"

-> master_user= "repluser"

-> master_password= "123abcxyz"

-> master_log_file= "master52.000002", the current master database log

-> pos point corresponding to master_log_pos=1237;

Mysql > start slave

Mysql > show slave status\ G; both IO and SQL processes are Yes host ip 52

Mgm56] # vim / etc/mha/app1.cnf

[server1]

Candidate_master=1

Hostname=192.168.4.51

: wq

Mgm56] #

Masterha_manager-- conf=/etc/mha/app1.cnf\

-remove_dead_master_conf-ignore_last_failover

View statu

Mgm56] # masterha_check_status-- conf=/etc/mha/app1.cnf

App1 (pid:9541) is running (0:PING_OK), master:192.168.4.52

After stopping the database service of the main database 52, the client can still connect

The vip address 192.168.4.100 accesses the MySQL database service.

There is no configuration of the main library 52 in the / etc/mha/app1.cnf file

Database 53-55 points the main library address to the newly elected main library ip address [log latest 51UX 53]

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.