LEEYANGY

LEEYANGY

学生党

LEEYANGY

LEEYANGY

关注TA

学生党

  •  未知
  • 未知
  • 写了29,238字

最近回复

该文章投稿至Nemo社区   数据库  板块 复制链接


MySQL常见问题处理

发布于 2021/04/09 00:43 224浏览 3回复 5,165

安装过程参考主页文章

解决Navicat 连接Mysql报2059错误

报错内容发生在Windows下的Navicat

下面是解决步骤:

报错原因 :

MySQL 8  之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password


解决方法:改加密规则
Win+R Input : cmd Enter
//login MySQL
Input : mysql -uroot -p123321a
//Select Databas
Input : use mysql;
Input : ALTER USER 'root'@'localhost' IDENTIFIED BY '123321' PASSWORD EXPIRE NEVER; #更改加密方式
Input : ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123321'; #更新用户密码
Input : FLUSH PRIVILEGES; #刷新权限
# 远程连接请将'localhost'换成'%'

    mysql -u root -p
mysql>use mysql;
mysql>select 'host' from user where user='root';
mysql>update user set host='%' where user='root';
mysql>flush privileges;
mysql>select 'host' from user where user='root';
运行完毕后再次连接测试,若还不行重启mysql服务,或是直接重启服务器


过程中使用的弱密码不针对实际环境,生产环节请不要使用弱密码!!!


针对新安装的MySQL密码问题:

leeyangy@leeyangy:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'


sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf, 进入到这个配置文件,然后在这个配置文件中的 [mysqld] 这一块中加入 skip-grant-tables 这句话

重启MySQL服务 service mysql restart

use mysql;   然后敲回车
update user set authentication_string=password("123321") where user="root"; 然后敲回车
flush privileges; 然后敲回车

退出数据库 注释掉skip-grant-tables

leeyangy@leeyangy:~$ mysql -u root -p
Enter password:
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

此时还是报错?再去取消注释 skip-grant-tables

mysql -u root -p 直接回车再回车
use mysql;

mysql> select user, plugin from user;
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.session | mysql_native_password |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

4 rows in set (0.00 sec)

查询到上面这样的信息

从表中可以看到在执行了select user, plugin from user;后,错误原因是因为plugin root的字段是auth_socket,改掉它,替换为mysql_native_password就行了。输入:

update user set authentication_string=password("123321"),plugin='mysql_native_password' where user='root';

再次查询

mysql> update user set authentication_string=password("123321"),plugin='mysql_native_password' where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> select user,plugin from user;
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| root | mysql_native_password |
| mysql.session | mysql_native_password |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+
4 rows in set (0.00 sec)

root用户的字段改成功了!



下面是卸载过程:

    1.首先查询依赖:    dpkg --list|grep mysql

    阿巴阿吧阿巴的列出信息

    .........................................................................................................

root@ubuntu:/home/ubuntu# dpkg --list|grep mysql

ii mysql-client-core-8.0 8.0.23-0ubuntu0.20.04.1 arm64 MySQL database core client binaries
rc mysql-common 5.8+1.0.5ubuntu2 all MySQL database common files, e.g. /etc/mysql/my.cnf
rc mysql-server-8.0 8.0.23-0ubuntu0.20.04.1 arm64 MySQL database server binaries and system database setup
ii mysql-server-core-8.0 8.0.23-0ubuntu0.20.04.1 arm64 MySQL database server binaries


 

    2. 借用root 卸载 :  

root@ubuntu:/home/ubuntu#sudo apt-get remove mysql-common


        从上面依赖关系中看到我当前安装的MySQL版本是8.0

        执行命令 sudo apt-get autoremove --purge mysql-server-8.0

        以下是输出log--由于使用不同版本linux 此处信息仅供参考

  

root@ubuntu:/home/ubuntu# sudo apt-get autoremove --purge mysql-server-8.0

Reading package lists... Done
Building dependency tree
Reading state information... Done
Package 'mysql-server-8.0' is not installed, so not removed
The following packages will be REMOVED:
libcgi-fast-perl* libcgi-pm-perl* libencode-locale-perl* libevent-core-2.1-7* libevent-pthreads-2.1-7* libfcgi-perl*
libhtml-parser-perl* libhtml-tagset-perl* libhtml-template-perl* libhttp-date-perl* libhttp-message-perl*
libio-html-perl* liblwp-mediatypes-perl* libmecab2* libtimedate-perl* liburi-perl* mecab-ipadic* mecab-ipadic-utf8*
mecab-utils* mysql-client-core-8.0* mysql-server-core-8.0*
0 upgraded, 0 newly installed, 21 to remove and 0 not upgraded.
After this operation, 294 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 99869 files and directories currently installed.)


        .......................................这里省略了...................................................................

        3.清理残余文件

       dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P

        4.再次查看还有没有剩余依赖 

dpkg --list|grep mysql

        如果查询结果为空,则结束卸载。

-----------------------------------------------------------------------

点赞(2)

上一个文章:计算机网络笔记1

下一个文章:用tomcat部署java web项目

点了个评