分类 Database 下的文章

好,简单来说,我前几天第一次用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

完。

要想使用外键,作为外键的字段,在两个表里必须完全一样,
而且parent table里,必须是primary key,且必须unique

MySQL 5.7.4之前的版本:

ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

For MySQL 5.7.4 or later:

ALTER TABLE mytbl ADD UNIQUE (columnName);

这个IGNORE主要目的是,因为表中可能已经存在了数据,而且会有重复数据。
但MySQL 5.7.4 之后再用IGNORE关键字,会报错。所以增加UNIQUE限制之前,要先确保数据没有重复。

还有一个小特色,甚至你可以为这个constraint加个名字:

ALTER TABLE myTable
  ADD CONSTRAINT constraintName 
    UNIQUE (columnName);