實(shí)現(xiàn)使用mysqldump渐裸、xtraback工具對MySQL備份，MySQL主從架構(gòu)搭建尖啡，MHA實(shí)現(xiàn)mysql高可用搭建
1橄仆、編寫腳本剩膘，支持讓用戶自主選擇衅斩，使用mysqldump還是xtraback全量備份。
vim mysqlbackup.sh
#!/usr/bin/env bash
mkdir -p /data/{mysqldump,xtrabackup}
read -p "Please choose a mysql backup tool(mysqldump or xtrabackup): " tool
if [ `echo $tool|tr '[A-Z]' '[a-z]'`=='mysqldump'];then
for db in `/usr/local/mysql/bin/mysql -uroot -p1q2w3e4r -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$'`;do /usr/local/mysql/bin/mysqldump -B -F -E -R $db --single-transaction --master-data=1 -- flush-privileges|gzip > /data/mysqldump/$db.sql.gz;done
if [ `echo $?` == 0 ];then
echo "mysqldump backup data successfully"
fi
elif [ `echo $tool|tr '[A-Z]' '[a-z]'`='xtrabackup' ];then
mkdir -p /data/xtrabackup/`date '+%F'`
/usr/bin/xtrabackup --backup --target-dir=/data/xtrabackup/`date '+%F'`
if [ `echo $?` == 0 ];then
echo "xtrabackup backup data successfully"
fi
else
echo "You entered incorrectly, please enter mysqldump or xtrabackup for backup."
fi
2怠褐、配置Mysql主從同步
1）在3臺主機(jī)上分別安裝mariadb畏梆，這里使用的mariadb-10.2.31二進(jìn)制版本，安裝過程不再詳述
192.168.2.239 mariadb-master
192.168.2.240 mariadb-slave1
192.168.2.241 mariadb-slave2
2）mysql主服務(wù)器配置奈懒，編輯mysql配置文件/etc/my.cnf奠涌，在mysqld下添加如下選項(xiàng)
vim /etc/my.cnf
[mysqld]
server_id = 1 #主從唯一標(biāo)識，主從架構(gòu)中的mysql實(shí)例該選項(xiàng)值必須唯一
log-bin _ mysql-bin #開啟二進(jìn)制日志文件
3）登錄mysql master實(shí)例創(chuàng)建主從同步賬號
create user 'repl'@'192.168.2.%' identified by 'repl';
grant replication slave on *.* to 'repl'@'192.168.2.%';
flush privileges;
重啟mariadb服務(wù)
systemctl restart mysqld
登錄數(shù)據(jù)庫查看二進(jìn)制文件及位置
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 375 |
| mysql-bin.000002 | 1968 |
+------------------+-----------+
4）mysql從節(jié)點(diǎn)配置 （多個(gè)從節(jié)點(diǎn)使用相同的方法進(jìn)行配置）
vim /etc/my.cnf 增加如下選項(xiàng)
[mysqld]
server_id = 2
relay_log = relay-log #啟用中繼日志
read_only #mysql的從節(jié)點(diǎn)開啟只讀
重啟mysql服務(wù)
systemctl restart mysqld
使用有復(fù)制權(quán)限的用戶賬號連接至主服務(wù)器磷杏，并啟動復(fù)制線程
CHANGE MASTER TO
MASTER_HOST='192.168.2.239',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1968;
啟動slave的sql和IO線程
MariaDB [(none)]> start slave;
查詢slave的sql和IO線程是否正常
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.239
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1968
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5）在主節(jié)點(diǎn)上導(dǎo)入數(shù)據(jù)驗(yàn)證主從數(shù)據(jù)是否同步
mysql-master上導(dǎo)入hellodb庫
MariaDB [hellodb]> source /root/hellodb_innodb.sql
mysql-salve從節(jié)點(diǎn)檢查數(shù)據(jù)同步正常
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
3溜畅、使用MHA實(shí)現(xiàn)Mysql高可用。
192.168.2.238 mha-manager
192.168.2.239 mysql-master
192.168.2.240 mysql-slave1
192.168.2.241 mysql-slave2
1）mysql主服務(wù)器配置极祸，編輯mysql配置文件/etc/my.cnf慈格，在mysqld下添加如下選項(xiàng)
vim /etc/my.cnf
[mysqld]
server_id = 1 #主從唯一標(biāo)識，主從架構(gòu)中的mysql實(shí)例該選項(xiàng)值必須唯一
log-bin _ mysql-bin #開啟二進(jìn)制日志文件
skip_name_resolve=1
2）登錄mysql master實(shí)例創(chuàng)建主從同步賬號遥金，在mysql主從集群所有節(jié)點(diǎn)執(zhí)行此操作
create user 'repl'@'192.168.2.%' identified by 'repl';
grant replication slave on *.* to 'repl'@'192.168.2.%';
flush privileges;
重啟mariadb服務(wù)
systemctl restart mysqld
登錄數(shù)據(jù)庫查看二進(jìn)制文件及位置
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 375 |
| mysql-bin.000002 | 1968 |
+------------------+-----------+
3）mysql從節(jié)點(diǎn)配置 （多個(gè)從節(jié)點(diǎn)使用相同的方法進(jìn)行配置）
vim /etc/my.cnf 增加如下選項(xiàng)
[mysqld]
server_id = 2
relay_log = relay-log #啟用中繼日志
read_only #mysql的從節(jié)點(diǎn)開啟只讀
relay_log_purge=0
skip_name_resolve=1
重啟mysql服務(wù)
systemctl restart mysqld
使用有復(fù)制權(quán)限的用戶賬號連接至主服務(wù)器浴捆，并啟動復(fù)制線程
CHANGE MASTER TO
MASTER_HOST='192.168.2.239',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1968;
啟動slave的sql和IO線程
MariaDB [(none)]> start slave;
查詢slave的sql和IO線程是否正常
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.239
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1968
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4）在主節(jié)點(diǎn)上導(dǎo)入數(shù)據(jù)驗(yàn)證主從數(shù)據(jù)是否同步
mysql-master上導(dǎo)入hellodb庫
MariaDB [hellodb]> source /root/hellodb_innodb.sql
mysql-salve從節(jié)點(diǎn)檢查數(shù)據(jù)同步正常
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5）在MHA-Manager管理幾點(diǎn)上安裝依賴包和mha4mysql-manager mha4mysql-node包，在mha集群的其他節(jié)點(diǎn)上安裝mha4mysql-node包稿械。
yum install -y perl-Mail-Sender perl-Email-Date-Format perl-MIME-Types perl-MIME-Lite perl-Parallel-ForkManager perl-Mail-Sendmail perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-YAML-Tiny perl-PAR-Dist perl-Module-ScanDeps perl-Module-CoreList perl-Module-Build perl-CPAN perl-YAML perl-CPANPLUS perl-File-Remove perl-Module-Install
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
軟件包下載地址：
https://github.com/yoshinorim/mha4mysql-manager
https://github.com/yoshinorim/mha4mysql-node
6）在集群所有的節(jié)點(diǎn)上相互配置ssh基于key驗(yàn)證选泻，這里配置集群所有的主機(jī)使用同一個(gè)公鑰和私鑰
ssh-keygen
ssh-copy-id 127.0.0.1
scp -r /root/.ssh root@remoteIp:/root/
7）在管理節(jié)點(diǎn)建立配置文件
創(chuàng)建存放配置文件的目錄 mkdir -p /etc/mastermha
vim /etc/mastermha/app1.conf
[server default]
user=mhauser
password=mhauser
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1
[server1]
hostname=192.168.2.239
candidate_master=1
[server2]
hostname=192.168.2.240
candidate_master=1
[server3]
hostname=192.168.2.241
8）登錄mysql master創(chuàng)建mha管理賬號
create user 'mhauser'@'192.168.2.%' identified by 'mhauser';
grant all on *.* to 'mhauser'@'192.168.2.%';
9）使用的是二進(jìn)制方式安裝mysql，需要設(shè)置mysql命令的軟鏈接美莫，否則執(zhí)行檢查會提示命令找不到錯(cuò)誤页眯。
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
10）Mha驗(yàn)證和啟動
驗(yàn)證節(jié)點(diǎn)之間ssh key相互登錄
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
顯示如下結(jié)果為成功
Sun Feb 23 16:17:15 2020 - [info] All SSH connection tests passed successfully.
mha主從復(fù)制測試
masterha_check_repl --conf=/etc/mastermha/app1.cnf
Sat Feb 29 15:13:16 2020 - [info] Checking replication health on 192.168.2.240..
Sat Feb 29 15:13:16 2020 - [info] ok.
Sat Feb 29 15:13:16 2020 - [info] Checking replication health on 192.168.2.241..
Sat Feb 29 15:13:16 2020 - [info] ok.
Sat Feb 29 15:13:16 2020 - [warning] master_ip_failover_script is not defined.
Sat Feb 29 15:13:16 2020 - [warning] shutdown_script is not defined.
Sat Feb 29 15:13:16 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
啟動mha，使用后臺運(yùn)行
nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null &
ps -aux|grep mha
root 24329 2.6 0.4 207436 21948 pts/1 S 15:17 0:00 perl /usr/bin/masterha_manager --conf=/etc/mastermha/app1.cnf
11）模擬2.239主服務(wù)器故障厢呵，集群自動提升其他節(jié)點(diǎn)為主節(jié)點(diǎn)窝撵，停止2.239mysql服務(wù)，查看mha日志自動將配置文件中定義的候選節(jié)點(diǎn)240替身為mysql master述吸。
Started automated(non-interactive) failover.
The latest slave 192.168.2.240(192.168.2.240:3306) has all relay logs for recovery.
Selected 192.168.2.240(192.168.2.240:3306) as a new master.
192.168.2.240(192.168.2.240:3306): OK: Applying all logs succeeded.
192.168.2.241(192.168.2.241:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.2.241(192.168.2.241:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.2.240(192.168.2.240:3306)
192.168.2.240(192.168.2.240:3306): Resetting slave info succeeded.
Master failover to 192.168.2.240(192.168.2.240:3306) completed successfully.
