数据库keepalived部署文档

86565656

　　

一.mysql数据库RPM安装包安装

rpm –qa |grep -i mysql     //查看是否安装了数据库

rpm -e 文件名–nodeps      //卸载已安装的文件

tar –xzvf ./ MariaDB-10-linux.tar.gz  //解压MySQL rpm安装包

rpm -ivh *.rpm --force --nodeps     //安装MySQL数据库

rpm -qa|grep -i mariadb          //验证安装是否成功

安装成功显示如下:

[root@Test2 MariaDB-10-linux]# rpm -qa |grep -i  mariadb

MariaDB-Galera-server-10.0.14-1.el6.x86_64

MariaDB-cassandra-engine-10.0.15-1.el6.x86_64

MariaDB-common-10.0.15-1.el6.x86_64

MariaDB-test-10.0.15-1.el6.x86_64

MariaDB-oqgraph-engine-10.0.15-1.el6.x86_64

MariaDB-shared-10.0.15-1.el6.x86_64

MariaDB-client-10.0.15-1.el6.x86_64

MariaDB-server-10.0.15-1.el6.x86_64

MariaDB-Galera-test-10.0.14-1.el6.x86_64

启动数据库:

[root@Test2 MariaDB-10-linux]# servicemysql start

Starting MySQL.. SUCCESS!

添加root用户密码

[root@Test2 MariaDB-10-linux]# mysqladmin-uroot password 123

登录进入数据库

[root@Test2 MariaDB-10-linux]# mysql -uroot-p123

数据库主从模式

二.主数据库

1.创建一个复制用户，具有replication slave 权限。

MariaDB [(none)]> grant replicationslave on *.* to 'copy'@'192.168.98.130' identified by 'copy';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

2.编辑my.cnf文件

[mysqld]

key_buffer_size=2048M

server-id=132

log-bin=/var/lib/mysql/mysql-bin

log-slave-updates=1

3重启MySQL数据库

[root@Test0 ~]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

4设置读写锁

MariaDB [(none)]> flush tables with readlock;

Query OK, 0 rows affected (0.00 sec)

5得到binlog日志和偏移量

MariaDB [(none)]>  show master status;

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

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |

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

| mysql-bin.000002 |      312 |              |                  |

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

1 row in set (0.00 sec)

*******************************************************************************

6.备份数据库

[root@Test0 ~]# mysqldump -uroot -p123 test>test.sql

解锁

MariaDB [(none)]> unlock tables;

Query OK, 0 rows affected (0.00 sec)

************************************************************************************************************************************************************

2从数据库

1编辑配置文件

vi /etc/my.cnf.d/server.conf

添加

server-id=109

2.重启数据库

[root@Test2 MariaDB-10-linux]# servicemysql restart

Shutting down MySQL... SUCCESS!

Starting MySQL.. SUCCESS!

3.停止复制

MariaDB [(none)]> stop slave;

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

MariaDB [(none)]> change master tomaster_host='192.168.98.132', master_user='copy', master_password='copy',master_log_file='mysql-bin.0000013', master_log_pos=365;

出现错误:

MariaDB [(none)]> show slave status\G;

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

               Slave_IO_State:

                  Master_Host: 192.168.98.132

                  Master_User: copy

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:mysql-bin.0000013

         Read_Master_Log_Pos: 365

               Relay_Log_File:Test2-relay-bin.000001

                Relay_Log_Pos: 4

       Relay_Master_Log_File: mysql-bin.0000013

            Slave_IO_Running: No

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

              Relay_Log_Space: 248

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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 1236

                Last_IO_Error: Got fatal error1236 from master when reading data from binary log: 'Could not find first logfile name in binary log index file'

               Last_SQL_Errno: 0

               Last_SQL_Error:

Replicate_Ignore_Server_Ids:

            Master_Server_Id: 132

               Master_SSL_Crl:

          Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos:

1 row in set (0.00 sec)

ERROR: No query specified

解决方法:

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=365;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;

4检查主从是否同步正常

MariaDB [(none)]> show slave status\G;

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

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 192.168.98.132

                  Master_User: copy

                  Master_Port: 3306

                Connect_Retry: 60

             Master_Log_File:mysql-bin.000013

         Read_Master_Log_Pos: 365

               Relay_Log_File:Test2-relay-bin.000002

                Relay_Log_Pos: 535

       Relay_Master_Log_File: mysql-bin.000013

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

              Relay_Log_Space: 832

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

               Master_SSL_Crl:

          Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos:

1 row in set (0.00 sec)

ERROR: No query specified

5主数据库查看:

MariaDB [(none)]> show processlist\G;

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

     Id: 5

   User: root

   Host: localhost

     db: NULL

Command: Query

   Time: 0

  State: init

   Info: show processlist

Progress: 0.000

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

     Id: 9

   User: copy

   Host: 192.168.98.130:56411

     db: NULL

Command: Binlog Dump

   Time: 54

  State: Master has sent all binlog to slave; waiting for binlog to beupdated

   Info: NULL

Progress: 0.000

2 rows in set (0.00 sec)

ERROR: No query specified

测试:

创建数据库:

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

三.数据库keepalive

1下载keepalived软件

http://www.keepalived.org/software/keepalived-1.3.7.tar.gz

[root@Test0 usr]# cd keepalived-1.1.5

[root@Test0 keepalived-1.1.5]# ls

AUTHOR bin  ChangeLog  configure configure.in  CONTRIBUTORS  COPYING doc  genhash  INSTALL install-sh  keepalived  keepalived.spec  lib Makefile.in  README  TODO VERSION

[root@Test0 keepalived-1.1.5]# mkdir -p/usr/local/keepalived

2配置keppalived

[root@Test0 keepalived-1.1.5]# ./configure--prefix=/usr/local/keepalived

问题:1

configure: error:

  !!!OpenSSL is not properly installed on your system. !!!

  !!!Can not include OpenSSL headers files.            !!!

解决方法：

yum -y install openssl-devel

问题:2

configure: error: Popt libraries isrequired

解决方法:

[root@Test2 keepalived-1.1.5]# yum installpopt-devel

make

make install

root@Test0 keepalived-1.1.5]# make install

make -C keepalived install

make[1]: Entering directory`/usr/keepalived-1.1.5/keepalived'

install -d /usr/local/keepalived/sbin

install -m 700 ../bin/keepalived/usr/local/keepalived/sbin/

install -d /usr/local/keepalived/etc/init.d

install -m 755 etc/init.d/keepalived.init/usr/local/keepalived/etc/init.d/keepalived

install -d /usr/local/keepalived/etc/keepalived/samples

install -m 644etc/keepalived/keepalived.conf /usr/local/keepalived/etc/keepalived/

install -m 644 ../doc/samples/*/usr/local/keepalived/etc/keepalived/samples/

make[1]: Leaving directory`/usr/keepalived-1.1.5/keepalived'

make -C genhash install

make[1]: Entering directory`/usr/keepalived-1.1.5/genhash'

install -d /usr/local/keepalived/bin

install -m 755 ../bin/genhash/usr/local/keepalived/bin/

make[1]: Leaving directory`/usr/keepalived-1.1.5/genhash'

[root@Test0 keepalived-1.1.5]#

keepalive 安装成功

拷贝相关启动文件到系统目录

cp/usr/keepalived-1.3.7/keepalived/etc/init.d/keepalived  /etc/init.d/

cp /usr/local/sbin/keepalived /usr/sbin/

cp/usr/keepalived-1.3.7/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp -r /usr/keepalived-1.3.7/keepalived/etc/keepalived/keepalived.conf  /etc/keepalived/

3检查脚本

3.1判断mysql进程

编写检查MySQL进程是否存活shell脚本

E:\文档\linux服务器脚本\check_mysql.bash

1.   #!/bin/bash

2.   MYSQL=/usr/bin/mysql

3.   MYSQL_HOST=localhost

4.   MYSQL_USER=root

5.   MYSQL_PASSWORD=123

6.   CHECK_TIME=3

7.   #mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

8.   MYSQL_OK=1

9.   function check_mysql_health (){

10.  $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1

11.  if [ $? = 0 ] ;then

12.       MYSQL_OK=1

13.  else

14.       MYSQL_OK=0

15.  fi

16.       return $MYSQL_OK

17.  }

18.  while [ $CHECK_TIME -ne 0 ]

19.  do

20.       let "CHECK_TIME-=1"  ##（小提示这里我们采用的是let进行整数的运算当然您可以用expr，感觉let省去了$比较方便）

21.       check_mysql_health

22.       if [ $MYSQL_OK = 1 ] ; then

23.            CHECK_TIME=0

24.            exit 0

25.       fi

26.

27.       if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]

28.       then

29.            /etc/init.d/keepalived stop

30.       exit 1

31.       fi

32.       sleep 1

33.  done

3.2判断防火墙是否打开

1.   #! /bin/bash

2.

3.   /etc/init.d/iptables stop

4 keepalived配置文件

主库配置文件

1.   ! Configuration File for keepalived

2.

3.   global_defs {

4.      notification_email {

5.      yuchao_cacti@163.com

6.   }

7.

8.      notification_email_from yuchao_cacti@163.com

9.      smtp_server smtp.163.com

10.     smtp_connect_timeout 30

11.     router_id LVS_DEVEL

12.     vrrp_skip_check_adv_addr

13.     vrrp_strict

14.     vrrp_garp_interval 0

15.     vrrp_gna_interval 0

16.  }

17.  vrrp_script check_run {

18.     script "/home/sh/check_mysql.sh"

19.     interval 5

20.  }

21.

22.  vrrp_instance VI_1 {

23.      state MASTER

24.      interface eth0

25.      virtual_router_id 51

26.      priority 100

27.      advert_int 1

28.      authentication {

29.          auth_type PASS

30.          auth_pass 1111

31.      }

32.      virtual_ipaddress {

1.          192.168.98.155/24  dev eth0 label eth0:0

33.      }

34.      track_script {

35.      check_run

36.      }

37.           notify_master /home/sh/close_iptables.sh

38.      notify_backup /home/sh/close_iptables.sh

39.  }

备用配置文件

2.   ! Configuration File for keepalived

3.

4.   global_defs {

5.      notification_email {

6.      yuchao_cacti@163.com

7.   }

8.

9.      notification_email_from yuchao_cacti@163.com

10.     smtp_server smtp.163.com

11.     smtp_connect_timeout 30

12.     router_id LVS_DEVEL

13.     vrrp_skip_check_adv_addr

14.     vrrp_strict

15.     vrrp_garp_interval 0

16.     vrrp_gna_interval 0

17.  }

18.  vrrp_script check_run {

19.     script "/home/sh/check_mysql.sh"

20.     interval 5

21.  }

22.

23.  vrrp_instance VI_1 {

24.      state BACKUP

25.      interface eth0

26.      virtual_router_id 51

27.      priority 99

28.      advert_int 1

29.      authentication {

30.          auth_type PASS

31.          auth_pass 1111

32.      }

33.      virtual_ipaddress {

34.          192.168.98.155/24 dev eth0 label eth0:0

35.      }

36.      track_script {

37.      check_run

38.      }

39.      notify_master/home/sh/close_iptables.sh

40.      notify_backup /home/sh/close_iptables.sh

41.  }

42.  virtual_server 192.168.98.155 3306 {

43.       delay_loop 2

44.       lb_algo wrr

45.       lb_kind DR

46.       persistence_timeout 60

47.       protocol TCP

48.       real_server 192.168.98.130 3306 {

49.           weight 3

50.           TCP_CHECK {

51.               connect_timeout 10

52.               nb_get_retry 3

53.               delay_before_retry 3

54.               connect_port 3306

55.           }

56.       }

57.  }

配置完成

5重新启动keepalived 和mysql

service keepalived start

service mysql start

主设备和从设备做一样的操作

6查看是否正常绑定了虚拟的IP地址

master(主设备)ifconfig

eth0     Link encap:Ethernet  HWaddr00:50:56:26:31:26

         inet addr:192.168.98.132 Bcast:192.168.98.255 Mask:255.255.255.0

         inet6 addr: fe80::250:56ff:fe26:3126/64 Scope:Link

         UP BROADCAST RUNNING MULTICAST MTU:1500  Metric:1

         RX packets:15300 errors:0 dropped:0 overruns:0 frame:0

         TX packets:15135 errors:0 dropped:0 overruns:0 carrier:0

         collisions:0 txqueuelen:1000

         RX bytes:2745739 (2.6 MiB)  TXbytes:2846628 (2.7 MiB)

eth0:0    Link encap:Ethernet  HWaddr 00:50:56:26:31:26

          inet addr:192.168.98.155  Bcast:0.0.0.0 Mask:255.255.255.0

          UP BROADCAST RUNNING MULTICAST  MTU:1500 Metric:1

lo       Link encap:Local Loopback

         inet addr:127.0.0.1 Mask:255.0.0.0

         inet6 addr: ::1/128 Scope:Host

         UP LOOPBACK RUNNING MTU:16436  Metric:1

         RX packets:4322 errors:0 dropped:0 overruns:0 frame:0

         TX packets:4322 errors:0 dropped:0 overruns:0 carrier:0

         collisions:0 txqueuelen:0

         RX bytes:250079 (244.2 KiB)  TXbytes:250079 (244.2 KiB)

backup(从设备)ifconfig

eth0     Link encap:Ethernet  HWaddr00:0C:29:C3:8D:91

         inet addr:192.168.98.130 Bcast:192.168.98.255 Mask:255.255.255.0

         inet6 addr: fe80::20c:29ff:fec3:8d91/64 Scope:Link

         UP BROADCAST RUNNING MULTICAST MTU:1500  Metric:1

         RX packets:15867 errors:0 dropped:0 overruns:0 frame:0

         TX packets:7302 errors:0 dropped:0 overruns:0 carrier:0

         collisions:0 txqueuelen:1000

         RX bytes:1913375 (1.8 MiB)  TXbytes:1218646 (1.1 MiB)

lo       Link encap:Local Loopback

         inet addr:127.0.0.1 Mask:255.0.0.0

         inet6 addr: ::1/128 Scope:Host

         UP LOOPBACK RUNNING MTU:16436  Metric:1

         RX packets:4212 errors:0 dropped:0 overruns:0 frame:0

         TX packets:4212 errors:0 dropped:0 overruns:0 carrier:0

         collisions:0 txqueuelen:0

         RX bytes:235872 (230.3 KiB)  TXbytes:235872 (230.3 KiB)

问题3:

发现不能ping 通虚拟IP地址

解决方法:

关闭防火墙

问题4:不能绑定虚拟IP地址

解决方法:

1下载最新版本的keepalived软件

6测试MySQL自动切换

同时主设备和从设备的MySQL  keepalived服务

主设备关闭/开启

从另外一同网段主机访问VIP---mysql

[root@Test01 ~]# mysql -umaster -p1111-h192.168.98.155

mysql> show variables like  "server_id";

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

| Variable_name | Value |

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

| server_id     | 132  |

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

1 row in set (0.00 sec)

关闭主设备时,自动切换到备用数据库

mysql> show variables like  "server_id";

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

| Variable_name | Value |

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

| server_id     | 130  |

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

1 row in set (0.00 sec)

仅在本地测试环境中使用,生产环境还需要进一步完善