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

Overview of MMM

MMM (Master-Master replication manager for MySQL) is a set of scripts that support dual-master failover and dual-master daily management. MMM is developed in the Perl language and is mainly used to monitor and manage MySQL Master-Master (dual master) replication, which can be said to be the mysql master replication manager. Although it is called dual-master replication, only one master is allowed to write at the same time in the business, and the other alternative master provides partial read service to speed up the warm-up of the alternative master at the time of master switching. It can be said that MMM script program on the one hand realizes the function of failover, on the other hand, its internal additional tool scripts can also achieve read load balancing of multiple slave. A scalable suite of scripts for monitoring, failover, and management of mysql master replication configurations (only one node can be written at any time). This suite can also load balance any number of slave servers that reside in the standard master-slave configuration, so you can use it to launch virtual ip on a group of servers that reside in replication It also has scripts that implement data backup and resynchronization between nodes.

MMM provides both automatic and manual ways to remove the virtual ip of servers with high replication latency in a group of servers. At the same time, it can also back up data and achieve data synchronization between the two nodes. Because MMM cannot completely guarantee data consistency, MMM is suitable for scenarios where data consistency is not very high, but you want to maximize business availability. MySQL itself does not provide a replication failover solution, through the MMM scheme to achieve server failover, so as to achieve the high availability of mysql. For those businesses that require high data consistency, a highly available architecture such as MMM is not recommended.

Experimental environment

Master server 1VR 192.168.52.135 db1 writer VIP:192.168.52.200

Master server 2VR 192.168.52.133 db2

Slave server 1VR 192.168.52.134 db3 reader VIP:192.168.52.210192.168.52.220

Slave server 2VR 192.168.52.148 db4

Monitoring server: 192.168.52.150

Experimental procedure

First, install mariadb service on four MySQL servers

Configure the local yum source (do the same on all four servers)

[root@localhost ~] # wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

# configure aliyun source

-- 2019-11-25 09Rose 51MU 39Mi-http://mirrors.aliyun.com/repo/Centos-7.repo

Parsing host mirrors.aliyun.com (mirrors.aliyun.com)... 124.14.2.219, 124.14.2.218, 124.14.2.234,...

Connecting mirrors.aliyun.com (mirrors.aliyun.com) | 124.14.2.219 |: 80. Connected.

A HTTP request has been issued and is waiting for a response. 200 OK

Length: 2523 (2.5K) [application/octet-stream]

Saving to: / etc/yum.repos.d/CentOS-Base.repo

2523 [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= >] 100% [= = >] 100% [= >] 100% [= >] 100% [>] 100% [= >] 100% [>] 100% [= >] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [= >] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [

2019-11-25 09:51:39 (225 MB/s)-saved "/ etc/yum.repos.d/CentOS-Base.repo" [2523 Universe 2523])

[root@localhost ~] # yum-y install epel-release # install epel-release source

. / / omit the process

[root@localhost ~] # yum clean all & & yum makecache # clear the old cache and synchronize the current server cache

.. / / omit the process

Install the mariadb (one of the MySQL services) service on master server 1

[root@localhost ~] # hostnamectl set-hostname db1 # modify hostname

[root@localhost ~] # su # switch bash environment

[root@db1 ~] #

[root@db1 ~] # yum install mariadb-server mariadb- y # installation service

. / / omit the process

[root@db1 ~] # vim / etc/my.cnf # modify the configuration file

[mysqld]

Log_error=/var/lib/mysql/mysql.err

Log=/var/lib/mysql/mysql_log.log

Log_slow_queries=/var/lib/mysql_slow_queris.log

Binlog-ignore-db=mysql,information_schema

Character_set_server=utf8

Log_bin=mysql_bin

Server_id=1

Log_slave_updates=true

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

[root@db1 ~] # systemctl stop firewalld.service # turn off the firewall

[root@db1 ~] # setenforce 0 # turn off enhanced security features

[root@db1 ~] # systemctl start mariadb.service # enable the service

[root@db1 ~] # netstat-ntap | grep 3306 # View Port

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 4601/mysqld

[root@db1 ~] #

Install the mariadb service on primary server 2

[root@localhost ~] # hostnamectl set-hostname db2

[root@localhost ~] # su

[root@db2 ~] #

[root@db2 ~] # yum install mariadb-server mariadb- y

[root@db2 ~] # vim / etc/my.cnf

[mysqld]

Log_error=/var/lib/mysql/mysql.err

Log=/var/lib/mysql/mysql_log.log

Log_slow_queries=/var/lib/mysql_slow_queris.log

Binlog-ignore-db=mysql,information_schema

Character_set_server=utf8

Log_bin=mysql_bin

Server_id=2

Log_slave_updates=true

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

[root@db2 ~] # systemctl stop firewalld.service

[root@db2 ~] # setenforce 0

[root@db2 ~] # systemctl start mariadb.service

[root@db2 ~] # netstat-ntap | grep 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 50643/mysqld

[root@db2 ~] #

Install the mariadb service on slave server 1

[root@localhost ~] # hostnamectl set-hostname db3

[root@localhost ~] # su

[root@db3 ~] #

[root@db3 ~] # yum install mariadb-server mariadb- y

[root@db3 ~] # vim / etc/my.cnf

[mysqld]

Log_error=/var/lib/mysql/mysql.err

Log=/var/lib/mysql/mysql_log.log

Log_slow_queries=/var/lib/mysql_slow_queris.log

Binlog-ignore-db=mysql,information_schema

Character_set_server=utf8

Log_bin=mysql_bin

Server_id=11

Log_slave_updates=true

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

[root@db3 ~] # systemctl stop firewalld.service

[root@db3 ~] # setenforce 0

[root@db3 ~] # systemctl start mariadb.service

[root@db3 ~] # netstat-ntap | grep 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 50578/mysqld

[root@db3 ~] #

Install the mariadb service on slave server 2

[root@localhost ~] # hostnamectl set-hostname db4

[root@localhost ~] # su

[root@db4 ~] #

[root@db4 ~] # yum install mariadb-server mariadb- y

[root@db4 ~] # vim / etc/my.cnf

[mysqld]

Log_error=/var/lib/mysql/mysql.err

Log=/var/lib/mysql/mysql_log.log

Log_slow_queries=/var/lib/mysql_slow_queris.log

Binlog-ignore-db=mysql,information_schema

Character_set_server=utf8

Log_bin=mysql_bin

Server_id=22

Log_slave_updates=true

Sync_binlog=1

Auto_increment_increment=2

Auto_increment_offset=1

[root@db4 ~] # systemctl stop firewalld.service

[root@db4 ~] # setenforce 0

[root@db4 ~] # systemctl start mariadb.service

[root@db4 ~] # netstat-ntap | grep 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 39804/mysqld

[root@db4 ~] #

Second, configure MySQL multi-master and multi-slave mode

1. Check the location of log_bin logs and poss values of master server 1 and master server 2, respectively

Primary server 1:

[root@db1 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 2

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > show master status; # View status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 245 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

Primary Server 2:

[root@db2 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 2

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > show master status; # View status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql_bin.000003 | 245 | | mysql,information_schema | |

+-+

1 row in set (0.00 sec)

Master server 1 and master server 2 enhance access to each other

Primary server 1:

MariaDB [(none)] > grant replication slave on. To 'replication'@'192.168.52.%' identified by' 123456'

# raise the right

Query OK, 0 rows affected (0.01 sec)

Primary Server 2:

MariaDB [(none)] > grant replication slave on. To 'replication'@'192.168.52.%' identified by' 123456'

# raise the right

Primary server 1:

MariaDB [(none)] > change master to master_host='192.168.52.133',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

# specify specific information of the synchronization server

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > start slave; # enable synchronization

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > show slave status\ G; # View status

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.52.133

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql_bin.000003

Read_Master_Log_Pos: 575

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 694

Relay_Master_Log_File: mysql_bin.000003

Slave_IO_Running: Yes # confirm to enable

Slave_SQL_Running: Yes # confirm to enable

Master_Server_Id: 2

1 row in set (0.00 sec)

ERROR: No query specified

Primary Server 2:

MariaDB [(none)] > change master to master_host='192.168.52.135',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

# specify specific information of the synchronization server

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)] > start slave; # enable synchronization

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > show slave status\ G; # View status

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.52.135

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql_bin.000003

Read_Master_Log_Pos: 575

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 694

Relay_Master_Log_File: mysql_bin.000003

Slave_IO_Running: Yes # confirm to enable

Slave_SQL_Running: Yes # confirm to enable

Master_Server_Id: 1

1 row in set (0.00 sec)

ERROR: No query specified

Test master master synchronization

Primary server 1:

MariaDB [(none)] > create database school; # create database school

Query OK, 1 row affected (0.01sec)

MariaDB [(none)] > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | school | # created successfully |

| | test |

+-+

5 rows in set (0.00 sec)

Primary Server 2:

MariaDB [(none)] > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | school | # synchronization succeeded |

| | test |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > drop database school; # Delete database school

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > show databases; # View database (deleted successfully)

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

Primary server 1:

MariaDB [(none)] > show databases; # View the database (synchronization succeeded)

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

Configure slave servers 1 and 2 as slave libraries for master server 1

From server 1:

[root@db3 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 2

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > change master to master_host='192.168.52.135',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > start slave

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] >

From Server 2:

[root@db4 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 2

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > change master to master_host='192.168.52.135',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)] > start slave

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] >

Test multi-master and multi-slave mode

Primary server 1:

MariaDB [(none)] > create database myschool; # create database myschool

Query OK, 1 row affected (0.01sec)

MariaDB [(none)] > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | myschool | # created successfully |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > quit

Bye

[root@db1 ~] #

Primary Server 2:

MariaDB [(none)] > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | myschool | # synchronization succeeded |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > quit

Bye

[root@db2 ~] #

From server 1:

MariaDB [(none)] > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | myschool | # synchronization succeeded |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > quit

Bye

[root@db3 ~] #

From Server 2:

MariaDB [(none)] > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | myschool | # synchronization succeeded |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > quit

Bye

[root@db4 ~] #

3. Install and configure MySQL-MMM

1. Four MySQL servers install MMM respectively

[root@db1 ~] # yum-y install mysql-mmm*

. / / omit the process

[root@db1 ~] #

[root@db2 ~] # yum-y install mysql-mmm*

. / / omit the process

[root@db2 ~] #

[root@db3 ~] # yum-y install mysql-mmm*

. / / omit the process

[root@db3 ~] #

[root@db4 ~] # yum-y install mysql-mmm*

. / / omit the process

[root@db4 ~] #

2. Modification

[root@db1 ~] # vim / etc/mysql-mmm/mmm_common.conf

Cluster_interface ens33

Pid_path / run/mysql-mmm-agent.pid

Bin_path / usr/libexec/mysql-mmm/

Replication_user replication

Replication_password 123456

Agent_user mmm_agent

Agent_password 123456

Ip 192.168.52.135

Mode master

Peer db2

Ip 192.168.52.133

Mode master

Peer db1

Ip 192.168.52.134

Mode slave

Ip 192.168.52.148

Mode slave

Hosts db1, db2

Ips 192.168.52.200

Mode exclusive

Hosts db3, db4

Ips 192.168.52.210, 192.168.52.220

Mode balanced

Push the / etc/mysql-mmm/mmm_common.conf configuration file to the other three servers

[root@db1 ~] # scp / etc/mysql-mmm/mmm_common.conf root@192.168.52.133:/etc/mysql-mmm/

Root@192.168.52.133's password:

Mmm_common.conf 100% 837 1.1MB/s 00:00

[root@db1 ~] # scp / etc/mysql-mmm/mmm_common.conf root@192.168.52.134:/etc/mysql-mmm/

Root@192.168.52.134's password:

Mmm_common.conf 100% 837 1.1MB/s 00:00

[root@db1 ~] # scp / etc/mysql-mmm/mmm_common.conf root@192.168.52.148:/etc/mysql-mmm/

Root@192.168.52.148's password:

Mmm_common.conf 100% 837 951.0KB/s 00:00

Configure the local yum source on the monitoring server monitor

[root@localhost ~] # hostnamectl set-hostname monitor

[root@localhost ~] # su

[root@monitor ~] # wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

-- 2019-11-25 11 40RV 39-http://mirrors.aliyun.com/repo/Centos-7.repo

Parsing host mirrors.aliyun.com (mirrors.aliyun.com)... 124.14.2.222, 124.14.2.218, 124.14.2.234,...

Connecting mirrors.aliyun.com (mirrors.aliyun.com) | 124.14.2.222 |: 80. Connected.

A HTTP request has been issued and is waiting for a response. 200 OK

Length: 2523 (2.5K) [application/octet-stream]

Saving to: / etc/yum.repos.d/CentOS-Base.repo

2523 [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= = >] 100% [= >] 100% [= = >] 100% [= >] 100% [= >] 100% [>] 100% [= >] 100% [>] 100% [= >] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [= >] 100% [>] 100% [>] 100% [>] 100% [>] 100% [>] 100% [

2019-11-25 11:40:39 (663 MB/s)-saved "/ etc/yum.repos.d/CentOS-Base.repo" [2523 Universe 2523])

[root@monitor ~] #

[root@monitor ~] # yum-y install epel-release

. / / omit the process

[root@monitor] # yum clean all & & yum makecache

. / / omit the process

[root@monitor ~] # yum-y install mysql-mmm*

. / / omit the process

On master server 1, push the configuration file / etc/mysql-mmm/mmm_common.conf to monitor

[root@db1 ~] # scp / etc/mysql-mmm/mmmcommon.conf root@192.168.52.150:/etc/mysql-mmm/

Root@192.168.52.150's password:

Mmmcommon.conf 100% 837 1.4MB/s 00:00

[root@db1 ~] #

Modify configuration file / etc/mysql-mmm/mmm_mon.conf

[root@monitor ~] # vim / etc/mysql-mmm/mmm_mon.conf

Ping_ips 192.168.52.135192.168.52.133192.168.52.134192.168.52.148 # Monitoring Server IP

Auto_set_online 10

Monitor_user mmm_monitor # user name

Monitor_password 123456 # password

Authorize monitor access on four MySQL servers

Master server 1 virtual ~

[root@db1 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 8

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > grant super, replication client, process on. To 'mmm_agent'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > grant replication client on. To 'mmm_monitor'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > quit

Bye

[root@db1] # ~ ~

Primary Server 2:

[root@db2 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 6

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > grant super, replication client, process on. To 'mmm_agent'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > grant replication client on. To 'mmm_monitor'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > quit

Bye

[root@db2 ~] #

From server 1:

[root@db3 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 5

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > grant super, replication client, process on. To 'mmm_agent'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > grant replication client on. To 'mmm_monitor'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > quit

Bye

[root@db3 ~] #

From server 1:

[root@db3 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 5

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > grant super, replication client, process on. To 'mmm_agent'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > grant replication client on. To 'mmm_monitor'@'192.168.52.%' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > quit

Bye

[root@db3 ~] #

Modify the / etc/mysql-mmm/mmm_agent.conf file of the MySQL server respectively

Test MMM High availability

Turn on mysql-mmm-monitor.service

[root@monitor ~] # systemctl stop firewalld.service # turn off the firewall

[root@monitor ~] # setenforce 0 # turn off enhanced security features

[root@monitor ~] # systemctl start mysql-mmm-monitor.service # enable the service

[root@monitor ~] # mmm_control show # Test Cluster

Db1 (192.168.52.135) master/ONLINE. Roles: writer (192.168.52.200)

Db2 (192.168.52.133) master/ONLINE. Roles:

Db3 (192.168.52.134) slave/ONLINE. Roles: reader (192.168.52.220)

Db4 (192.168.52.148) slave/ONLINE. Roles: reader (192.168.52.210)

Check the cluster

[root@monitor ~] # mmm_control checks all

Db4 ping [last change: 2019-11-25 12:15:00] OK

Db4 mysql [last change: 2019-11-25 12:15:00] OK

Db4 rep_threads [last change: 2019-11-25 12:15:00] OK

Db4 rep_backlog [last change: 2019-11-25 12:15:00] OK: Backlog is null

Db2 ping [last change: 2019-11-25 12:15:00] OK

Db2 mysql [last change: 2019-11-25 12:15:00] OK

Db2 rep_threads [last change: 2019-11-25 12:15:00] OK

Db2 rep_backlog [last change: 2019-11-25 12:15:00] OK: Backlog is null

Db3 ping [last change: 2019-11-25 12:15:00] OK

Db3 mysql [last change: 2019-11-25 12:15:00] OK

Db3 rep_threads [last change: 2019-11-25 12:15:00] OK

Db3 rep_backlog [last change: 2019-11-25 12:15:00] OK: Backlog is null

Db1 ping [last change: 2019-11-25 12:15:00] OK

Db1 mysql [last change: 2019-11-25 12:15:00] OK

Db1 rep_threads [last change: 2019-11-25 12:15:00] OK

Db1 rep_backlog [last change: 2019-11-25 12:15:00] OK: Backlog is null

[root@monitor ~] #

Switch writer to db2

[root@monitor ~] # mmm_control move_role writer db2

OK: Role 'writer' has been moved from' db1' to 'db2'. Now you can wait some time and check new roles info!

[root@monitor ~] # mmm_control show

Db1 (192.168.52.135) master/ONLINE. Roles:

Db2 (192.168.52.133) master/ONLINE. Roles: writer (192.168.52.200)

Db3 (192.168.52.134) slave/ONLINE. Roles: reader (192.168.52.220)

Db4 (192.168.52.148) slave/ONLINE. Roles: reader (192.168.52.210)

[root@monitor ~] #

Install the mariadb service on monitor and authorize monitor access on master server 1

Primary server 1:

[root@db1 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 464

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > grant all on. To 'testdb'@'192.168.52.150' identified by' 123456; # Authorization

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] >

Log in to the master server virtual IP with monitor

[root@monitor] # mysql-u testdb-p123456-h 192.168.52.200

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 584

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | myschool |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)]

Delete the database myschool on the monitor server

Monitor:

MariaDB [(none)] > drop database myschool

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

View the three server databases that are not closed separately

Primary Server 2:

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

From server 1:

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

From Server 2:

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

Restart the MySQL service of primary server 1 to view the database

[root@db1 ~] # systemctl stop mariadb.service

[root@db1 ~] #

[root@db1 ~] # mysql

Welcome to the MariaDB monitor. Commands end with; or\ g.

Your MariaDB connection id is 464

Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

MariaDB [(none)] > show databases; # View the database (synchronization succeeded)

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] >

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.