2022年MySQLMM+KeepAlived高可用架構部署測試

1、MySQL MM+KeepAlived高可用架構部署測試馬飛2022-2-23 一、環(huán)境預備1.1 操作系統(tǒng)信息安裝三臺 CentOS6.7 64 位操作系統(tǒng)；采納最小安裝模式；sed -i s/=enforcing/=disabled/g /etc/selinux/config service itpables stop chkconfig iptables off 1.2 配置 NTP同步時間yum -y install ntp vi /etc/ntp.conf 增加 3 個時間服務器server iburst # 西南地區(qū)網絡中心 server iburst # 西北地區(qū)網絡中心 ser

2、ver iburst # 東北地區(qū)網絡中心啟動 ntp 服務systemctl start ntpd systemctl enable ntpd 查看狀態(tài)ntpq -p 1.3 配置角色信息主機名IP 地址描述Server id Db1 00 Master1 1 Db2 01 Master2 2 Db3 02 Slave1 3 1.4 服務角色及描述IP 地址角色描述00 ReadWriter 應用程序連接該IP 對主庫進寫懇求01 ReadWriter 應用程序連接該IP 對主庫進寫

3、懇求02 Read Only 應用程序連接到該IP 處理讀懇求40 ReadWriter 應用程序連接到該虛IP 處理讀寫懇求 （不直聯100,101）,通過連接 140 實現高可用；1.5 配置 /etc/hosts 在其中添加全部的主機信息：rootdb1 mysql# more /etc/hosts localhost localhost.localdomain localhost4 localhost4.localdomain4 :1 localhost localhost.localdomain localhost6 l

4、ocalhost6.localdomain6 2 db1 3 db2 0 b3二、安裝 MySQL 2.1 卸載 rpm 包roothadoop61 # rpm -qa | grep -i mysql mysql-5.1.73-5.el6_7.1.x86_64 mysql-server-5.1.73-5.el6_7.1.x86_64 perl-DBD-MySQL-4.013-3.el6.x86_64 mysql-devel-5.1.73-5.el6_7.1.x86_64 mysql-libs-5.1.73-5.el6_7.1.x

5、86_64 rpm -ev mysql-devel-5.1.73-5.el6_7.1.x86_64 rpm -ev mysql-server-5.1.73-5.el6_7.1.x86_64 rpm -ev perl-DBD-MySQL-4.013-3.el6.x86_64 rpm -ev mysql-5.1.73-5.el6_7.1.x86_64 roothadoop61 # rpm -e -nodeps mysql-libs-5.1.73-5.el6_7.1.x86_64 warning: /etc/f saved as /etc/f.rpmsave 2.2 收集 mysql 對應的文件夾r

6、oothadoop61 # find / -name mysql /var/lib/mysql /var/lib/mysql/mysql 2.3 刪除 mysql 對應的文件夾rm -rf /var/lib/mysql 2.4 下載 mysql 安裝程序2.5 解壓 mysql 安裝程序tar -zxvf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz -C /usr/local cd /usr/local mv mysql-5.6.31-linux-glibc2.5-x86_64 mysql5.6 2.6 添加用戶和組groupadd mysql user

7、add -r -g mysql mysql 2.7 安裝數據庫進入安裝 mysql 軟件目錄：cd /usr/local/mysql 修改當前目錄擁有者為 mysql 用戶：chown -R mysql:mysql ./ 安裝數據庫：mkdir -p /data/mysql/data chown -R mysql.mysql /data/mysql #如是最小安裝系統(tǒng),仍需要安裝以下包：yum -y install perl perl-devel yum -y install libaio ./scripts/mysql_install_db -basedir=/usr/local/mysql

8、5.6 -datadir=/data/mysql/data -user=mysql 修改配置文件：cp /usr/local/mysql5.6/f /etc/f # vi /etc/f mysqld basedir=/usr/local/mysql datadir= /data/mysql/data port=3306 sql_mode=NO_ENGINE_SUBSTITUTIO character-set-server=utf8 mysql default-character-set=utf8 client default-character-set=utf8 啟動腳本放到開機初始化目錄cp

9、 support- /etc/init.d/mysql 啟動 mysql 服務：service mysql start 查看 mysql 進程：ps -ef|grep mysql 三、修改 MySQL配置db1,db2,db3 三臺主機上分別建立如下目錄 mkdir /var/log/mysql & chown -R mysql:mysql /var/log/mysql/ 3.1 db1配置mysqld basedir=/usr/local/mysql datadir=/data/mysql/data port=3306 sql_mode=NO_ENGINE_SUBSTITUTIO chara

10、cter-set-server=utf8 server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 1024M log_slave_updates = 1 auto-incr

11、ement-increment = 2 auto-increment-offset = 1 binlog_format = MIXED replicate-ignore-db = mysql binlog-ignore-db = mysql mysql default-character-set=utf8 client default-character-set=utf8 3.2 db2配置mysqld basedir=/usr/local/mysql datadir=/data/mysql/data port=3306 sql_mode=NO_ENGINE_SUBSTITUTIO chara

12、cter-set-server=utf8 server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 1024M log_slave_updates = 1 auto-incr

13、ement-increment = 2 auto-increment-offset = 2 binlog_format = MIXED replicate-ignore-db = mysql binlog-ignore-db = mysql mysql default-character-set=utf8 client default-character-set=utf8 3.3 db3配置mysqld basedir=/usr/local/mysql datadir=/data/mysql/data port=3306 sql_mode=NO_ENGINE_SUBSTITUTIO chara

14、cter-set-server=utf8 server_id = 3 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 1024M log_slave_updates = 1 binlog_fo

15、rmat = MIXED replicate-ignore-db = mysql binlog-ignore-db = mysql read-only mysql default-character-set=utf8 client default-character-set=utf8 四、 MySQL復制配置從 master-001 復制到 master-002 從 master-002 復制到 slave-001 4.1 配置 db1 作為主復制到 db2 （1）配置 db1 由于這里是配置db1 做為源數據,所以不需要設置change master 和啟動 slave 進程#在 db1 上

16、創(chuàng)建復制用戶 grant replication slave on *.* to repl192.168.8.% identified by 123456; #從 db1 上導出數據 mysqldump -master-data=2 -single-transaction -R -triggers -A all.sql #拷貝 all.sql 至 db2,db3 scp all.sql rootdb2:/usr/local/mysql5.6 scp all.sql rootdb3:/usr/local/mysql5.6（2）配置 db2 #將 all.sql 導入 db2,db3 從庫 mys

17、ql -f -default-character-set=utf8 show slave statusG * 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 12 Master_User: replication Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-master-002-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-master-

18、001-bin.000004 Relay_Log_Pos: 294 Relay_Master_Log_File: mysql-master-002-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Sk

19、ip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 472 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_Err

20、no: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4 Master_Info_File: /opt/mysql/data/ SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waitin

21、g for the slave I/O thread to update it 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 1 row in set 0.00 sec 留意 : Slave_IO_Running: Yes Slave_SQL_Running: Yes 4.2 配置

22、 db1 作為主復制到 db3 （1）配置 db3 指定change master 為 db1,表示從 db1 復制到db3 #ssh 到 db3 服務器mysql -u root -p #在 mysql 掌握臺錄入#這個是在 f 中沒有使用 CHANGE MASTER TO GTIDMySQL5.6新特性 下的命令：MASTER_HOST=3, MASTER_USER=repl, MASTER_PASSWORD=repl, MASTER_PORT=3306, MASTER_LOG_FILE=mysql-bin.000007, MASTER_LOG_POS=120, MA

23、STER_CONNECT_RETRY=10; # 這個是在 f 中使用 GTIDMySQL5.6新特性 下的命令 CHANGE MASTER TO MASTER_HOST=3, MASTER_USER=repl, MASTER_PASSWORD=repl, MASTER_PORT=3306, MASTER_AUTO_POSITION = 1; #重置 reset reset slave; #啟動 slave start slave; #查看 slave 狀態(tài) show slave statusG #結果如下mysql show slave statusG 4.3 配置 d

24、b2 作為主復制到 db1 （1）配置 db1 指定change master 為 db2,表示從 db2 復制到db1 #ssh 到 master-001 服務器 mysql -u root -p #在 mysql 掌握臺錄入,這個是在f 中沒有使用GTIDMySQL5.6新特性 下的命令CHANGE MASTER TO MASTER_HOST=3, MASTER_USER=repl, MASTER_PASSWORD=repl, MASTER_PORT=3306, MASTER_LOG_FILE=mysql-bin.000007, MASTER_LOG_POS=1733

25、12, MASTER_CONNECT_RETRY=10; #這個是在 f 中使用 GTIDMySQL5.6新特性 下的命令 CHANGE MASTER TO MASTER_HOST=3, MASTER_USER=repl, MASTER_PASSWORD=repl, MASTER_PORT=3306, MASTER_AUTO_POSITION = 1; #重置 reset reset slave; #啟動 slave start slave; #查看 slave 狀態(tài) show slave statusG #結果如下 mysql show slave statusG *

26、1. row * Slave_IO_State: Waiting for master to send event Master_Host: 12 Master_User: replication Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-master-002-bin.000001 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-master-001-bin.000004 Relay_Log_Pos: 294 Relay_Master_Log_File

27、: mysql-master-002-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 120 Relay_Log_Space:

28、 472 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:

29、Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4 Master_Info_File: /opt/mysql/data/ SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Co

30、unt: 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 1 row in set 0.00 sec 留意 : Slave_IO_Running: Yes Slave_SQL_Running: Yes 4.4 MM 環(huán)境測試在 db1 上,增刪改表空間,表,字段,內容可以同步到db2,db3 在 db2 上,增刪改表空間,

31、表,字段,內容可以同步到db1,db3 五、 keepalived安裝5.1、軟件下載（1）軟件下載keepalived-1.2.7.tar.gz（2）內核參數修改留意：修改修改操作系統(tǒng)內核參數“net.ipv4.ip_forward”#修改內核參數“net.ipv4.ip_forward”vi /etc/sysctl.conf # Controls IP packet forwarding net.ipv4.ip_forward = 1 #使配置生效 sysctl -p 功能：設置或重新設置聯網功能,如IP 轉發(fā)、IP 碎片去除以及源路由檢查等； 用戶只需要編輯 /etc/sysctl.co

32、nf 文件,即可手工或自動執(zhí)行由 sysctl 掌握的功能；（3）系統(tǒng)環(huán)境操作系統(tǒng)角色IP 地址Keepalived版本CentOS6.7 主角色00 1.2.7 CentOS6.7 主角色01 1.2.7 CentOS6.7 備角色02 5.2、軟件安裝（1）解壓軟件 tar zxvf keepalived-1.2.7.tar.gz cd keepalived-1.2.7 （2）安裝工具 yum install -y gcc openssl-devel popt-devel 如是本地掛載光盤鏡像就使用如下方法：yum -dis

33、ablerepo=* -enablerepo=c6-media install -y gcc openssl-devel popt-devel 安裝上述工具編譯有以下警告信息：configure: WARNING: keepalived will be built without libnl support. Keepalived configuration - Keepalived version : 1.2.7 Compiler : gcc Compiler flags : -g -O2 Extra Lib : -lpopt -lssl -lcrypto Use IPVS Framewor

34、k : Yes IPVS sync daemon support : Yes IPVS use libnl : No Use VRRP Framework : Yes Use VRRP VMAC : Yes SNMP support : No Use Debug flags : Nos 解決方法：yum -y install libnl* Keepalived configuration - Keepalived version : 1.2.7 Compiler : gcc Compiler flags : -g -O2 Extra Lib : -lpopt -lssl -lcrypto -l

35、nl Use IPVS Framework : Yes IPVS sync daemon support : Yes IPVS use libnl : Yes Use VRRP Framework : Yes Use VRRP VMAC : Yes SNMP support : No Use Debug flags : No #將包下載至本地并進行安裝 mkdir /root/libnl3 yum install -downloadonly -downloaddir=/root/libnl3 libnl* cd /root/libnl3 rpm -ivh *.rpm （3）編譯安裝軟件./co

36、nfigure make & make install configure.txt（4）軟件配置cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d/ cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ chmod +x /etc/init.d/keepalived chkconfig -add keepalived chkconfig keepalived on mkdir /etc/keepalived ln -s /usr/local/sbin/keepalived /usr/s

37、bin/ 主節(jié)點配置文件 :/etc/keepalived/ keepalived.conf check_oracle_status.sh備節(jié)點配置文件：keepalived.conf check_oracle_status.sh（5）啟動服務rootdb9 keepalived# service keepalived start 確定 正在啟動 keepalived： rootdb9 keepalived# service keepalived status keepalived pid 30799 正在運行 . rootdb9 keepalived# ps -ef | grep keep

38、root 30799 1 0 13:25 . 00:00:00 keepalived -D root 30800 30799 0 13:25 . 00:00:00 keepalived -D root 30802 30799 0 13:25 . 00:00:00 keepalived -D root 30879 20806 0 13:25 pts/0 00:00:00 grep keep （6）配置日志文件#修改 keepalived 配置文件 vi /etc/sysconfig/keepalived KEEPALIVED_OPTIONS=-D -d -S 0 #修改系統(tǒng)日志配置文件 vi /

39、etc/rsyslog.conf local0.* /var/log/keepalived.log #重啟系統(tǒng)日志服務 /etc/init.d/rsyslog restart #查詢 keepalived 日志 more /var/log/keepalived.log 5.3、安裝問題（1）啟動 keepalived 服務 rootdb9 keepalived-1.2.7# service keepalived status keepalived 已死,但是 subsys 被鎖文件；rootdb9 keepalived-1.2.7# service keepalived stop 停止 kee

40、palived： 失敗 問題緣由：在啟動前沒有置/etc/keeplivied/keeplivied.conf（2）刪除虛擬網卡地址 ip addr del 01/32 dev eth1 （3）刪除 keepalived find / -name keepalived （4） 腳本不能啟動緣由 主從節(jié)點,以下腳本無執(zhí)行權限chmod u+x /usr/local/script/check_oracle_status.sh 5.4、軟件測試（1）主節(jié)點啟動服務 service keepalived start 查看 VIP 情形 ip addr | grep 20.255.

41、82.201/32 inet 01/32 scope global eth1 查看服務運行情形： service keepalived status 查看主節(jié)點日志情形more /var/log/keepalived.log （2）從節(jié)點啟動服務 service keepalived start 查看 VIP 情形： ip addr | grep 01/32 inet 01/32 scope global eth1 查看服務運行情形： service keepalived status 查看主節(jié)點日志情形 more /var/l

42、og/keepalived.log 六、 keepalived 配置（1）db01 上 keepalived.conf 文件內容rootdb1 mysql# more /etc/keepalived/keepalived.conf #. Configuration keepalived global_defs router_id db1 #修改為自己的主機名 vrrp_instance VI_1 state BACKUP #都修改成 BACKUP interface eth0 virtual_router_id 60 #默認 51 主從都修改為60 90,備用節(jié)點必需比主節(jié)priority 1

43、00 #優(yōu)先級（ 1-254 之間）,另一臺改為點優(yōu)先級低；advert_int 1 nopreempt #不搶占資源,意思就是它活了之后也不會再把主搶回來 authentication #設置驗證信息,兩個節(jié)點必需一樣 auth_type PASS auth_pass 1111 virtual_ipaddress 40 virtual_server 40 3306 delay_loop 6 lb_algo wrr #LVS算法 lb_kind DR #LVS模式 nat_mask persistence_timeout

44、50 # 會話保持時間 protocol TCP real_server 00 3306 weight 1 notify_down /usr/local/script/mysql.sh #檢測到服務down 后執(zhí)行的腳本TCP_CHECK connect_timeout 10 #連接超時時間 nb_get_retry 3 #重連次數 connect_port 3306 #健康檢查端口 （2）db02 上 keepalived.conf 文件內容rootdb2 mysql# more /etc/keepalived/keepalived.conf #. Configurat

45、ion keepalived global_defs router_id db2 #修改為自己的主機名 vrrp_instance VI_1 state BACKUP #都修改成 BACKUP interface eth0 virtual_router_id 60 #默認 51 主從都修改為 60 priority 80 #在 mysql-ha1 上 LVS上修改成 100 advert_int 1 authentication auth_type PASS auth_pass 1111 virtual_ipaddress 40 virtual_server 40 3306 delay_