Shulou(Shulou.com)05/31 Report--

MySQL high availability cluster introduction and usage, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1 Overview of MMM

1.1 about mysql-mmm

MySQL Primary Primary replication Manager

A suite of scripts for monitoring, failover, and management of MySQL master replication configurations

Read load balancing and data backup for any number of slave servers in the standard master-slave configuration

The failover of the server can be realized through the MMM scheme, thus the high availability of mysql can be realized.

1.2 introduction to highly available clusters:

Active and standby mode when the master cannot provide services, the standby host takes over from it to provide services, which is transparent to the client.

1.3 case premise environment: master-slave structure

Five servers, two data servers are the master of each other, and one of the other two data servers is a slave and a monitoring server.

Master51:192.168.4.51

Master52:192.168.4.52

Slave53:192.168.4.53

Slave54:192.168.4.54

Write-vip:192.168.4.100

Read-vip:192.168.4.101/102

1.4 main mode configuration

(1) user authorization

Configure on master51

Mysql > grant replication slave on *. * to username @ 192.168.4.52 identified by "password"

The same operation is authorized to 192.168.4.51 on master52.

(2) enable binlog log

Master51 and master52 remain consistent except that server_id is inconsistent.

Vim / etc/my.cnf

Server_id=51

Log-bin

Binlog-format= "mixed"

(3) restart the database server

Systemctl restart mysqld

(4) check the binlog log file that is in use.

Mysql > show master status

Configure from the server

When master51 is a master database and master52 is a slave database:

(1) Test whether the authorized user of the main library is valid on 52

# mysql-h292.168.4.51-u username-p password

Mysql > show grants

(2) Log in to the main database information of the local data administrator in 52

Mysql > change master to

-> master_host= "192.168.4.51"

-> master_user= "user name"

-> master_password= "password"

-> master_log_file= "main file name", which can be seen in # show master status.

-> master_log_pos= number; # View the main Position (offset position)

(5) enable slave service

Mysql > start slave

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

You must ensure that the status of the Slave_IO,Slave_SQL is yes.

(6) when master52 is a master database and master51 is a slave database, the operation on master51 is the same as above.

2 Mysql-MMM framework

2.1 Server role

Management node: mmm-monitor (service process) (55 servers)

# the monitoring daemon responsible for all monitoring work, deciding whether to remove or restore the failed node

Database node: mmm-agent (service process) (that is, 51Murmur54 server)

# Agent daemon running on the Mysql server, providing a simple set of remote services to the monitoring node

2.2 Core Software package and its function

Net-ARP: assigning virtual Ip addresses

The core process of mysql-mmm architecture

2.3 configuration

(1) tar-xf mysql-mmm-2.2.1.tar.gz (installed on all hosts)

View the documentation and execute make install

Configuration file path: / etc/mysql-mmm

(2) modify the configuration file of data node host mmm_agentd service

Vim / etc/mysql-mmm/mmm_agent.conf # specifies the name of the server in the cluster, which cannot be repeated

This hostname # configuration declares its own hostname

(3) modify the configuration file of the host mmm_monitor service of the management node

Vim / etc/mysql-mmm/mmm_mon.conf

Include mmm_common.conf # loading public configuration files

Ip 192.168.4.55 # Management node host (that is, the IP address of the monitoring host)

Pid_path / var/run/mmm_mond.pid

Bin_path / usr/lib/mysql-mmm/

Status_path / var/lib/misc/mmm_mond.status

Ping_ips 192.168.4.51, 192.168.4.52, 192.168.4.53, 192.168.4.54 # set the monitored database

Monitor_user monitor # mysql users of the monitoring database

Monitor_password 123456

Debug 0 # startup service does not display startup information (1 display)

(4) modify the public configuration file

Vim / etc/mysql-mmm/mmm_common.conf # is modified and sent to other servers

Active_master_role writer

Cluster_interface eth0

Pid_path / var/run/mmm_agentd.pid

Bin_path / usr/lib/mysql-mmm/

Replication_user ser51

# set master-slave synchronization users to be the same as those authorized by the master database on this machine

# can be viewed in / var/lib/mysql/master.info

Replication_password 123456

Agent_user agent # mmm_agent controls the user

Agent_password 123456

# set the first master server

Ip 192.168.4.51 # Master server IP

Mode master

Peer master52 # specify another primary server

# set up the second primary server

Ip 192.168.4.52

Mode master

Peer master51

Ip 192.168.4.53

Mode slave

Ip 192.168.4.54

Mode slave

# set the working mode of write server

Hosts master51, the master server written by master52

Ips 192.168.4.100 # set the VIP address

Mode exclusive # exclusive mode, only 51Accord 52 is allowed to use

Hosts slave53, slave54

Ips 192.168.4.101, 192.168.4.102 # reader-vip

Mode balanced # equilibrium mode

(5) add the corresponding authorized user to the data node host according to the configuration file settings

Management node monitoring database user authorization

Mysql > grant replication client on *. * to monitor@'%' identified by "123456"

Database node controls database user authorization

Mysql > grant replication client,process,super on *. * to agent@'%' identified by "123456"

2.4 installation dependencies

Installed on all servers:

Write a script:

Vim install.sh

#! / bin/bash

Yum-y install perl-*

Rpm-Q gcc gcc-c++

If [$?-ne 0]; then

Yum-y install gcc gcc-c++

Fi

Tar-xf Algorithm-Diff-1.1902.tar.gz

Cd Algorithm-Diff-1.1902

Perl Makefile.PL

Make

Make install

Cd..

Rpm-ivh perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm

Tar-zxf Proc-Daemon-0.03.tar.gz

Cd Proc-Daemon-0.03

Perl Makefile.PL

Make

Make install

Cd..

Gunzip Net-ARP-1.0.8.tgz

Tar-xf Net-ARP-1.0.8.tar

Cd Net-ARP-1.0.8/

Perl Makefile.PL

Make

Make install

2.5 start the service

(1) start the data node host mmm_agentd service

[root@ser51 ~] # / etc/init.d/mysql-mmm-agent start (stop,status)

[root@ser55 ~] # netstat-anptu | grep 9989

(2) start the host mmm_monitor service of the management node

[root@ser55 ~] # / etc/init.d/mysql-mmm-monitor start

[root@ser55 ~] # netstat-anptu | grep 9988

(3) View the monitoring information on the host of the management node

[root@ser55 ~] # mmm_control show

Defined (@ array) is deprecated at / usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined ())

Master51 (192.168.4.51) master/AWAITING_RECOVERY. Roles

Master52 (192.168.4.52) master/AWAITING_RECOVERY. Roles

Slave53 (192.168.4.54) slave/AWAITING_RECOVERY. Roles

Slave54 (192.168.4.53) slave/AWAITING_RECOVERY. Roles

[root@ser55 ~] # mmm_control set_online master51 (name set in agent configuration file; set_offine: offline)

Defined (@ array) is deprecated at / usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.

(Maybe you should just omit the defined ())

Master51 (192.168.4.51) master/ONLINE. Roles: writer (192.168.4.100)

Master52 (192.168.4.52) master/ONLINE. Roles:

Slave53 (192.168.4.54) slave/ONLINE. Roles: reader (192.168.4.102)

Slave54 (192.168.4.53) slave/ONLINE. Roles: reader (192.168.4.101)

At this point, ping 192.168.4.100 ping can be reached with 101 ip 102 (make sure that these CPUs in this network segment are not occupied)

(4) does the data node host view the VIP address?

Ip addr show | grep 192.168.4.

2.6 Test

Mysql > grant all on *. * to user name @'% 'identified by "123456"; # authorize client to connect to database user

Mysql-h292.168.4.100-u user name-p123456

Whether you turn off the database service of master51 or master52, VIP will be occupied by one of them. Users will be able to connect to the database as well as the same VIP, and after the database is operated, the data will be automatically synchronized when the main database server is restarted. VIP will not be preempted at this time.

Stop the agent service. When one of the master databases stops, the monitor cannot automatically switch VIP.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.