mysql主主+keepalived高可用-創(chuàng)新互聯(lián)

mysql主主+keepalived高可用

1.兩臺主機(jī)

host:
db1  外網(wǎng)：11.0.0.51  內(nèi)網(wǎng)：172.16.1.51
db2  外網(wǎng)：11.0.0.52  內(nèi)網(wǎng)：172.16.1.52

2.兩臺主機(jī)分別下載mysql

cd /usr/chris/src
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar
useradd mysql -u 550 -s /sbin/nologin -M
tar xf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.24

3.db1數(shù)據(jù)庫操作

ln -s /usr/local/mysql-5.7.24 /usr/local/mysql

vim /etc/my.cnf 
#begin
[mysqld]
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
datadir=/data/mysql/data
basedir=/usr/local/mysql

socket=/tmp/mysql.sock
user=mysql
symbolic-links=0

#slow log
slow-query-log=ON
slow_query_log_file=/data/mysql/data/log/mysql_slow_query.log
long_query_time=3
log_queries_not_using_indexes = OFF
log-slave-updates=true

gtid-mode=on
enforce-gtid-consistency=true

ft_min_word_len = 2
net_buffer_length = 8K
max_allowed_packet = 64M
wait_timeout = 180000
interactive_timeout=180000
thread_cache_size = 128
thread_stack = 512K
table_open_cache = 512
join_buffer_size    = 16M
sort_buffer_size    = 16M
log-error = /data/mysql/log/error.log

log_slave_updates = 1
#character_name:value
character-set-server=utf8  
collation-server=utf8_general_ci

log_bin = OFF
log-bin = mysql-bin
#relay-log = mysql-bin
server-id = 20
#auto_increment_offset = 2
#auto_increment_increment = 2
max_connections = 2000
group_concat_max_len = 2000
max_allowed_packet = 1024M
innodb_buffer_pool_size = 512M

read-only=0
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=amh

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
###end

4.創(chuàng)建數(shù)據(jù)目錄，修改啟動(dòng)

mkdir /data/mysql/{data,log} -p
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

#啟動(dòng)腳本修改
vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/data/mysql/data

#環(huán)境變量修改（加入mysql路徑）
vim ~/.bash_profile
MYSQL_HOME=/usr/local/mysql
export PATH=$JAVA_HOME/bin:$PATH:$MYSQL_HOME/bin

source ~/.bash_profile
#初始化啟動(dòng)
chown -R mysql:mysql /data/mysql
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

#如果看見錯(cuò)誤提示error while loading shared libraries: libaio.so.1，代表缺少了庫文件，安裝一下就可以了
yum install  libaio-devel.x86_64 -y

/etc/init.d/mysqld start

mysql -uroot -p
輸入初始密碼：
mysql> set password for root@localhost = PASSWORD('123456');
mysql> flush privileges;
###這里db1數(shù)據(jù)庫就配置完成了。

5.db2配置大致相同,就是配置文件需要修改

vim /etc/my.cnf
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

datadir=/data/mysql/data
basedir=/usr/local/mysql

socket=/tmp/mysql.sock
user=mysql
symbolic-links=0

#slow log
slow-query-log=ON
slow_query_log_file=/data/mysql/data/log/mysql_slow_query.log
long_query_time=3
log_queries_not_using_indexes = OFF

log-slave-updates = true
ft_min_word_len = 2
net_buffer_length = 8K
max_allowed_packet = 64M
wait_timeout = 180000
interactive_timeout=180000
thread_cache_size = 128
thread_stack = 512K
table_open_cache = 512
join_buffer_size    = 16M
sort_buffer_size    = 16M
log-error = /data/mysql/log/error.log

log_slave_updates = 1
#character_name:value
character-set-server=utf8  
collation-server=utf8_general_ci

log_bin = OFF
log-bin = mysql-bin
#relay-log = mysql-bin
server-id = 21
#auto_increment_offset = 2
#auto_increment_increment = 2
max_connections = 2000
group_concat_max_len = 2000
max_allowed_packet = 1024M
innodb_buffer_pool_size = 512M

gtid-mode=on
enforce-gtid-consistency=true

read-only=0
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=amh

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#啟動(dòng)修改密碼：
/etc/init.d/mysqld start
mysql -uroot -p
password:

mysql> set password for root@localhost = PASSWORD('123456');
mysql> flush privileges;

6.數(shù)據(jù)庫主主設(shè)置（互為主從）

--------------------------db1操作：--------------------------
mysql -uroot -p123456

mysql> grant replication slave on *.* to 'rep'@'172.16.1.52' identified by '123456';
mysql> flush privileges;
mysql> show master status\G
*************************** 1. row ***************************
                         File: mysql-bin.000015
                 Position: 234
         Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,amh
Executed_Gtid_Set: 79a90ed3-fa86-11e7-ac66-000c29542a93:1-3,
9238586c-fa86-11e7-acab-000c29f6ed58:1-5
1 row in set (0.00 sec)

###【這一步需要自己查看主庫最新mysql-bin和pos,下面有查看】
mysql> change master to
         > master_host='172.16.1.52',
         > master_user='rep',
         > master_password='123456',
         > master_log_file='mysql-bin.000024',   #這個(gè)地方是主庫的最新mysql-bin
         > master_log_pos='234';                        #主庫的最新pos

mysql> start slave;
mysql> show slave status\G

###生產(chǎn)環(huán)境如果要修改數(shù)據(jù)庫架構(gòu)不建議這么做，適合新部署，開啟gtid功能
###mysql> stop slave;
###mysql> change master to MASTER_AUTO_POSITION=1;

--------------------------db2操作：----------------------------
mysql> grant replication slave on *.* to 'rep'@'172.16.1.51' identified by '123456';
mysql> flush privileges;
mysql> show master status\G
*************************** 1. row ***************************
                         File: mysql-bin.000024
                 Position: 234
         Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,amh
Executed_Gtid_Set: 79a90ed3-fa86-11e7-ac66-000c29542a93:1-3,
9238586c-fa86-11e7-acab-000c29f6ed58:1-5
1 row in set (0.00 sec)

###【這一步需要自己查看主庫最新mysql-bin和pos,上面有查看】
mysql> change master to
         > master_host='172.16.1.51',
         > master_user='rep',
         > master_password='123456',
         > master_log_file='mysql-bin.000015',
         > master_log_pos='234';

mysql> start slave;
mysql> show slave status\G

###生產(chǎn)環(huán)境不建議這么做，開啟gtid功能
###mysql> stop slave;
###mysql> change master to MASTER_AUTO_POSITION=1;

7.keepalived安裝配置

###db1和db2分別安裝
yum install keepalived -y

###db1的keepalived.conf配置
mkdir /etc/keepalived/scripts -p

vim /etc/keepalived/keepalived.conf
global_defs {
router_id db
}

vrrp_instance VI_1 {
        state MASTER
        interface eth2
        virtual_router_id 61
        priority 150
        advert_int 1
        nopreempt
        authentication {
                auth_type PASS
                auth_pass 1111
        }
        virtual_ipaddress {
                172.16.1.3
        }
}

virtual_server 172.16.1.3 3306 {
        delay_loop 2
        lb_algo wrr
        lb_kind DR 
        nat_mask 255.255.255.0
        persistence_timeout 50
        protocol TCP

        real_server 172.16.1.51 3306 {
                weight 3
                notify_down /etc/keepalived/scripts/mysql_check.sh
        TCP_CHECK {
                        connect_timeout 3
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                }
        }
}
###此處還要配置一個(gè)觸發(fā)腳本，用來檢測數(shù)據(jù)庫是否存活（我的很簡單粗暴，你們自己可以寫連接數(shù)據(jù)庫判斷）
vim /etc/keepalived/scripts/mysql_check.sh
#!/bin/bash

Db1_Num=`netstat -luntp|grep 3306|wc -l`

if [ $Db1_Num -eq 0 ];then
        /etc/init.d/keepalived stop
        echo "Mysql vip is moved."
else
        echo "Mysql is running."
        exit 1
fi

###腳本需要加執(zhí)行權(quán)限
chmod +x /etc/keepalived/scripts/mysql_check.sh

###db2的keepalived.conf配置
    global_defs {
        router_id db
}

vrrp_instance VI_1 {
        state BACKUP 
        interface eth2
        virtual_router_id 61
        priority 100
        advert_int 1
        nopreempt
        authentication {
                auth_type PASS
                auth_pass 1111
        }
        virtual_ipaddress {
                172.16.1.3
        }
}

virtual_server 172.16.1.3 3306 {
        delay_loop 2
        lb_algo wrr
        lb_kind DR 
        nat_mask 255.255.255.0
        persistence_timeout 50
        protocol TCP

        real_server 172.16.1.52 3306 {
                weight 3
                notify_down /etc/keepalived/scripts/mysql_check.sh
        TCP_CHECK {
                        connect_timeout 3
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                }
        }
}

###觸發(fā)腳本同上（在環(huán)境配置一樣的情況下，類似端口都是3306，自己注意，還有腳本的執(zhí)行權(quán)限）

8.老衲不測試了

###自己停掉數(shù)據(jù)庫查看vip是否飄移

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

專注于為中小企業(yè)提供成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)樂都免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了超過千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

本文名稱：mysql主主+keepalived高可用-創(chuàng)新互聯(lián)
本文地址：http://jinyejixie.com/article2/dijjic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、網(wǎng)站設(shè)計(jì)、網(wǎng)站導(dǎo)航、品牌網(wǎng)站制作、建站公司、軟件開發(fā)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

mysql主主+keepalived高可用-創(chuàng)新互聯(lián)