![ning_lianjie]( "ning_lianjie")
MySQL升级+MMM部署文档 ————利用MySQL Replication复制进行数据迁移说明：1. 三台虚拟机，两台MySQL agent和一台Monitor2. IP地址，MySQL 192.168.250.251|252Monitor 192.168.250.2533. 如果在虚拟机上测试成功，再根据实际环境更改。1 需求分析1.1 信息收集服务器 主机名 操作系统（X64） IP CPU 内存 磁盘 master1 CentOS 5.6 192.168.250.251 master2 CentOS 5.6 192.168.250.252 monitor253 CentOS 5.6 192.168.250.253 MySQL 主机名 版本（Percona） 安装路径 状态 备注 master1 Percona-Server-5.5.12-rel20.3.tar.gz /usr/local/mysql master2 Percona-Server-5.5.12-rel20.3.tar.gz /usr/local/mysql 1.2 数据迁移（独立）基础知识：物理环境下，master1上面有数据，停机后，需要更新MySQL版本，并导入到master2中，做MMM。如果需要导入mysql库，注意刷新权限mysql> FLUSH PRIVILEGES;参考命令：锁表 mysql> FLUSHTABLES WITH READ LOCK;解锁 mysql>UNLOCK TABLES; 迁移方案：环境：在master1上有在线的服务，要求避免停机，或者停机时间尽量缩短。思路：利用master1上的mysqldump出来的全库备份，在master2上进行回复，master1与master2互为主从，在master2上执行changemaster to的时候，偏移量要指定为mysqldump的便宜量，让master2开始从master1同步数据，当同步完成后，利用mmm直接切换到master2。在把master1的数据库升级完成之后，导入数据并配置成master2的slave 迁移步骤：1. 环境说明：master1是在线业务（模拟），IP地址192.168.250.251master2上面装有MySQL 5.5,IP地址192.168.250.2522. 创建一个不断写入数据的脚本创建测试用户GRANT ALL PRIVILEGES ON *.* TO 'test01'@'192.168.250.%'IDENTIFIED BY 'test01' WITH GRANT OPTION;FLUSH PRIVILEGES;脚本内容#!/bin/bashmysql -utest01 -ptest01 -h192.168.250.251 -e"DROP TABLE IF EXISTS test.test01;"mysql -utest01 -ptest01 -h192.168.250.251 -e"CREATE TABLE test.test01 ( a int(11) NOT NULL auto_increment, b datetimedefault NULL, PRIMARY KEY (a))AUTO_INCREMENT=1;"while true;domysql -utest01 -ptest01 -h192.168.250.251 -e"insert into test.test01 (b) values(now());"sleep 1 ;done; 3. 计划做成MMM架构VIP（writer）192.168.250.100VIP（reader）192.168.250.101在master1上创建同步账户配置master2为master1的slave在master2上导入master1的数据，并记录偏移量，从偏移量出开始复制。(master1) mysqldump -uroot-p'bhaWka88Itzkqvm&' -x -R --triggers --master-data=2 –databases mysql test>master1.sql指定多个库备份，并查看CHANGEMASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=;对应的值参考命令 head -n 30 master1.sqlCHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=698669; (master1) rsync -av master1.sql192.168.250.252:/opt传到master2上，导入(master2) mysql(master2) mysql> CHANGE MASTER TOmaster_host='192.168.250.251', master_port=3306, master_user='replication', master_password='password',master_log_file='mysql-bin.000002', master_log_pos=698669;(master2) mysql> START SLAVE;2 部署过程2.1 编译安装MySQL 5.5MMM架构，至少需要两台MySQL Server和一台MonitorServerMySQL服务器 主机名 IP server-id 用途 master1 192.168.250.251 1 master1 master2 192.168.250.252 2 master2 monitor253 192.168.250.253 无 monitor 虚IP分配 VIP 角色（role） 说明 192.168.250.100 写（write） 应用连接此IP用于写入 192.168.250.101 读（reader） 应用连接此IP用于读取 读（reader） 配置多个读（reader）VIP，可以实现读的负载均衡。 CMake下载安装wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gztar -xzvf cmake-2.8.4.tar.gzcd cmake-2.8.4./configuremake &&make install MySQL下载安装软件包：Percona-Server-5.5.12-rel20.3.tar.gzwget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.12-20.3/source/Percona-Server-5.5.12-rel20.3.tar.gztar zxvfPercona-Server-5.5.12-rel20.3.tar.gzcd Percona-Server-5.5.12-rel20.3cmake .-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\ -DMYSQL_DATADIR=/usr/local/mysql/data\ -DINSTALL_LAYOUT=STANDALONE\ -DDEFAULT_CHARSET=utf8\ -DDEFAULT_COLLATION=utf8_general_ci\ -DEXTRA_CHARSETS=all\ -DWITH_INNOBASE_STORAGE_ENGINE=1\ -DWITH_READLINE=1\ -DENABLED_LOCAL_INFILE=1\ -DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock\ -DMYSQL_TCP_PORT=3306\ -DWITH_DEBUG=0make && make installcd /usr/local/mysqlgroupadd mysqluseradd -r -g mysql mysqlchown -R mysql .chgrp -R mysql .scripts/mysql_install_db --user=mysqlchown -R root .chown -R mysql datacp support-files/my-medium.cnf /etc/my.cnf（可选），MySQL的my.cnf配置文件启动MySQLbin/mysqld_safe --user=mysql &cp support-files/mysql.server /etc/init.d/mysql.server编辑mysql.server，指定下面两个目录basedir=/usr/local/mysqldatadir=/usr/local/mysql/datacp /usr/local/mysql/bin/mysql/usr/bin/mysql 复制客户端工具编译参数解释： -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 指定安装路径 -DMYSQL_DATADIR=/usr/local/mysql/data 指定数据存放目录 -DINSTALL_LAYOUT=STANDALONE 指定安装布局，tar.gz包安装，默认为STANDALONE -DDEFAULT_CHARSET=utf8 指定默认字符集 -DDEFAULT_COLLATION=utf8_general_ci 指定字符集编码 -DEXTRA_CHARSETS=all 全字符集支持 -DWITH_INNOBASE_STORAGE_ENGINE=1 安装InnoDB引擎（The MyISAM, MERGE, MEMORY, and CSV engines are mandatory (alwayscompiled into the server) and need not be installed explicitly.） -DWITH_READLINE=1 使用readline库，与捆绑分布。Whether to use the readline library bundled with the distribution. -DENABLED_LOCAL_INFILE=1 允许从文件中加载数据 -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock 指定socket路径，默认在/tmp/mysql.socket -DMYSQL_TCP_PORT=3306 -DWITH_DEBUG=0 关闭Debug 编辑my.cnf文件，以master1的my.cnf为例说明（个人认为，参数中”_”和”-”的区别，在于有没有赋值。）server_id = 1binlog_format = mixedlog_bin = mysql-binlog_bin_index = mysql-bin.indexrelay_log = mysql-relay-binrelay_log_index = mysql-relay-bin.indexexpire_logs_days = 10max_binlog_size = 100Mlog_slave_updates = 1skip-slave-startbind_address = 0.0.0.0参数解释：server_idbinlog_format 设置二进制日志格式，有三种，基于SQL语句的，基于行的还有混合的log_bin 打开二进制日志并指定名称log_bin_index 指定二进制索引文件的名称relay_log 指定relay_log名称relay_log_index 指定relay_log_index名称expire_logs_days 自动删除10天之前的max_binlog_size 每个二进制日志的文件大小为100M，超过100M就切换一个新的。log_slave_updates 在执行threa_sql线程的同时，将记录写到自己二进制日志中。skip-slave-start 开启MySQL时，不自动执行startslavebind-address的设置是指在本机的哪一个ip上监听bind-address = 127.0.0.1#只允许本机访问bind-address = 0.0.0.0#允许任何主机访问 启动mysql/etc/init.d/mysql.server start进入mysql，增加用户安全性，给root用户添加密码（mkpasswd工具生成，需要安装expect），删除空用户。mysql> select user,host,password frommysql.user;+------+-----------+----------+| user | host | password |+------+-----------+----------+| root | localhost | || root | master1 | || root | 127.0.0.1 | || root | ::1 | || | localhost | || | master1 | |+------+-----------+----------+6 rows in set (0.02 sec) GRANT ALL PRIVILEGES ON *.* TO'root'@'localhost' IDENTIFIED BY 'bhaWka88Itzkqvm&' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON *.* TO 'root'@'master1'IDENTIFIED BY 'bhaWka88Itzkqvm&' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'IDENTIFIED BY 'bhaWka88Itzkqvm&' WITH GRANT OPTION;DROP USER 'root'@'::1';DROP USER ''@'localhost';DROP USER ''@'master1'; 创建复制账户，两个agent（MySQL Server）都要创建 用途 说明 所需权限 monitor user monitor主机用来检查agent的健康状态 REPLICATION CLIENT agent user agent主机改变模式的 SUPER, REPLICATION CLIENT, PROCESS relication user 用来复制 REPLICATION SLAVE 参考命令：（数字和小写的英文字母根据实际情况更改）GRANT REPLICATION CLIENT ON *.* TO'mmm_monitor'@'192.168.250.%' IDENTIFIED BY 'password';GRANT SUPER, REPLICATION CLIENT, PROCESS ON*.* TO 'mmm_agent'@'192.168.250.%' IDENTIFIED BY 'password';GRANT REPLICATION SLAVE ON *.* TO'replication'@'192.168.250.%' IDENTIFIED BY 'password';FLUSH PRIVILEGES; 执行完成后，结果如下mysql> select user,host,password frommysql.user;+-------------+---------------+-------------------------------------------+| user | host | password |+-------------+---------------+-------------------------------------------+| root | localhost |*F7E13D1EE7328BEDCD4B8244640D9C1F4BEE7FCC || root | master1 |*F7E13D1EE7328BEDCD4B8244640D9C1F4BEE7FCC || root | 127.0.0.1 |*F7E13D1EE7328BEDCD4B8244640D9C1F4BEE7FCC || mmm_monitor | 192.168.250.% | *D196693C1DB5E4018CEE437337303EC10DAF403C|| mmm_agent | 192.168.250.% |*D196693C1DB5E4018CEE437337303EC10DAF403C || replication | 192.168.250.% |*D196693C1DB5E4018CEE437337303EC10DAF403C |+-------------+---------------+-------------------------------------------+6 rows in set (0.00 sec) 根据流程，再在master2上做同样的操作。需要注意几个地方 my.cnf要注意修改server_idserver_id = 2binlog_format = mixedlog_bin = mysql-binlog_bin_index = mysql-bin.indexrelay_log = mysql-relay-binrelay_log_index = mysql-relay-bin.indexexpire_logs_days = 10max_binlog_size = 100Mlog_slave_updates = 1skip-slave-startbind_address = 0.0.0.0 删除空用户mysql> DROP USER ''@'master2';2.2 MySQL 复制注意：要保证两台MySQL的数据一致 虚拟机环境下的master1和master2两个MySQL都是新装的，故数据是一样的。【可选】执行下面命令，删除位于索引文件中的所有二进制日志，把二进制日志索引文件从新设置为空，并创建一个新的二进制日志文件mysql> reset master; master1与master2的复制配置，标记红色的部分，根据status得到信息进行更改。括号里面的是指在哪台服务器进行操作。配置master2为master1的slave(master1) mysql> SHOW MASTER STATUS\G(master2) mysql> CHANGE MASTER TOmaster_host='192.168.250.251', master_port=3306, master_user='replication', master_password='password',master_log_file='mysql-bin.000001', master_log_pos=107;(master2) mysql> START SLAVE;(master2) mysql> SHOW SLAVE STATUS\G查看，确认IO线程和SQL线程为YESSlave_IO_Running: YesSlave_SQL_Running: Yes 配置master1为master2的slave(master2) mysql> SHOW MASTER STATUS\G(master1) mysql> CHANGE MASTER TOmaster_host='192.168.250.252', master_port=3306, master_user='replication', master_password='password',master_log_file='mysql-bin.000001', master_log_pos=182;(master1) mysql> START SLAVE;(master1) mysql> SHOW SLAVE STATUS\G 2.3 MMM安装因操作系统是CentOS，可以配置yum源，直接yum安装，在三台主机上都要安装yum源。yum源下载安装wget http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpmrpm -ivh epel-release-5-4.noarch.rpm 2.4 Databases hostshell> yum install mysql-mmm-agent会自动安装依赖包 修改配置文件以master1为例在mysql-mmm的2.2.1-1.el5版本中，所有的配置选项集中在/etc/mysql-mmm/mmm_common.conf，这个配置文件修改完成后，要同步到三台服务器上。/etc/mysql-mmm/mmm_common.conf配置文件，标记红色的部分，需要根据实际情况修改。 active_master_role writer cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user replicantion replication_password password agent_user mmm_agent agent_password password ip 192.168.250.251 mode master peer db2 ip 192.168.250.252 mode master peer db1 hosts db1, db2 ips 192.168.250.100 mode exclusive hosts db1, db2 ips 192.168.250.101 mode balanced 注意：peer的意思是等同，表示db1与db2是同等的。ips指定VIPmode exclusive 只有两种模式：exclusive是排他，在这种模式下任何时候只能一个host拥有该角色balanced模式下可以多个host同时拥有此角色。一般writer是exclusive，reader是balanced可以在exclusive 的中设置prefer=db1，这样在db6恢复正常之后，就可以再次被切换为写库了。 还需要查看/etc/mysql-mmm/mmm_agent.conf文件，this db1根据mmm_common.conf的配置修改,db1为配置文件指定的名称 include mmm_common.confthis db1 开启mmm-agent/etc/init.d/mysql-mmm-agent start监听端口9989netstat -tunlp……192.168.250.251:9989 0.0.0.0:* LISTEN 11516/mmm_agentd 在master2上配置首先下载yum源，安装mysql-mmm-agentyum install mysql-mmm-agent把master1上面的/etc/mysql-mmm/mmm_common.conf文件直接拉过来就可以了(master2) rsync -av192.168.250.251:/etc/mysql-mmm/mmm_common.conf /etc/mysql-mmm/……yes输入master1的密码 agent配置文件，要注意修改/etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db2 开启agent服务/etc/init.d/mysql-mmm-agent start2.5 Monitoring host安装上面的yum源之后，输入下面的一条命令即可搞定所有依赖包shell> yum install mysql-mmm-monitor 把master1上面的/etc/mysql-mmm/mmm_common.conf文件直接拉过来(monitor253) rsync -av192.168.250.251:/etc/mysql-mmm/mmm_common.conf /etc/mysql-mmm/ [root@monitor253 opt]# vim/etc/mysql-mmm/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.250.2, 192.168.250.251, 192.168.250.252 auto_set_online 60 #The kill_host_bin does not exist by default, though the monitor will #throw a warning about it missing. Seethe 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 password debug 0 设置ping_ips，用于检测，分别ping网关、master1、master2 设置日志格式（暂未研究）/etc/mysql-mmm/mmm_mon_log.conf查看日志tail -f /var/log/mysql-mmm/mmm_mond.log 开启监控服务/etc/init.d/mysql-mmm-monitor start 如果没有可以成功启动，并且日志没有报错，可以进行下面的命令测试。3 MMM控制命令# mmm_control helpValid commands are: help - show this message ping - ping monitor show - show status checks [|all [|all]] - show checks status set_online - set host online set_offline - set host offline mode - print current mode. set_active - switch into active mode. set_manual - switch into manual mode. set_passive - switch into passive mode. move_role [--force] - move exclusive role to host (Onlyuse --force if you know what you are doing!) set_ip - set role with ipto host 测试ping# mmm_control pingOK: Pinged successfully! 查看状态# mmm_control show 检查状态# mmm_control checks 设置上线# mmm_control set_online db2 查看模式# mmm_control modeACTIVE设置模式set_active - switch into activemode.set_manual - switch into manualmode.set_passive - switch into passivemode. 改变角色(未测试)move_role [--force] - move exclusive role to host(Only use --force if you know what you aredoing!)4 HA测试4.1 模拟master1的MySQL挂掉ps -ef |grep mysqlkill -9 pid monitor253的日志信息2011/06/29 17:59:16 FATAL State of host'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK) 查看状态，reader和writer都在db2上面了。mmm_control show db1(192.168.250.251) master/HARD_OFFLINE. Roles: db2(192.168.250.252) master/ONLINE. Roles: reader(192.168.250.101),writer(192.168.250.100) 在master2上查看IP地址ip addr show1: lo: mtu16436 qdisc noqueue link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo2: eth0: mtu 1500 qdisc pfifo_fast qlen 1000 link/ether 00:0c:29:a3:42:fd brd ff:ff:ff:ff:ff:ff inet 192.168.250.252/24 brd 192.168.250.255 scope global eth0 inet 192.168.250.101/32 scope global eth0inet192.168.250.100/32 scope global eth0 4.2 master1恢复后的状态monitor253的日志2011/06/29 18:04:17 FATAL State of host'db1' changed from HARD_OFFLINE to AWAITING_RECOVERY 查看状态mmm_control show db1(192.168.250.251) master/AWAITING_RECOVERY. Roles: db2(192.168.250.252) master/ONLINE. Roles: reader(192.168.250.101), writer(192.168.250.100) 由HARD_OFFLINE变为AWAITING_RECOVERY状态需要手动设置on_line# mmm_control set_online db1 OK: State of 'db1' changed to ONLINE. Nowyou can wait some time and check its new roles! 日志消息2011/06/29 18:06:18 FATAL Admin changedstate of 'db1' from AWAITING_RECOVERY to ONLINE2011/06/29 18:06:22 FATAL State of host'db1' changed from ONLINE to REPLICATION_FAIL 查看状态mmm_control show db1(192.168.250.251) master/REPLICATION_FAIL.Roles: db2(192.168.250.252) master/ONLINE. Roles: reader(192.168.250.101),writer(192.168.250.100) 造成这样的结果是因为MySQL在启动后，没有自动开启复制（START SLAVE），因为设置了skip-slave-start参数进入master1，执行下面的命令mysql> START SLAVE 在monitor253上查看状态mmm_control show db1(192.168.250.251) master/ONLINE. Roles: reader(192.168.250.101) db2(192.168.250.252) master/ONLINE. Roles: writer(192.168.250.100) 02-01 08:27