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

This article mainly introduces Mysql high availability cluster handouts, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated articles in industry information.

Mysql high availability cluster (mysql-mmm + master-slave synchronization)

Lord 14, Lord 13

From 11 to 12

Environment:

192.168.4.13 and 192.168.4.14 are principal and subordinate to each other

192.168.4.11 and 192.168.4.12 are slave servers of 192.168.4.13

First, configure master-slave synchronization

1. Synchronize the master-slave configuration of 14Universe 13.

fourteen

1 add authorized user

Mysql > grant replication slave on. To slaveuser@ "%" identified by "123456"

2 enable binlog Log

# vim / etc/my,cnf

Log-bin=master14

Server_id=14

Binlog_format= "mixed"

3 restart the database service

# systemctl stop mysqld

# systemctl start mysqld

4 View log information

Mysql > show master status

5 use the local database administrator to log in and specify the main database information

Mysql > change master to master_host= "192.168.4.13"

-> master_user= "slaveuser"

-> master_password= "123456"

-> master_log_file= "master13.000001"

-> master_log_pos=154

Mysql > start slave

Mysql > show slave status\ G

+ + +

thirteen

1 add authorized user

Mysql > grant replication slave on. To slaveuser@ "%" identified by "123456"

2 enable binlog Log

# vim / etc/my,cnf

Log-bin=master13

Server_id=13

Binlog_format= "mixed"

Log_slave_updates

3 restart the database service

# systemctl stop mysqld

# systemctl start mysqld

4 View log information

Mysql > show master status

5 use the local database administrator to log in and specify the main database information

Mysql > change master to master_host= "192.168.4.14"

-> master_user= "slaveuser"

-> master_password= "123456"

-> master_log_file= "master13.000001"

-> master_log_pos=154

Mysql > start slave

Mysql > show slave status\ G

+ +

Configure 11pr 12 as a slave server of 13

eleven

Specify server_id

# vim / etc/my.cnf

Server_id=11

Restart the database service

# systemctl stop mysqld

# systemctl start mysqld

Log in using the local database administrator to specify the main database information

Mysql > change master to master_host= "192.168.4.13"

-> master_user= "slaveuser"

-> master_password= "123456"

-> master_log_file= "master13.000001"

-> master_log_pos=154

Mysql > start slave

Mysql > show slave status\ G

+

twelve

Specify server_id

# vim / etc/my.cnf

Server_id=12

Restart the database service

# systemctl stop mysqld

# systemctl start mysqld

Log in using the local database administrator to specify the main database information

Mysql > change master to master_host= "192.168.4.13"

-> master_user= "slaveuser"

-> master_password= "123456"

-> master_log_file= "master13.000001"

-> master_log_pos=154

Mysql > start slave

Mysql > show slave status\ G

+ +

Testing on the client side

Add the user guser that accesses the data on 14 hosts, and the same authorized users on the other 3 hosts.

Mysql > create database gamedb

Mysql > grant all on gamedb. To guser@ "%" identified by "123456"

Mysql > select user,host from mysql.user

The client host uses the authorized user guser to connect to the 14 server, and the new data generated is also available on the other 3 hosts.

Mysql-h "192.168.4.14"-uguser-p123456

Select @ @ hostname; to view the name of the currently accessed server

# # #

2. Configure mysql-mmm

1 mysql-mmm introduction

Monitoring service: runs on the management node to monitor the data node

Agent service: runs on the data node and is used to provide the system to the monitoring host

2 install mysql-mmm software on all hosts

Unzip mysql-mmm.zip

Cd mysql-mmm

Tar-zxvf mysql-mmm-2.2.1.tar.gz

Cd mysql-mmm-2.2.1/

Make install

Ls / etc/mysql-mmm/.conf

3 modify the configuration file

Modify the data node agent service configuration file (modify 11dy12 13p14)

Vim / / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This db11 / / Custom name

Modify the configuration file of the management node monitoring service (modify 15)

Vim / etc/mysql-mmm/mmm_mon.conf

Include mmm_common.conf

Ip 192.168.4.15

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.11, 192.168.4.12, 192.168.4.13, 192.168.4.14

Monitor_user monitor

Monitor_password 123456

Debug 0

Modify public files

On the eleventh

Vim / etc/mysql-mmm/mmm_common.conf

1 active_master_role writer

two

three

four

5 cluster_interface eth0

six

7 pid_path / var/run/mmm_agentd.pid

8 bin_path / usr/lib/mysql-mmm/

nine

10 replication_user slaveuser

11 replication_password 123456

twelve

13 agent_user agent

14 agent_password 123456

fifteen

sixteen

seventeen

18 ip 192.168.4.14

19 mode master

20 peer db13

twenty-one

twenty-two

twenty-three

24 ip 192.168.4.13

25 mode master

26 peer db14

twenty-seven

twenty-eight

twenty-nine

30 ip 192.168.4.12

31 mode slave

thirty-two

thirty-three

thirty-four

35 ip 192.168.4.11

36 mode slave

thirty-seven

thirty-eight

thirty-nine

40 hosts db13, db14

41 ips 192.168.4.100

42 mode exclusive

forty-three

forty-four

forty-five

46 hosts db11, db12

47 ips 192.168.4.101, 192.168.4.102

48 mode balanced

forty-nine

Scp / etc/mysql-mmm/mmm_common.conf 192.168.4.15:/etc/mysql-mmm/ (copy the public files to 12, 13, 14, 15)

Add the corresponding authorized user on the data node host according to the settings of the configuration file

Grant replication client on. To monitor@ "%" identified by "123456"

Grant replication client,process,super on. To agent@ "%" identified by "123456"

Select user,host from mysql.user where user= "agent" or user= "monitor"

4 start the service

Start the proxy service on the data node host: mmm_agent

The installation service runs dependent on the software package installation to obtain the vip address such as the na Jinbao startup service

On 11murmur14.

Tar-zxf Algorithm-Diff-1.1902.tar.gzcd Algorithm-Diff-1.1902/perl Makefile.PLmakemake installtar-zxf Proc-Daemon-0.03.tar.gzcd Proc-Daemon-0.03/perl Makefile.PLmakemake installrpm-ivh-nodeps perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm/etc/init.d/mysql-mmm-agent start/etc/init.d/mysql-mmm-agent statusnetstat-untlap | grep mmmnetstat-untlap | grep: 9989yum-y install gcc gcc- C++gunzip Net-ARP-1.0.8.tgztar-xf Net-ARP-1.0.8.tarcd Net-ARP-1.0.8/perl Makefile.PLmakemake install

On 15

Tar-zxf Algorithm-Diff-1.1902.tar.gzcd Algorithm-Diff-1.1902/perl Makefile.PLmakemake installtar-zxf Proc-Daemon-0.03.tar.gzcd Proc-Daemon-0.03/perl Makefile.PLmakemake installrpm-ivh-- nodeps perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm

Start the monitoring service on the host of the management node: mmm_mond

Installation service runs dependent package startup service

/ etc/init.d/mysql-mmm-monitor status/etc/init.d/mysql-mmm-monitor startnetstat-untalp | grep: 9988ls / var/log/mysql-mmm/vim / var/log/mysql-mmm/mmm_mond.log

5 verify the configuration of mysql-mmm

Check that the database service on the database node is running

Systemctl status mysqldmysql-uroot-p123456-e "show slave status\ G" | grep-I YES

Local login management on the monitoring server

[root@mysql15 ~] # mmm_control show View status

(Maybe you should just omit the defined ())

Db11 (192.168.4.11) slave/AWAITING_RECOVERY. Roles:

Db12 (192.168.4.12) slave/AWAITING_RECOVERY. Roles:

Db13 (192.168.4.13) master/AWAITING_RECOVERY. Roles:

Db14 (192.168.4.14) master/AWAITING_RECOVERY. Roles:

Mmm_control set_online hostname setting host to online status mmm_control set_offline hostname setting host to offline statu

Set the status of 4 database hosts to online:

Mmm_control set_online db11mmm_control set_online db12mmm_control set_online db13mmm_control set_online db14mmm_control show

Check whether the vi address is obtained locally on the data node.

Ip addr show | grep 192.168.4.100

Client connects to vip to access database service

Mysql-h292.168.4.100-uguser-p123456

Mysql > select @ @ hostname

The change of virtual IP can be tested by the stop mysqld service of 11BI 12pm 13J 14.

After reading the above handouts on Mysql high-availability cluster, I hope it can bring some help to you in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.