分类 Database 下的文章

自从Ubuntu18.04之后，MySQL的root用户就自动用个什么其他方式连接，总之不是密码了，所以我其实已经有多个文章来解决这个问题，但还是再写一篇，以便自己加深记忆。

首先，mysql -u root登录mysql；
其次，ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root-password';更改root用户的密码，这个命令看着似乎也顺便把root用户的连接方式改为传统的密码连接了。

在user的home目录下，建立一个文件.my.cnf，里面这样写：

[client]
user=username
password='mypassword'

然后就可以直接敲入mysql命令登录了，不用mysql -u username -p了。

好几种方式：

// No. 1
mysql -u username -p < data.sql

//No. 2
mysql> CREATE DATABASE myDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;   
mysql> use myDB;                  # 使用已创建的数据库 
mysql> source /path/to/data.sql   # 导入备份数据库

感觉第二种似乎挺不错的

Statements That Cannot Be Rolled Back

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.

这个是摘自MySQL文档，8.0和5.7都是同样的文档。但据说PostgreSQL没有这样的问题，还没测试。

好，简单来说，我前几天第一次用Ubuntu 18，发现MySQL 5.7竟然不用密码就可以在shell中进入，但是当WordPress程序用用户名和密码的方式连接时（用户名是root，密码为空），总是报错：

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

这尼玛不是搞笑呢么？root都没权限？后来一搜索，原来是这个原因：

The reason is that recent Ubuntu installation (maybe others also), mysql is using by default the UNIX auth_socket plugin.
Basically means that: db_users using it, will be "auth" by the system user credentias. You can see if your root user is set up like this by doing the following:

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin.

然后有2种解决方案：

  • You can set the root user to use the mysql_native_password plugin
  • You can create a new db_user with you system_user (recommended)

第一种方案：

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

第二种：

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

但说真的，第二种就算是推荐的，但没有密码啊，而且WordPress等程序怎么连接啊？不会啊。

所以就用第一种解决方案。

事情要是到此为止，还可以;但显然没那么简单。此时，你用mysql命令，还是可以直接进入。

搜了一个解决方案：

  1. Open & Edit /etc/mysql/mysql.cnf
  2. Add skip-grant-tables under [mysqld]
  3. Restart Mysql
  4. You should be able to login by using the command mysql -u root -p
  5. Run mysql> flush privileges;
  6. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
  7. Go back to /etc/mysql/mysql.cnf and remove/comment skip-grant-tables
  8. Restart Mysql
  9. Now you will be able to login with the new password mysql -u root -p

完。

2019年127日补充

其实这个在mysql的官方文档里有:
https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

  1. 1
  2. 2
  3. 3