MySQL——运维篇-EW帮帮网

Linux下安装mysql

1.检查mysql是否安装过

rpm -qa | grep mysql
#检查服务是否已经启动
ps -ef | grep mysql

2.下载mysql压缩包

MySQL :: MySQL Community Downloads

#压缩包类型是tar.xz，解压缩的方式是-xvf
tar -xvf mysql-8.0.38-linux-glibc2.12-x86_64.tar.xz
#重命名为mysql方便管理
mv mysql-8.0.38-linux-glibc2.12-x86_64.tar.xz mysql
#移动到/usr/local统一管理
mv mysql /usr/local

3.创建数据目录

#到mysql安装目录下
cd /user/local/mysql
#创建数据目录，否则启动mysql服务系统会默认把数据目录放在/var/lib/mysql下，但这样不方便管理
mkdir data

4.创建用户和用户组并授权

工作中采用多用户登录页可以保证安全性
授权：数据库和文件系统交互，需要给MySQL授权文件夹

#用户组
groupadd mysql
#用户
useradd -g mysql mysql
#授权
chown -R mysql.mysql /usr/local/mysql/

5.修改my.cnf配置文件

如果是centos镜像，本身就带有my.cnf配置文件，直接修改etc下的my.cnf即可

vim /etc/my.cnf

[client]
    socket = /usr/local/mysql/mysql.sock
    default-character-set=utf8
[mysqld]
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    socket = /usr/local/mysql/mysql.sock
    character-set-server=utf8
    port = 3306
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

6.初始化mysql环境

初始化完成系统会随机生成一个初始密码，记得要将这个密码保存好，后面登录mysql后在改成自己的密码
如果忘记了初始密码则需要在my.cnf中加上skip-grant-tables设置无密码登录再修改密码

#mysql安装假设为/usr/local/mysql
/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize

7.设置mysql自启动

#将mysql.server启动文件复制到/etc/init.d目录，目的是让mysql服务随系统自启动
cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
#赋予mysql服务自启动权限
chmod +x /etc/rc.d/init.d/mysqld
#开启mysql自启动
chkconfig --add mysqld
#检查mysql服务是已设置自启动
chkconfig --list mysqld

8.配置环境变量

vim /etc/profile

export MYSQL_HOME=/usr/local/mysql
export CLASSPATH=$MYSQL_HOME/lib
export PATH=$PATH:$MYSQL_HOME/bin

#刷新环境变量
source /etc/profile

9.管理mysql服务

#启动mysql服务，这里已经配置了环境变量，在任何目录下都可以启动
systemctl start mysqld
#查看mysql服务状态
systemctl status mysqld
#停止mysql服务
systemctl stop mysqld
#重启mysql服务
systemctl restart mysqld

10.登录mysql服务并修改密码

#-p表示使用密码登录
mysql -uroot -p -P3306
#显示输入密码，输入前述的初始密码即可
#出现 mysql> 标识时说明登录成功
#将登录密码改成123456
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

11.设置远程登录

mysql默认root登录只能是localhost，除了本机ip外其他的ip都拒绝访问，为了实现其他的ip也可以访问就需要设置远程登录

#登录mysql服务后执行
mysql> use mysql;
#将root用户登录的ip设为%，即任何ip均可
mysql> update user set host='%' where user='root'
#赋权
Grant all privileges on *.* to 'root'@'%';
#刷新mysql权限
mysql> flush privileges;

12.开放端口

#mysql默认端口是3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重启防火墙
firewall-cmd --reload
#查看开放的端口号
firewall-cmd --list-all

Windows下安装mysql

1.下载mysql压缩包并解压到指定位置

MySQL :: Begin Your Download

2.在解压路径下新建my.ini配置文件

[client]    
#客户端设置，即客户端默认的连接参数
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
 
#默认编码
default-character-set = utf8mb4
 
[mysql]   
#客户端设置
# 设置mysql客户端默认字符集
default-character-set = utf8mb4
 
[mysqld]    
#服务端基本设置
# 默认连接端口
port=3306
 
# MySQL安装根目录的路径
basedir=D:\Server\mysql-8.0\mysql-8.0.30-winx64
 
# MySQL服务器数据目录的路径
datadir=D:\Server\mysql-8.0\mysql-8.0.30-winx64\data
 
# 允许最大连接数
max_connections=200
 
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
 
#服务端默认编码
character_set_server = utf8mb4
 
#在创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
 
# 配置时区
default-time_zone='+8:00'

3.初始化mysql环境

在解压的mysql\bin目录下打开cmd（管理员身份）
这里会生成一个系统随机密码，是mysql数据库的初始密码，记得保存下来

mysqld --initialize --console
mysqld --install "MySQL3306" --defaults-file="D:\Server\mysql-8.0\mysql-8.0.30-winx64\my.ini"

4.开启mysql服务

net start mysql

5.登录验证

这里需要登录密码，就是之前随机生成的系统密码

#-p表示使用密码登录
mysql -uroot -p -P3306

6.修改root密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

7.开启远程访问

#登录mysql服务后执行
mysql> use mysql;
#将root用户登录的ip设为%，即任何ip均可
mysql> update user set host='%' where user='root';
#赋权
Grant all privileges on *.* to 'root'@'%';
#刷新mysql权限
mysql> flush privileges;

数据传输

数据导入

MySQL提供的load指令插入数据，通过load指令可以一次性将本地数据文件全部加载到数据库之中

#客户端连接MySQL服务器时，加上参数-local-infile
mysql -local-infile -u root -p

#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

#执行load指令将准备好的数据，加载到表结构中
load data local infile '本地文件路径' into table '表名' fields terminated by '字段对应数据间的分隔符' lines terminated by '每一行数据结束的分隔符';

#例如将/root/sql1.log的数据插入到tb_user表中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by ';';

cp全量备份

原理：将数据库表文件复制一份
缺点：必须先对数据库加全局数据库锁

cp 表路径名.ibd 目标路径

mysqldump备份

mysqldump是mysql提供了数据备份工具
缺点：数据量大时效率较低

#选择要加锁的数据库
use 数据库名;

#对选择的数据库加全局锁
flush tables with read lock;

#备份数据库，注意mysqldump是mysql的一个工具，不是mysql命令，需要在命令行中执行，不能在MySQL内执行
mysqldump -h 数据库所在ip -uroot -p密码 数据库名 > sql文件路径
#MySQL8不能直接明文带密码，需要手动再次输入密码
mysqldump -h 217.0.0.1 -uroot -p mybatis > D:/mybatis.sql;
#此时系统提示输入密码，校对完成后即可备份到指定路径下

#释放锁
unlock tables;

Xtrabackup备份

从 Percona 官网下载对应系统的二进制包，解压后配置环境变量

基本全量备份

# 备份到本地目录（需 MySQL 用户有 REPLICATION CLIENT 权限）
xtrabackup --backup --user=root --password=123456 --target-dir=/backup/full_backup
 
# 关键参数说明：
# --backup：执行备份操作
# --user/--password：MySQL 账号密码
# --target-dir：备份输出目录
# --host：指定 MySQL 主机（默认 localhost）
# --port：指定 MySQL 端口（默认 3306）

压缩备份（节省空间）

xtrabackup --backup --user=root --password=123456 \
  --target-dir=/backup/full_backup --compress --compress-threads=4
 
# 解压时使用：
xtrabackup --decompress /backup/full_backup

远程备份（备份到另一台服务器）

# 在备份服务器上执行（通过 SSH）
xtrabackup --backup --user=root --password=123456 \
  --target-dir=/backup/full_backup --stream=xbstream | ssh user@remote_server "cat > /remote_backup/full.xbstream"
 
# 远程解压：
ssh user@remote_server "xbstream -x < /remote_backup/full.xbstream -C /remote_backup/full_backup"

增量备份：只处理上一次全量或增量备份的变更部分

# 1. 先完成一次全量备份
xtrabackup --backup --user=root --password=123456 --target-dir=/backup/full_backup

# 2. 执行增量备份（基于全量备份）
xtrabackup --backup --user=root --password=123456 \
  --target-dir=/backup/inc_backup1 \
  --incremental-basedir=/backup/full_backup

# 3. 第二次增量备份（基于第一次增量）
xtrabackup --backup --user=root --password=123456 \
  --target-dir=/backup/inc_backup2 \
  --incremental-basedir=/backup/inc_backup1
  
# 合并第一次增量到全量
xtrabackup --prepare --apply-log-only --target-dir=/backup/full_backup \
  --incremental-dir=/backup/inc_backup1

# 合并第二次增量
xtrabackup --prepare --apply-log-only --target-dir=/backup/full_backup \
  --incremental-dir=/backup/inc_backup2

# 最终准备（模拟崩溃恢复）
xtrabackup --prepare --target-dir=/backup/full_backup

分库分表（数据分片）

目的：单表行数超过 500 万行或者单表容量超过 2GB，推荐进行分库分表
分库分表目的是提高并发性能，如果单机部署没有实际意义
1. 单库分表时，所有表仍共享同一数据库实例的CPU、内存和磁盘
2. 多库分表需要分布式事务支持，而数据库本身并没有此功能，需要中间件的配合（如Mycat）

垂直拆分（按列拆分）

定义：按业务字段拆分，将大表拆分为多个小表（如用户表拆分为 user_base、user_ext）

适用场景：表字段过多，存在大量稀疏字段（如用户表有 100 列，但常用字段仅 20 列）

-- 原表
CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  address TEXT,  -- 不常用字段
  login_log TEXT -- 不常用字段
);
 
-- 垂直拆分后
CREATE TABLE user_base (id INT PRIMARY KEY, name VARCHAR(50), age INT);
CREATE TABLE user_ext (id INT PRIMARY KEY, address TEXT, login_log TEXT);

水平拆分（按行拆分）

定义：按行数据拆分，将单表数据分布到多个同构表中（如 order_0、order_1）
适用场景：单表数据量过大（如订单表每天新增 100 万条）
拆分方式
1. 哈希取模
```
-- 示例：按用户 ID 哈希分片
SELECT * FROM order_${user_id % 4} WHERE user_id = 1001;
```
2. 范围分片：按时间、ID 范围拆分（如 order_202301、order_202302）
3. 一致性哈希：减少分片扩容时的数据迁移量

主从复制

读写分离

目的：多节点部署数据库，一部分数据库实现读操作，另一部分实现写操作，提高了高并发环境下数据库的稳定性
主从复制也是分布式部署，但关注的是稳定性而非性能

同步延迟问题

同步原理：MySQL通过分享binlog二进制日志，其他数据库“回放”先前操作实现数据一致
延迟原因：从库回放binlog操作时耗时过长或者出现异常
1. 数据吞吐过大
2. 网络传输延迟

解决方案

硬件优化

硬盘升级

网络升级

业务层面优化

避免长事务，拆分为小批次提交

业务核心数据直接使用主库

架构层面优化

增加多级从库，缓解从库压力

增加数据库中间件，优化从库路由，高延迟时从库降级

增加缓存中间件
MySQL调参
主库配置
sync_binlog = 1          # 每次事务提交都刷盘（确保数据安全）
binlog_group_commit_sync_delay = 100  # 允许短暂延迟提交，合并事务
binlog_group_commit_sync_no_delay_count = 10  # 批量提交阈值
gtid_mode = ON #使用 GTID 复制
enforce_gtid_consistency = ON #使用 GTID 复制
从库配置
slave_parallel_workers = 8  # 启用并行复制（MySQL 5.7+）
slave_parallel_type = LOGICAL_CLOCK  # 基于事务时间并行（推荐）
slave_preserve_commit_order = 1  # 保证事务顺序（避免主键冲突）
gtid_mode = ON #使用 GTID 复制
enforce_gtid_consistency = ON #使用 GTID 复制
实时监控延迟
SHOW SLAVE STATUS\G：需手动执行查看，无法自动实时告警
#关键字段
Slave_IO_Running: Yes          # I/O线程是否运行
Slave_SQL_Running: Yes         # SQL线程是否运行
Seconds_Behind_Master: 3        # 延迟秒数（0表示无延迟）
Read_Master_Log_Pos: 123456    # 主库Binlog位置
Exec_Master_Log_Pos: 123400    # 从库已执行位置
Percona Monitoring and Management (PMM)：最常用三方工具，可以延迟告警

开始部署

如果使用物理机中的多虚拟机，需要保证虚拟机的uuid不同，可以将auto.cnf删掉再重启mysql服务，这样就会自动生成auto.cnf

[root@centos ~]# find / -iname "auto.cnf"
/usr/local/mysql/data/auto.cnf
[root@centos ~]# rm -f /usr/local/mysql/data/auto.cnf

主库配置

修改主库配置文件

vim /etc/my.cnf
#windows下则是修改my.ini配置文件

[mysqld]
    #开启log-bin二进制日志，主库必须开启，因为要记录状态给从库同步
    #windows环境设置log-bin=mysql-bin
    log-bin=/usr/local/mysql/mysql-bin
    #配置唯一的服务器ID
    server-id=1
    #下面这两个不是必须要配置
    #主要是为了使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性
    innodb_flush_log_at_trx_commit = 1
    sync_binlog = 1

#假设binlog目录为/var/log/mysql
chown mysql:mysql /var/log/mysql
#windows下创建二进制日志目录即可

重启数据库，并查看以下配置是否生效

#sysyemctl命令包含了service命令
systemctl restart mysql
#进入mysql
mysql -uroot -p
#查看本数据库id，要保证所有数据库id不能重复
mysql> show variables like 'server_id';
#主库log_bin必须开状态（on）
mysql> show variables like 'log_bin';
#skip_networking默认是OFF关闭状态，启用后主从将无法通信
#skip_networking必须是关闭状态（off）
mysql> show variables like '%skip_networking%';

在主库上建立给从库使用的账号

#创建给从库用户同步数据的账号，名称是slave，密码是123456，从库的ip任意（%）
create user slave@'%' identified by '123456';
#可能会出现报错root权限不够不能对slave赋权，重新给root刷新权限即可
GRANT ALL ON *.* TO 'root'@'%';
#给slave账户授权，ALL PRIVILEGES表示授权所有表
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%'WITH GRANT OPTION;
#刷新权限
flush privileges;

查看主库的二进制日志

show master status;

从库设置

测试在主库上创建的同步账号是否可用

#可以登录即成功
mysql -u slave -p'123456' -h 主库ip

修改从库配置文件

vim /etc/my.cnf
#windows下则是my.ini

#添加下面的配置
[mysqld]
    #开启log-bin二进制日志，从库不是必须开启
    #log-bin=/usr/local/mysql/mysql-bin
    #配置唯一的服务器ID
    server-id=2
    #下面这两个不是必须要配置
    #主要是为了使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性
    innodb_flush_log_at_trx_commit = 1
    sync_binlog = 1

重启数据库，并查看以下配置是否生效

#sysyemctl命令包含了service命令
systemctl restart mysql
#进入mysql
mysql -uroot -p
#查看本数据库id，要保证所有数据库id不能重复
mysql> show variables like 'server_id';
#主库log_bin必须开状态（on）
mysql> show variables like 'log_bin';
#skip_networking默认是OFF关闭状态，启用后主从将无法通信
#skip_networking必须是关闭状态（off）
mysql> show variables like '%skip_networking%';

配置从库同步主库

#mysql8改变了密码规则，需要先设置密码规则
CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
#通过前述创建的账号连接到主库
CHANGE MASTER TO 
MASTER_HOST='主库ip',
MASTER_USER='同步账号名称',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='前述查到的主库状态的file属性',
MASTER_LOG_POS=前述查到的主库状态的position属性;

启动从库复制功能

start slave; 
#停止同步
stop slave;
#清除复制参数
reset slave all;

检查数据同步是否成功开启

#当结果中Slave_IO_Running和Slave_SQL_Running都为yes时说明成功
show slave status;

分布式事务

目的：分布式部署环境下（例如数据分片），还使用传统事务无法保证ACID，需要使用分布式事务
CAP定理：在分布式系统中，最多只能同时满足其中两项
1. 一致性：所有节点在同一时间数据一致
2. 可用性：每个请求都能收到响应
3. 分区容错性：系统在网络分区时仍能运行

常用解决方案

方案	一致性	性能	实现复杂度	适用场景
2/3PC	强	低	中	金融交易等强一致性场景
TCC	最终	高	高	高并发支付、订单系统
SAGA	最终	中	中	长时间业务流程
事务消息	最终	高	低	异步解耦的最终一致性场景
Seata AT	最终	中	中	微服务架构下的常见场景

2/3PC

原理：两/三阶段提交
1. 准备阶段
  
  协调者询问所有参与者是否可以提交
  
  参与者执行事务但不提交，返回准备结果
2. 提交阶段
  
  如果所有参与者都准备成功，协调者发送提交命令
  
  如果有参与者准备失败，协调者发送回滚命令
优点
1. 强一致性保证
2. 实现相对简单
缺点
1. 同步阻塞（参与者需要等待协调者指令）
2. 单点问题（协调者故障）
3. 数据不一致风险（第二阶段失败时）
改进点：
1. 增加CanCommit阶段，先询问是否可以执行事务
2. 准备阶段后增加超时机制
3. 提交阶段分为PreCommit和DoCommit

TCC

原理
1. Try：尝试执行，完成所有业务检查，预留必要资源
2. Confirm：确认执行，真正执行业务，不作任何检查，只使用Try阶段预留的资源
3. Cancel：取消执行，释放Try阶段预留的资源
优点
1. 性能较好（最终一致性）
2. 适用于高并发场景
3. 业务可控制性强
缺点
1. 对业务侵入性强
2. 需要处理各种异常情况

示例（转账业务）：

// Try阶段
public boolean tryTransfer(Account from, Account to, BigDecimal amount) {
    if (from.getBalance().compareTo(amount) < 0) {
        return false; // 余额不足
    }
    // 冻结发送方资金
    from.setFrozenBalance(from.getFrozenBalance().add(amount));
    // 预留接收方资金（如果需要）
    to.setReservedBalance(to.getReservedBalance().add(amount));
    return true;
}
 
// Confirm阶段
public void confirmTransfer(Account from, Account to, BigDecimal amount) {
    // 扣除发送方冻结资金
    from.setBalance(from.getBalance().subtract(amount));
    from.setFrozenBalance(from.getFrozenBalance().subtract(amount));
    // 增加接收方可用资金
    to.setBalance(to.getBalance().add(amount));
    to.setReservedBalance(to.getReservedBalance().subtract(amount));
}
 
// Cancel阶段
public void cancelTransfer(Account from, Account to, BigDecimal amount) {
    // 解冻发送方资金
    from.setFrozenBalance(from.getFrozenBalance().subtract(amount));
    // 释放接收方预留资金
    to.setReservedBalance(to.getReservedBalance().subtract(amount));
}

SAGA

原理
1. 将长事务拆分为多个本地事务
2. 每个本地事务有对应的补偿事务
3. 如果某个步骤失败，按顺序执行补偿事务回滚
优点
1. 避免长时间锁定资源
2. 适合长时间运行的事务
3. 实现相对简单
缺点
1. 补偿操作可能复杂
2. 事务历史记录维护成本高

本地消息表(事务消息)

原理
1. 将分布式事务拆分为本地事务和消息发送
2. 使用消息表记录待发送消息
3. 通过定时任务或事件驱动发送消息到消息队列
4. 消费者处理消息并反馈结果
优点
1. 实现简单
2. 保证最终一致性
3. 对业务侵入性较小
缺点
1. 需要处理重复消息
2. 消息可能丢失（需要重试机制）

Seata

Seata是阿里巴巴开源的分布式事务解决方案

原理

一阶段：

拦截SQL解析，生成undo log

执行业务SQL并提交本地事务

注册全局事务和分支事务

二阶段：

提交：直接删除undo log

回滚：根据undo log执行反向SQL

优点
1. 对业务无侵入
2. 性能较好
3. 支持多种数据源
缺点
1. 依赖全局锁（高并发下有性能影响）
2. 需要维护undo log表

MySQL——运维篇

Linux下安装mysql

1.检查mysql是否安装过

2.下载mysql压缩包

3.创建数据目录

4.创建用户和用户组并授权

5.修改my.cnf配置文件

6.初始化mysql环境

7.设置mysql自启动

8.配置环境变量

9.管理mysql服务

10.登录mysql服务并修改密码

11.设置远程登录

12.开放端口

Windows下安装mysql

1.下载mysql压缩包并解压到指定位置

2.在解压路径下新建my.ini配置文件

3.初始化mysql环境

4.开启mysql服务

5.登录验证

6.修改root密码

7.开启远程访问

数据传输

数据导入

cp全量备份

mysqldump备份

Xtrabackup备份

分库分表（数据分片）

垂直拆分（按列拆分）

水平拆分（按行拆分）

主从复制

读写分离

同步延迟问题

开始部署

主库配置

从库设置

分布式事务

2/3PC

TCC

SAGA

本地消息表(事务消息)

Seata

数据库调参

网站公告

今日签到

热门文章

最新发布