Mysql集群技术！综合大礼包等你来拿！！

最新推荐文章于 2024-09-07 08:07:39 发布

Glowon_CcSsYy

最新推荐文章于 2024-09-07 08:07:39 发布

阅读量1.1k

点赞数 23

文章标签： mysql 数据库运维服务器

本文链接：https://blog.csdn.net/m0_67529175/article/details/141690537

版权

一、Mysql 在服务器中的部署方法

1.1 在Linux下部署mysql

1.1.1.下载源码安装需要用到的软件

1.1.2.#导入libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm并解压mysql-boost-5.7.44.tar.gz

1.1.3.源码编译安装mysql

[root@mysql-node1/2  mysql-5.7.44]# cmake \    
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \      #指定安装路径        
-DMYSQL_DATADIR=/data/mysql \                   #指定数据目录 
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \     #指定套接字文件 
-DWITH_INNOBASE_STORAGE_ENGINE=1 \             #指定启用INNODB存储引擎，默认用myisam         
用myisam-DWITH_EXTRA_CHARSETS=all \            #扩展字符集   
-DDEFAULT_CHARSET=utf8mb4 \                     #指定默认字符集 
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \        #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/    #指定c++库依赖

[root@mysql-node10  mysql-5.7.44]# make -j4    #-j4是代表有几个核心就跑几个进程                    
[root@mysql-node10  mysql-5.7.44# make install    #进行编译

注意：当cmake出错后如果想重新检测，删除 mysql-5.7.44 中 CMakeCache.txt即可

1.1.4 部署mysql

1.1.4.1.创建数据库用户并生成mysql目录

1.1.4.2.修改环境变量并生成配置文件

1.1.4.3.数据库初始化建立mysql基本数据

!!报错问题：

1.1.4.4.启动数据库

1.1.4.5.进行数据库安全初始化

1.1.4.6测试

二、mysql的组从复制

2.1 配置mastesr

[root@mysq1-node1~#vim /etc/my.cnf
[mysq1d]
datadir=/data/mysq1
socket=/data/mysq1/mysq1.sock
symbolic-links=0
1og-bin=mysq1-bin
server-id=10
[root@mysq1-node1~]#/etc/init.d/mysqld restart

#进入数据库配置用户权限
[root@mysq1-node1 ~#mysq1 -plee
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MysQL monitor.Commands end with;org.Your MySQL connection id is 3Server version:5.7.44-10g source distribution
copyright(c)2000，2023，0racle and/or its affiliates

mySq1>CREATE USER 'rep'@'%'IDENTIFIED BY'123465';   ##生成专门用来做复制的用户，此用户是用于s1ave端做认证用
mySq1>GRANT REPLICATION SLAVE ON*.* To repl@'%';   ##对这个用户进行授权
mySG7> SHOW MASTER STATUS;    ##查看master的状态

2.2 配置salve

[root@mysq1-node2 ~]# vim /etc/my.cnf
[mysq1d]
datadir=/data/mysq1
socket=/data/mysq1/mysq1.sock
symbolic-links=0
server-id=20
[root@mysq1-node2 ~]#/etc/init.d/mysqld restart
[root@mysq1-node2 ~]# mysq1 -p123456

mysql>CHANGE MASTER TOMASTER_HOST='172.25.254.10',MASTER_USER='repl',MASTER_PASSWORD='123465',MASTER_LOG_FILE='mysq1-bin.000002',MASTER_LOG_POS=154;
Query OK,0 rows affected,2warnings(0.01 sec)

mysq1> start slave;
Query ok,0 rows affected(0.01 sec)

!!报错问题：

附加知识点：

删除数据库：drop database (数据库名称)；

删除数据库表：drop table (数据库表名称)；

删除数据表数据：delete from 表名 where 字段名=字段值；

2.3测试

（一定要当10 20连接成功之后才开始创建才会过去数据如果是在连接之前创建的是不会有数据保存的！！）

node2：在slave中查看数据是否有同步过来

(只要两台机子是连接成功的数据就会同时拥有 master机上面创建的数据都会在slave机上面显示)

2.4 当有数据时添加slave2

#完成基础配置
[root@mysql-node3 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 symbolic-links=0
 server-id=30
 [root@mysql-node3 ~]# /etc/init.d/mysqld restart

#从master节点备份数据
[root@mysql-node1 ~]# mysqldump -uroot -p123456 csy > csy.sql


生产环境中备份时需要锁表，保证备份前后的数据一致
mysql> FLUSH TABLES WITH READ LOCK;
备份后再解锁
mysql> UNLOCK TABLES;
 mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句

---- Table structure for table `userlist`--
 DROP TABLE IF EXISTS `userlist`;        #需要合并数据时需要删除此语句
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;

 #利用master节点中备份出来的csy.sql在slave2中拉平数据
[root@mysql-node3 ~]# mysql -uroot -p123456 -e "create database csy;"
 [root@mysql-node3 ~]# mysql -uroot -p csy <csy.sql
 [root@mysql-node3 ~]# mysql -uroot -p123456 -e "select * from csy.userlist;"
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +----------+----------+
 | username | password |
 +----------+----------+
 | user1    | 123      
 |user2     | 123      
 |
 +----------+----------+

 #配置slave2的slave功能
#在master中查询日志pos
 mysql -uroot -p123456 -e "SHOW MASTER STATUS;"
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +------------------+----------+--------------+------------------+-------------------+
 | File             
| Position | Binlog_Do_DB | Binlog_Ignore_DB | 
Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.000002 |    154 |              
|                  
|                 
|
 +------------------+----------+--------------+------------------+-------------------+
 [root@mysql-node3 ~]# mysql -uroot -pcsy
 mysql>  CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl', 
MASTER_PASSWORD='123465', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
 mysql> start slave;
 mysql> SHOW SLAVE STATUS\G;

测试与上述相同

2.5 延迟复制

延迟复制时用来控制sqI线程的，和i/o线程无关

这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的

是日志已经保存在slave端了，那个sql要等多久进行回放

当master端误操作，可以在slave端进行数据备份

测试：在master中写入数据后过了延迟时间才能被查询到

2.6 慢查询日志

慢查询，顾名思义，执行很慢的查询

当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个 SQL语句就是需要优化的

慢查询被记录在慢查询日志里

慢查询日志默认是不开启的

如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

开启慢查询日志

测试慢查询

2.7 mysql的并行复制

默认情况下slave中使用的是sql单线程回放

在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重

开启MySQL的多线程回放可以解决上述问题

注意：

MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。

2.8 原理刨析

三个线程
实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3个线程来操作一个主库线程，两个从库线程。
二进制日志转储线程(Binlogdumpthread)是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件(Event)的时候，会在 Binlog上加锁，读取完成之后，再将锁释放掉。
从库 I/0 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/0 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志(Relaylog)。
从库 SQL线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤
步骤1:Master将写操作记录到二进制日志(binlog)。
步骤2:Slave将Master的binarylogevents拷贝到它的中继日志(relaylog);

步骤3:Slave重做中继日志中的事件，将改变应用到自己的数据库中。MVSQL复制是异步的且串行化的，而且重启后从接入点开始复制。

具体操作
1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息
2.master端在设定好binlog启动后会开启binlog dump的线程

3.master端的binlog dump把二进制的更新发送到slave端的

4.slave端开启两个线程，一个是I/0线程，一个是sqI线程，

1）i/o线程用于接收master端的二进制日志，此线程会在本地打开relayog中继日志，并且保存到本地磁盘
2）sqI线程读取本地relog中继日志进行回放
5.什么时候我们需要多个slave?
当读取的而操作远远高与写操作时。我们采用一主多从架构数据库外层接入负载均衡层并搭配高可用机制

2.9 架构缺陷

主从架构采用的是异步机制
master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测master端直接保存二进制日志到磁盘

当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slavemaster出现问题slave端接管master，这个过程中数据就丢失了

这样的问题出现就无法达到数据的强一致性，零数据丢失

三、半同步模式

3.1半同步模式原理

1.用户线程写入完成后master中的dump会把日志推送到slave端

2.slave中的io线程接收后保存到relaylog中继日志

3.保存完成后slave向master端返回ack

4.在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎

5.在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok

3.2 gtid模式

当为启用gtid时我们要考虑的问题

在master端的写入时多用户读写，在slave端的复制时单线程日志回放，所以slave端一定会延迟与 master端

这种延迟在slave端的延迟可能会不一致，当master挂掉后slave接管，一般会挑选一个和master延迟日志最接近的充当新的master

那么为接管master的主机继续充当slave角色并会指向到新的master上，作为其slave

这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之间差多少

当激活GITD之后

当master出现问题后，slave2和master的数据最接近，会被作为新的master

slave1指向新的master，但是他不会去检测新的master的pos id，只需要继续读取自己gtid_next即可

[root@mysq1-nodel ~]# mysqlbin1og-vv /data/mysq1/mysq1-bin.000003
@@省略内容@@
SET @@SESSION.GTID_NEXT='AUTOMATIc’ /* added by mysqlbinlog *//*!*/;
DELIMITER ;
# End of 1og file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mysq1-node2 ~#mysql -p123456
mysql>select*from mysql.gtid_executed;
 +--------------------------------------+----------------+--------------+
 | source_uuid                          
| interval_start | interval_end |
 +--------------------------------------+----------------+--------------+
 | 768c6b91-4c01-11ef-a514-000c299355ea |              1 |            1 |
 +--------------------------------------+----------------+--------------+
 1 row in set (0.00 sec)

设置gtid

 #在master端和slave端开启gtid模式
[root@mysql-node1 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=10
 log-bin=mysql-bin
 gtid_mode=ON
 enforce-gtid-consistency=ON
 symbolic-links=0
 [root@mysql-node1 ~]# /etc/init.d/mysqld restart

 [root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=20
 log-bin=mysql-bin
 gtid_mode=ON
 enforce-gtid-consistency=ON
 symbolic-links=0
 [root@mysql-node2 ~]# /etc/init.d/mysqld restart

 [root@mysql-node3 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=30
 log-bin=mysql-bin
 gtid_mode=ON
 enforce-gtid-consistency=ON
 symbolic-links=0
 [root@mysql-node3 ~]# /etc/init.d/mysqld restart

 #停止slave端
[root@mysql-node2 ~]# mysql -p
 mysql> stop slave;
 Query OK, 0 rows affected (0.00 sec)

 [root@mysql-node3 ~]# mysql -p
 mysql> stop slave;
 Query OK, 0 rows affected (0.01 sec)

 #开启slave端的gtid
 mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl', 
MASTER_PASSWORD='123465', MASTER_AUTO_POSITION=1;
 mysql> start slave;
 mysql> show slave status\G;


   Executed_Gtid_Set:
                Auto_Position: 1        #功能开启
         Replicate_Rewrite_DB:

3.3.启用半同步模式

在master端配置启用半同步模式

 [root@mysql-node1 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
server-id=10
 log-bin=mysql-bin
 gtid_mode=ON
 enforce-gtid-consistency=ON
 rpl_semi_sync_master_enabled=1          #开启半同步功能
symbolic-links=0
 [root@mysql-node1 ~]# mysql -p lee
 #安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
 #查看插件情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->  FROM INFORMATION_SCHEMA.PLUGINS
    ->  WHERE PLUGIN_NAME LIKE '%semi%';
 +----------------------+---------------+
 | PLUGIN_NAME          | PLUGIN_STATUS |
 +----------------------+---------------+
 | rpl_semi_sync_master | ACTIVE        |
 +----------------------+---------------+
 1 row in set (0.01 sec)
 #打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
 #查看半同步功能状态
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
 +-------------------------------------------+------------+
 | Variable_name                             | Value      |
 +-------------------------------------------+------------+
 | rpl_semi_sync_master_enabled              | ON         |
 | 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 |
 +-------------------------------------------+------------+
 mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
 +--------------------------------------------+-------+
 | Variable_name                              | Value |
 +--------------------------------------------+-------+
 | Rpl_semi_sync_master_clients               | 0     |
 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
 | Rpl_semi_sync_master_net_wait_time         | 0     |
 | Rpl_semi_sync_master_net_waits             | 0     |
 | Rpl_semi_sync_master_no_times              | 0     |
 | Rpl_semi_sync_master_no_tx                 | 0     |
 | Rpl_semi_sync_master_status                | ON    |
 | Rpl_semi_sync_master_timefunc_failures     | 0     |
 | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
 | Rpl_semi_sync_master_tx_wait_time          | 0     |
 | Rpl_semi_sync_master_tx_waits              | 0     |
 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
 | Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
 +--------------------------------------------+-------+
 14 rows in set (0.00 sec)
 mysql> show plugins

在slave端开启半同步功能

[root@mysql-node2 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=20
 log-bin=mysql-bin
 gtid_mode=ON
 enforce-gtid-consistency=ON 
 rpl_semi_sync_master_enabled=1           #开启半同步功能
symbolic-links=0

[root@mysql-node2 ~]# mysql -plee
 mysql>  INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 Query OK, 0 rows affected (0.01 sec)
 mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
 Query OK, 0 rows affected (0.00 sec)
 mysql> STOP SLAVE IO_THREAD;    #重启io线程，半同步才能生效        
Query OK, 0 rows affected (0.00 sec)
 mysql> START SLAVE IO_THREAD;            ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)
 mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
 +---------------------------------+-------+
 | Variable_name                   | Value |
 +---------------------------------+-------+
 | rpl_semi_sync_slave_enabled    |       | 
| rpl_semi_sync_slave_trace_level | 32    | ON   
 +---------------------------------+-------+
 2 rows in set (0.01 sec)
 mysql>  SHOW STATUS LIKE 'Rpl_semi_sync%';
 +----------------------------+-------+
 | Variable_name              | Value |
 +----------------------------+-------+
 | Rpl_semi_sync_slave_status | ON    |
 +----------------------------+-------+
 1 row in set (0.00 sec)

3.4.测试

在master端写入数据

测试
master
mysql> insert into csy.userlist values('tongbu','321');
Query OK, 1 row affected (0.02 sec)
 
slave
mysql> select * from howe.userlist;
+----------+----------+
| username | password |
+----------+----------+
| tongbu   | 321      |
+----------+----------+
1 rows in set (0.00 sec)
===========================
 
#模拟故障
关闭slave  会发现master只有等到slave开启后才可以继续写入

四、mysql高可用之组复制 (MGR)

MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年12 月推出的一个全新的高可用与高扩展的解决方案
组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务MySQL组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题MGR对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致
提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制
MGR由组通信系统(Group communicationsystem，GCs)协议支持
该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

4.1 组复制流程

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于（N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接提交即可

注意：节点数量不能超过9台

4.2 组复制单主和多主模式

single-primary mode(单写或单主模式)

单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主服务器

multi-primary mode(多写或多主模式)

组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的

4.3.实现mysql组复制

配置文件说明：

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0    
server-id=1                          #配置server唯一标识号
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用指定存储引擎    
gtid_mode=ON                         #启用全局事件标识
enforce_gtid_consistency=ON          #强制gtid一致
master_info_repository=TABLE         #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE
binlog_checksum=NONE                 #禁止对二进制日志校验
log_slave_updates=ON                 #打开数据库中继，
                                 #当slave中sql线程读取日志后也会写入到自己的binlog中
log_bin=binlog                     #重新指定log名称 
binlog_format=ROW                #使用行日志格式 
plugin_load_add='group_replication.so'             #加载组复制插件
transaction_write_set_extraction=XXHASH64             #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #通知插件正式加入 ，或创建的组名#名称为uuid格式
group_replication_start_on_boot=off #在server启动时不自动启动组复制
group_replication_local_address="172.25.254.10:33061" #指定插件接受其他成员的信息端口
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061，
172.25.254.30:33061"                               #本地地址允许访问成员列表
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"     #主机白名单
#不随系统自启而启动，只在初始成员主机中手动开启，
#需要在两种情况下做设定：1.初始化建组时 2.关闭并重新启动整个组时
group_replication_bootstrap_group=off 
group_replication_single_primary_mode=OFF         #使用多主模式
group_replication_enforce_update_everywhere_checks=ON     #组同步中有任何改变检测更新
group_replication_allow_local_disjoint_gtids_join=1     #放弃自己信息以master事件为主

编辑主配置文件：

#清除数据
[root@mysql-node1 ~]# /etc/init.d/mysqld stop 
[root@mysql-node1 ~]# rm -rf /data/mysql/*
 
#配置myc.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.250.10:33061"
group_replication_group_seeds="172.25.250.10:33061,172.25.250.20:33061,172.25.250.30:33061"
group_replication_ip_whitelist="172.25.250.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
 
#初始化 
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld restart 
[root@mysql-node1 ~]# mysql -uroot -p'g*)lg0wf>?bY'    #此密码是初始化生成的

配置mysql：

mysql> SET SQL_LOG_BIN=0;
 Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
 Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
 Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
 Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL 
'group_replication_recovery';
 Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON;     
Query OK, 0 rows affected (0.00 sec)

mysql>  START GROUP_REPLICATION;
 Query OK, 0 rows affected, 1 warning (2.19 sec)

mysql>  SET GLOBAL group_replication_bootstrap_group=OFF;   
 Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

全部主机做本地解析

#在复制配置文件到myql-node2和mysql-node3

 [root@mysql-node2 & 3 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf       
[root@mysql-node2 & 3 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf

#修改mysql—node2和mysl-node3中的配置
[root@mysql-node2 & 3 ~]# rm -fr /data/mysql/
 [root@mysql-node10 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 symbolic-links=0
 server-id=20
                 
#在30上
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
 gtid_mode=ON
 enforce_gtid_consistency=ON
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
binlog_checksum=NONE
 log_slave_updates=ON
 log_bin=binlog
 binlog_format=ROW
 plugin_load_add='group_replication.so'
 transaction_write_set_extraction=XXHASH64
 group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
 group_replication_start_on_boot=off
 group_replication_local_address="172.25.254.20:33061"       

#在30上要写30
 group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061，
172.25.254.30:33061"
 group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
 group_replication_bootstrap_group=off
 group_replication_single_primary_mode=OFF
 group_replication_enforce_update_everywhere_checks=ON
 group_replication_allow_local_disjoint_gtids_join=1

 [root@mysql-node1 ~]# mysqld --user=mysql --initialize
 [root@mysql-node1 ~]# /etc/init.d/mysqld start
 [root@mysql-node1 ~]# mysql -uroot -p初始化后生成的密码  -e "alter user 
root@localhost identified by '123456';"
 #配置sql
 [root@mysql-node2 & 3 ~]# mysql -p123456
 mysql> SET SQL_LOG_BIN=0;
 Query OK, 0 rows affected (0.00 sec)

 mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
 Query OK, 0 rows affected (0.00 sec)

 mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
 Query OK, 0 rows affected (0.00 sec)

 mysql> FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec)

 mysql> SET SQL_LOG_BIN=1;
 Query OK, 0 rows affected (0.00 sec)

 mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 
'group_replication_recovery';
 Query OK, 0 rows affected, 2 warnings (0.00 sec)

 mysql>  START GROUP_REPLICATION;
 Query OK, 0 rows affected, 1 warning (2.19 sec)

 mysql> SELECT * FROM performance_schema.replication_group_members;

测试：

在node1进行添写操作

[root@mysql-node1 ~]# mysql -uroot -p123456
mysql> create database csy;
Query OK, 1 row affected (0.00 sec)
 
mysql> create table csy.userlist(
    -> username varchar(10) primary key not null,
    -> password varchar(50) not null
    -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into csy.userlist values ('user1','111');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from csy.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

node2 进行添写操作

mysql> insert into csy.userlist values ('user2','222');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from csy.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

node3 上查看

五、mysql-router（mysql路由）

MySQL Router

是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。

利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL数据库服务器

Mysql route的部署方式

我们需要在所有的数据库主机之外再开一台主机mysql-router

#安装mysql-router
[root@mysql-node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm 
warning: mysql-router-community-8.4.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID a8d3785c: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-router-community-8.4.0-1.el################################# [100%]
 
 
#配置mysql-router
[root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf 
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.250.10:3306,172.25.250.20:3306,172.25.250.30:3306
routing_strategy = round-robin
 
 
#修改sql node(1,2,3)都要做
mysql> create user root@'%' identified by 'redhat';
Query OK, 0 rows affected (0.01 sec)
 
mysql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye
 
#再回到node1连接
[root@mysql-node1 ~]# systemctl restart mysqlrouter.service 
[root@mysql-node1 ~]# netstat -lntup | grep 7001
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      2740/mysqlrouter    
 
[root@mysql-node1 ~]# mysql -uroot -predhat -h 172.25.250.10 -p 7001
 
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
 
mysql> quit
Bye
 
再来一次
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

测试：

建立测试用户：


mysql> CREATE USER lee@'%' IDENTIFIED BY 'lee';
 mysql> GRANT ALL ON lee.* TO lee@'%';

查看调度结果：

六、mysql高可用之MHA

6.1.MHA概述

MHA（MySQL Master High Availability） 是一种开源的MySQL高可用解决方案，旨在解决MySQL单点故障问题，通过自动化的故障切换功能，确保在主数据库出现故障时，系统能够自动切换到备份数据库，从而保证数据库服务的持续性和数据的一致性。

为什么要用MHA？

Master的单点故障问题

MHA的作用

故障切换自动化：MHA能在0-30秒内自动完成故障切换操作，减少服务中断时间。
数据一致性保证：在故障切换过程中，MHA会确保数据的一致性，避免数据丢失或损坏。
简化管理：MHA提供了集中的管理界面，可以方便地对多个MySQL集群进行监控和管理。
增强可用性：通过主从复制和故障切换机制，MHA显著提高了MySQL数据库的可用性。

MHA的运用场景

MHA适用于各种需要高可用性MySQL数据库服务的场景，包括但不限于：

中小型MySQL数据库环境：对于资源有限、规模适中的MySQL数据库环境，MHA提供了简单、高效的高可用解决方案。
关键业务系统：在关键业务系统中，数据库的高可用性至关重要。MHA能够确保在数据库故障时，系统能够迅速恢复服务，减少业务损失。
云环境和容器化部署：在云环境和容器化部署中，MHA可以轻松地与各种基础设施集成，提供灵活的高可用解决方案。
高并发读写场景：虽然MHA主要关注故障切换和数据一致性，但在高并发读写场景中，结合读写分离等技术，可以进一步提高数据库的整体性能。

MHA的组成

MHA主要由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

MHA Manager：负责监控整个MySQL主从集群的健康状态，包括检测主节点是否在线、从节点的复制延迟等。当主节点出现故障时，MHA Manager会自动执行故障转移流程，选择一个从节点升级为主节点，并通知其他从节点改变复制源至新的主节点。
MHA Node：每个MySQL服务器上运行的一个进程，用于与MHA Manager通信并执行故障切换操作。

MHA的工作原理

监控与故障检测：MHA Manager通过心跳检测机制实时监控主节点状态。
自动故障转移：一旦检测到主节点失效，MHA Manager会立即触发故障转移流程，选择一个符合条件的从节点晋升为主节点。
数据一致性保证：在故障转移过程中，MHA会利用MySQL的复制功能（如半同步复制）来确保数据的一致性。
恢复与同步：当原主节点恢复后，MHA Manager会将其重新加入集群，并开始同步更新，以确保数据的最终一致性。

MHA 的特点

1.自动故障切换过程中，MHA从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失

2.使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性

3.目前MHA支持一主多从架构，最少三台服务，即一主两从

故障切换备选主库的算法

1．一般判断从库的是从（position/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选主。
2．数据一致的情况下，按照配置文件顺序，选择备选主库。
3．设定有权重（candidate_master=1），按照权重强制指定备选主。
（1）默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效。
（2）如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主。

6.2 MHA部署实施

6.2.1 搭建主两从架构

#在master节点中
[root@mysql-node1~]# /etc/init.d/mysqld stop
 [root@mysql-node1 ~]# rm -fr /data/mysql/*
 [root@mysql-node1 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=10
 log-bin=mysql-bin
 gtid_mode=ON
 log_slave_updates=ON
 enforce-gtid-consistency=ON
 symbolic-links=0
 [root@mysql-node1 ~]# mysqld --user mysql --initialize
 [root@mysql-node1 ~]# /etc/init.d/mysqld start
 [root@mysql-node1 ~]# mysql_secure_installation
 [root@mysql-node1 ~]# mysql -p
 mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee';
 Query OK, 0 rows affected (0.00 sec)
 mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
 Query OK, 0 rows affected (0.00 sec)
 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
 Query OK, 0 rows affected (0.02 sec)
 mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
 Query OK, 0 rows affected (0.00 sec)

#在slave1和slave2中
[root@mysql-node2 & 3 ~]# /etc/init.d/mysqld stop
 [root@mysql-node2 & 3 ~]# rm -fr /data/mysql/*
 [root@mysql-node2 & 3 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 server-id=20/30
 log-bin=mysql-bin
 gtid_mode=ON
 log_slave_updates=ON
 enforce-gtid-consistency=ON
 symbolic-links=0
 [root@mysql-node2 & 3 ~]# mysqld --user mysql --initialize
 [root@mysql-node2 & 3 ~]# /etc/init.d/mysqld start
 [root@mysql-node2 & 3 ~]# mysql_secure_installation
 [root@mysql-node2 & 3 ~]# mysql -p
 mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl', 
MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
 Query OK, 0 rows affected, 2 warnings (0.00 sec)
 mysql> start slave;
 Query OK, 0 rows affected (0.00 sec)
 mysql>  INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 Query OK, 0 rows affected (0.01 sec)
 mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
 Query OK, 0 rows affected (0.00 sec)
 mysql>  STOP SLAVE IO_THREAD;
 Query OK, 0 rows affected (0.00 sec)
 mysql> START SLAVE IO_THREAD;
 Query OK, 0 rows affected (0.00 sec)
 mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
 +----------------------------+-------+
 | Variable_name              
| Value |
 +----------------------------+-------+
 | Rpl_semi_sync_slave_status | ON    
|
 +----------------------------+-------+
 1 row in set (0.01 sec)

6.2.2安装MHA所需要的软件

#在MHA中
[root@mysql-mha ~]# unzip MHA-7.zip
 [root@mysql-mha MHA-7]# ls
 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Mail-Sender-0.8.231.el7.noarch.rpm
 mha4mysql-manager-0.58.tar.gz                   perl-Mail-Sendmail-0.7921.el7.noarch.rpm
 mha4mysql-node-0.58-0.el7.centos.noarch.rpm     1.el7.noarch.rpm
 perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Lite-3.0302.el7.noarch.rpm
 perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Net-Telnet-3.0319.el7.noarch.rpm
 perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       2.el7.noarch.rpm
 [root@mysql-mha MHA-7]# yum install *.rpm -y
 perl-MIME-Types-1.38perl-Parallel-ForkManager-1.18
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
root@172.25.254.10:/mnt

 [root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
root@172.25.254.20:/mnt

 [root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
root@172.25.254.30:/mnt

 #在sql-node中
[root@mysql-node1 ~]# yum install /mnt/mha4mysql-node-0.580.el7.centos.noarch.rpm -y
 [root@mysql-node2 ~]# yum install /mnt/mha4mysql-node-0.580.el7.centos.noarch.rpm -y
 [root@mysql-node3 ~]# yum install /mnt/mha4mysql-node-0.580.el7.centos.noarch.rpm -y

在软件中包含的工具包介绍

1.Manager工具包主要包括以下几个工具：

masterha_check_ssh #检查MHA的SSH配置状况
masterha_check_repl #检查MySQL复制状况 
masterha_manger #启动MHA
masterha_check_status #检测当前MHA运行状态
masterha_master_monitor #检测master是否宕机
masterha_master_switch #控制故障转移（自动或者手动）
masterha_conf_host #添加或删除配置的server信息
2.Node工具包 （通常由masterHA主机直接调用，无需人为执行）

save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs #清除中继日志（不会阻塞SQL线程）

6.2.3 配置MHA 的管理环境

1.生成配置目录和配置文件

[root@mysql-mha ~]# mkdir /etc/mha
[root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mysql-mha MHA-7]# cd mha4mysql-manager-0.58/samples/conf/
[root@mysql-mha conf]# cat masterha_default.cnf app1.cnf > /etc/mha/app1.cnf

因为我们当前只有一套主从，所以我们只需要写一个配置文件即可

rpm包中没有为我们准备配置文件的模板

可以解压源码包后在samples中找到配置文件的模板文件

#生成配置文件

#生成配置文件
[root@mysql-mha ~]# mkdir  /etc/masterha
 [root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
 [root@mysql-mha MHA-7]# cd  mha4mysql-manager-0.58/samples/conf/
 [root@mysql-mha conf]# cat masterha_default.cnf app1.cnf  > 
/etc/masterha/app1.cnf

2.检测配置：

a）检测网络及ssh免密

#进行免密
[root@mysql-mha .ssh]# ssh-keygen 
 
[root@mysql-mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.250.10
 
 
[root@mysql-mha .ssh]# ssh-copy-id -i id_rsa.pub root@172.25.250.20
 
[root@mysql-mha .ssh]# ssh-copy-id -i id_rsa.pub root@172.25.250.30
 
[root@mysql-mha .ssh]# scp id_rsa root@172.25.250.10:/root/.ssh
          
[root@mysql-mha .ssh]# scp id_rsa root@172.25.250.20:/root/.ssh
 
[root@mysql-mha .ssh]# scp id_rsa root@172.25.250.30:/root/.ssh
 
 
#如果scp速度慢
[root@mysql-node1 ~]# vim /etc/ssh/sshd_config 
[root@mysql-node1 ~]# systemctl restart sshd

检测网络及ssh免密
masterha_check_ssh --conf=/etc/mha/app1.cnf

b）检测数据主从复制情况

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

6.2.4 MHA的故障切换

MHA的故障切换过程

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

#在master数据节点还在正常工作情况下   
[root@mysql-mha ~]# masterha_master_switch \ 
--conf=/etc/masterha/app1.cnf \          #指定配置文件
--master_state=alive \                   #指定master节点状态
--new_master_host=172.25.254.20 \        #指定新master节点
--new_master_port=3306 \                 #执行新master节点端口
--orig_master_is_new_slave \             #原始master会变成新的slave
 --running_updates_limit=10000           #切换的超时时间

6.2.4.1node1 中手动切换master到node2

CHANGE MASTER TO MASTER_HOST='172.25.250.20', MASTER_USER='repl',MASTER_PASSWORD='123465', MASTER_AUTO_POSITION=1;
 
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CHANGE MASTER TO MASTER_HOST='172.25.250.20', MASTER_USER='howe',MASTER_PASSWORD='redhat', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
 
#最后检测一主两从
masterha_check_repl --conf=/etc/mha/app1.cnf

6.1.4.2master故障手动切换

#模拟master故障

[root@mysql-node20 mysql]# /etc/init.d/mysqld stop

#在MHA-master中做故障切换

[root@mysql-mha masterha]# masterha_master_switch \

--master_state=dead \

-- conf=/etc/masterha/app1.cnf \

--dead_master_host=192.168.56.12 \

-- dead_master_port=3306 \

--new_master_host=192.168.56.11 \

--new_master_port=3306 \

-- ignore_last_failover \

--ignore_last_failover masterha_master_switch \

--master_state=dead \

--conf=/etc/masterha/app1.cnf \

-- dead_master_host=172.25.254.20 \

--dead_master_port=3306 \

-- new_master_host=172.25.254.10 \

--new_master_port=3306 \

--ignore_last_failover \

--dead_master_ip= is not set. Using 172.25.254.20.

6.1.4.3自动切换

[root@mysql-mha ~]# cd /etc/mha/
[root@mysql-mha mha]# rm -rf app1.failover.complete	#删掉所文件
#监控程序通过指定配置文件监控master状态，当master出问题后自动切换并退出避免重复做故障切换
 
[root@mysql-mha mha]# masterha_manager --conf=/etc/mha/app1.cnf

node3此时已经却换成master为node2

恢复故障节点

[root@mysql-node1 ~]# /etc/init.d/mysqld start 
Starting MySQL. SUCCESS! 
 
#node2
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CHANGE MASTER TO MASTER_HOST='172.25.250.10', MASTER_USER='howe',MASTER_PASSWORD='redhat', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
同时检查node3 是否master回到node1

清除锁文件

[root@mysql-mha masterha]# rm -rf app1.failover.complete manager.log

6.2.3 为MHA添加VIP功能

6.2.3.1准备vip脚本

[root@mysql-node3 ~]# cp master_ip_failover.txt  /usr/local/bin/master_ip_failover
[root@mysql-node3 ~]# cd /usr/local/bin/
[root@mysql-node3 /usr/local/bin]# yum install -y dos2unix
[root@mysql-node3 /usr/local/bin]# dos2unix master_ip_failover 
[root@mysql-node3 /usr/local/bin]# chmod +x master_ip_failover

6.2.3.2更改manager配置文件

vi /etc/mha/app1.cnf
# 添加：
master_ip_failover_script=/usr/local/bin/master_ip_failover

6.2.3.3修改文件内容

[root@mysql-node3 /usr/local/bin]# vim /usr/local/bin/master_ip_failover 
 
my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

6.2.3.4手工生成第一个vip地址

6.2.3.5重启mha

masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

Glowon_CcSsYy

关注

23
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
Mysql集群技术！综合大礼包等你来拿！！

是一种开源的MySQL高可用解决方案，旨在解决MySQL单点故障问题，通过自动化的故障切换功能，确保在主数据库出现故障时，系统能够自动切换到备份数据库，从而保证数据库服务的持续性和数据的一致性。
复制链接

扫一扫