MDYH

02 MySQL数据库

7分钟阅读

MySQL与MariaDB数据库

安装与配置

安装

# Debian/Ubuntu - MariaDB
sudo apt install mariadb-server mariadb-client
 
# Debian/Ubuntu - MySQL
sudo apt install mysql-server mysql-client
 
# RHEL/CentOS - MariaDB
sudo dnf install mariadb-server mariadb
 
# RHEL/CentOS - MySQL
sudo dnf install mysql-server mysql
 
# 启动服务
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl start mysql
sudo systemctl enable mysql

安全配置

# 运行安全脚本
sudo mysql_secure_installation
 
# 交互式配置：
# 1. 设置root密码
# 2. 移除匿名用户
# 3. 禁止root远程登录
# 4. 移除测试数据库
# 5. 重新加载权限表

配置文件

# /etc/mysql/mariadb.conf.d/50-server.cnf
# 或 /etc/my.cnf
 
[mysqld]
# 基础设置
user = mysql
port = 3306
bind-address = 0.0.0.0
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
 
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
 
# 连接设置
max_connections = 200
max_connect_errors = 100
wait_timeout = 28800
interactive_timeout = 28800
 
# 缓存设置
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 400
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 8M
 
# InnoDB设置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
 
# 日志设置
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
 
[client]
default-character-set = utf8mb4
 
[mysql]
default-character-set = utf8mb4

基本操作

连接数据库

# 本地连接
mysql -u root -p
mysql -u root -p database_name
 
# 远程连接
mysql -h 192.168.1.100 -P 3306 -u user -p
mysql -h host -u user -p database_name
 
# 执行SQL文件
mysql -u root -p database < backup.sql
mysql -u root -p -e "SHOW DATABASES;"
 
# 导出数据
mysqldump -u root -p database > backup.sql
mysqldump -u root -p database table > table_backup.sql
mysqldump -u root -p --all-databases > all_backup.sql

数据库管理

-- 显示数据库
SHOW DATABASES;
 
-- 创建数据库
CREATE DATABASE mydb;
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- 删除数据库
DROP DATABASE mydb;
 
-- 选择数据库
USE mydb;
 
-- 显示当前数据库
SELECT DATABASE();

表操作

-- 显示表
SHOW TABLES;
SHOW TABLES FROM mydb;
 
-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 查看表结构
DESCRIBE users;
DESC users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;
 
-- 修改表
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN email VARCHAR(200);
ALTER TABLE users ADD INDEX idx_username (username);
ALTER TABLE users DROP INDEX idx_username;
ALTER TABLE users RENAME TO members;
 
-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;
 
-- 清空表
TRUNCATE TABLE users;

数据操作

-- 插入数据
INSERT INTO users (username, email, password) VALUES ('john', 'john@example.com', 'hashed_password');
INSERT INTO users (username, email, password) VALUES 
    ('user1', 'user1@example.com', 'pass1'),
    ('user2', 'user2@example.com', 'pass2');
 
-- 查询数据
SELECT * FROM users;
SELECT id, username, email FROM users;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 18 ORDER BY created_at DESC;
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT COUNT(*) FROM users;
SELECT age, COUNT(*) FROM users GROUP BY age;
 
-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE users SET age = age + 1 WHERE age < 30;
 
-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;

用户与权限

用户管理

-- 查看用户
SELECT User, Host FROM mysql.user;
 
-- 创建用户
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'user'@'%' IDENTIFIED BY 'password';
CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'password';
 
-- 修改密码
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('new_password');
 
-- 删除用户
DROP USER 'user'@'localhost';

权限管理

-- 查看权限
SHOW GRANTS FOR 'user'@'localhost';
 
-- 授权
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON mydb.users TO 'user'@'localhost';
GRANT SELECT ON *.* TO 'readonly'@'%';
 
-- 撤销权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'user'@'localhost';
REVOKE DELETE ON mydb.* FROM 'user'@'localhost';
 
-- 刷新权限
FLUSH PRIVILEGES;

常用权限

权限说明
ALL所有权限
SELECT查询
INSERT插入
UPDATE更新
DELETE删除
CREATE创建表/数据库
DROP删除表/数据库
ALTER修改表结构
INDEX创建/删除索引
GRANT OPTION授权权限

备份与恢复

mysqldump备份

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql
 
# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > backup.sql
 
# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql
 
# 备份单个表
mysqldump -u root -p mydb users > users_backup.sql
 
# 只备份结构
mysqldump -u root -p --no-data mydb > structure.sql
 
# 只备份数据
mysqldump -u root -p --no-create-info mydb > data.sql
 
# 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz
 
# 远程备份
mysqldump -h remote_host -u root -p mydb > backup.sql

恢复数据

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql
 
# 恢复压缩备份
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb
 
# 从所有数据库备份恢复
mysql -u root -p < all_backup.sql

自动备份脚本

#!/bin/bash
# MySQL自动备份脚本
 
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="password"
DATABASE="mydb"
RETENTION_DAYS=7
 
mkdir -p $BACKUP_DIR
 
mysqldump -u $DB_USER -p$DB_PASS --single-transaction --routines --triggers $DATABASE | gzip > $BACKUP_DIR/${DATABASE}_${DATE}.sql.gz
 
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

性能优化

查询优化

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
 
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
 
-- 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
 
-- 查看索引使用
SHOW INDEX FROM users;
 
-- 创建索引
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age);
 
-- 删除索引
DROP INDEX idx_email ON users;

状态监控

-- 查看状态
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';
 
-- 查看进程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
 
-- 杀死进程
KILL 123;
 
-- 查看变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';
 
-- 动态修改变量
SET GLOBAL max_connections = 500;

表优化

-- 分析表
ANALYZE TABLE users;
 
-- 优化表
OPTIMIZE TABLE users;
 
-- 检查表
CHECK TABLE users;
 
-- 修复表
REPAIR TABLE users;

💡 数据库管理建议:

  • 定期备份并测试恢复
  • 使用强密码和最小权限
  • 监控慢查询并优化索引
  • 合理设置连接数和缓存

🔗 相关笔记: 09.01_Web服务部署 12.01_性能优化 13.01_服务器搭建实战

关系图谱