Advantages and disadvantages of MySQL-MMM:
Advantages: high availability, good scalability, automatic failover, for primary and primary synchronization, only one database write operation is provided at the same time to ensure data consistency. Disadvantages: the Monitor node is a single point, which can be combined with Keepalived to achieve high availability.
How MySQL-MMM works:
MMM (Master Master replication manager Mysql) is a set of flexible script program based on perl implementation, which is used to monitor and migrate mysql replication, and manage the configuration of Mysql master replication (only one node can be written at the same time).
Mmm Mond: monitor the process, take charge of all monitoring work, determine and process all node role activities. This script needs to be run on the supervisor.
Mmm agent D: the agent process running on each mysql server completes the monitoring probe work and performs simple remote service settings. This script needs to be run on the regulated machine.
Mmm? Control: a simple script that provides commands to manage the MMM? Mond process.
the supervision end of MySQL MMM will provide multiple virtual IPS (VIPs), including a writable VIP and multiple readable VIPs. Through supervision management, these IPS will be bound to the available mysql. When a MySQL fails, the supervision will migrate the VIPs to other mysql.
in the whole supervision process, you need to add related authorized users to mysql so that mysql can support the maintenance of the supervisor. Authorized users include an mmm monitor user and an mmm agent user. If you want to use mmm backup tools, you need to add an mmm tools user.
Experimental environment:
Master 1 server 192.168.13.167 Master 2 server 192.168.13.151 slave1 server 192.168.13.168 Slave 2 server 192.168.13.145 monitor server 192.168.13.164
1. MYSQL database needs to be installed in master1\master2\slave1\slave2
[root@master1 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo ##Get source address [root@master1 ~]# yum -y install epel-release ##Install epel source [root@master1 ~]# yum clean all && yum makecache ##yum cache empty [root@master1 ~]# yum -y install mariadb-server mariadb ##Install mariadb database [root@master1 ~]# systemctl stop firewalld.service ##Turn off firewall [root@master1 ~]# setenforce 0 [root@master1 ~]# systemctl start mariadb.service ##Open database
2. Modify the master 1 database configuration file (use scp to synchronize to other database servers, monitor does not need to)
[root@master1 ~]# vim /etc/my.cnf ##Modify profile [mysqld] log_error=/var/lib/mysql/mysql.err ##Error log file log=/var/lib/mysql/mysql_log.log ##Master slave log storage location log_slow_queries=/var/lib/mysql_slow_queris.log ##man log binlog-ignore-db=mysql,information_schema ##Binary character_set_server=utf8 ##character set log_bin=mysql_bin ##Binary log file server_id=11 ##Service id (cannot be the same) log_slave_updates=true ##Allow updates from server sync_binlog=1 ##Synchronization log auto_increment_increment=2 ##Self adding column auto_increment_offset=1 ##starting point [root@master1 ~]# systemctl restart mariadb.service ##Restart database
When using scp to copy database configuration files to other servers, you need to modify the server ID, which cannot be the same
[root@master1 ~]# scp /etc/my.cnf root@192.168.13.151:/etc/ (server-id=22) [root@master1 ~]# scp /etc/my.cnf root@192.168.13.168:/etc/ (server-id=33) [root@master1 ~]# scp /etc/my.cnf root@192.168.13.145:/etc/ (server-id=44) [root@master1 ~]# netstat -anpt | grep 3306 ##Viewing port 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4235/mysqld
3. Configure master 1 and master 2 to replicate with each other
//master1 server//
[root@master1 ~]# mysql ##Enter database
MariaDB [(none)]> show master status; ##View the status information of the primary server
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000001 | 245 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.13.%' identified by '123456';
##Authorization to 13 segment network segment replication user name replication password 123456
Query OK, 0 rows affected (0.00 sec)
//master2 server//
MariaDB [(none)]> change master to master_host='192.168.13.167',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=245;
##Synchronizing the master1 server on master2
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.13.%' identified by '123456';
##Authorize replication permission on master2
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status; ##View the status information of the master 2 server
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000001 | 410 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> flush privileges; ##Refresh authority
Query OK, 0 rows affected (0.00 sec)
//master1 server//
MariaDB [(none)]> change master to master_host='192.168.13.151',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=410;
##Master 1 synchronizes Master 2 server database
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//Start synchronization on master1 and master2//
MariaDB [(none)]> start slave; ##Open synchronization
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G; ##View synchronization status information
Slave_IO_Running: Yes
Slave_SQL_Running: Yes4. In slave1 and slave2, make master-slave synchronization, and pay attention to the change of log file and location parameters
MariaDB [(none)]> change master to master_host='192.168.13.167',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=245;
##Synchronize master 1 master on slave
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges; ##Refresh authority
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave; ##Open synchronization
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G; ##View status information for synchronization
Slave_IO_Running: Yes
Slave_SQL_Running: Yes5. Test the synchronization status of master master and slave
//Master server master1// MariaDB [(none)]> create database school; ##Create database Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; ##view the database +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | test | +--------------------+ 5 rows in set (0.00 sec) //View database from server// MariaDB [(none)]> show databases; ##Realize master-slave synchronization +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | test | +--------------------+ 5 rows in set (0.00 sec)
6. Install MMM on all servers. Note: the epel source should be configured with alicloud source, and then the epel release source should be installed. (you need to install the source, clear the cache, and then install MMM on the monitor.)
##On the monitor server wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo yum -y install epel-release yum clean all && yum makecache yum -y install mysql-mmm* ##mmm is required for the rest of the servers [root@master1 ~]# yum -y install mysql-mmm*
7. After installation, configure mmm on all servers
[root@master1 ~]# vim /etc/mysql-mmm/mmm_common.conf ##All hosts should be configured to directly copy multiple copies
......
<host default>
cluster_interface ens33 ##Modify NIC
...
replication_user replication ##Modify user name
replication_password 123456 ##Password
agent_user mmm_agent
agent_password 123456 ##Password
<host db1>
ip 192.168.195.128 ##master1 address
mode master
peer db2
</host>
<host db2>
ip 192.168.195.129 ##master2 address
mode master
peer db1
</host>
<host db3>
ip 192.168.195.130 ##slave1 address
mode slave
</host>
<host db4>
ip 192.168.195.131 ##slave2 address
mode slave
</host>
<role writer>
hosts db1, db2 ##Write server virtual ip
ips 192.168.195.250
mode exclusive
</role>
<role reader>
hosts db3, db4 ##Read server virtual ip
ips 192.168.195.251, 192.168.195.252
mode balanced
</role>
##Copy to another server
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.13.151:/etc/mysql-mmm/
root@192.168.13.151's password:
mmm_common.conf 100% 836 267.1KB/s 00:00
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.13.168:/etc/mysql-mmm/
root@192.168.13.168's password:
mmm_common.conf 100% 836 863.2KB/s 00:00
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.13.145:/etc/mysql-mmm/
root@192.168.13.145's password:
mmm_common.conf 100% 836 904.7KB/s 00:00
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.13.164:/etc/mysql-mmm/8. Configure on the monitor server
[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf
<host default>
monitor_user mmm_monitor
monitor_password 123456 ##Change the password of monitor
</host>9. Authorize MMM agent and MMM monitor on all databases
MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.13.%' identified by '123456'; ##Authorized agent Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.13.%' identified by '123456'; ##Authorization monitoring Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; ##Refresh authority Query OK, 0 rows affected (0.00 sec)
10. Modify MMM agent.conf of all databases
[root@master1 ~]# vim /etc/mysql-mmm/mmm_agent.conf this db1 //Adjust one by one according to the plan [root@master2 ~]# vim /etc/mysql-mmm/mmm_agent.conf this db2 //Adjust one by one according to the plan [root@slave1 ~]# vim /etc/mysql-mmm/mmm_agent.conf this db3 //Adjust one by one according to the plan [root@slave2 ~]# vim /etc/mysql-mmm/mmm_agent.conf this db4 //Adjust one by one according to the plan ##All databases on [root@master1 ~]systemctl start mysql-mmm-agent.service ##Start agent service [root@master1 ~]systemctl enable mysql-mmm-agent.service ##Add power on self start
11. Configure on monitor
[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf
<monitor>
ip 127.0.0.1
pid_path /run/mysql-mmm-monitor.pid
bin_path /usr/libexec/mysql-mmm
status_path /var/lib/mysql-mmm/mmm_mond.status
ping_ips 192.168.13.167,192.168.13.151,192.168.13.168,192.168.13.
145
##All database server addresses
auto_set_online 10 ##Automatic online time
[root@monitor ~]# systemctl stop firewalld.service ##Turn off firewall
[root@monitor ~]# setenforce 0
[root@monitor ~]# systemctl start mysql-mmm-monitor.service ##Start monitoring service
[root@monitor ~]# mmm_control show ##View the master slave's elegant address
db1(192.168.13.167) master/ONLINE. Roles: writer(192.168.13.250)
db2(192.168.13.151) master/ONLINE. Roles:
db3(192.168.13.168) slave/ONLINE. Roles: reader(192.168.13.252)
db4(192.168.13.145) slave/ONLINE. Roles: reader(192.168.13.251)12. Test floating address
[root@master1 ~]# systemctl stop mariadb.service ##Simulation stop master1 server
//View on monitor server//
[root@monitor ~]# mmm_control show
db1(192.168.13.167) master/HARD_OFFLINE. Roles:
db2(192.168.13.151) master/ONLINE. Roles: writer(192.168.13.250)
db3(192.168.13.168) slave/ONLINE. Roles: reader(192.168.13.252)
db4(192.168.13.145) slave/ONLINE. Roles: reader(192.168.13.251)
(restart master1 Database service, virtual address will not be preempted master1)
mmm_control checks all //Need all kinds of OK
mmm_control move_role writer db1 ##Virtual address can be switched13. Install MySQL on the monitor as a test machine and log in the database with virtual ip
[root@monitor ~]# yum install mysql -y //Authorize monitor address access on the master1 server// MariaDB [(none)]> grant all on *.* to 'testdba'@'192.168.13.164' identified by '123456'; ##Authorize monitor address access Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; ##Refresh authority Query OK, 0 rows affected (0.00 sec) [root@monitor ~]# mysql -utestdba -p -h 192.168.13.250 ##Use the virtual address to log in to the database Enter password: MariaDB [(none)]>