从零开始配置MySQL MMM .

云平台是个好东西，MySQL-mmm的典型配置是需要五台机器，一台作为mmm admin，两台master，两台slave。一下子找五台机器真不容易，何况还要安装同样的操作系统。而有了cloud，简单几步就有了完备的实验环境：四台数据库服务器和一台管理服务器（Memory：8G，CPU：2G，Disk：128G，64bit RHEL6）。在此，向为付出辛劳搭建云平台的同事们表示由衷的感谢:-)下面言归正传，开始全新的MySQL mmm之旅。下面要配置的MySQL Cluster环境包含四台数据库服务器和一台管理服务器，如下：IPServer Nameserver idmonitor192.168.84.174--master192.168.85.167db11master192.168.85.169db22slave192.168.85.171db33slave192.168.85.168db44 配置完成后，使用下面的虚拟IP访问MySQL ClusterIProle192.168.85.200writer192.168.85.201reader192.168.85.202reader192.168.85.203reader 一、配置MySQL Relication 1. 安装MySQL 通过yum命令直接安装了mysql5.1.52。 2. 修改配置文件/etc/my.cnf 要将添加的内容放在配置文件的[mysqld]部分，如下： [plain] view plaincopyprint?[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql #下面为新添加的内容 default-storage-engine = innodb replicate-ignore-db = mysql binlog-ignore-db = mysql server-id = 1 log-bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-bin.relay relay_log_index = /var/log/mysql/mysql-bin.relay.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql#下面为新添加的内容default-storage-engine = innodbreplicate-ignore-db = mysqlbinlog-ignore-db = mysqlserver-id = 1log-bin = /var/log/mysql/mysql-bin.loglog_bin_index = /var/log/mysql/mysql-bin.log.indexrelay_log = /var/log/mysql/mysql-bin.relayrelay_log_index = /var/log/mysql/mysql-bin.relay.indexexpire_logs_days = 10max_binlog_size = 100Mlog_slave_updates = 1 注意： 1）server-id在每台服务器上的值都是不一样，在这里依次为1、2、3、4。 2）因为在这里把log文件配置到了/var/log/mysql下，而mysql默认的目录是在/var/lib/mysql，所以首先要新建mysql文件夹，然后用chown -R mysql.mysql mysql命令将mysql的所有者修改为用户mysql。其次要保证，mysql文件夹的权限755（即-rwxr-xr-x）。如果没有修改权限和所有者，重启服务时就会在错误日志中出现找不到mysql-bin.log或者mysql-bin.log.index的错误（/usr/libexec/mysqld: File '/var/log/mysql/mysql-bin.log.index' not found (Errcode: 13)）。 3. 重新启动mysql服务在完成了对my.cnf的修改后，通过service mysqld restart重新启动mysql服务。在正确启动后，可以通过如下方式检查配置是否正确： 1）登录mysql，执行show master status，看是否有如下输出：+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 106 | | mysql |+------------------+----------+--------------+------------------+ 2）到/var/log/mysql目录下，看是否产生了类似mysql-bin.000001和mysql-bin.log.index的文件。二、新建同步数据库需要的用户使用mysql-mmm时一共需要三个用户: replication、mmm_agent和mmm_monitor（管理服务器上用来监控cluster状态的用户，所以可以限定只能从管理服务器登录）。使用下面三条命令新建这三个用户并分配相应的权限：[sql] view plaincopyprint?GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.84.%' IDENTIFIED BY 'monitor'; GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.85.%' IDENTIFIED BY 'agent'; GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.85.%' IDENTIFIED BY 'replication'; GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.84.%' IDENTIFIED BY 'monitor';GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.85.%' IDENTIFIED BY 'agent';GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.85.%' IDENTIFIED BY 'replication'; 三、同步主从数据库 1. 从主数据库服务器导出当前数据库内容 [sql] view plaincopyprint?mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | mysql | +------------------+----------+--------------+------------------+ mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 106 | | mysql |+------------------+----------+--------------+------------------+ 注意保留上述信息，后面还会用到。另外，不要结束当前mysql控制台，重新打开一个窗口，导出数据库。 # mysqldump -uroot -proot --all-databases > db01_20111005.sql 释放锁[html] view plaincopyprint?mysql> UNLOCK TABLES; mysql> UNLOCK TABLES; 2. 将导出的sql文件导入到其他几台数据库服务器上。首先通过scp复制过去：[html] view plaincopyprint?# scp db01_20111005.sql [email protected]:/root/ # scp db01_20111005.sql [email protected]:/root/ 在其他几台服务其上导入改SQL文件：[html] view plaincopyprint?# mysql -uroot -proot db01_20111005.sql # mysql -uroot -proot 3. 启动从数据库SLAVE进程。[sql] view plaincopyprint?mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO master_host='192.168.85.167', master_port=3306, master_user='replication',master_password='replication', master_log_file='mysql-bin.000001', master_log_pos=106; Query OK, 0 rows affected (0.07 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.85.180 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 106 Relay_Log_File: mysql-bin.000003 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 106 Relay_Log_Space: 400 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) mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO master_host='192.168.85.167', master_port=3306, master_user='replication',master_password='replication', master_log_file='mysql-bin.000001', master_log_pos=106;Query OK, 0 rows affected (0.07 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.85.180 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 106 Relay_Log_File: mysql-bin.000003 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 106 Relay_Log_Space: 400 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: 0Master_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) 4. 将db02作为master，db01作为slave，重复1-3。四、安装MMM 在管理服务器和数据库服务器上分别要运行mysql-mmm monitor和agent程序。下面分别安装： 1. 安装监控程序在管理服务器（192.168.84.174）上，执行下面命令：[plain] view plaincopyprint?# yum -y install mysql-mmm-monitor* # yum -y install mysql-mmm-monitor* 与monitor依赖的所有文件也会随之安装，但是有一个例外perl-Time-HiRes，所以还需要执行下面的命令：[plain] view plaincopyprint?# yum -y install perl-Time-HiRes* # yum -y install perl-Time-HiRes* 2. 安装代理程序在数据库服务器上执行下面的命令：[plain] view plaincopyprint?# yum -y install mysql-mmm-agent* # yum -y install mysql-mmm-agent* 五、配置MMM 1. 编辑mmm_common.conf 完成安装后，所有的配置文件都放到了/etc/mysql-mmm/下面。管理服务器和数据库服务器上都要包含一个共同的文件mmm_common.conf，内容如下：[plain] view plaincopyprint?active_master_role writer 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 ip 192.168.85.167 mode master peer db2 ip 192.168.85.169 mode master peer db1 ip 192.168.85.171 mode slave ip 192.168.85.168 mode slave hosts db1, db2 ips 192.168.85.200 mode exclusive hosts db2, db3, db4 ips 192.168.85.201, 192.168.85.202, 192.168.85.203 mode balanced active_master_role writer 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 ip 192.168.85.167 mode master peer db2 ip 192.168.85.169 mode master peer db1 ip 192.168.85.171 mode slave ip 192.168.85.168 mode slave hosts db1, db2 ips 192.168.85.200 mode exclusive hosts db2, db3, db4 ips 192.168.85.201, 192.168.85.202, 192.168.85.203 mode balanced 可以在db1上编辑该文件后，通过scp命令分别复制到monitor、db2、db3和db4上。 2. 编辑mmm_agent.conf。在数据库服务器上，还有一个mmm_agent.conf需要修改，其内容是：[plain] view plaincopyprint?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 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，在不同的数据库服务器上要分别改为db2、db3和db4，否则代理就会无法启动。 3. 编辑mmm_mon.confg。在管理服务器上，修改mmm_mon.conf文件，修改后内容为：[plain] view plaincopyprint?include mmm_common.conf ip 192.168.84.174 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.85.167, 192.168.85.169, 192.168.85.171, 192.168.85.168 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 monitor debug 0 include mmm_common.conf ip 192.168.84.174 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.85.167, 192.168.85.169, 192.168.85.171, 192.168.85.168 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 monitordebug 0 六、启动MMM 1. 在数据库服务器上启动代理程序[plain] view plaincopyprint?# cd /etc/init.d/ # chkconfig mysql-mmm-agent on # service mysql-mmm-agent start # cd /etc/init.d/# chkconfig mysql-mmm-agent on# service mysql-mmm-agent start 2. 在管理服务器上启动监控程序[plain] view plaincopyprint?# cd /etc/init.d/ # chkconfig mysql-mmm-monitor on # service mysql-mmm-monitor start # cd /etc/init.d/# chkconfig mysql-mmm-monitor on# service mysql-mmm-monitor start 启动后，稍等几秒，可以通过mmm_control程序查看状态：[plain] view plaincopyprint?# mmm_control show db1(192.168.85.167) master/ONLINE. Roles: writer(192.168.85.200) db2(192.168.85.169) master/ONLINE. Roles: reader(192.168.85.202) db3(192.168.85.171) slave/ONLINE. Roles: reader(192.168.85.203) db4(192.168.85.168) slave/ONLINE. Roles: reader(192.168.85.201) # mmm_control show db1(192.168.85.167) master/ONLINE. Roles: writer(192.168.85.200) db2(192.168.85.169) master/ONLINE. Roles: reader(192.168.85.202) db3(192.168.85.171) slave/ONLINE. Roles: reader(192.168.85.203) db4(192.168.85.168) slave/ONLINE. Roles: reader(192.168.85.201) 七、遇到两个问题 1. 监控程序服务器无法启动在管理服务器上，一切都完成后，通过mmm_control查看状态，得到下面的错误信息：ERROR: Can't connect to monitor daemon! 通过编辑/etc/mysql-mmm/mmm_mon.conf文件将debug 0改为debug 1，打开监控程序的debug状态。重新启动监控程序（service mysql-mmm-monitor restart），就会看到详细的错误信息，找不到Perl Time HiRes库。执行yum -y install perl-Time-HiRes*就可以解决。 2. 防火墙问题导致Warning: agent on host db1 is not reachable. 控制台程序正确启动后，再次执行mmm_control show，却看到下面的输出：[plain] view plaincopyprint?# Warning: agent on host db1 is not reachable # Warning: agent on host db2 is not reachable # Warning: agent on host db3 is not reachable # Warning: agent on host db4 is not reachable db1(192.168.85.167) master/ONLINE. Roles: db2(192.168.85.169) master/ONLINE. Roles: db3(192.168.85.171) slave/ONLINE. Roles: db4(192.168.85.168) slave/ONLINE. Roles: # Warning: agent on host db1 is not reachable# Warning: agent on host db2 is not reachable# Warning: agent on host db3 is not reachable# Warning: agent on host db4 is not reachable db1(192.168.85.167) master/ONLINE. Roles: db2(192.168.85.169) master/ONLINE. Roles: db3(192.168.85.171) slave/ONLINE. Roles: db4(192.168.85.168) slave/ONLINE. Roles: 再次打开debug，发现了下面的错误信息：2011/10/07 13:38:45 DEBUG Sending command 'GET_AGENT_STATUS()' to db4 (192.168.85.167:9989)2011/10/07 13:38:45 ERROR The status of the agent on host 'db4' could not be determined (answer was: 0). 通过telnet 192.168.85.167 9989下面检查网络连接，得到了No route to host的错误信息。登录db1，通过setup程序里的Firewall configuration关闭Firewall（这不是一个好主意）。同样，关闭db2、db3和db4上的防火墙，再次重启监控程序，一切回到正常状态！参考文章： MySQL MMM 官方安装文档 http://mysql-mmm.org/mmm2:guide MMM Manual http://mysql-mmm.org/mysql-mmm.html

璇撰沐玛

从零开始配置MySQL MMM .