第十五周作业

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

#!/bin/bash
#
. /etc/init.d/functions
username=root
passname=123456
PS3="Please input a number[1|2]:  "
mysqldumpdir=/mysqldumpdir
xtrabackupdir=/xtrabackupdir-`date +"%F-%T"`
[ -d $mysqldumpdir ] || mkdir $mysqldumpdir
select backup_method in mysqldump xtrabackup;do
    case $backup_method in
        mysqldump)
            mysqldump -u${username} -p${passname} -A -F -E -R -q \
            --single-transaction --master-data=1 --flush-privileges \
            --triggers --default-character-set=utf8 \
            > $mysqldumpdir/`date +"%F-%T"`.sql && \
            action "`date +"%F-%T"` data backup to $mysqldumpdir" true || action "backup data" false
            break
            ;;
        xtrabackup)
            xtrabackup --user=$username --password=$passname \
            --backup --target-dir=$xtrabackupdir  \
            &> /dev/null && action "`date +"%F-%T"` data backup to $xtrabackupdir" true \
            || action "backup data" false
            break
            ;;
        *)
            echo "not support"
            ;;
    esac
done

配置Mysql主从同步

基于mysql5.7
master： 192.168.43.9
slave： 192.168.43.29
二进制包：/usr/local/src/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

配置 MySQL master

vim /scripts/install_mysql5.7.sh

#!/bin/bash                                                                                                                                                                                  
#
. /etc/init.d/functions
SRC_DIR=`pwd`
MYSQL='mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz'
COLOR="echo -e \\033[01;31m"
END='\033[0m'
MYSQL_ROOT_PASSWORD=123456
[ -d /data ] || mkdir /data
check (){
cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
    return
fi
}

install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
     yum  -y -q install numactl-libs   libaio mariadb &> /dev/null
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }

    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=9
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock
EOF
    mysqld --initialize --user=mysql --datadir=/data/mysql
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    chkconfig --add mysqld
    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成"
}

check
install_mysql

运行脚本

配置

sed -i '/\[mysqld\]/a\gtid_mode=on\nenforce_gtid_consistency' /etc/my.cnf
service mysqld restart

mysql -uroot -p123456 -e "grant replication slave on *.* to repluser@'192.168.43.%' identified by 'centos';flush privileges;"

配置 MySQL slave

scp 192.168.43.9:/usr/local/src/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz /usr/local/src/
scp 192.168.43.9:/scripts/install_mysql5.7.sh /usr/local/src/

修改 install_mysql5.7.sh这个脚本

将 server-id=9 改成 server-id=29
将 log-bin这行删除掉
增加下面2行
gtid_mode=on
enforce_gtid_consistency

运行脚本，配置连接主的ip，用户名，密码，开启自动找二进制文件位置

mysql -uroot -p123456
MySQL [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.43.9',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_PORT=3306,
    -> MASTER_auto_position=1;
MySQL [(none)]> start slave; 

测试主从是否成功
在 master 上导入 hellodb_innodb.sql
mysql -uroot -p123456 < hellodb_innodb.sql

在从节点上查看

使用MHA实现Mysql高可用

1 ) 基于 mysql5.7 GTID 主从搭建已经ok了
2 ) 准备软件包
mha4mysql-node-0.56-0.el6.noarch.rpm
mha4mysql-manager-0.56-0.el6.noarch.rpm

MHA 机器上安装 mha4mysql-manager-0.56-0.el6.noarch.rpm，mha4mysql-node-0.56-0.el6.noarch.rpm
[MHA]#

yum install mha*.rpm -y

master，slave1，slave2安装mha4mysql-node-0.56-0.el6.noarch.rpm
[master]#
[slave1]#
[slave2]#

yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y

3 ) 基于key的认证
[master]#

ssh-keygen -P "" -t rsa -f /root/.ssh/id_rsa &> /dev/null
ssh-copy-id 127.0.0.1
scp -r ~/.ssh/ 192.168.43.19:/root/
scp -r ~/.ssh/ 192.168.43.29:/root/
scp -r ~/.ssh/ 192.168.43.39:/root/

4 ) 配置文件
[master]# vim /etc/my.cnf

[mysqld]
server-id=19
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
gtid_mode=on
enforce_gtid_consistency
skip_name_resolve
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock

[slave1]# vim /etc/my.cnf

[mysqld]
server-id=29
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
gtid_mode=on
enforce_gtid_consistency
read_only
skip_name_resolve
relay_log_purge=0
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock

[slave2]# vim /etc/my.cnf

[mysqld]
server-id=39
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
gtid_mode=on
enforce_gtid_consistency
read_only
skip_name_resolve
relay_log_purge=0
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock

5 ) master ,slave1,slave2都授权
[master]#

MySQL [(none)]> grant all on *.* to mhauser@'192.168.43.%' identified by 'centos';
MySQL [(none)]> flush privileges;

6 ) 管理节点配置文件
[MHA]#

mkdir /etc/mha/

vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=centos
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=centos
ping_interval=1
[server1]
hostname=192.168.43.19
candidate_master=1
[server2]
hostname=192.168.43.29
[server3]
hostname=192.168.43.39
candidate_master=1

6 ) 测试 mha是否成功
测试ssh

测试 repl

7 ） 启动 mha
[MHA]#

masterha_manager --conf=/etc/mha/app1.cnf

8 ) 验证高可用性
当master挂了，会自动将master切换到backup节点上