1、编写脚本,支持让用户自主选择,使用mysqldump还是xtraback全量备份。
[root@C76 ~]# yum install mariadb-server -y
[root@C76 ~]# systemctl restart mariadb
[root@C76 ~]# cat mariadb-backpu.sh
#!/bin/bash
BACKUP_DIR=/data/backup
USER=root
PASSWD=redhat
[ -d $BACKUP_DIR ] || mkdir $BACKUP_DIR
while true;do
cat <<EOF
Please choice you back up tool:
1.mysqldump
2.xtrabackup
3.quit
EOF
read -p "Your chose: " chose
case "$chose" in
1)
mysqldump -u$USER -p$PASSWD -A -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP_DIR/fullmydumpbak_$(date +%F_%T).sql
;;
2)
innobackupex --user=$USER --password=$PASSWD $BACKUP_DIR
;;
3)
echo "Bye!" && exit 2
;;
*)
echo "Chose Error !" && exit 3
;;
esac
done
2、配置Mysql主从同步
1、主从复制
vim /etc/my.cnf
主:server-id=1
log-bin=/data
systemctl restart mariadb
grant replication slave on *.* to repluser@'192.168.44.%' identified by 'centos'
2、备份
mysqldump -A --single-transaction --master-data=1 -F >/data/all.sql
3、建立复制
vim /data/all.sql
mysql>CHANGE MASTER TO MASTER_HOST='192.168.44.128',
MSTER_USER='repluser',
MSTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=245#;
MASTER_PORT=3306
从节点配置
server-id=2
read-only
datadir=/var/lib/mysql
systemctl start mariadb
mysql < /data/all.sql
mariadb>show slave status\G
show variables like 'server_id';
show variables variables like 'read_only
source /data/all.sql #执行
start slave;#启动复制
3、使用MHA实现Mysql高可用
#至少需要三台服务器,一台主,两台从服务器
主同理配置
1、修改主配置文件
vim /etc/my.cnf
server-id=1
log-bin
auto_increment_offset=1
auto_increment_increment=2
2、重启数据库服务
systemctl restart mariadb
3、建立复制账号
grant replication slave on *.* to repluser@'192.168.44.%' identified by 'centos'
4、备份
mysqldump -A --single-transaction --master-data=1 -F >/data/all.sql
scp /data/all.sql 192.168.44.130:/data
从节点都从主服务器中复制
1、修改配置文件
vim /etc/my.cng
server-id=2
2、重启服务
systemctl restart mariadb
3、mysql>CHANGE MASTER TO MASTER_HOST='192.168.44.128',
MSTER_USER='repluser',
MSTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245#;
MASTER_PORT=3306
mysql >start slave;
##同理配置从三
#装插件
mysq>show plugins;#查看现有的插件
#在主服务器上装
mysql>INSTALL PLLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1 #启用插件
mysql>SHOW GLOBAL VARIABLES LIKE '% semi%';
OFF默认是不启用的
查看状态信息
#在所有的从节点安装插件
show plugins;
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#启用
mysql>SET GLOBAL rpl_semi_sync_slave_enabled=1
#从节点配置完一定要把线程重新启用一下
stop slave;
start slave;