keepalive+mysql 主主配置

1.   环境说明：

机器名	eth0	说明
server01	192.168.100.30/24	Mysql、keepalive
server02	192.168.100.31/24	Mysql、keepalive

 

2.   安装mysql

去官网下载mysql5.5的安装包，源码安装比较麻烦所以忽略

MySQL-client-5.5.29-1.el6.x86_64.rpm  MySQL-server-5.5.29-1.el6.x86_64.rpm MySQL-devel-5.5.29-2.el6.x86_64.rpm

出现错误提示：

[root@server01 ~]# rpm -ivhMySQL-server-5.5.29-1.el6.x86_64.rpm

Preparing...               ########################################### [100%]

       file /usr/share/mysql/charsets/Index.xml from install ofMySQL-server-5.5.29-1.el6.x86_64 conflicts with file from packagemysql-libs-5.1.61-4.el6.x86_64

这个是因为安装了5.1的工具包的问题卸载加参数-nodeps不检查依赖

[root@server01 ~]# rpm -e --nodepsmysql-libs

[root@server01 ~]# rpm -ivh  MySQL-server-5.5.29-1.el6.x86_64.rpm

[root@server01 ~]# rpm -ivh  MySQL-client-5.5.29-1.el6.x86_64.rpm

 

3.   配置mysql

查看mysql安装路径

[root@server01 mysql]# whereis mysql

mysql: /usr/bin/mysql /usr/lib64/mysql/usr/share/mysql /usr/share/man/man1/mysql.1.gz

创建配置文件夹可以直接放在/etc目录下

 [root@server01mysql]# cp my-medium.cnf  /etc/

[root@server01 mysql]# mv my-medium.cnfmy.cnf

[root@server01 mysql]# chown  mysql:mysql /etc/my.conf

配置my.cnf

master.cnf:

[mysqld]

server-id=1

log-bin=server01.log

relay-log-index=slave-relay-bin.index #配置双主模式所以服务器都要配置从的relay-log

relay-log=slave-relay-bin

innodb_flush_log_at_trx_commit=1

sync_binlog=1

注意：为了使用事务的InnoDB在复制中最大的持久性和一致性，你应该指定innodb_flush_log_at_trx_commit=1,sync_binlog=1 选项。

需要在从机的slave.cnf文件的【mysqld】部分增加server-id选项。server-id的值类似主机，必须是 1到2 的32次方之间的一个正整数，而且必须和主机的ID不一样。如果你设置多台从机，那么每台必须有别于主机和其他从机的唯一的server-id值。可以把server-id值认为是类似IP地址的东西：这些ID在复制服务器通信的时候标识了每台唯一的服务器实例。

slave.cnf:

[mysqld]

server-id=2

log-bin=server02-bin.log

relay-log-index=slave-relay-bin.index

relay-log=slave-relay-bin

innodb_flush_log_at_trx_commit=1

sync_binlog=1

 

设置mysql自启动

[root@server01 mysql]# chkconfig  mysql on

[root@server01 etc]# service  mysql start

[root@server01 ~]# mysqladmin -urootpassword password

4.    创建复制账户

[root@server01 ~]# mysql –uroot

mysql> create userrepl_user@192.168.100.31;

mysql> grant replication slave on *.* torepl_user@192.168.100.31 identified by 'password';

 

锁住主机，禁止用户写入mysql

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

不改变mysql用户，复制test测试同步库

mysqldump -urrot -password test >test.sql

从机启动会解锁

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec

拷贝test.sql到从机

从机执行

mysql -uroot -ppassword test < test.sql

回到主机查看当前日志偏差值

mysql> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB|

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

| server01-bin.000003 |     4921 |              |                  |

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

1 row in set (0.00 sec)

 

在从机上初始化复制了。在从机上执行以下命令

 

mysql> slave stop;

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

 

mysql> change master toMASTER_HOST='192.168.100.30',

   -> MASTER_USER='repl_user',

   -> MASTER_PASSWORD='password',

   -> MASTER_LOG_FILE='server01-bin.000003',

   -> MASTER_LOG_POS= 2877;

Query OK, 0 rows affected (0.03 sec)

 

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

 

查看状态

mysql> show slave status\G

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

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 192.168.100.30

                  Master_User: repl_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:server01-bin.000003

         Read_Master_Log_Pos: 4921

               Relay_Log_File:slave-relay-bin.000002

                Relay_Log_Pos: 256

       Relay_Master_Log_File: server01-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: 4921

              Relay_Log_Space: 412

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

1 row in set (0.00 sec)

5.    验证从机复制特性

主机数据库内容如下：

mysql> use test

Database changed

mysql> select * from new

   -> ;

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

| id | col1 | col2 |

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

|  1| hah  | test |

|  2| s    | b    |

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

2 rows in set (0.00 sec)

从机数据库内容：

Database changed

mysql> select * from new;

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

| id | col1 | col2 |

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

|  1| hah  | test |

|  2| s    | b    |

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

2 rows in set (0.02 sec)

 

回到主机修改数据:

mysql> update new setcol1='haha',col2='change' where id='1';

Query OK, 1 row affected (0.04 sec)

Rows matched: 1  Changed: 1 Warnings: 0

 

mysql> select * from new

   -> ;

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

| id | col1 | col2   |

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

|  1| haha | change |

|  2| s    | b      |

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

2 rows in set (0.00 sec)

查看从机是否修改：

mysql> select * from new;

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

| id | col1 | col2   |

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

|  1| haha | change |

|  2| s    | b      |

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

2 rows in set (0.00 sec)

观察得出从机已经从主机同步到了数据。

 

6.   主主模式配置

现在的设置为192.168.100.30为主机，192.168.100.31为从机，

按照上述设置再将192.168.100.31设为主机，192.168.100.30设为从机。

设置完成后两台机器既相互为主机也相互为备机，都可以写入数据并同步到对方机器。

 

7.    安装keepalived（两台均安装）

安装依赖包

[root@server01 ~]# yum install -y gcc gcc+gcc-c++ openssl openssl-devel popt-devel

下载

[root@server01 ~]# wgethttp://www.keepalived.org/software/keepalived-1.2.7.tar.gz

[root@server01 ~]# tar -xzf  keepalived-1.2.7.tar.gz

[root@server01 ~]#./configure  --prefix=/usr/local/keeplived

[root@server01 ~]# make

[root@server01 ~]# make install

[root@server01 ~]# cp/usr/local/keeplived/sbin/keepalived /usr/bin/

[root@server01 ~]# cp /usr/local/keeplived/etc/sysconfig/keepalived  /etc/sysconfig/

[root@server01 ~]# cp/usr/local/keeplived/etc/rc.d/init.d/keepalived /etc/init.d/

[root@server01 ~]# mkdir /etc/keepalived

创建 vi keepalived.conf如下内容

! Configuration File for keepalived

global_defs {

   notification_email {

       cmwu@biencloud.com

    }

   

   notification_email_from haha@biencloud.com

   smtp_server 127.0.0.1

   router_id LVS_DEVEL

}

 

vrrp_instance VI_1 {

   state BACKUP

   interface eth0

   virtual_router_id 50

   priority 100

   advert_int 1

   nopreempt

   authentication {

       auth_type PASS

       auth_pass 1111

    }

 

virtual_ipaddress {

   192.168.100.40

    }

}

192.168.100.31上的配置文件为

global_defs {

   notification_email {

       cmwu@biencloud.com

    }

   

   notification_email_from haha@biencloud.com

   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 50

   priority 90

   advert_int 1

   authentication {

       auth_type PASS

       auth_pass 1111

    }

 

virtual_ipaddress {

   192.168.100.40

    }

}

[root@server01 ~]# ping 192.168.100.40

PING 192.168.100.40 (192.168.100.40) 56(84)bytes of data.

64 bytes from 192.168.100.40: icmp_seq=1ttl=64 time=0.262 ms

[root@server02 ~]# ping 192.168.100.40

PING 192.168.100.40 (192.168.100.40) 56(84)bytes of data.

64 bytes from 192.168.100.40: icmp_seq=1ttl=64 time=0.064 ms

两台服务器均可以ping通服务器。

Ip addr可以看到虚拟IP已经绑定到网卡上了