MySQL两主多从，且故障转移配置

一、角色划分

1、MySQL数据库规划
主机名 IP地址 角色 mysql_server_id
weiliaodb1 192.168.1.233 master1 1
weiliaodb2 192.168.1.234 master2 2
weiliaodb3 192.168.1.235 slave1 3
monitor 192.168.1.240 mmm_mon -

2、虚拟IP规划
IP地址 角色
192.168.1.10 writer
192.168.1.20 reader
192.168.1.30 reader
192.168.1.40 reader

二、MySQL数据同步设定

1、数据导入导出
在 192.168.1.233:
# /usr/local/mysql/bin/mysqldump -uroot -pxinhuo123 --database openfire > /data/openfire.sql

在 192.168.1.234、192.168.1.235:
# mysql -uroot -pxinhuo123 -e "create database openfire default charset utf8 COLLATE utf8_general_ci;"
# mysql -uroot -pxinhuo123 openfire < /data/openfire.sql

2、数据同步帐号添加
在 192.168.1.233:
mysql> grant replication slave on *.* to 'slave'@'192.168.1.234' identified by '123456';
mysql> grant replication slave on *.* to 'slave'@'192.168.1.235' identified by '123456';
mysql> flush privileges;

在 192.168.1.234:
mysql> grant replication slave on *.* to 'slave'@'192.168.1.233' identified by '123456';
mysql> grant replication slave on *.* to 'slave'@'192.168.1.235' identified by '123456';
mysql> flush privileges;

3、数据库配置文件修改
在 192.168.1.233 的 /etc/my.cnf 添加:
log-bin=mysql-bin
server-id = 1
binlog-do-db=openfire
binlog-ignore-db=mysql,information_schema
replicate-do-db=openfire
replicate-ignore-db=mysql,information_schema
log-slave-updates
slave-skip-errors=all
sync_binlog=1
#replicate-same-server-id #主要用于同时写的情况
#auto_increment_increment=2
#auto_increment_offset=1
read_only = 0

在 192.168.1.234 的 /etc/my.cnf 添加:
log-bin=mysql-bin
server-id = 2
binlog-do-db=openfire
binlog-ignore-db=mysql,information_schema
replicate-do-db=openfire
replicate-ignore-db=mysql,information_schema
log-slave-updates
slave-skip-errors=all
sync_binlog=1
#replicate-same-server-id #主要用于同时写的情况
#auto_increment_increment=2
#auto_increment_offset=2
read_only = 0

在 192.168.1.235 的 /etc/my.cnf 添加:
log-bin=mysql-bin
server-id = 3
log-slave-updates
binlog-do-db=openfire
binlog-ignore-db=mysql,information_schema
replicate-do-db=openfire
replicate-ignore-db=mysql,information_schema
read_only = 1

在 192.168.1.240 的 /etc/my.cnf 添加:
log-bin=mysql-bin
server-id = 4
log-slave-updates
binlog-do-db=openfire
binlog-ignore-db=mysql,information_schema
replicate-do-db=openfire
replicate-ignore-db=mysql,information_schema

重启4台DB:
# service mysqld restart

4、同步设置
在 192.168.1.233、192.168.1.234:
mysql> flush tables with read lock;
mysql> show master status\G;
记录下各自的 master_log_file、master_log_pos值

在 192.168.1.233:
mysql> change master to
-> master_host='192.168.1.234',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='binlog.000052',
-> master_log_pos=500;

在 192.168.1.234:
mysql> change master to
-> master_host='192.168.1.233',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='binlog.000022',
-> master_log_pos=1354;

在 192.168.1.235:
mysql> change master to
-> master_host='192.168.1.234',
-> master_user='slave',
-> master_password='123456',
-> master_log_file='binlog.000052',
-> master_log_pos=500;

在 192.168.1.233、192.168.1.234、192.168.1.235:
mysql> start slave;
mysql> show slave status\G;

确保有如下值：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在 192.168.1.233，192.168.1.234:
unlock tables;

三、mysql-mmm 依赖的 perl 库安装
如果无法进入 cpan 方式安装，可以如下操作：
# yum -y install perl-ExtUtils-MakeMaker
# tar -zxvf CPAN-1.9205.tar.gz
# cd CPAN-1.9205
# perl Makefile.PL
# make && make install

1、在 weiliaodb1、weiliaodb2、weiliaodb3 安装 mysql-agent 所依赖的 perl 库
# perl -MCPAN -e shell
cpan> install Algorithm::Diff
cpan> install DBI
cpan> install Log::Dispatch
cpan> install Log::Log4perl
cpan> install Mail::Send
cpan> install Net::ARP
cpan> install Proc::Daemon
cpan> install Time::HiRes
cpan> install DBD::mysql
cpan> install File::stat
cpan> install File:basename

2、在 monitor 安装 mysql-monitor 所依赖的 perl 库
# perl -MCPAN -e shell
cpan> install Algorithm::Diff
cpan> install Class::Singleton
cpan> install Log::Dispatch
cpan> install Log::Log4perl
cpan> install Mail:Send
cpan> install Proc::Daemon
cpan> install Thread::Queue
cpan> install Time::HiRes
cpan> install DBI and DBD::mysql
cpan> install File::Basename
cpan> install File::stat
cpan> install File::Temp
cpan> Net::Ping

四、安装 mysql-mmm

1、在 weiliaodb1、weiliaodb2、weiliaodb3、monitor
# wget http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz
# mv :mmm2:mysql-mmm-2.2.1.tar.gz mysql-mmm-2.2.1.tar.gz
# tar -zxvf mysql-mmm-2.2.1.tar.gz
# cd mysql-mmm-2.2.1
# make && make install

2、路径分布
/usr/lib/perl5/vendor_perl/5.8.8/MMM MMM 使用的 perl 模块
/usr/lib/mysql-mmm MMM 的脚本揑件
/usr/sbin MMM 的命令保存路径
/var/log/mysql-mmm MMM 的日志保存路径
/etc MMM 配置文件保存的路径
/etc/mysql-mmm MMM 配置文件保存的路径，优先级最高
/etc/init.d/ agentd 和 monitor 的启劢关闭脚本

3、配置 MYSQL-MMM-AGENTD
添加 agentd 使用的 mysql 用户 (weiliaodb1、weiliaodb2、weiliaodb3)：
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.233' identified by '123456';
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.234' identified by '123456';
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.235' identified by '123456';
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.240' identified by '123456';

配置 mmm_common.conf (weiliaodb1、weiliaodb2、weiliaodb3、monitor)：
# vi /etc/mysql-mmm/mmm_common.conf
active_master_role writer

<host default>
cluster_interface eth0

pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/

replication_user slave
replication_password 123456

agent_user mmm_agent
agent_password 123456

mysql_port 3306
</host>

<host weiliaodb1>
ip 192.168.1.233
mode master
peer weiliaodb2
</host>

<host weiliaodb2>
ip 192.168.1.234
mode master
peer weiliaodb1
</host>

<host weiliaodb3>
ip 192.168.1.235
mode slave
</host>

<role writer>
hosts weiliaodb1, weiliaodb2
ips 192.168.1.10
mode exclusive
</role>

<role reader>
hosts weiliaodb1, weiliaodb2, weiliaodb3
ips 192.168.1.20,192.168.1.30,192.168.1.40
mode balanced
</role>

配置 mmm_agent.conf (weiliaodb1、weiliaodb2、weiliaodb3)：
在 weiliaodb1 上：
# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this weiliaodb1

在 weiliaodb2 上：
# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this weiliaodb2

在 weiliaodb3 上：
# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this weiliaodb3

4、启动 MYSQL-MMM_AGENT (weiliaodb1、weiliaodb2、weiliaodb3)
# /etc/init.d/mysql-mmm-agent start
# /etc/init.d/mysql-mmm-agent status
# ps aux | grep mmm
# netstat -tulnp | grep mmm

注意：/var/log/mysql-mmm/mmm_agentd.log

5、配置 MYSQL-MMM-MONITOR
创建 mmm-monitor 使用的 mysql 用户 (weiliaodb1、weiliaodb2、weiliaodb3)：
mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.1.240' identified by '123456';

修改 monitor 上的配置
# vi /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf

<monitor>
ip 192.168.1.240
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path /var/lib/misc/mmm_mond.status
ping_ips 192.168.1.233, 192.168.1.234, 192.168.1.235
</monitor>

<host default>
monitor_user mmm_monitor
monitor_password 123456
</host>

debug 0

启动 MYSQL-MMM-MONITOR
# /etc/init.d/mysql-mmm-monitor start
# ps aux | grep mmm

6、管理 MYSQL-MMM-MONITOR
# mmm_control mode
# mmm_control show
weiliaodb1(192.168.1.233) master/AWAITING_RECOVERY. Roles:
weiliaodb2(192.168.1.234) master/AWAITING_RECOVERY. Roles:
weiliaodb3(192.168.1.235) slave/AWAITING_RECOVERY. Roles:

现在都处于 AWATING_RECOVERY 状态

# mmm_control set_online weiliaodb1
# mmm_control set_online weiliaodb2
# mmm_control set_online weiliaodb3

# mmm_control show
# mmm_control checks all

将 writer 角色以外的主机的 mysql 都设置成 read-only=1
mysql> show global variables like 'read_only';
mysql> set global read_only=on;

注意：如果某台DB宕机超过60秒，需要手动设置online

五、测试部分
1、写入数据是否同步 (略)
2、writer 故障切换 (略)
3、weiliaodb3 的主是否切换 (略)
4、测试角色优先配置的影响

修改 mmm-monitor 的配置，让 weiliaodb1 具有优先获取 writer 角色
# vi /etc/mysql-mmm/mmm_common.conf
<role writer>
hosts weiliaodb1, weiliaodb2
ips 192.168.1.10
mode exclusive
prefer weiliaodb1
</role>

# /etc/init.d/mysql-mmm-monitor restart

转载于:https://blog.51cto.com/sofar/764190