MySQL單機改造成HA方案的配置

IT那活兒發布于2023-01-11 13:20 / 2024人閱讀

MySQL單機改造成HA方案的配置

一. MySQL HA改造背景

因目前某業務系統MySQL數據庫為單節點，為提高數據庫的高可用性，降低MySQL對業務的停機風險，經研究決定將MySQL數據庫由現有的單實例改造成雙主架構。

二. MySQL HA環境準備

2.1 系統環境準備

CentOS 7.9 64位

192.168.1.113（master1）

192.168.1.115（master2）

2.2 禁用selinux（雙主節點）

vi /etc/sysconfig/selinux

SELINUX=disable

2.3 設置用戶的文件句柄及進程數（雙主節點）

Vim /etc/security/limits.conf

shsnc soft nproc 2047

shsnc hard nproc 16384

shsnc soft nofile 16384

shsnc hard nofile 65536

shsnc soft stack 10240

2.4 系統內核參數優化（雙主節點）

Vim /etc/sysctl.conf
vm.swappiness = 0
net.ipv6.conf.all.disable_ipv6 = 1
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 600
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_local_port_range = 40000 65500
kernel.sem = 250 32000 100 128
vm.min_free_kbytes = 524288
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2542751744
vm.min_free_kbytes=524288
kernel.shmall = free/4
kernel.shmmax = free*1024*0.8

三. MySQL HA配置過程

3.1 部署MySQL服務器（雙主節點）

安裝mysql數據庫

在Oracle官網下載對應版本的mysql免編譯二進制安裝包解壓即可。

配置mysql

配置mysql master節點：

Vim /home/mysql/my_cnf/my.cnf

▼▼▼
[mysql]
port=3306
socket=/data/mysql/tmp/mysql.sock
default-character-set=utf8

[mysqld_safe]
log-error=/data/mysql/log/mysql.error

[mysqld]
port=3306
user=shsnc
basedir=/home/shsnc/mysql
datadir=/data/mysql/data
socket=/data/mysql/tmp/mysql.sock
pid-file=/data/mysql/log/mysql.pid
character-set-server=utf8
skip-name-resolve
lower_case_table_names=1
transaction_isolation=READ-COMMITTED
server_id=1
autocommit=1
tmp_table_size=128M
max_heap_table_size=96M
max_connections=1000
max_connect_errors=6000
long_query_time=1
innodb_buffer_pool_size=16G
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8M
innodb_log_file_size=128M
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_write_io_threads=8
innodb_read_io_threads=4
innodb_doublewrite=0
innodb_purge_threads=1
innodb_stats_on_metadata=OFF
innodb_io_capacity=1000
log-bin-trust-function-creators=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
event_scheduler=on
max_allowed_packet=2G
slow_query_log=1
auto_increment_offset=1
auto_increment_increment=2
#replication
log_bin=/data/mysql/log/bin.log
log_bin_index=/data/mysql/log/bin.index
sync_binlog=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
sync_binlog=1
log_slave_updates
binlog_format=ROW
binlog_rows_query_log_events=1
relay_log=/data/mysql/log/relay.log
relay_log_recovery=1
slave_skip_errors=ddl_exist_errors
slave-rows-search-algorithms=INDEX_SCAN,HASH_SCAN
#gtid
enforce_gtid_consistency=1
gtid_mode=ON
master_info_repository=table
relay_log_info_repository=table
slave_parallel_type=logical_clock
slave_parallel_workers=4
slave_pending_jobs_size_max=1G
slave_preserve_commit_order=1
sync_master_info=1
sync_relay_log_info=1

注：配置slave節點的時候只需修改server_id選項即可。

啟動mysql服務

▼▼▼
mysqld_safe --defaults-file=/home/mysql/my_cnf/my.cnf &

創建mysql復制賬號

▼▼▼
create userrepl@192.168.1.% identified with mysql_native_password by xxxxxx;
grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@192.168.1.%;
flush privileges;

配置雙主同步

配置113同步到115:

▼▼▼
CHANGE MASTER TO
   MASTER_HOST=192.168.1.113,
   MASTER_USER=repl,
   MASTER_PASSWORD=xxxxxx,
   MASTER_PORT=3306,
   MASTER_AUTO_POSITION=1;
Start slave;

配置115同步到113：

▼▼▼
CHANGE MASTER TO
   MASTER_HOST=192.168.1.115,
   MASTER_USER=repl,
   MASTER_PASSWORD=xxxxxx,
   MASTER_PORT=3306,
   MASTER_AUTO_POSITION=1;
Start slave;

查看slave狀態,Show slave statusG，若 IO、SQL進程狀態都為YES則配置OK。

3.2 配置mysql HA高可用（雙主節點）

安裝keeepliaved

▼▼▼
./configure --prefix=/opt/keepalived && make && make install

配置keeepliaved

配置keepalived MASTER路由器

vi /opt/keepalived/etc/keepalived/keepalived.conf

▼▼▼
! Configuration File for keepalived
global_defs {
    notification_email {
       shsnc@shsnc.com
   }
    notification_email_from smtp.163.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id LVS_DEVEL
}
vrrp_script checkk_mysql {
    script "/opt/keepalived/script/check_mysql.sh"
    interval 5
    fall 3
    rise 2
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens160
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 110120
    }
    virtual_ipaddress {
        135.10.51.8
    }
    track_script {
        checkk_mysql
    }
    notify_master "/opt/keepalived/script/keepalived_notify.sh master"
    notify_backup "/opt/keepalived/script/keepalived_notify.sh backup"
}

注：BACKUP路由器只需修改優先級選項即可。

HA實時監測腳本，檢查 mysql 進程是否存在，如果mysql掛了就停止keepalived,使VIP漂移到BACKUP機器上

Vim /opt/keepalived/script/check_mysql.sh

▼▼▼
A=`ps -C mysqld --no-header |wc -l`
if [ $A -eq 0 ]
then
    /bin/systemctl stop syskeepalived
fi

HA切換執行腳本：

Vim /opt/keepalived/script/keepalived_notify.sh

▼▼▼
#!/bin/bash
. ~/.bash_profile
#mysql user
DB_USER="root"

#mysql user password
DB_PASSWORD=shsnc!@#
MYSQL_SOCK=""
#mysql_bin
MYSQL_BIN="/home/shsnc/mysql/bin/mysql"
#mysql client command
MYSQL_CMD="${MYSQL_BIN} -u${DB_USER} -p${DB_PASSWORD}"
#query the killed seesions id sql
MYSQL_SQL="select concat(kill ,id,;) from information_schema.processlist where user not in (system user,repl,replic,backup,bkpuser,bomcjk,root,myrobot)"
#關閉事件任務
AMP_EVENT_PARTITION_DISABLE="ALTER event domp.event_add_partitions ON COMPLETION PRESERVE DISABLE"
DOMP_EVENT_PARTITION_DISABLE="ALTER event amp.event_add_partitions ON COMPLETION PRESERVE DISABLE"
#開啟事件任務
AMP_EVENT_PARTITION_ENABLE="ALTER event domp.event_add_partitions ON COMPLETION PRESERVE ENABLE"
DOMP_EVENT_PARTITION_ENABLE="ALTER event amp.event_add_partitions ON COMPLETION PRESERVE ENABLE"
#define function: get mysql service information
function get_mysql_infor() 
{
    for sock in `ps -ef | grep mysqld | grep --socket= | awk -F--socket= {print $2} | awk {print $1}`
    do
        MYSQL_SOCK="${MYSQL_SOCK}$(echo $sock)"
    done
}
#define function: mysql kill sessions
function kill_sessions()
{
    #receive a mysql socket file parameter
    #my_sock=$(get_mysql_infor)
  unset MYSQL_SOCK
  get_mysql_infor
  
    # kill session 
    #..................
    #begin to kill mysql sessions
    ${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${MYSQL_SQL}" 2>/dev/null | ${MYSQL_CMD} > /dev/null 2>&1
}
#define function: set mysql read_only mode
function set_readonly()
{
    #receive mysql socket file && read_only sign parameter
    #my_sock=$(get_mysql_infor)
  unset MYSQL_SOCK
  get_mysql_infor
  
    my_sign=$1
    #begin to set mysql read_only mode
    ${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "set global read_only=${my_sign}" 2>/dev/null
}
#define function: keepalived state changed to master
function Keepalived_changed_to_master()
{
  #my_sock=$(get_mysql_infor)
  unset MYSQL_SOCK
  get_mysql_infor
#開始事件任務
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${AMP_EVENT_PARTITION_ENABLE} 2>/dev/null "
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${DOMP_EVENT_PARTITION_ENABLE} 2>/dev/null "
  Seconds_Behind_Master=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Seconds_Behind_Master | awk -F": " {print $2})
  Slave_IO_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_IO_Running | awk -F": " {print $2})
  Slave_SQL_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_SQL_Running | awk -F": " {print $2})
  Master_Log_File=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Master_Log_File | awk -F": " {print $2})
  Relay_Master_Log_File=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " {print $2})
  Read_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " {print $2})
  Exec_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Exec_Master_Log_Pos | awk -F": " {print $2})
  echo
  #
    if [ "${Slave_IO_Running}" = "Yes" -a "${Slave_SQL_Running}" = "Yes" ]
    then
        if [ $Seconds_Behind_Master -eq 0 ]
        then
            echo 判斷復制無延遲,即將切換
            #.........set mysql no read_only mode.........
            set_readonly 0
            exit 0
        fi
    fi
    if [ "${Slave_IO_Running}" = "Connecting" -a "${Slave_SQL_Running}" = "Yes" ]
    then
        if [ "${Master_Log_File}" = "${Relay_Master_Log_File}" -a "${Read_Master_Log_Pos}" = "${Exec_Master_Log_Pos}" ]
        then
            echo "主庫宕機,即將切換"
            #.........set mysql no read_only mode.........
            set_readonly 0
            exit 0
    fi
    fi
}
#define function: keepalived state changed to backup
function Keepalived_changed_to_backup()
{
  #my_sock=$(get_mysql_infor)
  unset MYSQL_SOCK
  get_mysql_infor
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${AMP_EVENT_PARTITION_DISABLE} 2>/dev/null "
${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${DOMP_EVENT_PARTITION_DISABLE} 2>/dev/null "
    #set mysql read_only mode
  set_readonly 1
  #kill mysql sessions
  kill_sessions
}
#start this shell
case $1 in
    master)
      Keepalived_changed_to_master
        ;;
   
  backup)
      Keepalived_changed_to_backup
      ;;
  *)
      ;;
esac

啟動keepalived
systemctl start keepalived
觀察113/115機器上的日志情況
tailf /var/logs/message
查看113/115機器上IP信息，查看VIP地址是否起來
ip addr
同時模擬keepalived故障時ip漂移情況以及主從節點只讀狀態設置情況：

四. MySQL HA方案驗證

通過上面一系列的配置我們將MySQL+Keepalived配置好了，接下來我們模擬業務通過訪問VIP來連接數據庫，若ok，則MySQL高可用配置ok。

4.1 在數據庫上創建VIP地址白名單賬號

▼▼▼
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO shsnc@135.10.66.125 identified by xxxxxx;

4.2 驗證MySQL HA

使用mysql客戶端命令行通過VIP地址訪問數據庫：
mysql -h135.10.51.8 -ushsnc -p
經測試，通過VIP地址能正常訪問到mysql數據庫。
應用修改數據庫配置文件，并啟動應用及測試:
將配置文件中數據庫連接中配置IP 改為 HA方案的虛擬VIP: 135.10.51.8
經測試，啟動應用后，測試業務數據正常，且能正常寫入和讀取數據，MySQL高可用配置ok。

END

更多精彩干貨分享

點擊下方名片關注

IT那活兒

云服務器 GPU云服務器單機 mysql 備份方案把自己電腦改造成云服務器把家中電腦改造成云服務器容器化改造方案

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129882.html

DataX在有贊大數據平臺的實踐

摘要：與大數據體系交互上報運行統計數據自帶了運行結果的統計數據，我們希望把這些統計數據上報到元數據系統，作為的過程元數據存儲下來。基于我們的開發策略，不要把有贊元數據系統的嵌入源碼，而是在之外獲取，截取出打印的統計信息再上報。一、需求有贊大數據技術應用的早期，我們使用 Sqoop 作為數據同步工具，滿足了 MySQL 與 Hive 之間數據同步的日常開發需求。隨著公司業務發展，數據同步的場景越...

JerryWangSAP 2019-05-22 17:07 評論0 收藏0
XXL-CONF v1.6.0 發布，分布式配置管理平臺。廢棄ZK輕量級架構升級

摘要：訪問令牌為提升系統安全性，配置中心和客戶端進行安全性校驗，雙方匹配才允許通訊啟動時，優先全量加載鏡像數據到層，避免逐個請求耗時簡介是一個輕量級分布式配置管理平臺，擁有輕量級秒級動態推送多環境多語言配置監聽權限控制版本回滾等特性。 Release Notes 1、輕量級改造：廢棄ZK，改為 DB + 磁盤 + long polling 方案，部署更輕量，學習更簡單；集群部署更方便，與單...

Pandaaa 2019-08-16 13:52 評論0 收藏0
“分庫分表" ？選型和流程要慎重，否則會失控

摘要：但你是否知道分庫分表需要哪些要素拆分過程是復雜的，提前計劃，不要等真正開工，各種意外的工作接踵而至，以至失控。在實施分庫分表策略時，這些個性會造成策略過大不好維護。更多文章關注微信公眾號《小姐姐味道》 https://mp.weixin.qq.com/s?__... 數據庫中間件之分庫分表恭喜你，貴公司終于成長到一定規模，需要考慮高可用，甚至分庫分表了。但你是否知道分庫分表需要哪...

archieyang 2019-08-16 13:56 評論0 收藏0
微信分享｜如何在云中構建大規模分布式系統

摘要：大家好，我是系統工程師王煜，今天由來分享在云計算平臺上構建穩定可靠的分布式系統架構。接下來我來給大家介紹如果利用云計算的優勢，結合企業的業務特點構建穩定可靠的分布式系統。本次分享 William 將從技術角度分析在云計算環境中，當用戶業務面對流量激增、數據量翻番、訪問量指數級攀升的煩惱時，如何利用云計算平臺的彈性，結合業務自身特點，設計和構建一個高可用、高伸縮性的后端系統架構。同時會...

RichardXG 2019-06-28 10:49 評論0 收藏0