server-id = 2 #DB1和DB2的id 必须不同

log-bin=mysql-bin  #开启mysql的二进制日志功能
read_only=1             #定义主从复制，只读模式

relay-log = mysql-relay-bin #定义中继日志的命名格式

replicate-wild-ignore-table=mysql.% #不复制的数据库或表

replicate-wild-ignore-table=test.% #不复制的数据库或表
replicate-wild-ignore-table=information_schema.% #不复制的数据库或表

grant replication slave on *.*  to  "chen"@"172.16.1.101" identified by "123456";
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 106         |                    |　　　　　　　　　  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql > change master to \

        master_host="172.16.1.100",

        master_user="chen",

        master_password="123456",

        master_log_file="mysql-bin.000006",

        master_log_pos=106;

mysql > start slave;

mysql > show slave status;

mysql> show slave status\G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.1.100

                  Master_User: chen

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000009

          Read_Master_Log_Pos: 106

               Relay_Log_File: mysql-relay-bin.000021

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000009

             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.%,information_schema.%

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 106

              Relay_Log_Space: 551

              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

[root@db2 ~]# vim /etc/keepalived/keepalived.conf

 auth_type PASS

 auth_pass 1111

 }

 virtual_ipaddress {

 172.16.1.99

 }

}

virtual_server 172.16.1.99 3306 {

 delay_loop 2

 #lb_algo rr              #LVS算法，用不到，我们就关闭了

 #lb_kind DR              #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL

 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器

 protocol TCP

 real_server 172.16.1.101 3306 {  #检测本地mysql，backup也要写检测本地mysql

 weight 3

 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时，执行此脚本，杀死

keepalived实现切换

 TCP_CHECK {

 connect_timeout 3    #连接超时

 nb_get_retry 3      #重试次数

 delay_before_retry 3 #重试间隔时间

  }

}

"/etc/keepalived/keepalived.conf" 40L, 1292C written

[root@db2 ~]# vim /etc/keepalived/keepalived.conf

 auth_type PASS

 auth_pass 1111

 }

 virtual_ipaddress {

 172.16.1.99

 }

}

virtual_server 172.16.1.99 3306 {

 delay_loop 2

 #lb_algo rr              #LVS算法，用不到，我们就关闭了

 #lb_kind DR              #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL

 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器

 protocol TCP

 real_server 172.16.1.101 3306 {  #检测本地mysql，backup也要写检测本地mysql

 weight 3

 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时，执行此脚本，杀死

keepalived实现切换

 TCP_CHECK {

 connect_timeout 3    #连接超时

 nb_get_retry 3      #重试次数

 delay_before_retry 3 #重试间隔时间

  }

}

#!/bin/bash

#function:监视mysql主从服务器状态

#written by:alec

#

user=mysql

host=172.16.1.150

password=123

#

check_mysql() {

IO=`/usr/local/mysql/bin/mysql -u$user -p$password -h$host -e 'show slave status\G'|grep IO_Running|tr -s ' '|awk '{print $2}'|grep Yes|wc -l`

SQL=`/usr/local/mysql/bin/mysql -u$user -p$password -h$host -e 'show slave status\G'|grep SQL_Running|tr -s ' '|sed -n '1p'|awk '{print $2}'|grep Yes|wc -l`

if [ "$IO" -eq 1 ]&&[ "$SQL" -eq 1 ]

then

service keepalived status|grep pid

if [ $? -eq 0 ]

then

echo "服务已经启动"

else

service keepalived start

if [ $? -eq 0 ]

then

echo "服务启动成功"

else

echo "服务启动失败"

fi

fi

else

service keepalived status|grep pid

if [ $? -eq 0 ]

then

service keepalived stop

if [ $? -eq 0 ]

then

echo "服务关闭成功"

else

echo "服务关闭失败"

fi

else

echo "服务已经关闭"

fi

fi

}

main(){

check_mysql

}

main;