mysql-vip:192.168.15.170 mysql-master1:192.168.15.168 mysql-master2:192.168.15.169
OS版本：CentOS 5.5 mysql版本：mysql-5.1.53.tar.gz keepalived版本：keepalived-1.2.1.tar.gz ipvsadm版本：ipvsadm-1.24.tar.gz
在两台机器上分别安装ipvsadm keepalived mysql 安装ipvsadm # uname -r 2.6.18-194.el5 # ln -sv /usr/src/kernels/2.6.18-194.el5-i686/ /usr/src/linux # tar zxvf ipvsadm-1.24.tar.gz # cd ipvsadm-1.24 # make && make install # ipvsadm IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn 检查当前加载的内核模块，是否存在ip_vs模块 # lsmod | grep ip_vs ip_vs 78081 0
安装mysql useradd mysql -s /sbin/nologin tar zxvf mysql-5.1.53.tar.gz cd mysql-5.1.53 ./configure --prefix=/usr/local/mysql --localstatedir=/opt/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --
enable-thread-safe-client make && make install cp support-files/my-large.cnf /etc/my.cnf cp support-files/mysql.server /etc/rc.d/init.d/mysqld chmod 755 /etc/rc.d/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on cd /usr/local/mysql/ chgrp -R mysql . mkdir /opt/data chown -R mysql:mysql /opt/data /usr/local/mysql/bin/mysql_install_db --user=mysql 修改配置文件 vi /etc/my.cnf 不同的地方就是server-id,主服务器配置文件不用修改,从服务器的配置文件server-id=10.其他的内容基本相同. /usr/local/mysql/bin/mysqld_safe --user=mysql& 设置root 密码 /usr/local/mysql/bin/mysqladmin -u root password "123456"
将192.168.15.168设为 192.168.15.169的主服务器，在192.168.15.168新建授权用户 #/usr/local/mysql/bin/mysql -uroot -p mysql> grant replication slave on *.* to 'replication'@'%' identified by 'replication'; 得到binlog日志文件名和偏移量 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 244 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
在192.168.15.169上将192.168.15.168设置为自己的主服务器 #/usr/local/mysql/bin/mysql -uroot –p mysql> change master to
master_host='192.168.15.168',master_user='replication',master_password='replication',master_log_file=' mysql-bin.000003',master_log_pos=244;
将192.168.15.169设为 192.168.15.168的主服务器，在192.168.15.168新建授权用户 #/usr/local/mysql/bin/mysql -uroot –p mysql> grant replication slave on *.* to 'replication'@'%' identified by 'replication'; 得到binlog日志文件名和偏移量 mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 244 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
在192.168.15.168上将192.168.15.169设置为自己的主服务器 #/usr/local/mysql/bin/mysql -uroot –p mysql> change master to
master_host='192.168.15.169',master_user='replication',master_password='replication',master_log_file=' mysql-bin.000003',master_log_pos=244; 启用复制功能（两台机器） mysql> start slave; mysql> show slave status/G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.15.168 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 398 Relay_Log_File: mysql2-relay-bin.000002 Relay_Log_Pos: 405 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 398 Relay_Log_Space: 561 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)
同步测试 在168上的test数据库中创建user表 mysql> use test; mysql> create table user(id int); 在169中查看user表 mysql> use test; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.00 sec)
在169上test数据库中常见job表 mysql> create table job(id int); 在168中查看user表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | job | | user | +----------------+ 2 rows in set (0.00 sec)
安装keepalived # tar zxvf keepalived-1.2.1.tar.gz # cd keepalived-1.2.1 # ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18 -194.el5-i686/ # make && make install
192.168.15.168上配置keepalived # mkdir /etc/keepalived # vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { notification_email { 157704487@qq.com } notification_email_from 157704487@qq.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MySQL-ha } vrrp_instance VI_1 { state BACKUP #两台配置此处均是BACKUP interface eth0 virtual_router_id 51 priority 100 #优先级，另一台改为90 advert_int 1 nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.15.170 } } virtual_server 192.168.15.170 3306 { delay_loop 2 #每个2秒检查一次real_server状态 lb_algo wrr #LVS算法 lb_kind DR #LVS模式 persistence_timeout 60 #会话保持时间 protocol TCP real_server 192.168.15.168 3306 { weight 3 notify_down /usr/local/MySQL/bin/MySQL.sh #检测到服务down后执行的脚本 TCP_CHECK { connect_timeout 10 #连接超时时间 nb_get_retry 3 #重连次数 delay_before_retry 3 #重连间隔时间 connect_port 3306 #健康检查端口 } } 编写检测服务down后所要执行的脚本 #vi /usr/local/MySQL/bin/MySQL.sh #!/bin/sh pkill keepalived #chmod +x /usr/local/MySQL/bin/MySQL.sh 启动keepalived #/usr/local/keepalived/sbin/keepalived –D #ps -aux | grep keepalived
192.168.15.169上配置keepalived # mkdir /etc/keepalived # vi /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { notification_email { 157704487@qq.com } notification_email_from 157704487@qq.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MySQL-ha } vrrp_instance VI_1 { state BACKUP #两台配置此处均是BACKUP interface eth0 virtual_router_id 51 priority 90 #优先级 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.15.170 } } virtual_server 192.168.15.170 3306 { delay_loop 2 #每个2秒检查一次real_server状态 lb_algo wrr #LVS算法 lb_kind DR #LVS模式 persistence_timeout 60 #会话保持时间 protocol TCP real_server 192.168.15.169 3306 { weight 3 notify_down /usr/local/MySQL/bin/MySQL.sh #检测到服务down后执行的脚本 TCP_CHECK { connect_timeout 10 #连接超时时间 nb_get_retry 3 #重连次数 delay_before_retry 3 #重连间隔时间 connect_port 3306 #健康检查端口 } } 编写检测服务down后所要执行的脚本 #vi /usr/local/MySQL/bin/MySQL.sh #!/bin/sh pkill keepalived #chmod +x /usr/local/MySQL/bin/MySQL.sh 启动keepalived #/usr/local/keepalived/sbin/keepalived –D #ps -aux | grep keepalived
keepalived故障转移测试 在windows 客户端一直ping 192.168.15.170 然后分别关闭192.168.15.168 192.168.15.169 上的keepalived 看是否自动切换 168上tail -n 100 /var/log/messages Feb 24 16:50:30 tomcat2 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE 169上tail -n 100 /var/log/messages Feb 24 16:32:07 mysql2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering BACKUP STATE
关闭168上的keepalived pkill keepalived 169上tail -n 100 /var/log/messages Feb 24 16:41:36 mysql2 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE Feb 24 16:41:37 mysql2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE
开启168上的keepalived /usr/local/keepalived/sbin/keepalived -D 168上tail -n 100 /var/log/messages Feb 24 17:02:08 tomcat2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering BACKUP STATE
关闭169上的keepalived /usr/local/keepalived/sbin/keepalived -D 168上tail -n 100 /var/log/messages Feb 25 10:11:20 tomcat2 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE Feb 25 10:11:21 tomcat2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE
开启169上的keepalived /usr/local/keepalived/sbin/keepalived -D 169上tail -n 100 /var/log/messages Feb 25 10:14:13 mysql2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering BACKUP STATE
MySQL故障转移测试 在168上关闭mysql服务 /etc/init.d/mysqld stop 168上tail -n 100 /var/log/messages Feb 25 10:15:26 tomcat2 Keepalived_healthcheckers: TCP connection to [192.168.15.168:3306] failed !!! Feb 25 10:15:26 tomcat2 Keepalived_healthcheckers: Removing service [192.168.15.168:3306] from VS [192.168.15.170:3306] 在168上开启mysqlfuw /etc/init.d/mysqld start tail -n 100 /var/log/messages Feb 25 10:18:16 tomcat2 Keepalived_healthcheckers: TCP connection to [192.168.15.168:3306] success. Feb 25 10:18:16 tomcat2 Keepalived_healthcheckers: Adding service [192.168.15.168:3306] to VS [192.168.15.170:3306]
在169上测试也一样 /etc/init.d/mysqld stop Feb 25 10:20:05 mysql2 Keepalived_healthcheckers: TCP connection to [192.168.15.169:3306] failed !!! Feb 25 10:20:05 mysql2 Keepalived_healthcheckers: Removing service [192.168.15.169:3306] from VS [192.168.15.170:3306] /etc/init.d/mysqld start Feb 25 10:20:37 mysql2 Keepalived_healthcheckers: TCP connection to [192.168.15.169:3306] success. Feb 25 10:20:37 mysql2 Keepalived_healthcheckers: Adding service [192.168.15.169:3306] to VS [192.168.15.170:3306]
mysql 远程登录测试 在两台服务器上分别执行，授予远程登录
1. MySQL> grant all privileges on *.* to 'root'@'%' identified by '123456'; 2. Query OK, 0 rows affected (0.00 sec) 3. 4. MySQL> flush privileges; 5. Query OK, 0 rows affected (0.00 sec)
查看远程连接的实际服务是那个
168上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.15.170:3306 wrr persistent 60 -> 192.168.15.168:3306 Local 3 1 0
169上ipvsadm –ln
[root@mysql2 ~]# 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.15.170:3306 wrr persistent 60 -> 192.168.15.169:3306 Local 3 0 0
相关链接 http://database.51cto.com/art/201012/237204.htm
