mysql必备小知识_mysql常用小知识

以下基于Ubuntu16.04：

卸载：

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

sudo rm -rf /etc/mysql /var/lib/mysql

sudo apt-get autoremove

sudo apt-get autoclean

创建用户(发现已经不建议grant顺带创建用户了)：

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

数据库编码环境：

/etc/mysql/conf.d/mysql.cnf

[mysql]

default-character-set=utf8

[client]

default-character-set=utf8

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]

init-connect='SET NAMES utf8'

character-set-server = utf8

mysql> SHOW VARIABLES LIKE 'character%';

遇到一个很神奇的问题就是

ERROR 1045 (28000): 'Access denied for user 'root'@'localhost' (using password: YES)'

出现这个问题的原因是把旧数据库一股脑打包导入，日志的描述是：

2018-03-16T06:07:52.910261Z 0 [Warning] User entry 'root'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.

2018-03-16T06:07:52.910298Z 0 [Warning] Some of the user accounts with SUPER privileges were disabled because of empty mysql.user.plugin value. If you are upgrading from MySQL 5.6 to MySQL 5.7 it means we were not able to substitute for empty plugin column. Probably because of pre 4.1 password hash. If your account is disabled you will need to:

2018-03-16T06:07:52.910303Z 0 [Warning] 1. Stop the server and restart it with --skip-grant-tables.

2018-03-16T06:07:52.910306Z 0 [Warning] 2. Run mysql_upgrade.

2018-03-16T06:07:52.910309Z 0 [Warning] 3. Restart the server with the parameters you normally use.

2018-03-16T06:07:52.910321Z 0 [Warning] For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual

试过很多办法，包括日志中提到的mysql_upgrade，目前试过有效的办法是关闭mysql后，在上文中的mysqld.cnf中mysqld下方加入skip-grant-tables，启动mysql，

mysql -u root -p

mysql> flush privileges;

mysql> update user set plugin='mysql_native_password' where User='root';

然后移除或者注释掉添加的skip部分，重启mysql