How do you create a MySQL user that can be connected to remotely by command-line?

Question

How do you create a MySQL user that can be connected remotely by command-line?

Answer

To create a MySQL user which can be connected to remotely, you would need to include the IP address of where the connection is coming from.

For example, on the remote MySQL server, you would want to create a user like this.

 * These steps show how to create the user manually. They also assume you have 'root' access on the remote MySQL server. *

1. Log in to the remote server as 'root'.

2. Type "mysql -u root" and hit Enter. You will see a MySQL prompt.

 # mysql -u root
mysql>

3. To create the user, create the user with the connection IP address, type:

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

 * Be sure to change the username, ip_address, and password to the one you wish to create. *

4. To verify it exist afterward, use the following command inside the MySQL shell:

  select user,host from mysql.user where host like '%ip_address%';

It will output the list of all MySQL users that can be connected to by the IP address.

That's it.