天天看點

資料庫keepalived部署文檔

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

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;

[mysqld]

key_buffer_size=2048M

server-id=132

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

log-slave-updates=1

[root@Test0 ~]# service mysql restart

Shutting down MySQL.. SUCCESS!

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

MariaDB [(none)]>  show master status;

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

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB |

| mysql-bin.000002 |      312 |              |                  |

1 row in set (0.00 sec)

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

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

解鎖

MariaDB [(none)]> unlock tables;

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

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

添加

server-id=109

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

Shutting down MySQL... SUCCESS!

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:

ERROR: No query specified

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

MariaDB [(none)]> start slave;

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

               Slave_IO_State: Waiting formaster to send event

             Master_Log_File:mysql-bin.000013

               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

                  Last_Error:

              Relay_Log_Space: 832

       Seconds_Behind_Master: 0

                Last_IO_Errno: 0

                Last_IO_Error:

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

     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

 Command: Binlog Dump

   Time: 54

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

   Info: NULL

2 rows in set (0.00 sec)

測試:

建立資料庫:

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

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

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

configure: error:

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

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

 yum -y install openssl-devel

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/

編寫檢查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   

1.   #! /bin/bash  

2.     

3.   /etc/init.d/iptables stop  

主庫配置檔案

1.   ! Configuration File for keepalived  

3.   global_defs {  

4.      notification_email {  

5.      [email protected]  

6.   }  

7.     

8.      notification_email_from [email protected]  

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.      [email protected]  

7.   }  

8.     

9.      notification_email_from [email protected]  

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.  }  

配置完成

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

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

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

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

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

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

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

發現不能ping 通虛拟IP位址

關閉防火牆

1下載下傳最新版本的keepalived軟體

同時主裝置和從裝置的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  |

關閉主裝置時,自動切換到備用資料庫

| server_id     | 130  |

僅在本地測試環境中使用,生産環境還需要進一步完善

本文轉自 Barron1 51CTO部落格,原文連結:http://blog.51cto.com/13172370/1973837,如需轉載請自行聯系原作者