[MySQL集群架构] mysql主主+lvs

听老歌

[MySQL集群架构] mysql主主+lvs

测试机2台，分别为A，B，系统均为AS4。A的IP:192.168.17.21，B的IP:192.168.17.53。
1：安装mysql。
分别在A,B上执行

1. [root@data1 ~]# cd /home
   
2. [root@data1 ~]# tar zxvf mysql-5.1.32.tar.gz
   
3. [root@data1 ~]# cd mysql-5.1.32
   
4. [root@data1 ~]# ./configure --prefix=/usr/local/mysql --with-extra-charsets=all --enable-thread-safe-client --with-plugin-partition
   
5. [root@data1 ~]# make
   
6. [root@data1 ~]# make install
   
7. [root@data1 ~]# /usr/local/mysql/bin/mysql_install_db --user=mysql
   
8. [root@data1 ~]# cp support-files/my-medium.cnf /etc/my.cnf
   
9. [root@data1 ~]# cd /usr/local/mysql/
   
10. [root@data1 ~]# chown -R root .
    
11. [root@data1 ~]# chown -R mysql var
    
12. [root@data1 ~]# chgrp -R mysql .
    
13. [root@data1 ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
    
14. [root@data1 ~]# /usr/local/mysql/bin/mysqladmin -uroot password newpasswd

复制代码

2:A,B互为主从
AB上分别添加复制用户，方便起见，用root测试。

mysql> GRANT ALL PRIVILEGES ON *.* TO [email=root@]root@"%[/email]" IDENTIFIED BY "newpasswd"  WITH GRANT OPTION;
mysql> flush privileges;

A上，修改my.cnf文件，[mysqld]下添加：

1. 
   
2. .
   
3. .
   
4. .
   
5. auto-increment-increment = 2
   
6. auto-increment-offset = 2
   
7. server-id = 10
   
8. log-error = /usr/local/mysql/var/data1.err
   
9. log-bin = /usr/local/mysql/var/mysql-master-bin.log
   
10. binlog-do-db = db1
    
11. binlog-do-db = db2
    
12. binlog-do-db = db3
    
13. server-id = 2
    
14. master-host = 192.168.17.21
    
15. master-user = root
    
16. master-password = newpasswd
    
17. master-port = 3306
    
18. master-connect-retry=60
    
19. replicate-do-db=db1
    
20. replicate-do-db=db2
    
21. replicate-do-db=db3
    
22. log-bin=/usr/local/mysql/var/mysql-slave-bin.log

复制代码

B上，修改my.cnf文件，[mysqld]下添加：

1. 
   
2. auto-increment-increment = 2
   
3. auto-increment-offset = 1
   
4. server-id = 1
   
5. log-error = /usr/local/mysql/var/data2.err
   
6. log-bin = /usr/local/mysql/var/mysql-master-bin.log
   
7. binlog-do-db = db1
   
8. binlog-do-db = db2
   
9. binlog-do-db = db3
   
10. server-id = 20
    
11. master-host = 192.168.17.21
    
12. master-user = root
    
13. master-password = newpasswd
    
14. master-port = 3306
    
15. master-connect-retry=60
    
16. replicate-do-db=db1
    
17. replicate-do-db=db2
    
18. replicate-do-db=db3
    
19. log-bin=/usr/local/mysql/var/mysql-slave-bin.log

复制代码

A,B上重新启动mysql。
[root@data1 ~]#mysqladmin -uroot -pnewpasswd shutdown
[root@data1 ~]#/usr/local/mysql/bin/mysqld_safe &
A上进入mysql，并查看master状态
[root@data1 ~]# mysql -uroot -pnewpasswd
mysql> show master status;
+------------------------+----------+--------------------------------+------------------+
| File                   | Position | Binlog_Do_DB                   | Binlog_Ignore_DB |
+------------------------+----------+--------------------------------+------------------+
| mysql-slave-bin.000018 | 44305519 | db1,db2,db3                    |                  |
+------------------------+----------+--------------------------------+------------------+
进入B，设置B为A的从。
[root@data2 ~]# mysql -uroot -pnewpasswd
mysql>slave stop;
mysql>change master to
mysql>master_host='192.168.17.21',
mysql>master_user='root',
mysql>master_password='newpasswd',
mysql>master_port=3308,
mysql>master_log_file='mysql-slave-bin.000018',
mysql>master_log_pos=44305519;
mysql>slave start;
mysql> show master status;
+------------------------+----------+--------------------------------+------------------+
| File                   | Position | Binlog_Do_DB                   | Binlog_Ignore_DB |
+------------------------+----------+--------------------------------+------------------+
| mysql-slave-bin.000010 | 42354594 | db1,db2,db3                    |                  |
+------------------------+----------+--------------------------------+------------------+
1 row in set (0.00 sec)
进入A：

1. mysql>slave stop;
   
2. mysql>change master to
   
3. mysql>master_host='192.168.17.53',
   
4. mysql>master_user='root',
   
5. mysql>master_password='newpasswd',
   
6. mysql>master_port=3308,
   
7. mysql>master_log_file='mysql-slave-bin.000010',
   
8. mysql>master_log_pos=42354594;
   
9. mysql>slave start;

复制代码

3:安装LVS
A上虚拟一个网卡，IP地址为192.168.17.88，即VIP。

1. [root@data1 ~] touch /etc/sysconfig/network-scripts/ifcfg-eth0:1
   
2. [root@data1 ~] vi /etc/sysconfig/network-scripts/ifcfg-eth0:1
   
3. 
   
4. 
   
5. DEVICE=eth0:1
   
6. BOOTPROTO=none
   
7. HWADDR=
   
8. ONBOOT=yes
   
9. TYPE=Ethernet
   
10. IPADDR=192.168.17.88
    
11. NETMASK=255.255.255.0
    
12. USERCTL=no
    
13. IPV6INIT=no
    
14. PEERDNS=no

复制代码

[root@data1 ~] ifup eth0:1
AB上分别执行lvs.sh，内容为：

1. 
   
2. 
   
3. #!/bin/bash
   
4. #description : start realserver  
   
5. VIP=203.110.169.25
   
6. /sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 up
   
7. /sbin/route add -host $VIP dev lo:0
   
8. echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
   
9. echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
   
10. echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
    
11. echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce  
    
12. sysctl -p

复制代码

A,B上分别安装ipvsadm和piranha：

1. [root@data1 ~] cd /home/lvs
   
2. [root@data1 lvs]# rpm -ivh ipvsadm-1.24-6.1.i386.rpm
   
3. [root@data1 lvs]# rpm -ivh piranha-0.8.2-1.i386.rpm
   
4. [root@data1 lvs]# /usr/sbin/piranha-passwd  
   
5. New Password:  
   
6. Verify:  
   
7. Updating password for user piranha
   
8. [root@data1 lvs]# service piranha-gui restart
   
9. Shutting down piranha-gui: [  OK  ]
   
10. Starting piranha-gui: [  OK  ]
    
11. [root@data1 lvs]# chkconfig piranha-gui on

复制代码

登陆A的piranha进行配置，http://192.168.17.21:3636。
把A的/etc/sysconfig/ha/lvs.cf复制到B上。
具体配置如下：

1. 
   
2. 
   
3. serial_no = 14
   
4. primary = 192.168.17.21
   
5. service = lvs
   
6. backup_active = 1
   
7. backup = 192.168.17.53
   
8. heartbeat = 1
   
9. heartbeat_port = 539
   
10. keepalive = 6
    
11. deadtime = 18
    
12. network = direct
    
13. debug_level = NONE
    
14. monitor_links = 0
    
15. virtual mysql {
    
16.       active = 1
    
17.       address = 192.168.17.88 eth0:1
    
18.       vip_nmask = 255.255.255.0
    
19.       port = 3306
    
20.       use_regex = 0
    
21.       load_monitor = none
    
22.       scheduler = wlc
    
23.       protocol = tcp
    
24.       timeout = 6
    
25.       reentry = 15
    
26.       quiesce_server = 0
    
27.       server 21 {
    
28.           address = 192.168.17.21
    
29.           active = 1
    
30.           weight = 1
    
31.       }
    
32.       server 53 {
    
33.           address = 192.168.17.53
    
34.           active = 1
    
35.           weight = 1
    
36.       }
    
37. }
    

复制代码

AB上分别执行：
[root@data1 lvs]# service pulse start
至此，over。

测试了几天，进行了大量的数据库操作，至今没有报错。
方法虽然很简单，但是感觉蛮实用。
测试到月底，如果可行就开始上线。
有什么不对的和疏忽的地方请各位指正。

小忻黑夜

学习鸟  谢谢分享