高性能 MySQL 运维实战：安装部署 + 命令速查 + 性能调优 + 备份恢复（超详细版）

在互联网后端架构中MySQL 是使用最广泛的关系型数据库。无论是开发、测试还是运维岗位掌握 MySQL 基础运维、安装部署、性能调优与备份恢复都是必备核心能力。本文结合多年实战经验整理一套可直接用于生产环境的 MySQL 运维实战手册帮助大家快速上手、避坑、提升稳定性与性能。一、 MySQL发行版的选择1.1 MySQL 官方发行版MySQL 是目前业界使用最广泛的关系型数据库具备四大核心优势简单易用学习与使用门槛低具备基础 IT 背景即可参照文档完成安装、部署与日常使用。开源免费开源特性使其普及度高可免费使用降低企业成本。多存储引擎支持内置 MyISAM、InnoDB、MERGE、MEMORY、BDB、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE 等多种存储引擎可按业务场景灵活选用。原生高可用能力自带 Replication 主从复制功能可实现数据实时备份支撑基础高可用架构。1.2 MySQL 核心存储引擎MyISAM 与 InnoDBMySQL 4.x/5.x 早期版本默认存储引擎为MyISAM从MySQL 5.5开始默认存储引擎正式切换为InnoDB。1.2.1 核心区别对比特性InnoDBMyISAM事务支持支持不支持锁机制行级锁并发高表级锁并发低外键支持不支持缓存缓存数据 索引只缓存索引适用场景高频写、金融 / 订单等高安全业务大量查询、只读业务1.2.2 引擎选型建议必须使用事务、追求数据安全与高并发 →必选 InnoDB以查询为主、需要全文索引、对写入性能要求低 →可选 MyISAM1.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. 二进制安装推荐自定义路径下载并解压mysqlcd /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.confvim /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 --usermysql \ --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 start2. 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 rootlocalhost identified by rootmySQL123;通过这个命令就修改了root用户的密码。3. Docker 安装快速体验配置阿里云安装源yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo yum makecache fast #刷新缓存接着安装dockeryum 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_PASSWORDroot123 \ -e MYSQL_DATABASEiivey \ -e MYSQL_USERiivey \ -e MYSQL_PASSWORDmysql123 \ swr.cn-north-1.myhuaweicloud.com/iivey/mysql:8.0.23 \ --default-authentication-pluginmysql_native_password \ --character-set-serverutf8上面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密码 abcd123mysql -h110.110.110.110 -uroot -pabcd1233退出 MySQLexit;3.2 MySQL 密码修改命令格式mysqladmin -u用户名 -p旧密码 password 新密码;1初始无密码修改直接设置新密码首先在Mysql安装目录下面的bin目录mysqladmin -u root password ab12注因为开始时root没有密码所以-p旧密码一项就可以省略了2已有密码修改旧密码 ab12 → 新密码 abc345再将root的密码改为abc345mysqladmin -u root -p ab12 password abc3453.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 test2localhost identified by abc;3无密码用户如果你不想test2有密码可以再执行下面这个命令将密码取消掉。grant select,insert,update,delete on mydb.* to test2localhost identified by ;4指定 IP 访问、授予全权限如果想给一个用户test2授予访问mydb数据库的所有权限并且仅允许test2在192.168.11.121这个客户端ip登录访问可执行如下命令grant all on mydb.* to test2192.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 注意最后有个sshow 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.5insert 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 id1;6修改数据语法update 表名 set 字段新值,… where 条件update MyClass set nameMary where id1;7增加字段命令alter table 表名 add 字段 类型 其他例如在表MyClass中添加了一个字段passtest类型为int(4)默认值为0alter table MyClass add passtest int(4) default 0;8修改表名命令rename table 原表名 to 新表名;例如在表MyClass名字更改为YouClassrename table MyClass to YouClass;3.6 mysqldump 数据库备份小数据量适用1导出整个数据库导出文件默认是存在mysqldump -u 用户名 -p 数据库名 导出的文件名;mysqldump -u用户名 -p 数据库名 文件名.sql2导出单张表mysqldump -u 用户名 -p 数据库名 表名 导出的文件名;mysqldump -u用户名 -p 数据库名 表名 表名.sql3仅导出表结构无数据mysqldump -u用户名 -p -d --add-drop-table 数据库名 结构.sql说明-d不导出数据--add-drop-table创建前先删除。4指定字符集导出mysqldump -uroot -p --default-character-setlatin1 --set-charsetgbk --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调度器不要用cfqCFQ是完全公平排队I/O调度算法CFQ适用于系统中存在多任务I/O请求的情况通过在多进程中轮换保证了系统I/O请求整体的低延迟。但是对于只有少数进程存在大量密集的I/O请求的情况会出现明显的I/O性能下降。NOOP是电梯式调度算法调度方式十分简单它是按先来先处理的思路将请求插入到等待队列的尾部。DEADLINE是截止时间调度算法它确保了在一个截止时间内服务的请求,这个截止时间是可调整的,而默认读期限短于写期限.这样就防止了写操作因为不能被读取而饿死的现象。Deadline对数据库环境(ORACLE RAC,MYSQL等)是最好的选择。3.2 推荐使用xfs文件系统不要使用ext3ext4勉强可用如果业务量很大的话一定要用xfs此外文件系统在mount时建议增加noatime, nodiratime, nobarrier几个选项nobarrier是xfs文件系统特有的。这几个选项主要是禁止记录文件或目录最近一次访问时间戳。4、Linux系统内核参数优化4.1 概述MySQL 性能不只是数据库本身的配置更依赖操作系统内核 硬件 数据库参数三层协同优化。本章内容直接适用于 CentOS/RHEL 系列服务器是线上高并发、大数据量 MySQL 的标准调优项。4.2 Linux 系统内核参数优化数据库类业务对内存使用、I/O 调度、缓存刷新非常敏感内核参数必须针对性调整。1vm.swappiness 内存交换优化作用控制系统使用 swap 分区的倾向默认值60内存用到 40% 就开始换出对 MySQL 极不友好调优值5–10设置命令echo 10 /proc/sys/vm/swappiness说明尽量使用物理内存避免 MySQL 数据被换入 swap 导致性能急剧下降。2脏页参数 vm.dirty_background_ratio vm.dirty_ratio这两个参数控制内存缓存数据刷入磁盘的策略直接影响 MySQL 写入抖动。vm.dirty_background_ratio后台异步刷盘阈值达到内存占比后系统自动回写不阻塞业务。建议5–10vm.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 项参数。1innodb_buffer_pool_size最核心作用InnoDB 数据与索引的内存缓存池规则内存 4GB设为物理内存的 20%内存 ≥128GB建议72G左右通用物理内存的 50%–65%意义越大越能把热点数据放内存查询性能成倍提升。2innodb_log_file_sizeredo 日志大小作用控制 InnoDB 事务重做日志大小建议2GB配合默认 2 组日志总 redo 空间 4GB注意太大崩溃恢复时间变长太小频繁日志切换写入性能上不去总 redo 空间 innodb_log_file_size × innodb_log_files_in_group3innodb_log_buffer_size日志缓冲区默认1MB调优业务含大文本、大对象时需加大判断依据Innodb_log_waits≠ 0 就需要调大说明缓冲区越大I/O 次数越少但异常宕机可能丢失少量数据。4innodb_flush_log_at_trx_commit事务安全与性能平衡控制日志刷盘策略数据安全与性能的最重要开关。1默认最安全每次提交事务都刷盘不丢数据 →主库必须用 10每秒批量刷盘性能极高宕机可能丢 1 秒数据 →游戏库 / 非核心库可用2提交写到系统缓存每秒刷盘 →从库可用5skip_name_resolve关闭 DNS 反向解析建议开启 1作用避免 MySQL 对连接 IP 做反向 DNS 解析导致连接超时、卡顿副作用授权只能用 IP不能用主机名6max_connections最大连接数作用控制 MySQL 同时接受的客户端连接数生产建议不超过20000报错 Too many connections说明此值太小必须同步提升系统文件句柄/etc/security/limits.confmysql hard nofile 65535 mysql soft nofile 65535/usr/lib/systemd/system/mysqld.serviceLimitNOFILE65535 LimitNPROC65535重载生效systemctl daemon-reload systemctl restart mysqld7gtid_mode开启 GTID 复制建议on作用主从复制使用全局事务 ID运维更简单优势自动定位事务位置切换、搭建从库更安全可靠。8log_bin开启二进制日志作用记录所有数据变更用于主从复制 基于时间点恢复必须开启只要是主库一定要开。9tmp_table_size临时表内存大小默认32M建议64M说明GROUP BY、ORDER BY 等会使用临时表超过则落盘影响性能。10max_allowed_packet最大数据包报错1153 - Got a packet bigger than max_allowed_packet场景导入大数据、大字段、批量插入时出现处理调大该参数客户端与服务器都要改五、 Mysql数据库备份工具xtrabackup5.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命令仅用xtrabackupXtraBackup 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 备份原理启动备份时记录当前LSN日志序列号后台持续复制数据文件同时启动日志监听线程实时捕获备份期间产生的 redo log备份结束时数据文件 增量日志 一致性数据5.3.2 恢复原理Prepare 阶段重做已提交事务redo回滚未提交事务undo将数据同步到一致状态确保启动后不报错类似 MySQL 启动时的崩溃恢复流程5.4 XtraBackup 全量备份实战5.4.1 创建专用备份用户生产规范为安全起见不建议用 root 备份创建最小权限备份用户grant reload,lock tables,replication client,create tablespace,super on *.* to bakuser172.16.213.% identified by 123456;5.4.2 全量备份命令xtrabackup --defaults-file/etc/my.cnf \ --userroot \ --passwordrootmySQL123 \ --backup \ --target-dir/data2/backup5.4.3 核心参数说明--defaults-file指定 MySQL 配置文件--backup标识执行备份操作--target-dir备份文件存放目录--user/--password数据库认证信息--host/--port/--socket连接方式5.5 XtraBackup 全量恢复实战恢复必须分两步prepare 一致性数据 → copy-back 恢复文件且恢复前必须关闭 MySQL。5.5.1 Step1Prepare 数据关键步骤xtrabackup --hostlocalhost \ --userroot \ --passwordrootmySQL123 \ --port3306 \ --prepare \ --target-dir/data2/backup作用重做已提交事务回滚未提交事务使数据达到一致性可启动状态5.5.2 Step2停止 MySQL 并清空数据目录systemctl stop mysqld # 务必清空 datadir rm -rf /var/lib/mysql/*5.5.3 Step3执行恢复xtrabackup --hostlocalhost \ --userroot \ --passwordrootmySQL123 \ --port3306 \ --datadir/var/lib/mysql \ --copy-back \ --target-dir/data2/backup5.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 \ --userroot \ --passwordrootmySQL123 \ --backup \ --streamxbstream \ --parallel4 \ --compress-threads8 \ | gzip /data2/xtrabackup/mysqlbak1.xb.gz5.6.2 直接备份到远程服务器xtrabackup --defaults-file/etc/my.cnf \ --userroot \ --passwordrootmySQL123 \ --backup \ --streamxbstream \ --parallel4 \ --compress-threads8 \ | ssh 172.16.213.80 gzip /mnt/mysqlbak1.xb.gz5.6.3 解压与恢复流备份gzip -d -c mysqlbak2.xb.gz | xbstream -x -v -C xtrabackup_backupfiles-C指定解压目录解压后再执行--prepare与--copy-back