【运维实战】Ubuntu 下 MySQL 进阶配置：远程访问 + 性能优化 + 备份策略

修改 MySQL 绑定地址
Ubuntu 的 MySQL 配置文件路径和 CentOS 不同，重点注意不要找错文件：

# 编辑MySQL核心配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 找到以下行：
bind-address = 127.0.0.1
# 注释掉该行（加#），或直接改为0.0.0.0（允许绑定所有IP）
# bind-address = 127.0.0.1
bind-address = 0.0.0.0

开放 Ubuntu 防火墙 3306 端口
MySQL 默认使用 3306 端口，需确保服务器防火墙放行该端口：

# 查看防火墙状态（默认inactive，未开启）
ufw status
# 开启防火墙
ufw enable
# 注意开启防火墙可能会断开ssh连接，
# 添加SSH规则（防止远程连接断开）
ufw allow ssh

# 若防火墙开启（active），执行以下命令开放端口
ufw allow 3306/tcp # 仅放行TCP协议（MySQL用TCP）
ufw reload # 重🆕加载防火墙规则

# 验证端口是否开放
ufw show added

正常输出示例：

root@ubuntn-server:~# ufw show added
Added user rules (see 'ufw status' for running firewall):
ufw allow 22/tcp
ufw allow 3306/tcp

重启 MySQL 并验证远程访问

# Ubuntu的MySQL服务名是mysql，不是mysqld！
systemctl restart mysql
systemctl status mysql # 确认服务为active (running)

# 远程机器验证连接（替换参数）
# MySQL服务器IP就是你在Ubuntu系统的IP地址，这里我的服务器IP是：192.168.47.128 （替换成你们自己的IP地址）
# 创建的授权用户就是上期创建数据库并且创建的授权用户，这里我创建的用户名是：test_a（替换成你们自己创建的用户）
# 192.168.47.1128：MySQL服务器IP；
# testuser：创建的授权用户
# 不明白的可以去看[MySQL高效部署全流程指南](https://blog.csdn.net/xjx5207/article/details/158463867?spm=1001.2014.3001.5502)
mysql -h 192.168.47.128 -u test_a -p
# 输入密码后能进入mysql>提示符，说明远程访问成功
# 这里需要注意，由于我之前授权的时候使用的是127.0.0.1的地址，你需要访问的远程主机的IP地址需要在数据库服务器上授权用户访问相对应数据库的权限，修改相对应的ip

备份原有配置（避免改错回滚）

cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

调整核心优化参数

vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 在[mysqld]段落下添加/修改以下参数：
[mysqld]
# 1. InnoDB缓冲池（核心！建议为物理内存的50-70%）
# 4G内存设2G，2G内存设1G，8G内存设5G
innodb_buffer_pool_size = 2G

# 2. 最大连接数（默认151，根据业务调整，建议200-500）
max_connections = 300

# 3. 开启慢查询日志（定位性能瓶颈，运维必备）
slow_query_log = 1 # 开启慢查询
slow_query_log_file = /var/log/mysql/slow.log # 日志路径
long_query_time = 2 # 执行超2秒的SQL记为慢查询

验证优化参数生效

# 重启MySQL服务
sudo systemctl restart mysql

# 登录MySQL验证参数是否与你设置的参数相对
mysql -u root
# 查看缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
# 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
# 查看慢查询日志状态（Value为ON则生效）
SHOW VARIABLES LIKE 'slow_query_log';

手动执行备份（基础操作）

# 1. 创建备份目录（可以根据你所属项目的数据库单独建立一个数据库备份目录）
mkdir -p /backup/mysql

# 2. 执行备份
# test：要备份的数据库名；backup_20260302.sql：带日期的备份文件名
mysqldump -u root -p test > /backup/mysql/backup_20260303.sql

# 示例：备份test_db数据库
mysqldump -u root -p test_db > /backup/mysql/backup_20260303.sql
# 输入数据库密码后开始备份，无报错即成功

# 3.查看备份文件是否存在
ls /backup/mysql
# 如果backup_20260303.sql存在，则证明备份成功了

设置定时备份（crontab 自动执行）

# 编辑定时任务
crontab -e

# 在文件末尾添加以下内容（每天凌晨2点备份，自动压缩）
# 解释：
# 0 2 * * * → 每天凌晨2点执行
# gzip → 压缩备份文件（节省空间）
# $(date +\\%Y\\%m\\%d) → 自动生成年月日（如20260302）
0 2 * * * /usr/bin/mysqldump -u root -p'你设置的数据库密码' test | gzip > /backup/mysql/backup_$(date +\\%Y\\%m\\%d).sql.gz >> /backup/mysql/backup.log 2>&1

# 示例：每天下午18点执行备份test数据库
0 18 * * * /usr/bin/mysqldump -u root -p'root123456' test | gzip > /backup/mysql/backup_$(date +\\%Y\\%m\\%d).sql.gz >> /backup/mysql/backup.log 2>&1
# 保存退出后，验证定时任务
crontab -l

备份恢复测试

# 1. 创建test_db_restore空库用于恢复测试
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS test_db_restore DEFAULT CHARSET utf8mb4;"

# 2. 导入备份文件恢复数据
mysql -u root -p test_db_restore < /backup/mysql/backup_20260303.sql

# 3. 登录验证（查看恢复后的数据是否存在）
mysql -u root -p test_db_restore
SHOW TABLES; # 能看到数据库表即恢复成功