Posts in category mysql

ubuntu 上安装 MySQL 5.7 后对 root 用户的处理

在 Ubuntu 18.04(Ubuntu 16.04 应该也是一样)上通过命令 sudo apt-get install mysql-server 安装 MySQL 5.7 数据库服务器的过程中,没有询问输入密码,安装完成后只能通过 sudo mysql -u root 登录到 MySQL 服务器(不用输入 MySQL 的 root 密码),而使用普通 Linux 帐号则无法登录;

按照网上的资料(参考 https://www.jianshu.com/p/4ca115648939http://www.phpbug.cn/archives/621.html ),解决方案如下:

  1. First, connect in sudo mysql
    sudo mysql -u root
    
  2. Check your accounts present in your db
    mysql> SELECT User,Host FROM mysql.user;
    
        +------------------+-----------+
        | User             | Host      |
        +------------------+-----------+
        | admin            | localhost |
        | debian-sys-maint | localhost |
        | magento_user     | localhost |
        | mysql.sys        | localhost |
        | root             | localhost |
        +------------------+-----------+
    
  3. Delete current root@localhost account
    mysql> DROP USER 'root'@'localhost';
    
  4. Recreate your user
    mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
    
  5. Give permissions to your user (don’t forget to flush privileges)
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
    mysql> FLUSH PRIVILEGES;
    

但是上述的操作其实会带来一个副作用,就是 在给新建的其他用户赋权限的时候会出现 mysql grant 命令错误:ERROR 1044 (42000): Access denied for 'root' With All Privileges,解决方法如下( 参考 http://www.cnblogs.com/xiaoerlang/p/4538746.htmlhttps://stackoverflow.com/questions/21714869/error-1044-42000-access-denied-for-root-with-all-privileges ):

  1. 确认 root@localhost 是否已被赋予的权限 Grant_priv
    mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;
    
    +-----------+------------------+-------------------------------------------+------------+------------+
    | host      | user             | password                                  | Grant_priv | Super_priv |
    +-----------+------------------+-------------------------------------------+------------+------------+
    | localhost | root             | ***************************************** | N          | Y          |
    | localhost | debian-sys-maint | ***************************************** | Y          | Y          |
    | localhost | staging          | ***************************************** | N          | N          |
    +-----------+------------------+-------------------------------------------+------------+------------+
    
  2. 如果 root@localhost 的 Grant_privN,则设为 Y
    mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
    mysql> FLUSH PRIVILEGES;
    mysql> GRANT ALL ON *.* TO 'root'@'localhost';
    
  3. 重新登录 MySQL,即可解决。