在互联网后端架构中，MySQL 是使用最广泛的关系型数据库。无论是开发、测试还是运维岗位，掌握 MySQL 基础运维、安装部署、性能调优与备份恢复都是必备核心能力。本文结合多年实战经验，整理一套可直接用于生产环境的 MySQL 运维实战手册，帮助大家快速上手、避坑、提升稳定性与性能。

一、 MySQL发行版的选择

1.1 MySQL 官方发行版

MySQL 是目前业界使用最广泛的关系型数据库，具备四大核心优势：

• 简单易用：学习与使用门槛低，具备基础 IT 背景即可参照文档完成安装、部署与日常使用。
• 开源免费：开源特性使其普及度高，可免费使用，降低企业成本。
• 多存储引擎支持：内置 MyISAM、InnoDB、MERGE、MEMORY、BDB、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE 等多种存储引擎，可按业务场景灵活选用。
• 原生高可用能力：自带 Replication 主从复制功能，可实现数据实时备份，支撑基础高可用架构。

1.2 MySQL 核心存储引擎：MyISAM 与 InnoDB

MySQL 4.x/5.x 早期版本默认存储引擎为MyISAM；从MySQL 5.5开始，默认存储引擎正式切换为InnoDB。

1.2.1 核心区别对比

特性	InnoDB	MyISAM
事务支持	支持	不支持
锁机制	行级锁，并发高	表级锁，并发低
外键	支持	不支持
缓存	缓存数据 + 索引	只缓存索引
适用场景	高频写、金融 / 订单等高安全业务	大量查询、只读业务

1.2.2 引擎选型建议

• 必须使用事务、追求数据安全与高并发 → 必选 InnoDB
• 以查询为主、需要全文索引、对写入性能要求低 → 可选 MyISAM

1.3 Percona Server 分支

Percona Server 由专业 MySQL 服务厂商 Percona 研发，具备以下特点：

• 完全兼容官方 MySQL，无需修改代码即可平滑替换。
• 搭载高性能XtraDB存储引擎，性能优于官方 InnoDB。
• 提供PXC集群高可用方案，配套 percona-toolkit 等运维工具集。
• 是最贴近官方 MySQL 企业版的开源分支。

1.4 MariaDB 分支

MariaDB 由 MySQL 创始人主导开发，定位为 MySQL 无缝替代品：

• 完全兼容 MySQL 的 API 与命令行，迁移成本极低。
• 原生支持 MyISAM、InnoDB 等标准引擎；10.0.9 版本后，默认使用 XtraDB（代号 Aria）替代官方 InnoDB。

1.5 生产环境发行版选型结论

线上业务优先顺序为：Percona Server ＞ 官方 MySQL ＞ MariaDB

二、MySQL 三种安装方式

1. 二进制安装（推荐自定义路径）

下载并解压mysql

cd /usr/local
xz -d mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz
tar xvf mysql-8.0.25-linux-glibc2.12-x86_64.tar
mv mysql-8.0.25-linux-glibc2.12-x86_64 mysql

创建目录准备初始化

mkdir -p /usr/local/mysql/{data,etc,logs}
useradd mysql

编写my.conf

vim /usr/local/mysql/etc/my.cnf

配置如下：

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

log-error=/usr/local/mysql/logs/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid

进行初始化

cd /usr/local/mysql
bin/mysqld --initialize --user=mysql \
  --basedir=/usr/local/mysql \
  --datadir=/usr/local/mysql/data

授权并启动

chown -R mysql:mysql /usr/local/mysql
cp support-files/mysql.server /etc/init.d/
/etc/init.d/mysql.server start

2. Yum 安装（简单快捷）

yum install -y mysql-server mysql mysql-common mysql-libs

安装完成后，可直接启动mysql服务，会自动初始化系统库以及启动相关服务，mysql启动完成后，会生成root用户的默认密码，可从mysqld.log日志文件中获取临时的密码。

systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log

此密码可用于临时登录，登录后，需要马上修改为自己的新密码，执行如下SQL命令：

mysql -uroot -p
alter user 'root'@'localhost' identified by 'root@mySQL123';

通过这个命令就修改了root用户的密码。

3. Docker 安装（快速体验）

配置阿里云安装源

yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
yum makecache fast #刷新缓存

接着安装docker

yum install -y docker-ce
docker version #查看docker版本检查是否安装成功
systemctl enable docker && systemctl start docker

然后拉去mysql镜像

docker pull swr.cn-north-1.myhuaweicloud.com/iivey/mysql:8.0.23

启动容器

docker run -itd -p 3306:3306 --name mysql8 \
  --restart unless-stopped \
  -v /dockerdata/mysql/db:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -e MYSQL_DATABASE=iivey \
  -e MYSQL_USER=iivey \
  -e MYSQL_PASSWORD=mysql123 \
  swr.cn-north-1.myhuaweicloud.com/iivey/mysql:8.0.23 \
  --default-authentication-plugin=mysql_native_password \
  --character-set-server=utf8

上面docker run命令中， /dockerdata/mysql/db路径是宿主机的路径，需要先创建好。

三、 Mysql常用基础命令操作

3.1 MySQL 连接与退出

常用客户端工具：Navicat、phpMyAdmin、MySQL-Front

命令格式：mysql -h 主机地址 -u用户名 -p用户密码

（1）连接本地 MySQL

进入 MySQL 安装目录 bin 下执行：

./mysql -u root -p

回车后输入密码，密码前不能加空格。

（2）连接远程 MySQL

示例：远程 IP 110.110.110.110，用户 root，密码 abcd123

mysql -h110.110.110.110 -uroot -pabcd123

（3）退出 MySQL

exit;

3.2 MySQL 密码修改

命令格式：mysqladmin -u用户名 -p旧密码 password 新密码;

（1）初始无密码修改（直接设置新密码）

首先在Mysql安装目录下面的bin目录

mysqladmin -u root password ab12

注：因为开始时root没有密码，所以-p旧密码一项就可以省略了

（2）已有密码修改（旧密码 ab12 → 新密码 abc345）

再将root的密码改为abc345

mysqladmin -u root -p ab12 password abc345

3.3 用户创建与权限授权

必须在 MySQL 命令行内执行，结尾带分号

命令格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

（1）创建任意 IP 可登录的高权限用户（谨慎使用）

增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限

grant select,insert,update,delete on *.* to test1@"%" identified by "abc";

但这种权限增加的用户是十分危险的，如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录这台mysql数据库，并可对数据进行任意操作，解决办法是设置登录权限

（2）创建本地限制、单库权限用户（安全推荐）

增加一个用户test2密码为abc,让它只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库

grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";

（3）无密码用户

如果你不想test2有密码，可以再执行下面这个命令将密码取消掉。

grant select,insert,update,delete on mydb.* to test2@localhost identified by "";

（4）指定 IP 访问、授予全权限

 如果想给一个用户test2授予访问mydb数据库的所有权限，并且仅允许test2在192.168.11.121这个客户端ip登录访问，可执行如下命令

grant all on mydb.* to test2@192.168.11.121 identified by "abc";

3.4 数据库基础操作

（1）创建数据库

命令：create  database  <数据库名>；

create database abc;

创建库并分配用户（常用规范）

CREATE DATABASE 库名;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 库名.* TO 库名@localhost IDENTIFIED BY '密码';

（2）查看所有数据库

命令：show databases （注意：最后有个s）；

show databases;

（3）删除数据库

命令：drop database <数据库名>；

例如：删除名为 iivey的数据库

drop database iivey;

安全删除（不存在不报错）

drop database if exists 库名;

（4）进入 / 使用数据库

命令： use <数据库名>；

use iivey;

use语句可以通告MySQL把iivey数据库作为默认（当前）数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句

3.5 数据表常用操作

（1）创建表

命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

create table MyClass(
id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default '0',
degree double(16,2)
);

（2）删除表

命令：drop table <表名>；

例如：删除表名为 MyClass 的表

drop table MyClass;

（3）插入数据

命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]；

例如：在表MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为90.45, 编号为2 的名为Joan 的成绩为88.99， 编号为3的名为Wang的成绩为99.5

insert into MyClass values(1,'Tom',90.45),(2,'Joan',88.99),(3,'Wang',99.5);

（4）查询数据

命令： select <字段1，字段2，...> from < 表名 > where < 表达式 >；

例如：查看表 MyClass 中所有数据

select * from MyClass;

例如：查看表 MyClass 中前2行数据

select * from MyClass order by id limit 0,2;

（5）删除数据

 命令：delete from 表名 where 表达式；

例如：删除表 MyClass中编号为1的记录

delete from MyClass where id=1;

（6）修改数据

语法：update 表名 set 字段=新值,… where 条件；

update MyClass set name='Mary' where id=1;

（7）增加字段

命令：alter table 表名 add 字段  类型 其他；

例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0

alter table MyClass add passtest int(4) default '0';

（8）修改表名

命令：rename table 原表名 to 新表名;

例如：在表MyClass名字更改为YouClass

rename table MyClass to YouClass;

3.6 mysqldump 数据库备份（小数据量适用）

（1）导出整个数据库

导出文件默认是存在    

mysqldump -u 用户名 -p 数据库名 > 导出的文件名;

mysqldump -u用户名 -p 数据库名 > 文件名.sql

（2）导出单张表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名;

mysqldump -u用户名 -p 数据库名 表名 > 表名.sql

（3）仅导出表结构（无数据）

mysqldump -u用户名 -p -d --add-drop-table 数据库名 > 结构.sql

说明：-d 不导出数据，--add-drop-table 创建前先删除。

（4）指定字符集导出

mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt 数据库名 > backup.sql

四、MYSQL通用调优策略

1、硬件层相关优化

修改服务器BIOS设置，找到，CPU电源管理，选择Performance Per Watt Optimized(DAPC)，发挥CPU最大性能。

然后关闭再将C-states和C1E关闭，开启Turbo Boots可以将CPU保持运行全核睿频的状态下。

然后修改BIOS设置中的Memory Frequency（内存频率），这个选项是控制BIOS内存频率，可以通过此参数节省内存频率以节省电力，但对于跑MySQL的机器来说，省电就算了，还是以性能为主，选择Maximum Performance（最佳性能）。

最后，在内存设置菜单中，启用Node Interleaving，避免NUMA问题，这个参数是专门为了控制NUMA而设置。NUMA是一种关于多个cpu如何访问内存的架构模型。

2、磁盘I/O相关优化

（1）、使用SSD硬盘，至少获得数百倍甚至万倍的IOPS提升。

（2）、购置阵列卡建议配备CACHE及BBU模块，可明显提升IOPS 这个主要针对机械硬盘，SSD磁盘除外。同时需要定期检查CACHE及BBU模块的健康状况，确保意外时不至于丢失数据。

主流的DELL/HP/IBM等服务器厂商，都会在Raid控制卡里都会内置128MB至1GB不等的Cache Memory，而我们对磁盘的读和写操作都会通过事先在Cache Memory中Hit或缓存，这样一来就可以大大提高了实际IO性能.    而BBU就是Raid卡中的一个电池备用模块，因为之前我们说到在Raid的环境下很多情况下数据都是通过Cache Memory和磁盘交换的，而Memory本身并无法保障数据持久性，万一电源中断，而数据没来得及flush到物理磁盘上，就会造成数据丢失的悲剧。为此硬件厂商提供了BBU，其中包含了一块锂电池来保障万一电源中断的情况下，Cache Memory中的数据不至于丢失，直至电源恢復。

（3）、磁盘raid级别尽量选择raid10，而不是raid5.

3、文件系统层优化

3.1 使用deadline/noop这两种I/O调度器，不要用cfq

CFQ是完全公平排队I/O调度算法，CFQ适用于系统中存在多任务I/O请求的情况，通过在多进程中轮换，保证了系统I/O请求整体的低延迟。但是，对于只有少数进程存在大量密集的I/O请求的情况，会出现明显的I/O性能下降。

NOOP是电梯式调度算法，调度方式十分简单，它是按先来先处理的思路将请求插入到等待队列的尾部。

DEADLINE是截止时间调度算法，它确保了在一个截止时间内服务的请求,这个截止时间是可调整的,而默认读期限短于写期限.这样就防止了写操作因为不能被读取而饿死的现象。Deadline对数据库环境(ORACLE RAC,MYSQL等)是最好的选择。

3.2 推荐使用xfs文件系统

不要使用ext3，ext4勉强可用，如果业务量很大的话，一定要用xfs，此外，文件系统在mount时，建议增加：noatime, nodiratime, nobarrier几个选项（nobarrier是xfs文件系统特有的）。这几个选项主要是禁止记录文件或目录最近一次访问时间戳。

4、Linux系统内核参数优化

4.1 概述

MySQL 性能不只是数据库本身的配置，更依赖操作系统内核 + 硬件 + 数据库参数三层协同优化。本章内容直接适用于 CentOS/RHEL 系列服务器，是线上高并发、大数据量 MySQL 的标准调优项。

4.2 Linux 系统内核参数优化

数据库类业务对内存使用、I/O 调度、缓存刷新非常敏感，内核参数必须针对性调整。

（1）vm.swappiness 内存交换优化

• 作用：控制系统使用 swap 分区的倾向
• 默认值：60（内存用到 40% 就开始换出，对 MySQL 极不友好）
• 调优值：5–10
• 设置命令

echo 10 > /proc/sys/vm/swappiness

• 说明：尽量使用物理内存，避免 MySQL 数据被换入 swap 导致性能急剧下降。

（2）脏页参数 vm.dirty_background_ratio & vm.dirty_ratio

这两个参数控制内存缓存数据刷入磁盘的策略，直接影响 MySQL 写入抖动。

1. vm.dirty_background_ratio后台异步刷盘阈值，达到内存占比后系统自动回写，不阻塞业务。建议：5–10

2. vm.dirty_ratio强制同步刷盘阈值，达到后会阻塞应用 I/O。建议：设置为上面值的 2 倍左右

• 查看命令

cat /proc/sys/vm/dirty_ratio
cat /proc/sys/vm/dirty_background_ratio

• 优化目标：让脏数据持续平稳刷盘，避免瞬间大量 I/O 引发数据库卡顿。

4.3 MySQL 核心参数优化建议（生产级）

以下为 InnoDB 引擎为主的 MySQL 实例最关键、最常用、必须调的 10 项参数。

（1）innodb_buffer_pool_size（最核心）

• 作用：InnoDB 数据与索引的内存缓存池
• 规则：
  • 内存 <4GB：设为物理内存的 20%
  • 内存 ≥128GB：建议72G左右
  • 通用：物理内存的 50%–65%
• 意义：越大越能把热点数据放内存，查询性能成倍提升。

（2）innodb_log_file_size（redo 日志大小）

• 作用：控制 InnoDB 事务重做日志大小
• 建议：2GB（配合默认 2 组日志，总 redo 空间 4GB）
• 注意：
  • 太大：崩溃恢复时间变长
  • 太小：频繁日志切换，写入性能上不去
• 总 redo 空间 = innodb_log_file_size × innodb_log_files_in_group

（3）innodb_log_buffer_size（日志缓冲区）

• 默认：1MB
• 调优：业务含大文本、大对象时需加大
• 判断依据：Innodb_log_waits ≠ 0 就需要调大
• 说明：缓冲区越大，I/O 次数越少，但异常宕机可能丢失少量数据。

（4）innodb_flush_log_at_trx_commit（事务安全与性能平衡）

控制日志刷盘策略，数据安全与性能的最重要开关。

• 1（默认，最安全）：每次提交事务都刷盘，不丢数据 → 主库必须用 1
• 0：每秒批量刷盘，性能极高，宕机可能丢 1 秒数据 → 游戏库 / 非核心库可用
• 2：提交写到系统缓存，每秒刷盘 → 从库可用

（5）skip_name_resolve（关闭 DNS 反向解析）

• 建议：开启 = 1
• 作用：避免 MySQL 对连接 IP 做反向 DNS 解析，导致连接超时、卡顿
• 副作用：授权只能用 IP，不能用主机名

（6）max_connections（最大连接数）

• 作用：控制 MySQL 同时接受的客户端连接数
• 生产建议：不超过20000
• 报错 Too many connections 说明此值太小

必须同步提升系统文件句柄：

/etc/security/limits.conf

mysql hard nofile 65535
mysql soft nofile 65535

/usr/lib/systemd/system/mysqld.service

LimitNOFILE=65535
LimitNPROC=65535

重载生效

systemctl daemon-reload
systemctl restart mysqld

（7）gtid_mode（开启 GTID 复制）

• 建议：on
• 作用：主从复制使用全局事务 ID，运维更简单
• 优势：自动定位事务位置，切换、搭建从库更安全可靠。

（8）log_bin（开启二进制日志）

• 作用：记录所有数据变更，用于主从复制 + 基于时间点恢复
• 必须开启：只要是主库，一定要开。

（9）tmp_table_size（临时表内存大小）

• 默认：32M
• 建议：64M
• 说明：GROUP BY、ORDER BY 等会使用临时表，超过则落盘，影响性能。

（10）max_allowed_packet（最大数据包）

• 报错：1153 - Got a packet bigger than max_allowed_packet
• 场景：导入大数据、大字段、批量插入时出现
• 处理：调大该参数，客户端与服务器都要改

五、 Mysql数据库备份工具xtrabackup

5.1 XtraBackup 工具介绍

5.1.1 核心特性

XtraBackup 是 Percona 公司 开发的专为 InnoDB 设计的在线热备工具，核心优势：

• 开源免费，无版权风险
• 真正在线热备，备份期间不锁库、不影响业务写入
• 备份恢复速度快，占用空间小
• 支持全量备份、增量备份、流备份、远程备份
• 兼容 MySQL 主流版本，支持 TB 级海量数据

官方地址：http://www.percona.com/software/percona-xtrabackupYUM 源下载：https://www.percona.com/downloads/percona-release/

5.1.2 版本说明

• XtraBackup 8.0：适配 MySQL 8.0，移除 innobackupex 命令，仅用 xtrabackup
• XtraBackup 2.4：适配 MySQL 5.6 / 5.7
• 工具依据 my.cnf 读取配置，需要数据库连接权限与 datadir 操作权限

5.2 XtraBackup 安装（CentOS 实战）

5.2.1 安装 YUM 源

# 安装 Percona YUM 源
rpm -ivh percona-release-1.0-26.noarch.rpm

# 验证可用安装包
yum list percona-xtrabackup*

5.2.2 安装依赖与 XtraBackup

# 安装兼容依赖
yum -y install mysql-community-libs-compat.x86_64

# MySQL 8.0 安装 XtraBackup 8.0
yum install percona-xtrabackup-80.x86_64 -y

安装完成后即可使用 xtrabackup 命令。

5.3 XtraBackup 备份恢复原理

XtraBackup 基于 InnoDB redo log（事务日志） 实现一致性热备。

5.3.1 备份原理

1. 启动备份时，记录当前 LSN（日志序列号）
2. 后台持续复制数据文件
3. 同时启动日志监听线程，实时捕获备份期间产生的 redo log
4. 备份结束时，数据文件 + 增量日志 = 一致性数据

5.3.2 恢复原理（Prepare 阶段）

1. 重做已提交事务（redo）
2. 回滚未提交事务（undo）
3. 将数据同步到一致状态，确保启动后不报错
4. 类似 MySQL 启动时的崩溃恢复流程

5.4 XtraBackup 全量备份实战

5.4.1 创建专用备份用户（生产规范）

为安全起见，不建议用 root 备份，创建最小权限备份用户：

grant reload,lock tables,replication client,create tablespace,super on *.* 
to bakuser@'172.16.213.%' identified by '123456';

5.4.2 全量备份命令

xtrabackup --defaults-file=/etc/my.cnf \
--user=root \
--password='root@mySQL123' \
--backup \
--target-dir=/data2/backup

5.4.3 核心参数说明

• --defaults-file：指定 MySQL 配置文件
• --backup：标识执行备份操作
• --target-dir：备份文件存放目录
• --user/--password：数据库认证信息
• --host/--port/--socket：连接方式

5.5 XtraBackup 全量恢复实战

恢复必须分两步：prepare 一致性数据 → copy-back 恢复文件，且恢复前必须关闭 MySQL。

5.5.1 Step1：Prepare 数据（关键步骤）

xtrabackup --host=localhost \
--user=root \
--password='root@mySQL123' \
--port=3306 \
--prepare \
--target-dir=/data2/backup

作用：

• 重做已提交事务
• 回滚未提交事务
• 使数据达到一致性可启动状态

5.5.2 Step2：停止 MySQL 并清空数据目录

systemctl stop mysqld
# 务必清空 datadir
rm -rf /var/lib/mysql/*

5.5.3 Step3：执行恢复

xtrabackup --host=localhost \
--user=root \
--password='root@mySQL123' \
--port=3306 \
--datadir=/var/lib/mysql \
--copy-back \
--target-dir=/data2/backup

5.5.4 Step4：修改权限并启动

chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

恢复完成，数据库可正常访问。

5.6 海量数据备份优化（生产推荐）

面对 100GB+ 甚至 TB 级 数据，普通备份速度慢、占空间，可使用流备份 + 压缩 + 远程备份。

5.6.1 本地流式压缩备份

xtrabackup --defaults-file=/etc/my.cnf \
--user=root \
--password='root@mySQL123' \
--backup \
--stream=xbstream \
--parallel=4 \
--compress-threads=8 \
| gzip > /data2/xtrabackup/mysqlbak1.xb.gz

5.6.2 直接备份到远程服务器

xtrabackup --defaults-file=/etc/my.cnf \
--user=root \
--password='root@mySQL123' \
--backup \
--stream=xbstream \
--parallel=4 \
--compress-threads=8 \
| ssh 172.16.213.80 "gzip > /mnt/mysqlbak1.xb.gz"

5.6.3 解压与恢复流备份

gzip -d -c mysqlbak2.xb.gz | xbstream -x -v -C xtrabackup_backupfiles

• -C：指定解压目录
• 解压后再执行 --prepare 与 --copy-back