试验环境：
主：192.168.1.210（CentOS6.5）
从属：192.168.1.211（CentOS6.5）
VIP：192.168.1.208
MySQL的主主互备模式配置
步骤1：主服务的/etc/my.cnf中配置
1 2 3 4 五 6 7 8 9 10 11 12 [mysqld] basedir = /usr/local/mysql datadir = /var/lib/mysql port = 3306 socket = /var/lib/mysql/mysql .sock server_id = 1 log-bin = mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% #指定不需要复制的库，mysql.%表示mysql库下的所有对象 replicate-wild-ignore-table= test .% replicate-wild-ignore-table=information_schema.%第二步：从服务的/etc/my.cnf中配置
1 2 3 4 五 6 7 8 9 10 11 12 [mysqld] basedir = /usr/local/mysql datadir = /var/lib/mysql port = 3306 socket = /var/lib/mysql/mysql .sock server_id = 2 log-bin = mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table= test .% replicate-wild-ignore-table=information_schema.%第三步：重启两台主从mysql的服务
1 2 3 4 五 6 [root@master ~] # service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] [root@slave ~] # service mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ]
第四步：查看主从的日志斌日志状态
记录文件和位置的值
1 2 3 4 五 6 7 [root@master ~] # mysql -uroot -ppasswd -e 'show master status' Warning: Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 414 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 2 3 4 五 6 7 [root@slave ~] # mysql -uroot -ppasswd -e 'show master status' Warning: Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 414 | | | | +------------------+----------+--------------+------------------+-------------------+
第5步：创建主从同步复制用户
1，主
1 2 3 4 五 6 7 8 9 10 mysql> grant replication slave on *.* to 'replication' @ '192.168.1.211' identified by 'replication' ; mysql> flush privileges; mysql> change master to -> master_host= '192.168.1.211' , -> master_user= 'replication' , -> master_password= 'replication' , -> master_port=3306, -> master_log_file= 'mysql-bin.000001' , -> master_log_pos=414; mysql> start slave;2，从属
1 2 3 4 五 6 7 8 9 10 mysql> grant replication slave on *.* to 'replication' @ '192.168.1.210' identified by 'replication' ; mysql> flush privileges; mysql> change master to -> master_host= '192.168.1.210' , -> master_user= 'replication' , -> master_password= 'replication' , -> master_port=3306, -> master_log_file= 'mysql-bin.000001' , -> master_log_pos=414; mysql> start slave;同步失败可能需要停止或重设从
停止从站
mysql> reset slave;
步骤6：分别在主和从上查看从属状态，验证是否成功配置主主复制模式
1，主
2，从属
从状态同步过程可能需要重启MySQL的服务
[root @ master〜]＃service mysqld restart [root @ slave〜]＃service mysqld restart
第7步：验证，在主上创建TEST1数据库，从上查看是否同步
如图1所示，主站上创建TEST1数据库
1 [root@master ~] # mysql -uroot -ppasswd -e 'create database test1'2，从属上查看是否同步创建TEST1
1 2 3 4 五 6 7 8 9 [root@slave ~] # mysql -uroot -ppasswd -e 'show databases' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test1 | +--------------------+
安装和配置KEEPALIVED实现MySQL的双主高可用
第一步：安装KEEPALIVED
方法一：使用百胜安装KEEPALIVED，需要安装EPEL释放源
[root @ master〜]＃rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm [root @ slave〜]＃rpm -ivh http：/ /mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
[root @ slave〜]＃yum -y install keepalived
查看KEEPALIVED相关目录
1 2 3 4 五 6 [root@slave ~] # ls /usr/sbin/keepalived /usr/sbin/keepalived [root@slave ~] # ls /etc/init.d/keepalived /etc/init .d /keepalived [root@slave ~] # ls /etc/keepalived/keepalived.conf /etc/keepalived/keepalived .conf
方法二：从KEEPALIVED网站官方http://www.keepalived.org下载源代码包compile-安装
1，下载KEEPALIVED最新版
[root @ master〜]＃wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
[root @ slave〜]＃wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
2，安装KEEPALIVED依赖软件包
[root @ master〜]＃yum install pcre-devel openssl-devel popt-devel libnl-devel
3，解压并安装的keepalived
1 2 3 4 五 [root@master ~] # tar zxf keepalived-1.2.19.tar.gz [root@master ~] # cd keepalived-1.2.19 [root@master keepalived-1.2.19] # ./configure --prefix=/usr/local/keepalived --sysconf= /etc --with-kernel- dir = /usr/src/kernels/2 .6.32-431.el6.x86_64 1 2 [root@master keepalived-1.2.19] # make [root@master keepalived-1.2.19] # make install查看KEEPALIVED相关的文件
1 2 3 4 [root@master keepalived-1.2.19] # ls /etc/keepalived/ keepalived.conf samples [root@master keepalived-1.2.19] # ls /etc/init.d/keepalived /etc/init .d /keepalived链接在/ usr /本地/ KEEPALIVED / sbin目录/ KEEPALIVED到/ sbin目录/目录
1 [root@master keepalived-1.2.19] # ln -s /usr/local/keepalived/sbin/keepalived /sbin/设置KEEPALIVED启动级别
1 2 [root@master keepalived-1.2.19] # chkconfig --add keepalived [root@master keepalived-1.2.19] # chkconfig --level 35 keepalived on
第二步：配置KEEPALIVED
1，法师的keepalived.conf配置文件
1 2 3 4 五 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 三十 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 ! Configuration File for keepalived global_defs { notification_email { root@huangmingming.cn 741616710@qq.com } notification_email_from keepalived@localhost smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL } vrrp_instance HA_1 { state BACKUP #master和slave都配置为BACKUP interface eth0 #指定HA检测的网络接口 virtual_router_id 80 #虚拟路由标识，主备相同 priority 100 #定义优先级，slave设置90 advert_int 1 #设定master和slave之间同步检查的时间间隔 nopreempt #不抢占模式。只在优先级高的机器上设置即可 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #设置虚拟IP，可以设置多个，每行一个 192.168.1.208 /24 dev eth0 #MySQL对外服务的IP，即VIP } } virtual_server 192.168.1.208 3306 { delay_loop 2 #每隔2秒查询real server状态 lb_algo wrr #lvs 算法 lb_kinf DR #LVS模式（Direct Route） persistence_timeout 50 protocol TCP real_server 192.168.1.210 3306 { #监听本机的IP weight 1 notify_down /usr/local/keepalived/bin/mysql .sh TCP_CHECK { connect_timeout 10 #10秒无响应超时 bingto 192.168.1.208 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } }KEEPALIVED检测脚本，当其中一台MySQL的服务出现故障向下掉时，实现自动切换到正常的MySQL的服务器继续提供服务
1 2 3 [root@master ~] # vim /usr/local/keepalived/bin/mysql.sh #!/bin/bash pkill keepalived
2，从站的keepalived.conf配置文件
1 2 3 4 五 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 三十 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 ! Configuration File for keepalived global_defs { notification_email { root@huangmingming.cn 741616710@qq.com } notification_email_from keepalived@localhost smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL } vrrp_instance HA_1 { state BACKUP #master和slave都配置为BACKUP interface eth0 #指定HA检测的网络接口 virtual_router_id 80 #虚拟路由标识，主备相同 priority 90 #定义优先级，slave设置90 advert_int 1 #设定master和slave之间同步检查的时间间隔 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #设置虚拟IP，可以设置多个，每行一个 192.168.1.208 /24 dev eth0 #MySQL对外服务的IP，即VIP } } virtual_server 192.168.1.208 3306 { delay_loop 2 lb_algo wrr lb_kinf DR persistence_timeout 50 protocol TCP real_server 192.168.1.211 3306 { #监听本机的IP weight 1 notify_down /usr/local/mysql/bin/mysql .sh TCP_CHECK { connect_timeout 10 bingto 192.168.1.208 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } }
第三步：授权VIP的根用户权限
授权远程主机可以通过VIP登录的MySQL，并测试数据复制功能
1 2 mysql> grant all on *.* to root@ '192.168.1.208' identified by '741616710' ; mysql> flush privileges;第四步：测试KEEPALIVED高可用功能
1，远程主机登录通过VIP192.168.1.208登录的MySQL，MySQL的查看连接状态
1 2 3 4 五 6 7 mysql> show variables like 'hostname%' ; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | master | +---------------+--------+ 1 row in set (0.00 sec)从上面查看的结果看样看出在正常情况下连接的是主
2，故障测试，停止主的MySQL的服务，再次查看是否转移至从属服务器上
1 2 [root@master ~] # service mysqld stop Shutting down MySQL.... SUCCESS! 1 2 3 4 五 6 7 8 9 10 11 12 13 14 mysql> show variables like 'hostname%' ; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> show variables like 'hostname%' ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id : 1268 Current database: *** NONE *** +---------------+-------+ | Variable_name | Value | +---------------+-------+ | hostname | slave | +---------------+-------+ 1 row in set (0.01 sec)由测试结果可以看出，KEEPALIVED成功转移的MySQL服务
