Mysql-mmm配置全自动切换主从关系和读写分离
mysql-mmm的作用很简单就是读和写以ip的形式分离出来，并且可以全自动对mysql主从配置进行故障切换。
整个架构如下图：
操作系统全部为centos5.5 32bit
mysql版本为mysql-5.1.59
mysql-mmm版本为mysql-mmm-2.2.1
另外还需要4个虚拟IP，作用为：
192.168.93.141 数据库写入ip
192.168.93.142 数据库读取ip
192.168.93.143 数据库读取ip
192.168.93.144 数据库读取ip
一、安装mysql
| useradd mysql tar zxvf mysql-5.1.59.tar.gz cd mysql-5.1.59 ./configure –prefix=/usr/local/mysql –without-debug –enable-thread-safe-client –enable-assembler –enable-profiling –with-mysqld-ldflags=-all-static –with-client-ldflags=-all-static –with-charset=utf8 –with-extra-charsets=all –with-big-tables –enable-largefile –without-ndb-debug –with-plugins=partition –localstatedir=/home/var make make install |
vi /etc/my.cnf
| [client] port = 3306 socket = /tmp/mysql.sock[mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking log-bin=mysql-bin character-set-server=utf8 [mysqldump] [isamchk] [myisamchk] [mysqlhotcopy] [mysqld_safe] |
保存退出！
继续安装Mysql
| /usr/local/mysql/bin/mysql_install_db –user=mysql chown -R mysql:mysql /home/var /usr/local/mysql/bin/mysqld_safe –user=mysql & cp ./support-files/mysql.server /etc/rc.d/init.d/mysql chmod +x /etc/rc.d/init.d/mysql chkconfig –add mysql |
vi ~/.bash_profile
| PATH=$PATH:$HOME/bin:/usr/local/mysql/bin/ |
追加蓝色部分，然后保存退出
使配置立即生效
| source ~/.bash_profile |
二、配置主从关系
DB1：编辑/etc/my.cnf，加入以下内容：
| server-id=1 log-bin=mysql-bin binlog_format=ROW log-slave-updates sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 skip_slave_start |
DB2：编辑/etc/my.cnf，加入以下内容：
| server-id=2 log-bin=mysql-bin binlog_format=ROW log-slave-updates sync_binlog=1 auto_increment_increment=2 auto_increment_offset=2 |
DB3：编辑/etc/my.cnf，加入以下内容：
| server-id=3 log-bin=mysql-bin log-slave-updates |
然后重启mysql服务！
接下创建一个同步用的帐号并授权：
DB1授权：
| mysql -uroot -p123456 -e “GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.93.138’ IDENTIFIED BY ‘slave’; ” mysql -uroot -p123456 -e “GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.93.139’ IDENTIFIED BY ‘slave’; “ |
DB2授权：
| mysql -uroot -p123456 -e “GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.93.137’ IDENTIFIED BY ‘slave’; ” mysql -uroot -p123456 -e “GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’192.168.93.139’ IDENTIFIED BY ‘slave’; “ |
DB3的话以谁为主都无所谓，当主出现故障的时候，mmm会自动切换主的，我这里就以DB2为主，配置方从的步骤：锁表——（主）导出数据——（主）查看指针位置——（主）解锁——（从）导入数据——（从）导入主服务器的指针位置——（双方）开启主从线程
DB2：（PS我这里是新的数据库，所以省了导出数据这一步！）
| mysql> flush tables with read lock; mysql> show master status; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000004 | 586 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) mysql> unlock tables; |
得出指针位置之后在DB1和DB3上操作：
| mysql> change master to -> master_host=’192.168.93.138′, -> master_user=’slave’, -> master_password=’slave’, -> master_log_file=’mysql-bin.000004′, -> master_log_pos=586, -> master_connect_retry=10; Query OK, 0 rows affected (0.04 sec) mysql> slave start; mysql> show slave status\G; |
看见蓝色那两行已经yes了，表示配置成功，刚才一口气把DB2配成DB1和DB3的主，现在要把DB1配成DB2的主，实现DB1和DB2互为主从关系
查看DB1的指针位置：
| mysql> show master status; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000005 | 586 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) |
然后在DB2上导入指针位置：
| mysql> change master to -> master_host=’192.168.93.137′, -> master_user=’slave’, -> master_password=’slave’, -> master_log_file=’mysql-bin.000005′, -> master_log_pos=586, -> master_connect_retry=10; Query OK, 0 rows affected (0.01 sec) mysql> slave start; mysql> show slave status\G; |
现在三台DB的主从关系都配置好了，可以测试一下：
在DB1的test库建个表，在DB2插入一条数据，在DB3查看
DB1：
| mysql> create table test.t (id int(11) not null auto_increment,name varchar(30),primary key (id)); Query OK, 0 rows affected (0.03 sec) |
DB2：
| mysql> use test Database changed mysql> show tables; +—————-+ | Tables_in_test | +—————-+ | t | +—————-+ 1 row in set (0.00 sec) mysql> insert into t(name) values (‘mysql-mmm’); |
DB3：
| mysql> use test Database changed mysql> select * from t; +—-+———–+ | id | name | +—-+———–+ | 2 | mysql-mmm | +—-+———–+ 1 row in set (0.00 sec) |
DB1：
| mysql> select * from test.t; +—-+———–+ | id | name | +—-+———–+ | 2 | mysql-mmm | +—-+———–+ 1 row in set (0.00 sec) |
OK！三台DB的主从配置正常工作
三、安装mysql-mmm
安装Mysql-mmm之前，需要先安装一些perl模块，安装方法用perl -MCPAN -e shell 直接在线安装，第一次使用perl -MCPAN -e shell 命令时会有一大堆选项的，我直接enter过去，比较关键的是选择地区、国家和源这三个，按的时候注意选择一下就没问题，DB1、DB2和DB3需要安装 以下模块：
| # perl -MCPAN -e shell cpan> install Algorithm::Diff cpan> install DBI cpan>install Log::Dispatch cpan> install Log::Log4perl cpan> install Mail::Send cpan> install Net::ARP cpan> install Proc::Daemon cpan> install Time::HiRes cpan>install DBD::mysql cpan>install File::stat cpan>install File:basename |
server4需要安装以下：
| # perl -MCPAN -e shell cpan> install Algorithm::Diff cpan> install Class::Singleton cpan> install Log::Dispatch cpan> install Log::Log4perl cpan> install Mail::Send cpan> install Proc::Daemon cpan> install Thread::Queue cpan> install Time::HiRes cpan> install DBI cpan>install DBD::mysql |
安装mysql-mmm，四台机器都要安装：
| tar zxvf mysql-mmm-2.2.1.tar.gz cd mysql-mmm-2.2.1 make instal |
mysql-mmm文件位置及作用如下：
| /usr/lib/perl5/vendor_perl/5.8.8/MMM MMM 使用的 perl 模块 /usr/lib/mysql-mmm MMM 的脚本揑件 /usr/sbin MMM 的命令保存路径 /var/log/mysql-mmm MMM 的日志保存路径 /etc MMM 配置文件保存的路径 /etc/mysql-mmm MMM 配置文件保存的路径，优先级最高 /etc/init.d/ agentd 和 monitor 的启劢关闭脚本 |
数据库授权一个Mysql-mmm专用用户（DB1、DB2和DB3都要授权）：
| mysql -uroot -p123456 -e “grant super,replication client,process on *.* to ‘mmm_agent’@’192.168.93.137’ identified by ‘mmm_agent’;” mysql -uroot -p123456 -e “grant super,replication client,process on *.* to ‘mmm_agent’@’192.168.93.138’ identified by ‘mmm_agent’;” mysql -uroot -p123456 -e “grant super,replication client,process on *.* to ‘mmm_agent’@’192.168.93.139’ identified by ‘mmm_agent’;” mysql -uroot -p123456 -e “grant super,replication client,process on *.* to ‘mmm_agent’@’192.168.93.140’ identified by ‘mmm_agent’;” |
修改mysql-mmm配置文件：
DB1：
/etc/mysql-mmm/mmm_agent.conf
| include mmm_common.conf this db1 |
蓝色的部分DB2和DB3则分别修改为db2和db3
/etc/mysql-mmm/mmm_common.conf
| active_master_role writer <host default> cluster_interface eth0 pid_path /var/run/mmm_agentd.pid replication_user slave agent_user mmm_agent <host db1> <host db2> <host db3> <role reader> |
将这份配置复制至其它三台机器，包括server4，然后启动DB1\、DB2和DB3可以启动mysql-mmm-agant:：
| [root@localhost mysql-mmm-2.2.1]# /etc/init.d/mysql-mmm-agent start Daemon bin: ‘/usr/sbin/mmm_agentd’ Daemon pid: ‘/var/run/mmm_agentd.pid’ Starting MMM Agent daemon… Ok |
最后配置server4的mysql-mmm-monitor
/etc/mysql-mmm/mmm_mon.conf
| include mmm_common.conf
<monitor> <host default> debug 0 |
启动mysql-mmm-monitor：
| [root@localhost mysql-mmm-2.2.1]# /etc/init.d/mysql-mmm-monitor start Daemon bin: ‘/usr/sbin/mmm_mond’ Daemon pid: ‘/var/run/mmm_mond.pid’ Starting MMM Monitor daemon: Ok [root@localhost mysql-mmm-2.2.1]# mmm_control show db1(192.168.93.137) master/AWAITING_RECOVERY. Roles: db2(192.168.93.138) master/AWAITING_RECOVERY. Roles: db3(192.168.93.139) slave/AWAITING_RECOVERY. Roles: |
将所有DB服务器设为online状态：
| [root@localhost mysql-mmm-2.2.1]# mmm_control set_online db1 OK: State of ‘db1’ changed to ONLINE. Now you can wait some time and check its new roles! [root@localhost mysql-mmm-2.2.1]# mmm_control set_online db2 OK: State of ‘db2’ changed to ONLINE. Now you can wait some time and check its new roles! [root@localhost mysql-mmm-2.2.1]# mmm_control set_online db3 OK: State of ‘db3’ changed to ONLINE. Now you can wait some time and check its new roles! [root@localhost mysql-mmm-2.2.1]# mmm_control show db1(192.168.93.137) master/ONLINE. Roles: reader(192.168.93.143), writer(192.168.93.141) db2(192.168.93.138) master/ONLINE. Roles: reader(192.168.93.142) db3(192.168.93.139) slave/ONLINE. Roles: reader(192.168.93.144) |
设置成功，下面查看节点状态：
| [root@localhost mysql-mmm-2.2.1]# mmm_control checks all db2 ping [last change: 2011/10/07 03:10:39] OK db2 mysql [last change: 2011/10/07 03:10:39] OK db2 rep_threads [last change: 2011/10/07 03:10:39] OK db2 rep_backlog [last change: 2011/10/07 03:10:39] OK: Backlog is null db3 ping [last change: 2011/10/07 03:10:39] OK db3 mysql [last change: 2011/10/07 03:10:39] OK db3 rep_threads [last change: 2011/10/07 03:10:39] OK db3 rep_backlog [last change: 2011/10/07 03:10:39] OK: Backlog is null db1 ping [last change: 2011/10/07 03:10:39] OK db1 mysql [last change: 2011/10/07 03:10:39] OK db1 rep_threads [last change: 2011/10/07 03:10:39] OK db1 rep_backlog [last change: 2011/10/07 03:10:39] OK: Backlog is null |
最后将除write机器的DB的read_only状态设为on：
| set global read_only=on; |
并写入my.cnf！
在write角色的机器上授权一个帐号给外部登陆：
| mysql -uroot -p123456 -e “grant all privileges on test.* to lihuipeng@’192.168.93.%’ identified by ‘lihuipeng’;” |
然后从另外一台机器上试试登陆四个虚拟IP：
| [root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.141 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 716 Server version: 5.1.59-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql>
[root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.143 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql>
[root@localhost mysql-mmm-2.2.1]# mysql -ulihuipeng -plihuipeng -h192.168.93.144 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> |
四个ip都可以使用，其它功能大家可以自己去测试一下，这里不多写了，这里主要把数据库的write功能独立出来一个ip，让两台互为主从的机器中 其中一台担任write角色，其余机器担任read角色，出现故障时mysql-mmm可以自动切换write角色和主从关系。
配合amoeba完成真正读写分离：http://lihuipeng.blog.51cto.com/3064864/689103
本文出自 “疯狂的猴子” 博客，请务必保留此出处http://lihuipeng.blog.51cto.com/3064864/689064