Skip to main content

MySQL8 备忘

配置​

文件名为my.ini,放置于根目录下

[mysqld]
# 端口
port=3386

# 安装目录
basedir=C:\mysql-8.0.30-winx64

# 数据目录(如果启动报错,需手动建立此目录)
datadir=C:\mysql-8.0.30-winx64\data

# 最大连接数
max_connections=200

# 连接失败次数限制,一定程度的防攻击
max_connect_errors=10

# 字符集
# character-set-server=utf8
character-set-server=utf8mb4

# 引擎
default-storage-engine=INNODB

# 模式
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 密码插件
default_authentication_plugin=mysql_native_password

# 二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者
log_bin_trust_function_creators = 1

[mysql]
# 客户端字符集
; default-character-set=utf8
default-character-set=utf8mb4

另一个配置，供参考

[mysqld]
# 设置默认认证插件为原先策略(mysql_native_password)，以保持与旧版本MySQL的兼容性
default-authentication-plugin=mysql_native_password

# 设置默认存储引擎为InnoDB
default-storage-engine=INNODB

# 服务端编码设置为utf8mb4，支持存储和处理Unicode字符集
character-set-server=utf8mb4

# 客户端编码设置为utf8mb4
character_set_client=utf8mb4

# 不进行客户端握手，强制使用指定的字符集
character-set-client-handshake=FALSE

# 建立连接使用的编码设置为utf8mb4
character_set_connection=utf8mb4

# 设置服务器默认排序规则为utf8mb4_unicode_ci
collation-server=utf8mb4_unicode_ci

# 初始化连接时设置字符集为utf8mb4
init_connect='SET NAMES utf8mb4'

# 启用显式默认值为时间戳列
explicit_defaults_for_timestamp=true

# 允许二进制日志中使用函数创建器
log_bin_trust_function_creators=1

[client]
# 设置客户端默认字符集为utf8mb4
default-character-set=utf8mb4

[mysql]
# 设置mysql命令行客户端默认字符集为utf8mb4
default-character-set=utf8mb4

服务​

服务​

  • 注册为系统服务
cd C:\mysql-5.7.26-winx64\bin
# 先初始化
mysqld --initialize
# 注册服务
mysqld --install MySql8
  • 开启服务
net start MySql8

# 查看服务是否开启
services.msc
  • 移除服务
net stop MySql8
sc delete MySql8
  • 查看错误日志
mysqld --console

初始化​

# 随机密码
mysqld --initialize
  • 查看密码 打开C:\mysql-5.7.26-winx64\data\WIN-**.err文件,找到这样一行:
2022-07-04T03:24:12.915341Z 1 [Note] A temporary password is generated for root@localhost: EZRW8rWPni_O
  • 登录
# 使用前面找到的随机密码登录
mysql -uroot -P53306 -p

修改密码​

alter user `root`@`localhost` identified by 'ajroot';

基础操作​

创建用户​

-- 用户名: admin
-- 数据库: yufu
use mysql;

-- 查看用户
-- select * from user;

create USER "admin"@"%" IDENTIFIED by 'Yfty!23456';
--create user 'admin'@'%' IDENTIFIED with mysql_native_password by 'password';
grant all privileges on yufu.* to 'admin'@'%';
flush privileges;

-- https://zhuanlan.zhihu.com/p/210259396

修改用户权限​

-- 用户名: admin
use mysql;
update user set Host='%' where user='admin';

删除用户​

-- 用户名: admin
drop user admin;

创建数据库​

-- 数据库: db
CREATE DATABASE db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

清空数据库​

-- 数据库: db
drop database db;

Docker​

在docker中启动mysql8,即使配置了utf8mb4,仍然可能乱码,参考Docker Mysql DB 初始化 UTF8 乱码问题的解决,在docker-compose.yml中增加配置LANG=C.UTF-8,如:

version: '3.3'  
services:  
  mysql8:  
    image: mysql:8.0.36  
    container_name: mlcache-mysql  
    privileged: true  
    restart: always  
    environment:  
      - TZ=Asia/Shanghai  
      - MYSQL_ROOT_PASSWORD=th123456  
      - MYSQL_DATABASE=mlcache_sample  
      - LANG=C.UTF-8  
    volumes:  
      - ./data/mysql/data/:/var/lib/mysql/  
      - ../mysql/init/:/docker-entrypoint-initdb.d/  
      - ../mysql/conf/:/etc/mysql/conf.d/  
    ports:  
      - 3386:3306

常用操作​

展示所有表有多少条数据​

SELECT table_name, table_rows
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='yfty'
ORDER BY table_rows DESC ;

导出类Excel表字段​

错误提示​

SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = "error!";

错误​

log_bin_trust_function_creators​

  • 异常如下:
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) - SQLException
  • 解决方案1 修改my.cnf配置
log_bin_trust_function_creators = 1
  • 解决方案2 执行sql:
SET GLOBAL log_bin_trust_function_creators = 1;

which is not functionally dependent on columns in group by clause​

select @@global.sql_mode;

去掉里面的 ONLY_FULL_GROUP_BY 模式，然后set 回去

set @@global.sql_mode = "查出来的值，去掉 ONLY_FULL_GROUP_BY"

或者,配置:

[mysqld]
sql_mode="查出来的值，去掉 ONLY_FULL_GROUP_BY"

Authentication plugin 'caching_sha2_password' cannot be loaded' 找不到指定的模块​

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';