MySQL 8.* 常见问题

1.无法使用show命令

解决办法：

mysql> create user 'mysql.infoschema'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'mysql.infoschema'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| discuz             |
| information_schema |
| mysql              |
| performance_schema |
| rsyslog            |
| sys                |
| wordpress          |
+--------------------+
7 rows in set (0.00 sec)

2.无法远程连接

解决方法 ：
修改配置

[12:51:44 root@ubuntu22 ~]#vim /etc/mysql/mysql.conf.d/mysqld.cnf
#注释这行
#bind-address           = 127.0.0.1
mysqlx-bind-address     = 127.0.0.1

3.连接密码规则问题

解决方法：
第一种、修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf
#添加一行
default_authentication_plugin=mysql_native_password

第二种、修改密码规则

mysql> SELECT user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | %         | mysql_native_password |
| root1            | %         | mysql_native_password |
| discuzer         | 10.0.0.%  | caching_sha2_password |
| rsysloger        | 10.0.0.%  | caching_sha2_password |
| wordpresser      | 10.0.0.%  | caching_sha2_password |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
| rsyslog          | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
11 rows in set (0.00 sec)

#修改
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你自己的密码';