MySQL8.0:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';   ###修改localhost root用户访问本机数据库的密码
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456';            ###创建root用户允许所有ip访问数据库及密码   #危险!!#

mysql> GRANT ALL ON *.* TO 'root'@'%';    ###授权root用户访问所有ip访问及表权限   #危险!!#
mysql> FLUSH PRIVILEGES;  刷新权限


mysql> CREATE USER 'repl'@'192.168.0.19' IDENTIFIED WITH mysql_native_password BY '123456';   ##创建允许从库访问主库的用户、ip及密码
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.19';   ##授权从库权限及从库ip地址

change master to master_host='192.168.0.18',master_user='repl',master_password='123456',master_log_file='mysql-bin.000011',master_log_pos=840;  ##从库读取主库的host地址、用户、密码及主库的binlog二进制文件名和pos位置号

mysql> show master status\G;  查看主库信息 binlog二进制文件名、当前POS号

mysql> show slave status\G;  查看从库信息  relaylog信息及其他信息


MHA 4主机 1主2从 1管理节点(所有节点全部安装mha4mysql-node, 服务节点安装mha4mysql-manager及mha4mysql-node)centos 7.9.2009 + mysql8.0.23 测试通过:

主my.cnf:

[mysqld]
basedir=/opt/mysql
datadir=/opt/mysqldata
port=3306
binlog_format=ROW
log-bin=mysql-bin
socket=/opt/mysql/mysql.sock
server-id=1
default-storage-engine = innodb
character_set_server = utf8mb4
collation_server = utf8mb4_bin
user=mysql
log-error=/var/log/mysql/error.log

binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

#这三个主库从库都配置了,不同步他们的binlog二进制信息

replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
#这三个在从机上配置,主库上没有配置,从机不同步主机的用户权限及信息


default_authentication_plugin = 'mysql_native_password'

gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=1

[client]
port=3306
socket=/opt/mysql/mysql.sock
#default-character-set=utf8mb4

[root@mysql-4 ~]# vi /etc/mha/mysql-mha.conf

[server default]
user=dba_mha    ### MHA访问数据库的用户  主从都设置
password=123456    ###MHA访问数据库的密码
manager_workdir=/home/mha     ###管理节点的目录
manager_log=/home/mha/manager.log    ###日志文件名
remote_workdir=/home/mha                    ###发生故障时主库保存binlog的位置
ssh_user=root                                    ### ssh用户
repl_user=repl                                   ###主从同步的用户名
repl_password=123456                      ###主从同步的用户密码
ping_interval=1                                  ###ping次数
master_binlog_dir=/opt/mysqldata       ###主库的binlog位置
ssh_port=22                                         ###ssh端口号
master_ip_failover_script = /usr/bin/master_ip_failover              ###切换脚本
secondary_check_script = /usr/bin/masterha_secondary_check -s 192.168.0.18 -s 192.168.0.19 -s 192.168.0.20
[server1]
hostname=192.168.0.18         ##主库
port=3306
candidate_master=1             ##可参与切换的库
[server2]
hostname=192.168.0.19
port=3306
candidate_master=1            ##可参与切换的库(可以是从库 出故障时可参与切换成主库)
[server3]
hostname=192.168.0.20
port=3306
no_master=1                 ##不参与切换的库(从库)


切换脚本:

[root@mysql-4 ~]# vi /bin/master_ip_failover
#!/usr/bin/env perl


use strict;
use warnings FATAL => 'all';

use Getopt::Long;
use MHA::DBHelper;

my (
  $command,        $ssh_user,         $orig_master_host,
  $orig_master_ip, $orig_master_port, $new_master_host,
  $new_master_ip,  $new_master_port,  $new_master_user,
  $new_master_password
);
my $vip = '192.168.0.27/24';     ##配置的一个同网段虚拟ip
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";     ###监听的网卡
my $ssh_stop_vip = "/sbin/ifconfig ens33:$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,
  'new_master_user=s'     => \$new_master_user,
  'new_master_password=s' => \$new_master_password,
);

exit &main();

sub main {
  if ( $command eq "stop" || $command eq "stopssh" ) {

    # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
    # If you manage master ip address at global catalog database,
    # invalidate orig_master_ip here.
    my $exit_code = 1;
    eval {

      # updating global catalog, etc
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
    elsif ( $command eq "start" ) {

        # all arguments are passed.
        # If you manage master ip address at global catalog database,
        # activate new_master_ip here.
        # You can also grant write access (create user, set read_only=0, etc) here.
        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
        print "Checking the Status of the script.. OK \n";
        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}


sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
   `ssh $ssh_user\@$orig_master_host \" $ssh_stop_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";
}

masterha_check_ssh --conf=/etc/mysql-mha.conf    ###测试各节点ssh

masterha_check_repl --conf=/etc/mysql-mha.conf    ###测试repl、binlog、及配置文件

masterha_manager --conf=/etc/mysql-mha.conf &   ###开启mha

©著作权归作者所有:来自51CTO博客作者PartyAnimals的原创作品,如需转载,请注明出处,否则将追究法律责任

更多相关文章

  1. 写用户注册表
  2. 手把手从0开始教你搭建Jumpserver,为服务器安全保驾护航!
  3. 浅析ElasticSearch原理
  4. 课程表、用户表单代码
  5. 容器技术|Docker三剑客之docker-swarm
  6. 课程表和用户注册的作业
  7. 表格和用户注册
  8. Spark + ElasticSearch 构建电商用户标签系统实现精准营销
  9. 将Dynamics 365中的用户及其角色、角色导出到Excel中

随机推荐

  1. Unity Android打包apk安装错误INSTALL_FA
  2. Android PVPlayer介绍
  3. android:screenOrientation横屏竖屏设置
  4. 知识体系引导
  5. Android(安卓)最火的快速开发框架android
  6. Android常用知识笔记
  7. Android的架构与Android应用程序启动流程
  8. Android点击事件之后跳到界面指定位置
  9. android上的i-jetty (1)环境搭建
  10. Android 之 EditText属性用法介绍