MySQL User Account

Create MySQL User Account

  1. Login
    mysql> mysql -u root -p
    
  2. Create a new database app_db
    mysql> CREATE DATABASE app_db;
    
  3. Switch to app_db
    mysql> USE app_db
    
  4. Create a user that can be login from
    • 127.0.0.1
    • localhost

      Unlike other RDBMS, an account MUST contain the host where he is allowed to login from
      mysql> CREATE USER 'dev'@'127.0.0.1' IDENTIFIED BY 'my_password';
      mysql> CREATE USER 'dev'@'localhost' IDENTIFIED BY 'my_password';
      
  5. Find the hostname MySQL used for the remote host
    # In the remote host
    # Replace mysqlhost.domain.com with the hostname of the MySQL server in the following command
    
    % mysql -u dev -p -h mysqlhost.domain.com
    Enter password: ***
    
    ERROR 1130 (HY000): Host 'somehost' is not allowed to connect to this MySQL server
    

    Notice the somehost value in the above error message

  6. Create the user for the remote host
    mysql> CREATE USER 'dev'@'somehost' IDENTIFIED BY 'my_password';
    
  7. Grant the privileges (Because it is a development environment for learning, we will grant it ALL privilege)
    mysql> GRANT ALL ON app_db.* TO 'dev'@'127.0.0.1' WITH GRANT OPTION;
    mysql> GRANT ALL ON app_db.* TO 'dev'@'localhost' WITH GRANT OPTION;
    

    Replace somehost with the correct value

    mysql> GRANT ALL ON app_db.* TO 'dev'@'somehost' WITH GRANT OPTION;