读写分离的三种方法

1. IP connectivity (fping)

2. MySQL connectivity (mysql connect and SELECT NOW()) 3. MySQL IO and SQL threads status (SHOW SLAVE STATUS)

4. MySQL replication backlog – seconds behind master (SHOW SLAVE STATUS) 四. MMM准备工作: 主机名 db1 db2 mon IP 192.168.1.167 192.168.1.168 192.168.1.164 Port 3306 3306 3306 App mysql mysql Mysql Mysql目录 /usr/local/mysql /usr/local/mysql /usr/local/mysql Mmm目录 /usr/local/mmm /usr/local/mmm /usr/local/mmm 虚拟IP地址:：192.168.1.173(reader) 192.168.1.174(reader) 192.168.1.175(writer) MySQL帐号:

监听帐号(即MON使用帐号)

GRANT ALL PRIVILEGES on *.* to ‘rep_monitor’@’192.168.1.164′ identified by ‘RepMonitor’; 代理帐号(即db之间MMM帐号)

GRANT ALL PRIVILEGES on *.* to ‘rep_agent’@’192.168.1.167′ identified by ‘RepAgent’; 复制帐号(用于master-master )

GRANT replication slave on *.* to ‘master’@'%’ identified by ‘master’; 数据库访问帐号

GRANT ALL PRIVILEGES on *.* to ‘martin’@'%’ identified by ‘martin’; 五.配置步骤():

1. DB1 Master配置(MySQL安装省略):

shell > vi /usr/local/mysql/my.cnf # 添加如下内容:

#——————Master-Master config—————– replicate-same-server-id server-id=1 # Server ID

log-bin = /usr/local/mysql/data/mysql-bin.log binlog-ignore-db=mysql # No sync databases binlog-ignore-db=test # No sync databases #binlog-do-db=test # sync databases

#————————————————————

shell > /usr/local/mysql/bin/mysqld_safe –user=mysql –skip-slave-start &

查看db2二进制文件和偏移量

mysql > show master status; # 此命令在db2 服务器上执行; 继续返回db1

mysql > change master to -> master_host=”192.168.1.168″,

-> master_user=”master”, -> master_password=”master”,

-> master_log_file=”mysql-bin.000001″, -> master_log_pos=106;

mysql > start slave; # 启动slave 进程 2. DB2 Master 配置(MySQL安装省略) shell > vi /usr/local/mysql/my.cnf # 添加如下内容:

#——————Master-Master config—————– replicate-same-server-id server-id=2 # Server ID

log-bin = /usr/local/mysql/data/mysql-bin.log binlog-ignore-db=mysql # No sync databases binlog-ignore-db=test # No sync databases #binlog-do-db=test # sync databases

#————————————————————

shell > /usr/local/mysql/bin/mysqld_safe –user=mysql –skip-slave-start &

查看db2二进制文件和偏移量

mysql > show master status; # 此命令在db1 服务器上执行; 继续返回db1

mysql > change master to -> master_host=”192.168.1.167″, -> master_user=”master”, -> master_password=”master”,

-> master_log_file=”mysql-bin.000001″, -> master_log_pos=106;

mysql > start slave; # 启动slave 进程 3. 检查Master-Master是否正常 shell > show slave status \\G …

Slave_IO_Running: Yes Slave_SQL_Running: Yes …

5. DB1 MMM配置:

shell > ln -s /usr/local/mysql/bin/mysql_config /sbin/mysql_config #由于编译安装需要执行此命令 shell > ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.16 /usr/lib/ #由于编译安装需要执行此命令

shell > cpan Algorithm::Diff Proc::Daemon Time::HiRes DBI DBD::mysql shell > yum -y install iproute #如果已经安装 会提示已经安装

shell > wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2 shell > tar xvf mmm-1.0.tar.bz2 shell > cd mmm-1.0

shell > perl install.pl.

shell > cp /usr/local/mmm/etc/examples/mmm_agent.conf.example

/usr/local/mmm/etc/mmm_agent.conf

shell > vi /usr/local/mmm/etc/mmm_agent.conf

#———————————-以下是我mmm_agent.conf———————————-

#

# Master-Master Manager config (agent) #

# Debug mode debug no # Paths

pid_path /usr/local/mmm/var/mmmd_agent.pid bin_path /usr/local/mmm/bin # Logging setup log mydebug

file /usr/local/mmm/var/mmm-debug.log level debug log mytraps

file /usr/local/mmm/var/mmm-traps.log level trap

# MMMD command socket tcp-port and ip bind_port 9989 # Cluster interface cluster_interface eth0 # Define current server id this db1 mode master # For masters peer db2

# Cluster hosts addresses and access params host db1 ip 192.168.1.167 port 3306 user rep_agent password RepAgent host db2 ip 192.168.1.168 port 3306 user rep_agent password RepAgent

#———————————-以上是我mmm_agent.conf———————————-

shell > /usr/local/mmm/scripts/init.d/mmm_agent start # 启动 mmm_agent shell > lsof -i:9989

COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME perl 2466 root 3u IPv4 7740 TCP *:9989 (LISTEN) 4. DB2 MMM配置:

shell > ln -s /usr/local/mysql/bin/mysql_config /sbin/mysql_config

shell > ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.16 /usr/lib/

shell > cpan Algorithm::Diff Proc::Daemon Time::HiRes DBI DBD::mysql shell > yum -y install iproute #如果已经安装 会提示已经安装

shell > wget http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2 shell > tar xvf mmm-1.0.tar.bz2 shell > cd mmm-1.0 shell > perl install.pl

shell > cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf

shell > vi /usr/local/mmm/etc/mmm_agent.conf

#———————————-以下是我mmm_agent.conf———————————- #

# Master-Master Manager config (agent) #

# Debug mode debug no # Paths

pid_path /usr/local/mmm/var/mmmd_agent.pid bin_path /usr/local/mmm/bin # Logging setup log mydebug

file /usr/local/mmm/var/mmm-debug.log level debug log mytraps

file /usr/local/mmm/var/mmm-traps.log level trap

# MMMD command socket tcp-port and ip bind_port 9989 # Cluster interface cluster_interface eth0 # Define current server id this db2 mode master # For masters peer db1

# Cluster hosts addresses and access params host db1 ip 192.168.1.167 port 3306 user rep_agent password RepAgent host db2 ip 192.168.1.168 port 3306