MySQL升级+MMM部署文档
————利用MySQL Replication复制进行数据迁移
说明:
1.
三台虚拟机,两台MySQL agent和一台Monitor
2.
IP地址,MySQL 192.168.250.251|252
Monitor 192.168.250.253
3.
如果在虚拟机上测试成功,再根据实际环境更改。
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> FLUSH
TABLES WITH READ LOCK;
解锁 mysql>
UNLOCK TABLES;
迁移方案:
环境:在master1上有在线的服务,要求避免停机,或者停机时间尽量缩短。
思路:利用master1上的mysqldump出来的全库备份,在master2上进行回复,master1与master2互为主从,在master2上执行change
master to的时候,偏移量要指定为mysqldump的便宜量,让master2开始从master1同步数据,当同步完成后,利用mmm直接切换到master2。
在把master1的数据库升级完成之后,导入数据并配置成master2的slave
迁移步骤:
1.
环境说明:
master1是在线业务(模拟),IP地址192.168.250.251
master2上面装有MySQL 5.5,IP地址192.168.250.252
2.
创建一个不断写入数据的脚本
创建测试用户
GRANT ALL PRIVILEGES ON *.* TO 'test01'@'192.168.250.%'
IDENTIFIED BY 'test01' WITH GRANT OPTION;
FLUSH PRIVILEGES;
脚本内容
#!/bin/bash
mysql -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 datetime
default NULL, PRIMARY KEY (a))
AUTO_INCREMENT=1;"
while true;
do
mysql -utest01 -ptest01 -h192.168.250.251 -e
"insert into test.test01 (b) values(now());"
sleep 1 ;
done;
3.
计划做成MMM架构
VIP(writer)192.168.250.100
VIP(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
指定多个库备份,并查看CHANGE
MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=;对应的值
参考命令 head -n 30 master1.sql
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=698669;
(master1) rsync -av master1.sql
192.168.250.252:/opt
传到master2上,导入
(master2) mysql < ...
(master2) mysql> CHANGE MASTER TO
master_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.5
MMM架构,至少需要两台MySQL Server和一台Monitor
Server
MySQL服务器
主机名
|
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.gz
tar -xzvf cmake-2.8.4.tar.gz
cd cmake-2.8.4
./configure
make &&make install
MySQL下载安装
软件包:Percona-Server-5.5.12-rel20.3.tar.gz
wget 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.gz
tar zxvf
Percona-Server-5.5.12-rel20.3.tar.gz
cd Percona-Server-5.5.12-rel20.3
cmake .
-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=0
make && make install
cd /usr/local/mysql
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data
cp support-files/my-medium.cnf /etc/my.cnf(可选),MySQL的my.cnf配置文件
启动MySQL
bin/mysqld_safe --user=mysql &
cp support-files/mysql.server /etc/init.d/mysql.server
编辑mysql.server,指定下面两个目录
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
cp /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 (always
compiled 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 = 1
binlog_format = mixed
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
skip-slave-start
bind_address = 0.0.0.0
参数解释:
server_id
binlog_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时,不自动执行start
slave
bind-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 from
mysql.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 from
mysql.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_id
server_id = 2
binlog_format = mixed
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
skip-slave-start
bind_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 TO
master_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线程为YES
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置master1为master2的slave
(master2) mysql> SHOW MASTER STATUS\G
(master1) mysql> CHANGE MASTER TO
master_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.rpm
rpm -ivh epel-release-5-4.noarch.rpm
2.4
Databases host
shell> 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
<host default>
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
</host>
<host db1>
ip 192.168.250.251
mode master
peer db2
</host>
<host db2>
ip 192.168.250.252
mode master
peer db1
</host>
<role writer>
hosts db1, db2
ips 192.168.250.100
mode exclusive
</role>
<role reader>
hosts db1, db2
ips 192.168.250.101
mode balanced
</role>
注意:
peer的意思是等同,表示db1与db2是同等的。
ips指定VIP
mode exclusive 只有两种模式:exclusive是排他,在这种模式下任何时候只能一个host拥有该角色
balanced模式下可以多个host同时拥有此角色。一般writer是exclusive,reader是balanced
可以在exclusive 的<role writer>中设置prefer=db1,这样在db6恢复正常之后,就可以再次被切换为写库了。
还需要查看/etc/mysql-mmm/mmm_agent.conf
文件,this db1根据mmm_common.conf的配置修改,db1为配置文件指定的名称
include mmm_common.conf
this db1
开启mmm-agent
/etc/init.d/mysql-mmm-agent start
监听端口9989
netstat -tunlp
……
192.168.250.251:9989 0.0.0.0:* LISTEN 11516/mmm_agentd
在master2上配置
首先下载yum源,安装mysql-mmm-agent
yum install mysql-mmm-agent
把master1上面的/etc/mysql-mmm/mmm_common.conf文件直接拉过来就可以了
(master2) rsync -av
192.168.250.251:/etc/mysql-mmm/mmm_common.conf /etc/mysql-mmm/
……yes
输入master1的密码
agent配置文件,要注意修改
/etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db2
开启agent服务
/etc/init.d/mysql-mmm-agent start
2.5
Monitoring host
安装上面的yum源之后,输入下面的一条命令即可搞定所有依赖包
shell> yum install mysql-mmm-monitor
把master1上面的/etc/mysql-mmm/mmm_common.conf文件直接拉过来
(monitor253) rsync -av
192.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
<monitor>
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. See
the section 5.10 "Kill Host
#
Functionality" in the PDF documentation.
#
#
kill_host_bin
/usr/libexec/mysql-mmm/monitor/kill_host
#
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password password
</host>
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 help
Valid commands are:
help
- show this message
ping
- ping monitor
show
- show status
checks [<host>|all [<check>|all]] - show checks status
set_online <host>
- set host <host> online
set_offline <host>
- set host <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] <role> <host> - move exclusive role
<role> to host <host>
(Only
use --force if you know what you are doing!)
set_ip <ip> <host> - set role with ip <ip>
to host <host>
测试ping
# mmm_control ping
OK: Pinged successfully!
查看状态
# mmm_control show
检查状态
# mmm_control checks
设置上线
# mmm_control set_online db2
查看模式
# mmm_control mode
ACTIVE
设置模式
set_active - switch into active
mode.
set_manual - switch into manual
mode.
set_passive - switch into passive
mode.
改变角色(未测试)
move_role [--force] <role>
<host> - move exclusive role <role> to host <host>
(Only use --force if you know what you are
doing!)
4 HA测试
4.1
模拟master1的MySQL挂掉
ps -ef |grep mysql
kill -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 show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu
16436 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 lo
2: eth0:
<BROADCAST,MULTICAST,UP,LOWER_UP> 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 eth0
inet
192.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. Now
you can wait some time and check its new roles!
日志消息
2011/06/29 18:06:18 FATAL Admin changed
state of 'db1' from AWAITING_RECOVERY to ONLINE
2011/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) |