MySQL8.0高可用MIC的實現(xiàn)
更新時間：2024年10月28日 10:30:39 作者：愛喝旺仔的昊昊
本文介紹了如何實現(xiàn)MySQL8.0高可用MIC,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
一、機器準(zhǔn)備
機器
主機名 | ip地址 | 角色 | 軟件 |
mic-master | 192.168.252.148 | 主節(jié)點 | mysql8.0.23 mysql-shell-8.0.23 |
mic-node1 | 192.168.252.142 | node1 | mysql8.0.23 |
mic-node2 | 192.168.252.145 | node2 | mysql8.0.23 |
關(guān)閉防火墻
systemctl stop firewalld setenforce 0
二、環(huán)境準(zhǔn)備
安裝mysql8.0.23
官方地址：MySQL :: Begin Your Download
三臺機器均安裝
1.安裝工具 yum -y install perl-JSON perl-Test-Simple 2.獲取安裝包 wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-server-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-devel-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-common-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-embedded-compat-8.0.23-1.el7.x86_64.rpm wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-test-8.0.23-1.el7.x86_64.rpm 3.安裝 rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-devel-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-embedded-compat-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.23-1.el7.x86_64.rpm rpm -ivh mysql-community-test-8.0.23-1.el7.x86_64.rpm
修改配置文件
所有節(jié)點修改進行修改配置文件
添加配置的模版 server_id=[id] #每一臺的都不一樣，要唯一 report_host=[當(dāng)前服務(wù)器ip] report_port=[mysql端口號] loose-group_replication_ip_whitelist="[ip1],[ip2],[ip3]"
進行修改
vim /etc/my.cnf
192.168.252.148：
server-id=1 report_host=192.168.252.148 report_port=3306 loose-group_replication_ip_whitelist="192.168.252.148,192.168.252.142,192.168.252.145"
192.168.252.142：
server-id=2 report_host=192.168.252.142 report_port=3306 loose-group_replication_ip_whitelist="192.168.252.148,192.168.252.142,192.168.252.145"
192.168.252.145：
server-id=3 report_host=192.168.252.145 report_port=3306 loose-group_replication_ip_whitelist="192.168.252.148,192.168.252.142,192.168.252.145"
啟動并修改密碼
systemctl start mysqld
192.168.252.148：
cat /var/log/mysqld.log | grep "password" mysqladmin -uroot -p'Cu)ghjzfK3_J' password @Syh2025659
192.168.252.142：
cat /var/log/mysqld.log | grep "password" mysqladmin -uroot -p'*qywTIZHh8-j' password '@Syh2025659'
192.168.252.145：
cat /var/log/mysqld.log | grep "password" mysqladmin -uroot -p'Z+Ly31m>Y#sy' password '@Syh2025659'
主服務(wù)器安裝mysql-shell-8.0.23
wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz 解壓： tar -xzf mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/ cd /usr/local mv mysql-shell-8.0.23-linux-glibc2.12-x86-64bi mysql-shell
三、終端操作
MySQL操作
創(chuàng)建用戶并授權(quán)
三臺機器均添加
1.創(chuàng)建用戶 create user syh identified by '@Syh2025659'; 2.授權(quán)給用戶 GRANT BACKUP_ADMIN, CLONE_ADMIN, CREATE USER, EXECUTE, FILE, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHUTDOWN, SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'syh'@'%' WITH GRANT OPTION; GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'syh'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'syh'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'syh'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'syh'@'%' WITH GRANT OPTION; GRANT ALL ON *.* to 'syh'@'%'; GRANT ALL PRIVILEGES on *.* to 'syh'@'%' WITH GRANT OPTION; 3.刷新權(quán)限 FLUSH PRIVILEGES;
192.168.252.148:
192.168.252.142:
192.168.252.145:
MySQL-Shell操作
進入mysql-shell
/usr/local/mysql-shell/bin/mysqlsh #啟動
連接主庫信息
\c syh@192.168.252.148:3306
配置集群
dba.configureInstance('syh@192.168.252.148:3306');
dba.configureInstance('syh@192.168.252.142:3306');
dba.configureInstance('syh@192.168.252.145:3306');
創(chuàng)建一個集群
var cluster=dba.createCluster('mysqlcluster');
查看主機群狀態(tài)
dba.getCluster().status();
添加兩個從節(jié)點
var cluster=dba.getCluster();
cluster.addInstance('syh@192.168.252.142:3306');
cluster.addInstance('syh@192.168.252.145:3306');
查看節(jié)點
cluster.status();
清空集群
dba.dropMetadataSchema(); 清除所有集群
四、故障恢復(fù)
安裝mysql-router
192.168.252.148：
wget https://downloads.mysql.com/archives/get/p/41/file/mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz tar xf mysql-router-8.0.23-linux-glibc2.12-x86_64.tar.xz -C /usr/local cd /usr/local mv mysql-router-8.0.23-linux-glibc2.12-x86_64 mysql-router
修改配置文件
首次啟動
192.168.252.148：
cd /usr/local/mysql-router/bin ./mysqlrouter --bootstrap syh@192.168.252.148:3306 --user=root
修改配置文件
vim /usr/local/mysql-router/mysqlrouter.conf #dynamic_state=/usr/local/mysql-router/bin/../var/lib/mysqlrouter/state.json bootstrap_server_addresses=mysql://192.168.252.148:3306,mysql://192.168.252.142:3306,mysql://192.168.252.145:3306
啟動
192.168.252.148：
cd /usr/local/mysql-router/bin ./mysqlrouter -c ../mysqlrouter.conf & netstat -tnpl
停掉主庫
192.168.252.148：
systemctl stop mysqld
操作mysql-shell
192.168.252.148：
/usr/local/mysql-shell/bin/mysqlsh \c syh@192.168.252.145:3306 var cluster=dba.getCluster(); cluster.status();
192.168.252.148:
systemctl start mysqld
再次查看
恢復(fù)！
到此這篇關(guān)于MySQL8.0高可用MIC的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL8.0高可用MIC內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL使用select語句查詢指定表中指定列(字段)的數(shù)據(jù)
本文介紹MySQL數(shù)據(jù)庫中執(zhí)行select查詢語句，查詢指定列的數(shù)據(jù)，即指定字段的數(shù)據(jù),需要的朋友可以參考下2016-11-11
這篇文章主要介紹了CentOS7 64位下MySQL5.7安裝與配置教程，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-08-08
這篇文章主要介紹了MySQL索引失效十種場景與優(yōu)化方案，文中有詳細的代碼示例供參考閱讀，感興趣的朋友可以看一下2023-05-05
MySQL查詢學(xué)習(xí)之基礎(chǔ)查詢操作
這篇文章主要給大家介紹了關(guān)于MySQL查詢學(xué)習(xí)之基礎(chǔ)查詢操作的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
本文詳細描述了如何使用SQL備份文件恢復(fù)MySQL數(shù)據(jù)庫的過程,包括查詢當(dāng)前數(shù)據(jù)庫的字符集和排序規(guī)則、創(chuàng)建恢復(fù)前備份目錄、備份現(xiàn)有數(shù)據(jù)庫、刪除舊庫、重建新庫、使用備份文件覆蓋恢復(fù)、以及恢復(fù)后的檢查和測試,需要的朋友可以參考下2026-03-03
mysql odbc字符集設(shè)置(中文顯示亂碼),碰到這個問題的朋友可以參考下。2011-08-08
這篇文章主要介紹了MySQL用戶權(quán)限驗證與管理方法,結(jié)合實例形式詳細分析了mysql針對用戶權(quán)限的驗證、查看、收回、修改等各種常用操作技巧,需要的朋友可以參考下2018-04-04
解決找回mysql數(shù)據(jù)庫密碼和密碼過期問題
這篇文章主要介紹了解決找回mysql數(shù)據(jù)庫密碼和密碼過期問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
MySQL處理重復(fù)數(shù)據(jù)的學(xué)習(xí)筆記
在本篇文章里小編給大家分享的是一篇關(guān)于MySQL處理重復(fù)數(shù)據(jù)的學(xué)習(xí)筆記，需要的朋友們可以參考下。2020-03-03
這篇文章主要介紹了mysql優(yōu)化利器之explain使用介紹,需要的朋友可以參考下2017-01-01