mysql 常见问题

如何彻底清除mysql

1．查看是否有安装过mysql

rpm -qa | grep -i mysql

2．删除mysql

yum -y remove MySQL- 或者 yum -y remove mysql80-
一般用rpm -e 的命令删除mysql,这样表面上删除了mysql,可是mysql的一些残余程序仍然存在,并且通过第一步的方式也查找不到残余,而yum命令比较强大,可以完全删除mysql.(ps:用rpm删除后再次安装的时候会提示已经安装了,这就是rpm没删除干净的原因)

3．把所有出现的目录统统删除

find / -name mysql
查找mysql的一些目录，把所有出现的目录删除，可以使用rm -rf 路径，删除时请注意，一旦删除无法恢复。

4．删除配置文件

rm -rf /etc/my.cnf

5．删除mysql的默认密码

rm -rf /root/.mysql_sercret
删除mysql的默认密码,如果不删除,以后安装mysql这个sercret中的默认密码不会变,使用其中的默认密码就可能会报类似Access denied for user ‘root@localhost’ (using password:yes)的错误.

忘记密码

https://stackoverflow.com/questions/50691977/how-to-reset-the-root-password-in-mysql-8-0-11

https://developer.aliyun.com/article/545022

version 8 必须分开命令

1. 创建账号
2. 赋予权限
3. 更新权限

   mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD'; 
   mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; 
   mysql> FLUSH PRIVILEGES;

   更新密码

   ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'new_password';

   
   

   创建用户与管理与查看权限

   

mysql 5, https://linuxize.com/post/how-to-create-mysql-user-accounts-and-grant-privileges/
mysql 8, https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

// 创建用户
mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
// 赋予权限
mysql> GRANT ALL PRIVILEGES ON database.table TO 'sammy'@'localhost' WITH GRANT OPTION;
// 查看权限
mysql> SHOW GRANTS FOR 'database_user'@'localhost';

如何撤销或者删除权限

• 方法一：获得系统权限

  msyql> grant system_user on *.* on to root;

• 方法二：或者登录要撤销权限的用户

https://blog.csdn.net/qq_37189082/article/details/92800663

mysql> REVOKE all privileges ON *.* from 'name'@'host';
mysql> flush privileges;

如何删除用户

mysql> drop user dbadmin@localhost
mysql> select user, host from mysql.user;

https://www.mysqltutorial.org/mysql-drop-user/

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

在 node 端连接数据库时才会有

https://hackthestuff.com/article/error-solved-client-does-not-support-authentication-protocol-requested-by-server-consider-upgrading-mysql-client

>mysql ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
>mysql flush privileges;
>mysql exit;

Illegal mix of collations MySQL Error

https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error

其他

https://blog.csdn.net/weixin_27066963/article/details/114745731