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

MySQL8.0高可用MIC的實(shí)現(xiàn)

 更新時(shí)間：2024年10月28日 10:30:39   作者：愛喝旺仔的昊昊  
本文介紹了如何實(shí)現(xiàn)MySQL8.0高可用MIC,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

一、機(jī)器準(zhǔn)備

機(jī)器

主機(jī)名

ip地址

角色

軟件

mic-master

192.168.252.148

主節(jié)點(diǎn)

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

三臺機(jī)器均安裝

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é)點(diǎn)修改進(jìn)行修改配置文件

添加配置的模版
server_id=[id]  #每一臺的都不一樣，要唯一
report_host=[當(dāng)前服務(wù)器ip]
report_port=[mysql端口號]
loose-group_replication_ip_whitelist="[ip1],[ip2],[ip3]"

進(jìn)行修改

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

下載鏈接：https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.23-linux-glibc2.12-x86-64bit.tar.gz

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)

三臺機(jī)器均添加

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操作

進(jìn)入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)建一個(gè)集群

var cluster=dba.createCluster('mysqlcluster');

查看主機(jī)群狀態(tài)

dba.getCluster().status();

添加兩個(gè)從節(jié)點(diǎn)

var cluster=dba.getCluster();
cluster.addInstance('syh@192.168.252.142:3306');
cluster.addInstance('syh@192.168.252.145:3306');

查看節(jié)點(diǎn)

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的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL8.0高可用MIC內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • mysql 控制臺程序的提示符 prompt 字符串設(shè)置

    mysql 控制臺程序的提示符 prompt 字符串設(shè)置

    mysql 控制臺程序的提示符 prompt 字符串設(shè)置，學(xué)習(xí)mysql的朋友可以參考下。
    2011-08-08
  • MySQL SHOW PROCESSLIST協(xié)助故障診斷全過程

    MySQL SHOW PROCESSLIST協(xié)助故障診斷全過程

    這篇文章主要給大家介紹了關(guān)于MySQL SHOW PROCESSLIST協(xié)助故障診斷的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-02-02
  • mysql Non-Transactional Database Only(只支持MyISAM)

    mysql Non-Transactional Database Only(只支持MyISAM)

    按照discuz官方的建議，選的都是Non-Transactional Database Only 只支持MyISAM，其實(shí)默認(rèn)都安裝也挺好
    2016-04-04
  • MySQL中鎖的相關(guān)問題

    MySQL中鎖的相關(guān)問題

    這篇文章主要介紹了MySQL中鎖的相關(guān)問題,本文給大家介紹了從對數(shù)據(jù)操作的粒度分 ，從對數(shù)據(jù)操作的類型分，給大家介紹的非常詳細(xì)，需要的朋友可以參考下
    2021-12-12
  • MySQL中登錄與退出超全圖文講解

    MySQL中登錄與退出超全圖文講解

    大多數(shù)人都認(rèn)為在不需要事務(wù)化處理的情況下,MySQL是管理內(nèi)容最好的選擇,下面這篇文章主要給大家介紹了關(guān)于MySQL中登錄與退出圖文講解的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下
    2023-06-06
  • mysql 數(shù)據(jù)表中查找重復(fù)記錄

    mysql 數(shù)據(jù)表中查找重復(fù)記錄

    以下sql語句可以實(shí)現(xiàn)查找出一個(gè)表中的所有重復(fù)的記錄
    2009-07-07
  • MySQL使用ReplicationConnection導(dǎo)致連接失效解決

    MySQL使用ReplicationConnection導(dǎo)致連接失效解決

    這篇文章主要為大家介紹了MySQL使用ReplicationConnection導(dǎo)致連接失效問題分析解決，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪
    2022-07-07
  • MySQL誤操作后快速恢復(fù)數(shù)據(jù)的方法

    MySQL誤操作后快速恢復(fù)數(shù)據(jù)的方法

    這篇文章主要介紹了MySQL誤操作后快速恢復(fù)數(shù)據(jù)的方法,需要的朋友可以參考下
    2016-12-12
  • mysql的3種分表方案

    mysql的3種分表方案

    這篇文章主要介紹了mysql的3種分表方案,先從為什么要分表說起,然后分析了3種方法的優(yōu)劣勢,最后做了一個(gè)總結(jié),需要的朋友可以參考下
    2014-05-05
  • 簡單講解MySQL中的多源復(fù)制

    簡單講解MySQL中的多源復(fù)制

    這篇文章主要介紹了簡單講解MySQL中的多源復(fù)制,多源復(fù)制功能自從5.7.2版本以后被加入MySQL,需要的朋友可以參考下
    2015-04-04

最新評論