MariaDB+Keepalived雙主高可用配置MySQL-HA_MySQL

來源：懂視網(wǎng) 責(zé)編：小采時間：2020-11-09 18:39:56

MariaDB+Keepalived雙主高可用配置MySQL-HA_MySQL

MariaDB+Keepalived雙主高可用配置MySQL-HA_MySQL:MariaDB bitsCN.com 利用keepalived構(gòu)建高可用MySQL-HA，保證兩臺MySQL數(shù)據(jù)的一致性，然后用keepalived實(shí)現(xiàn)虛擬VIP，通過keepalived自帶的服務(wù)監(jiān)控功能來實(shí)現(xiàn)MySQL故障時自動切換。硬件拓?fù)淙缦拢?VIP：192.168.1.200mysql1:19

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

導(dǎo)讀MariaDB+Keepalived雙主高可用配置MySQL-HA_MySQL:MariaDB bitsCN.com 利用keepalived構(gòu)建高可用MySQL-HA，保證兩臺MySQL數(shù)據(jù)的一致性，然后用keepalived實(shí)現(xiàn)虛擬VIP，通過keepalived自帶的服務(wù)監(jiān)控功能來實(shí)現(xiàn)MySQL故障時自動切換。硬件拓?fù)淙缦拢?VIP：192.168.1.200mysql1:19

MariaDB

bitsCN.com 利用keepalived構(gòu)建高可用MySQL-HA，保證兩臺MySQL數(shù)據(jù)的一致性，然后用keepalived實(shí)現(xiàn)虛擬VIP，通過keepalived自帶的服務(wù)監(jiān)控功能來實(shí)現(xiàn)MySQL故障時自動切換。硬件拓?fù)淙缦拢?VIP：192.168.1.200mysql1:192.168.1.201mysql2:192.168.1.202 操作系統(tǒng)：CentOS release 6.3（32位）MySQL版本：MariaDB 5.5.31 Stable下載地址(64位請下載64版本)：https://downloads.mariadb.org/f/mariadb-5.5.31/kvm-tarbake-jaunty-x86/mariadb-5.5.31.tar.gz/from/http:/mirrors.scie.in/mariadbKeepalived版本：Version 1.2.7下載地址：http://www.keepalived.org/software/keepalived-1.2.7.tar.gz 一、配置Centos運(yùn)行環(huán)境：執(zhí)行：rpm -qa|grep mysqlrpm -e mysqlyum -y remove mysql-server mysqlyum -y remove php-mysql移除系統(tǒng)自帶的mysql yum -y install yum-fastestmirroryum -y update更新系統(tǒng)軟件； rm -rf /etc/localtimeln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime yum install -y ntpntpdate -d cn.pool.ntp.orgdate設(shè)置時區(qū)并同步系統(tǒng)時間 #Disable SeLinuxif [ -s /etc/selinux/config ]; thensed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/configfildconfig關(guān)閉安全增強(qiáng) cat >>/etc/security/limits.conf<>/etc/sysctl.conf<

2、安裝：[root@localhost down]# tar -zxvf keepalived-1.2.7.tar.gz[root@localhost down]# cd keepalived-1.2.7

[root@localhost keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived 安裝到/usr/local/keepalived目錄下;至此keepalived安裝完畢。四、分別在201和202兩臺機(jī)器上都重復(fù)二，三安裝好mariaDB和keepalived。五、配置201數(shù)據(jù)庫服務(wù)器： 1、設(shè)置mariaDB數(shù)據(jù)庫配置文件：[root@localhost /]# vi /etc/my.cnf 確保/etc/my.cnf中有如下參數(shù)，沒有的話需手工添加，并重啟mysql服務(wù)。[mysqld]
log-bin=mysql-bin #啟動二進(jìn)制文件
server-id=1 #服務(wù)器ID

設(shè)置完畢啟動mariaDB服務(wù)器 [root@localhost /]# service mariamysql start

2、登錄mysql,然后在增加一個賬號專門用于同步，如下：

[root@localhost /]# /usr/local/mariamysql/bin/mysql -uroot -p #初始密碼為空到Enter password:處直接回車即可

MariaDB [(none)]> grant replication slave on *.* to 'backup'@'192.168.1.202' identified by 'backup'; flush privileges;

顯示master狀態(tài)：MariaDB [(none)]> show master status;

記錄下File和Position然后在202上面設(shè)置從201同步。
六、配置202數(shù)據(jù)庫服務(wù)器： [root@localhost /]# vi /etc/my.cnf 確保/etc/my.cnf中有如下參數(shù)，沒有的話需手工添加，并重啟mysql服務(wù)。[mysqld]
log-bin=mysql-bin #啟動二進(jìn)制文件
server-id=10（此處要設(shè)置的跟201不同）#服務(wù)器ID

設(shè)置完畢啟動mariaDB服務(wù)器。[root@localhost /]# service mariamysql start登錄數(shù)據(jù)庫：[root@localhost /]# /usr/local/mariamysql/bin/mysql -uroot -p 輸入：MariaDB [(none)]> change master to master_host='192.168.1.201',master_user='backup',master_password='backup',master_log_file='mysql-bin.000010',master_log_pos=245;注意：245對應(yīng)上面在201上面記下的Position,mysql-bin.000010對應(yīng)201上面記錄的File

執(zhí)行成功后，輸入命令顯示從庫狀態(tài)：MariaDB [(none)]> show slave status /G;

Slave_IO_Running: Yes Slave_SQL_Running: Yes兩項(xiàng)都顯示Yes時說明從201同步數(shù)據(jù)成功。至此201為主202為從的主從架構(gòu)數(shù)據(jù)設(shè)置成功！七、設(shè)置201和202互為主從: 1、202機(jī)器上增加一個帳號專門用于同步數(shù)據(jù)：MariaDB [(none)]> grant replication slave on *.* to 'backup'@'192.168.1.201' identified by 'backup'; flush privileges;

2、顯示202做為主庫時的狀態(tài)：MariaDB [(none)]> show master status;

3、在201數(shù)據(jù)庫服務(wù)器上：MariaDB [(none)]> change master to master_host='192.168.1.202',master_user='backup',master_password='backup',master_log_file='mysql-bin.000005',master_log_pos=5005;

注意：5005對應(yīng)上面在202上面記下的Position,mysql-bin.000005對應(yīng)202上面記錄的File顯示狀態(tài)：MariaDB [(none)]> show slave status /G;

Slave_IO_Running: Yes Slave_SQL_Running: Yes兩項(xiàng)都顯示Yes時說明從202同步數(shù)據(jù)成功。至此201、202互為主從設(shè)置成功！可以試試在這兩臺服務(wù)器上任何一臺增加一個數(shù)據(jù)庫，并建個表，增加一些數(shù)據(jù)看看，互為主從同步的狀態(tài)是否成功！首先在201上面：MariaDB [(none)]> create database mysqltest;MariaDB [(none)]> use mysqltest;MariaDB [mysqltest]> create table user(id int(5),name char(10));MariaDB [mysqltest]> insert into user values (00001,'zhangsan');在202上面驗(yàn)證一下：MariaDB [(none)]> use mysqltest;MariaDB [mysqltest]> select * from user;

會發(fā)現(xiàn)201上面的數(shù)據(jù)已經(jīng)自動同步到202上面了同樣在202上面:MariaDB [mysqltest]> insert into user values (00002,'wander'); 在201上面驗(yàn)證一下：MariaDB [mysqltest]> select * from user;

互為主從結(jié)構(gòu)設(shè)置完畢注意：如果同步不成功，首先要確保服務(wù)器3306端口打開的。centos可以用service iptables stop關(guān)閉防火墻。八、利用keepalived實(shí)現(xiàn)高可用 keepalived實(shí)現(xiàn)虛擬IP，通過keepalived自帶的服務(wù)監(jiān)控功能來實(shí)現(xiàn)MySQL故障時自動切換； 1、keepalived設(shè)置：201服務(wù)器上面，編輯keeplaived.conf配置文件：[root@localhost /]# vi /usr/local/keepalived/etc/keepalived/keepalived.conf

配置文件內(nèi)容如下:! Configuration File for keepalived

global_defs {
router_id mysql-ha
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 201
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.1.200
}
}

virtual_server 192.168.1.200 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.1.201 3306 {
weight 1
notify_down /usr/local/keepalived/etc/keepalived/mysql.sh
TCP_CHECK {
connect_port 3306
connect_timeout 3
nb_get_retry 2
delay_before_retry 1
}
}
}
~ 編輯mysql服務(wù)停止后的切換腳本：mysql.sh[root@localhost /]# vi /usr/local/keepalived/etc/keepalived/mysql.sh內(nèi)容如下：#!/bin/bash
pkill keepalived 2、啟動201上面的keepalived[root@localhost /]# /usr/local/keepalived/sbin/keepalived -f /usr/local/keepalived/etc/keepalived/keepalived.conf -D查看：啟動成功后會有三個keepalived進(jìn)程此是在任一局域機(jī)器上面ping 192.168.1.200發(fā)現(xiàn)已經(jīng)可以ping通，并且用192.168.1.200這個IP也能夠連接到數(shù)據(jù)庫服務(wù)器。 3、在202機(jī)器上面重復(fù)1、2步驟；配置keepalived.conf文件的時候注意要把 real_server 192.168.1.201 3306改為 real_server 192.168.1.202 3306virtual_router_id 201 改為virtual_router_id 202 至此MariaDB+Keepalived雙主高可用配置MySQL-HA設(shè)置完畢。可以試著把201上面的mariaDB停止 [root@localhost /]# service mariamysql stop;會發(fā)現(xiàn)連接192.168.1.200還是可以連接上去的,keepalived會自動切換到202的服務(wù)器上面去。這樣，當(dāng)一臺數(shù)據(jù)庫服務(wù)器發(fā)生故障時，另一臺服務(wù)器可以立即切換過來，保證高可用。bitsCN.com