编写脚本，支持让用户自主选择，使用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主从同步
实验环境：
主节点（node1）：centos7.6 yum安装mariadb ip：172.16.2.131
从节点（node2）：centos7.6 yum安装mariadb ip：172.16.2.132
主节点配置
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'; //创建同步账号
MariaDB [(none)]> show master logs; //查看二进制日志文件及pos位置
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
从节点配置
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) //根据主节点信息添加
MariaDB [(none)]> start slave; //启动slave功能
Query OK, 0 rows affected (0.00 sec)
验证
MariaDB [(none)]> show slave status\G //查看从节点slave 状态
*************************** 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; //在主节点创建一个数据库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases; //从节点也可以看到新建的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
使用MHA实现Mysql高可用
实验环境：
主节点 （node1）： centos7.6 安装mariadb mha4mysql-node ip：172.16.2.131
从节点1（node2）： centos7.6 安装mariadb mha4mysql-node ip：172.16.2.132
从节点2（node3）： centos7.6 安装mariadb mha4mysql-node ip：172.16.2.132
管理节点（node4）： centos7.6 安装mha4mysql-node mha4mysql-manager ip：172.16.2.132
- 在所有节点实现相互之间ssh key验证
1.实现主从复制
主节点配置
[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' //创建同步账号
MariaDB [(none)]> grant all on *.* to mhauser@'172.16.2.%' identified by '123456'; //创建mha管理账号
[root@node2 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y //安装mha4mysql-node
从节点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中创建的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节点
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.模拟故障，查看效果
将主节点关机，模拟故障。
查看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) //主节点故障
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) //从节点1变为新的主服务器
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! //主节点故障
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.
切换后查看新主服务器状态
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状态已清除
Empty set (0.00 sec)
查看从服务器2状态
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
