Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1744082
  • 博文数量: 117
  • 博客积分: 2559
  • 博客等级: 少校
  • 技术积分: 4385
  • 用 户 组: 普通用户
  • 注册时间: 2010-08-13 20:08
个人简介

作为初学者,要想取得进步,成为高手,首先应该了解自己的不足之处.

文章分类

全部博文(117)

文章存档

2014年(1)

2013年(25)

2012年(13)

2011年(77)

2010年(1)

分类: Mysql/postgreSQL

2011-07-01 14:52:51

                                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上进行回复,master1master2互为主从,在master2上执行change master to的时候,偏移量要指定为mysqldump的便宜量,让master2开始从master1同步数据,当同步完成后,利用mmm直接切换到master2

在把master1的数据库升级完成之后,导入数据并配置成master2slave

 

迁移步骤:

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架构

VIPwriter192.168.250.100

VIPreader192.168.250.101

master1上创建同步账户

配置master2master1slave

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

 

配置多个读(readerVIP,可以实现读的负载均衡。

 

CMake下载安装

wget

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

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(可选),MySQLmy.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文件,以master1my.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';

 

创建复制账户,两个agentMySQL 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的数据一致

 

虚拟机环境下的master1master2两个MySQL都是新装的,故数据是一样的。

【可选】执行下面命令,删除位于索引文件中的所有二进制日志,把二进制日志索引文件从新设置为空,并创建一个新的二进制日志文件

mysql> reset master;

 

master1master2的复制配置,标记红色的部分,根据status得到信息进行更改。

括号里面的是指在哪台服务器进行操作。

配置master2master1slave

(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

 

配置master1master2slave

(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

rpm -ivh epel-release-5-4.noarch.rpm

 

2.4     Databases host

shell> yum install mysql-mmm-agent

会自动安装依赖包

 

修改配置文件

master1为例

mysql-mmm2.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的意思是等同,表示db1db2是同等的。

ips指定VIP

mode exclusive 只有两种模式:exclusive是排他,在这种模式下任何时候只能一个host拥有该角色

balanced模式下可以多个host同时拥有此角色。一般writerexclusivereaderbalanced

可以在exclusive 中设置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

 

    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_user        mmm_monitor

    monitor_password    password

 

debug 0

 

 

设置ping_ips,用于检测,分别ping网关、master1master2

 

 

设置日志格式(暂未研究)/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 [|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

                                        (Only use --force if you know what you are doing!)

    set_ip                 - set role with ip to 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] - move exclusive role to host

(Only use --force if you know what you are doing!)

4   HA测试 4.1     模拟master1MySQL挂掉

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)

 

查看状态,readerwriter都在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: 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: 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)

阅读(4082) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~