欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL雙主搭建+keepalived高可用的實現(xiàn)

 更新時間：2025年04月03日 11:05:12   作者：zongzizz  
本文主要介紹了MySQL雙主搭建+keepalived高可用的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、測試環(huán)境準備

節(jié)點1節(jié)點2
IP地址192.168.101.77192.168.101.79
MySQL版本8.0.328.0.32

二、主從搭建

1.創(chuàng)建復(fù)制用戶

節(jié)點1執(zhí)行：

mysql> CREATE USER 'repl'@'%' IDENTIFIED  BY '123123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> show master status;

2.創(chuàng)建復(fù)制關(guān)系

節(jié)點2執(zhí)行

mysql> 
change master to    
master_host='192.168.101.77',    
MASTER_PORT=3306,    
master_user='repl',  
master_password='123123' ,  
MASTER_AUTO_POSITION=1;

3.開啟復(fù)制，確認復(fù)制是否成功

開啟復(fù)制
mysql> start slave;
查看復(fù)制狀態(tài)（下面是正常狀態(tài)）
root@localhost:(none)>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.101.77
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 197
               Relay_Log_File: localhost-relay-bin.000004
                Relay_Log_Pos: 413
        Relay_Master_Log_File: mysql-bin.000007
             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: 197
              Relay_Log_Space: 2098
              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
                  Master_UUID: f6ca767b-2144-11ee-80e4-080027a4fdbc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:4-8
            Executed_Gtid_Set: f6ca767b-2144-11ee-80e4-080027a4fdbc:1-8
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

可在節(jié)點1插入數(shù)據(jù)，然后在節(jié)點2查詢數(shù)據(jù)是否同步，確定同步是否正常。

4.同步排錯

報錯1：

Last_IO_Error: error connecting to master 'repl@192.168.101.77:3306' - retry-time: 60 retries: 21 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解決方法：

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password  BY '123123';

報錯2：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '75433121-6a95-11ef-8b71-080027123ed0:1-3', and the missing transactions are 'f6ca767b-2144-11ee-80e4-080027a4fdbc:1-3''

解決方法：

root@localhost:(none)>stop slave;
Query OK, 0 rows affected, 1 warning (0.11 sec)

root@localhost:(none)>reset master;
Query OK, 0 rows affected (0.10 sec)

root@localhost:(none)>reset slave;
Query OK, 0 rows affected, 1 warning (0.20 sec)

root@localhost:(none)>set @@GLOBAL.GTID_PURGED='f6ca767b-2144-11ee-80e4-080027a4fdbc:1-3';
Query OK, 0 rows affected (0.06 sec)

root@localhost:(none)>start slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)

三、雙主配置

雙主配置也就是將上面的主從操作反向操作一遍。

1.創(chuàng)建復(fù)制用戶

mysql> CREATE USER 'repl1'@'%' IDENTIFIED  BY '123123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl1'@'%';
mysql> FLUSH PRIVILEGES;
mysql> show master status;

2.創(chuàng)建復(fù)制關(guān)系

mysql>
change master to
master_host='192.168.101.79',
MASTER_PORT=3306,
master_user='repl',
master_password='123123' ,
MASTER_AUTO_POSITION=1;

3.開啟復(fù)制，確認復(fù)制是否成功

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

4.雙主測試

節(jié)點1插入數(shù)據(jù)，節(jié)點2查詢
節(jié)點2插入數(shù)據(jù)，節(jié)點1查詢

四、keepalived配置

1.keepalived安裝、啟停操作

yum -y install keepalived
systemctl status keepalived
systemctl start keepalived
systemctl stop keepalived

2.keepalived配置文件

vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email {
      acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
    notification_email_from Alexandre.Cassen@firewall.loc
   router_id MYCAT_HA # 定義名稱
}
vrrp_script chk_mycat_alive {
    script "/etc/keepalived/mysql_check.sh"    # 返回狀態(tài)碼為0表示正常,檢測腳本為true;返回狀態(tài)碼非0表示異常,檢測腳本為false
    interval 2                                 # 檢測腳本執(zhí)行的間隔，單位是秒
    weight 20
}
vrrp_instance VI_1 {
      state MASTER     #主節(jié)點為MASTER，備節(jié)點為BACKUP
      interface enp0s3    #根據(jù)實際網(wǎng)卡名配置
      virtual_router_id 88
      priority 100
      advert_int 1
      authentication {
         auth_type PASS
         auth_pass qwert
    }
    track_script {
        chk_mycat_alive            # 調(diào)用檢測腳本
    }
   virtual_ipaddress {
         192.168.101.199/24            # 定義虛擬ip(VIP)
    }
}

3.keepalived配置文件

服務(wù)狀態(tài)檢查腳本

cat /etc/keepalived/mysql_check.sh
#!/bin/bash
count=`ps -C mysqld --no-heading|wc -l`
time=$(date "+%Y-%m-%d %H:%M:%S")
if [ $count = 0 ]; then
    echo "$time : count=$count, mysql is not running..." >> /var/log/keepalived_check.log
    exit 1 # 返回1說明腳本非正常執(zhí)行,mysql不在運行中
else
    echo "$time : count=$count, mysql is running..." >> /var/log/keepalived_check.log
    exit 0 # 返回0說明腳本正常執(zhí)行,mysql正在運行中
    fi

4.keepalived配置成功

配置完成后可在其中一節(jié)點上看到虛擬地址，同網(wǎng)段也可以ping通虛擬地址，同時也可以通過虛擬地址連接上數(shù)據(jù)庫。

在這里插入圖片描述

在這里插入圖片描述

五、集群狀態(tài)測試

兩節(jié)點物理地址均可連接數(shù)據(jù)庫，也可通過虛擬地址連接數(shù)據(jù)庫，下面我們測試一下關(guān)閉一臺服務(wù)器（虛擬地址所服務(wù)器）測試是否還是可通過虛擬地址連接數(shù)據(jù)庫。

在這里插入圖片描述

節(jié)點2關(guān)機后虛擬地址跑到節(jié)點1上去了，測試用虛擬地址連接數(shù)據(jù)庫依然可以連接

在這里插入圖片描述

在這里插入圖片描述

重新啟動節(jié)點2后，啟動數(shù)據(jù)庫即可拉起雙主集群

到此這篇關(guān)于MySQL雙主搭建+keepalived高可用的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL雙主搭建+keepalived高可用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論