編寫腳本，支持讓用戶自主選擇，使用mysqldump還是xtraback全量備份。
vim backup.sh
backupdir=/data/backup
yum install -y xtrabackup &> /dev/null
read -p "please choose the backup tool? mysqldump or xtrabackup: " tool
case $tool in
mysqldump)
mysqldump -A -F --single-transaction --master-data=2 > $backupdir/dump`date +%F`.sql
echo "The databases have been backuped to $backupdir by mysqldump! "
;;
xtrabackup)
xtrabackup --backup --target-dir=$backupdir/xtrabackup`date +%F` &> /dev/null
echo "The databases have been backuped to $backupdir by xtrabackup! "
;;
*)
echo "something wrong! please type mysqldump or xtrabackup!"
exit 10
;;
esac
Mysql主從同步
實驗環(huán)境：
主節(jié)點（node1）：centos7.6 yum安裝mariadb ip：172.16.2.131
從節(jié)點（node2）：centos7.6 yum安裝mariadb ip：172.16.2.132
主節(jié)點配置
vim /etc/my.cnf //編輯配置文件
[mysqld]
log-bin //啟用二進制日志
server-id=1 //設置server-id，此ip要保證唯一性
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
systemctl restart mariadb //重啟服務
MariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.2.%' identified by '123456'; //創(chuàng)建同步賬號
MariaDB [(none)]> show master logs; //查看二進制日志文件及pos位置
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
從節(jié)點配置
vim /etc/my.cnf //編輯配置文件
[mysqld]
server-id=2 //設置server-id，此ip要保證唯一性
read-only //開啟只讀
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
systemctl restart mariadb //重啟服務
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '172.16.2.131', MASTER_USER = 'repluser', MASTER_PASSWORD = '123456', MASTER_LOG_FILE= 'mariadb-bin.000001', MASTER_LOG_POS = 245;
Query OK, 0 rows affected (0.11 sec) //根據(jù)主節(jié)點信息添加
MariaDB [(none)]> start slave; //啟動slave功能
Query OK, 0 rows affected (0.00 sec)
驗證
MariaDB [(none)]> show slave status\G //查看從節(jié)點slave 狀態(tài)
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 320
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 606
Relay_Master_Log_File: mariadb-bin.000002
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: 320
Relay_Log_Space: 1188
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)
MariaDB [mysql]> create database db1; //在主節(jié)點創(chuàng)建一個數(shù)據(jù)庫
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases; //從節(jié)點也可以看到新建的數(shù)據(jù)庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
使用MHA實現(xiàn)Mysql高可用
實驗環(huán)境：
主節(jié)點 （node1）： centos7.6 安裝mariadb mha4mysql-node ip：172.16.2.131
從節(jié)點1（node2）： centos7.6 安裝mariadb mha4mysql-node ip：172.16.2.132
從節(jié)點2（node3）： centos7.6 安裝mariadb mha4mysql-node ip：172.16.2.132
管理節(jié)點（node4）： centos7.6 安裝mha4mysql-node mha4mysql-manager ip：172.16.2.132
- 在所有節(jié)點實現(xiàn)相互之間ssh key驗證
1.實現(xiàn)主從復制
主節(jié)點配置
[root@node1 yum.repos.d]# vim /etc/my.cnf //編輯配置文件
[mysqld]
log-bin //啟用二進制日志
server-id=1 //設置server-id，此ip要保證唯一性
skip_name_resolve //跳過dns解析
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[root@node1 yum.repos.d]# systemctl restart mariadb //重啟服務
MariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.2.%' identified by '123456' //創(chuàng)建同步賬號
MariaDB [(none)]> grant all on *.* to mhauser@'172.16.2.%' identified by '123456'; //創(chuàng)建mha管理賬號
[root@node2 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y //安裝mha4mysql-node
從節(jié)點1配置：
[root@node2 yum.repos.d]# vim /etc/my.cnf//編輯配置文件
[mysqld]
server-id=2 //設置server-id，此ip要保證唯一性
read-only //設置只讀
log-bin //啟用二進制日志
relay_log_purge=0 //不自動清理中繼日志
skip_name_resolve //跳過dns解析
[root@node2 yum.repos.d]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '172.16.2.131', MASTER_USER = 'repluser', MASTER_PASSWORD = '123456', MASTER_LOG_FILE= 'mariadb-bin.000001', MASTER_LOG_POS = 245;
[root@node2 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y //安裝mha4mysql-node
2.管理端配置
[root@node4 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm //安裝mha4mysql-node mha4mysql-manager
[root@node4 ~]# vim /etc/mastermha/app.cnf //編輯mha配置文件
[server default]
user=mhauser //mysql中創(chuàng)建的mha管理賬號
password=123456
manager_workdir=/data/mastermha/app/
manager_log=/data/mastermha/app/manager.log //日志位置
remote_workdir=/data/mastermha/app/
ssh_user=root //管理各個主機所有賬號
repl_user=repluser //mysql同步賬號
repl_password=123456
ping_interval=1
[server1] //加入各個MYSQL節(jié)點
hostname=172.16.2.131
candidate_master=1 //master候選
[server2]
hostname=172.16.2.132
candidate_master=1
[server3]
hostname=172.16.2.137
[root@node4 ~]# masterha_manager --conf=/etc/mastermha/app.cnf //啟動程序
3.模擬故障，查看效果
將主節(jié)點關機，模擬故障。
查看mha日志
[root@node4 ~]# cat /data/mastermha/app/manager.log //查看日志
Thu Apr 9 21:08:26 2020 - [info] Dead Servers:
Thu Apr 9 21:08:26 2020 - [info] 172.16.2.131(172.16.2.131:3306) //主節(jié)點故障
Thu Apr 9 21:08:26 2020 - [info] Checking master reachability via MySQL(double check)...
Thu Apr 9 21:08:27 2020 - [info] ok.
Thu Apr 9 21:08:27 2020 - [info] Alive Servers:
Thu Apr 9 21:08:27 2020 - [info] 172.16.2.132(172.16.2.132:3306)
Thu Apr 9 21:08:27 2020 - [info] 172.16.2.137(172.16.2.137:3306)
Thu Apr 9 21:08:27 2020 - [info] Alive Slaves:
Thu Apr 9 21:08:27 2020 - [info] 172.16.2.132(172.16.2.132:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:27 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:27 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Apr 9 21:08:27 2020 - [info] 172.16.2.137(172.16.2.137:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:27 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:27 2020 - [info] Starting Non-GTID based failover.
Thu Apr 9 21:08:27 2020 - [info]
Thu Apr 9 21:08:27 2020 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Apr 9 21:08:27 2020 - [info]
Thu Apr 9 21:08:27 2020 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu Apr 9 21:08:27 2020 - [info]
Thu Apr 9 21:08:27 2020 - [info] Forcing shutdown so that applications never connect to the current master..
Thu Apr 9 21:08:27 2020 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Thu Apr 9 21:08:27 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu Apr 9 21:08:28 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 3: Master Recovery Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] The latest binary log file/position on all slaves is mariadb-bin.000003:521
Thu Apr 9 21:08:28 2020 - [info] Latest slaves (Slaves that received relay log files to the latest):
Thu Apr 9 21:08:28 2020 - [info] 172.16.2.132(172.16.2.132:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:28 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:28 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Apr 9 21:08:28 2020 - [info] 172.16.2.137(172.16.2.137:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:28 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:28 2020 - [info] The oldest binary log file/position on all slaves is mariadb-bin.000003:521
Thu Apr 9 21:08:28 2020 - [info] Oldest slaves:
Thu Apr 9 21:08:28 2020 - [info] 172.16.2.132(172.16.2.132:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:28 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:28 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Apr 9 21:08:28 2020 - [info] 172.16.2.137(172.16.2.137:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:28 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [warning] Dead Master is not SSH reachable. Could not save it's binlogs. Transactions that were not sent to the latest slave (Read_Master_Log_Pos to the tail of the dead master's binlog) were lost.
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 3.3: Determining New Master Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Thu Apr 9 21:08:28 2020 - [info] All slaves received relay logs to the same position. No need to resync each other.
Thu Apr 9 21:08:28 2020 - [info] Searching new master from slaves..
Thu Apr 9 21:08:28 2020 - [info] Candidate masters from the configuration file:
Thu Apr 9 21:08:28 2020 - [info] 172.16.2.132(172.16.2.132:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled
Thu Apr 9 21:08:28 2020 - [info] Replicating from 172.16.2.131(172.16.2.131:3306)
Thu Apr 9 21:08:28 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Apr 9 21:08:28 2020 - [info] Non-candidate masters:
Thu Apr 9 21:08:28 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Thu Apr 9 21:08:28 2020 - [info] New master is 172.16.2.132(172.16.2.132:3306) //從節(jié)點1變?yōu)樾碌闹鞣掌?Thu Apr 9 21:08:28 2020 - [info] Starting master failover..
Thu Apr 9 21:08:28 2020 - [info]
From:
172.16.2.131(172.16.2.131:3306) (current master)
+--172.16.2.132(172.16.2.132:3306)
+--172.16.2.137(172.16.2.137:3306)
To:
172.16.2.132(172.16.2.132:3306) (new master)
+--172.16.2.137(172.16.2.137:3306)
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 3.4: Master Log Apply Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Thu Apr 9 21:08:28 2020 - [info] Starting recovery on 172.16.2.132(172.16.2.132:3306)..
Thu Apr 9 21:08:28 2020 - [info] This server has all relay logs. Waiting all logs to be applied..
Thu Apr 9 21:08:28 2020 - [info] done.
Thu Apr 9 21:08:28 2020 - [info] All relay logs were successfully applied.
Thu Apr 9 21:08:28 2020 - [info] Getting new master's binlog name and position..
Thu Apr 9 21:08:28 2020 - [info] mariadb-bin.000002:477
Thu Apr 9 21:08:28 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.2.132', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=477, MASTER_USER='repluser', MASTER_PASSWORD='xxx';
Thu Apr 9 21:08:28 2020 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Thu Apr 9 21:08:28 2020 - [info] Setting read_only=0 on 172.16.2.132(172.16.2.132:3306)..
Thu Apr 9 21:08:28 2020 - [info] ok.
Thu Apr 9 21:08:28 2020 - [info] ** Finished master recovery successfully.
Thu Apr 9 21:08:28 2020 - [info] * Phase 3: Master Recovery Phase completed.
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 4: Slaves Recovery Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Thu Apr 9 21:08:28 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] -- Slave diff file generation on host 172.16.2.137(172.16.2.137:3306) started, pid: 9240. Check tmp log /data/mastermha/app//172.16.2.137_3306_20200409210825.log if it takes time..
Thu Apr 9 21:08:29 2020 - [info]
Thu Apr 9 21:08:29 2020 - [info] Log messages from 172.16.2.137 ...
Thu Apr 9 21:08:29 2020 - [info]
Thu Apr 9 21:08:28 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Apr 9 21:08:29 2020 - [info] End of log messages from 172.16.2.137.
Thu Apr 9 21:08:29 2020 - [info] -- 172.16.2.137(172.16.2.137:3306) has the latest relay log events.
Thu Apr 9 21:08:29 2020 - [info] Generating relay diff files from the latest slave succeeded.
Thu Apr 9 21:08:29 2020 - [info]
Thu Apr 9 21:08:29 2020 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Thu Apr 9 21:08:29 2020 - [info]
Thu Apr 9 21:08:29 2020 - [info] -- Slave recovery on host 172.16.2.137(172.16.2.137:3306) started, pid: 9242. Check tmp log /data/mastermha/app//172.16.2.137_3306_20200409210825.log if it takes time..
Thu Apr 9 21:08:30 2020 - [info]
Thu Apr 9 21:08:30 2020 - [info] Log messages from 172.16.2.137 ...
Thu Apr 9 21:08:30 2020 - [info]
Thu Apr 9 21:08:29 2020 - [info] Starting recovery on 172.16.2.137(172.16.2.137:3306)..
Thu Apr 9 21:08:29 2020 - [info] This server has all relay logs. Waiting all logs to be applied..
Thu Apr 9 21:08:29 2020 - [info] done.
Thu Apr 9 21:08:29 2020 - [info] All relay logs were successfully applied.
Thu Apr 9 21:08:29 2020 - [info] Resetting slave 172.16.2.137(172.16.2.137:3306) and starting replication from the new master 172.16.2.132(172.16.2.132:3306)..
Thu Apr 9 21:08:29 2020 - [info] Executed CHANGE MASTER.
Thu Apr 9 21:08:29 2020 - [info] Slave started.
Thu Apr 9 21:08:30 2020 - [info] End of log messages from 172.16.2.137.
Thu Apr 9 21:08:30 2020 - [info] -- Slave recovery on host 172.16.2.137(172.16.2.137:3306) succeeded.
Thu Apr 9 21:08:30 2020 - [info] All new slave servers recovered successfully.
Thu Apr 9 21:08:30 2020 - [info]
Thu Apr 9 21:08:30 2020 - [info] * Phase 5: New master cleanup phase..
Thu Apr 9 21:08:30 2020 - [info]
Thu Apr 9 21:08:30 2020 - [info] Resetting slave info on the new master..
Thu Apr 9 21:08:30 2020 - [info] 172.16.2.132: Resetting slave info succeeded.
Thu Apr 9 21:08:30 2020 - [info] Master failover to 172.16.2.132(172.16.2.132:3306) completed successfully.
Thu Apr 9 21:08:30 2020 - [info]
----- Failover Report -----
app: MySQL Master failover 172.16.2.131(172.16.2.131:3306) to 172.16.2.132(172.16.2.132:3306) succeeded
//切換成功
Master 172.16.2.131(172.16.2.131:3306) is down! //主節(jié)點故障
Check MHA Manager logs at node4:/data/mastermha/app/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 172.16.2.132(172.16.2.132:3306) has all relay logs for recovery.
Selected 172.16.2.132(172.16.2.132:3306) as a new master.
172.16.2.132(172.16.2.132:3306): OK: Applying all logs succeeded.
172.16.2.137(172.16.2.137:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
172.16.2.137(172.16.2.137:3306): OK: Applying all logs succeeded. Slave started, replicating from 172.16.2.132(172.16.2.132:3306)
172.16.2.132(172.16.2.132:3306): Resetting slave info succeeded.
Master failover to 172.16.2.132(172.16.2.132:3306) completed successfully.
切換后查看新主服務器狀態(tài)
MariaDB [(none)]> show variables like "read_only" //查看read_only已關閉
-> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show slave status\G //slave狀態(tài)已清除
Empty set (0.00 sec)
查看從服務器2狀態(tài)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.132 //主服務器已變化
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 477
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
