大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
MMM( Master- Master replication manager for MYSQL, MYSQL主主复制管理器)
是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发,主要用来监控和管理 MYSQL master-master(双主)复制,虽然叫做双主复制,但是业务上同一时刻只允许对一个主进行写入,另一台备选主上提供部分读服务,以加速在主主切换时备选主的预热,可以说MMM这套脚本程序一方面实现了故障切换的功能
另一方面其内部附加的工具脚本也可以实现多个 Slave的read负载均衡。
超过10余年行业经验,技术领先,服务至上的经营模式,全靠网络和口碑获得客户,为自己降低成本,也就是为客户降低成本。到目前业务范围包括了:成都网站设计、网站制作,成都网站推广,成都网站优化,整体网络托管,微信小程序定制开发,微信开发,App定制开发,同时也可以让客户的网站和网络营销和我们一样获得订单和生意!
4台安装mysql5.7服务的设备
1台用来安装mmm的centos7设备
systemctl stop firewalld.service
setenforce 0
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
vim /etc/my.cnf
修改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
log_bin=mysql_bin
server_id=1
log_slave_updates=true
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
systemctl restart mysqld
-------没有问题后,把配置文件复制到其它3台数据库服务器上并启动服务器-----
scp /etc/my.cnf root@192.168.100.101:/etc/
scp /etc/my.cnf root@192.168.100.102:/etc/
scp /etc/my.cnf root@192.168.100.103:/etc/
-----注意:配置文件中的server_id 要修改-----
show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 339 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec) ##记录日志文件名称和 位置值,在两台主上查看。
----在m1上为m2授予从的权限,在m2上也要为m1授予从的权限---
grant replication slave on *.* to 'replication'@'192.168.100.%' identified by '123456'; ##两台主服务器都执行,从服务器不需要
change master to master_host='192.168.100.101',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=339;
-----注意,在m1上要指定m2上的日志文件名,和位置参数-----在m2上要反过来。
start slave;
show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-------在另一台主上做----要指定m1的日志和位置参数---
change master to master_host='192.168.100.100',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=339;
start slave;
show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
change master to master_host='192.168.100.100',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=339;
start slave;
show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在所有服务器上安装-----注意,epel源要配置好
yum -y install mysql-mmm*
cd /etc/mysql-mmm/
vi mmm_common.conf ##所有主机上都要配置,直接复制多份
cluster_interface ens33
……
replication_user replication
replication_password 123456
agent_user mmm_agent
agent_password 123456
ip 192.168.100.100
mode master
peer db2
ip 192.168.100.101
mode master
peer db1
ip 192.168.100.102
mode slave
ip 192.168.100.103
mode slave
hosts db1, db2
ips 192.168.100.200
mode exclusive
hosts db3, db4
ips 192.168.100.201, 192.168.100.202
mode balanced
scp mmm_common.conf root@192.168.100.100:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.100.101:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.100.102:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.100.103:/etc/mysql-mmm/ ## 复制给mysql服务器
------------------在monitor服务器上配置----
cd /etc/mysql-mmm/ //改密码
vi mmm_mon.conf
monitor_user mmm_monitor
monitor_password 123456
--------------在所有数据库上为mmm_agent授权-----
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.100.%' identified by '123456';
--------------在所有数据库上为mmm_moniter授权-----
grant replication client on *.* to 'mmm_monitor'@'192.168.100.%' identified by '123456';
flush privileges;
---------------修改所有数据库的mmm_agent.conf----
vi /etc/mysql-mmm/mmm_agent.conf
this db1 ##主1 主2 从1 从2 分别为 db{1,2,3,4}
--------------在所有数据库服务器上启动mysql-mmm-agent---
systemctl start mysql-mmm-agent.service
systemctl enable mysql-mmm-agent.service
------------------在monitor服务器上配置------
cd /etc/mysql-mmm/
vi mmm_mon.conf
……
ping_ips 192.168.100.100,192.168.100.101,192.168.100.102,192.168.100.103 ##数据库服务器地址
auto_set_online 10
systemctl start mysql-mmm-monitor.service ##启动mysql-mmm-monitor
mmm_control show ##查看各节点的情况
db1(192.168.100.100) master/ONLINE. Roles: writer(192.168.100.200)
db2(192.168.100.101) master/ONLINE. Roles:
db3(192.168.100.102) slave/ONLINE. Roles: reader(192.168.100.201)
db4(192.168.100.103) slave/ONLINE. Roles: reader(192.168.100.202)
mmm_control checks all ## 需要各种OK
mmm_control move_role writer db1 ##手动转换角色
关掉主1 的mysql服务
systemctl stop mysqld
mmm_control show ##查看各节点的情况
db1(192.168.100.100) master/HARD_OFFLINE. Roles:
db2(192.168.100.101) master/ONLINE. Roles: writer(192.168.100.200)
db3(192.168.100.102) slave/ONLINE. Roles: reader(192.168.100.201)
db4(192.168.100.103) slave/ONLINE. Roles: reader(192.168.100.202)
关掉从1的mysql服务
mmm_control show ##查看各节点的情况
db1(192.168.100.100) master/HARD_OFFLINE. Roles:
db2(192.168.100.101) master/ONLINE. Roles: writer(192.168.100.200)
db3(192.168.100.102) slave/HARD_OFFLINEE. Roles:
db4(192.168.100.103) slave/ONLINE. Roles: reader(192.168.100.201,192.168.100.202)