maxzhao

不要害怕Exception和Error

MySQL8.0新特性-InnoDB增强

Edited on 2022-07-04 In DB , MySQL

本文地址：MySQL8.0新特性-InnoDB增强

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
 官方介绍

本文地址： https://github.com/maxzhao-it/blog/post/5476/

MySQL8.0新特性-JSON增强

Edited on 2022-07-04 In DB , MySQL

本文地址：MySQL8.0新特性-JSON增强

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
 官方介绍

本文地址： https://github.com/maxzhao-it/blog/post/53163/

前言

都说MySql8.x比5.7快两倍，可以参考一下。
但不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本）。数据备份方式还是可以的。

注意MySql8 的用户安全策略的改变
注意MySql8 编码格式
支撑内网服务器安装

准备

下载

# 打包下载
wget https://cdn.mysql.com//Downloads/MySQL-8.2/mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar
# 单独下载

wget https://cdn.mysql.com//Downloads/MySQL-8.2/mysql-community-server-8.2.0-1.el7.x86_64.rpm
wget https://cdn.mysql.com//Downloads/MySQL-8.2/mysql-community-libs-8.2.0-1.el7.x86_64.rpm
wget https://cdn.mysql.com//Downloads/MySQL-8.2/mysql-community-client-8.2.0-1.el7.x86_64.rpm

安装

先查看有没有 `mariadb`

1	rpm -qa \| grep mariadb

有就强制卸载

1	rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

强制安装

rpm -ivh mysql-community-common-8.2.0-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-server-8.2.0-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-8.2.0-1.el7.x86_64.rpm --nodeps --force
# 可选安装
rpm -ivh mysql-community-client-8.2.0-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-devel-8.2.0-1.el7.x86_64.rpm --nodeps --force

这时候查看安装

rpm -qa|grep mysql
mysql-community-server-8.2.0-1.el7.x86_64
mysql-community-client-8.2.0-1.el7.x86_64
mysql-community-libs-8.2.0-1.el7.x86_64

初始化配置

1	vim /etc/my.cnf

配置路径 /opt/db/

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
socket=/opt/db/mysql_data/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端口
port=3331 
datadir=/opt/db/mysql_data
socket=/opt/db/mysql_data/mysql.sock
log-error=/opt/db/mysql_log/mysqld.log
pid-file=/opt/db/mysqld/mysqld.pid
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
#排序规则
collation-server=utf8mb4_0900_ai_ci
#utf8mb4_0900_ai_ci  排序规则：ai 口音不敏感  ci 不区分大小写  ,默认支持表情符号
#utf8mb4_0900_ai_ci  属于 utf8mb4_unicode_ci 中的一种
#utf8mb4_general_ci  没有实现utf8mb4_unicode_ci 的排序规则。没有utf8mb4_unicode_ci 准备。但是比较和排序的时候更快
# 创建新表时将使用的默认存储引擎,innodb支持事物
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
lc-messages-dir=/usr/share/MySQL-8.2

rm -rf /opt/db/mysql/
rm -rf /opt/db/mysqld/
rm -rf /opt/db/mysql_data
rm -rf /opt/db/mysql_log
mkdir -p /opt/db/mysql/
mkdir -p /opt/db/mysqld/
mkdir -p /opt/db/mysql_data
mkdir -p /opt/db/mysql_log
chown mysql:mysql /opt/db/ -R
chmod 777 -R /opt/db/mysqld

sudo mysqld --initialize --user=mysql --basedir=/usr
setenforce 0
systemctl start mysqld.service
systemctl status mysqld.service
# 权限
# chmod 777 /opt/db/mysqld/mysqld.pid
# chmod 777 /opt/db/mysql_log/mysqld.log
# 开机自起
systemctl enable mysqld
cat /opt/db/mysql_log/mysqld.log

报错解决

mysqld: error while loading shared libraries: libaio.so.1

1	yum install libaio

Can't find error-message file '/usr/share/MySQL-8.2/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

1	lc-messages-dir=/usr/share/MySQL-8.2

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

1 2	rm -rf /var/lib/mysql/mysql.sock systemctl restart mysqld

查看安装日志中的密码

1	cat /opt/db/mysql_log/mysqld.log \| grep password

修改密码

flush privileges;
alter user 'root'@'localhost' identified  by "123456";
# alter user 'root'@'localhost' identified WITH caching_sha2_password by "123456";
#创建远程连接
create user root@'%' identified by '123456';
grant all privileges on *.* to root@'%';
flush privileges;

无密码的方式，可用于找回密码。

输入一百次也输入不对默认密码配置文件 MySQL 免密码登录编辑 MySQL 的配置文件

1	vim /etc/my.cnf

在 [mysqld]下添加

1 2	#在 [mysqld] 开头的下面一行加入下面这句 skip-grant-tables

修改密码(记得删除my.cnf文件的东西)

# 这里需要先刷新权限，不然会报错。
flush privileges;
alter user 'root'@'localhost' identified  by "123456";
# alter user 'root'@'localhost' identified WITH caching_sha2_password by "123456";
#创建远程连接
create user root@'%' identified by '123456';
grant all privileges on *.* to root@'%';
flush privileges;

或者

1
2
3

bin/mysql 
use mysql;
update user SET Password = 'new-password' WHERE User = 'root';

基本配置

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
socket=/opt/db/mysql_data/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端口
port = 3331 
datadir=/opt/db/mysql_data
socket=/opt/db/mysql_data/mysql.sock
log-error=/opt/db/mysql_log/mysqld.log
pid-file=/opt/db/mysqld/mysqld.pid
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
#排序规则
collation-server=utf8mb4_0900_ai_ci
#utf8mb4_0900_ai_ci  排序规则：ai 口音不敏感  ci 不区分大小写  ,默认支持表情符号
#utf8mb4_0900_ai_ci  属于 utf8mb4_unicode_ci 中的一种
#utf8mb4_general_ci  没有实现utf8mb4_unicode_ci 的排序规则。没有utf8mb4_unicode_ci 准备。但是比较和排序的时候更快
# 创建新表时将使用的默认存储引擎,innodb支持事物
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
lc-messages-dir=/usr/share/MySQL-8.2
# 不使用密码进入
# skip-grant-tables

推荐

MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
[官方介绍]([https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

本文地址： https://github.com/maxzhao-it/blog/post/26543/

Centos7 解压安装MYSQL8

Edited on 2022-07-04 In DB , MySQL

都说MySql8.x比5.7快两倍，可以参考一下。
但不支持从 MySQL 8.0 降级到 MySQL 5.7（或从某个 MySQL 8.0 版本降级到任意一个更早的 MySQL 8.0 版本）。数据备份方式还是可以的。

注意MySql8 的用户安全策略的改变
注意MySql8 编码格式

这里安装的是MySql 8

下载地址：https://dev.mysql.com/downloads/mysql/8.0.html

我这里下载的是mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

一、解压mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

首先解压，输入mysql按tab补齐，/home/mysql是自己想要安装的目录.

没有权限用su输入root密码，用root执行。

tar -xvf报gz错误，就用tar -zxvf 解压命令

mkdir /opt/db
cd /opt/db
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.26-el7-x86_64.tar
tar -xvf mysql-8.0.26-el7-x86_64.tar
tar -zxvf mysql-8.0.26-el7-x86_64.tar.gz
#tar -xvf mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
#mv mysql-8.0.13-linux-glibc2.12-x86_64 /home/mysql
cd /home/mysql

二、可以先把mariadb卸载了

mariadb其实与mysql是一样的，只不过mysql之父离开了Oracle，觉得依靠Oracle不靠谱，想做一个完全开源的数据库，这就是mariadb，mariadb的端口还是3306。

rpm -qa|grep mariadb
#mariadb-libs-5.5.60-1.el7_5.x86_64
rpm -e --nodeps mariadb-libs  #tab补齐
#删除etc目录下的my.cnf文件
#可以先看一下my.cnf文件里面写的什么
cat /etc/my.cnf
#如果mariadb没有启动过，里面的文件夹和文件都不用看了，都是不存在的(也可以不删除，后面还会创建)
rm /etc/my.cnf
#检查mysql有没有安装
rpm -qa | grep mysql

三、检查mysql组和用户是否存在，如无创建

cat /etc/group | grep mysql 
cat /etc/passwd | grep mysql
#没有就u添加
groupadd mysql
useradd -g mysql mysql 
passwd mysql

四、在/etc下创建my.cnf

编码在5.5.3以上都会支持utf8mb4（utf8 most byte 8），意思是支持4byte的不常用汉字和Emoji表情，毕竟65535还真的是不够用。

对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

1	vim /etc/my.cnf

删除所有内容，添加下面内容

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
socket=/opt/db/mysql_data/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端口
port = 3335
datadir=/opt/db/mysql_data
socket=/opt/db/mysql_data/mysql.sock
log-error=/opt/db/mysql_log/mysqld.log
pid-file=/opt/db/mysqld/mysqld.pid
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
#排序规则
collation-server=utf8mb4_0900_ai_ci
#utf8mb4_0900_ai_ci  排序规则：ai 口音不敏感  ci 不区分大小写  ,默认支持表情符号
#utf8mb4_0900_ai_ci  属于 utf8mb4_unicode_ci 中的一种
#utf8mb4_general_ci  没有实现utf8mb4_unicode_ci 的排序规则。没有utf8mb4_unicode_ci 准备。但是比较和排序的时候更快
# 创建新表时将使用的默认存储引擎,innodb支持事物
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M
lc-messages-dir=/opt/db/mysql8/share

五、安装

cd /opt/db/
mv mysql-8.0.26-el7-x86_64  mysql8
cd mysql8
#在mysql中添加data文件夹，init失败时，从这里开始
rm -rf /opt/db/mysql/
rm -rf /opt/db/mysqld/
rm -rf /opt/db/mysql_data
rm -rf /opt/db/mysql_log
mkdir -p /opt/db/mysql/
mkdir -p /opt/db/mysqld/
mkdir -p /opt/db/mysql_data
mkdir -p /opt/db/mysql_log
chown mysql:mysql /opt/db/ -R
chmod 777 -R /opt/db/mysqld
# 已经不用的命令
#bin/mysql_install_db --user=mysql --basedir=/home/mysql/ --datadir=/home/mysql/data/
# yum -y install numactl
bin/mysqld --initialize --user=mysql  --datadir=/home/mysql-8.0.26-el7-x86_64/
# 查看错误日志
cat /opt/db/mysql_log/mysqld.log |grep ERROR

安装失败，可以看一下mysql 账户对/opt/db/mysql下的文件有没有读写权限

如果执行报错UTF8MB4，就把/etc/my.cnf的utf8换成UTF8MB4

六、连接

查看密码

1	cat /opt/db/mysql_log/mysqld.log \|grep password

启动(&是放在后台执行)

1 2	bin/mysqld_safe --user=mysql & bin/mysql -uroot -p

然后执行(但是如果使用无密码的方式登陆，就不可以使用下面令名)

alter user 'root'@'localhost' identified  by "123456";
# alter user 'root'@'localhost' identified WITH caching_sha2_password by "123456";
#创建远程连接
create user root@'%' identified by '123456';
grant all privileges on *.* to root@'%';
flush privileges;

无密码的方式，可用于找回密码。

配置文件 MySQL 免密码登录

编辑 MySQL 的配置文件

1	vim /etc/my.cnf

在 [mysqld]下添加

1 2	#在 [mysqld] 开头的下面一行加入下面这句 skip-grant-tables

修改密码(记得删除my.cnf文件的东西)

# 这里需要先刷新权限，不然会报错。
flush privileges;
alter user 'root'@'localhost' identified  by "123456";
# alter user 'root'@'localhost' identified WITH caching_sha2_password by "123456";
#创建远程连接
create user root@'%' identified by '123456';
grant all privileges on *.* to root@'%';
flush privileges;

或者

1
2
3

bin/mysql 
use mysql;
update user SET Password = 'new-password' WHERE User = 'root';

七、后续操作

创建软连接

1	ln -s /home/mysql/bin/* /usr/local/bin/

/usr/local/bin目录是给用户放置自己的可执行程序的地方，推荐放在这里，不会被系统升级而覆盖同名文件。

添加系统环境变量

1	vim /etc/profile

加入配置

添加服务

将服务文件拷贝到init.d下，并重命名为mysqld
cp support-files/mysql.server /etc/init.d/mysqld
赋予可执行权限
chmod +x /etc/init.d/mysqld
添加服务
chkconfig --add mysqld
关闭firewall,安装iptables可选（比如服务器就需要开放3306）

本文地址： Centos7 解压安装MYSQL8

推荐

MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
[官方介绍]([https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

本文地址： https://github.com/maxzhao-it/blog/post/26542/

三种新的索引方式

1、隐藏索引

MySQL8.0 支持隐藏索引（invisible index），不可见索引
隐藏索引不会被优化器使用，但需要维护。
应用场景：软删除、灰度发布。
- 软删除：不确定当前索引是否需要删除的时候，软删除，不会彻底删除，可以恢复索引，不需要重新创建，但需要维护。
- 灰度发布：测试索引，对当前数据库不会参生太多影响，确认有效后，可以取消隐藏，改变为正常索引。

操作：

create table app_user (
pkid int,
age int
);
-- 正常索引
create index age_idx on app_user(age) ;
-- 隐藏索引 ，主键不可以设置尾隐藏索引
create index id_idx on app_user(pkid) invisible;
-- 有一个参数Visible为NO
show index from app_user;
-- 查询优化器对索引的使用情况
-- 会使用索引
explain select * from app_user where age=18;
-- 不会使用索引
explain select * from app_user where pkid=1;
-- 查询优化器的隐藏索引的开关
select @@optimizer_switch\G
-- 查询优化器使用隐藏索引，在当前会话中
set session optimizer_switch="use_invisible_indexes=on";
-- 打开之后可以使用索引
explain select * from app_user where pkid=1;
-- 设置索引可见
alter table app_user index id_idx visiblle;
-- 设置索引隐藏
alter table app_user index id_idx invisiblle;

2、降序索引

MySQL8.0真正支持降序索引（descending index）。
只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。
MySQL8.0不再对GROUP BY操作进行隐式排序，也就是说，排序必须要使用ORDER BY。

操作：

create table app_dept
(
pkid int,
num int,
cou int,
index idx1(num asc,cou desc)
);
-- 在5.7中是没有desc的，只有8.0才会有desc
show cteate table app_dept\G
insert into app_dept values(1,1,300),(2,6,500),(5,1,256),(3,4,400);
-- 查询优化器使用索引的情况，会发现使用当前索引，但不用额外的排序（using filesort）操作
explain select * from app_dept order by num,cou desc;
-- 反顺序查询，只会出现反向索引扫描（backward index scan），不会重新排序
explain select * from app_dept order by num desc,cou ;
-- GROUP BY 没有默认排序
select count(*) ,cou from app_dept group by cou;

3、函数索引

MySQL8.0支持在索引中使用函数（表达式）的值。
支持降序索引，支持JSON数据索引。
函数索引基于虚拟列功能实现。

create table t1(
c1 varchar(100),
c2 varchar(100)
);
create index idx1 on t1(c1);
-- 创建函数索引
create index fun_idx2 on t1(UPPER(c1);
show index from t1\G
-- 当使用函数时候，就不会走当前普通索引
explain select * from t1 where upper(c1)='A';
-- 走当前函数索引
explain select * from t1 where upper(c2)='A';
-- 添加一个 计算列,并未该列实现索引，虚拟列实现函数索引，
alter table t1 add column c3 varchar(100) generated always as (upper(c1));




-- 创建JSON数据索引测试，data->>'$.name' as char(30) 意思是取当前name值，类型尾char
create table emp(
data json,
index((CAST(data->>'$.name' as char(30))))
);

show index from emp\G
-- 当前就会使用JSON索引
explain select * from emp where CAST(data->>'$.name' as char(30))='A';

本文地址：MySQL8.0新特性-新的索引方式

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
 官方介绍

本文地址： https://github.com/maxzhao-it/blog/post/64934/

MySQL8.0备份和还原

Edited on 2022-09-14 In DB , MySQL

大家的需求都很清楚了，那么我们直接来看如何备份和还原。

下面的命令基于 Manjaro Linux，在 Windows 下添加命令的 .exe后缀。

备份

查看备份命令参数 : mysqldump --help

下面的备份的脚本中会带有 drop create insert

单库备份

1	mysqldump -h127.0.0.1 -uroot -p [OPTIONS] [数据库名称] >/var/xxx地址.sql

单库中单\多表备份

1	mysqldump -h127.0.0.1 -uroot -p [OPTIONS] [数据库名称] [tables] >/var/xxx地址.sql

多库备份

1	mysqldump -h127.0.0.1 -uroot -p [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] >/var/xxx地址.sql

全库备份

1	mysqldump -h127.0.0.1 -uroot -p [OPTIONS] --all-databases [OPTIONS] >/var/xxx地址.sql

常用参数

查看备份命令参数 : mysqldump --help

下面列出常用参数

全称	简写	含义	默认
–all-tablespaces	-Y	备份表空间	FALSE
–no-tablespaces	-y	不备份	FALSE
–add-database	-A	全库备份	FALSE
–add-drop-databas		在建库之前drop	FALSE
–add-drop-trigger		在建触发器之前drop	FALSE
–add-drop-table		在建表之前drop	TRUE
–no-create-info	-t	无create table语句	FALSE
–no-data	-d	无数据	FALSE
no-create-db	-n	无建库语句	FALSE

正常的全库备份

1	mysqldump -h127.0.0.1 -umaxzhao -p -B db_name >backup/mysql_db_name.sql

设置编码

查看编码

1
2
3

-- 找到character_set_database
show
variables like '%char%';

设置编码

1	mysqldump --default-character-set=utf8mb4

`PowerShell`导出报错

可以直接指定文件，不适用 shell 的 > 输出。

1	--result-file=1.sql

PowerShell 重定向导出的结果是 utf-16

数据还原

在命令行执行

1	mysql -h127.0.0.1 -uroot -p < /var/备份文件.sql

在 SQL 指令中执行

1	source /var/备份文件.sql

错误解决

`ERROR at line : Unknown command '\''.`

1	mysql -h127.0.0.1 -uroot -p --default-character-set=utf8 < /var/备份文件.sql

推荐

MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
[官方介绍]([https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

本文地址： https://github.com/maxzhao-it/blog/post/49206/

基本配置文件 `my.cnf`

[mysqld]
basedir=D:\mysql
datadir=D:\mysql\data
port=3336
character-set-server=utf8mb4
# 下面这个选项是可以选择的
#default_authentication_plugin=mysql_native_password
#排序规则
collation-server=utf8mb4_0900_ai_ci
#utf8mb4_0900_ai_ci  排序规则：ai 口音不敏感  ci 不区分大小写  ,默认支持表情符号
#utf8mb4_0900_ai_ci  属于 utf8mb4_unicode_ci 中的一种
#utf8mb4_general_ci  没有实现utf8mb4_unicode_ci 的排序规则。没有utf8mb4_unicode_ci 准备。但是比较和排序的时候更快
[mysql]
default-character-set=utf8mb4
# 不使用密码进入
# skip-grant-table
[client]
default-character-set=utf8mb4

数据库初始化：mysqld --initialize

libicuuc.so.63最后说;

数据库初始化后随机密码在日志文件中hostname.err，需要使用该密码登录并修改密码。

1
2
3

alter user 'maxzhao'@'localhost' identified by "maxzhao";
alter user 'maxzhao'@'localhost' identified with mysql_native_password by "maxzhao";
alter user 'maxzhao'@'localhost' identified with caching_sha2_password by "maxzhao";

MySQL8默认的认证插件是 caching_sha2_password，很多客户端都不支持，可将默认的认证插件修改为mysql_native_password，在配置文件/etc/my.cnf
中配置default_authentication_plugin=mysql_native_password。也有一部分linux配置文件在/etc/mysql/my.cnf。

1、用户创建和授权（分离）

创建账号、分配权限

CREATE USER 'maxzhao'@'localhost' IDENTIFIED BY 'maxzhao';
grant all privileges on *.* TO 'maxzhao'@'localhost' WITH GRANT OPTION;

CREATE USER 'maxzhao'@'%' IDENTIFIED BY 'maxzhao';
GRANT ALL PRIVILEGES ON *.* TO 'maxzhao'@'%' WITH GRANT OPTION;

CREATE USER 'maxzhao'@'localhost' IDENTIFIED BY 'maxzhao';
GRANT RELOAD,PROCESS ON *.* TO 'maxzhao'@'%';
Flush privileges;
-- #显示账号及权限相关信息
SHOW GRANTS FOR 'maxzhao'@'localhost';

SHOW CREATE USER 'maxzhao'@'localhost';

-- 查看用户
select host,user from mysql.user;

-- mysql8添加的新的默认用户
-- mysql.infoschema

2、认证插件更新

MySQL8.0 中默认的身份认证插件是 caching_sha2_password，替代类之前的mysql_native_password 。

具体配置开篇降讲到了。

查看：

show variables like ‘default_authentication_plugin;
select user,host,plugin from mysql.user;

更新：

在配置文件中修改（如上）
alter user 'maxzhao'@'%' identified with mysql_native_password by 'maxzhao';

3、密码管理

MySQL8.0允许限制重复使用以前的密码，当用root权限修改密码时，不受权限``限制。

配置文件`/etc/mysql/my.cnf`或者 `/etc/my.cnf/`中的参数（需要重启服务器生效）：

# 不能与以前3次密码重复。
password_history=3   
# 不能与3天内密码重复。
password_reuse_interval=3
# 默认为`off`，在修改密码时，是否需要当前密码。
password_require_current=ON

当password_require_current=ON的时候，修改密码的SQL语句为：

1	alter user maxzhao identified by 'maxzhao' replace 'maxzhao';

在环境变量中配置：

-- 当前会生效，重启后失效
set global
-- 8.0之后，会把环境变量持久话，重启后不会失效
set persist password_history=4
-- 查看变量
show variables like 'password_history';

MySQL8.0的持久化操作实现很简单，会把当前持久化的边境变量写入/var/lib/mysql/mysqld-auto.cnf中。

用户级别的变量

alter user 'maxzhao'@'localhost' password history 5;
-- 查看表结构
desc mysql.user;
-- 查看当前用户策略
select user,host,Password_reuse_history from mysql.user;
-- 查看mysql密码历史记录
desc mysql.password_history;

二进制日志

1
2
3

-- log_bin
-- 查看变量
show variables like '%log_bin%';

4、角色管理（新功能）

极大的简化了用户授权的管理。

授权命令

-- 创建角色
create role 'role_w';
select host,user,authentication_string from mysql.user ;
-- 给角色授权
grant insert,update,delete,select on *.* to 'role_w';
-- 创建用户
create user 'maxzhao_1' identified by 'maxzhao';
-- 授角色权限
grant 'role_w'  to 'maxzhao_1';
-- 查看用户权限
show grants from 'maxzhao_1';
show grants from 'maxzhao_1' using 'role_w';
-- 用户：查询用户的当前角色，会发现当前尾NONE
select currnt_role();
-- 用户：我们需要设置当前角色
set role 'role_w';
-- 为用户设置默认角色
set default role 'role_w' to 'maxzhao_1';
set default tole all to 'maxzhao_1';
-- 查看用户角色关联信息
select * from mysql.default_role;
-- 用户所授予的角色信息
select * from mysql.role_edges;

撤销权限命令：

1	revoke insert,update on . from ‘role_w';

基本操作语句

-- 添加字段
alter table app_user add app_role_ids varchar(2000);
-- 修改字段
alter table app_user modify app_role_ids varchar(2000) null comment '用户角色表';

本文地址：MySQL8.0创建用户及其配置

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
 官方介绍

ArchLinux下安装的问题

libicuuc.so.63问题

这里在 arch linux
下有可能会出现 mysqld: error while loading shared libraries: libicuuc.so.63: cannot open shared object file: No such file or directory
，需要下载 icu63，用软件管理器下载很方便，或者添加 yaourt下载也很方便。

arch-gnome 编程环境和其它环境安装

wget http://download.icu-project.org/files/icu4c/63.1/icu4c-63_1-src.tgz
tar -zxvf icu4c-63_1-src.tgz
cd icu/source
./configure --prefix=/usr          
make
# make 需要 sudo pacman -S make
cd lib
cp libicuuc.so.63*  /usr/lib
cp libicui18n.so.63* /usr/lib
cp libicudata.so.63* /usr/lib

文件报错问题

1
2
3

cd /var/lib/mysql/
rm *
mysqld --initialize-insecure

启动报错

1
2
3

# [InnoDB] The innodb_system data file 'ibdata1' must be writable

chown -R mysql:mysql  /usr/lib/mysql

本文地址：MySQL8.0创建用户及其配置

本文地址： https://github.com/maxzhao-it/blog/post/14082/

MySQL8.0新特性-通用表表达式（CTE）

Edited on 2022-07-04 In DB , MySQL

通用表表达式与派生表类似。

CTE可以多次引用，并且可以引用其它CTE，可以递归。

CTE支持SIUD等语句。

1、非递归的CTE

通过一个简单的实例来了解一下：

select  * from (select 1) as cte1;
-- CTE 形式
with cte1 as (select 1)
select * from cte1;
-- CTE 更复杂的方式
with cte1(id) as (select 1)
cte2(id) as (select id+1 from cte1)
select * from cte1 join cte2;

2、递归CTE

通过一个简单的实例来了解一下：

with recursive cte(n) as
(
select 1 union all
select n+1 from cte where n<5
)
select * from cte;

如图所示：

实例演示：

-- 初始化
create table app_user
(
id int,
name varchar(10),
parent_id int
);
insert into app_user values(12,'jack',15),(32,'luc',15),(416,'halun',12),(15,'susan',61),(61,'mack',NULL);
select * from app_user order by id;
-- 管理关系
with recursive app_user_path(id,name,path) as
(
select id,name,cast(id as char(200)) from app_user
    where parent_id is null
    union all
    select u.id,u.name,concat(up.path,',',u.id)
    from app_user_path as up join app_user as u
    on up.id = u.parent_id
)
select * from app_user_path order by path;

限制参数

cte_max_recursion_depth 递归深度
max_execution_time 最大执行时间

-- 一个死循环的sql
with recursive cte(n) as
(
select 1 union all
select n+1 from cte
)
select * from cte;
-- ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
-- 系统默认限制为1000次
show variables like 'cte_max%';
-- 设置会话级别的参数
set session cte_max_recursion_depth=100;
-- 当前会生效，重启后失效
set global cte_max_recursion_depth=100;
-- 8.0之后，会把环境变量持久话，重启后不会失效
-- MySQL8.0的持久化操作实现很简单，会把当前持久化的边境变量写入/var/lib/mysql/mysqld-auto.cnf中。
set persist cte_max_recursion_depth=100;

简单的测试：实现斐波那契

with recursive cte(n,m) as
(
select 1,1 union all
select n+m,n from cte
    where m<20
)
select m from cte;

是不是感觉很简单，我第一次写这个的时候用了1分钟，大家呢？

本文地址：MySQL8.0新特性-通用表表达式（CTE）

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
 官方介绍

本文地址： https://github.com/maxzhao-it/blog/post/41258/

简介

窗口函数与分组聚合函数类似，但是每行数据都生成一个结果。

聚合窗口函数： SUM、AVG、COUNT、MAX、MIN ……

-- 创建表
create table sales
(
year int,
country varchar(20),
goods varchar(50),
profit int
);
insert into sales values(2018,'c1','g1',314),(2016,'c1','g1',33),
(2017,'c1','g2',31),
(2016,'c2','g1',331),
(2017,'c2','g2',343),
(2018,'c2','g1',32),
(2016,'c3','g1',52),
(2017,'c3','g2',312),
(2018,'c3','g1',53);

普通函数

--正常情况下的计算不同国家的总利润：
select country,sum(profit) as all_profit
from sales group by country
order by country;
-- 分组聚合函数的写法，不需要手动分组
select year,country,goods,profit,
sum(profit) OVER (partition by country) as all_profit
from sales
order by country,year,goods,profit;

专用窗口函数

1
2
3

-- 准备数据
create table numbers(val int);
insert into numbers values(1),(2),(3),(3),(3),(3),(4),(5),(5),(6),(6),(7),(8),(8),(9);

序号函数

显示当前行号

ROW_NUMBER()：排序
RANK()
DENSE_RANK()

-- 正常排序
select val,row_number() over (order by val) as 'row_number' from numbers;
-- 如果val相同，则排序相同，下一个排序号会出现间隙，步长不一定为1
select val,rank() over (order by val) as 'rank' from numbers;
-- 如果val相同，则排序相同，排序号之间没有间隙，步长为1
select val,dense_rank() over (order by val) as 'dense_rank' from numbers;

头尾\前后函数

FIRST_VALUE()
LAST_VALUE()
LEAD()
LAG()

select val,first_value(val) over (order by val) as 'first'
,last_value(val)  over (order by val) as 'last'
,lead(val,1) over (order by val) as 'lead'
,lag(val,1) over (order by val)  as  'lag'
from numbers;

分布函数

CUME_DIST()
- 用途：分组内小于等于当前rank值的行数/分组内总行数，这个函数比percen_rank使用场景更多。
- 应用场景：大于等于当前val的记录比例有多少。
PERCENT_RANK()
- 用法：和之前的RANK()函数相关，每行按照如下公式进行计算： (rank - 1) / (rows - 1) 其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

select val,rank() over (order by val) as 'rank'
,percent_rank() over (order by val) as 'percent_rank'
,cume_dist() over (order by val)  as 'cume_dist'
from numbers;

数据区间函数

NTH_VALUE(expr,n)
- 用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。
- 应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。
NTILE()
- 用途：将分区中的有序数据分为n个桶，记录桶号。
- 应用场景：将每个用户的订单按照订单金额分成3组。

select year,profit,nth_value(profit,1)  over w as 'nth_1'
,nth_value(profit,2) over w as 'nth_2'
,nth_value(profit,3) over w as 'nth_3'
from sales
WINDOW w as (partition by year order by profit)
order by year,profit;

select year,profit,ntile(1)  over w as 'ntile_1'
,ntile(2) over w as 'ntile_2'
,ntile(3) over w as 'ntile_3'
,ntile(5) over w as 'ntile_3'
from sales
WINDOW w as (partition by year order by profit)
order by year,profit;

本文地址：MySQL8.0新特性-窗口函数

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
 官方介绍

本文地址： https://github.com/maxzhao-it/blog/post/33720/

12.进入mysql操作行，为root用户设置新密码（小编设为rootroot）：
alter user 'root'@'localhost' identified by 'maxzhao';
13.设置允许远程连接数据库，命令如下：
connect mysql;
update user set user.Host='%' where user.User='root';
14.刷新权限，命令如下：
flush privileges;
exit;

基本配置

[mysqld]
basedir=D:\mysql
datadir=D:\mysql\data
port=3336
character-set-server=utf8mb4
#排序规则
collation-server=utf8mb4_0900_ai_ci
#utf8mb4_0900_ai_ci  排序规则：ai 口音不敏感  ci 不区分大小写  ,默认支持表情符号
#utf8mb4_0900_ai_ci  属于 utf8mb4_unicode_ci 中的一种
#utf8mb4_general_ci  没有实现utf8mb4_unicode_ci 的排序规则。没有utf8mb4_unicode_ci 准备。但是比较和排序的时候更快
# 下面这个选项是可以选择的
#default_authentication_plugin=mysql_native_password

[mysql]
default-character-set=utf8mb4
# 不使用密码进入
# skip-grant-table

[client]
default-character-set=utf8mb4

本文地址：解压安装MySQL5.7（Linux）

推荐

MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
[官方介绍]([https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

本文地址： https://github.com/maxzhao-it/blog/post/24766/

前言

准备

下载

安装

先查看有没有 mariadb

有就强制卸载

强制安装

这时候查看安装

初始化配置

报错解决

查看安装日志中的密码

登录

修改密码

无密码的方式，可用于找回密码。

基本配置

这里安装的是MySql 8

一、解压mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

二、可以先把mariadb卸载了

三、检查mysql组和用户是否存在，如无创建

四、在/etc下创建my.cnf

五、安装

六、连接

查看密码

无密码的方式，可用于找回密码。

七、后续操作

创建软连接

添加系统环境变量

添加服务

三种新的索引方式

1、隐藏索引

2、降序索引

3、函数索引

备份

单库备份

单库中单\多表备份

多库备份

全库备份

常用参数

设置编码

PowerShell导出报错

数据还原

在命令行执行

在 SQL 指令中执行

错误解决

ERROR at line : Unknown command '\''.

基本配置文件 my.cnf

1、用户创建和授权（分离）

创建账号、分配权限

2、认证插件更新

3、密码管理

配置文件/etc/mysql/my.cnf或者 /etc/my.cnf/中的参数（需要重启服务器生效）：

在环境变量中配置：

用户级别的变量

4、角色管理（新功能）

授权命令

基本操作语句

ArchLinux下安装的问题

libicuuc.so.63问题

文件报错问题

启动报错

1、非递归的CTE

通过一个简单的实例来了解一下：

2、递归CTE

通过一个简单的实例来了解一下：

实例演示：

限制参数

简单的测试：实现斐波那契

简介

普通函数

专用窗口函数

序号函数

头尾\前后函数

分布函数

数据区间函数

基本配置

先查看有没有 `mariadb`

`PowerShell`导出报错

`ERROR at line : Unknown command '\''.`

基本配置文件 `my.cnf`

配置文件`/etc/mysql/my.cnf`或者 `/etc/my.cnf/`中的参数（需要重启服务器生效）：