mysq的mmm高可用

首页 > 代码库 > mysq的mmm高可用

2024-09-04 13:13:31 218人阅读

MMM实现MySQL高可用

一、MMM介绍

二、MySQL-MMM工作关系图

三、MySQL-MMM工作原理图

四、案例环境使用五台服务器模拟搭建，具体环境如下所示

主机名称	操作系统	IP地址	VIP	主要软件
M1	CentOS 6.5 x86_64	192.168.200.21	192.168.200.30	MySQL-MMM 2.2.1
M2	CentOS 6.5 x86_64	192.168.200.22	192.168.200.30	MySQL-MMM 2.2.1
S1	CentOS 6.5 x86_64	192.168.200.23	192.168.200.33	MySQL-MMM 2.2.1
S2	CentOS 6.5 x86_64	192.168.200.24	192.168.200.34	MySQL-MMM 2.2.1
monitor	CentOS 6.5 x86_64	192.168.200.25	无	MySQL-MMM 2.2.1

五、案例实施

1.1 环境准备

[root@m1 ~]# cat /etc/hosts

192.168.200.21m1

192.168.200.22m2

192.168.200.23s1

192.168.200.24s2

192.168.200.25monitor

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

[root@m1 ~]# scp /etc/hosts m2:/etc/hosts

[root@m1 ~]# scp /etc/hosts s1:/etc/hosts

[root@m1 ~]# scp /etc/hosts s2:/etc/hosts

[root@m1 ~]# scp /etc/hosts monitor:/etc/hosts

1.2 搭建MMM, MySQL多主多从模式replication

1.2.1 安装MySQL并配置 (m1 m2 s1 s2)

[root@m1 ~]# yum -y install mysql mysql-server mysql-devel

注意：在m2 s1 s2 上做同样的操作,大家一定要注意：对后续实验过程中对perl语言开发环境的完整支持，记得要将centos6.5的两张光盘都要做到yum仓库中去。

1.2.3 配置m1 m2 s1 s2

[root@m1 ~]# vi /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/lib/mysql/mysql.err

log=/var/lib/mysql/query_log.log

log-slow-queries=/var/lib/mysql/slow_query_log.log

user=mysql

default-character-set=utf8

log-bin=mysql-bin

server-id=1

binlog-ignore-db=mysql,information_schema,test

log-slave-updates

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[client]

default-character-set=utf8

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

注意：每台mysql主机server-id不能相同，其它配置文件参数相同即可！

比如：m1 --- server-id=1

m2 --- server-id=2

s1 --- server-id=3

s2 --- server-id=4

[root@m1 ~]# scp /etc/my.cnf m2:/etc/my.cnf

[root@m1 ~]# scp /etc/my.cnf s1:/etc/my.cnf

[root@m1 ~]# scp /etc/my.cnf s2:/etc/my.cnf

[root@m2 ~]# sed -i ‘/ server-id/ s/1/2/g‘ /etc/my.cnf

[root@s1 ~]# sed -i ‘/ server-id/ s/1/3/g‘ /etc/my.cnf

[root@s2 ~]# sed -i ‘/ server-id/ s/1/4/g‘ /etc/my.cnf

分别在m1 m2 s1 s2上启动mysql服务

[root@m1 ~]# /etc/init.d/mysqld start

[root@m1 ~]# chkconfig mysqld on

[root@m1 ~]# mysqladmin -uroot password "123"

1.2.4 MySQL配置m1 m2上为AA复制模式

1.2.4.1 先产看下log bin日志和postion值位置

[root@m1 ~]# mysql -uroot -p123

mysql> show master status;

+------------------+----------+--------------+-------------------------------+

+------------------+----------+--------------+-------------------------------+

+------------------+----------+--------------+-------------------------------+

[root@m2 ~]# mysql -uroot -p123

mysql> show master status;

+------------------+----------+--------------+-------------------------------+

+------------------+----------+--------------+-------------------------------+

+------------------+----------+--------------+-------------------------------+

1.2.4.2 m1 m2互相提升访问权限

在m1上的操作：

mysql> grant replication slave on *.* to ‘replication‘@‘192.168.200.%‘ identified by ‘123‘;

mysql> flush privileges;

在m2上的操作：

mysql> grant replication slave on *.* to ‘replication‘@‘192.168.200.%‘ identified by ‘123‘;

mysql> flush privileges;

mysql> change master to master_host=‘192.168.200.21‘,

-> master_user=‘replication‘,master_password=‘123‘,

-> master_log_file=‘mysql-bin.000003‘,

-> master_log_pos=249;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.21

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 492

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 494

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 492

Relay_Log_Space: 650

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

在m1上的操作：

mysql> change master to master_host=‘192.168.200.22‘, master_user=‘replication‘,master_password=‘123‘, master_log_file=‘mysql-bin.000003‘, master_log_pos=249;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.22

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 735

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 494

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 735

Relay_Log_Space: 650

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

1.2.4.3 测试AA复制同步，在mysql-m2上新建一个库yylin

mysql> create database yylin;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| yylin |

+--------------------+

在m1上查看测试：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| yylin |

+--------------------+

4 rows in set (0.01 sec)

1.2.5 配置s1和s2作为m1 的从库

1.2.5.1 先看一下m1 上master状态值

mysql> show master status;

+------------------+----------+--------------+-------------------------------+

+------------------+----------+--------------+-------------------------------+

+------------------+----------+--------------+-------------------------------+

1.2.5.2 在s1和s2分别执行:

[root@s1 ~]# mysql -uroot -p123

mysql> change master to master_host=‘192.168.200.21‘, master_user=‘replication‘,master_password=‘123‘, master_log_file=‘mysql-bin.000003‘, master_log_pos=825;

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.200.21

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 825

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 825

Relay_Log_Space: 407

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

1.3 安装配置mysql-MMM

1.3.1 Centos默认没有

[root@m1 ~]# wget http://mirrors.yun-idc.com/epel/6/x86_64/epel-release-6-8.noarch.rpm

[root@m1 ~]# rpm -ivh epel-release-6-8.noarch.rpm

在其它m2 s1 s2 monitor上均做如上操作，并在五台服务器上都安装mysql-mmm*

[root@m1 ~]# yum -y install mysql-mmm*

1.3.2 在m1 m2 s1 s2 四个节点上授权monitor访问

[root@m1 ~]# mysql -uroot -p123

mysql> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.200.%‘ identified by ‘123‘;

Query OK, 0 rows affected (0.01 sec)

mysql> grant super,replication client, process on *.* to ‘mmm_agent‘@‘192.168.200.%‘ identified by ‘123‘;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

1.3.3 修改/etc/mysql-mmm/mmm_common.conf配置文件，系统中所有主机(m1 m2 s1 s2 monitor)的该配置文件内容都是一样的，包括监控主机mysql-monitor

[root@m1 ~]# cd /etc/mysql-mmm/

[root@m1 mysql-mmm]# cp mmm_common.conf mmm_common.conf.bak

[root@m1 mysql-mmm]# vi /etc/mysql-mmm/mmm_common.conf

active_master_role writer

cluster_interface eth1

pid_path /var/run/mysql-mmm/mmm_agentd.pid

bin_path /usr/libexec/mysql-mmm/

replication_user replication

replication_password 123

agent_user mmm_agent

agent_password 123

</host>

ip 192.168.200.21

mode master

peer db2

</host>

ip 192.168.200.22

mode master

peer db1

</host>

ip 192.168.200.23

mode slave

</host>

ip 192.168.200.24

mode slave

</host>

hosts db1, db2

ips 192.168.200.30

mode exclusive

</role>

hosts db3, db4

ips 192.168.200.31, 192.168.200.32

mode balanced

</role>

[root@m1 mysql-mmm]# scp mmm_common.conf m2:/etc/mysql-mmm/mmm_common.conf

[root@m1 mysql-mmm]# scp mmm_common.conf s1:/etc/mysql-mmm/mmm_common.conf

[root@m1 mysql-mmm]# scp mmm_common.conf s2:/etc/mysql-mmm/mmm_common.conf

[root@m1 mysql-mmm]# scp mmm_common.conf \

monitor:/etc/mysql-mmm/mmm_common.conf

1.3.4 在数据库主机(m1 m2 s1 s2)上我们需要编辑/etc/mysql-mmm/mmm_agent.conf 文件，根据不同的主机修改为不同的值:

[root@m1 mysql-mmm]# cp mmm_agent.conf mmm_agent.conf.bak

[root@m1 mysql-mmm]# vi mmm_agent.conf

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.

this db1

上面的db1在不同的数据库机器上(m1 m2 s1 s2)分别修改为:db1、db2、db3和db4

[root@m2 ~]# cd /etc/mysql-mmm/

[root@m2 mysql-mmm]# sed -i ‘/^this/ s/1/2/g‘ mmm_agent.conf

[root@s1 ~]# cd /etc/mysql-mmm/

[root@s1 mysql-mmm]# sed -i ‘/^this/ s/1/3/g‘ mmm_agent.conf

[root@s2 ~]# cd /etc/mysql-mmm/

[root@s2 mysql-mmm]# sed -i ‘/^this/ s/1/4/g‘ mmm_agent.conf

1.3.5 在监控主机(monitor)上我们需要编辑/etc/mysql-mmm/mmm_mon.conf

[root@monitor ~]# cd /etc/mysql-mmm/

[root@monitor mysql-mmm]# cp mmm_mon.conf mmm_mon.conf.bak

[root@monitor mysql-mmm]# vi mmm_mon.conf

include mmm_common.conf

ip 127.0.0.1

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 192.168.200.21,192.168.200.22,192.168.200.23,192.168.200.24

auto_set_online 10

# 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>

monitor_user mmm_monitor

monitor_password 123

</host>

debug 0

1.3.6 启动代理

[root@m1 ~]# /etc/init.d/mysql-mmm-agent start

Starting MMM Agent Daemon: [确定]

[root@m2 ~]# /etc/init.d/mysql-mmm-agent start

Starting MMM Agent Daemon: [确定]

[root@s1 ~]# /etc/init.d/mysql-mmm-agent start

Starting MMM Agent Daemon: [确定]

[root@s2 ~]# /etc/init.d/mysql-mmm-agent start

Starting MMM Agent Daemon: [确定]

1.3.7 启动监控

[root@monitor ~]# /etc/init.d/mysql-mmm-monitor start

Starting MMM Monitor Daemon: [确定]

1.3.8 测试集群

[root@monitor mysql-mmm]# mmm_control show

db1(192.168.200.21) master/ONLINE. Roles: writer(192.168.200.30)

db2(192.168.200.22) master/ONLINE. Roles:

db3(192.168.200.23) slave/ONLINE. Roles: reader(192.168.200.32)

db4(192.168.200.24) slave/ONLINE. Roles: reader(192.168.200.31)

主库或者从库宕机，会有十秒左右时间，数据不能够查询和插入

本文出自 “12384524” 博客，请务必保留此出处http://12394524.blog.51cto.com/12384524/1898244

mysq的mmm高可用

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > mysq的mmm高可用

mysq的mmm高可用

看完仍有疑问？有类似问题直接问程序猿