Build mysql-mmm high-availability clusters

Build mysql-mmm high-availability clusters MMM profile: 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 using the Perl language, which is mainly used to monitor and manage MySQL Master-master (dual master) replication, although it is called dual master replication, but only one master is allowed to write at the same time in the business, and the other one provides partial read services to speed up the preheating of the alternate master at the time of primary master switching. Can say mmm this set of scripts to implement the function of failover, on the other hand, its internal additional tool script can also achieve multiple slave read load balancing. MMM provides both automatic and manual removal of the virtual IP of a server with high replication latency in a set of servers, as well as the ability to back up data, achieve data synchronization between two nodes, and more. Because MMM does not fully guarantee the consistency of data, MMM is suitable for the data consistency requirements are not very high, but also want to maximize the business availability of the scene. For businesses that have a high level of conformance to data, it is highly recommended that you use MMM as a highly available architecture. Pros: 1 Stable and mature open source products, after the test of time the core technology is MySQL's own technology, just use a script to control, so the principle is relatively easy to understand, and management can be more intelligent. 2 Easy to install, simple to configure, easy to use 3 powerful (Ha,failover,tools suite, cluster mode can manage multiple MMM groups with one monitor) Cons: 1 because there is only one write point in the architecture, scalability is limited, but it is sufficient for the average midsize enterprise. Solution: For large applications can be taken vertically split into multiple MMM architectures in a way that is managed using MMM cluster. 2 for read-write separation and read load Balancing, the program should be developed or done using other tools. MMM High-availability architecture:

• mmm_mond: Monitor processes, responsible for all monitoring tasks, and determine and process all node role activities. This script needs to be run on a supervised machine.
• MMM_AGENTD: The agent process running on each MySQL server, completing the monitored probe work and performing simple remote service settings. This script needs to be run on a supervised machine.
• Mmm_control: A simple script that provides commands to manage the mmm_mond process. Example of this case:

Host name	system	main software	IP	Virtual IP
Mysql-master1	CentOS 7.3 x86_64	Mmm	192.168.217.129	192.168.217.100
Mysql-master2	CentOS 7.3 x86_64	Mmm	192.168.217.130	192.168.217.100
Mysql-slave1	CentOS 7.3 x86_64	Mmm	192.168.217.131	192.168.217.200
Mysql-slave2	CentOS 7.3 x86_64	Mmm	192.168.217.132	192.168.217.210
Monitor	CentOS 7.3 x86_64	Mmm	192.168.217.133

Install MMM to install on all servers:

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo   #配置ALI云源，然后安装epel-release源。yum clean all && yum makecache      #删除缓存         下载到本地电脑缓存yum -y install epel-release       #安装epel 源  用于安装 mmmyum -y install mysql-mmm*     #安装 mmm 服务  在所有服务器上安装

To configure the MySQL server:

• Configure all MySQL, Server-id different than the others are the same:

  vim/etc/my.cnf[mysqld]user = mysql #管理用户 basedir =/usr/local/mysql #工作目录datadir =/usr /local/mysql/data #数据库文件port = 3306character_set_server=utf8pid-file =/usr/local/mysql/mysqld.pid #pid process file Socke t =/usr/local/mysql/mysql.sock #链接数据库server-id = 1 Binlog-ignore-db=mysql,information_schema #不需要同步的数据库名称log_slave_u Pdates=true #从服务器更新二进制日志sync_binlog =1 #同步二进制日志auto_increment_increment =2 #字段一次递增值auto_increment_offset =1 #自增字段的 Starting value Log-error=/usr/local/mysql/data/mysql_error.log #错误日志general_log =on #开启通用日志general_log_fi Le=/usr/local/mysql/data/mysql_general.loglog_bin=mysql-bin #二进制日志slow_query_log =on #开启慢查询日志 slow_query_log_file= Mysql_slow_query.log long_query_time=1  

• Copy the configuration file to the other 3 database servers and start the server

  scp /etc/my.cnf [email protected]:/etc/     #对方有权限的用户和IP地址  输入yes 、密码进行复制 systemctl restart mysqld.service    #修改所有数据库的 server-id 重启服务

• To configure primary master replication:

mysql -u root  -p     #进入数据库在m1上为m2授予从的权限，在m2上也要为m1授予从的权限mysql>grant replication slave on *.* to ‘replication‘@‘192.168.217.%‘ identified by ‘123456‘; mysql>show master status;      #记录日志文件名称和 位置值，在两台主上查看。mysql>change master to master_host=‘192.168.217.129‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=1104;注意 指向的IP地址、日志名称、偏移量 ，不要搞错mysql>start slave;      #开启同步mysql>show slave status\G;    #查看链接状态    Slave_IO_Running: Yes    Slave_SQL_Running: Yes以上两台主都执行，从不需要 。可以在主服务器上创建数据库 、在另一台主服务器查看、删除，做测试。

• Configure two slave servers:

指向随便一台主服务器即可 mysql>change master to master_host=‘192.168.217.129‘,master_user=‘replication‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=1104;注意 指向的IP地址、日志名称、偏移量 ，不要搞错start slave;show slave status\G;    Slave_IO_Running: Yes    Slave_SQL_Running: Yes

Test master/slave, main master, synchronization situation

MMM is configured on all hosts

• Configuration mysql-master1:

    cd/etc/mysql-mmm/vim mmm_common.conf ....  

  scp mmm_common.conf [email protected]:/etc/mysql-mmm/scp mmm_common.conf [email protected]:/etc/mysql-mmm/scp mmm_common.conf [email protected]:/etc/mysql-mmm/scp mmm_common.conf [email protected]:/etc/mysql-mmm/

Configure the Monitor server:

cd /etc/mysql-mmm/vim mmm_mon.conf…… ping_ips            192.168.217.129,192.168.217.130,192.168217.131,192.168.217.132    #数据库服务器地址 auto_set_online     10       #设置自动在线时间……

Authorization and modification in all databases:

在所有数据库上为mmm_agent授权mysql>grant super, replication client, process on *.* to ‘mmm_agent‘@‘192.168.217.%‘ identified by ‘123456‘;在所有数据库上为mmm_moniter授权mysql>grant replication client on *.* to ‘mmm_monitor‘@‘192.168.217.%‘ identified by ‘123456‘;flush privileges;      #刷新修改所有数据库的mmm_agent.conf文件vim /etc/mysql-mmm/mmm_agent.confthis db1     #/根据规mmm_common.conf 配置中

Configure the Monitor server:

systemctl start mysql-mmm-monitor.service       #启动监控服务mmm_control show            #查看各节点的情况  db1(192.168.217.129) master/ONLINE. Roles: writer(192.168.217.100)  db2(192.168.217.130) master/ONLINE. Roles:   db3(192.168.217.131) slave/ONLINE. Roles: reader(192.168.217.200)  db4(192.168.217.132) slave/ONLINE. Roles: reader(192.168.217.210)

mmm_control checks all      #各种OK  说明运转正常mmm_control move_role writer db2   #手动指定活跃服务器   注意：不会抢占mmm_control show            #查看各节点的情况  db1(192.168.217.129) master/ONLINE. Roles:  db2(192.168.217.130) master/ONLINE. Roles: writer(192.168.217.100)  db3(192.168.217.131) slave/ONLINE. Roles: reader(192.168.217.200)  db4(192.168.217.132) slave/ONLINE. Roles: reader(192.168.217.210)

test: Close the Mysql-master1 service to see if Mysql-master2 will preempt. Note: Wait for a period of time to view. Close the MYSQL-SLAVE1 service, mysql-slave1 virtual IP will automatically drift to Mysql-slave2, Mysql-slave2 will have two addresses.

Build mysql-mmm high-availability clusters