环境描述：

MASTER：192.168.1.13

BACKUP：192.168.1.14

VIP：192.168.1.200

1、配置keepalived

在node1、node2节点上分别安装keepalived软件yum install -y pcre-devel openssl-devel popt-devel ncurses-devel*wget http://www.keepalived.org/software/keepalived-1.2.16.tar.gztar zxvf keepalived-1.2.16.tar.gzcd keepalived-1.2.16./configure --prefix=/usr/local/keepalived(如遇到configure: error: No SO_MARK declaration in headers，则用./configure --prefix=/usr/local/keepalived --disable-fwmark)make && make install
安装成功后做成服务模式cp /usr/local/keepalived/sbin/keepalived /usr/sbin/cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
配置文件mkdir -p /etc/keepalived/cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.confchmod +x /etc/init.d/keepalived
service keepalived startchkconfig keepalived oncd /etc/keepalived/cp keepalived.conf keepalived.conf.bak 

 1.1、MySQL01配置keepalived为MASTER

[root@MySQL01 /]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 192.168.200.1

smtp_connect_timeout 30

router_id 01

}

vrrp_instance VI_1 {

    state MASTER（修改为主）

    interface eth0 （对应网卡）

virtual_router_id 51(如果冲突就无法获取vip地址)

    priority 150(修改权重，主的数值要大)

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

    }

virtual_ipaddress {

192.168.1.200/24(主要修改vip地址)

    }

}

1.2、MySQL02配置keepalived为BACKUP

[root@MySQL02 keepalived]# cat /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server 192.168.200.1

smtp_connect_timeout 30

router_id 02

}

vrrp_instance VI_1 {

    state BACKUP（修改）

interface eth0

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

    }

virtual_ipaddress {

192.168.1.200/24（VIP地址）

    }

}

1.3、启动keepalived

加入开机启动：chkconfig keepalived on

[root@MySQL01 /]# /etc/init.d/keepalived start

Starting keepalived:

[root@MySQL02 /]# /etc/init.d/keepalived start

Starting keepalived:

1、MySQL01为MASTER存在VIP

[root@MySQL01 /]# ip addr

1: lo:mtu 65536 qdiscnoqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host 

valid_lft forever preferred_lft forever

2: eth0:mtu 1500 qdiscpfifo_fast state UP qlen 1000

link/ether 00:0c:29:85:45:80 brdff:ff:ff:ff:ff:ff

inet 192.168.1.13/24 brd 192.168.1.255 scope global eth0

inet 192.168.1.200/32 scope global eth0

    inet6 fe80::20c:29ff:fe85:4580/64 scope link 

valid_lft forever preferred_lft forever

2、MySQL01为BACKUP不存在VIP

[root@MySQL02 ]# ip addr

1: lo:mtu 65536 qdiscnoqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host 

valid_lft forever preferred_lft forever

2: eth0:mtu 1500 qdiscpfifo_fast state UP qlen 1000

link/ether 00:0c:29:4b:db:a9 brdff:ff:ff:ff:ff:ff

inet 192.168.1.14/24 brd 192.168.1.255 scope global eth0

inet 192.168.1.200/32 scope global eth0

    inet6 fe80::20c:29ff:fe4b:dba9/64 scope link 

valid_lft forever preferred_lft forever

2、配置双主双从

2.1、配置MySQL01为主库（主从）

1、配置mysqld(vi /etc/my.cnf):

[mysqld] 
server-id = 1                    #backup这台设置2 
log-bin = mysql-bin 
binlog-ignore-db = mysql,information_schema      #忽略写入binlog日志的库 
auto-increment-increment = 2            #字段变化增量值 
auto-increment-offset = 1              #初始字段ID为1 
slave-skip-errors = all                      #忽略所有复制产生的错误 

2、创建一个用户用于salve连接master

mysql>  GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED  BY 'replication'; 

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

+-------------+-------------+

| user        | host        |

+-------------+-------------+

| root        | 127.0.0.1   |

| replication | 192.168.0.% |

| mall        | 192.168.1.% |

| replication | 192.168.1.% |

|             | MYSQL01     |

| root        | MYSQL01     |

|             | localhost   |

| root        | localhost   |

+-------------+-------------+

8 rows in set (0.00 sec)

3、查看master的状态

mysql> show master status;              

+------------------+----------+--------------+--------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |

+------------------+----------+--------------+--------------------------+

| mysql-bin.000001 |      106 |              | mysql,information_schema |

+------------------+----------+--------------+--------------------------+

1 row in set (0.01 sec)

4、先关闭slave，再实现同步

mysql>stop slave;

mysql>reset slave;

mysql> change  master to 
    ->  master_host='192.168.1.14', 
    ->  master_user='replication', 
    ->  master_password='replication', 
    ->  master_log_file='mysql-bin.000001', 
    ->  master_log_pos=106;  #对端状态显示的值 
mysql> start  slave;        #启动同步

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.14

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 106

               Relay_Log_File: MYSQL01-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000001

             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: 106

              Relay_Log_Space: 408

              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)

2.2、配置MySQL02为从库

1、执行以下命令连接主库

mysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED  BY 'replication'; 
mysql> flush  privileges; 
mysql> change  master to          （配置时先关闭slave,同上)

    ->  master_host='192.168.1.13', 
    ->  master_user='replication', 
    ->  master_password='replication', 
    ->  master_log_file='mysql-bin.000001', 
    ->  master_log_pos=106; 

2、启动slave

mysql> start slave;       

Query OK, 0 rows affected, 1 warning (0.00 sec)

3、查看salve状态

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.13

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 106

               Relay_Log_File: MYSQL02-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000001

             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: 106

              Relay_Log_Space: 408

              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)

ERROR: 

No query specified

4、在主库创建一个test01库

mysql> create database test01;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mall               |

| mysql              |

| performance_schema |

| test               |

| test01             |

+--------------------+

6 rows in set (0.00 sec)

5、在从库查看

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mall               |

| mysql              |

| performance_schema |

| test              |

| test01             |

+--------------------+

6 rows in set (0.00 sec)

2.3配置MySQL02为主库（双主双从）

1、开启log-bin

[root@MySQL02 keepalived]# grep log-bin /etc/my.cnf

log-bin=mysql-bin

2、创建一个用户用于连接数据库

mysql> grant replication slave on *.* to 'mall'@'192.168.1.%' identified by '123456';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

+------+-------------+

| user | host        |

+------+-------------+

| root | 127.0.0.1   |

| mall | 192.168.1.% |

| root | 192.168.1.% |

| root | ::1         |

|      | MySQL01     |

| root | MySQL01     |

|      | localhost   |

| root | localhost   |

+------+-------------+

8 rows in set (0.00 sec)

3、查看mstat状态

mysql> show master status;        

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 |      346 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

2.4、配置MySQL01为从库

[root@MySQL01/]# mysql -u root -p'123456'<< EOF

> CHANGE MASTER TO

> MASTER_HOST='192.168.1.14',

> MASTER_PORT=3306,

> MASTER_USER='mall',

> MASTER_PASSWORD='123456',

> MASTER_LOG_FILE='mysql-bin.000005',

> MASTER_LOG_POS=346;

> EOF

2、启动slave

mysql> start slave;   

Query OK, 0 rows affected, 1 warning (0.00 sec)

3、查看slave状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.14

Master_User: mall

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 433

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 579

Relay_Master_Log_File: mysql-bin.000005

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: mysql.%,test.%,performance_schema.*

Last_Errno: 0

Last_Error: 

Skip_Counter: 0

Exec_Master_Log_Pos: 433

Relay_Log_Space: 729

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: 

Replicate_Ignore_Server_Ids: 

Master_Server_Id: 2

1 row in set (0.00 sec)

ERROR: 

No query specified

2.5、测试双主双从的效果

1、在MySQL01创建数据库

mysql> create database test04;

Query OK, 1 row affected (0.00 sec)

2、在MySQL02上查看

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mall               |

| mysql              |

| performance_schema |

| test               |

| test01             |

| test02             |

| test03             |

| test04             |

+--------------------+

9 rows in set (0.00 sec)

3、在MySQL02创建数据库

mysql> create database test05;

Query OK, 1 row affected (0.00 sec)

4、在MySQL01上查看

mysql> show databases;        

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mall               |

| mysql              |

| performance_schema |

| test               |

| test01             |

| test02             |

| test03             |

| test04             |

| test05             |

+--------------------+

10 rows in set (0.00 sec)