MySQL主从以及MHA搭建

最新推荐文章于 2022-04-12 21:23:23 发布

mangzi_

最新推荐文章于 2022-04-12 21:23:23 发布

阅读量431

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43519771/article/details/120214693

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、环境软件版本及机器介绍

环境	版本
操作系统：Centos 7 x86_64	7
虚拟机：WMware	16.1.2
数据库：mysql	5.7.34

主机IP	角色	server-id
192.168.0.227	master	1	mysql主节点，负责写数据
192.168.0.228	slave1	2	mysql从节点，负责读数据，备选master
192.168.0.229	slave2	3	mysql从节点，负责读数据
192.168.0.230	manager		mySQL 高可用方案，故障切换，mha-monitor

二、MySQL主从搭建

3.1、环境准备

配置虚拟机以及环境确保各IP之间可以ping通
在一台虚拟机上安装mysql服务利用克隆快速搭建检查mysql使用是否正常
因为是克隆的虚拟机所以要修改mysql的UUID 不然主从复制会报错

2.2、MySQL主从配置

2.2.1、Master节点配置

编辑配置文件my.cnf，开启binlog，指定需要同步数据库和不同步的数据库

[mysqld]
#开启binlog日志功能
log_bin=mysql-bin
#设置server-id
server-id=1

sync-binlog=1
#设置忽略的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql

#设置semi开启和超时设置，这里暂时不开启，安装完半同步的sime插件后开启。
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=1000

保存之后重启数据库：

[root@localhost mysql-rpm]# systemctl restart mysqld

登录master，并且给从库进行授权操作，授权之后刷新权限。

-- 授权
mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看主库Master的状态。

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 869
     Binlog_Do_DB: 
 Binlog_Ignore_DB: information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

2.2.2、Slave节点配置

从库开启relay-log中继日志，并且制定server-id。

编辑/etc/my.cnf配置文件。

log_bin=mysql-bin
sync-binlog=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

#指定server id,slave2机器配置为3
server-id=2
#指定中继日志名称
relay_log=mysql-relay-bin
#开启数据库只读
read_only=1

重启mysql的slave服务器。

[root@localhost mysql-rpm]# systemctl restart mysqld

登录两台slave服务器，创建连接到master服务器进行同步。

mysql> change master to master_host='192.168.0.227',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动slave

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

对从库进行链接授权：

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2.3、半同步配置

首先检查主库是否支持动态插件的安装：

mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

2.3.1、Master节点配置

检查数据库是否已经安装了sime插件。

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)

安装sime插件，并且参考morialdb给插件取个名字semisync_master.so：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

安装完成之后可以查看semi的环境变量信息。

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

之后开启semi的功能，开启semi有两种方法，一种是set global …，另一种是配置my.cnf。需要设置超时时间，默认10秒钟过长，改成1秒钟。

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

mysql> set global rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 1000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

2.3.2、Slave节点配置

从库也需要做semi的安装操作，安装semi_slave插件。

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)

查看semi的环境变量。

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

开启semi功能

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

重启slave

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

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

2.3.3、测试

检查mysql日志，查看semi模块是否已经启用。

[root@localhost mysql-rpm]# tail -f /var/log/mysqld.log

三、MHA搭建

3.1、环境准备

3.1.1、主机SSH互通

在四台服务器上分别执行下面命令，生成公钥和私钥。

[root@localhost ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
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:
68:86:33:a6:0a:fc:df:e0:36:d4:d1:a0:18:1d:f3:75 root@localhost.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
|    .o.  . E     |
|   . .o.. .      |
|    o ..o        |
|   . o o .       |
|    = = S        |
|.  o * .         |
|... ..           |
|... .oo          |
|.  .oo..         |
+-----------------+

在三台MySQL服务器分别执行下面命令，将公钥拷贝到MHA Manager服务器上。

[root@localhost mysql-rpm]# ssh-copy-id 192.168.0.230
The authenticity of host '192.168.0.230 (192.168.0.230)' can't be established.
ECDSA key fingerprint is a0:a7:22:1d:23:06:a1:86:d9:6e:4a:fe:e4:45:d4:72.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.0.230's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.0.230'"
and check to make sure that only the key(s) you wanted were added.

在MHA Manager服务器检查是否成功上传公钥。

[root@localhost ~]# cat /root/.ssh/authorized_keys

从MHA Manager服务器执行命令，向其他三台机器分发公钥。

[root@localhost ~]# scp /root/.ssh/authorized_keys 192.168.0.227:/root/.ssh/authorized_keys
[root@localhost ~]# scp /root/.ssh/authorized_keys 192.168.0.228:/root/.ssh/authorized_keys
[root@localhost ~]# scp /root/.ssh/authorized_keys 192.168.0.229:/root/.ssh/authorized_keys

检查四台机器之间是否ssh互通，各个机器都进行检查，如果不通，则通过ssh-copy-id命令将公钥复制给相应的机器。

[root@localhost mysql-rpm]# ssh 192.168.0.229

3.1.2、MHA下载

MySQL对应的MHA版本是0.5.8，在GitHub上找到对应的rpm包进行下载，需要下载MHA manager和node的安装包。

https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58

下载后将manager和node的安装包分别上传到对应服务器。

三台MySQL服务器需要安装node，MHA Manager服务器需要安装manager和node。

3.2、MHA安装

3.2.1、MHA Node安装

四台服务器上都需要安装mha4mysql-node。MHA的Node依赖于perl-DBD-MySQL，所以要先安装perl-DBD-MySQL。

[root@localhost mysql-rpm]# yum install perl-DBD-MySQL -y
[root@localhost mysql-rpm]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

3.2.2、MHA Manager安装

在MHA Manager服务器安装mha4mysql-manager。

mha4mysql-manager又依赖了perl-Config-Tiny、perl-Log-Dispath、perl-Parallel-ForkManager，也分别进行安装，接下来安装epel环境。

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm -y

yum install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
此时发现日志中出现
No package perl-Log-Dispatch available.
No package perl-Parallel-ForkManager available.
仓库中不存在这两个安装包，需要手动安装

这里我采用手动下载rpm包然后通过XFtp上传到服务器上。首先下载perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm包，在安装时提示所需的依赖：
perl(Class::Load) is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(MIME::Lite) is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(Mail::Send) is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(Mail::Sender) is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(Mail::Sendmail) is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(Params::Validate) is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(Params::Validate) >= 0.15 is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
perl(Sys::Syslog) >= 0.25 is needed by perl-Log-Dispatch-2.41-1.el7.1.noarch
因此需要下载其他依赖包。
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-IO-Socket-SSL-1.94-7.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MailTools-2.12-2.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
perl-Params-Validate-1.08-4.el7.x86_64.rpm
perl-Sys-Syslog-0.33-3.el7.x86_64.rpm

安装perl-Class-Load，可以在线安装。

[root@localhost mysql-rpm]# yum install perl-Class-Load

安装其他依赖包

[root@localhost mysql-rpm]# rpm -ivh perl-Email-Date-Format-1.002-15.el7.noarch.rpm
[root@localhost mysql-rpm]# yum install perl-IO-Socket-SSL
[root@localhost mysql-rpm]# rpm -ivh perl-Mail-Sender-0.8.23-1.el7.noarch.rpm 
[root@localhost mysql-rpm]# rpm -ivh perl-MIME-Types-1.38-2.el7.noarch.rpm
[root@localhost mysql-rpm]# rpm -ivh perl-MIME-Lite-3.030-1.el7.noarch.rpm
[root@localhost mysql-rpm]# rpm -ivh perl-Sys-Syslog-0.33-3.el7.x86_64.rpm 
[root@localhost mysql-rpm]# rpm -ivh perl-Params-Validate-1.08-4.el7.x86_64.rpm 
[root@localhost mysql-rpm]# rpm -ivh perl-Mail-Sender-0.8.23-1.el7.noarch.rpm 
[root@localhost mysql-rpm]# rpm -ivh perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
[root@localhost mysql-rpm]# yum install perl-TimeDate -y
[root@localhost mysql-rpm]# yum install perl-Net-SMTP-SSL -y
[root@localhost mysql-rpm]# rpm -ivh perl-MailTools-2.12-2.el7.noarch.rpm
[root@localhost mysql-rpm]# rpm -ivh perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@localhost mysql-rpm]# rpm -ivh perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm

到此epel环境安装完毕，接下来安装mha4mysql-manager。

[root@localhost mysql-rpm]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3.3、MHA配置

MHA Manager服务器需要为每个监控的Master/Slave集群提供一个专用的配置文件，而所有的Master/Slave集群也可以共享全局配置。

3.3.1、初始化配置目录

#目录说明
#/var/log                      (Centos目录)
#        /mha                  (MHA监控根目录)
#            /app1             (MHA监控实例根目录)
#                 /manager.log (MHA监控实例日志文件)
[root@localhost bin]# mkdir -p /var/log/mha/app1
[root@localhost bin]# touch /var/log/mha/manager.log

3.3.2、配置监控全局配置文件

vim /etc/masterha_default.cnf

[server default]
#用户名
user=root
#密码
password=root
#ssh登录账号
ssh_user=root
#主从复制账号
repl_user=root
#主从复制密码
repl_password=root
#设置监控主库，发送ping包的时间间隔
ping_interval=1
#设置发生切换之后发送的报警脚本
#report_script=/usr/local/send_report
#二次检查的主机，实现多路监测master的可用性
secondary_check_script=masterha_secondary_check -s 192.168.0.227 -s 192.168.0.228 -s 192.168.0.229

3.3.3、配置监控实例配置文件

vim /etc/mha/app1.cnf

[server default]
#MHA监控实例根目录
manager_workdir=/var/log/mha/app1
#MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log

#[serverx]         服务器编号
#hostname          主机名
#candidate_master  可以做主库
#master_binlog_dir binlog日志文件目录

[server1]
hostname=192.168.0.227
candidate_master=1
master_binlog_dir="/var/lib/mysql"

[server2]
hostname=192.168.0.228
candidate_master=1
master_binlog_dir="/var/lib/mysql"

[server3]
hostname=192.168.0.229
candidate_master=1
master_binlog_dir="/var/lib/mysql"

3.4、MHA配置检测

3.4.1、执行SSH通信检测

在MHA Manager服务器上执行：

[root@localhost bin]# masterha_check_ssh --conf=/etc/mha/app1.cnf

3.4.2、检测MySQL主从复制

在MHA Manager服务器上执行：

[root@localhost bin]# masterha_check_repl --conf=/etc/mha/app1.cnf

出现了“MySQL Replication Health is OK.”说明集群没有问题。

3.5、启动 MHA manager

在MHA Manager服务器上执行：

[root@localhost bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &

[root@localhost bin]# nohup: ignoring input and appending output to ‘nohup.out’

[root@localhost bin]# jobs
[1]+  Running                 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &

启动命令参数说明

--remove_dead_master_conf       该参数代表当发生主从切换后，老的主库的ip将会从配置文件中移除。
--manger_log                    日志存放位置
--ignore_last_failover          在缺省情况下，如果MHA检测到连续发生宕机，且两次宕机间隔不足8小时的话，则不会进行Failover，之所以这样限制是为了避免ping-pong效应。该参数代表忽略上次MHA触发切换产生的文件，默认情况下，MHA发生切换后会在日志目录，也就是上面我设置的/data产生app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为--ignore_last_failover。

查看监控状态命令：

[root@localhost bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:54196) is running(0:PING_OK), master:192.168.0.227

查看监控日志命令：

[root@localhost bin]# tail -f /var/log/mha/app1/manager.log

3.6、测试MHA故障转移

3.6.1、模拟主节点奔溃

master上停止mysql服务。

systemctl stop mysqld

在MHA Manager上查看MHA服务和切换日志

整个MHA的切换过程包括以下步骤：

1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作（这个我这里还没有实现，需要研究）
3.复制dead maste和最新slave相差的relay log，并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件（binlog events）
6.提升一个slave为新的master进行复制
7.使其他的slave连接新的master进行复制

发生切换后，新主库的起始日志和点分别是 mysql-bin.000002:586

在slave2机器上查看主从复制情况：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.228
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 586
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.6.2、原主启动切换回主

启动MySQL原主服务：

systemctl start mysqld

挂到新主做从库：

mysql> change master to master_host='192.168.0.228',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000002',master_log_pos=586;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.228
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 586
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

使用MHA在线命令将原主切换回来

[root@localhost bin]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.0.227 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

注意：此时/etc/mha/app1.cnf文件的内容已经删除了192.168.0.227机器，需要将该机器重新挂载家MHA机器中，再进行原主切换操作。此外，如果来了一台新的机器做从库，则使用先将数据备份导入，然后再复制。

在主从完成切换之后，MHA Manager会自动退出，需要在完成主从切换之后重新启动

[root@localhost bin]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

切换的日志记录：

[root@localhost bin]# vim /etc/mha/app1.cnf 
[root@localhost bin]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.0.227 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

四、测试SQL脚本

4.1、创建lagou数据库

mysql> create database lagou;
Query OK, 1 row affected (0.01 sec)
mysql> use lagou;

4.2、创建Position表

mysql> create table position ( id int(20) primary key, name varchar(50), salary varchar(20), city varchar(50) ) ENGINE=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

往Position表中插入记录：

mysql> insert into position values(1,'Java',13000,'shanghai');
Query OK, 1 row affected (0.01 sec)

mysql> insert into position values(2,'DBA',20000,'beijing');
Query OK, 1 row affected (0.00 sec)

4.3、创建Position_detail表

create TABLE position_detail (id int(20) primary key,pid int(20),description text) ENGINE=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)

往position_detail表中插入数据：

mysql> insert into position_detail values(1,1,'JAVA Developer');
Query OK, 1 row affected (0.00 sec)

mysql> insert into position_detail values(2,2,'Database Administrator');
Query OK, 1 row affected (0.01 sec)

mangzi_

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL主从以及MHA搭建

一、环境软件版本及机器介绍环境版本操作系统：Centos 7 x86_647虚拟机：WMware16.1.2数据库：mysql5.7.34主机IP角色server-id192.168.0.227master1mysql主节点，负责写数据192.168.0.228slave12mysql从节点，负责读数据，备选master192.168.0.229slave23mysql从节点，负责读数据192.168.
复制链接

扫一扫