Ubuntu MariaDB权限设置与优化指南

一、Ubuntu MariaDB权限设置

1. 登录MariaDB

使用root用户或具有sudo权限的用户登录MariaDB，命令如下：
sudo mysql -u root -p
输入密码后进入MariaDB命令行界面。

2. 创建数据库用户

通过CREATE USER语句创建用户，指定用户名、主机（localhost表示本地连接，%表示允许远程连接）和密码：
CREATE USER 'username'@'localhost' IDENTIFIED BY 'StrongPassword123';
示例：创建本地用户db_user，密码为SecurePass@2025：
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'SecurePass@2025';

3. 分配最小必要权限

遵循最小权限原则，仅授予用户完成任务所需的权限，避免使用GRANT ALL PRIVILEGES（除非必要）。常见权限类型：

完全权限（仅用于管理员）：GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
读写权限：GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';
只读权限：GRANT SELECT ON database_name.* TO 'username'@'localhost';
特定表权限：GRANT SELECT, UPDATE ON database_name.table_name TO 'username'@'localhost';
示例：授予db_user对mydb数据库的读写权限：
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'db_user'@'localhost';

4. 刷新权限使更改生效

执行FLUSH PRIVILEGES;命令，使权限修改立即生效（MariaDB 10.4+版本会自动刷新，但显式执行更安全）。

5. 查看与管理用户权限

查看用户及主机：SELECT User, Host FROM mysql.user;
查看用户权限：SHOW GRANTS FOR 'username'@'localhost';
撤销权限：REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';
删除用户：DROP USER 'username'@'localhost';

6. 远程访问配置（可选）

若需允许远程连接，需修改MariaDB配置文件并调整权限：

编辑配置文件：sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf，找到bind-address并将其修改为服务器IP地址或0.0.0.0（允许所有IP连接）；
重启MariaDB服务：sudo systemctl restart mariadb；
授权远程用户：GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'remote_ip' IDENTIFIED BY 'StrongPassword123';（remote_ip为远程客户端IP）。

二、Ubuntu MariaDB权限优化

1. 遵循最小权限原则

定期审计用户权限，删除不必要的权限或用户。例如，若用户仅需读取数据，应将权限从ALL PRIVILEGES调整为SELECT；禁止为开发人员分配生产数据库的DROP或ALTER权限。

2. 定期审计权限

使用SELECT User, Host, Select_priv, Insert_priv, Update_priv FROM mysql.db;命令查看用户权限详情，识别并移除未使用的权限或用户。建议每月执行一次权限审计。

3. 限制远程访问

仅允许可信IP地址连接MariaDB，通过GRANT语句指定username@specific_ip；
修改bind-address限制监听地址（如bind-address = 192.168.1.100）；
配置防火墙（如ufw）允许仅必要的端口（默认3306）：sudo ufw allow from trusted_ip to any port 3306。

4. 强化密码策略

创建用户时使用强密码（包含大小写字母、数字、特殊字符，长度≥12位）；
定期更换密码（如每90天）；
启用密码过期策略（通过ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewPassword' PASSWORD EXPIRE INTERVAL 90 DAY;设置）。

5. 移除匿名用户与测试数据库

运行mysql_secure_installation脚本，自动移除匿名用户、禁止root远程登录、删除测试数据库（test），提升数据库安全性。

三、Ubuntu MariaDB性能优化

1. 硬件优化

增加内存：MariaDB依赖内存缓存数据，建议内存容量为数据量的1.5-2倍；
使用SSD：SSD的随机读写性能远优于HDD，能显著提升数据库响应速度；
多核CPU：MariaDB支持多线程处理，更多CPU核心可提高并发处理能力。

2. 配置优化

编辑/etc/mysql/mariadb.conf.d/50-server.cnf文件，调整关键参数：

innodb_buffer_pool_size：设置为总内存的70%左右（如16GB内存设置为11GB），用于缓存InnoDB表数据和索引；
innodb_log_file_size：增大日志文件大小（如256MB-1GB），减少日志刷盘频率，提升写入性能；
innodb_flush_log_at_trx_commit：设置为2（平衡性能与安全性，牺牲部分数据安全性换取更高性能，适用于非金融场景）；
max_connections：根据应用需求设置（如100-500），避免过多连接导致资源耗尽；
query_cache_size：启用查询缓存（如64MB），缓存重复查询结果（注意：MariaDB 10.4+版本已移除查询缓存，需使用其他缓存机制）。

3. 索引优化

创建合适索引：为WHERE、JOIN、ORDER BY子句中频繁使用的列创建索引（如CREATE INDEX idx_column ON table_name(column_name);）；
避免过度索引：每个索引会增加写入和更新的开销，建议单表索引数量不超过5个；
使用组合索引：对于多个列联合查询，创建组合索引（如CREATE INDEX idx_name_age ON users(last_name, first_name);）；
定期重建索引：使用OPTIMIZE TABLE命令整理表碎片，保持索引效率（如OPTIMIZE TABLE large_table;）。

4. 查询优化

使用EXPLAIN分析查询：通过EXPLAIN SELECT * FROM table_name WHERE condition;查看查询计划，识别全表扫描、索引未使用等问题；
避免SELECT *：仅选择需要的列（如SELECT id, name FROM users;），减少数据传输量和内存占用；
优化查询语句：简化复杂子查询，使用JOIN替代子查询（如SELECT a.name FROM table_a a JOIN table_b b ON a.id = b.a_id WHERE b.status = 1;）；
使用LIMIT限制结果集：对于大数据量分页查询，使用LIMIT 10 OFFSET 20（获取第3页，每页10条）。

5. 定期维护

优化表：定期执行OPTIMIZE TABLE命令，整理表碎片（适用于频繁更新的表）；
清理日志：定期清理二进制日志（mysqlbinlog）和慢查询日志（slow_query_log），释放磁盘空间；
备份数据：使用mysqldump或mariabackup工具定期备份数据（如每日增量备份、每周全量备份）。

Ubuntu MariaDB权限设置与优化