create user with remote access

To create a MySQL user called "admin" with the password "asterisk" and grant all privileges to the "asterisk" database, as well as allow remote MySQL connections, follow these steps:

1. Log in to the MySQL server as the root user:

   css
   mysql -u root -p
   

2. Enter the MySQL root password when prompted.

3. Create a new user "admin" with the password "asterisk":

   sql
   CREATE USER 'admin'@'%' IDENTIFIED BY 'asterisk';
   

   This creates a user named "admin" that can connect from any remote host ("%").

4. Grant all privileges to the user on the "asterisk" database:

   sql
   GRANT ALL PRIVILEGES ON asterisk.* TO 'admin'@'%';
   

5. Flush the privileges to apply the changes:

   sql
   FLUSH PRIVILEGES;

Allow remote connection

nano my.cnf.d/mariadb-server.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mariadb/mariadb.log

pid-file=/run/mariadb/mariadb.pid

bind-address=0.0.0.0

Restart the service

 systemctl restart mysqld

TEST

 mysql -h 145.27.107.195 -u admin -pasterisk

How to Allow Remote Connections to MySQL

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

1.2 Change Bind-Address IP

You now have access to the MySQL server configuration file. Scroll down to the bind-address line and change the IP address. The current default IP is set to 127.0.0.1. This IP limits MySQL connections to the local machine.

The new IP should match the address of the machine that needs to access the MySQL server remotely. For example, if you bind MySQL to 0.0.0.0, then any machine that reaches the MySQL server can also connect with it.

Once you make the necessary changes, save and exit the configuration file.

Note: Remote access is additionally verified by using the correct credentials and user parameters you have defined for your MySQL users.

1.3 Restart MySQL Service

Apply the changes made to the MySQL config file by restarting the MySQL service:

sudo systemctl restart mysql

Next, your current firewall settings need to be adjusted to allow traffic to the default MySQL port.

Step 2: Set up Firewall to Allow Remote MySQL Connection

While editing the configuration file, you probably observed that the default MySQL port is 3306. 

https://phoenixnap.com/kb/mysql-remote-connection

MySQL Error-The Server Requested An Authentication Method Unknown To The Client

1 - CREATE USER 'admin'@'%' IDENTIFIED BY '91224YB11111';

2 - GRANT ALL ON *.* TO 'admin'@'%';

3- ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY '91224YB11111';

3 - FLUSH PRIVILEGES;

https://www.skynats.com/blog/mysql-error-the-server-requested-authentication-method-unknown-to-the-client/