架構圖如下:
1.按照架構圖所示,準備機器,做好時間同步,主機名解析
192.168.42.150 node1 [proxySQL keepalived]
192.168.42.151 node2 [proxySQL keepalived]
192.168.42.152 node3 [mysql-master wha]
192.168.42.153 node4 [mysql-slave1 wha]
192.168.42.154 node5 [mysql-slave2 wha]
192.168.42.155 node6 [mysql-slave3 wha]
2.我們先做主從復制
(1).在node3,node4,node5,node6上分別安裝mariadb
yum install mariadb mariadb-server -y
(2).node3配置/etc/my.cnf.d/server.conf
node3[master]:
vim /etc/my.cnf.d/server.cnf [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 1 log_bin = log-bin
(3).啟動node3節點的mariadb
systemctl start mariadb.service
(4).登錄mysql,創建主從復制賬號
grant all privileges on *.* to 'mageedu'@'192.168.42.%' identified by '123456' grant replication slave,replication client on *.* to 'repuser'@'192.168.42.%' identified by 'repass'; flush privileges;
(5).配置其他從節點
node3:
[mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 11 relay_log=relay-log read_only=ON
node4:
[mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 12 relay_log=relay-log read_only=ON
node5:
[mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 13 relay_log=relay-log read_only=ON
配置完之后,啟動mariadb
(6).啟動從節點的slave(各個節點)
寫這個之前需要在master節點上查看:
MariaDB [(none)]> show master logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | log-bin.000001 | 30331 | | log-bin.000002 | 1038814 | | log-bin.000003 | 514 | | log-bin.000004 | 245 | +----------------+-----------+ 4 rows in set (0.00 sec)
然后啟動從節點mysql
CHANGE MASTER TO MASTER_HOST='192.168.42.152',MASTER_USER='repuser',MASTER_PASSWORD='repass',MASTER_LOG_FILE='log-bin.000004',MASTER_LOG_POS=245;
START SLAVE ;
SHOW SLAVE STATUS\G;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.42.152
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000004
Read_Master_Log_Pos: 245
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 527
Relay_Master_Log_File: log-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_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: 245
Relay_Log_Space: 815
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: 1
1 row in set (0.00 sec)
至此主從復制就完成了
(7).測試主從復制
在master上創建helloword數據庫:
MariaDB [(none)]> create database helloword; Query OK, 1 row affected (0.00 sec)
查看一下子:
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | helloword | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
然后在各個從節點上查看:
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | helloword | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec)
測試成功.
3.現在我們來做主從復制的讀寫分離
(1).在node1,node2上分別安裝ProxySQL.
下載ProxySQL:
wget -c ftp://172.16.0.1/pub/Sources/7.x86_64/proxysql/proxysql-1.3.6-1-centos7.x86_64.rpm yum install proxysql-1.3.6-1-centos7.x86_64.rpm -y
安裝mariadb客戶端
yum install mariadb -y
(2).配置ProxySQL：
vim /etc/proxysql.cnf
配置示例：
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:3306;/tmp/mysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
{
address = "192.168.42.152" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = "192.168.42.153"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
},
{
address = "192.168.42.154"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
},
{
address = "192.168.42.155"
port = 3306
hostgroup = 1
status = "ONLINE"
weight = 1
compression = 0
}
)
mysql_users:
(
{
username = "mageedu"
password = "123456"
default_hostgroup = 0
max_connections=1000
default_schema="test"
active = 1
}
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
{
writer_hostgroup=0
reader_hostgroup=1
}
)
(3).啟動proxySQL
[root@node2 init.d]# service proxysql start Starting ProxySQL: DONE! [root@node2 init.d]# ss -tnl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 127.0.0.1:6032 *:* LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::*
(4).連接mysql,查看一下子
[root@node1 ~]# mysql -umageedu -p123456 -h 192.168.42.152 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 387 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellowword | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec)
(5).將另一個節點node2也配置下,并啟動測試一下
4.將node1和node2的proxysql做成高可用(讀寫分離高可用)
(1).node1和node2分別安裝keepalived
yum install keepalived -y
(2).node1的keepalived的配置:
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from keepalived@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node0
vrrp_mcast_group4 224.1.101.23
}
#存在文件時，檢測成功，即執行降級；否則不存在,全部退出；實現服務器切換
vrrp_script chk_down{
script "[[ -f /etc/keepalived/down ]] && exit 1 || exit 0"
interval 1
weight -10
fall 1
rize 1
}
#腳本，健康狀態檢測，檢測proxysql是否存活
vrrp_script chk_proxysql {
script "killall -0 proxysql && exit 0 || exit 1"
interval 1
weight -10
fall 1
rise 1
}
vrrp_instance sr1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass rEiszbuO
}
virtual_ipaddress {
192.168.42.182/24 dev ens33 label ens33:0
}
#腳本調用
track_script {
chk_down
chk_proxysql
}
notify_master "/etc/keepalived/notify.sh master"
notify_backup "/etc/keepalived/notify.sh backup"
notify_fault "/etc/keepalived/notify.sh fault"
}
(3).node2的keepalived配置
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from keepalived@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node1
vrrp_mcast_group4 224.1.101.23
}
#存在文件時，檢測成功，即執行降級；否則不存在,全部退出；實現服務器切換
vrrp_script chk_down{
script "[[ -f /etc/keepalived/down ]] && exit 1 || exit 0"
interval 1
weight -10
fall 1
rize 1
}
#腳本，健康狀態檢測，檢測proxysql是否存活
vrrp_script chk_proxysql {
script "killall -0 proxysql && exit 0 || exit 1"
interval 1
weight -10
fall 1
rise 1
}
vrrp_instance sr1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 96
advert_int 1
authentication {
auth_type PASS
auth_pass rEiszbuO
}
virtual_ipaddress {
192.168.42.182/24 dev ens33 label ens33:0
}
#腳本調用
track_script {
chk_down
chk_proxysql
}
notify_master "/etc/keepalived/notify.sh master"
notify_backup "/etc/keepalived/notify.sh backup"
notify_fault "/etc/keepalived/notify.sh fault"
}
(4).notify.sh腳本
#!/bin/bash
#
contact='root@localhost'
notify() {
mailsubject="vrrp:$(hostname) to be $1"
mailbody="$(hostname) to be $1,vrrp transition, $(date)."
echo "$mailbody" | mail -s "$mailsubject" $contact
}
case $1 in
master)
notify master
service proxysql start
;;
backup)
notify backup
service proxysql start
;;
fault)
notify fault
service proxysql stop
;;
*)
echo "Usage: $(basename $0) {master|backup|fault}"
exit 1
;;
esac
(5).因為keepalived是引用漂移ip地址,所以,我們上面配置的proxysql.conf的IP綁定需要修改
mysql_ifaces="192.168.42.182:6032;/tmp/proxysql_admin.sock" interfaces="192.168.42.182:3306;/tmp/proxysql.sock"
記得是node1和node2都要修改哦!
(6).在node1啟動keepalived測試
service proxysql stop
killall proxysql
systemctl start keepalived
ifconfig
ens33:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.42.182 netmask 255.255.255.0 broadcast 0.0.0.0
ether 00:0c:29:c1:a3:0a txqueuelen 1000 (Ethernet)
[root@node2 keepalived]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 127.0.0.1:6032 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 1024 *:3306 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
(7).在node2上也啟動keepalived
service proxysql stop killall proxysql systemctl start keepalived
此時ifconfig是看不到ens33:0的地址的
可以看到proxysql是啟動起來的
[root@node2 keepalived]# ss -tnl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 127.0.0.1:6032 *:* LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 1024 *:3306 *:* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::*
(8).在node1上關閉keepalived
systemctl stop keepalived killall proxysql
(9).在node2上ifconfig查看,192.168.42.182地址是否漂移過去
ifconfig
ens33:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.42.182 netmask 255.255.255.0 broadcast 0.0.0.0
ether 00:0c:29:c1:a3:0a txqueuelen 1000 (Ethernet)
可以看到果然漂移過來了 至此我們的proxysql高可用已經完成了
5.接下來我們做mariadb 主節點的高可用 我們這里的辦法是用MHA,將從節點提升為主節點
MHA（Master HA）是一款開源的 MySQL 的高可用程序，它為 MySQL 主從復制架構提供 了 automating master failover 功能。MHA 在監控到 master 節點故障時，會提升其中擁有最新 數據的 slave 節點成為新的 master 節點，在此期間，MHA 會通過于其它從節點獲取額外信 息來避免一致性方面的問題。MHA 還提供了 master 節點的在線切換功能，即按需切換 master/slave 節點。
MHA 服務有兩種角色，MHA Manager(管理節點)和 MHA Node(數據節點)： MHA Manager：通常單獨部署在一臺獨立機器上管理多個 master/slave 集群，每個 master/slave 集群稱作一個 application； MHA node：運行在每臺 MySQL 服務器上(master/slave/manager)，它通過監控具備解析 和清理 logs 功能的腳本來加快故障轉移。
(1).在node3 [mariadb master]節點上創建秘鑰
ssh-keygen -t rsa -P '' cd .ssh 先復制給自己 ssh-copy-id -i id_rsa.pub root@192.168.42.152 然后復制給node3,node4,node5,node1,node2 ssh-copy-id -i id_rsa.pub root@192.168.42.153 ssh-copy-id -i id_rsa.pub root@192.168.42.154 ssh-copy-id -i id_rsa.pub root@192.168.42.155 ssh-copy-id -i id_rsa.pub root@192.168.42.150 ssh-copy-id -i id_rsa.pub root@192.168.42.151 scp id_rsa id_rsa.pub root@192.168.42.153:~/.ssh/ scp id_rsa id_rsa.pub root@192.168.42.154:~/.ssh/ scp id_rsa id_rsa.pub root@192.168.42.155:~/.ssh/ scp id_rsa id_rsa.pub root@192.168.42.150:~/.ssh/ scp id_rsa id_rsa.pub root@192.168.42.151:~/.ssh/
(2).在node1,node2,node3,node4,node5,node6上下載MHA
cd ~
下載:MHA
wget -c http://192.168.42.26/install_package/down/Sources/mha/mha4mysql-manager-0.56-0.el6.noarch.rpm wget -c http://192.168.42.26/install_package/down/Sources/mha/mha4mysql-node-0.56-0.el6.noarch.rpm
(3).我們使用node1,node2來當管理節點,并做高可用 node1:
yum install mha4mysql* -y
node2同上
(4).我們在node3,node4,node5,node6上安裝mha4mysql-node-0.56-0.el6.noarch.rpm即可
yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
(5).Manger 節點需要為每個監控的 master/slave 集群提供一個專用的配置文件， 而所有的 master/slave 集群也可共享全局配置。全局配置文件默認為/etc/masterha_default.cnf，其為可 選配置。如果僅監控一組 master/slave 集群，也可直接通過 application 的配置來提供各服務 器的默認配置信息。而每個 application 的配置文件路徑為自定義，
例如，本示例中將使用 先創建目錄
mkdir /etc/masterha vim /etc/masterha/app1.cnf
其內容如下所示:
[server default] user=mageedu password=123456 manager_workdir=/data/masterha/app1 manager_log=/data/masterha/app1/manager.log remote_workdir=/data/masterha/app1 ssh_user=root repl_user=repuser repl_password=repass ping_interval=1 [server1] hostname=192.168.42.152 candidate_master=1 [server2] hostname=192.168.42.153 candidate_master=1 [server3] hostname=192.168.42.154 candidate_master=1 [server4] hostname=192.168.42.155 candidate_master=1 (6).檢測各節點間 ssh 互信通信配置是否 OK： masterha_check_ssh --conf=/etc/masterha/app1.cnf 輸出信息最后一行類似如下信息，表示其通過檢測。 [info] All SSH connection tests passed successfully. (7).修改主節點和從節點的配置 初始主節點 master 配置： [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 1 log_bin = log-bin relay-log=relay-bin 所有 slave 節點依賴的配置： [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 11 relay_log=relay-log read_only=ON log_bin = log-bin relay_log_purge=0
(8).檢查管理的 MySQL 復制集群的連接配置參數是否 OK：
masterha_check_repl --conf=/etc/masterha/app1.cnf
輸出信息如下所示，最后一行的“Health is OK”信息表示通過檢測。
Fri Jul 14 15:40:37 2017 - [info] Checking replication health on 192.168.42.153.. Fri Jul 14 15:40:37 2017 - [info] ok. Fri Jul 14 15:40:37 2017 - [info] Checking replication health on 192.168.42.154.. Fri Jul 14 15:40:37 2017 - [info] ok. Fri Jul 14 15:40:37 2017 - [info] Checking replication health on 192.168.42.155.. Fri Jul 14 15:40:37 2017 - [info] ok. Fri Jul 14 15:40:37 2017 - [warning] master_ip_failover_script is not defined. Fri Jul 14 15:40:37 2017 - [warning] shutdown_script is not defined. Fri Jul 14 15:40:37 2017 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
(9).啟動 MHA：
nohup masterha_manager --conf=/etc/masterha/app1.cnf >/data/masterha/app1/manager.log 2>&1 & ps -aux root 75846 1.6 2.1 296664 21652 pts/1 S 15:47 0:00 perl /usr/bin/masterha_manager --conf=/etc/masterha/app1.cnf root 75951 0.0 0.0 0 0 ? S 15:47 0:00 [kworker/u256:0] root 76088 0.0 0.1 151056 1836 pts/1 R+ 15:48 0:00 ps -aux
啟動成功后，可通過如下命令來查看 master 節點的狀態。
[root@node1 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf app1 (pid:75846) is running(0:PING_OK), master:192.168.42.152
上面的信息中“app1 (pid:4978) is running(0:PING_OK)”表示 MHA 服務運行 OK，否則，則 會顯示為類似“app1 is stopped(1:NOT_RUNNING).”。
如果要停止 MHA，需要使用 masterha_stop 命令。
masterha_stop --conf=/etc/masterha/app1.cnf Stopped app1 successfully. [1]+ Exit 1 nohup masterha_manager --conf=/etc/masterha/app1.cnf > /data/masterha/app1/manager.log 2>&1
(10).測試故障轉移
1.在 master 節點關閉 mariadb 服務
killall -9 mysqld mysqld_safe
我們再一次去node1查看
[root@node1 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf app1 is stopped(2:NOT_RUNNING). [1]+ Done nohup masterha_manager --conf=/etc/masterha/app1.cnf > /data/masterha/app1/manager.log 2>&1
2.在 manager 節點查看日志
cat /data/masterha/app1/manager.log 日 志 文 件 中 出 現 的 如 下 信 息 ， 表 示 manager 檢 測 到 192.168.42.152 節點故障，而后自動執行故障轉移，將 192.168.42.153 提升為了主節點。
----- Failover Report ----- app1: MySQL Master failover 192.168.42.152(192.168.42.152:3306) to 192.168.42.153(192.168.42.153:3306) succeeded Master 192.168.42.152(192.168.42.152:3306) is down! Check MHA Manager logs at node1:/data/masterha/app1/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.42.153(192.168.42.153:3306) has all relay logs for recovery. Selected 192.168.42.153(192.168.42.153:3306) as a new master. 192.168.42.153(192.168.42.153:3306): OK: Applying all logs succeeded. 192.168.42.155(192.168.42.155:3306): This host has the latest relay log events. 192.168.42.154(192.168.42.154:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.42.155(192.168.42.155:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.42.153(192.168.42.153:3306) 192.168.42.154(192.168.42.154:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.42.153(192.168.42.153:3306) 192.168.42.153(192.168.42.153:3306): Resetting slave info succeeded. Master failover to 192.168.42.153(192.168.42.153:3306) completed successfully.
注意，故障轉移完成后，manager 將會自動停止，此時使用 masterha_check_status 命令檢測 將會遇到錯誤提示，如下所示。
masterha_check_status --conf=/etc/masterha/app1.cnf app1 is stopped(2:NOT_RUNNING).
- 提供新的從節點以修復復制集群
(1).在新的主節點,備份數據
mysqldump -uroot -x -R -E --triggers --master-data=2 --all-databases > alldb.sql scp alldb.sql root@192.168.42.152:~
(2).node3節點操作
清空所有的數據
rm -rf /var/lib/mysql/*
將原來主節點的配置更改為從配置
vim /etc/my.cnf.d/server.conf [mysqld] innodb_file_per_table=ON skip_name_resolve=ON server_id = 1 relay_log=relay-log read_only=ON log_bin = log-bin relay_log_purge=0
啟動mariadb
systemctl start mariadb
導入數據
mysql < /root/alldb.sql
查看復制點
head -30 /root/alldb.sql 得到復制點 CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000001', MASTER_LOG_POS=328;
登錄mysql,連接進行主從復制
CHANGE MASTER TO MASTER_HOST='192.168.42.153',MASTER_USER='repuser',MASTER_PASSWORD='repass',MASTER_LOG_FILE='log-bin.000001',MASTER_LOG_POS=328; START SLAVE ; SHOW SLAVE STATUS\G;
在現在的主節點刪除一個庫,查看一下子
MariaDB [(none)]> drop database hellowword; Query OK, 0 rows affected (0.00 sec)
node3節點查看:
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
我們可以看到庫被刪了,因此我們的故障轉移到恢復已經成功
我們先在目前的mariadb主節點上flush privileges,然后去manage節點操作
步驟同上面的
(8).檢查管理的 MySQL 復制集群的連接配置參數是否 OK：
(9).啟動 MHA：
一樣
原有 master 節點故障后，需要重新準備好一個新的 MySQL 節點?；趤碜杂?master 節點 的備份恢復數據后，將其配置為新的 master 的從節點即可。注意，新加入的節點如果為新 增節點，其 IP 地址要配置為原來 master 節點的 IP，否則，還需要修改 app1.cnf 中相應的 ip 地址。隨后再次啟動 manager，并再次檢測其狀態。
后續的mha高可用是集成在proxysql的高可用里面的,這個我們以后再續.
原創文章，作者：srayban，如若轉載，請注明出處：http://www.www58058.com/80462
牛逼啊