背景
mysql8安裝
備機(jī)安裝mysql8
- 主備狀態(tài)查看
mysql router安裝
keepalived安裝
proxy安裝
MHA安裝
my.cnf配置
Granafa監(jiān)控
主從同步處理
- 主從error處理
  - pt-table-sync安裝
  - 手動(dòng)處理
  - 自動(dòng)處理
- 主掛了從升主處理
- 主掛了從升主之后，原主恢復(fù)變從
主從同步業(yè)務(wù)優(yōu)化處理
- before
- after
canal 增量+全量處理

背景

centos7.6機(jī)器，256G內(nèi)存，64C，3.5T SSD
之前線上已經(jīng)有mysql運(yùn)行中的機(jī)器了，所以會(huì)在原有基礎(chǔ)上修改/etc/my.cnf配置文件
目前架構(gòu)是一主一臺(tái)，兩個(gè)集群，數(shù)據(jù)是應(yīng)用層雙寫，業(yè)務(wù)不太敏感數(shù)據(jù)輕微不一致
使用mysql router做集群內(nèi)的讀寫分離，兩臺(tái)都安裝router，并使用vip做router的高可用
沒用使用MHA等方案監(jiān)控自動(dòng)主從切換，當(dāng)主掛了時(shí)，人工介入目前，后續(xù)可能考慮
架構(gòu)，單集群展示，VIP會(huì)漂移到Router A或者Router B，然后根據(jù)讀寫到mysqlA或者mysqlB

image.png

mysql8安裝

安裝repo，注意看有沒報(bào)錯(cuò)哈，沒報(bào)錯(cuò)就我截圖那樣

yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

image.png

安裝mysql8，注意看有沒報(bào)錯(cuò)哈，沒報(bào)錯(cuò)就我截圖那樣

yum install -y --nogpgcheck mysql-community-server

image.png

看下機(jī)器之前有沒mysql數(shù)據(jù)，有的話考慮rm -rf掉

ll /var/lib/mysql/

我是在本地編輯my.cnf然后上傳上去的，所以我這邊執(zhí)行刪除之前舊的my.cnf，再上傳，如果你使用默認(rèn)的就忽略就好

cd /etc
rm my.cnf
rz 上傳本地編輯的文件
chmod 644 /etc/my.cnf

我的SSD盤是/cache1盤，my.inf配置了對(duì)應(yīng)數(shù)據(jù)盤

mkdir -p /cache1/mysql_data
chmod 750 /cache1/mysql_data

開啟mysql并自啟動(dòng)，沒報(bào)錯(cuò)就是啟動(dòng)成功，這種啟動(dòng)方式能自動(dòng)重啟， kill -9可以測(cè)試

systemctl start mysqld
systemctl enable mysqld

改密碼，先看機(jī)器上初始密碼，我的是qMP-Vqed_6ec，我的密碼是沒有特殊符合不符合安全規(guī)則，我是先隨便設(shè)置了個(gè)符合的然后SET GLOBAL validate_password.special_char_count = 0;再改回來

sudo grep 'temporary password' /cache1/mysql_data/*.err
mysql -uroot -pqMP-Vqed_6ec
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxx';

配置備庫讀取權(quán)限

# 主庫執(zhí)行
CREATE USER 'repl_user'@'備庫' IDENTIFIED BY '密碼';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'備庫';
FLUSH PRIVILEGES;

備機(jī)安裝mysql8

步驟基本跟上面一致，就是my.cnf配置備注部分要改
也配置下讀取備庫(萬一主從切換)權(quán)限

# 備庫執(zhí)行
CREATE USER 'repl_user'@'主庫' IDENTIFIED BY '密碼';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'主庫';
FLUSH PRIVILEGES;

備庫要執(zhí)行開啟同步

CHANGE MASTER TO
MASTER_HOST='主庫ip',
MASTER_PORT=設(shè)置的端口,
MASTER_USER='repl_user',
MASTER_PASSWORD='密碼',
MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G;

主備狀態(tài)查看

查看主備狀態(tài)具體

# 看下有沒erro
1. SHOW SLAVE STATUS\G;
# 有error可能具體報(bào)錯(cuò)
SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

mysqlrouter安裝

安裝命令，注意看有沒報(bào)錯(cuò)，中途記得輸入y

wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-7.noarch.rpm
sudo yum clean all

sudo yum install mysql-router --nogpgcheck

image.png

查看版本

mysqlrouter --version

image.png

修改配置文件

vim /etc/mysqlrouter/mysqlrouter.conf

在某尾加上

# 寫路由，所有寫流量只走主庫
[routing:write]
bind_address = 0.0.0.0:7001
mode = read-write
destinations = 主庫:xxx

# 讀路由，優(yōu)先走從庫，從庫掛了才能fallback到主庫，當(dāng)然讀也可以不走主庫，另外拓展出一臺(tái)讀機(jī)器，因?yàn)榻o的資源有限制，主庫要拿來讀了
[routing:read]
bind_address = 0.0.0.0:7002
mode = read-only
destinations = 從庫:xxx，主庫:xxx

啟動(dòng)

sudo systemctl start mysqlrouter
sudo systemctl enable mysqlrouter

查看效果

sudo netstat -lntp | grep mysqlrouter

image.png

keepalived安裝

考慮到mysqlrouter單點(diǎn)故障，這邊使用keepalived做高可用
安裝，中間記得輸入y，注意看有沒報(bào)錯(cuò)

yum install keepalived

image.png

看下網(wǎng)卡，我的是輸出eth2，如果主備keepalived機(jī)器網(wǎng)卡不同則virtual_ipaddress配置需不同

ip link show | grep 'state UP' | awk -F': ' '{print $2}'

修改配置文件

vim /etc/keepalived/keepalived.conf

配置文件數(shù)據(jù)，mysqlrouter有兩臺(tái)，主的設(shè)置如下

global_defs {
   router_id MYSQL_ROUTER_MASTER
}

vrrp_script check_mysqlrouter {
    script "killall -0 mysqlrouter"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth2
    virtual_router_id 211
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 你的密碼
    }
    track_script {
        check_mysqlrouter
    }
    virtual_ipaddress {
        你的vip地址(我這邊是找公司申請(qǐng)的)
    }
}

配置文件數(shù)據(jù)，mysqlrouter有兩臺(tái)，備的設(shè)置如下，我的網(wǎng)卡是一致的，如果你的網(wǎng)卡不一致要特殊配置

global_defs {
   router_id MYSQL_ROUTER_SLAVE
}

vrrp_script check_mysqlrouter {
    script "killall -0 mysqlrouter"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    state SLAVE
    interface eth2
    virtual_router_id 211
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass xxx須跟主保持一致
    }
    track_script {
        check_mysqlrouter
    }
    virtual_ipaddress {
        你的vip需要跟主保持一致(我這邊是找公司申請(qǐng)的vip)
    }
}

啟動(dòng)

# 啟動(dòng)服務(wù)（所有節(jié)點(diǎn)）
systemctl start keepalived

# 設(shè)置開機(jī)自啟
systemctl enable keepalived

# 檢查服務(wù)狀態(tài)
systemctl status keepalived

image.png

驗(yàn)證vip功能

# 在主節(jié)點(diǎn)停止服務(wù)
systemctl stop keepalived

# 在備節(jié)點(diǎn)檢查VIP（應(yīng)自動(dòng)接管），eth2 網(wǎng)要根據(jù)你自己的來
watch -n1 "ip addr show eth2 | grep 'inet '"

# 恢復(fù)主節(jié)點(diǎn)
systemctl start keepalived

# 觀察VIP回切（根據(jù)preempt配置）
tail -f /var/log/keepalived.log

驗(yàn)證綁定, 注意eth2可能要改:
ip addr show eth2 | grep 'inet '

proxy安裝

由于mysqlrouter已經(jīng)符合要求，并且mysqlrouter更加輕量化，故不考慮使用proxy

MHA安裝

目前沒考慮自動(dòng)主備切換，后續(xù)有需要再安裝
注意MHA 不會(huì)自動(dòng)回切或恢復(fù)舊主
MySQL InnoDB Cluster中MySQL Group Replication支持舊主重新假如自動(dòng)修復(fù)，帶需依賴舊主恢復(fù)時(shí)間和新主保留二進(jìn)制數(shù)據(jù)的時(shí)間
大型mysql集群可以用

MySQL InnoDB Cluster
PXC多主，但是強(qiáng)一致性數(shù)據(jù)要求，跨集群有點(diǎn)蛋疼
分布式中間件（如 ProxySQL + 分片）
MHA + Keepalived

Granafa監(jiān)控

做好監(jiān)控查看各個(gè)指標(biāo)

主從同步處理

主從error處理

處理之前需要主從機(jī)器上面的router都把備庫踢掉

pt-table-sync安裝


// centos7.6，建議主備兩臺(tái)機(jī)器都裝上，防止備升主，主降備

sudo yum install [https://repo.percona.com/yum/percona-release-latest.noarch.rpm](https://repo.percona.com/yum/percona-release-latest.noarch.rpm) 

sudo percona-release enable tools release

sudo yum install percona-toolkit

pt-table-sync --version

手動(dòng)處理

看下error的地方，大部分的錯(cuò)誤是從庫不小心寫入數(shù)據(jù)


// 看下error

SHOW SLAVE STATUS\G;

// 看具體error

SELECT * FROM performance_schema.replication_applier_status_by_worker\G;

跳過某個(gè)異常, 可能有多個(gè), 跳過后注意看異常的表，然后執(zhí)行查看具體哪張表問題，再處理，有可能下面要循環(huán)多次以解決異常


STOP SLAVE;

// 這個(gè)gtid是你從 performance_schema.replication_applier_status_by_worker看到具體的錯(cuò)誤來的
SET GTID_NEXT = '14c92d46-3541-11f0-a4f6-90e2ba00f37c:1222';
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;
SHOW SLAVE STATUS\G;

自動(dòng)處理

使用pt-table-sync自動(dòng)查看，注意有時(shí)候數(shù)據(jù)量太多會(huì)卡住，可能某張表太大卡住，這時(shí)候就要手動(dòng)接入處理掉卡住的大表


// --table不配置的話就是整個(gè)庫查看異同

pt-table-sync h=主庫host,P=port,u=xxx,p=xxxx \
h=從庫host,P=port,u=xxx,p=xxxx \
--database xxx--table xx,xxx \
--print --no-check-slave --no-transaction > repair.sql

人工看下repair.sql數(shù)據(jù)確認(rèn)下
自動(dòng)修復(fù)


// 這里我配置了不可手動(dòng)寫入

STOP SLAVE;

SET GLOBAL super_read_only = OFF;

// 執(zhí)行修復(fù)

mysql -hxxx -Pxxx -uxx -pxxxx 庫名< repair.sql

SET GLOBAL super_read_only = ON;

// 確定要跳過的gtid, 看Last_SQL_Error

SHOW SLAVE STATUS\G;

SET GTID_NEXT='xxx:123';  
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

START SLAVE;
SHOW SLAVE STATUS\G;

主掛了從升主處理


1\. 將從庫提升為新主庫
- vim /etc/my.cnf
將read_only=1 super_read_only=1反向設(shè)置都為0

2\. 確認(rèn)主庫故障
- SHOW SLAVE STATUS\G;
- 確保 Slave_IO_Running 和 Slave_SQL_Running 均為 Yes，且 Seconds_Behind_Master 為 0（無延遲）
3\. 停止從庫復(fù)制進(jìn)程
在從庫執(zhí)行以下命令，停止復(fù)制并解除與原主庫的關(guān)聯(lián)
STOP SLAVE;
RESET SLAVE ALL;  -- 清除復(fù)制信息
4\. 主備的/etc/mysqlrouter/mysqlrouter.conf，router寫庫改掉
systemctl restart mysqlrouter

主掛了從升主之后，原主恢復(fù)變從

當(dāng)原主庫恢復(fù)后，MHA 默認(rèn)不會(huì)自動(dòng)將其加入集群，需要人工干預(yù)或配置腳本自動(dòng)處理。備份新主庫數(shù)據(jù) （確保安全）。在原主庫上重建數(shù)據(jù)
如果差別不大，比如binlog保存三天，只掛了一天，那就走上面提到的自動(dòng)處理流程
如果差異比較大執(zhí)行下面步驟，注意主庫數(shù)據(jù)可以定時(shí)備份


1\. 停止并重置從庫
STOP SLAVE;
RESET SLAVE ALL; 

2\. 備份主庫
# 主庫執(zhí)行（GTID 模式專用）
mysqldump -h xxx -P xx-u xxx -pxxxx \
--single-transaction --set-gtid-purged=ON \
--databases xxx> xxx_full_backup.sql

2.1 傳輸, 公司傳輸需要通過跳板機(jī)
# 跳板機(jī)執(zhí)行拉
scp -P xx [root@x](mailto:root@xxx)xx:/root/xxx_full_backup.sql /home/xxxx
# 跳板機(jī)執(zhí)行推
scp -P xx  xx_full_backup.sql [root@x](mailto:root@xxx)xxx:/root
# 避免重復(fù)
rm xxxx_full_backup.sql 

3\. 清空從庫 xxx庫, 執(zhí)行前主從都要將router read去掉從庫
-- 從庫執(zhí)行
SET GLOBAL super_read_only = OFF;
DROP DATABASE IF EXISTS xxx;
SET GLOBAL super_read_only = ON;

4\. 導(dǎo)入到從庫
# 移除 SET GTID_PURGED 語句
sed '/GTID_PURGED/d' XXX_full_backup.sql > clean_backup.sql

可以開兩個(gè)窗口
SET GLOBAL super_read_only = OFF;
mysql -uxxx -pxxxx < clean_backup.sql
SET GLOBAL super_read_only = ON;
5\. 重新鏈接
-- 從庫執(zhí)行
grep 'GTID_PURGED' xxx_full_backup.sql
STOP SLAVE;
RESET MASTER;
SET GLOBAL GTID_PURGED = 'ea6a933c-5647-11f0-afa0-e4434bb74caa:1-1485';
CHANGE MASTER TO
  MASTER_HOST = 'xxx',
  MASTER_PORT = xxx,
  MASTER_USER = 'xxx',
  MASTER_PASSWORD = 'xxxxxxx',
  MASTER_AUTO_POSITION = 1;
START SLAVE;
SHOW SLAVE STATUS\G;

my.cnf配置

貼出 centos7.6機(jī)器，256G內(nèi)存，64C，3.5T SSD對(duì)應(yīng)的配置，主庫，從庫根據(jù)注釋改改就好, 線上機(jī)器是join 臨時(shí)表量很大超級(jí)多，所以按照下面配置了

[mysqld]
# 只同步 mydb 這個(gè)數(shù)據(jù)庫
replicate-wild-do-table=xxx.%

# 不同步xxx.test
#replicate-ignore-table=xxx.test
# 兼容舊版本
default_authentication_plugin=mysql_native_password 
max_connections=3500
tmp_table_size=17179869184
max_heap_table_size=17179869184
table_open_cache=8192
innodb_buffer_pool_size=171798691840
innodb_io_capacity_max=20000
innodb_io_capacity=10000
binlog_cache_size=33554432
#expire_logs_days=1 已廢棄
binlog_expire_logs_seconds=86400
long_query_time=30
innodb_flush_neighbors=0
lower_case_table_names=0
binlog_ignore_db=scheduler_task

performance_schema_instrument=wait/lock/metadata/sql/mdl=ON
default_authentication_plugin=mysql_native_password
#query_cache_size=0 已廢棄
#query_cache_type=0 已廢棄
relay_log_info_repository=TABLE
master_info_repository=TABLE
explicit_defaults_for_timestamp=1
key_buffer_size=4G
server_id=12502990
report_port=ss
report_host=你的ip
port=ss
datadir=/cache1/mysql_data
socket=/cache1/mysql_data/mysql.sock
log_error=/cache1/mysql_data/mysql_error.err
pid_file=/cache1/mysql_data/mysqld.pid
slow_query_log=1
slow_query_log_file=slow_query.log
#log_bin=mysql-bin
relay_log=mysql-relay-bin
relay_log_space_limit=10737418240
general_log=0
general_log_file=mysql-general.log
binlog_format=ROW
sync_binlog=0
log_slave_updates=1
# 主庫兩個(gè)都設(shè)置0
read_only=0
super_read_only=0

event_scheduler=0
transaction_isolation=REPEATABLE-READ
max_allowed_packet=64M
sort_buffer_size=32M
read_buffer_size=64M
read_rnd_buffer_size=32M
join_buffer_size=32M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
group_concat_max_len=64K
innodb_stats_on_metadata=0
innodb_file_per_table=1
thread_cache_size=1024
skip_external_locking
skip_name_resolve
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
character_set_server=utf8
log_bin_trust_function_creators=1
open_files_limit=60000
secure_file_priv=''
slave_net_timeout=30
binlog_error_action=IGNORE_ERROR
#show_compatibility_56=on
#log_warnings=1
optimizer_switch='semijoin=OFF'
gtid_mode=ON
enforce_gtid_consistency=ON
mysqlx=0

[mysql]
no_auto_rehash
prompt="\u \d>"
[mysqldump]
quick

[client]
socket=/cache1/mysql_data/mysql.sock

主從同步業(yè)務(wù)優(yōu)化處理

before

mysql5.7有三張表 A B C, 其中B的表變化非常頻繁。之前主從正常，現(xiàn)在從庫斷了，一天，之前操作如下：

從主庫dumpB表，然后記錄binlog 水位
從庫清理B 表然后重新執(zhí)行dump的B表sql
主從同步設(shè)置從B表的binlog點(diǎn)位開始處理
pt-sync修復(fù)其他表的不同步數(shù)據(jù)
整體流程如上，有個(gè)問題假如C表也是變化很頻繁，用pt-sync多修復(fù)幾輪也修復(fù)不過來，那整個(gè)流程要怎么處理合適？

after

全量物理備份 + 精準(zhǔn)binlog同步
pt-sync是邏輯行級(jí)修復(fù)工具，對(duì)高頻寫入的表（如C表）效率極低：修復(fù)期間新變更持續(xù)產(chǎn)生，形成“修復(fù)速度 < 變更速度”的死循環(huán)。
僅備份B表會(huì)導(dǎo)致其他表（A/C）與binlog起點(diǎn)不一致：從庫從新binlog位點(diǎn)開始同步時(shí)，A/C表會(huì)重復(fù)應(yīng)用歷史binlog（因從庫舊數(shù)據(jù)未清理），引發(fā)數(shù)據(jù)錯(cuò)亂
只備份需要主從的表
具體步驟

主庫執(zhí)行全量物理備份（推薦XtraBackup，設(shè)置備份主從同步需要的表），優(yōu)勢(shì)如下
1.1 備份期間不鎖表（InnoDB熱備），業(yè)務(wù)無感知。
1.2 自動(dòng)生成xtrabackup_binlog_info文件，精確記錄備份結(jié)束時(shí)的binlog位點(diǎn)（如mysql-bin.000003, 107）。
1.3 備份集包含所有表（A/B/C）的一致性狀態(tài)
從庫清理舊數(shù)據(jù)，恢復(fù)物理備份
配置主從同步，從備份記錄的binlog位點(diǎn)開始
監(jiān)控同步狀態(tài)，確保追平，SHOW SLAVE STATUS；

為什么不推薦邏輯備份（mysqldump）？

鎖表風(fēng)險(xiǎn)：即使--single-transaction，MyISAM表或DDL操作仍可能阻塞。
恢復(fù)速度慢：SQL邏輯導(dǎo)入比物理文件拷貝慢5-10倍，延長從庫不可用時(shí)間。
大表瓶頸：100GB+的B/C表用mysqldump可能需數(shù)小時(shí)，期間主庫binlog持續(xù)增長，追平更困難。

如果后續(xù)新增數(shù)據(jù)量小 + 更新頻率低的 D 表，直接用 pt-sync 修復(fù)是最簡單安全的方案。mysql 5.7以及以后都可以新增配置后reload

canal 增量+全量處理

整體架構(gòu)設(shè)計(jì)，比如寫入es，使用XtraBackup全量存下來，然后記錄binlog點(diǎn)位

整體.png
Canal 客戶端啟動(dòng)指令（精確控制位點(diǎn)），使用 Canal Admin 控制臺(tái)（生產(chǎn)推薦）, 這樣可以做到每個(gè)客戶端都不同點(diǎn)位處理，開啟增量同步
json

{
  "journalName": "mysql-bin.000003",  // SHOW MASTER STATUS 的 File
  "position": 12345,                  // SHOW MASTER STATUS 的 Position
  "timestamp": 1696161000000,         // 毫秒時(shí)間戳（可選）
  "gtid": ""                          // 如使用 GTID 模式
}

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

mysql高可用架構(gòu)

mysql高可用架構(gòu)

目錄

背景

mysql8安裝

備機(jī)安裝mysql8

主備狀態(tài)查看

mysqlrouter安裝

image.png

keepalived安裝

驗(yàn)證vip功能

proxy安裝

MHA安裝

Granafa監(jiān)控

主從同步處理

主從error處理

pt-table-sync安裝

手動(dòng)處理

自動(dòng)處理

主掛了從升主處理

主掛了從升主之后，原主恢復(fù)變從

my.cnf配置

主從同步業(yè)務(wù)優(yōu)化處理

before

after

canal 增量+全量處理

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

mysql高可用架構(gòu)

目錄

背景

mysql8安裝

備機(jī)安裝mysql8

主備狀態(tài)查看

mysqlrouter安裝

image.png

keepalived安裝

驗(yàn)證vip功能

proxy安裝

MHA安裝

Granafa監(jiān)控

主從同步處理

主從error處理

pt-table-sync安裝

手動(dòng)處理

自動(dòng)處理

主掛了從升主處理

主掛了從升主之后，原主恢復(fù)變從

my.cnf配置

主從同步業(yè)務(wù)優(yōu)化處理

before

after

canal 增量+全量處理

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av