1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。

#!/bin/bash

PS3="Please input a choice:"
select menu in mysqldump xtrabackup; do
case $REPLY in
1)
mkdir /backup/dump/ &> /dev/null
mysqldump -F -A --single-transaction --master-data=1 > /backup/dump/all.sql
break
;;
2)
mkdir /backup/test/ &> /dev/null
xtrabackup --backup --target-dir=/backup/test/ &> /dev/null
break
;;
*)
echo "please input again!"
esac
done

2、配置Mysql主从同步

环境：192.168.43.127 master
192.168.43.137 slave

1、master配置文件：
[ backup]#cat /etc/my.cnf
[mysqld]
**server-id=1**
max_connections=2000
binlog_format=ROW
**log-bin=/data/mysql/logbin/mysql-bin**
transaction-isolation=SERIALIZABLE
datadir=/data/mysql/data
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/log/mariadb/mariadb.log
pid-file=/data/mysql/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

2、在master上添加复制用户：

MariaDB [(none)]> grant replication slave on . to ‘repluser‘@‘192.168.43.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3、备份master数据库，并拷贝到slave上：

[ mysql]#mysqldump -F -A --single-transaction --master-data=1 > /backup/all.sql
[ backup]#scp -r /backup/all.sql 192.168.43.137:/data/

4、slave节点配置：

[mysqld]
socket = /data/mysql/mysql.sock
datadir = /data/mysql
server-id=2
read_only

（1）slave导入数据库之前数据：
[ mysql]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.25-MariaDB Source distribution

Copyright (c) 2000, 2018, 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 |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

（2）导入数据库，与主服务器保持数据一致：

[ mysql]#mysql < /data/all.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

（3）配置主从同步（slave上操作）

查看数据库备份文件主服务二进制日志位置：

设置之前，slave状态是空的
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)

MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST=‘192.168.43.127‘,
-> MASTER_USER=‘repluser‘,
-> MASTER_PASSWORD=‘123456‘,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘mysql-bin.000005‘,
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

设置之后可以看到slave状态如下：

MariaDB [(none)]> show slave status\G
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.43.127
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 245
Relay_Log_File: ka1-relay-bin.000002
Relay_Log_Pos: 544
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
………

5、主从同步测试：

（1） master删除数据：

MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [hellodb]> select * from teachers
-> ;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shiitai | 77 | F |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> delete from teachers where Age=77;
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)

（2）从服务器实时同步数据：

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
+-----+---------------+-----+--------+
2 rows in set (0.00 sec)

3、使用MHA实现Mysql高可用。

在主从复制基础之上，实现MHA高可用；
节点要开启二进制日志及中继日志；
从节点为 read_only；
关闭从节点的 relay_log_purge 中继日志自动清理功能
环境准备：
master 192.168.43.127
slave 192.168.43.137
slave/manager 192.168.43.147

1、master的配置文件：
[mysqld]
server-id=1
skip_name_resolve=1
relay-log=relay-log
log-bin=bin-log
innodb-file-per-table=ON
datadir=/data/mysql/data
slave1配置文件：
[mysqld]
datadir=/data/mysql/data
server-id=2
skip_name_resolve=1
read_only
relay_log_purge=0
relay-log=relay-log
log-bin=bin-log
innodb-file-per-table=ON
slave2配置文件：
[mysqld]
datadir=/data/mysql/data
server-id=3
skip_name_resolve=1
read_only
relay_log_purge=0
relay-log=relay-log
log-bin=bin-log
innodb-file-per-table=ON

2、各个节点上安装mha4mysql-node，manager节点上安装mha4mysql-manager

[]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm
[]#yum install mha4mysql-manager-0.56-0.el6.noarch.rpm

3、master上创建MHA管理用户核复制用户，在主从同步正常情况下会同步给slave和slave2:

MariaDB [(none)]> grant replication slave on . to ‘192.168.43.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on . to ‘192.168.43.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、各节点之间免密登录：

（1）各节点上生成密钥：

（2）后用命令将公钥拷贝到其他节点
[[email protected]]#ssh-copy-id 192.168.43.147
[[email protected]]#ssh-copy-id 192.168.43.137

[[email protected]]#ssh-copy-id 192.168.43.127
[[email protected]]#ssh-copy-id 192.168.43.137

[[email protected]]#ssh-copy-id 192.168.43.127
[[email protected]]#ssh-copy-id 192.168.43.147

5、在manager节点生成mha配置文件：

[[email protected]]#cat /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=123456
manager_workdir=/data/mastermha/mysql/
manager_log=/data/mastermha/mysql/manager.log
remote_workdir=/data/mastermha/mysql/
ssh_user=root
repl_user=repluser
repl_password=123456
ping_interval=1
[server1]
hostname=192.168.43.127
candidate_master=1
master_binlog_dir=/data/mysql/data/
[server2]
hostname=192.168.43.137
candidate_master=1
master_binlog_dir=/data/mysql/data/
[server3]
hostname=192.168.43.147
master_binlog_dir=/data/mysql/data/

注释：

master_binlog_dir #mysql的binlog日志目录没有在默认的/var/log/mysql下，需要配置此项，写明具体路径
candidate_master=1 #是否 可以成为master，1是

6、检查

（1） 检查ssh登录是否正常：

[[email protected]]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf

（2）检查节点之间复制是否正常：
[[email protected]]# masterha_check_repl --conf=/etc/mastermha/app1.cnf

7、启动MHA:

[[email protected]]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &

8、检查MHA状态:

[[email protected]]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:75633) is running(0:PING_OK), master:192.168.43.127

9、故障转移测试：

（1）在master上停止mariadb服务
[[email protected]]#systemctl stop mariadb

（2）在manager上查看日志：
[[email protected]]#tail -f /data/mastermha/mysql/manager.log

（3）在备用master上状态，已经由slave变成了master：
MariaDB [hellodb]> show slave status\G

（4）在slave2上查看slave状态，此时的master已经由192.168.43.127变成了192.168.43.137
MariaDB [(none)]> show slave status\G