Mysql在服务器中的部署方法

安装MySQL依赖性

root@mysql-node10 ~]# dnf install cmake gcc-c++ openssl-devel \

ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64

下载并解压源码包

使用命令tar zxf mysql-boost-5.7.44.tar.gz进行解压

源码编译安装mysql

[root@mysql-node10 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

-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 -j2 #-j2 表示有几个

核心就跑几个进程

[root@mysql-node10 mysql-5.7.44# make install

部署mysql

创建MySQL数据目录和创建用户并给定权限

生成启动脚本

[root@node10 ~]# cd /usr/local/mysql/support-files/

[root@node10 support-files]# cp mysql.server /etc/init.d/mysqld

修改环境变量

还要进行source ~/.bash_profile命令

生成配置文件

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

会随机生成密码，得记住

使用mysql_secure_installation命令进行修改密码，修改完成后便可以进入使用了

安装完成

mysql的组从复制

MySQL的组从复制（Multi-Source Replication）是一种高级复制技术，它允许一个MySQL服务器（主服务器）从多个MySQL服务器（从服务器）接收数据。这种复制方式可以提高数据的可用性和系统的容错能力，同时也支持读写分离，提升系统的并发处理能力。

组从复制的基本原理是在主服务器上配置多个从服务器的连接，每个从服务器都可以独立地向主服务器发送复制请求。主服务器维护一个二进制日志（Binary Log），记录所有的数据变更操作。从服务器通过IO线程连接到主服务器，并请求二进制日志的内容。主服务器的二进制日志dump线程将日志事件发送给从服务器的IO线程，从服务器的IO线程将这些日志事件写入到本地的中继日志（Relay Log）中。最后，从服务器的SQL线程会读取中继日志中的事件，并在本地数据库上执行这些事件，从而实现数据的同步。

MySQL组从复制的作用

组从复制的主要作用包括：

1. 提高数据可用性：通过从多个服务器复制数据，即使部分服务器出现故障，系统仍然可以继续运行，因为数据已经在其他服务器上有了副本。

2. 读写分离：可以将读操作分散到多个从服务器上，减轻主服务器的负载，提高系统的整体性能。

3. 容错能力：在主服务器发生故障时，可以迅速将服务切换到从服务器，确保服务的连续性和可用性。

4. 数据备份：从服务器可以用于数据库备份目的，备份可以在不影响主服务器性能的情况下进行，而且备份可以是实时的，以确保数据安全4。

5. 支持复杂的复制拓扑：组从复制支持构建复杂的复制拓扑结构，如级联复制，可以有效缓解主服务器的复制压力，同时对数据一致性没有负面影响

主机：172.25.254.10

从机：172.25.254.20

在主机中配置如下

从机配置如下：

主机进入MySQL数据库后操作

配置用户权限

查看master的状态

从机上：

连接主机并启动slave

查看slave连接是否成功

测试：

在主机上建库

从机进行查看

同样有lee库

主机建表测试：

从机查看，同样有这个表，说明实验成功

多台主从

将mysql1进行备份给node3

备份成功

配置多台主从

配置成功

延迟复制：

时间为60s

#在slave端

mysql> STOP SLAVE SQL_THREAD;

mysql> CHANGE MASTER TO MASTER_DELAY=60;

mysql> START SLAVE SQL_THREAD;

mysql> SHOW SLAVE STATUS\G;

在master主机中新加内容

第一时间查询不到结果

60s后

慢查询

在MySQL中，慢查询通常指的是执行时间超过预设阈值的SQL查询。这个阈值可以在MySQL的配置文件中设置，默认情况下可能是2秒。当一个查询的执行时间超过这个阈值时，它会被记录到慢查询日志中。慢查询日志是MySQL提供的一种日志记录机制，用于帮助数据库管理员和开发人员识别和优化性能较差的查询。

MySQL慢查询的作用

慢查询的作用主要包括以下几点：

1. 性能优化：通过分析慢查询日志，可以找出执行时间较长的查询，进而对这些查询进行优化，以提高数据库的整体性能。
2. 资源管理：慢查询可能会占用大量的数据库资源，如CPU和内存。通过监控和优化慢查询，可以更有效地管理这些资源，避免数据库过载。
3. 故障排查：慢查询日志可以帮助定位数据库性能问题，识别潜在的瓶颈，从而进行故障排查和性能调优

mysql> SET GLOBAL slow_query_log=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> SET long_query_time=4;

Query OK, 0 rows affected (0.00 sec)

查看日志

测试：

mysql> select sleep (10);

[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log

/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started

with:

Tcp port: 3306 Unix socket: /data/mysql/mysql.sock

Time Id Command Argument

\# Time: 2024-07-29T17:04:07.612704Z

\# User@Host: root[root] @ localhost [] Id: 8

\# Query_time: 10.000773 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1722272647;

select sleep (10);

并行复制

MySQL并行复制是为了提高主从复制的效率，减少主从延迟而设计的。在传统的主从复制中，从服务器上只有一个SQL线程来重放主服务器上的二进制日志（binlog），这在主服务器负载较高时会导致从服务器的复制延迟。并行复制通过允许从服务器上的多个SQL线程同时重放不同的二进制日志事件来解决这个问题。

MySQL并行复制的作用

并行复制的主要作用是减少主从复制中的延迟，提高数据同步的效率。通过允许从服务器上的多个SQL线程并发处理不同的二进制日志事件，并行复制可以更好地利用从服务器的硬件资源，特别是在主服务器负载较高时，可以显著提升复制性能，确保从服务器能够更快地应用主服务器上的更改，从而保持数据的一致性和实时性。此外，并行复制还有助于提高数据库的高可用性，因为减少了复制延迟，可以更快地在主服务器出现故障时进行故障转移

此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

半同步模式

MySQL的半同步复制（Semisynchronous replication）是一种复制模式，它介于异步复制和全同步复制之间。在半同步复制模式下，主库在执行完客户端提交的事务后，不是立即返回给客户端，而是等待至少一个从库确认接收到了该事务的二进制日志（binlog）事件，并将其写入到中继日志（relay log）中，主库在收到至少一个从库的确认后才会向客户端返回操作完成的响应。这样可以确保主库上的事务至少在一个从库上有了副本，提高了数据的安全性。

半同步复制的主要作用是提高数据的一致性和可靠性。在默认的异步复制模式中，主库在提交事务后不会等待从库的确认，这可能导致主从数据不一致的情况。而半同步复制通过等待至少一个从库的确认，减少了这种风险。即使主库发生故障，已经同步到至少一个从库的事务数据也不会丢失，从而在主从切换时能够保持数据的一致性。

半同步复制还可以在一定程度上减少主库的数据丢失风险，因为它确保了在主库崩溃前，至少有一部分数据已经安全地复制到了从库。此外，半同步复制提供了一个比异步复制更强的数据一致性保证，同时相比全同步复制，它对性能的影响较小，因为它只要求至少一个从库确认接收，而不是所有从库都完成事务的复制。

gtid模式

主机

slave1：

slave2：

slave1:

[root@mysql-node2 ~]# mysql -p

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',

MASTER_PASSWORD='ljx', MASTER_AUTO_POSITION=1;

mysql> start slave;

mysql> show slave status\G;

slave2:

[root@mysql-node3 ~]# mysql -p

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',

MASTER_PASSWORD='ljx', MASTER_AUTO_POSITION=1;

mysql> start slave;

mysql> show slave status\G;

启用半同步模式

master中：

安装半同步插件和查看插件状态

#打开半同步功能和查看半同步功能状态

在slave端开启半同步功能

[root@mysql-node2 ~]# vim /etc/my.cnf

slave1和2端查看服务状态(slave1和slave2操作相同)

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)

测试：在master中写入内容，在slave1和2中查看是否生成

slave1:

slave2：

模拟故障：停掉slave1和slave2，master无法生成表内容

开启后马上成功生成内容

实验完成

实现mysql组复制

MySQL组复制（MySQL Group Replication，简称MGR）是MySQL官方提供的一种分布式复制解决方案，它建立在现有的MySQL复制框架之上，并基于Paxos协议实现。组复制的核心在于通过组通讯层和XCom层（Paxos层）来确保集群中的数据一致性和高可用性。

在组复制中，每个节点都可以独立执行事务，但在事务提交之前，会通过组通讯层将事务的变更广播给集群中的其他节点。这样可以确保所有节点上的事务以相同的顺序执行，从而达到数据的一致性。组通讯层还负责故障检测和集群成员的管理。XCom层基于Paxos协议，确保了集群中各节点收到数据的顺序一致，并且在多数派节点可用的情况下，数据不会丢失。

MySQL组复制的作用

MySQL组复制的主要作用包括：

1. 数据一致性：通过Paxos协议确保集群中所有节点的数据一致性，即使在网络分区或节点故障的情况下也能保持数据的同步。
2. 高可用性：当集群中的主节点发生故障时，组复制能够自动进行主备切换，确保服务的连续性。
3. 自动故障检测和恢复：组复制能够自动检测节点故障，并在故障恢复后，通过复制来同步数据，恢复节点的正常运行。
4. 支持多主和单主模式：组复制支持多主模式，其中所有节点都可以读写，适用于读写负载均衡；也支持单主模式，其中只有一个节点可以写入，适用于需要严格控制写操作的场景。
5. 扩展性：新节点可以自动加入集群，并从现有节点同步数据，实现集群的水平扩展。

主设备配置

从设备和主设备一样，只需改IP即可。

在MySQL1中：

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'ljx';

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='ljx' 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;

从设备中，配置相同

mysql> SET SQL_LOG_BIN=0;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'ljx';

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='ljx' 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;

在mysql2中配置并查看

在MySQL3中查看

测试：

在MySQL1中插入表并写入内容

在MySQL2和MySQL3中查看

在MySQL2中写入内容

MySQL1和3同样能查看

在MySQL3中写入内容

在MySQL1和2中查看

mysql-router（mysql路由）

MySQL Router是一个轻量级中间件，它位于应用程序和MySQL服务器之间，提供透明的数据库流量路由。MySQL Router的核心原理包括：

1. 流量转发层：MySQL Router作为一个流量转发层，不直接修改或检查数据包，而是根据配置将应用程序的读写请求转发给后端的MySQL服务器。
2. 负载均衡：当后端有多个MySQL服务器时，MySQL Router可以对读写请求进行负载均衡，确保数据库集群的性能和可用性。
3. 故障转移：如果某个MySQL服务器失效，MySQL Router能够自动将其从活动列表中移除，并在服务器恢复后重新加入，从而实现故障转移。
4. 集群拓扑感知：对于构建为InnoDB Cluster模式的MySQL服务器，MySQL Router能够基于metaCache机制感知集群拓扑的变更，如主从切换和从库增减，并据此自动调整路由策略。

MySQL Router的作用

MySQL Router的主要作用包括：

1. 读写分离：通过将读写请求分发到不同的服务器，提高数据库操作的效率。
2. 高可用性：自动处理服务器故障，确保应用程序的连续运行。
3. 负载均衡：在多个服务器之间分配负载，优化资源使用。
4. 透明性：对前端应用程序来说，MySQL Router是透明的，不需要修改应用程序代码即可利用其功能。
5. 集群迁移：支持数据库集群的迁移，应用程序无需修改配置即可连接到新的数据库集群。

mysql负载均衡

首先下载mysql-router这个包

配置mysql-router

vim /etc/mysqlrouter/mysqlrouter.conf

在mysql20和30上添加用户

测试：

mysql -ulee -pljx -h 172.25.254.10 -P7001

查看调度效果

MHA部署实施

MySQL MHA（Master High Availability）是一个开源的高可用性解决方案，专注于为MySQL主从复制架构提供自动化的主故障转移。MHA能够在主节点故障时，自动选择一个合适的从节点提升为主节点，并确保数据的一致性。它支持一主多从的架构，并且能够在0-30秒内完成故障切换，从而最大限度地减少服务中断时间1234。MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点），其中管理节点负责监控集群状态并在主节点故障时执行故障转移，而数据节点安装在每台MySQL服务器上，负责执行具体的故障转移任务

在主机中

[root@mysql-node10 ~]# vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

server-id=1

log-bin=mysql-bin

gtid_mode=ON

log_slave_updates=ON

enforce-gtid-consistency=ON

symbolic-links=0

[root@mysql-node10 ~]# mysql -uroot -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-node20 & 30 ~]# vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/data/mysql/mysql.sock

server-id=1

log-bin=mysql-bin

gtid_mode=ON

log_slave_updates=ON

enforce-gtid-consistency=ON

symbolic-links=0

在slave1和slave2中

[root@mysql-node20 & 30 ~]# 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%';

[root@mysql-mha ~]# unzip MHA-7.zip

[root@mysql-mha MHA-7]# yum install *.rpm -y

[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

在master 中

[root@mysql-node10 ~]# yum install /mnt/mha4mysql-node-0.58-

0.el7.centos.noarch.rpm -y

[root@mysql-node20 ~]# yum install /mnt/mha4mysql-node-0.58-

0.el7.centos.noarch.rpm -y

[root@mysql-node30 ~]# yum install /mnt/mha4mysql-node-0.58-

0.el7.centos.noarch.rpm -y

在mha中

#生成配置文件

[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

# 编辑配置文件

[root@mysql-mha ~]# vim /etc/masterha/app1.cnf

[server default]

user=root #mysql 管理员用户，因为需要做自动化配置

password=lee #mysql 密码

ssh_user=root #ssh 远程登陆用户

repl_user=repl #mysql 主从复制中负责认证的用户

repl_password=lee #mysql 主从复制中负责认证的用户密码

master_binlog_dir= /data/mysql # 二进制日志目录

remote_workdir=/tmp # 远程工作目录

# 此参数使为了提供冗余检测，方式是 mha 主机网络自身的问题无法连接数据库节点，应为集群之外的主机

secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s

172.25.254.11

ping_interval=3 # 每隔 3 秒检测一次

# 发生故障后调用的脚本，用来迁移 vip

# master_ip_failover_script= /script/masterha/master_ip_failover

# 电源管理脚本 # shutdown_script= /script/masterha/power_manager

# 当发生故障后用此脚本发邮件或者告警通知

# report_script= /script/masterha/send_report

# 在线切换时调用的 vip 迁移脚本，手动

# master_ip_online_change_script= /script/masterha/master_ip_online_change

manager_workdir=/etc/masterha #mha 工作目录

manager_log=/var/etc/masterha/manager.log #mha 日志

[server1]

hostname=172.25.254.10

candidate_master=1 # 可能作为 master 的主机

check_repl_delay=0

[server2]

hostname=172.25.254.20

candidate_master=1 # 可能作为 master 的主机

check_repl_delay=0

[server3]

hostname=172.25.254.30

no_master=1 # 不会作为 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