Linux下MySQL的报错及解决

一、由于已经忘了sql语句，导致出现如下错误Linux下Access denied for user ‘root’@‘localhost’ (using password: NO)

2019-5-19日更新，还是遇到了这个问题，根据之前写的博客还是进不去，甚至想到使用 rm -rf data/* 和rm -rf arch/* 删除目录下的东西来进行重装MySQL。
fuck,其实是忘了之前正确的进入姿势：root用户下使用/etc/init.d/mysql stop 先去停止数据库。然后在这个用户下使用这个命令进入安全模式。
[mysqladmin@hadoop004 ruoze_g6]$ mysqld --skip-grant-tables
新开一个窗口输入如下命令进入mysql数据库，mysql -u root mysql
解决办法:
1、首先结束mysql进程，利用ps aux | grep mysql命令得到进程ID号，kill ID号，结束进程。

2、运行此命令：mysqld --skip-grant-tables
3、另外打开一个终端：输入mysql -u root mysql，顺利进入MySQL界面，选择mysql数据库，选择user用户表；
4、执行如下SQL语句：update user set password=password(‘960210’) where user = ‘root’;
5、刷新权限：flush privileges；
6、运行service mysql restart；重启MySQL服务后，再运行mysql -u root -p输入新密码即可。

二、根据若泽数据文档在阿里云上部署了Mysql5.7，云主机关机后重新启动MySQL服务失败，才知道犯了一个很愚蠢的问题。

[root@hadoop001 data]# service mysql start
Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/hostname.pid)

查看错误日志：/usr/local/mysql/data/hostname.err
打印近200行的错误日志：tail -200f hostname.err

2019-07-21T01:34:53.458083Z mysqld_safe mysqld from pid file /usr/local/mysql/data/hostname.pid ended
2019-07-21T01:55:42.996530Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2019-07-21T01:55:43.199280Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2019-07-21T01:55:43.199411Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-21T01:55:43.199440Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2019-07-21T01:55:43.199469Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.11-log) starting as process 9361 ...
2019-07-21T01:55:43.201693Z 0 [ERROR] Fatal error: Can't change to run as user 'mysql' ;  Please check that the user exists!

2019-07-21T01:55:43.201744Z 0 [ERROR] Aborting

2019-07-21T01:55:43.201767Z 0 [Note] Binlog end
2019-07-21T01:55:43.201886Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

2019-07-21T01:55:43.209838Z mysqld_safe mysqld from pid file /usr/local/mysql/data/hostname.pid ended

定位到Error那句话，cant change to run as user 'mysql'；
切换到mysqladmin用户下启动mysql服务，service mysql start	服务正常启动

三、MySQL5.7下数据库密码忘了如何解决

同一，但是5.7和5.6不同的是，5.7版本中取消了password字段，引入的字段是authentication_string，修改密码的语句如下所示：

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

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

再进行第二步操作：

mysql> create database wordpress;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by '960210';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '960210' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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