mysql master-master keepalive on centos6.3

mysql master-master keepalived

install mysql:

[root@m1 media]# yum install mysql-devel mysql-server

[root@m2 media]# yum install mysql-devel mysql-server

setup:

[root@m1 media]# service mysql start

[root@m2 media]# service mysqld start

m1

mysql> grant replication slave on *.* to 'copy1'@'192.168.160.132' identified by '123456';

m2

mysql> grant replication slave on *.* to 'copy2'@'192.168.160.131' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  

m1:

[root@m1 media]# service mysqld stop

[root@m1 media]# vi /etc/my.cnf

[root@m1 media]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

log-bin=mysql-bin

server-id=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@m1 media]# service mysqld start

[root@m2 media]# service mysqld stop

[root@m2 media]# vi /etc/my.cnf

[root@m2 media]# cat /etc/my.cnf

server-id=2

M1

mysql> flush tables with read lock\G

mysql> show master status\G

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

            File: mysql-bin.000001

        Position: 106

    Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

mysql>

M2

mysql> change master to master_host='192.168.160.132',master_user='copy2',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;

Query OK, 0 rows affected (0.06 sec)

mysql> start slave;

mysql> change master to master_host='192.168.160.131',master_user='copy1',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.02 sec)

review i/o slvae process

mysql> show processlist\G

     Id: 3

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 166

 State: Connecting to master

   Info: NULL

*************************** 2. row ***************************

     Id: 4

 State: Has read all relay log; waiting for the slave I/O thread to update it

*************************** 3. row ***************************

     Id: 5

   User: root

   Host: localhost

Command: Query

   Time: 0

 State: NULL

   Info: show processlist

3 rows in set (0.00 sec)

mysql> start slave' at line 1

     Id: 2

   Time: 83

    Id: 4

release lock:

mysql> unlock tables;

mysql> show slave status\G

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.160.132

                  Master_User: copy2

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 106

               Relay_Log_File: mysqld-relay-bin.000007

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000006

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

              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:

mysql> Ctrl-C -- exit!

Aborted

[root@m1 media]#

                  Master_Host: 192.168.160.131

                  Master_User: copy1

              Replicate_Do_DB:

              Relay_Log_Space: 1116

[root@m2 media]#

error:

1, user privileges

2,iptables selinux

show slave status\G

[root@m2 media]# service iptables stop

[root@m1 media]# service iptables stop

mysql> select Host,User,Password, Repl_slave_priv from user;

mysql> update user set host='%' where user='copy1';

[root@m1 media]# mysql -ucopy2 -p -h 192.168.160.132

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 34

Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

install keepalived

[root@m1 keepalived-1.2.2]# yum install gcc kernel-devel openssl-devel popt-devel

[root@m1 keepalived-1.2.2]# wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz

[root@m1 usr]# tar -vxf keepalived-1.2.2.tar.gz

[root@m1 usr]# cd keepalived-1.2.2

[root@m1 keepalived-1.2.2]# ./configure --prefix=/usr/keepalived && make && make install

the same to M1

setup

M1:

[root@m1 keepalived]# vim /etc/keepalived/mysql.sh

[root@m1 keepalived]# cat mysql.sh

#!/bin/bash

pkill keepalived

[root@m1 keepalived]#

[root@m1 keepalived]# chmod +x mysql.sh

[root@m1 keepalived-1.2.2]# mkdir /etc/keepalived

[root@m1 keepalived-1.2.2]# mv /usr/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

[root@m1 keepalived-1.2.2]# vim /etc/keepalived/keepalived.conf

[root@m1 keepalived]# cat keepalived.conf

! Configuration File for keepalived

global_defs {

   notification_email {

     [email protected]

   }

   notification_email_from [email protected]

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id LVS_DEVEL

}

vrrp_instance VI_1 {

    state BACKUP

    interface eth0

    virtual_router_id 51

    priority 100

    advert_int 1

    nopreempt

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    virtual_ipaddress {

        192.168.160.200

virtual_server 192.168.160.200 3306 {

    delay_loop 4

    lb_algo rr

    lb_kind dr

#    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

    real_server 192.168.160.131 3306 {

        weight 3

        notify_down /etc/keepalived/mysql.sh

        Tcp_CHECK{

                 connect_timeout 10

                 nb_get_retry3

                 delay_before_retry 3

                 connect_port 3306

                  }

#SSL_GET {

#            url {

#              path /

#              digest ff20ad2481f97b1754ef3e12ecd3a9cc

#            }

#              path /mrtg/

#              digest 9b3a0c85a887a256d6939da88aabd8cd

#            connect_timeout 3

#            nb_get_retry 3

#            delay_before_retry 3

#        }

#virtual_server 10.10.10.2 1358 {

#    delay_loop 6

#    lb_algo rr

#    lb_kind NAT

#    persistence_timeout 50

#    protocol TCP

#

#    sorry_server 192.168.200.200 1358

##    real_server 192.168.200.2 1358 {

#        weight 1

#        HTTP_GET {

#              path /testurl/test.jsp

#              digest 640205b7b0fc66c1ea91c463fac6334d

#              path /testurl2/test.jsp

#              path /testurl3/test.jsp

#    }

#    real_server 192.168.200.3 1358 {

#              digest 640205b7b0fc66c1ea91c463fac6334c

#}

#virtual_server 10.10.10.3 1358 {

#    delay_loop 3

#    real_server 192.168.200.4 1358 {

#    real_server 192.168.200.5 1358 {

[root@m2 keepalived]# vim /etc/keepalived/mysql.sh

[root@m2 keepalived]# cat mysql.sh

[root@m2 keepalived]#

[root@m2 keepalived]# chmod +x mysql.sh

[root@m2 keepalived-1.2.2]# mkdir /etc/keepalived

[root@m2 keepalived-1.2.2]# mv /usr/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

[root@m2 keepalived-1.2.2]# vim /etc/keepalived/keepalived.conf

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

    state MASTER

    priority 90

    delay_loop 6

    lb_kind DR

    real_server 192.168.160.132 3306 {

            connect_timeout 10

            nb_get_retry 3

            delay_before_retry 3

            connect_port 3306

           }

#        SSL_GET {

#            }

#    real_server 192.168.200.2 1358 {

#           url {

#           delay_before_retry 3

#   lb_kind NAT