【踩坑记录】初使用mysql8记录
- 一、设置密码时会遇到“坑”
- 前言
- 二、被远程连接时出现的“坑”
- 前言
- 三、navicat 连接失败的“坑”
- 前言
- 四、关于mysql的性能参数调整（此次是降低mysql的性能，目的是降低mysql的内存占用率，毕竟才1G的内存）
- 前言
- 正文开始
- 运维
- 优化mysql的内存占用
- 本文结束...
一、设置密码时会遇到“坑”
前言
mysql8 默认密码长度是8，并且安全等级比较高，需要vim 使用免登陆模式，将这里参数改一下 **↓ ↓**# 先登录mysql：
mysql -uroot -p
1、mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 | -- 默认长度：8
| validate_password.mixed_case_count | 1 | -- 修改密码至少要包含的小写字母个数和大写字母个数
| validate_password.number_count | 1 | -- 修改密码至少要包含的数字个数
| validate_password.policy | MEDIUM | -- 等级较高
| validate_password.special_char_count | 1 | -- 修改密码至少要包含的特殊字符的个数
+--------------------------------------+--------+
可以看到mysql8安装后，默认密码长度8，至少包含大小写字母各一个，数字至少1个，所以我们不妨先将密码设置为：Aa1234..
2、修改：
set global validate_password.length=4;
set global validate_password.policy=0;
set global validate_password.mixed_case_count=0;
set global validate_password.special_char_count=0;
set global validate_password.number_count=0;
3、修改后再查看
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+
7 rows in set (0.09 sec)
>>>> 处理结束...二、被远程连接时出现的“坑”
前言
解决java.sql.SQLException: null, message from server: "Host 'XXX' is not allowed to connect异常
不能被访问，原因mysql有默认的IP白名单限制① [root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
② mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
③ mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost | -- 可以看到这里 限制了只能本地连接，更改一下。
+------------------+-----------+
4 rows in set (0.03 sec)
④ mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
⑤ mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % | -- 已被修改了。
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
4 rows in set (0.05 sec)
⑥ mysql> quit
Bye
⑦ [root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
>>>>> 处理结束 ...三、navicat 连接失败的“坑”
前言
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements① [root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
② mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
③ mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | caching_sha2_password | $A$005$f/E fH%_Ka@ Bwcma3RGy7.gvCGtAnJk3C4sXoIeq6hl4uauBxYLY7vI5 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.07 sec)
-- 可以看到root的 plugin的值为：caching_sha2_password，这是因为密码不合格导致的，改成符合要求的。
④ mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements -- 说明改的密码不符合要求，这里又回到了第一处踩坑，
⑤ mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 | 这里我之前设置好的，不知道为什么又回去了？？！！！
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.09 sec)
⑥ mysql> set global validate_password.length=4;
Query OK, 0 rows affected (0.08 sec)
mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.number_count=0;
Query OK, 0 rows affected (0.00 sec)
⑦ mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+
7 rows in set (0.07 sec)
⑧ mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.12 sec)
⑨ mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.04 sec)
-- 可以看到root 的 plugin的值变为:mysql_native_password 这里就大功告成了！
-- 处理结束四、关于mysql的性能参数调整（此次是降低mysql的性能，目的是降低mysql的内存占用率，毕竟才1G的内存）
前言
该优化是针对小内存的服务器，降低mysql的内存占用而设置的，并不是提升mysql的使用性能的。
若想提升mysql的性能，可以把下面提到的参数往相反的方向调整。正文开始
运维
# 查看内存溢出被linux杀掉的程序
grep "Out of memory" /var/log/messages
# 进程活跃情况
top
# 看内存占用
free -m
# 硬盘占用率
df -h优化mysql的内存占用
mysql> SHOW VARIABLES LIKE '%table_open_cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 4000 |
| table_open_cache_instances | 16 |
+----------------------------+-------+
2 rows in set (0.03 sec)
mysql> show global status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 151 |
| Opened_tables | 230 |
+---------------+-------+
2 rows in set (0.01 sec)
-- 建议优化比：
Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95
-- 按照公式转换一下
151/0.85 = 178
Opened_tables 不得大于 178
151/0.95 = 158
table_open_cache 不得超过 158
所以,退出mysql
quit
# 执行
vim /etc/my.conf
# 若配置文件有table_open_cache，就将值改为经过计算得到的值，若没有则添加
table_open_cache=512
即可。
-- mysql buffer_pool 参数优化
innodb_buffer_pool_size：缓存区域的大小。
innodb_buffer_pool_chunk_size：当增加或减少innodb_buffer_pool_size时，操作以块（chunk）形式执行。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值128M。可以自行设定，且增加和减少都要以M为单位，并只能在启动前修改，修改后的值
innodb_buffer_pool_instances：当buffer pool比较大的时候（超过1G），innodb会把buffer pool划分成几个instances，这样可以提高读写操作的并发，减少竞争。读写page都使用hash函数分配给一个instances。把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。innodb_buffer_pool_instances 参数显著的影响测试结果，特别是非常高的 I/O 负载时。
-- 配合的优化SQL：
vim /etc/my.conf
# 增加一行（针对内存1G的服务器，降低mysql的缓存池，来降低mysql的内存使用率），相反的设置提高该值，也会是mysql的内存使用占比升高，并且mysql的性能也会相应提高
innodb_buffer_pool_size=64m
所以：
# 先查询默认的参数情况
show global status like 'Innodb_buffer_pool_pages_data';
show global status like 'Innodb_buffer_pool_pages_total';
show global status like 'Innodb_page_size';
↓ ↓
mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 1394 |
+-------------------------------+-------+
1 row in set (0.11 sec)
mysql> show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_total | 8192 |
+--------------------------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)
>>>> 处理结束...
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
