Linux作业（5）——MySQL集群之MHA,PXC及Ansi

2020-10-19  本文已影响0人  羰基生物

1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）

###先完全备份主节点的数据，然后将备份发送给新的从节点
[root@master ~]# mysqldump -A -F --single-transaction --master-data=1 > /backup/full_`date +%F_%T`.sql
[root@master ~]# scp /backup/full_2020-10-18_04\:51\:34.sql 10.0.0.53:/data/

###新的丛节点修改/etc/my.cnf配置文件，配置全局唯一的server-id
[root@slave2 ~]# vim /etc/my.cnf

[mysqld]
server-id=53
read-only

###查看并修改收到的备份文件
[root@slave2 data]# vim full_2020-10-18_05\:14\:06.sql 
###找到如下信息，并配置好，此时看到的MASTER_LOG_POS为156，文件为master-bin.000009，复制的只是从主节点备份操作后到现在的数据，而原来的数据通过备份文件恢复到新的从节点。
CHANGE MASTER TO MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000009', MASTER_LOG_POS=156;
--
-- Current Database: `hellodb`
--

###修改完成后，启动数据库，并将备份文件导入，可以直接在shell环境下重定向文件，也可以直接在MySQL中导入。
[root@slave2 data]# mysql > full_2020-10-18_05\:14\:06.sql 

###导入完成后，因为是新的从节点，需要手动开启两个线程io线程和sql线程。
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000009
          Read_Master_Log_Pos: 156
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: master-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
###能看到从节点的复制状态，查看数据已经恢复。

2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）

一主多从架构下，当主节点服务崩溃，且没有其他手段保证服务的情况下，需要将某个从节点提升为主节点，选择其中数据较新的从节点作为新的主节点，可以通过比较 Read_Master_Log_Pos的值，较大的那个成为新主节点。

[root@slave2 log]# vim /etc/my.cnf

[mysqld]
server-id=53
#read-only

mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.02 sec)



- 其它主从服务器安装mha的node包

  
  [root@slave2 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
  [root@slave1 backup]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
  [root@master ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm


- 在mha管理主机上配置所有主机基于key验证


  [root@mha ~]# ssh-keygen
  Generating public/private rsa key pair.
  Enter file in which to save the key (/root/.ssh/id_rsa): 
  Enter passphrase (empty for no passphrase): 
  Enter same passphrase again: 
  Your identification has been saved in /root/.ssh/id_rsa.
  Your public key has been saved in /root/.ssh/id_rsa.pub.
  The key fingerprint is:
  SHA256:dxVawlKpLLyeqg9E7rI9F8yuREf60SEXo2tuQJInWnI root@mha.org
  The key's randomart image is:
  +---[RSA 2048]----+
  |        o  oo.o  |
  |   .   . o. o+ . |
  |. E + +.o. o. .  |
  | = B o =o.o  .   |
  |.   *o= So. .    |
  |   + *+... .     |
  |  . +.+o .       |
  |   =.oo o        |
  |  . +*o.         |
  +----[SHA256]-----+
  [root@mha ~]# rsync -av .ssh 10.0.0.51:/root
  [root@mha ~]# rsync -av .ssh 10.0.0.52:/root
  [root@mha ~]# rsync -av .ssh 10.0.0.53:/root
  
  ###验证
  [root@mha ~]# ssh 10.0.0.51
  Last login: Sun Oct 18 08:42:17 2020 from 10.0.0.10
  [root@master ~]# exit
  logout
  Connection to 10.0.0.51 closed.
  


- 在MHA管理主机创建配置文件


  [root@mha ~]# vim /etc/mha/test.cnf
  
  [server default]
  user=mhauser
  password=123456
  manager_workdir=/data/mha/test/
  manager_log=/data/mha/test/manager.log
  remote_workdir=/data/mha/test/
  ssh_user=root
  repl_user=repl
  repl_password=replpass
  ping_interval=1
  master_ip_failover_script=/usr/local/bin/master_ip_failover###下一步准备相关脚本
  report_script=/usr/local/bin/sendmail.sh###下一步准备相关脚本
  check_repl_delay=0
  master_binlog_dir=/data/mysql/
  [server1]
  hostname=10.0.0.53
  candidate_master=1
  [server2]
  hostname=10.0.0.52
  [server3]
  hostname=10.0.0.51
  candidate_master=1


- 准备相关脚本

  - sendmail.sh


    [root@mha ~]# vim /usr/local/bin/sendmail.sh
    #!/bin/bash
    echo "MySQL is down" | mail -s "MHA WARNING" root@277297360.com 
    [root@mha ~]# chmod +x /usr/local/bin/sendmail.sh
    [root@mha ~]# cat  >> /etc/mail.rc <<EOF
    set from=277297360@qq.com
    set smtp=smtp.qq.com
    set smtp-auth-user=277297360@qq.com
    set smtp-auth-password=qyazdpihrzbtbjjf
    EOF


  - master_ip_failover

    [root@mha ~]# vim /usr/local/bin/master_ip_faolover 
    #!/usr/bin/env perl
    use strict;
    use warnings FATAL => 'all';
    use Getopt::Long;
    my (
    $command,$ssh_user,$orig_master_host,$orig_master_ip,
    $orig_master_port,$new_master_host,$new_master_ip,$new_master_port);
    my $vip = '10.0.0.200/24';
    my $gateway = '10.0.0.2';
    my $interface ='ens33';
    my $key ='1';
    my $ssh_start_vip="/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -C 3 -s $vip $gateway >/dev/null 2>&1";
    my %ssh_stop_vip ="/sbin/ifconfig $interface:$key down";
    Getoptions(
    'command=s' => \$command,
    'ssh_user=s' => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s' => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s' => \$new_master_host,
    'new_master_ip=s' => \$new_master_ip,
    'new_master_port=i' => \$new_master_port,
    );
    sub main {
    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
    if ( $command eq "stop" || $command eq "stopssh" ) {
    my $exit_code = 1;
    eval {
    print "Disabling the VIP on old master: $orig_master_host \n";
    &stop_vip();
    if ($@) {
    warn "Got Error: $@\n";
    exit $exit_code; 
    }
    exit $exit_code;
    }
    elsif ( $command eq "start" ) {
    my $exit_code = 10; 
    eval {
    print "Enabling the VIP - $vip on the new master - $new_master_host \n";
    &start_vip();
    $exit_code = 0;
    };
    if ($@) {
    warn $@;
    exit $exit_code;
    }
    exit $exit_code;
    }
    elsif ( $command eq "status" ) {
    print "Checking the status of the script.. ok \n";
    `ssh $ssh_suer\@$orig_master_host \" $ssh_start_vip \"`;
    exit 0;
    }
    else {
    &usage();
    exit 1;
    }
    }
    sub start_vip() {
    `ssh $ssh_suer\@$new_master_host \" $ssh_start_vip \"`;
    }
    sub stop_vip() {
    `ssh $ssh_suer\@$orig_master_host \" $ssh_start_vip \"`;
    }
    sub usage {
    print
    "Usage:master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
    }  
    
    [root@mha ~]# chmod +x /usr/local/bin/master_ip_faolover
    


- 配置master主机


  [root@master ~]# vim /etc/my.cnf
  [mysqld]
  server-id=1
  log_bin
  skip_name_resolve=1
  general_log
  
  
  [root@master ~]# yum install -y net-tools
  [root@master ~]# ifconfig ens33:1 10.0.0.200/24
  
  mysql> create user 'repl'@'10.0.0.%'identified with 'mysql_native_password' by 'replpass';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> grant replication slave on *.* to 'repl'@'10.0.0.%';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> create user 'mhauser'@'10.0.0.%'identified with 'mysql_native_password' by '123456';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> grant all on *.* to 'mhauser'@'10.0.0.%';
  Query OK, 0 rows affected (0.01 sec)
  
  ###这里忘记提前查看日志就创建账号了，所以待会配置从节点的时候需要再次授权


  

- 配置slave1


  [root@slave1 ~]# vim /etc/my.cnf
  [mysqld]
  server-id=52
  log_bin
  read_only
  relay_log_purge=0
  skip_name_resolve=1
  
  [root@slave1 ~]# systemctl enable --now mysqld
  
  mysql> change master to master_host='10.0.0.53',
      -> master_user='repl',
      -> master_password='replpass',
      -> master_log_file='slave2-bin.000009',
      -> master_log_pos=1863;
  
  mysql> create user 'repl'@'10.0.0.%'identified with 'mysql_native_password' by 'replpass';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> grant replication slave on *.* to 'repl'@'10.0.0.%';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> create user 'mhauser'@'10.0.0.%'identified with 'mysql_native_password' by '123456';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> grant all on *.* to 'mhauser'@'10.0.0.%';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> start slave;
  
  


- 配置slave2


  [root@slave2 ~]# vim /etc/my.cnf
  [mysqld]
  server-id=51
  log_bin
  read_only
  relay_log_purge=0
  skip_name_resolve=1
  
  [root@slave2 ~]# systemctl enable --now mysqld
  
  mysql> change master to master_host='10.0.0.53',
      -> master_user='repl',
      -> master_password='replpass',
      -> master_log_file='slave2-bin.000009',
      -> master_log_pos=1863;
  
  mysql> create user 'repl'@'10.0.0.%'identified with 'mysql_native_password' by 'replpass';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> grant replication slave on *.* to 'repl'@'10.0.0.%';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> create user 'mhauser'@'10.0.0.%'identified with 'mysql_native_password' by '123456';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> grant all on *.* to 'mhauser'@'10.0.0.%';
  Query OK, 0 rows affected (0.01 sec)
  
  mysql> start slave;

4、实战案例：Percona XtraDB Cluster(PXC 5.7）

5、通过 ansible 部署二进制 mysql 8

  [root@localhost scripts]# vim push_ssh_key.sh 

  #!/bin/bash
  IPLIST="
  10.0.0.106
  10.0.0.107
  10.0.0.108
  10.0.0.109"

  rpm -q sshpass &> /dev/null || yum -y isntall sshpass
  [ -f /root/.ssh/id_rsa ] || ssh-keygen -f /root/.ssh/id_rsa -P ''
  export SSHPASS=123456
  for IP in $IPLIST;do
          sshpass -e ssh-copy-id -o StrictHostkeyChecking=no $IP
  done


  #!/bin/bash
  
  OLD_PASSWORD=`grep  'temporary password' /var/password.txt | awk -F' ' '{printf $13}'`
  NEW_PASSWORD='Changeme_123'

  mysqladmin -uroot -p$OLD_PASSWORD password $NEW_PASSWORD


[root@localhost ~]# mysql -uroot -p'Changeme_123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
上一篇 下一篇

猜你喜欢

热点阅读