文章目录
1、编写脚本,支持让用户自主选择,使用mysqldump还是xtraback全量备份
#!/bin/bash
date=`date '+%F'`
sock="-S /data/mysql/mysql3306/socket/mysql.sock"
dbdir="/backup/db"
read -p "pleas input you db username :" user
read -p "pleas input you db password :" passwd
#mysqldump方法备份
sqldump(){
while :;do
read -p "Are you bakup all databases: " yn
if [[ "$yn" =~ ^[yY]([Ee][Ss])?$ ]];then
#备份所有库
mysqldump $sock -u${user} -p${passwd} -A -F --single-transaction --master-data=2|gzip > ${dbdir}/all-${date}.sql.gz
echo "database bakup in ${dbdir}/all-${date}.sql.gz"
exit 0
elif [[ "$yn" =~ ^[Nn][Oo]?$ ]];then
mysql $sock -u${user} -p${passwd} -e 'show databases'
read -p "chose you bakup databases :" db
#备份部分库
mysqldump $sock -u${user} -p${passwd} -B ${db}|gzip > ${dbdir}/part-${date}.sql.gz
echo "database bakup in ${dbdir}/part-${date}.sql.gz"
exit 0
else
echo 'please input y or n'
continue 1
fi
done
}
#xtrabackup方法备份
xtrabak(){
read -p "input you backup dir(default /backup/db): " xdbdir
[ -z $xdbdir ] && xdbdir=$dbdir
[ -d $xdbdir ] || mkdir -p $dbdir
mariabackup $sock --backup --target-dir=${xdbdir} --user=$user
}
main(){
read -p "please chose you backup 1:mysqldump,2:xtrabackup: " num
if [ "$num" -eq 1 ];then
sqldump
elif [ $num -eq 2 ];then
xtrabak
else
echo "please input 1 or 2"
fi
}
main
2、配置Mysql主从同步
单台主机配置多实例,参照:https://blog.csdn.net/apple2417/article/details/104821563
主:开启binlog日志,重启mysql
[mysqld]
server_id=1
log_bin=/data/mysql/mysql3306/log/mysql-bin
添加复制账号
mysql> grant replication slave on *.* TO 'repluser'@'192.168.214.%' identified by
'123456';
查看master状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从:开启中继日志,重启mysql
[mysqld]
server_id=2
#read_only=ON
relay_log=/data/mysql/mysql3307/log/relay-log
进入mysql配置连接主服务器,启动线程
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.214.13',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.214.13
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000006
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: 154
Relay_Log_Space: 521
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
Master_UUID: f9cd0615-6345-11ea-a72e-000c2994e329
Master_Info_File: /data/mysql/mysql3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
3、使用MHA实现Mysql高可用
准备四台主机:
MHA管理机:192.168.214.20
master:192.168.214.21
slave1:192.168.214.22
slave2:192.168.214.23
包:mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
1.配置master主节点
[root@localhost ~]# vi /etc/my.cnf
#添加
[mysqld]
server_id=21
log_bin
skip_name_resolve
#重启mariadb
[root@localhost ~]# systemctl restart mariadb
进入mariadb添加账户
[root@localhost ~]# mysql
#查看主节点状态从节点复制从这个时候开始
MariaDB [(none)]> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| localhost-bin.000001 | 332 |
+----------------------+-----------+
1 row in set (0.00 sec)
#添加复制账户和管理账户
MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.214.%' identified by '123456';
#添加管理账户
MariaDB [(none)]> grant all on *.* to mha@'192.168.214.%' identified by '123456';
安装mha4mysql-node
[root@localhost ~]# yum -y installl mha4mysql-node-0.56-0.el6.noarch.rpm
2.配置slave1从节点
[root@localhost ~]# vi /etc/my.cnf
#添加
[mysqld]
server_id=22
log-bin
read_only
skip_name_resolve
relay_log_purge=0
#重启mariadb
[root@localhost ~]# systemctl restart mariadb
进入mariadb配置从节点
[root@localhost ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.214.21',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='localhost-bin.000001',
MASTER_LOG_POS=332;
MariaDB [(none)]> start slave;
安装mha4mysql-node
[root@localhost ~]# yum -y installl mha4mysql-node-0.56-0.el6.noarch.rpm
slave2配置与slave1一样只需要把server_id=22改成server_id=23
3.配置MHA管理节点
添加epel源
[root@localhost ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@localhost ~]# yum clean all
[root@localhost ~]# yum makecache
安装包
[root@localhost ~]# ll
-rw-r--r-- 1 root root 87119 Dec 18 02:31 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r-- 1 root root 36326 Dec 18 02:31 mha4mysql-node-0.56-0.el6.noarch.rpm
[root@localhost ~]# yum -y install mha4mysql-*.rpm
配置文件
[root@localhost ~]# vi /etc/mha/mha.cnf
#添加
[server default]
user=mha
password=123456
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=123456
ping_interval=1
[server1]
hostname=192.168.214.21
candidate_master=1
[server2]
hostname=192.168.214.22
candidate_master=1
[server3]
hostname=192.168.214.23
添加信任关系
[root@localhost ~]# ssh-keygen -t rsa #后面直接enter
[root@localhost ~]# ssh-copy-id 127.0.0.1
[root@localhost ~]# scp -r ~/.ssh 192.168.214.21:/root/
[root@localhost ~]# scp -r ~/.ssh 192.168.214.22:/root/
[root@localhost ~]# scp -r ~/.ssh 192.168.214.23:/root/
验证
[root@localhost ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf
[root@localhost ~]# masterha_check_repl --conf=/etc/mha/mha.cnf
启动
[root@localhost ~]# masterha_manager --conf=/etc/mha/mha.cnf