mysql8

---

减少mysql8内存占用

变量	值	描述
performance_schema	OFF	资源配置动态监控,将这个关了内存少用200M
innodb_buffer_pool_chunk_size	32M	innodb缓冲池块大小,默认128M
innodb_buffer_pool_instances	1	innodb缓冲池实例个数
innodb_buffer_pool_size	32M	innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances,innodb缓冲池大小
innodb_log_buffer_size	4194304	日志缓存区4M,默认16M
table_open_cache	1000	缓存打开数据表个数,默认4000
table_definition_cache	900	400 + table_open_cache / 2表实例限制,减少大概100M内存占用
tmp_table_size	4194304	临时表大小限制4M,默认16M
key_buffer_size	8	MyISAM表缓存

/etc/my.cnf

performance_schema = OFF
innodb_buffer_pool_chunk_size = 32M
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 32M
innodb_log_buffer_size = 4M
table_open_cache = 1000
table_definition_cache = 900
tmp_table_size = 4M
key_buffer_size = 8

安装mysql8

// mysql8源地址:https://dev.mysql.com/downloads/file/?id=489467
wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm
// 安装mysql8数据源
yum install mysql80-community-release-el8-1.noarch.rpm -y
// 查看数据源
yum repolist enabled | grep "mysql.*-community.*"
// 禁用CentOS8自带mysql模块,否则会提示被模块过滤掉
yum module disable -y mysql
// 安装mysql
yum install mysql-community-server -y
// 开启mysql
service mysqld start
/bin/systemctl start mysqld.service
// 查看mysql的状态
service mysqld status
// 关闭mysql
service mysqld stop
/bin/systemctl stop mysqld.service

mysql8修改密码

// 显示mysql的随机密码
grep 'temporary password' /var/log/mysqld.log
// 登录mysql
mysql -u root -p
// 先修改成"Moments_2021",满足它的策略
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Moments_2021';
// 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';
// 修改密码长度：
set global validate_password.length=6;
// 修改密码等级：
set global validate_password.policy=0;
// 设置成自己想要的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

validate_password_policy

Policy	Tests Performed
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file

配置远程登录用户

// 删除用户
drop user 'Moments'@'%';
// 创建用户并设置密码
create user 'Moments'@'%' identified by '123456';
// 权限配置
grant all privileges on *.* to 'Moments'@'%' with grant option;
// 权限配置项立即生效
flush privileges;

MySQL said: Can't connect to MySQL server on 'xxxx' (60)

可能是阿里云的3306端口没有开放

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded

// 更改加密方式为mysql_native_password
alter user 'Moments'@'%' identified with mysql_native_password by '123456';
// 权限配置项立即生效
flush privileges;

grant

// 查询用户
select user,host from user;
// 删除用户
drop user 'Moments'@'127.0.0.1';
// 创建用户并设置密码
create user 'Moments'@'127.0.0.1' identified with mysql_native_password by 'Moments123456';
// 去掉ip限制,此时可以登录,会有一个默认的权限
update user set host='%' where user='Moments';
// 添加数据库权限,grant 权限 on 数据库对象 to 用户
grant all on laravel.* to 'Moments'@'%';
// 权限配置项立即生效
flush privileges;
// 2表实例限制
show grants for 'Moments'@'%';