sajad torkamani

Create user

Login as root (however you've configured root login).

sudo mysql

Create user.

CREATE USER 'example_user'@'%' IDENTIFIED WITH mysql_native_password BY 'some-strong-password';

Grant privileges.

GRANT ALL ON example_database.* TO 'example_user'@'%';
FLUSH PRIVILIGES;

Verify new user was created

mysql> select User,Host,plugin from mysql.user;

This should output something like:

+----------------------------+-----------+-----------------------+
| User                       | Host      | plugin                |
+----------------------------+-----------+-----------------------+
| example_user               | %         | mysql_native_password |
| debian-sys-maint           | localhost | caching_sha2_password |
| mysql.infoschema           | localhost | caching_sha2_password |
| mysql.session              | localhost | caching_sha2_password |
| mysql.sys                  | localhost | caching_sha2_password |
| root                       | localhost | auth_socket           |
+----------------------------+-----------+---------------------

Exit MySQL.

mysql> exit

Allow remote access

Edit /etc/mysql/mysql.conf.d/mysqld.cnf and comment out the following line:

bind-address          = 127.0.0.1

This ensures that users can login from other IP addresses. Ensure your firewall (if you have one) allows remote access to port 3306.

Restart MySQL server

sudo systemctl restart mysql.service

Login with new credentials

From your local machine:

mysql -h <server-ip> -u example_user -p