Free Online Tutorials

By default, MySQL or MariaDB only listens for connections from the localhost. All remote access to the server is denied by default.

Step 1: To enable remote access, first find location of configuration file then run the commands below to open MySQL/MariaDB configuration file:

vi /etc/my.cnf

Depend on your OS and verion of MySQL / MariaDB you use, here are some locations:

/etc/my.cnf
/etc/mysql/mysql.conf.d/mysql.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf

You can find location by using this command:

find / -name my.cnf
find / -name *server.cnf
find / -name mysql.cnf

Step 2: You have to commented out (prefaced with hash (#) characters) the following lines:

    [mysqld]
    ...
    #skip-networking
    ...
    #bind-address = <some ip-address>

or you can use your server ip address (suppose: 11.11.11.11) or 0.0.0.0

bind-address = 11.11.11.11

Step 3: restart MySQL / MariaDB server

systemctl restart mysql.service
systemctl restart mariadb.service

To verify that the change happens, run the commands below:

[root@tutorialspots ~]# netstat -anp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      2884/mysqld

Step 4: open firewall (suppose remote address: 22.22.22.22)

For iptables

[root@tutorialspots ~]# iptables -I INPUT -p udp -s 22.22.22.22 --dport 3306 -j ACCEPT
[root@tutorialspots ~]# iptables -I INPUT -p tcp -s 22.22.22.22 --dport 3306 -j ACCEPT
service iptables save

For firewalld

firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="22.22.22.22" port protocol="tcp" port="3306" accept'
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="22.22.22.22" port protocol="udp" port="3306" accept'
sudo firewall-cmd --reload

Step 5: Connect to your MySQL / MariaDB server using root user

mysql -uroot -pdatabaseuser_password -h server hostname or IP address

Example:

[root@tutorialspots ~]#mysql -uroot -pMYROOTPASSWORD
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 61351084
Server version: 10.3.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Step 6: For example, if you wish for a client computer with IP address 22.22.22.22 (sometimes you must use hostname like sv.tutorialspots.com) to connect to a database called your_database_name as user your_user, then use this command:

GRANT ALL ON your_database_name.* TO 'your_user@22.22.22.22' IDENTIFIED BY 'new password here';
quit;

If you get error:

ERROR 1045 (28000): Access denied for user 'your_user'@'22.22.22.22' (using password: YES)

Change

GRANT ALL ON your_database_name.* TO 'your_user@22.22.22.22' IDENTIFIED BY 'new password here';
quit;

To

CREATE USER 'your_user'@'22.22.22.22' IDENTIFIED BY 'new password here'; 
grant all privileges on your_database_name.* to 'your_user'@'22.22.22.22' with grant option;
quit;