MySQL-MMM优缺点:
优点:高可用性,扩展性好,出现故障自动切换,对于主主同步,在同一时间只提供一台数据库写操作,保证的数据的一致性。
缺点:Monitor节点是单点,可以结合Keepalived实现高可用。
实验环境:
master1服务器 192.168 .13 .167
master2服务器 192.168 .13 .151
slave1服务器 192.168 .13 .168
slave2服务器 192.168 .13 .145
monitor服务器 192.168 .13 .164
1、在master1\master2\slave1\slave2都需要安装MYSQL数据库
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
2、修改master1数据库配置文件(使用scp同步到其他数据库服务器上,monitor不需要)
[ root@master1 ~ ]
[ 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@master1 ~ ]
使用scp复制数据库配置文件到其他服务器上的时候,需要修改server-id不能一样
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
[ root@master1 ~ ]
tcp 0 0 0.0 .0 .0 : 3306 0.0 .0 .0 : * LISTEN 4235 / mysqld
3、配置主主复制(master1,master2)两台主服务器相互复制
// master1服务器//
[ root@master1 ~ ]
MariaDB [ ( none) ] > show master status;
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - +
| 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' ;
Query OK, 0 rows affected ( 0.00 sec)
// master2服务器//
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 ;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > grant replication slave on * . * to 'replication' @'192.168.13.%' identified by '123456' ;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > show master status;
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - +
| 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;
Query OK, 0 rows affected ( 0.00 sec)
// master1服务器//
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 ;
Query OK, 0 rows affected ( 0.03 sec)
MariaDB [ ( none) ] > flush privileges;
Query OK, 0 rows affected ( 0.00 sec)
// master1, master2上开启同步//
MariaDB [ ( none) ] > start slave;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4、在slave1,slave2做主从同步,注意日志文件和位置参数的改变
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 ;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > flush privileges;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > start slave;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、测试主主,主从的同步状态
// 主服务器master1//
MariaDB [ ( none) ] > create database school;
Query OK, 1 row affected ( 0.00 sec)
MariaDB [ ( none) ] > show databases;
+ - - - - - - - - - - - - - - - - - - - - +
| Database |
+ - - - - - - - - - - - - - - - - - - - - +
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+ - - - - - - - - - - - - - - - - - - - - +
5 rows in set ( 0.00 sec)
// 从服务器上查看数据库//
MariaDB [ ( none) ] > show databases;
+ - - - - - - - - - - - - - - - - - - - - +
| Database |
+ - - - - - - - - - - - - - - - - - - - - +
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+ - - - - - - - - - - - - - - - - - - - - +
5 rows in set ( 0.00 sec)
6、安装MMM,在所有服务器上安装,注意:epel源要配置好配置阿里云源,然后安装epel-release源。(monitor上需要先安装源,清空缓存,然后安装MMM)
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*
[ root@master1 ~ ]
7、安装结束后,所有服务器上对mmm进行配置
[ root@master1 ~ ]
……
< host default>
cluster_interface ens33
…
replication_user replication
replication_password 123456
agent_user mmm_agent
agent_password 123456
< host db1>
ip 192.168 .13 .167
mode master
peer db2
< / host>
< host db2>
ip 192.168 .13 .151
mode master
peer db1
< / host>
< host db3>
ip 192.168 .13 .168
mode slave
< / host>
< host db4>
ip 192.168 .13 .145
mode slave
< / host>
< role writer>
hosts db1, db2
ips 192.168 .13 .250
mode exclusive
< / role>
< role reader>
hosts db3, db4
ips 192.168 .13 .252 , 192.168 .13 .251
mode balanced
< / role>
[ root@master1 ~ ]
root@192.168 .13 .151 's password:
mmm_common. conf 100 % 836 267. 1KB/ s 00 : 00
[ root@master1 ~ ]
root@192.168 .13 .168 's password:
mmm_common. conf 100 % 836 863. 2KB/ s 00 : 00
[ root@master1 ~ ]
root@192.168 .13 .145 's password:
mmm_common. conf 100 % 836 904. 7KB/ s 00 : 00
[ root@master1 ~ ]
8、在monitor服务器上配置
[ root@monitor ~ ]
< host default>
monitor_user mmm_monitor
monitor_password 123456
< / host>
9、在所有数据库上为mmm_agent代理授权,为mmm_monitor授权监控
MariaDB [ ( none) ] > grant super , replication client, process on * . * to 'mmm_agent' @'192.168.13.%' identified by '123456' ;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > grant replication client on * . * to 'mmm_monitor' @'192.168.13.%' identified by '123456' ;
Query OK, 0 rows affected ( 0.00 sec)
MariaDB [ ( none) ] > flush privileges;
Query OK, 0 rows affected ( 0.00 sec)
10、修改所有数据库的mmm_agent.conf
[ root@master1 ~ ]
this db1 // 根据规划进行逐一调整
[ root@master2 ~ ]
this db2 // 根据规划进行逐一调整
[ root@slave1 ~ ]
this db3 // 根据规划进行逐一调整
[ root@slave2 ~ ]
this db4 // 根据规划进行逐一调整
[ root@master1 ~ ] systemctl start mysql- mmm- agent. service
[ root@master1 ~ ] systemctl enable mysql- mmm- agent. service
11、在monitor上配置
[ root@monitor ~ ]
< 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
auto_set_online 10
[ root@monitor ~ ]
[ root@monitor ~ ]
[ root@monitor ~ ]
[ root@monitor ~ ]
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、测试漂移地址
[ root@master1 ~ ]
// monitor服务器上查看//
[ root@monitor ~ ]
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 )
(重启master1数据库服务,虚拟地址不会被抢占到master1)
mmm_control checks all // 需要各种OK
mmm_control move_role writer db1
13、在monitor上安装MySQL作为测试机用虚拟ip登录数据库
[ root@monitor ~ ]
// master1服务器上授权monitor地址访问//
MariaDB [ ( none) ] > grant all on * . * to 'testdba' @'192.168.13.164' identified by '123456' ;
Query OK, 0 rows affected ( 0.01 sec)
MariaDB [ ( none) ] > flush privileges;
Query OK, 0 rows affected ( 0.00 sec)
[ root@monitor ~ ]
Enter password:
MariaDB [ ( none) ] >