heartbeat +mysql master +ldirectord构建高可用的mysql主主复制

shenlan219

贡献于2012-03-27

字数:0 关键词: MySQL 数据库服务器

对于对数据库要求比较高的业务,我们可以用 MYSQL 主主复制来保证业务的稳定性,同 时我们又要确保业务切换的的自动化,无手动干预。就得借组 heartbeat 和 ldirectord 来实现。 两台主机:第一步:MYSQL AA 复制,第二步:高可用,第三步:负载均衡和状态检测 环境:两台虚拟机,CentOS 5.2 一:MySQL AA 复制 安装软件 [root@ha1 ~]#yum –y install mysql mysql-server perl-DBI perl-DBD-MySQL ##节约时间我全 部用 yum 来安装软件包 MYSQL 的 MASTER 到 MASTER 的主主循环同步 1、环境描述。 主机:192.168.0.251(A) 主机:192.168.0.252(B) MYSQL 版本为 5.1.30 2、授权用户。 A: mysql> grant replication slave,file on *.* to 'repl1'@'192.168.0.232' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) B: mysql> grant replication slave,file on *.* to 'repl2'@'192.168.0.231' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 然后都停止 MYSQL 服务器。 3、配置文件。 在两个机器上的 my.cnf 里面都开启二进制日志 。 A: user = mysql log-bin=mysql-bin server-id= 1 binlog-do-db=test binlog-ignore-db=mysql replicate-do-db=test replicate-ignore-db=mysql log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 B: user = mysql log-bin=mysql-bin server-id= 2 binlog-do-db=test binlog-ignore-db=mysql replicate-do-db=test replicate-ignore-db=mysql log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=2 至于这些参数的说明具体看手册。 红色的部分非常重要,如果一个 MASTER 挂掉的话,另外一个马上接管。 紫红色的部分指的是服务器频繁的刷新日志。这个保证了在其中一台挂掉的话,日志刷新到 另外一台。从而保证了数据的同步 。 4、重新启动 MYSQL 服务器。 在 A 和 B 上执行相同的步骤 [root@ha1 ~]#service mysqld start 5、进入 MYSQL 的 SHELL。 A: mysql> flush tables with read lock\G Query OK, 0 rows affected (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 98 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) B: mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 98 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) 然后备份自己的数据,保持两个机器的数据一致。 方法很多。完了后看下一步。 6、在各自机器上执行 CHANGE MASTER TO 命令。 A: change master to master_host='192.168.0.252', master_user='repl2', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=98; mysql> start slave; Query OK, 0 rows affected (0.00 sec) B: change master to master_host='192.168.0.251', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=98; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) 7、查看各自机器上的 IO 进程和 SLAVE 进程是否都开启。 A: mysql> show processlist\G *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 19114 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 1054 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 5. row *************************** Id: 18076 User: repl1 Host: 192.168.0.252:53675 db: NULL Command: Binlog Dump Time: 765 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL B 上: mysql> show processlist\G *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 18775 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 775 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL ***************************3. row *************************** Id: 17783 User: repl2 Host: 192.168.0.251:42196 db: NULL Command: Binlog Dump Time: 1056 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL 如果红色部分没有出现,检查 DATA 目录下的错误文件。 8、释放掉各自的锁,然后进行插数据测试。 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) 插入之前两个机器表的对比: A: Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | ha | | test | +----------------+ 2 rows in set (0.00 sec) B: mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | ha | | test | +----------------+ 2 rows in set (0.00 sec) 从 A 机器上进行插入 A: mysql> create table test -> (id int not null auto_increment primary key, -> str varchar(255) not null) engine myisam; Query OK, 0 rows affected (0.01 sec) mysql> insert into test(str) values -> ('This is a master to master test table'); Query OK, 1 row affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | ha | | test | +----------------+ 2 rows in set (0.00 sec) mysql> select * from test; +----+---------------------------------------+ | id | str | +----+---------------------------------------+ | 1 | This is a master to master test table | +----+---------------------------------------+ 1 row in set (0.00 sec) 现在来看 B 机器: mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | ha | | test | +----------------+ 2 rows in set (0.00 sec) mysql> select * from t11_replicas; +----+---------------------------------------+ | id | str | +----+---------------------------------------+ | 1 | This is a master to master test table | +----+---------------------------------------+ 1 row in set (0.00 sec) 现在反过来从 B 机器上插入数据: B: mysql> insert test(str) values('This is a test 2'); Query OK, 1 row affected (0.00 sec) mysql> select * from t11_replicas; +----+---------------------------------------+ | id | str | +----+---------------------------------------+ | 1 | This is a master to master test table | | 2 | This is a test 2 | +----+---------------------------------------+ 2 rows in set (0.00 sec) 我们来看 A A: mysql> select * from test; +----+---------------------------------------+ | id | str | +----+---------------------------------------+ | 1 | This is a master to master test table | | 2 | This is a test 2 | +----+---------------------------------------+ 2 rows in set (0.00 sec) 现在两个表互相为 MASTER。 多 MASTER 自增字段冲突的问题。 具体文章见: http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication.html 在邮件列表中看到有人讨论在线同步与忽略库与表的问题,具体看: http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html 二:高可用 现在我们让两台成为高可用,所谓高可用,就是双机热备,互备。这里我不解释太多,相应 搞过 HA 的人都清楚,不清楚的上 www.linux-ha.org 上面看看文档。 主机 ha1 和 ha2 上安装 heartbeat Yum –y install heartbeat && yum –y install heartbeat //这里我也不清楚为什么要运行两次他才给我安装 heartbeat ,嘿嘿,哪位知道告诉我为什么, 我是每次都要晕两次才可以安装 配置 heartbeat 拷呗 cp /usr/share/doc/heartbeat-2.1.3/ 下面的 ha.cf,authkeys,haresources 到/etc/ha.d 目录下 面 cp /usr/share/doc/heartbeat-2.1.3/haresources /etc/ha.d cp /usr/share/doc/heartbeat-2.1.3/ha.cf /etc/ha.d cp /usr/share/doc/heartbeat-2.1.3/authkeys /etc/ha.d cd /etc/ha.d chmod 600 authkeys authkeys 文件中下面两行前面的#号去掉 auth 1 1 crc 配置 ha.cf 内容如下,我就不一一解释了,有英语注释,注意的地方我会加注释 debugfile /var/log/ha-debug logfile /var/log/ha-log logfacility local0 keepalive 2 deadtime 10 warntime 5 initdead 120 ucast eth1 192.168.254.5 //这里是对方的 ip 主要用于 HA 的相互检测 网络接口要注意了, 我用的是专用接口,ha2 主机改为 192.168.254.4 auto_failback on watchdog /dev/watchdog //软件狗 node ha1 node ha2 ping 192.168.254.1 respawn hacluster /usr/lib/heartbeat/ipfail apiauth ipfail gid=haclient uid=hacluster 配置 haresource ha1 IPaddr::192.168.0.253/24/eth0:1 ldirectord 主节点 浮动 IP 启动的服务 工作完成,把/etc/ha.d 目录全部覆盖到 ha2 上面去 scp -r /etc/ha.d 192.168.254.5:/etc 提示输入对方 root 用户的密码,全部转输完以后,注意修改/etc/ha.d/ha.cf 中的 ucast eth1 192.168.254.5 为对方的 IP 也就是 HA1 主机的 ETH1 的 IP 改为 ucast eth1 192.168.254.4 配置 real server 在 Real Server 上创建一个脚本: Shell # vi /root/mysql_real.sh #!/bin/bash #Description : RealServer Start! #Write by:huzi #Last Modefiy:2009-3-26 VIP=192.168.0.253 /sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 up /sbin/route add -host $VIP dev lo:0 echo 1 >/proc/sys/net/ipv4/ip_forward echo 1 >/proc/sys/net/ipv4/conf/lo/arp_ignore echo 2 >/proc/sys/net/ipv4/conf/lo/arp_announce echo 1 >/proc/sys/net/ipv4/conf/all/arp_ignore echo 2 >/proc/sys/net/ipv4/conf/all/arp_announce sysctl –p 两边一样,启动时候加载这个脚本 三、配置负载和状态检测 两台主机分别安装 ldirectord [root@ha1 ha.d]# yum –y install heartbeat-ldirectord cp /usr/share/doc/heartbeat-2.1.3/ldirectord.conf /etc/ha.d 内容如下 [root@ha1 ha.d]# cat ldirectord.cf # # Sample ldirectord configuration file to configure various virtual services. # # Ldirectord will connect to each real server once per second and request # /index.html. If the data returned by the server does not contain the # string "Test Message" then the test fails and the real server will be # taken out of the available pool. The real server will be added back into # the pool once the test succeeds. If all real servers are removed from the # pool then localhost:80 is added to the pool as a fallback measure. # Global Directives checktimeout=3 checkinterval=1 #fallback=127.0.0.1:80 autoreload=yes #logfile="/var/log/ldirectord.log" #logfile="local0" #emailalert="admin@x.y.z" #emailalertfreq=3600 #emailalertstatus=all quiescent=yes #Sample configuration for a MySQL virtual service. virtual = 192.168.0.253:3306 real=192.168.0.251:3306 gate 10 real=192.168.0.252:3306 gate 10 # fallback=127.0.0.1:3306 service=mysql scheduler=rr # #persistent=600 # #netmask=255.255.255.255 protocol=tcp # checktype=negotiate login="huzi" passwd="huzi" database="test" request="SELECT * FROM huzi" 在两边的 MYSQL test 数据库中建立 huzi 这个表,然后给予这两个 IP 访问的权限,我是直 接用%来表示 以下是两台主机都要操作 Mysql>grant all privileges on test.huzi to ‘huzi’@’%’ identified by ‘huzi’; Mysql>flush privileges; 两边启动 heartbeat 观察日志,用 ipvsadm –Ln 查看状态,然后用客户端连接测试 [root@ha1 ha.d]# ipvsadm -Ln IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 192.168.0.253:3306 rr -> 192.168.0.252:3306 Route 10 0 0 -> 192.168.0.251:3306 Local 10 0 0 在 WINDOWS 上面用客户端连接,然后看看状态 [root@ha1 ha.d]# ipvsadm -Ln IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 192.168.0.253:3306 rr -> 192.168.0.252:3306 Route 10 1 0 -> 192.168.0.251:3306 Local 10 2 0 [root@ha1 ha.d]# ipvsadm -L -c -n IPVS connection entries pro expire state source virtual destination TCP 14:59 ESTABLISHED 192.168.0.36:2298 192.168.0.253:3306 192.168.0.251:3306 TCP 14:59 ESTABLISHED 192.168.0.36:2300 192.168.0.253:3306 192.168.0.251:3306 TCP 14:59 ESTABLISHED 192.168.0.35:1782 192.168.0.253:3306 192.168.0.252:3306 [root@ha1 ha.d]# 启动方式放在/etc/rc.local 中 /root/mysql_real.sh Chkconfig mysqld on Chkconfig heartbeat on Mysql MASTER复制参考:http://blog.chinaunix.net/u/29134/showart_441667.html

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 3 人已下载

下载文档

相关文档