
MySQL/MariaDB 用户指引
MySQL 的存储引擎可选择。
简介
参考
版本关系
常见错误
Azure MySQL 导入数据权限不足
Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
设计原理
连接
MySQL 消耗线程管理客户端连接,5.5 以上版本支持线程池插件。
常用命令
废弃当前输入
粘贴了一段 SQL 后,发现有错误,不需要执行了,可以在最后加上 \c
让 MySQL 客户端忽略当前输入的命令。
巡查服务器状态
show full processlist;\n\p
show status like'Threads_%';\n\p
show status like '%connections%';\n\p
show status like 'wsrep_%';\n\p
检查数据表大小
SELECT table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = 'DB_NAME'
ORDER BY (data_length + index_length) DESC;
新建数据库
CREATE DATABASE DB_NAME DEFAULT charset utf8mb4 COLLATE utf8mb4_general_ci;
-- MySQL 8 之前
GRANT ALL PRIVILEGES ON DB_NAME.* TO DB_USER@'CLIENT_IP_OR_HOSTNAME' IDENTIFIED BY 'DB_PASSWORD';
FLUSH PRIVILEGES;
-- MySQL 8 之后
CREATE USER 'DB_USER'@'%' identified by 'DB_PASSWORD';
GRANT ALL on DB_NAME.* to 'DB_USER'@'%';
REVOKE ALL on DB_NAME.* from 'DB_USER'@'%';
GRANT select, insert, delete, update on DB_NAME.* to 'DB_USER'@'%';
用户相关
SELECT host, user
FROM mysql.user;
备份/恢复
gzip
mysql -u DB_USER -p DB_PASSWORD DB_NAME | gzip > BACKUP.sql.gz
gzip -d -c BACKUP.sql.gz | mysql -u DB_USER -pDB_PASSWORD DB_NAME
慢查询相关
-- 查询慢查询设置
show global variables like '%slow%';
show global variables like '%long%';
-- 开启慢查询
set global slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
FLUSH LOGS;
SET GLOBAL log_queries_not_using_indexes = 'ON';
[mysqld]
slow_query_log = /path/to/slow_query.log
long_query_time = 10
log_queries_not_using_indexes = ON
左联查询
在右侧的表没有满足条件的数据时补空记录。
-- 左联多表查询并导出为 CSV 文件(查询结果行数肯定等于第一张表的行数。)
SELECT name, mail, field_full_name_value, field_korean_name_value
FROM users
LEFT JOIN field_data_field_full_name on uid=field_data_field_full_name.entity_id
LEFT JOIN field_revision_field_korean_name on uid=field_revision_field_korean_name.entity_id
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
SHOW BINARY LOGS; -- 查看所有 Bin 日志
初始化 root 密码
#
mysqladmin -u root password