MariaDB 常用命令

本文档汇总了 MariaDB 数据库管理中最常用的命令，涵盖数据库连接、管理、备份恢复、性能监控等方面，便于 DBA 日常查阅和使用。

连接与断开数据库

1. 连接到本地数据库

# 使用默认端口（3306）连接到本地数据库
mysql -u root -p

# 使用指定端口连接到本地数据库
mysql -u root -p -P 3307

2. 连接到远程数据库

# 使用默认端口连接到远程数据库
mysql -u root -p -h 192.168.1.100

# 使用指定端口连接到远程数据库
mysql -u root -p -h 192.168.1.100 -P 3307

3. 连接到指定数据库

# 连接时指定数据库
mysql -u root -p ecommerce_db

# 或连接后切换数据库
mysql -u root -p
USE ecommerce_db;

4. 执行单条 SQL 命令

mysql -u root -p -e "SELECT * FROM users LIMIT 10;" ecommerce_db

5. 执行 SQL 文件

# 执行 SQL 文件
mysql -u root -p ecommerce_db < /path/to/sql/file.sql

# 执行 SQL 文件并输出结果
mysql -u root -p ecommerce_db < /path/to/sql/file.sql > /path/to/output/result.txt

6. 断开数据库连接

EXIT;
-- 或
QUIT;
-- 或使用快捷键 Ctrl+D

数据库管理命令

1. 查看所有数据库

SHOW DATABASES;

2. 创建数据库

-- 创建数据库
CREATE DATABASE ecommerce_db;

-- 创建数据库并指定字符集和排序规则
CREATE DATABASE ecommerce_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 查看数据库创建语句

SHOW CREATE DATABASE ecommerce_db;

4. 修改数据库

-- 修改数据库字符集和排序规则
ALTER DATABASE ecommerce_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. 删除数据库

DROP DATABASE IF EXISTS ecommerce_db;

6. 切换数据库

USE ecommerce_db;

表管理命令

1. 查看当前数据库中的所有表

SHOW TABLES;

2. 查看表结构

-- 查看表结构
DESCRIBE users;
-- 或
DESC users;

-- 查看详细表结构
SHOW CREATE TABLE users;

3. 创建表

CREATE TABLE users (
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(64) NOT NULL UNIQUE COMMENT '用户名',
    email VARCHAR(128) NOT NULL UNIQUE COMMENT '邮箱',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否激活',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

4. 修改表

-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) UNIQUE AFTER email;

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) UNIQUE;

-- 修改字段名
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30) UNIQUE;

-- 删除字段
ALTER TABLE users DROP COLUMN mobile;

-- 添加主键
ALTER TABLE orders ADD PRIMARY KEY (order_id);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_username (username);

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_create_time (create_time);

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);

-- 删除索引
ALTER TABLE users DROP INDEX uk_username;

-- 修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. 删除表

DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE IF EXISTS users, orders, products;

6. 截断表

TRUNCATE TABLE users;

7. 优化表

-- 优化表
OPTIMIZE TABLE users;

-- 优化多个表
OPTIMIZE TABLE users, orders, products;

8. 检查表

-- 检查表
CHECK TABLE users;

-- 修复表
REPAIR TABLE users;

数据操作命令（CRUD）

1. 插入数据（INSERT）

-- 插入单条数据
INSERT INTO users (username, email, password_hash) 
VALUES ('user1', 'user1@example.com', 'password_hash_123');

-- 插入多条数据
INSERT INTO users (username, email, password_hash) 
VALUES 
('user2', 'user2@example.com', 'password_hash_456'),
('user3', 'user3@example.com', 'password_hash_789');

-- 插入查询结果
INSERT INTO users_archive (username, email, create_time) 
SELECT username, email, create_time FROM users WHERE is_active = 0;

2. 查询数据（SELECT）

-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT user_id, username, email FROM users;

-- 带 WHERE 条件的查询
SELECT * FROM users WHERE is_active = 1;

-- 带 ORDER BY 的查询
SELECT * FROM users ORDER BY create_time DESC;

-- 带 LIMIT 的查询
SELECT * FROM users LIMIT 10;

-- 带 OFFSET 的查询
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 或
SELECT * FROM users LIMIT 20, 10;

-- 带 GROUP BY 的查询
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;

-- 带 HAVING 的查询
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING order_count > 5;

-- 带 JOIN 的查询
SELECT u.username, o.order_no, o.amount 
FROM users u 
INNER JOIN orders o ON u.user_id = o.user_id;

-- 带子查询的查询
SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 100);

3. 更新数据（UPDATE）

-- 更新单条数据
UPDATE users SET email = 'new_email@example.com' WHERE user_id = 1;

-- 更新多条数据
UPDATE users SET is_active = 0 WHERE create_time < '2023-01-01';

-- 更新带 LIMIT
UPDATE users SET is_active = 0 WHERE create_time < '2023-01-01' LIMIT 100;

4. 删除数据（DELETE）

-- 删除单条数据
DELETE FROM users WHERE user_id = 1;

-- 删除多条数据
DELETE FROM users WHERE create_time < '2023-01-01';

-- 删除带 LIMIT
DELETE FROM users WHERE create_time < '2023-01-01' LIMIT 100;

用户与权限管理命令

1. 查看所有用户

-- 查看所有用户
SELECT User, Host FROM mysql.user;

-- 查看当前用户
SELECT CURRENT_USER();
-- 或
SELECT USER();

2. 创建用户

-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- 创建允许远程访问的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';

-- 创建允许从特定IP访问的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';

3. 修改用户密码

-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewStrongPassword456!';

-- 或使用 SET PASSWORD
SET PASSWORD FOR 'app_user'@'localhost' = PASSWORD('NewStrongPassword456!');

4. 查看用户权限

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 查看当前用户权限
SHOW GRANTS;

5. 授予用户权限

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON ecommerce_db.* TO 'app_user'@'localhost';

-- 授予指定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.* TO 'app_user'@'localhost';

-- 授予表级权限
GRANT SELECT, INSERT ON ecommerce_db.users TO 'app_user'@'localhost';

-- 授予列级权限
GRANT SELECT (user_id, username, email) ON ecommerce_db.users TO 'app_user'@'localhost';

-- 授予权限并允许授权给其他用户
GRANT SELECT ON ecommerce_db.* TO 'app_user'@'localhost' WITH GRANT OPTION;

6. 回收用户权限

-- 回收所有权限
REVOKE ALL PRIVILEGES ON ecommerce_db.* FROM 'app_user'@'localhost';

-- 回收指定权限
REVOKE INSERT, UPDATE ON ecommerce_db.* FROM 'app_user'@'localhost';

7. 删除用户

DROP USER IF EXISTS 'app_user'@'localhost';

-- 删除多个用户
DROP USER IF EXISTS 'app_user'@'localhost', 'app_user'@'%';

8. 刷新权限

FLUSH PRIVILEGES;

备份与恢复命令

1. 使用 mysqldump 备份

# 备份单个数据库
mysqldump -u root -p ecommerce_db > /backup/ecommerce_db.sql

# 备份多个数据库
mysqldump -u root -p --databases ecommerce_db user_center_db > /backup/multi_databases.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > /backup/all_databases.sql

# 备份数据库结构（不包含数据）
mysqldump -u root -p --no-data ecommerce_db > /backup/ecommerce_db_schema.sql

# 备份数据库数据（不包含结构）
mysqldump -u root -p --no-create-info ecommerce_db > /backup/ecommerce_db_data.sql

# 备份指定表
mysqldump -u root -p ecommerce_db users orders > /backup/ecommerce_db_tables.sql

# 压缩备份
mysqldump -u root -p ecommerce_db | gzip > /backup/ecommerce_db.sql.gz

2. 使用 mysqldump 恢复

# 恢复数据库
mysql -u root -p ecommerce_db < /backup/ecommerce_db.sql

# 恢复压缩备份
gzip -d < /backup/ecommerce_db.sql.gz | mysql -u root -p ecommerce_db

# 恢复所有数据库
mysql -u root -p < /backup/all_databases.sql

3. 使用 mariabackup 备份

# 全量备份
mariabackup --backup --target-dir=/backup/full --user=backup --password=backup_password

# 增量备份（基于全量备份）
mariabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=backup --password=backup_password

# 压缩备份
mariabackup --backup --target-dir=/backup/full --compress --user=backup --password=backup_password

4. 使用 mariabackup 恢复

# 准备全量备份
mariabackup --prepare --target-dir=/backup/full

# 准备增量备份
mariabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1

# 恢复数据
mariabackup --copy-back --target-dir=/backup/full

# 修改数据目录权限
chown -R mysql:mysql /var/lib/mysql

5. 导出数据为 CSV

-- 导出数据为 CSV
SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 导入 CSV 数据
LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

性能监控命令

1. 查看数据库状态

-- 查看所有状态变量
SHOW GLOBAL STATUS;

-- 查看指定状态变量
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_hit_rate';

-- 查看会话状态变量
SHOW SESSION STATUS LIKE 'Threads_connected';

2. 查看数据库配置

-- 查看所有配置变量
SHOW GLOBAL VARIABLES;

-- 查看指定配置变量
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

-- 查看会话配置变量
SHOW SESSION VARIABLES LIKE 'sql_mode';

3. 查看当前连接

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看完整的当前连接
SHOW FULL PROCESSLIST;

-- 或从 information_schema 查看
SELECT * FROM information_schema.processlist;

4. 查看慢查询日志

-- 查看慢查询日志配置
SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 查看慢查询日志内容（如果使用表存储）
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

5. 分析查询执行计划

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'test';

-- 分析并执行查询（MariaDB 10.1+）
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'test';

6. 查看索引使用情况

-- 查看表的索引信息
SHOW INDEX FROM users;

-- 查看索引使用情况（MariaDB 10.0+）
SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'ecommerce_db';

7. 查看表和索引大小

-- 查看表大小
SELECT 
    table_name, 
    round(((data_length + index_length) / 1024 / 1024), 2) AS table_size_mb 
FROM information_schema.tables 
WHERE table_schema = 'ecommerce_db' 
ORDER BY table_size_mb DESC;

复制管理命令

1. 查看主从复制状态

-- 在从库上查看复制状态
SHOW SLAVE STATUS\G;

-- 查看主库二进制日志信息
SHOW MASTER STATUS;

2. 配置主从复制

-- 主库上创建复制用户
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

-- 从库上配置复制
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='ReplPassword123!',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=107,
    MASTER_CONNECT_RETRY=10;

-- 启动从库复制
START SLAVE;

-- 停止从库复制
STOP SLAVE;

-- 重置从库复制
RESET SLAVE;

3. 使用 GTID 复制

-- 主库和从库上启用 GTID
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = ON;

-- 从库上配置 GTID 复制
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='ReplPassword123!',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;

-- 启动从库复制
START SLAVE;

Galera Cluster 命令

1. 查看 Galera 集群状态

-- 查看 Galera 集群状态变量
SHOW GLOBAL STATUS LIKE 'wsrep%';

-- 查看集群大小
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size';

-- 查看节点状态
SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_comment';

2. 启动 Galera 集群

# 启动第一个节点（引导节点）
sudo systemctl start mariadb --wsrep-new-cluster

# 启动其他节点
sudo systemctl start mariadb

3. 查看 Galera 配置

-- 查看 Galera 配置变量
SHOW GLOBAL VARIABLES LIKE 'wsrep%';

4. 动态修改 Galera 配置

-- 动态修改 wsrep_slave_threads
SET GLOBAL wsrep_slave_threads = 8;

其他常用命令

1. 查看 MariaDB 版本

-- 查看 MariaDB 版本
SELECT VERSION();

-- 或使用命令行
mysql --version
-- 或
mysql -V

2. 查看当前时间

SELECT NOW();
-- 或
SELECT CURRENT_TIMESTAMP;

3. 查看字符集

-- 查看服务器字符集
SHOW VARIABLES LIKE 'character_set_server';

-- 查看数据库字符集
SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'ecommerce_db';

-- 查看表字符集
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'ecommerce_db';

4. 查看 SQL 模式

-- 查看 SQL 模式
SHOW GLOBAL VARIABLES LIKE 'sql_mode';

-- 查看会话 SQL 模式
SHOW SESSION VARIABLES LIKE 'sql_mode';

5. 刷新缓存

-- 刷新查询缓存
FLUSH QUERY CACHE;

-- 刷新所有缓存
FLUSH ALL;

-- 刷新表缓存
FLUSH TABLES;

-- 刷新表锁
FLUSH TABLES WITH READ LOCK;

6. 查看锁信息

-- 查看表锁
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看行锁
SELECT * FROM information_schema.INNODB_LOCKS;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

7. 查看事务信息

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看事务隔离级别
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

8. 执行系统命令

-- 执行系统命令（需要 FILE 权限）
SYSTEM ls -la;

常用命令行工具

1. mysqladmin

-- 查看服务器状态
mysqladmin -u root -p status

-- 查看服务器变量
mysqladmin -u root -p variables

-- 查看进程列表
mysqladmin -u root -p processlist

-- 刷新权限
mysqladmin -u root -p flush-privileges

-- 关闭服务器
mysqladmin -u root -p shutdown

2. mysqlcheck

-- 检查数据库
mysqlcheck -u root -p ecommerce_db

-- 修复数据库
mysqlcheck -u root -p --repair ecommerce_db

-- 优化数据库
mysqlcheck -u root -p --optimize ecommerce_db

-- 分析数据库
mysqlcheck -u root -p --analyze ecommerce_db

3. mysqldumpslow

-- 分析慢查询日志
mysqldumpslow /var/log/mariadb/mariadb-slow.log

-- 按查询次数排序
mysqldumpslow -s c /var/log/mariadb/mariadb-slow.log

-- 显示前 10 条慢查询
mysqldumpslow -t 10 /var/log/mariadb/mariadb-slow.log

4. pt-query-digest（Percona Toolkit）

-- 分析慢查询日志
pt-query-digest /var/log/mariadb/mariadb-slow.log

-- 分析二进制日志
pt-query-digest binlog.000001

-- 实时分析慢查询
pt-query-digest --processlist h=localhost,u=root,p=password

总结

本文档汇总了 MariaDB 数据库管理中最常用的命令，涵盖了数据库连接、管理、表管理、数据操作、用户与权限管理、备份恢复、性能监控、复制管理和 Galera Cluster 管理等方面。这些命令是 DBA 日常工作中最常用的工具，掌握这些命令可以提高数据库管理的效率和准确性。

在实际使用中，建议根据具体需求选择合适的命令，并结合 MariaDB 官方文档进行深入学习。同时，定期备份数据库，确保数据安全，是数据库管理的核心原则之一。