第十五周

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