基于keepalived实现mysql双主热备

基于keepalived实现mysql双主热备

一、环境说明

Ø Red Hat Enterprise Linux Server release 6.2或CentOS 6.4或Suse11sp1

Ø MySQL-5.1.66以上

二、单机mysql

1、安装单机mysql

1、操作说明

数据库安装需在root用户下进行操作，目前搭建mysql双主热备高可用，现提供两台服务器，服务器信息如下：10.0.2.235；10.0.2.236。首先需要在两台服务器上搭建单机版的mysql数据库。单机版的mysql数据库需要用到的安装包如下：

MySQL-server-5.5.46-1.linux2.6.x86_64.rpm，

MySQL-client-5.5.46-1.linux2.6.x86_64.rpm

2、操作指令

rpm -qa | grep mysql  #检查系统残余mysql

rpm -e --nodeps *  #如果有将检查出来的rpm依赖项删除

rpm –ivh MySQL-server-5.5.46-1.linux2.6.x86_64.rpm  #安装mysql服务端

rpm –ivh MySQL-client-5.5.46-1.linux2.6.x86_64.rpm  #安装mysql客户端

mysql_install_db --user=mysql --datadir=/usr/local/mysql/var   #安装插件

chown -R mysql:mysql /var/lib/mysql  #修改文件夹mysql用户权限

3、预期结果

控制台将打印如下内容：

linux-dk41:~ # rpm -ivh MySQL-server-5.5.46-1.linux2.6.x86_64.rpm

warning: MySQL-server-5.5.46-1.linux2.6.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 5072e1f5

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

file /usr/bin/mysqlhotcopy from install of MySQL-server-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqlhotcopy.1.gz from install of MySQL-server-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

。。。

。。。。。。

。。。、、、、、、、、

file /usr/share/mysql/my-small.cnf from install of MySQL-server-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-5.5.31-0.7.10.x86_64

file /usr/share/mysql/mysql_system_tables.sql from install of MySQL-server-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-5.5.31-0.7.10.x86_64

file /usr/share/mysql/mysql_system_tables_data.sql from install of MySQL-server-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-5.5.31-0.7.10.x86_64

file /usr/share/mysql/mysql_test_data_timezone.sql from install of MySQL-server-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-5.5.31-0.7.10.x86_64

linux-dk41:~ # rpm -ivh MySQL-client-5.5.46-1.linux2.6.x86_64.rpm

warning: MySQL-client-5.5.46-1.linux2.6.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 5072e1f5

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

file /usr/bin/mysql from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/bin/mysqladmin from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/bin/mysqlcheck from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/bin/mysqldump from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/bin/mysqlimport from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/bin/mysqlshow from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysql.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqladmin.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqlcheck.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqldump.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqlimport.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqlshow.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-client-5.5.31-0.7.10.x86_64

file /usr/bin/mysqlbinlog from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-5.5.31-0.7.10.x86_64

file /usr/share/man/man1/mysqlbinlog.1.gz from install of MySQL-client-5.5.46-1.linux2.6.x86_64 conflicts with file from package mysql-5.5.31-0.7.10.x86_64

4、配置文件

1、my.cnf

首先检查在/etc路径下是否存在my.cnf配置文件

如果没有将/usr/share/mysql/下任意的my-*.cnf复制为/etc/my.cnf

cp /usr/share/mysql/my-*.cnf  /etc/my.cnf

 

2、配置单机mysql

1、操作说明

修改mysql配置文件实现mysql默认没有密码启动然后通过客户端登陆mysql再通过sql命令修改密码。这里已经包含启动与验证部分。

2、操作指令

vim /etc/my.cnf   #在my.cnf配置文件中添加参数--skip-grant-tables

service mysql restart  #重新启动mysql服务

mysql  #登陆mysql服务

show databases;  #显示当前数据库信息

use mysql;  #使用mysql数据库

select Host,User,authentication_string from user;  #查询当前用户信息

grant all on *.* to 'root'@'%';  #创建远程访问root账号

注意：如果没有Host为localhost或者127.0.0.1的root用户同样创建一个

grant all on *.* to 'root'@'127.0.0.1';  #创建本地访问root账号

update set password=PASSWORD(‘123456’) where User=’root’;  #修改访问密码

flush privileges;  #刷新权限

quit #退出

vim /etc/my.cnf  #在my.cnf中去掉参数--skip-grant-tables

service mysql restart  #重启mysql服务

mysql -uroot -p  #手动输入密码登陆mysql

3、预期结果

通过命令service mysql status 能够查看mysql正常启动表示启动成功

通过命令mysql -uroot -p 能成功登陆

 

                                      

三、双机mysql

1、操作说明

模拟两台机器 IP：10.0.2.235、IP：10.0.2.236

分别在两台服务器上安装单机版MYSQL数据库后，启动mysql数据库

在10.0.2.235、10.0.2.236服务器的数据库上分别创建数据库名： hcicpm。

在数据库上创建表和导入初始数据。

注意：以上两步确保两个数据库创建的数据库名称和数据结构相同即可。

注意：创建的数据库就是作为数据同步的数据库，如果想做多个数据库数据同步，需要创建多个数据库。

数据库的用户名和密码必须相同。

2、操作指令

如果在创建单机数据库时已经创建了root用户并用root用户作为登陆用户，下面的创建新用户并赋予权限的可以略过。

登陆10.0.2.235服务器上的数据库

linux-dk41:~ # mysql -uroot -p

Enter password:

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

Your MySQL connection id is 6

Server version: 5.5.31-log Source distribution

 

Copyright (c) 2000, 2013, 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.

mysql> create user 'backup'@'%' IDENTIFIED BY '123456';         //创建数据同步用户和密码：backup/123456

Query OK, 0 rows affected (0.00 sec)

 

mysql>  Grant replication slave on *.* to backup@'10.0.2.236' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

登陆10.0.2.236服务器上的数据库

linux-dk41:/home/hcicloud/sql # mysql -uroot -p

Enter password:

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

Your MySQL connection id is 4

Server version: 5.5.31-log Source distribution

 

Copyright (c) 2000, 2013, 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.

 

mysql> create user 'backup'@'%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

 

mysql>  Grant replication slave on *.* to backup@'10.0.2.235' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

2.1第一次主从搭建：

A、登陆10.0.2.235服务器（master）：

[root@localhost ~]# vim  /etc/my.cnf

 

因为使用的是rpm包方式安装的mysql，需要将/user/share/mysql目录下的my-medium.cnf文件复制到/etc路径下并重新命名为my.cnf。

在[mysqld]下面添加如下红色字内容：

34 table_open_cache = 64

 35 sort_buffer_size = 512K

 36 net_buffer_length = 8K

 37 read_buffer_size = 256K

 38 read_rnd_buffer_size = 512K

 39 myisam_sort_buffer_size = 8M

 40 innodb_file_per_table=1

 41 # Don't listen on a TCP/IP port at all. This can be a security enhancement,

 42 # if all processes that need to connect to mysqld run on the same host.

 43 # All interaction with mysqld must be made via Unix sockets or named pipes.

 44 # Note that using this option without enabling named pipes on Windows

 45 # (via the "enable-named-pipe" option) will render mysqld useless!

 46 #

 47 #skip-networking

 48

 49 # Replication Master Server (default)

 50 # binary logging is required for replication

 51 server-id=1      #服务ID 主服务器为1， 从服务器为2

 52 log-bin=mysql-bin #在数据同步时，同步二进制日志

 53 binlog-do-db=hcicpm #同步数据的数据库名称，有几个，就添加几行

 54 log-bin-index=mysql-bin.index

 55

 56

 57 # binary logging format - mixed recommended

 58 binlog_format=mixed

 59

 60 # required unique id between 1 and 2^32 - 1

 61 # defaults to 1 if master-host is not set

 62 # but will not function as a master if omitted

 63

 64 # Replication Slave (comment out master section to use this)

 65 #

 66 # To configure this host as a replication slave, you can choose between

 67 # two methods :

 68 #

按[Esc] 输入【：wq】 按【Enter】

B、登陆10.0.2.236服务器（slave）：

[root@localhost ~]# vim  /etc/my.cnf

 

在[mysqld]下面添加如下内容：

32 key_buffer_size = 16M

 33 max_allowed_packet = 1M

 34 table_open_cache = 64

 35 sort_buffer_size = 512K

 36 net_buffer_length = 8K

 37 read_buffer_size = 256K

 38 read_rnd_buffer_size = 512K

 39 myisam_sort_buffer_size = 8M

 40 innodb_file_per_table=1

 41 # Don't listen on a TCP/IP port at all. This can be a security enhancement,

 42 # if all processes that need to connect to mysqld run on the same host.

 43 # All interaction with mysqld must be made via Unix sockets or named pipes.

 44 # Note that using this option without enabling named pipes on Windows

 45 # (via the "enable-named-pipe" option) will render mysqld useless!

 46 #

 47 #skip-networking

 48

 49 # Replication Master Server (default)

 50 # binary logging is required for replication

 51

 52 server-id=2    #服务ID 主服务器为1， 从服务器为2

 53 log-bin=mysql-bin

 54 binlog-do-db=hcicpm

 55 log-bin-index=mysql-bin.index

 56

 57 # binary logging format - mixed recommended

 58 binlog_format=mixed

 59

 60 # required unique id between 1 and 2^32 - 1

 61 # defaults to 1 if master-host is not set

 62 # but will not function as a master if omitted

 63

 64 # Replication Slave (comment out master section to use this)

 65 #

 66 # To configure this host as a replication slave, you can choose between

按[Esc] 输入【：wq】 按【Enter】

 

C、分别重启主从的MYSQL数据库。

使用命令：

[root@localhost ~]# service mysql restart

Shutting down MySQL.....                                   [确定]

Starting MySQL....                                         [确定]

 

D、登陆10.0.2.235服务器mysql

mysql> show master status;

记录结果中的：File和Position的值。例如为：mysql-bin.000008和107，后面操作会用到。

 

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000008 |      107 | hcicpm       |                  |

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

1 row in set (0.00 sec)

E、登陆10.0.2.236服务器mysql

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CHANGE MASTER TO  MASTER_HOST="10.0.2.235",MASTER_USER="backup", MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000008",MASTER_LOG_POS=107;

Query OK, 0 rows affected (0.01 sec)

 

mysql>

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.2.235

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000008

          Read_Master_Log_Pos: 107

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 253

        Relay_Master_Log_File: mysql-bin.000008

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

              Relay_Log_Space: 410

              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)

 

ERROR:

No query specified

出现如下：

            Slave_IO_Running: Yes        出现：同步IO 状态显示：YES 即可

            Slave_SQL_Running: Yes出现：同步服务正在运行 状态显示：yes

则说明同步成功。

2.2第二次主从搭建：

此次把236当做主，235当做从。

A、登陆10.0.2.236服务器mysql

mysql> show master status;

记录结果中的：File和Position的值。例如为：mysql-bin.000008和107，后面操作会用到.

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000008 |      107 | hcicpm       |                  |

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

1 row in set (0.00 sec)

B、登陆10.0.2.235服务器mysql

mysql> slave stop;

Query OK, 0 rows affected (0.01 sec)

 

mysql> CHANGE MASTER TO MASTER_HOST="10.0.2.236",MASTER_USER="backup", MASTER_PASSWORD="123456",MASTER_LOG_FILE="mysql-bin.000008",MASTER_LOG_POS=107;

Query OK, 0 rows affected (0.01 sec)

 

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.2.236

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000008

          Read_Master_Log_Pos: 107

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 253

        Relay_Master_Log_File: mysql-bin.000008

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

              Relay_Log_Space: 410

              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出现如下：

          Slave_IO_Running: Yes        出现：同步IO 状态显示：YES 即可

          Slave_SQL_Running: Yes出现：同步服务正在运行 状态显示：yes

则说明同步成功。

 

C、实验：在任意一台库中创建表，检查另一台库中是否自动创建了表。

同步时间取决于网络延时性 最慢5到10秒。

 

四、部署keepalived

1、安装keepalived

shell> cd /usr/local/src

shell> wget http://www.keepalived.org/software/keepalived-1.2.24.tar.gz

shell> tar -zxvf keepalived-1.2.24.tar.gz

shell> cd keepalived-1.2.24

shell> ./configure --prefix=/usr/local/keepalived

shell> make && make install

2、配置keepalived

安装完成后，进入安装目录的etc目录下，将keepalived相应的配置文件拷贝到系统相应的目录当中。keepalived启动时会从/etc/keepalived目录下查找keepalived.conf配置文件，如果没有找到则使用默认的配置。/etc/keepalived目录安装时默认是没有安装的，需要手动创建。配置文件目录结构如下所示：

shell> tree -l /usr/local/keepalived/etc-- keepalived

|   |-- keepalived.conf

|   `-- samples

|       |-- keepalived.conf.status_code

|       |-- keepalived.conf.track_interface

|       |-- keepalived.conf.vrrp

|       |-- 。。。

|-- rc.d

|   `-- init.d

|       `-- keepalived

`-- sysconfig

    `-- keepalived

分别对应系统目录（忽略samples目录）：

/etc/keepalived/keepalived.conf

/etc/rc.d/init.d/keepalived

/etc/sysconfig/keepalived

将配置文件拷贝到系统对应的目录下：

shell> mkdir /etc/keepalived

shell> cp /usr/local/keepalived/etc/keepalived.conf /etc/keepalived/keepalived.conf

shell> cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/keepalived

shell> cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived

设置keepalived服务开机启动：

shell> chkconfig keepalived onshell> service keepalived start   #启动服务shell> service keepalived stop    #停止服务shell> service keepalived restart #重启服务

3、mysql与keepalived

1、操作说明

在mysql所在两台服务器上搭建keepalive服务，并修改配置文件实现监听mysql的服务。

分别修改两台keepalive.conf配置文件，主备之间的priority参数不一致

2、配置文件

2.1 keepalive.conf

linux-dk41:/etc/keepalived # mv keepalived.conf keepalived.conf.bak

linux-dk41:/etc/keepalived # vim keepalived.conf

vrrp_script chk_mysql {

    script "/etc/keepalived/chk_mysql.sh"   #监控脚本

    interval 3

}

 

vrrp_instance VI_1 {

    state BACKUP

    interface eth1                       #2.本地业务网卡名称（ip a查）

    virtual_router_id 213              #3. 主备此ID保持一致（设置与tomcat不同的ID）

    priority 101                         #4.主备设置不同的，10.0.2.235（主）高于备

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass mysql

    }

    virtual_ipaddress {

        10.0.2.242                        #5.虚拟IP1地址

    }

    track_script {

        chk_mysql                      #执行上面的检查

    }

}

2.2 chk_mysql.sh

mysql健康检查脚本内容如下

通过touch命令创建此文件

需要赋予chk_mysql.sh可执行权限

将此脚本放置/etc/keepalived/路径下即可。

注意赋予可执行权限。

3、启动服务

启动服务后，通过查看网卡信息，验证keepalive服务是否模拟出虚ip。

linux-dk41:/etc/keepalived # service keepalived start

Starting Keepalived daemon                                                 done

查看网卡上多出一个虚拟ip用于mysql

linux-dk41:/etc/keepalived # ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN

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

    inet 127.0.0.1/8 brd 127.255.255.255 scope host lo

    inet 127.0.0.2/8 brd 127.255.255.255 scope host secondary lo

    inet6 ::1/128 scope host

       valid_lft forever preferred_lft forever

2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

    link/ether 00:0c:29:f0:dc:ae brd ff:ff:ff:ff:ff:ff

    inet 10.0.2.235/24 brd 10.0.2.255 scope global eth1

    inet 10.0.2.242/32 scope global eth1

    inet6 fe80::20c:29ff:fef0:dcae/64 scope link

       valid_lft forever preferred_lft forever

3: br0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UNKNOWN

    link/ether 86:81:10:25:80:10 brd ff:ff:ff:ff:ff:ff

    inet6 fe80::8481:10ff:fe25:8010/64 scope link

 

4、验证keepalive

搭建成功keepalive并重启后，通过service mysql stop命令宕机掉其中一台mysql,因为keepalive的检测周期配置的为3秒，故障转移时间取决于网络状况，所以再需要提供5-10秒故障发现并转移的时间。通过ip a命令查看两台服务器虚ip的情况，正常情况下虚ip会飘到活着mysql服务所在的服务器。

验证成功后，通过service mysql start恢复mysql的服务。

 

附keepalived配置文件（tomcat双机与mysql双机基于一个keepalived双虚ip实现）：