[mysqld]datadir = /data/mysqlsocket=/data/mysql/mysql.sockuser=mysql<span><strong>server-id=1</strong></span>table_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 64Mthread_concurrency = 8log-bin=mysql-binbinlog_format = mixedmax_connections=2048character_set_server=utf8wait_timeout=1800interactive_timeout=1800skip-show-databaseskip-name-resolvetmp_table_size = 512Mmax_heap_table_size = 512Mbinlog-ignore-db = mysqlreplicate-ignore-db = mysqlbinlog-ignore-db = information_schemareplicate-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-ignore-db = performance_schemabinlog-ignore-db = testreplicate-ignore-db = testinnodb_data_home_dir=/data/mysql/innodbinnodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir=/data/mysql/innodb/loginnodb_file_per_table=1innodb_buffer_pool_size = 1000Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 100Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50default-storage-engine = MyISAM#default-storage-engine = INNODB[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysqldump]quickmax_allowed_packet = 16Msocket=/data/mysql/mysql.sock[mysql]no-auto-rehashsocket=/data/mysql/mysql.sock

[mysqld]datadir = /data/mysqlsocket=/data/mysql/mysql.sockuser=mysql<span><strong>server-id=2</strong></span>table_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 64Mthread_concurrency = 8log-bin=mysql-binbinlog_format = mixedmax_connections=2048character_set_server=utf8wait_timeout=1800interactive_timeout=1800skip-show-databaseskip-name-resolvetmp_table_size = 512Mmax_heap_table_size = 512Mbinlog-ignore-db = mysqlreplicate-ignore-db = mysqlbinlog-ignore-db = information_schemareplicate-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-ignore-db = performance_schemabinlog-ignore-db = testreplicate-ignore-db = testinnodb_data_home_dir=/data/mysql/innodbinnodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir=/data/mysql/innodb/loginnodb_file_per_table=1innodb_buffer_pool_size = 1000Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 100Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50default-storage-engine = MyISAM#default-storage-engine = INNODB[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysqldump]quickmax_allowed_packet = 16Msocket=/data/mysql/mysql.sock[mysql]no-auto-rehashsocket=/data/mysql/mysql.sock

    GRANT REPLICATION CLIENT                 ON *.* TO ''mmm_monitor''@''192.168.0.136'' IDENTIFIED BY ''monitor'';      GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO ''mmm_agent''@''192.168.0.%''   IDENTIFIED BY ''agent'';      GRANT REPLICATION SLAVE                  ON *.* TO ''replication''@''192.168.0.%'' IDENTIFIED BY ''replication'';  

CHANGE MASTER TO master_host=''192.168.0.137'', master_port=3306, master_user=''replication'',master_password=''replication'', master_log_file=''mysql-bin.000006'', master_log_pos=106;注意：<span></span><pre name="code">master_log_file和master_log_pos的值通过如下命令察看在db2上执行如下mysql命令 show master status

CHANGE MASTER TO master_host=''192.168.0.135'', master_port=3306, master_user=''replication'',master_password=''replication'', master_log_file=''mysql-bin.000006'', master_log_pos=106;注意：<span></span><pre name="code">master_log_file和master_log_pos的值通过如下命令察看在db1上执行如下mysql命令 show master status

active_master_role      writer<host default>    cluster_interface       eth0    pid_path                /var/run/mysql-mmm/mmm_agentd.pid    bin_path                /usr/libexec/mysql-mmm/    replication_user        replication    replication_password    replication    agent_user              mmm_agent    agent_password          agent</host><host db1>    ip      192.168.0.135    mode    master    peer    db2</host><host db2>    ip      192.168.0.137    mode    master   peer    db1</host><role writer>    hosts   db1,db2    ips     192.168.0.138    mode    exclusive</role><role reader>    hosts   db1,db2    ips     192.168.0.139,192.168.0.140    mode    balanced</role>

include mmm_common.conf# The ''this'' variable refers to this server.  Proper operation requires # that ''this'' server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.<span><strong>this db1</strong></span>

include mmm_common.conf# The ''this'' variable refers to this server.  Proper operation requires # that ''this'' server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf.<span><strong>this db2</strong></span>

include mmm_common.conf<monitor>    ip                  <span><strong>192.168.0.136</strong></span>    pid_path            /var/run/mysql-mmm/mmm_mond.pid    bin_path            /usr/libexec/mysql-mmm    status_path         /var/lib/mysql-mmm/mmm_mond.status    ping_ips            <span><strong>192.168.0.135,192.168.0.137</strong></span>    auto_set_online     60    # The kill_host_bin does not exist by default, though the monitor will    # throw a warning about it missing.  See the section 5.10 "Kill Host    # Functionality" in the PDF documentation.    #    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host    #</monitor><host default>    monitor_user        mmm_monitor    monitor_password    monitor</host>debug 0

    # cd /etc/init.d/      # chkconfig mysql-mmm-agent on      # service mysql-mmm-agent start  

    # cd /etc/init.d/      # chkconfig mysql-mmm-monitor on      # service mysql-mmm-monitor start  

MariaDB-MMM 高可用群集

1.MySQL-MMM 概述

MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM 使用 Perl 语言开发，主要用来监控和管理 MySQL Master-Master（双主）复制，虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说 MMM 这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个 slave 的 read 负载均衡
MMM 提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟 ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于 MMM 无法完全的保证数据一致性，所以 MMM 适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用 MMM 这种高可用架构

2、MySQL-MMM 优缺点

优点：高可用性，扩展性好，出现故障自动切换，对于主主同步，在同一时间只提供一台数据库写操作，保证的数据的一致性
缺点：Monitor 节点是单点，可以结合 Keepalived 实现高可用

3、MySQL-MMM 工作原

MMM 是一套灵活的脚本程序，基于 perl 实现，用来对 mysql replication 进行监控和故障迁移，并能管理 mysql Master-Master 复制的配置 (同一时间只有一个节点是可写的)

mmm_mond：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。此脚本需要在监管机上运行

mmm_agentd：运行在每个 mysql 服务器上的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行

mmm_control：一个简单的脚本，提供管理 mmm_mond 进程的命令

mysql-mmm 的监管端会提供多个虚拟 IP（VIP），包括一个可写 VIP，多个可读 VIP，通过监管的管理，这些 IP 会绑定在可用 mysql 之上，当某一台 mysql 宕机时，监管会将 VIP 迁移至其他 mysql

在整个监管过程中，需要在 mysql 中添加相关授权用户，以便让 mysql 可以支持监理机的维护

master1 : 192.168.94.11           db1

master2 : 192.168.94.22           db2

slave1 :    192.168.94.33           db3

slave2 :    192.168.94.44　    　db4

monitor :  192.168.94.55　      monitor

VIP : 

192.168.94.250　　writer

192.168.94.251　　reader

192.168.94.252　　reader

============================================================================================================================================

首先保证每个服务器时间统一

分别在 master1、master2、slave1、slave2 上安装 MariaDB

[root@sqlmaster1 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
[root@sqlmaster1 ~]# yum -y install epel-release
[root@sqlmaster1 ~]# yum clean all && yum makecache
[root@sqlmaster1 ~]# yum -y install mariadb-server mariadb

修改 master1、master2 的 my.cnf 配置文件

[root@sqlmaster1 ~]# vim /etc/my.cnf
# 在[mysqld]下添加以下内容                                                                                     
log-bin=mysql-bin
log-slave-updates=true
server-id=11        # id号不能相同 master2为22 
relay-log=relay-log-bin
relay-log-index=relay-log-bin.index

修改 slave1、slave2 的 my.cnf 配置文件

[root@sqlslave1 ~]# vim /etc/my.cnf
# 在[mysqld]下添加以下内容
server-id=33    # slave2 为44
relay-log=relay-log-bin
relay-log-index=relay-log-bin.index

启动 mariadb

[root@sqlmaster1 ~]# systemctl start mariadb
[root@sqlmaster2 ~]# systemctl start mariadb
[root@sqlslave1 ~]# systemctl start mariadb
[root@sqlslave2 ~]# systemctl start mariadb

配置主主复制，两个 master 互相授予权限

# master1
[root@sqlmaster1 ~]# mysql
MariaDB [(none)]> grant replication slave on *.* to ''sqlsync''@''192.168.94.%'' identified by ''123123'';
MariaDB [(none)]> flush privileges;
# master2 做相同操作
[root@sqlmaster2 ~]# mysql
MariaDB [(none)]> grant replication slave on *.* to ''sqlsync''@''192.168.94.%'' identified by ''123123'';
MariaDB [(none)]> flush privileges;
===========================================================================================================
# master1 
MariaDB [(none)]> show master status;
# master2
MariaDB [(none)]> change master to master_host=''192.168.94.11'',master_user=''sqlsync'',master_password=''123123'',master_log_file=''mysql-bin.000001'',master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;

# Slave_IO_Running、Slave_SQL_Running 都为Yes即可

===========================================================================================================
# master2
MariaDB [(none)]> show master status;
# master1
MraiaDB [(none)]> change master to master_host=''192.168.94.22'',master_user=''sqlsync'',master_password=''123123'',master_log_file=''mysql-bin.000001'',master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;

# Slave_IO_Running、Slave_SQL_Running 都为Yes即可

配置主从复制 slave1、slave2

# slave1
[root@sqlslave1 ~]# mysql
MariaDB [(none)]> change master to master_host=''192.168.94.11'',master_user=''sqlsync'',master_password=''123123'',master_log_file=''mysql_bin.000001'',master_log_pos=358;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;

# Slave_IO_Running、Slave_SQL_Running 都为Yes即可

# slave2做相同操作 
[root@sqlslave2 ~]# mysql 
MariaDB [(none)]> change master to master_host=''192.168.94.11'',master_user=''sqlsync'',master_password=''123123'',master_log_file=''mysql_bin.000001'',master_log_pos=358; 
MariaDB [(none)]> start slave; 
MariaDB [(none)]> show slave status\G;

# Slave_IO_Running、Slave_SQL_Running 都为Yes即可

安装 MySQL-MMM 服务器

# 所有服务器做相同操作
[root@sqlmaster1 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo    
[root@sqlmaster1 ~]# yum -y install epel-release mysql-mmm*

配置 MySQL-MMM

[root@sqlmaster1 ~]# vim /etc/mysql-mmm/mmm_common.conf

[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.22:/etc/mysql-mmm/
[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.33:/etc/mysql-mmm/
[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.44:/etc/mysql-mmm/
[root@sqlmaster1 ~]# scp /etc/mysql-mmm/mmm_common.conf 192.168.94.55:/etc/mysql-mmm/

[root@sqlmaster1 ~]# vim /etc/mysql-mmm/mmm_agent.conf 
#  master1 为 this db1 , master1 为 this db2 , slave1 为 this db3 , slave2 为 this db4

在 monitor 服务器上编辑 mmm_mon.conf 配置文件

[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf

授权 mmm-agent

# 每台db都做相同操作
[root@monitor ~]# mysql
MariaDB [(none)]> grant super, replication client, process on *.* to ''mmm_agent''@''192.168.94.%'' identified by ''123123'';
MraidDB [(none)]> flush privileges;

开启 agent 服务

# 所有服务器都做相同操作
[root@sqlmaster1 ~]# systemctl enable mysql-mmm-agent
Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.
[root@sqlmaster1 ~]# systemctl start mysql-mmm-agent

在 monitor 服务器上进行检测

vim /usr/lib/systemd/<span>system</span>/nginx.service 

<span>[Unit]</span><span>Description=<span>nginx - high performance web server</span></span><span>After=<span>network.target remote-fs.target nss-lookup.target</span></span><span>[Service]</span><span>Type=<span>forking</span></span><span>ExecStart=<span>/usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf</span></span><span>ExecReload=<span>/usr/local/nginx/sbin/nginx -s reload</span></span><span>ExecStop=<span>/usr/local/nginx/sbin/nginx -s stop</span></span><span>[Install]</span><span>WantedBy=<span>multi-user.target</span></span>

[<span>Unit</span>]:服务的说明<span>
Description:描述服务</span><span>
After:描述服务类别</span><span>
[Service]服务运行参数的设置</span><span>
Type=forking是后台运行的形式</span><span>
ExecStart为服务的具体运行命令</span><span>
ExecReload为重启命令</span><span>
ExecStop为停止命令</span><span>
PrivateTmp=True表示给服务分配独立的临时空间</span><span>
注意：启动、重启、停止命令全部要求使用绝对路径</span><span>
[Install]服务安装的相关设置，可设置为多用户</span>

/usr/lib/systemd/<span>system</span>

systemctl <span><span>start</span> nginx.service</span>

systemctl disable nginx<span>.service</span>

systemctl status nginx<span>.service</span>

systemctl restart nginx<span>.service</span>

systemctl <span>list</span>-units --<span><span>type</span>=</span>service