Linux 学习（十六）

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

#!/bin/bash

BACKUP_DIR=/data/backup
USER=root
PASSWD=123456

[ -d $BACKUP_DIR ] || mkdir $BACKUP_DIR

while true;do

cat <<EOF
Please input a number to choice you backup:
  1.mysqldump
  2.xtrabackup
  3.quit
EOF

read -p "Your choose: " choose

case "$choose" 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/fullbak_$(date +%F_%T).sql
    ;;
   2)
    innobackupex --user=$USER --password=$PASSWD $BACKUP_DIR
    ;;
   3)
    echo "Bye!" && exit 2
    ;;
   *)
    echo "Choose Error !" && exit 3
    ;;
esac

done

2、配置Mysql主从同步

(1)、两台主机都安装好mariadb-server数据库

yum install -y mariadb-server

(2)、配置主服务器

vim  /etc/my.cnf
      server_id=1 
      log-bin=/data/logbin/mysql

systemctl restart mariadb
mysql

grant replication slave on *.* to repluser@'192.168.5.%' identified by '123456' ;

show master logs 

(3)、配置从服务器

vim  /etc/my.cnf
      server_id=2
      read-only

show slave status\G ;

CHANGE MASTER TO 
      MASTER_HOST='192.168.5.12',
      MASTER_USER='repluser',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='mysql.000002',
      MASTER_LOG_POS=245;

show slave status\G

start slave ;
show slave status\G 

show processlist ;

3、使用MHA实现Mysql高可用

主机：4台，一台MHA Manager (192.168.5.12)，三台主从复制架构(一主多从)，master (192.168.5.13)，slave1 (192.168.5.14)，slave2 (192.168.5.15)

（1）安装数据库

yum install -y mariadb-server

（2）主服务器配置

vim /etc/my.cnf
server-id=79
log-bin
skip_name_resolve

systemctl restart mariadb.service

（3）从服务器配置

vim /etc/my.cnf
server-id=72
log-bin
read-only
skip_name_resolve
relay_log_purge=0 

（4）主服务器添加远程连接用户和管理节点

grant replication slave on *.* to repluser@'192.168.5.%' identified by '123456';
grant all on *.* to mhauser@'192.168.5.%' identified by '123456';
select host,user,password from mysql.user;
show master logs ;

（5）配置从服务器(两台同样)

CHANGE MASTER TO 
  MASTER_HOST='192.168.5.13',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245;

show slave status\G

start slave ;

show variables like 'read_only' ;

（6）各主机安装MHA对应包

yum install mha*.rpm

（7）配置所有主机间的key验证

ssh-keygen
ls -a
ls -a .ssh
ssh-copy-id 192.168.5.12
cd .ssh
cat authorized_keys
scp -r .ssh 192.168.5.13:/root/
scp -r .ssh 192.168.5.14:/root/
scp -r .ssh 192.168.5.15:/root/

（8）配置管理节点

mkdir /etc/mah
vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=123456
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=123456
ping_interval=1
[server1]
hostname=192.168.5.13
candidate_master=1
[server2]
hostname=192.168.5.14
[server3]
hostname=192.168.5.15
candidate_master=1

（9）MHA验证

masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf 

（10）MHA启动

masterha_manager --conf=/etc/mha/app1.cnf
处于持续监控状态

（11）模拟主服务器出故障

先跑一个生成较大数据库的脚本

use test
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure  sp_testlog() 
    begin  
    declare i int;
    set i = 1; 
    while i <= 100000 
    do  insert into testlog(name,age) values (concat('wang',i),i); 
    set i = i +1; 
    end while; 
    end$$
delimiter ;
call sp_testlog;

未完成中间直接“断电”模拟宕机，此时可以发现管理主机监控发生改变并退出

（12）查看日志

tail -f /data/mastermha/app1/manager.log

提示192.168.5.15成为新的主服务器，验证查看

提示：在从服务器提升为主服务器后，必须人为去修改配置文件，将"read-only"删掉或注释掉