To create a user in MySQL for remote access, we need to follow these steps:
First, we need to edit the MySQL configuration file and change the bind-address parameter to allow connections from any IP address. We can find the configuration file in different locations depending on our operating system and MySQL version.
For example:
On Linux:
It could be /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf.
On Windows:
It could be C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.
Once we find the file:
Open it with a text editor and look for the line that says bind-address = 127.0.0.1. Change it to bind-address = 0.0.0.0 or to the IP address of the interface that we want to use for remote access.
After changing the file we can save the file and restart the MySQL service.
Second, we need to create a new user account with a secure password and grant it the appropriate privileges on the databases and tables that we want to access remotely.
We can use the CREATE USER and GRANT commands in MySQL to do this.
For example, if we want to create a user named myremoteuser with the password mypass and grant it all privileges on the database mydb, we can use the following commands:
CREATE USER 'myremoteuser'@'%' IDENTIFIED BY 'mypass';
GRANT ALL PRIVILEGES ON mydb.* TO 'myremoteuser'@'%';
FLUSH PRIVILEGES;
The ‘%’ symbol means that the user can connect from any host. We can also specify a specific host name or IP address instead of ‘%’.
For example, if we want to allow the user to connect only from the host 192.168.1.10, we can use ‘myremoteuser’@‘192.168.1.10’ instead of ‘myremoteuser’@‘%’.
We can also limit the privileges to specific operations, such as SELECT, INSERT, UPDATE, DELETE, etc.
Click on Privileges Provided by MySQL to view more detail on MySQL privileges.
If you want to learn more MySQL Commands then click this link.