MySQL User Guide

MySQL/MariaDB 用户指引

MySQL 的存储引擎可选择。

简介

版本关系

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

安装

MySQL 下载 :5.7最新版本

MariaDB 官方安装源生成器:Repository Generator

CentOS

国内使用官方生成的 yum 源安装 MariaDB 速度过慢。以下配置引用高速官方源为 CentOS 7 安装 MariaDB 10.2,其他版本可以参考这里调整 URL。

[mariadb]
name = MariaDB
baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.2/yum/centos7-amd64/
gpgkey = https://downloads.mariadb.com/MariaDB/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

阿里云服务器可以参考以下配置:

[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.2/centos7-amd64
gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1

Ubuntu

# 官方安装源密钥及依赖库
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo apt-get install software-properties-common
# MariaDB 10.4 on Ubuntu 16.04
sudo add-apt-repository 'deb [arch=amd64,arm64,i386,ppc64el] http://downloads.mariadb.com/MariaDB/mariadb-10.4/repo/ubuntu xenial main'
# MariaDB 10.4 on Ubuntu 18.04
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://downloads.mariadb.com/MariaDB/mariadb-10.4/repo/ubuntu bionic main'

Windows

解压数据库到安装目录,比如C:\MySQL,随后进行初始化,参考以下相应版本:

MySQL 5.7+ @ Windows

# 命令行进入数据库安装目录
# 初始化数据文件
mysqld --initialize-insecure --console

# 启动数据库
mysqld --console

# 设置 root 密码
mysql_secure_installation

安装目录下新建 my.ini 文件,存入以下初始化内容:

[mysqld]
explicit_defaults_for_timestamp = 1
max_allowed_packet = 32M

常见错误

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 以上版本支持线程池插件。

常用配置

相关路径

/Library/Application Support/appsolute/MAMP PRO/db MAMP MySQL 数据库目录
/Applications/MAMP/Library/scripts/mysql_install_db MAMP MySQL 数据库初始化脚本

开启 utf8mb4 字符集支持

MySQL 5.7 之前的版本需要手动开启 utf8mb4 支持,在 MySQL Server 的配置文件中加入以下配置即可:

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

Drupal 需添加 charset 和 collation 设置确保以 utf8mb4 字符集存取数据:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'databasename',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_general_ci',
);

常用命令

废弃当前输入

粘贴了一段 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;
GRANT ALL PRIVILEGES ON <DB NAME>.* TO <DB USER>@'<CLIENT IP/Hostname>' IDENTIFIED BY '<DB PASSWORD>' ;
FLUSH PRIVILEGES;

用户相关

SELECT host, user 
  FROM mysql.user;

导入 gz 备份

# macOS
gzip -d -c <备份文件名>.sql.gz | mysql -uroot -proot d_moha_www

慢查询相关

-- 查询慢查询设置
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: 2019/11/18