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;