MySQL

MariaDB vs MySQL, a Database Technologies Rundown (kinsta.com)

安装运行

本机安装运行

sudo apt install mysql-server mysql-client  # 可选

图形界面：MySQL Workbench。

使用Yum源安装MySQL

自动添加源信息：下载RPM包，使用安装包自动添加源信息。

sudo yum localinstall mysql80-community-release-fc34-1.noarch.rpm
yum repolist enabled | grep "mysql.*-community.*"

软件源提供多个版本的源信息，但安装官方未提供支持的版本可能出现问题。

MySQL 5.7 does not support Fedora; support was removed in MySQL 5.7.30.

手动添加源信息：/etc/yum.rpo.d/mysql-community.repo

sudo dnf remove @mysql
sudo dnf module reset mysql && sudo dnf module disable mysql

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=0
[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/
enabled=1
gpgcheck=0

执行安装：

sudo dnf config-manager --disable mysql80-community
sudo dnf config-manager --enable mysql57-community
sudo dnf install mysql-community-server
sudo systemctl enable --now mysqld

如果系统中由maria-db相关库，可能导致依赖检查失败，需要首先手动卸载maria-db相关库。

修改管理员登录密码：

# [CentOS] 获取临时密码,Ubuntu下初始密码为空
# sudo grep 'A temporary password' /var/log/mysqld.log |tail -1
sudo mysql_secure_installation   
# 或使用 mysql -u root -p <<< <temp_passwd>
# 然后修改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

The default password policy implemented by validate_password requires that passwords contain at least one uppercase letter, one lowercase letter, one digit, and one special character, and that the total password length is at least 8 characters.

1. MySQL :: MySQL 5.7 Reference Manual :: 2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository

2. Install MySQL 5.7 on CentOS 8 / RHEL 8 Linux - SystemPandit

使用Docker容器

使用docker-compose运行配置文件：

version: "3.1"
services:
  db:
    image: mysql:5.7
    container_name: mysql57
    environment:
      - "MYSQL_ROOT_PASSWORD=gang2019"   # 设置管理员密码
      - "MYSQL_DATABASE=exchange"        # 设置默认数据库
      - "MYSQL_USER=gary"                # 添加数据库用户（授权访问上述数据库）
      - "MYSQL_PASSWORD=gang2019"        # 设置用户密码（测试）
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - mysql:/var/lib/mysql                   # 数据库文件的存储位置
      - mysqlinit:/docker-entrypoint-initdb.d  # 初始化脚本目录(可选)
      - mysqlconf:/etc/mysql/conf.d            # 自定义配置目录(可选)
    ports:
      - 3306:3306   # host -> docker
    restart: always

  adminer:           # 管理服务
    image: adminer
    restart: always
    ports:
      - 18080:8080

volumes: 
  mysql:
    driver: local
  ......

目录mysql和mysql-init不要嵌套。

环境变量MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root设置从本地文件读取密码，类似地还有MYSQL_PASSWORD等。

或使用脚本运行：

#!/bin/bash
docker run -d \
	--name mysql57 \
	-v /home/gary/mysql:/var/lib/mysql \
	-v /home/gary/mysql-init:/docker-entrypoint-initdb.d \
	-v /my/custom:/etc/mysql/conf.d \  
	-e MYSQL_ROOT_PASSWORD=gang2019 \
	-e MYSQL_USER=gary \
	-e MYSQL_PASSWORD=gang2019 \
	-e MYSQL_DATABASE=exchange  \
	mysql:5.7 \
	--default-authentication-plugin=mysql_native_password

运行环境和配置

配置

配置文件路径：

Ubuntu：/etc/mysql/mysql.cond.d/mysqld.conf ；

CentOS：/etc/my.cnf；

数据存储路径

datadir=/var/lib/mysql

如果系统启用了SELinux或appamor，则直接修改路径配置将导致服务无法启动。在保证相应目录的文件权限设置正确的情况下，需要检查SELinux的策略配置。

MySQL :: MySQL 8.0 Reference Manual :: 6.7.4 SELinux File Context

版本信息

mysql --version  # mysql客户端版本信息
mysql            # 登录后客户端显示服务器版本信息

SHOW VARIABLES LIKE 'version';
SELECT VERSION() as version;
STATUS;                         # 包括服务器版本信息

目录信息

/etc/mysql/mysql.conf.d/mysqld.cnf # mysql-server配置文件

How To Move a MySQL Data Directory to a New Location on Ubuntu 18.04 | DigitalOcean

插件管理

SHOW PLUGINS;

查看MySQL服务状态

sudo netstat -nlp

使用系统服务管理程序（systemctl、service等）管理MySQL服务。

访问MySQL

命令行工具

登录

mysql -u <username> -p [<database>];
mysql -u root -p  # 使用root用户登录数据库的root账号

username表示要登录的用户的用户名，-p表示如果用户设置了密码，则在登录时要输入密码。

以下问题可能发生：

1. Shell登录用户密码验证失败：未创建本地访问账户（user@localhost）或本地账户与远程账户密码不一致。

2. 不允许主机访问：未对相应的主机/域名上的用户进行授权，需要在本机创建对应的用户账号。

3. root无法通过密码登录：为管理员账号设置密码（Generic Instructions）。

   Ubuntu系统下，MySQL 默认以安装软件的用户（通常为root）为数据的内置账户，通过系统中的用户身份进行登录验证（ auth_socket），无需MySQL用户密码。因此，首先尝试使用sudo mysql [-u root]命令进行登录（sudo命令表示以root用户登录数据库，其后的用户参数不一定要使用root，仅使用该用户的信息进行验证）；

   如果失败或者是需要从远程登录，则需要修改MySQL对用户的认证方式。在安全模式下启动MySQL：

   systemctl stop mysql  # => service mysql stop
   sudo mkdir -p /var/run/mysqld && chown mysql:mysql /var/run/mysqld  
   sudo mysqld_safe --skip-grant-tables &
   mysql -u root    # login without password
   service mysql restart
   

   ==如果未取消==skip-grant-tables，则无法使用网络连接访问（为了安全起见，服务器未开启网络监听端口）。

   修改数据库中root用户的==认证方式==和修改密码：

   USE mysql;
   UPDATE user SET plugin="mysql_native_password" WHERE User='root'; 
   ALTER USER root@localhost IDENTIFIED BY 'NewPassword'; 
   flush privileges;
   

   终止服务进程并重启服务再尝试登录。

4. warning: cannot change directory to /nonexistent: No such file or directory：https://stackoverflow.com/a/63040661/6571140。该问题导致用户登录无法认证。

   sudo usermod -d /var/lib/mysql/ mysql
   sudo systemctl start mysql.service  # service mysql start
   

5. 使用init的系统（例如WSL2）无法正常停止MySQL：为debian-sys-maint配置数据库账户。

   CREATE USER 'debian-sys-maint'@localhost identified by 'password'
   GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@localhost
   

6. Host xxx is blocked：使用mysqladmin -h MYSQL_HOST flush_hosts刷新客户端主机地址缓存。

远程访问

MySQL 8.0默认开放UNIX套接字/var/run/mysqld/mysqld.sock和本地TCP/IP套接字访问localhost/127.0.0.1；因此无法从远程机器连接到数据库（Connection refused error）。

A Unix socket file is used if you do not specify a host name or if you specify the special host name localhost.

修改MySQL Server配置文件，令bind-address=0.0.0.0，以允许本地回环地址以外的主机访问数据库。

注意：如果错误地将bind-address绑定到非本机IP将导致无法创建TCP套接字。

测试网络访问：

sudo mysqladmin version  
mysqladmin \ # no address option for local connect with root
    -h $(hostname)|<host_ip> [--port=3306] \ # TCP/IP with user@hostname
    --protocol=SOCKET --socket=/var/run/mysqld/mysqld.sock \ # UNIX SOCKET
    -u mysqluser \
    -p \ # specify user and password
    version 

--protocol=tcp,socket,pipe,memory

账户管理

创建删除角色

CREATE ROLE [IF NOT EXISTS] role [, role ...] 
DROP ROLE [IF EXISTs] role

给定IF NOT EXISTS或IF EXISTS，则在不满足条件时产生警告而非错误。

示例：

CREATE ROLE 'administrator', 'developer';
CREATE ROLE 'webapp'@'localhost';

创建用户

CREATE [OR REPLACE] USER [IF NOT EXISTS] user_name 
    [IDENTIFIED BY {'password' | PASSWORD 'password_hash'}] # 使用默认认证方法
   |[IDENTIFIED WITH auth_plugin ]                          # 显式指定认证方法（后续再设置密码）
   |[IDENTIFIED WITH auth_plugin BY PASSWORD('password')]   # mariadb使用USING->BY
   DEFAULT ROLE role;  # Only MySQL
RENAME USER old_user TO new_user;
SET DEFAULT ROLE
    {NONE | ALL | role [, role ...]}
    TO user[, user ...]
SET ROLE { 
	DEFAULT | NONE | ALL [except role[,...]] | role[, role ...]}

username用户名和域名组成'<username>'@'domain'（默认省略域名，等效于以%表示）中如果有特殊字符（例如-）则需要使用引号，密码必须置于引号中。

MySQL/Mariadb 5.x不支持同时设置密码和验证方法，可先创建用户并设定验证方法，随后设置密码。

SET ROLE设置当前用户当前会话的有效角色。

查看用户信息：

SELECT Host, User FROM mysql.user;

删除用户

DROP USER 'bloguser'@'localhost';

修改密码

ALTER USER '<username>'@'domain' IDENTIFIED 
      [WITH mysql_native_password] 
      BY '<password>';
SET PASSWORD FOR <user> = PASSWORD('pass_word')  # 设置密码Hash值

权限管理

授权给用户或角色

GRANT priv_type ON priv_level TO user_or_role;
priv_level: { *  | *.*  | db_name.*
  | db_name.tbl_name  | tbl_name  | db_name.routine_name };
GRANT role TO user_or_role;

priv_type表示对数据库的各类操作类型。

示例：

GRANT ALL [PRIVILEGES] ON *.* TO  'garywang';
GRANT ALL ON db1.* TO jeffrey@localhost;
GRANT SELECT, INSERT ON world.* TO 'role3';
GRANT 'role1', 'role2' TO user1, user2;

授权的对象必须存在，否则出错。

ERROR 1410 (42000): You are not allowed to create a user with GRANT.

SHOW GRANTS FOR 'bloguser'@'localhost';

撤销权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM '<username>';
REVOKE INSERT ON *.* FROM jeffrey@localhost;  # revoke insert privilage
REVOKE 'role1', 'role2' FROM user1, user2;
REVOKE SELECT ON world.* FROM role3;
mysql> FLUSH PRIVILEGES;

https://dev.mysql.com/doc/refman/8.0/en/grant.html

退出客户端：

exit;

SQL脚本

在MySQL命令行执行SQL脚本：

source path/to/mysql.sql

在终端执行MySQL命令或SQLSQL脚本：

mysql -u USER -p -e "sql_command"
mysql -u USER -p DATABASE < msyql.sql

SQL命令

• 每条命令以“;”或“\g”结束；
• 命令的关键字和标识符默认不区分大小写；

• 命令行输入可以使用“tab键”自动补全；
• 通过上下键可以查询输入历史；
• SQL 语法表述中“[]”中包含的内容是可选的，如果没有指明，则表示使用默认的命令；

在交互式命令行通过help命令，查看SQL命令文档。

help CREATE TABLE;
help SELECT;

数据库管理

SHOW DATABASES;
USE database_name;
SHOW TABLES;        -- 查看数据库的表
DESC table_name;    -- => describe 显示表的字段定义
SHOW COLLATION;     -- 显示内置的比较规则

使用SHOW CREATE TABLE查看创建分区的语句；

查看对象的定义

通过输出的语句，可重新创建相同定义的对象。

SHOW CREATE DATABASE  db_name \G;  # 显示数据库定义
SHOW CREATE TABLE     tb_name \G;  # 显示表的定义
SHOW CREATE PROCEDURE sp_name \G;  # 显示过程定义
SHOW CREATE FUNCTION  sp_name \G;

分区

every unique key on the table must use every column in the table's partitioning expression.

当声明unique key（primary key）时，其组成部分必须包含分区的列。

UNIQUE KEY (col1, col2)  -- col2 used for partitioning

Range Partition

CREATE TABLE table_name (column_name type contraints,...)
  PARTITION BY RANGE (column_name_x) (
     PARTITION part_name_a VALUES LESS THAN (n0),    # () 是必要的
     ...
     PARTITION part_name_x VALUES LESS THAN MAXVALUE # 不需要括号
  )

可以在创建表后（即使已经添加数据）再修改表的定义添加分区定义：

ALTER TABLE table_name PARTITION BY RANGE (column_name_x) (...)

也可以为表格添加一个或多个新的分区：

ALTER TABLE table_name ADD PARTITION (...)

划分已有分区（不会丢失数据）：

ALTER TABLE table_name REORGANIZE PARTITION exist_part INTO (
    PARTITION s0 VALUES LESS THAN (n00),
    PARTITION s1 VALUES LESS THAN (n01)
);

调整分区定义（只能调整连续分区）：

ALTER TABLE table_name REORGANIZE PARTITION p0,p1,... INTO (
    PARTITION p0 VALUES LESS THAN (n01)
);
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

List Partition

Hash Partition

Key Partition

分区信息

SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,
       PARTITION_ORDINAL_POSITION as part_idx,
       PARTITION_METHOD as method,
       PARTITION_EXPRESSION as part_expr,
       PARTITION_DESCRIPTION as part_desc,
       TABLE_ROWS as num_rows,
       CREATE_TIME 
   FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'table_name';

分区筛选

支持分区筛选的命令包括：SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA、LOAD XML。

SELECT * FROM table_name partition (p1,p2,...) WHERE ...

变量

用户变量

set @var_name = EXPRESSION,...;   -- => SELECT @test;

一条语句可设置多个变量。

参数和局部变量

CREATE PROCEDURE p(increment INT)
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 10 DO
    -- ... do work ...
    SET counter = counter + increment;
  END WHILE;
END//

系统变量

系统变量影响数据库行为，其中，全局变量影响当前会话以及其后建立的会话，会话变量仅影响当前会话。要设置永久性的系统变量，应该在选项文件中配置。

全局系统变量

SET GLOBAL max_connections = 1000;  -- => SET @@GLOBAL.max_connections = 1000;

要设置的系统变量必须是系统已经定义的，不能通过SET创建全局变量。

会话系统变量

SET SESSION sql_mode = 'TRADITIONAL'; 
-- => SET @@SESSION.sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';   
-- => SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';         
-- => SET @@sql_mode = 'TRADITIONAL';
SET max_join_size = DEFAULT;          
-- => SET max_join_size = @@GLOBAL.max_join_size;

显示系统变量

SELECT @@GLOBAL.sql_mode;           -- => 'TRADITIONAL';
SELECT @@sql_mode                   -- 局部变量
SHOW VARIABLES                      -- 显示所有系统变量的名称和值

过程

创建过程的语法如下：

-- CREATE ROUTINE privilege of DEFINER
delimiter //  --*
CREATE [DEFINER = user] PROCEDURE [IF NOT EXISTS] 
routine_name ([proc_parameter[,...]]) 
[characteristic ...] 
    routine_body
CREATE [DEFINER = user] FUNCTION [IF NOT EXISTS] 
routine_body_name ([func_parameter[,...]]) RETURNS type 
[characteristic ...] 
    routine_body
delimiter ;  --*

过程默认保存在当前使用的数据库，可显式指定关联数据库db_name.routine_name。

routine_body过程内容：可以为单条SQL语句，或由BEGIN...END语句块包含多条SQL语句。

*：过程中==包含多条SQL语句==时，过程本身的结束标识和过程内容routine_body中的语句结束标识不能一致，因此修改当前环境的结束标识为//。

PROCEDURE参数列表：[IN|OUT|INOUT] param_name type，默认为IN（输入参数），OUT（输出参数，初始值为NULL）可将值返回给调用者。

FUNCTION参数列表：param_name type，函数形式通过RETURN语句返回结果，RETURNS语句声明返回结果的类型。

调用过程

call db_name.routine_name(proc_parameter,...)

数据导入导出

SQL脚本

MySQL中如果表和表之间建立的外键约束，则无法删除表及修改表结构。解决方法是在Mysql中取消外键约束: SET FOREIGN_KEY_CHECKS=0；然后将原来表的数据导出到sql语句，重新创建此表后，再把数据使用sql导入，然后再设置外键约束: SET FOREIGN_KEY_CHECKS=1;

命令行导入导出工具

mysqldump生成SQL语句以重新申城原数据库对象的定义以及数据表的数据，用于备份数据库或传输到其他SQL服务器，mysqldump命令还可以输出CSV、其他分隔符文件或XML文件。

mysqldump -u root -p DATABASE_NAME [TABLE_NAME...] \
    --result-file=database_name.db   # 导出一个库的某些表
    --databases db_name ...          # 导出指定的库
    --all-databases                  # 导出所有库
    -d, --no-data                    # 不导出数据仅导出定义
mysqldump [options] --tab=dirname \  # 仅用于服务器所在机器，因为服务器要在指定目录创建文件
    --fields-terminated-by=... --fields-enclosed-by=... --fields-escaped-by=... \
    --lines-terminated-by=...  # mysqldump独立生成数据表创建文件和MYSQL服务导出数据文件

mysqldump使用和mysql相同参数连接数据库服务。

mysqldump可以方便查看和编辑导出的数据，适用于测试开发目的；但并不是一种快速或可扩展的备份大量数据的方法，因为恢复数据时执行SQL语句将产生大量插入、创建索引等磁盘I/O，导致恢复过程及其缓慢。对于大数据备份和恢复，可直接复制数据库文件目录。

问题

1. 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces：指定--no-tablespaces选项。

2. error: 1227: Access denied; you need (at least one of) the FILE privilege(s) for this operation when executing 'SELECT INTO OUTFILE'：指定全局文件权限GRANT FILE ON *.* TO user;

3. error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'：当指定--tab选项时，仅能从secure-file-priv指定的目录读写数据或在配置文件中将指定路径清空。

   SHOW VARIABLES LIKE "secure_file_priv"
   

   [mysqld]
   secure-file-priv = ""
   

mysql -u USER -p DB_NAME < database_name.db
mysql db_name < backup-file.sql
mysql -e "source /path-to-backup/backup-file.sql" db_name

导入文本数据（CSV）

mysqlimport [options] db_name textfile1 [textfile2 ...]

LOAD DATA [LOCAL] INFILE 'path/to/file.csv' INTO TABLE table_name
     FIELDS TERMINATED BY ',' 
     ENCLOSED BY '"'
     LINES TERMINATED BY '\n' 
     IGNORE 1 ROWS;  -- 忽略表头

如果使用--tab导出的数据，可分别导入.sql文件和文本文件。

MySQL命令行导入导出工具

MySQL连接器

Java

使用包管理器（rpm或deb）安装连接器的库还需要顺带安装依赖包。

========================================================================
 Package                       Arch     Version                    Size
========================================================================
Installing:
 mysql-connector-java          noarch   1:8.0.26-1.el7            2.7 M
Installing for dependencies:
 avahi-libs                    x86_64   0.6.31-20.el7              62 k
 copy-jdk-configs              noarch   3.3-10.el7_5               21 k
 cups-libs                     x86_64   1:1.6.3-51.el7            359 k
 java-1.8.0-openjdk-headless   x86_64   1:1.8.0.302.b08-0.el7_9    33 M
 javapackages-tools            noarch   3.4.1-11.el7               73 k
 libjpeg-turbo                 x86_64   1.2.90-8.el7              135 k
 libxslt                       x86_64   1.1.28-6.el7              242 k
 lksctp-tools                  x86_64   1.0.17-2.el7               88 k
 pcsc-lite-libs                x86_64   1.8.8-8.el7                34 k
 python-javapackages           noarch   3.4.1-11.el7               31 k
 python-lxml                   x86_64   3.2.1-4.el7               758 k
 tzdata-java                   noarch   2021a-1.el7               191 k

如果只需要连接器的库，可下载平台无关的档案文件。

常见问题

1. ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes：将键对应的列的数据类型长度减小。MySQL 经典案例分析：Specified key was too long - 云+社区 - 腾讯云 (tencent.com)。