MySQL User Guide

MySQL/MariaDB 用户指引

MySQL 的存储引擎可选择。

简介

参考

版本关系

MariaDB MySQL
10.0 - 10.1 5.6
10.2 - 10.3 5.7
10.4 8.0

常见错误

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
Author: njun
njun's picture
Updated: 2021/01/31