您好,登录后才能下订单哦!
MMM(Master-Master replication manager for Mysql,Mysql 主主复制管理器)是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发,主要用来监控和管理Mysql Master-Master(双主)复制,虽然叫做双主复制,但是业务上同一时刻只允许对一个主进行写入,另一台备选主上提供部分读服务,以加速在主主切换时备选主的预热,可以说MMM这套脚本程序一方面实现了故障切换的功能,另一方面其内部附加的工具脚本也可以实现多个Slave的read负载均衡
1)mmm_mon:监控进程,负责所有的监控工作,决定和处理所有节点角色活动。此脚本需要在监管机上运行
2)mmm_agent:运行在每个Mysql服务器上的代理进程,完成监控的探针工作和执行简单的远端服务设置。此脚本需要在各节点上运行
3)mmm_control:一个简单的脚本,提供管理mmm_mond进程的命令
4)mysql-mmm的监控端会提供多个虚拟IP(VIP),包括一个可写VIP,多个可读VIP,通过监管的管理,这些IP会绑定在可用的Mysql之上,当某一台Mysql宕机时,监控会将VIP迁移至其他Mysql
本案例环境使用五台服务器模拟搭建
主机 操作系统 IP地址
master1 CentOS7 192.168.195.128
master2 CentOS7 192.168.195.137
slave1 CentOS7 192.168.195.140
slave2 CentOS7 192.168.195.141
monitor CentOS7 192.168.195.142
CentOS默认没有mysql-mmm软件包,官方推荐使用epel源,五台主机都要安装eperl源和MMM
service firewalld stop
setenforce 0
wget -0 /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo //使用阿里云提供的yum源
yum -y install epel-release
yum clean all && yum makecache
yum -y install mariadb-server mariadb
systemctl start mariadb
首先修改master1主配置文件
vi /etc/my.cnf (9dd删掉[mysqld]标签之前的内容,重新插入下面的配置)
[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 //设置默认的字符集为utf-8
log_bin=mysql_bin //开启二进制日志,用于主从数据复制
server_id=1 //每台server_id的值不能相同
log_slave_updates=true //此数据库宕机,备用数据库接管
sync_binlog=1
auto_increment_increment=2 //字段一次递增2
auto_increment_offset=1 //自增字段的起始值:1,3,5,7.........等奇数ID
systemctl restart mariadb
netstat -anpt | grep 3306tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5029/mysqld
没有问题后,把配置文件my.cnf复制到其他3台数据库服务器上并启动服务器
每台mysql主机的server_id参数不能相同,其他配置文件参数相同
scp /etc/my.cnf root@192.168.195.140:/etc/
scp /etc/my.cnf root@192.168.195.137:/etc/
scp /etc/my.cnf root@192.168.195.141:/etc/
注:配置文件中的server_id参数不能相同,需要修改。第二台server_id=2 第三台3 第四台4
首先进入mysql查看log-bin日志和pos值的位置(在授权的时候要随时更新position位置变化)
在master1上
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 411 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)在master2上
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000004 | 491 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)master1,master2互相提升访问权限
注意:在master1上要指定master2上的日志文件名和位置参数。在m2上要反过来指定m1的。在授权的时候要随时查看
在master1上授权
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.195.%' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> change master to master_host='192.168.195.137',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000004',master_log_pos=491;
Query OK, 0 rows affected (0.06 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)在master2上授权
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.195.%' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> change master to master_host='192.168.195.128',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000003',master_log_pos=411;
Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)分别查看master1,master2服务器的主从状态
start slave; //开启同步功能
show slave status\G;
Slave_I0_Running: Yes //这两个选项参数必须为yes
Slave_SQL_Running: Yes
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.01 sec)
在master2上可以查看到刚才创建的库school,同步已经完成
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set (0.01 sec)
首先查看master1的状态值
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 583 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)在slave1,slave2上分别设置
MariaDB [(none)]> change master to master_host='192.168.195.128',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000003',master_log_pos=583;
分别查看slave1,slave2的主从状态
start slave; //开启同步功能
show slave status\G;
Slave_I0_Running: Yes //这两个选项参数必须为yes
Slave_SQL_Running: Yes
数据库服务器之间的主主复制同步,主从复制同步配置成功
yum -y install mysql-mmm*
系统中所有主机的该配置文件的内容都是一样的,包括监控主机monitor
修改后的内容为带加粗部分
vim /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface ens33
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user replicant
replication_password abc123
agent_user mmm_agent
agent_password agent
</host><host db1>
ip 192.168.195.128
mode master
peer db2
</host><host db2>
ip 192.168.195.137
mode master
peer db1
</host><host db3>
ip 192.168.195.140
mode slave
</host><host db4>
ip 192.168.195.141
mode slave
</host><role writer>
hosts db1, db2
ips 192.168.195.188 //设置虚拟IP
mode exclusive
</role><role reader>
hosts db3, db4
ips 192.168.195.200, 192.168.195.210 //设置虚拟IP
mode balanced
</role>远程复制 覆盖配置文件:
scp mmm_common.conf root@192.168.195.137:/etc/mysql-mmm/scp mmm_common.conf root@192.168.195.140:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.195.141:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.195.142:/etc/mysql-mmm/
vim /etc/mysql-mmm/mmm_common.conf 挨个查看一下
cd /etc/mysql-mmm/ //改密码
vim mmm_mon.conf
ping_ips 192.168.195.128,192.168.195.137,192.168.195.140,192.168.195.141 //监视器监听的服务器地址
auto_set_online 10 //超时连接时间10秒
<host default>
monitor_user mmm_monitor //用户名
monitor_password 123456 //密码
</host>
vim /etc/mysql-mmm/mmm_agent.conf
this db1 //分别修改为db1,db2,db3,db4
MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.195.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.195.%' identified by 'agent';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
在所在数据库服务器上启动mysql-mmm-agent
systemctl start mysql-mmm-agent.service
在monitor服务器上启动监控服务mysql-mmm-montior
systemctl start mysql-mmm-monitor.service
在monitor服务器上进行测试
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/ONLINE. Roles: writer(192.168.195.188)
db2(192.168.195.137) master/ONLINE. Roles:
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)检测状态需要全部OK
[root@localhost ~]# mmm_control checks all
db4 ping [last change: 2018/09/08 14:53:06] OK
db4 mysql [last change: 2018/09/08 14:53:06] OK
db4 rep_threads [last change: 2018/09/08 14:53:06] OK
db4 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null
db2 ping [last change: 2018/09/08 14:53:06] OK
db2 mysql [last change: 2018/09/08 14:53:06] OK
db2 rep_threads [last change: 2018/09/08 14:53:06] OK
db2 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null
db3 ping [last change: 2018/09/08 14:53:06] OK
db3 mysql [last change: 2018/09/08 14:53:06] OK
db3 rep_threads [last change: 2018/09/08 14:53:06] OK
db3 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null
db1 ping [last change: 2018/09/08 14:53:06] OK
db1 mysql [last change: 2018/09/08 14:53:06] OK
db1 rep_threads [last change: 2018/09/08 14:53:06] OK
db1 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null将虚拟IP:192.168.195.188切换到db2服务器上
[root@localhost ~]# 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@localhost ~]# mmm_control show
db1(192.168.195.128) master/ONLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)
1)停掉主db1数据库,等待几秒后,可以看到数据库db1处于HARD_OFFLINE(离线状态),检测不到数据库的存在
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/HARD_OFFLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)2)停掉从db3数据库,从的虚拟IP会全部在另一台正常数据库上,
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/HARD_OFFLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/HARD_OFFLINE. Roles:
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200), reader(192.168.195.210)重新开启db3数据库,从虚拟IP又会回到原来的位置
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/ONLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)
以监控机monitor充当客户进行远程登录mysql服务进行测试
按理来讲监控服务器只单独充当监控这一角色就行了,这里临时将它也作为客户端:
yum install -y mariadb-server mariadbsystemctl start mariadb.service
在数据库服务器上进行远程登录授权
MariaDB [(none)]> grant all on *.* to 'test'@'192.168.195.142' identified by '123123';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)在monitor进行远程登录mysql服务 ,并创建一个库shujuku,
mysql -utest -p123123 -h 192.168.195.188 //虚拟IP
MariaDB [school]> create database shujuku;
Query OK, 1 row affected (0.01 sec)登录其他四台节点,都可以看到刚创建的库shujuku,证明群集同步成功
MariaDB [school]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| shujuku |
| test |
+--------------------+
6 rows in set (0.00 sec)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。