mysql(mmm部署).docx

linux下安装mysql mmm环境:OS:Rad Hat Linux As51.安装步骤1.1 环境部署节点角色ipdb111db212db313VipRoleDescription50Writer应用程序通过该ip写入数据51Reader应用程序通过该ip读取数据52Reader应用程序通过该ip读取数据DB1从正常运行到宕机，再正常运行的整个过程：DB1宕机后,mmm会将写入的VIP地址飘逸到DB2,DB3从库对应的主库由之前的DB1转变为DB2,当DB1恢复后,DB1和DB2互为主从，DB1宕机期间在DB2上的操作会自动同步到DB1，但是DB3从库对应的主库不会自动指向DB1，同时写入的VIP也不会转移到DB1, 这个需要在下次DB2宕机之后才会指向DB1.1.2 mmm下载mmm下载地址:/p/mysql-master-ha/downloads/list/p/mysql-master-master1.3 建立各主机的等效连接步骤省略1.3.1 验证等效连接 名称节点到各节点的等效性roothost01 # ssh host02;host03 dateLast login: Mon Apr 18 11:27:01 2016 from roothost02 # ssh host01;host03 dateLast login: Mon Apr 18 10:48:31 2016 from roothost03 # ssh host01;host02 dateLast login: Mon Apr 18 14:52:30 2016 from 1.4 在3台机器上安装mysql分别在db1、db2、db3上安装mysql安装步骤可以参考/uid-77311-id-5700916.html1.5 配置各机器配置文件1.5.1 DB1vi /db/mmm/mysql/conf/f 内容如下mysqldserver_id=1001log-bin=binloglog-bin-index=binlog.indexport=3306basedir=/db/mmm/mysqldatadir=/db/mmm/mysql/datasocket=/db/mmm/mysql/mysql.sockcharacter-set-server=utf8max_connections = 1500show_compatibility_56=onreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=sys.%slave-net-timeout=3600#master-connect-retry=60 5.1版本以后不支持该参数#master-retry-count=86400 5.1版本以后不支持该参数sync_binlog=1innodb_flush_log_at_trx_commit=11.5.2 DB2vi /db/mmm/mysql/conf/f 内容如下mysqldserver_id=1002log-bin=binloglog-bin-index=binlog.indexport=3306basedir=/db/mmm/mysqldatadir=/db/mmm/mysql/datasocket=/db/mmm/mysql/mysql.sockcharacter-set-server=utf8max_connections = 1500show_compatibility_56=onreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=sys.%slave-net-timeout=3600#master-connect-retry=60 5.1版本以后不支持该参数#master-retry-count=86400 5.1版本以后不支持该参数sync_binlog=1innodb_flush_log_at_trx_commit=11.5.3 DB3vi /db/mmm/mysql/conf/f 内容如下mysqldserver_id=1003read_only=1 #设置为只读,跟db1 db2的差别log-bin=binloglog-bin-index=binlog.indexport=3306basedir=/db/mmm/mysqldatadir=/db/mmm/mysql/datasocket=/db/mmm/mysql/mysql.sockcharacter-set-server=utf8max_connections = 1500show_compatibility_56=onreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=information_schema.%replicate-wild-ignore-table=performance_schema.%replicate-wild-ignore-table=sys.%slave-net-timeout=3600#master-connect-retry=60 5.1版本以后不支持该参数#master-retry-count=86400 5.1版本以后不支持该参数1.6 启动mysql启动各DBroothost01 bin# ./mysqld_safe -defaults-file=/db/mmm/mysql/conf/f -user=mysql登陆各DB./mysql -h localhost -u root -p -S /db/mmm/mysql/mysql.sock1.7 主从配置(db1和db2配置为主主,db3配置为db1的从)1.7.1 授权在db1和db2上执行：grant replication slave ON *.* TO repl% identified by mysql;flush privileges;1.7.2 db3配置为db1的从change master to master_host=11, master_port=3306, master_user=repl, master_password=mysql, master_log_file=binlog.000002, master_log_pos=746;start slave;1.7.3 db1和db2配置为主主db1:change master to master_host=12, master_port=3306, master_user=repl, master_password=mysql, master_log_file=binlog.000002, master_log_pos=746;start slave;db2:change master to master_host=11, master_port=3306, master_user=repl, master_password=mysql, master_log_file=binlog.000002, master_log_pos=746;start slave;1.8 安装mmmdb1、db2、db3都要安装mysql-mmm-agent，我们这里db1还需要充当monitor的角色，所以在db1上还需要安装mysql-mmm-monitor.1.8.1 DB1-依赖包-rpm -ivh perl-Algorithm-Diff-1.1902-1.el5.rf.noarch.rpmrpm -ivh perl-Class-Singleton-1.4-1.el5.rf.noarch.rpmrpm -ivh perl-Proc-Daemon-0.03-1.2.el5.rf.noarch.rpmrpm -ivh perl-Net-ARP-1.0-1.el5.rf.x86_64.rpmrpm -ivh perl-Log-Log4perl-1.33-1.el5.pp.noarch.rpmrpm -ivh perl-Path-Class-0.16-1.el5.rf.noarch.rpm-mmm安装包-rpm -ivh mysql-mmm-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-tools-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-agent-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-monitor-2.2.1-1.el5.noarch.rpm1.8.2 DB2-依赖包-rpm -ivh perl-Algorithm-Diff-1.1902-1.el5.rf.noarch.rpmrpm -ivh perl-Class-Singleton-1.4-1.el5.rf.noarch.rpmrpm -ivh perl-Proc-Daemon-0.03-1.2.el5.rf.noarch.rpmrpm -ivh perl-Net-ARP-1.0-1.el5.rf.x86_64.rpmrpm -ivh perl-Log-Log4perl-1.33-1.el5.pp.noarch.rpmrpm -ivh perl-Path-Class-0.16-1.el5.rf.noarch.rpm-mmm安装包-rpm -ivh mysql-mmm-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-tools-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-agent-2.2.1-1.el5.noarch.rpm1.8.3 DB3-依赖包-rpm -ivh perl-Algorithm-Diff-1.1902-1.el5.rf.noarch.rpmrpm -ivh perl-Class-Singleton-1.4-1.el5.rf.noarch.rpmrpm -ivh perl-Proc-Daemon-0.03-1.2.el5.rf.noarch.rpmrpm -ivh perl-Net-ARP-1.0-1.el5.rf.x86_64.rpmrpm -ivh perl-Log-Log4perl-1.33-1.el5.pp.noarch.rpmrpm -ivh perl-Path-Class-0.16-1.el5.rf.noarch.rpm-mmm安装包-rpm -ivh mysql-mmm-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-tools-2.2.1-1.el5.noarch.rpmrpm -ivh mysql-mmm-agent-2.2.1-1.el5.noarch.rpm1.9 mmm配置1.9.1 授权在db1 db2 db3上执行如下授权语句：grant super, replication client, process on *.* to mmm_agent192.168.56.% identified by mysql;grant replication client on *.* to mmm_monitor192.168.56.% identified by mysql;flush privileges;1.9.2 编辑配置文件 mmm_common.confdb1、db2、db3上都配置一样，可以在db1上配置好后，拷贝到db2、db3.roothost01 mysql-mmm# more /etc/mysql-mmm/mmm_common.confactive_master_role writer cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user repl #复制的账号 replication_password mysql #复制账号密码 agent_user mmm_agent #跟上面步骤创建的用户保持一致 agent_password mysql ip 11 #db1的ip mode master peer db2 ip 12 #db2的ip mode master peer db1 ip 13 #db3的ip mode slave hosts db1, db2 ips 50 #设置写入的虚拟IP mode exclusive hosts db1, db2 ips 51, 52 #设置读取的虚拟IP mode balanced mmm_agent.conf在db1、db2、db3上都需要配置，配置分别如下：db1roothost01 mysql-mmm# more /etc/mysql-mmm/mmm_agent.confinclude 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.this db1db2roothost02 mysql-mmm# more mmm_agent.confinclude 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.this db2db3roothost03 mysql-mmm# more mmm_agent.confinclude 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.this db mmm_mon.conf仅在monitor的节点上配置，我们这里db1充当monitor的角色，所以只需要在db1上做配置。roothost01 mysql-mmm# more /etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf ip 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 11,12 #监控的双主db的ip地址 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_user mmm_monitor #监控账号 monitor_password mysql #监控账号密码debug 01.10 启动mmm1.10.1 启动agent在db1 db2 db3上执行如下命令roothost01 init.d# /etc/init.d/mysql-mmm-agent startStarting MMM Agent Daemon: OK 1.10.2 启动监控在监控机器上执行如下命令,我们这里是采用db1做监控机，就在该机器上执行即可。roothost01 init.d# /etc/init.d/mysql-mmm-monitor startStarting MMM Monitor Daemon: OK 1.10.3 查看监控状态在监控机器上执行命令roothost01 init.d# mmm_control show db1(11) master/ONLINE. Roles: reader(51), writer(50) db2(12) master/ONLINE. Roles: reader(52) db3(13) slave/ONLINE. Roles:1.11 故障模拟停止db1看下50看下是否能否漂移到db2上去，同时查看DB3的Slave的IP会不会从DB1改到DB2。停止db1:roothost01 init.d# mysqladmin -h localhost -u root -p -S /db/mmm/mysql/mysql.sock shutdown查看mmm状态roothost01 init.d# mmm_control show db1(11) master/HARD_OFFLINE. Roles: db2(12) master/ONLINE. Roles: reader(51), reader(52), writer(50) db3(13) slave/ONLINE. Roles:们可以看到当db1恢复后就充当slave的角色了！只有当db2挂了以后db1又会担当起主服务器的写入功能查看db3复制的状态mysql show slave status G;* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 12 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000004 Read_Master_Log_Pos: 692 Relay_Log_File: host03-relay-bin.000002 Relay_Log_Pos: 317 Relay_Master_Log_File: binlog.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replica