原文出處：https://blog.51cto.com/u_16213577/7402163
https://mp.weixin.qq.com/s/zWRDlY5E2y8EDLZFpcwbFQ
1、環境準備
5臺伺服器
192.168.2.34 master01 部署mysql 192.168.2.35 master02 部署mysql 192.168.2.36 slave01 部署mysql 192.168.2.37 slave02 部署mysql
192.168.2.40 mycat 部署jdk1.8，部署mycat，部署mysql
2、安裝mysql8.0資料庫
5臺伺服器均需要執行此指令碼
#編寫mysql安裝指令碼，使用如下指令碼繼續安裝 vi /opt/mysql_install.sh #!/bin/bash #關閉防火牆 systemctl stop firewalld #設定防火牆開機不啟動 systemctl disable firewalld #臨時關閉selinux setenfore 0 #環境清理 rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps echo $? rm -rf /etc/selinux/targeted/active/modules/100/mysql echo $? rm -rf /usr/lib64/mysql echo $? #安裝wget命令，下載mysql8.0的安裝包 yum -y install wget &> null echo $? cd /opt #下載mysql8.0安裝包 wget http://repo.mysql.com/mysql80-community-release-el7.rpm &> null echo $? #安裝mysql8.0 rpm -ivh mysql80-community-release-el7.rpm &> null echo $? yum -y install mysql-community-server &> null echo $? #啟動資料庫 systemctl start mysqld echo $? #查詢mysql初始密碼 p=`grep -i 'password' /var/log/mysqld.log | awk '{print $313}'` echo $? #指令碼執行最後，會列印mysql初始root密碼
3、修改root密碼（5臺均需要修改密碼）
mysql -uroot -p'初始密碼' #初始執行上述指令碼後，會進行列印 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32 Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter user 'root'@'localhost' identified by '1qaz!QAZ'; Query OK, 0 rows affected (0.00 sec)
4、mysql雙主、雙備配置
master01和master02互為主備
master01和slave01為主從複製
master02和slave02為主從複製
192.168.2.34-master01配置
#修改主機名 hostname master01 bash #修改mysql配置檔案 vi /etc/my.cnf [mysql] server-id=34 #mysql服務唯一id log-bin=mysql-bin #存放日誌檔案位置 auto_increment_increment=2 #控制主鍵自增長的步長，幾臺伺服器就設定幾 auto_increment_offset=1 #設定自增起始值，此處是第一臺 replication_do_db=test #設定需要同步的資料庫，不配置預設同步全部
systemctl restart mysqld #重啟資料庫
#在資料庫中建立使用者：master01 mysql -uroot -p'1qaz!QAZ' create user 'master01'@'%' identified with mysql_native_password by '1qaz!QAZ';
#賦予master01使用者許可權 grant replication slave on *.* to ;master01'@'%'; flush privileges; #重新整理資料庫，提交之前的操作到資料庫 change master to master_host='192.168.2.35',master_user='master02',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1;
mysql > start slave; mysql > show slave status; mysql> show master status; #此處查詢的資訊後面配置需要用到 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 157 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec
192.168.2.35-master02配置
#修改主機名 hostname master02 bash #修改mysql配置檔案 vi /etc/my.cnf [mysql] server-id=35 #mysql服務唯一id log-bin=mysql-bin #存放日誌檔案位置 auto_increment_increment=2 #控制主鍵自增長的步長，幾臺伺服器就設定幾 auto_increment_offset=2 #設定自增起始值，此處是第一臺 replication_do_db=test #設定需要同步的資料庫，不配置預設同步全部
systemctl restart mysqld #重啟資料庫
#在資料庫中建立使用者：master01 mysql -uroot -p'1qaz!QAZ' create user 'master02'@'%' identified with mysql_native_password by '1qaz!QAZ';
#賦予master01使用者許可權 grant replication slave on *.* to ;master02'@'%'; flush privileges; #重新整理資料庫，提交之前的操作到資料庫 mysql > change master to master_host='192.168.2.34',master_user='master01',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1;
mysql > start slave; mysql > show slave status; mysql> show master status; #此處查詢的資訊後面配置需要用到 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 157 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec
192.168.2.36-slave01配置
#修改主機名 hostname master01 bash #修改mysql配置檔案 vi /etc/my.cnf [mysql] server-id=36 #mysql服務唯一id
systemctl restart mysqld #重啟資料庫
mysql -uroot -p mysql > change master to master_host='192.168.2.34',master_user='master01',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1; mysql > start slave; mysql > show slave status;
192.168.2.36-slave02配置
#修改主機名 hostname master01 bash #修改mysql配置檔案 vi /etc/my.cnf [mysql] server-id=37 #mysql服務唯一id
systemctl restart mysqld #重啟資料庫
mysql -uroot -p mysql > change master to master_host='192.168.2.35',master_user='master02',master_password='1qaz!QAZ',master_log_file='mysql-bin.000001',master_log_pos=157,GET_MASTER_PUBLIC_KEY=1; mysql > start slave; mysql > show slave status;
至此，mysql雙主雙備已經部署完成
5、安裝資料庫中介軟體Mycat
192.168.2.40 mycat
5.1、安裝mycat需要安裝java環境，此處安裝jdk1.8版本
#jdk1.8安裝包獲取 # https://pan.baidu.com/s/1Z-ZwMHFk6325vD910gickw?pwd=gjf6 #此處將獲取到的jdk安裝包放在/opt目錄下 mkdir -p /usr/local/jdk1.8 cd /opt tar -zxvf jdk-8u191-linux-x64.tar.gz -C /usr/local/jdk1.8 #將以下內容寫入 /etc/profile export JAVA_HOME=/usr/local/jdk1.8 export JRE_HOME=/usr/local/jdk1.8/jre export CLASSPATH=.:$JAVA_HOME/lib/de.jar:$JAVA_HOME/lib/tools.jar$JRE_HOME/lib:$CLASSPATH export PATH=$JAVA_HOME/bin:$PATH source /etc/profile #安裝mycat #mycat安裝包地址 #https://pan.baidu.com/s/1Rlw7h5kfoWgyxE6MO366nw?pwd=6if5 mkdir /usr/local/mycat cd /opt tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local/mycat #然後將如下內容追加到/etc/profile export MYCAT_HOME=/usr/local/mycat export PATH=$MYCAT_HOME/bin:$PATH:$JAVA_HOME/bin source /etc/profile
5.2、mycat配置檔案詳解
#mycat已經安裝完成，此時需修改配置檔案
#mycat有三個主要的配置檔案 /usr/local/mycat/conf/schema.xml #涵蓋了mycat的邏輯庫，邏輯表，分片規則，分片節點和資料來源的配置
/usr/local/mycat/conf/server.xml #主要配置連線時的許可權過濾
/usr/local/mycat/conf/rule.xml #定義分片規則，分片規則決定了邏輯表中的資料以何種方式儲存到不同的資料庫
## schema.xml 配置檔案詳解
#如下是我的schema.xml配置檔案 <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="mycat"> </schema> <dataNode name="mycat" dataHost="master01" database="testdb" /> <dataHost name="master01" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="master01" url="192.168.2.34:3306" user="root" password="1qaz!QAZ"> <readHost host="master02" url="192.168.2.35:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave01" url="192.168.2.36:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave02" url="192.168.2.37:3306" user="root" password="1qaz!QAZ" /> </writeHost> </dataHost> </mycat:schema <!-- <schema name="TESTDB" #邏輯資料庫名稱（必須大寫） checkSQLschema="true" sqlMaxLimit="100" dataNode="mycat"> #定義資料節點名稱 </schema> <dataNode name="mycat" #mycat節點名稱 dataHost="master01" #資料庫例項主機名稱 database="testdb" /> #主機master01上mysql資料庫中存在的資料庫庫名稱 <dataHost name="master01" #節點名稱，與dataNode name一致 maxCon="1000" #最大連線數 minCon="10" #最小連線數 balance="1" #負載均衡方式（0：不開啟讀寫分離，1：雙主雙備模式下，master02、slave01和slave02都參與select語句負載均衡，2：所有操作隨機在writehost和readhost分發，3：所有讀請求都隨機分發到writehost對應的radwrite，writehost不參與讀請求） writeType="0" #寫操作分發方式（0：寫操作轉發到第一個writehost，第一個當機後轉發到第二個，1：隨機轉發到writehost） dbType="mysql" #資料庫型別 dbDriver="native" #資料庫驅動，支援native和jdbc switchType="1" #（1：主從自動切換，2：從機延時超過slave threshold值時切換） slaveThreshold="100"> <heartbeat>select user()</heartbeat> #心跳檢測 <writeHost host="master01" #寫運算元據庫例項主機形成 url="192.168.2.34:3306" #寫運算元據庫ip地址 user="root" #寫運算元據庫使用者名稱 password="1qaz!QAZ"> #寫運算元據庫使用者名稱密碼 #以下配置了3臺讀運算元據庫 <readHost host="master02" url="192.168.2.35:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave01" url="192.168.2.36:3306" user="root" password="1qaz!QAZ" /> <readHost host="slave02" url="192.168.2.37:3306" user="root" password="1qaz!QAZ" /> </writeHost> </dataHost> -->
## server.xml配置檔案詳解 <user name="mycat" defaultAccount="true"> #設定為mycat，便於和root區分 <property name="password">123456</property> <property name="schemas">TESTDB</property> <!-- 表級 DML 許可權設定 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="ilovyou" dml="0000"></table> </schema> </privileges> --> </user> <user name="user"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> <!-- <user name="mycat" defaultAccount="true"> #配置遠端登入mycat的使用者名稱 <property name="password">123456</property> #密碼 <property name="schemas">TESTDB</property> #可以訪問的邏輯資料庫 </user> <user name="user"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> -->
rule.xml 預設配置即可
#此時配置檔案已經修改完畢，啟動mycat cd /usr/local/mycat/bin ./mycat start consol Starting Mycat-server... ./mycat status Mycat-server is running (14149). #檢視mycat程序 ps -ef | grep mycat root 14149 1 0 15:45 ? 00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat root 14151 14149 8 15:45 ? 00:00:04 java -DMYCAT_HOME=. -server -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/log4j-1.2-api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.7.1-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar -Dwrapper.key=WnzqYVt4xpSGqMKn -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=14149 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start root 14536 1777 0 15:46 pts/0 00:00:00 grep --color=auto mycat #檢視mycat埠 netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 14151/java tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1123/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1290/master #遠端登入埠 tcp6 0 0 :::8066 :::* LISTEN 14151/java tcp6 0 0 :::9066 :::* LISTEN 14151/java tcp6 0 0 :::22 :::* LISTEN 1123/sshd tcp6 0 0 ::1:25 :::* LISTEN 1290/master
#管理埠 9066
#資料來源埠 8066
5.3、master01，master02，slave01，slave02賦予root使用者可遠端登入許可權
#因為在schma.xml檔案中配置了root使用者和密碼。mycat連線資料庫要用到，所以此處需要配置可以使用root使用者遠端d登入mysql資料庫
mysql > grant all privileges on *.* to 'root'@'%' with grant option;
mysql> select Host,user from mysql.user;
+-----------+------------------+ | Host | user | +-----------+------------------+ | % | root | # %表示可以遠端d登入 | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 7 rows in set (0.00 sec)
5.4、測試mycat是否可以正常登入
#在mycat（192.168.2.40）這臺伺服器進行測試
[root@xuguangjia bin]# mysql -h 192.168.2.40 -P 8066 -umycat -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> # 可以正常登入 MySQL [(none)]> show databases; #看到在schma.xml檔案中配置的邏輯庫 +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) MySQL [(none)]> use TESTDB; #進入邏輯庫 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [TESTDB]> show tables; +------------------+ | Tables_in_testdb | #表'ilovyou' 在庫testdb中 +------------------+ | ilovyou | #此時插到的TESTDB邏輯庫下有一張表“ilovyou” +------------------+ 1 row in set (0.12 sec) 這是因為在schma.xml檔案中配置瞭如下語句 <dataNode name="mycat" dataHost="master01" database="testdb" /> 此時的邏輯庫TESTDB 對應mysql資料庫中的testdb資料庫 #此時我們在master01的資料庫檢視所有的資料庫 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | | xuguangjia | +--------------------+ 6 rows in set (0.01 sec) mysql> use testdb; #進入testdb庫 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; #檢視錶 +------------------+ | Tables_in_testdb | +------------------+ | ilovyou | +------------------+ 1 row in set (0.00 sec) #至此，mycat實現mysq雙主雙備讀寫分離實驗配置完成。 #修改日誌mycat日誌級別，這樣j就可以檢視寫操作和讀操作分別在那臺伺服器上 vi /usr/local/mycat/conf/log4j2.xml ... <asyncRoot level="info" includeLocation="true"> ... ... ## info 修改為dubug <asyncRoot level="debug" includeLocation="true"> ... #然後重啟mycat cd /usr/local/mycat/bin ./mycat restart
Keepalived+Mysql雙活實現Mysql高可用
1、Mysql-8.0雙主部署
1.1、環境準備
iptables -F systemctl stop firewalld #關閉防火牆 setenfore 0 #關閉selinux
rpm -qa | grep mysql rpm -qa | grep mariadb mariadb-libs-5.5.68-1.el7.x86_64
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
find / -name mariadb find / -name mysql
/etc/selinux/targeted/active/modules/100/mysql /usr/lib64/mysql
rm -rf /etc/selinux/targeted/active/modules/100/mysql rm -rf /usr/lib64/mysql
1.2、安裝mysql（兩臺伺服器均需要安裝）
yum -y install wget #安裝wgt命令。用於下載mysql安裝包 wget http://repo.mysql.com/mysql80-community-release-el7.rpm rpm -ivh mysql80-community-release-el7.rpm yum -y install mysql-community-server
systemctl start mysqld #啟動mysql
grep -i 'password' /var/log/mysql.log #查詢mysql初始密
2023-03-23T06:39:43.463553Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q_gkjqeR1iGA #冒號後面的一串字元就是mysql初始密碼
1.3、修改mysql初始密碼
mysql -uroot -p'q_gkjqeR1iGA' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.32 Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter user 'root'@'localhost' identified by '1qaz!QAZ'; Query OK, 0 rows affected (0.00 sec)
1.4、配置主主同步
Master01配置
#修改主機名 hostname master01 bash #修改mysql配置檔案 vi /etc/my.cnf [mysql] server-id=11 #mysql服務唯一id log-bin=mysql-bin #存放日誌檔案位置 auto_increment_increment=2 #控制主鍵自增長的步長，幾臺伺服器就設定幾 、auto_increment_offset=1 #設定自增起始值，此處是第一臺 replication_do_db=test #設定需要同步的資料庫，不配置預設同步全部
systemctl restart mysqld #重啟資料庫
#在資料庫中建立使用者：master01 mysql -uroot -p'1qaz!QAZ' create user 'master01'@'%' identified with mysql_native_password by '1qaz!QAZ'; #賦予master01使用者許可權 grant replication slave on *.* to ;master01'@'%';
flush privileges; #重新整理資料庫，提交之前的操縱到資料庫
mysql> show master status; #此處查詢的資訊後面配置需要用到 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000005 | 2709 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 se
master02配置
#修改主機名 hostname master02 bash
#修改mysql配置檔案 vi /etc/my.cnf mysql-server=22 log-bin=mysql-bin auto_increment_increment=2 auto_increment_offset=2 replicate-do-db=test systemctl restart mysqld mysql -uroot -p'1qaz!QAZ'
#繫結master01資料庫 change master to master_host='master01-ip',master_user='master01',master_password='1qaz!QAZ',master_log_file='mysql-bin.000005',master_log_pos=2709,GET_MASTER_PUBLIC_KEY=1;
start slave; #啟動備份
mysql> show slave status \G #檢視配置狀態 *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.2.31 Master_User: master31 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 2709 Relay_Log_File: master2-relay-bin.000002 Relay_Log_Pos: 1102 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: demo_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: 2709 Relay_Log_Space: 1314 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: 31 Master_UUID: 839c6949-98f6-11ee-beb4-000c299a17cf 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: Executed_Gtid_Set: Auto_Position: 0 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) #至此，master02為master01的從資料庫已經配置完成。
master02配置（此時需要配置master01為master02的從資料庫）
#master02上進行配置 mysql -uroot -p'1qaz!QAZ'
create user 'master02'@'%' identified with mysql_native_password by '1qaz!QAz'; grant replication slave on *.* to 'master02'@'%';
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1965 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
master01進行配置（配置master01為master02的從資料庫）
mysql -uroot -p'1qaz!QAZ'
change master to master_host='master02-ip',master_user='master02',master_password='1qaz!QAZ',master_log_file='mysql-bin.000005',master_log_pos=1965,GET_MASTER_PUBLIC_KEY=1; start slave;
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.2.32 Master_User: master32 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1965 Relay_Log_File: master1-relay-bin.000002 Relay_Log_Pos: 646 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: demo_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: 1965 Relay_Log_Space: 858 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: 32 Master_UUID: d75926e6-98f6-11ee-80c7-000c2952c327 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: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set, 1 warning (0.00 sec) #至此，mysql主主同步已經配置w完成
2、keepalived原始碼安裝部署（master01和master02分別安裝keepalived，安裝步驟相同，配置檔案不同）
2.1安裝依賴包，下載keepalived2.2.8版本至/opt目錄
#安裝依賴包 yum -y install gcc openssl-devel popt-devel psmisc
#安裝wget命令，用於下載keepalived安裝包 yum -y install wget
#下載keepalived安裝包到/opt目錄 wget -p /opt --no-check-certificate https://www.keepalived.org/software/keepalived-2.2.8.tar.gz
#進入opt目錄 cd /opt
#在/opt目錄下進行解壓
tar -zxvf keepalived-2.2.8.tar.gz ls -l keepalived-2.2.8
總用量 1768 -rw-rw-r--. 1 keepalived keepalived 44361 5月 31 2023 aclocal.m4 -rw-rw-r--. 1 keepalived keepalived 41 9月 28 2009 AUTHOR -rwxrwxr-x. 1 keepalived keepalived 100 12月 21 2020 autogen.sh drwxr-xr-x. 2 root root 39 12月 14 18:31 bin drwxrwxr-x. 2 keepalived keepalived 60 12月 14 18:30 bin_install drwxrwxr-x. 2 keepalived keepalived 83 5月 31 2023 build-aux -rwxrwxr-x. 1 keepalived keepalived 100 5月 5 2020 build_setup -rw-rw-r--. 1 keepalived keepalived 507110 1月 27 2021 ChangeLog -rw-r--r--. 1 root root 461338 12月 14 18:30 config.log -rwxr-xr-x. 1 root root 46595 12月 14 18:30 config.status -rwxrwxr-x. 1 keepalived keepalived 448153 5月 31 2023 configure -rw-rw-r--. 1 keepalived keepalived 116637 5月 31 2023 configure.ac -rw-rw-r--. 1 keepalived keepalived 823 12月 25 2017 CONTRIBUTORS -rw-rw-r--. 1 keepalived keepalived 18092 11月 20 2012 COPYING drwxrwxr-x. 5 keepalived keepalived 226 12月 14 18:30 doc -rw-r--r--. 1 root root 2279 12月 14 18:30 Dockerfile -rw-rw-r--. 1 keepalived keepalived 2720 11月 21 2021 Dockerfile.in -rw-rw-r--. 1 keepalived keepalived 9940 8月 30 2022 INSTALL drwxrwxr-x. 11 keepalived keepalived 4096 12月 14 18:31 keepalived -rw-r--r--. 1 root root 9363 12月 14 18:30 keepalived.spec -rw-rw-r--. 1 keepalived keepalived 10701 1月 25 2022 keepalived.spec.in drwxrwxr-x. 3 keepalived keepalived 4096 12月 14 18:31 lib drwxrwxr-x. 2 keepalived keepalived 43 5月 31 2023 m4 -rw-r--r--. 1 root root 32582 12月 14 18:30 Makefile -rw-rw-r--. 1 keepalived keepalived 3212 11月 21 2021 Makefile.am -rw-rw-r--. 1 keepalived keepalived 31923 5月 31 2023 Makefile.in -rw-r--r--. 1 root root 1963 12月 14 18:31 README -rw-rw-r--. 1 keepalived keepalived 2812 2月 26 2021 README.md drwxrwxr-x. 3 keepalived keepalived 41 7月 7 2022 snap -rw-rw-r--. 1 keepalived keepalived 6898 3月 15 2021 TODO drwxrwxr-x. 2 keepalived keepalived 26 5月 31 2023 tools
2.2配置keepalived，並且進行編譯安裝
#進入解壓目錄 cd keepalived-2.2.8 #進行編譯安裝 ./configure --prefix=/opt/keepalived-2.2.8 make && make install
2.3將keepalived檔案複製到對應目錄下
#建立keepalived配置檔案存放目錄
mkdir /etc/keepalived cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
cp keepalived/etc/init.d/keepalived /etc/init.d/
cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp bin/keepalived /usr/sbin/
2.4建立kill_keepalived.sh指令碼
#建立停止keepaliecd服務的指令碼，後續會用到
cd /etc/keepalived vi kill_keepalived.sh
#!/bin/bash /etc/init.d/keepalived stop
#給予指令碼執行許可權 chmod +x /etc/keepalived/kill_keepalived.sh
2.5ifconfig檢視網路卡名稱
#檢視網名稱 ifconfig #本機網路卡名稱為ens33 ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.2.31 netmask 255.255.255.0 broadcast 192.168.2.255 inet6 fe80::8683:d772:2e3:ed49 prefixlen 64 scopeid 0x20<link> ether 00:0c:29:9a:17:cf txqueuelen 1000 (Ethernet) RX packets 120145 bytes 73062159 (69.6 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 123773 bytes 39409818 (37.5 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10<host> loop txqueuelen 1000 (Local Loopback) RX packets 22235 bytes 1536984 (1.4 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 22235 bytes 1536984 (1.4 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
2.6修改master01伺服器的keepalived配置檔案
#先對原配置檔案進行備份 mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived_back.conf
#配置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 smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL -1 #主機識別符號，唯一即可 # router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state BACKUP #標識keepalived角色，建議都設定為buckup，然後以優先順序為主 interface ens33 #網路卡名稱 virtual_router_id 151 #虛擬路由標識，兩者保持一致 priority 120 #優先順序，用來選舉master advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #keepalived虛擬出來的ip地址 192.168.2.50 192.168.2.51 192.168.2.52 } }
virtual_server 192.168.2.50 3306 { #虛擬地址和埠 delay_loop 2 #設定執行情況檢查時間，單位秒 lb_algo rr #設定後端排程器演算法，rr為輪詢演算法 lb_kind DR #設定lvs實現負載均衡的機制，DR，NAT，TUN三種模式 persistence_timeout 50 #會話保持時間，單位秒 protocol TCP #指定轉發協議，tcp和udp real_server 192.168.2.31 3306 { #本機真實ip地址和+3306埠 weight 5 #伺服器的權重值，權重越高，伺服器在負載均衡中被選中的機率就越高 notify_down /etc/keepalived/kill_keepalived.sh #檢測mysql程序，一旦mysql程序停止，就執行kill_keepalived.sh指令碼 TCP_CHECK { connect_ip 192.168.2.31 #實際物理機ip地址 connect_port 3306 #實際物理機埠 connect_timeout 3 retry 3 delay_before_retry 3 } } }
2.7修改master02伺服器keepalived配置檔案
#先對原配置檔案進行備份 mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived_back.conf #配置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 smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL -2 #主機識別符號，唯一即可 # router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state BACKUP #標識keepalived角色，建議都設定為buckup，然後以優先順序為主 interface ens33 #網路卡名稱 virtual_router_id 151 #虛擬路由標識，兩者保持一致 priority 100 #優先順序，用來選舉master advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #keepalived虛擬出來的ip地址 192.168.2.50 192.168.2.51 192.168.2.52 } } virtual_server 192.168.2.50 3306 { #虛擬地址和埠 delay_loop 2 #設定執行情況檢查時間，單位秒 lb_algo rr #設定後端排程器演算法，rr為輪詢演算法 lb_kind DR #設定lvs實現負載均衡的機制，DR，NAT，TUN三種模式 persistence_timeout 50 #會話保持時間，單位秒 protocol TCP #指定轉發協議，tcp和udp real_server 192.168.2.31 3306 { #本機真實ip地址和+3306埠 weight 5 #伺服器的權重值，權重越高，伺服器在負載均衡中被選中的機率就越高 notify_down /etc/keepalived/kill_keepalived.sh #檢測mysql程序，一旦mysql程序停止，就執行kill_keepalived.sh指令碼 TCP_CHECK { connect_ip 192.168.2.32 #實際物理機ip地址 connect_port 3306 #實際物理機埠 connect_timeout 3 retry 3 delay_before_retry 3 } } }
2.8啟動keepalived（兩臺伺服器均需要啟動）
/etc/init.d/keepalived start Starting keepalived (via systemctl): [ 確定 ] /etc/init.d/keepalived status ● keepalived.service - SYSV: Start and stop Keepalived Loaded: loaded (/etc/rc.d/init.d/keepalived; bad; vendor preset: disabled) Active: active (running) since 四 2023-12-14 21:18:36 CST; 55min ago Docs: man:systemd-sysv-generator(8) Process: 61589 ExecStop=/etc/rc.d/init.d/keepalived stop (code=exited, status=0/SUCCESS) Process: 61940 ExecStart=/etc/rc.d/init.d/keepalived start (code=exited, status=0/SUCCESS) Main PID: 61959 (keepalived) CGroup: /system.slice/keepalived.service ├─61959 keepalived -D ├─61960 keepalived -D └─61961 keepalived -D 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.50 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.51 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.52 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.50 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.51 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.52 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.50 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.51 12月 14 21:18:44 xu******** Keepalived_vrrp[61961]: Sending gratuitous ARP on ens33 for 192.168.2.52 12月 14 21:55:46 xu******** Keepalived_healthcheckers[61960]: A thread timer expired 1.459160 seconds ago
2.9配置虛擬ip登入使用者
#建立使用者keepalived useradd -m keepalived #修改密碼 passwd keepalived 1qaz!QAZ
使用192.168.2.50虛擬地址ssh遠端登入伺服器，實際地址為192.168.2.31
此時停止master01上的keepalived程序，再次進行登入檢視
#停止kddpalived程序 /etc/init.d/keepalived stop Stopping keepalived (via systemctl): [ 確定 ]
此時的虛擬ip地址依舊是192.168.2.50，但是物理機ip地址已經自動切換到了192.168.2.32
綜上測試可知keepalived部署成功