Creating a User in MySQL and Granting Remote Login Permissions

In MySQL, it is common to create a user who can access the database remotely and grant them permissions for specific databases. This article provides a detailed guide on how to achieve this.

Step 1: Log into MySQL

First, log into MySQL as an administrator (e.g., root user):

1
mysql -u root -p

Step 2: Create Database and Set Encoding

Create a database named project and set its encoding to utf8mb4:

1
CREATE DATABASE project CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Step 3: Create User and Allow Access from Any IP Address

Create a user named myuser and allow access from any IP address. Set a password, for example, myuser_password:

1
CREATE USER 'myuser'@'%' IDENTIFIED BY 'myuser_password';

Step 4: Grant User Permissions

Grant myuser all privileges on the project database:

1
GRANT ALL PRIVILEGES ON project.* TO 'myuser'@'%';

Step 5: Refresh Privileges

Refresh the privileges to apply the changes:

1
FLUSH PRIVILEGES;

Complete SQL Script

Here is the complete SQL script that covers creating the database, the user, and granting permissions:

1
-- Create the database and set encoding
2
CREATE DATABASE project CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3

4
-- Create the user and allow access from any IP address
5
CREATE USER 'myuser'@'%' IDENTIFIED BY 'myuser_password';
6

7
-- Grant user privileges
8
GRANT ALL PRIVILEGES ON project.* TO 'myuser'@'%';
9

10
-- Refresh privileges
11
FLUSH PRIVILEGES;

Deleting an Existing User

If a user named myuser already exists and you want to recreate it, you can delete the existing user first:

1
DROP USER 'myuser'@'localhost';
2
DROP USER 'myuser'@'%';
3

4
FLUSH PRIVILEGES;

Configuring MySQL to Allow Remote Access

Ensure that the MySQL configuration file allows remote access. Edit the MySQL configuration file (e.g., /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf), and find the following line:

1
bind-address = 127.0.0.1

Comment it out or change it to:

1
bind-address = 0.0.0.0

Then, restart the MySQL service:

1
sudo systemctl restart mysql

Configuring the Firewall

If your server has a firewall, ensure that the MySQL port (usually 3306) is allowed through the firewall. For example, using ufw:

1
sudo ufw allow 3306/tcp

By following these steps, you can successfully create a MySQL user who can access the database remotely and grant them the necessary permissions for specific databases.