Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»General Articles»How to Allow Remote Connections to MySQL

    How to Allow Remote Connections to MySQL

    By RahulApril 19, 20215 Mins Read

    The default MySQL server listens only on local host interface. Which means no one can connect MySQL from the remote system. This is a good practice for security purposes on productions environments. But in case of database and web servers are running separately, needs to allow MySQL remote connections.

    Advertisement

    MySQL allows us to define an IP address to listen. You can provide the IP address of LAN network, which allow access MySQL from local network only. To allow the public network, you can simply define all zero’s (0.0.0.0) as an IP address to allow MySQL remote connection for any host.

    This tutorial will help you to configure MySQL server to allow connections from remote systems.

    Step 1 – Configure MySQL Service

    The default MySQL server listen on localhost interface only. You need to change this to listen on all interface.

    Edit MySQL database server configuration file in a text editor. The Debian based systems have MySQL configuration fiel at /etc/mysql/mysql.conf.d/mysqld.cnf. Let’s Edit MySQL configuration file:

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

    Search for the bind-address setting in file. The default bind-address is set to 127.0.0.1 as below:

    1
    2
    3
    4
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1

    Change bind-address value to 0.0.0.0 to enable MySQL database server listen on all network interfaces.

    1
    2
    3
    4
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 0.0.0.0

    After change the value will look like above.

    In MySQL 8 database configuration file, you will not see the bind-address setting. So you just need to manually add the bind-address under [mysqld] tag.

    [mysqld]
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    datadir         = /var/lib/mysql
    log-error       = /var/log/mysql/error.log
    bind-address    = 0.0.0.0
    

    Save the configuration file and close it.

    Restart MySQL service to apply changes.

    systemctl restart mysql.service 
    

    Now, verify the changes and MySQL is listenning on from all remote hosts. Execute the following command to view service status:

    netstat -tulpn  | grep -e "Local Address" -e "mysql" 
    
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1698595/mysqld
    tcp6       0      0 :::33060                :::*                    LISTEN      1698595/mysqld
    

    You will see that MySQL is now listening on 0 0.0.0.0:3306 local address. It means, server is now listening on all network interfaces on port 3306.

    Step 2 – Create MySQL User with Remote Access

    Next, you need to create MySQL user to connect from remote host.

    mysql> CRATE USER 'username'@'remote_server_ip_or_hostname' IDENTIFIED BY 'pa$$word';
    
    • username – The name of user account used for login to MySQL server
    • remote_server_ip_or_hostname – This is the ip address or hostname of remote host from where user is allowed to connect mysql server.

    You can also use ‘%’ instead of remote host to allow any remote host. For example:

    mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'pa$$word';
    

    Alternatively, you can create a new user account that will only connect from the remote host with the following command:

    mysql> GRANT ALL on *.* TO 'username'@'remote_server_ip_or_hostname';
    

    Reload the privileges to running MySQL server.

    mysql> FLUSH PRIVILEGES;
    

    At this point, you have configured your MySQL server to listen from remote hosts. Created a MySQL user account used for authentication from remote hosts.

    Next, is to adjust security groups or system firewall to open MySQL port for remote systems.

    Step 3 – Adjust Firewall

    Before changing system firewall, the system running in cloud hosting environments must have update security group to open MySQL port based on hosting services.

    Then, you need to open port in system firewall (if active). For example, most of the Linux system opted firewalld and some of system like Debian based systems uses UFW firewall.

    Update the firewall based on your operating systems.

    Using FirewallD

    The FirewallD is the default firewall services in CentOS and RedHat based systems. You can use the following command to open MySQL port 3306 for everyone, but it it not recommended for production servers.

    sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp 
    sudo firewall-cmd --reload 
    

    The production server are recommended to open port for required hosts only. The FirewallD uses can use rich rules to open specific port for specific ip address or network only. For example:

    firewall-cmd --permanent --zone=public --add-rich-rule='
      rule family="ipv4"
      source address="192.168.0.0/24"
      port protocol="tcp" port="3306" accept' 
    

    After adding required rule in firewalld, make sure to reload to apply changes at runtime.

    sudo firewall-cmd --reload 
    

    Using UFW

    The UFW (Uncomplicated Firewall) is the default firewall tool in Ubuntu and Debian systesm. You can use the following command to open port 3306 from everyone.

    sudo ufw allow 3306/tcp 
    

    But the productions users are recommended to open port for specific ip or network only. To Allow access from a specific IP addresses use command like:

    sudo ufw allow from 192.168.0.0/24 to any port 3306 
    

    Make sure to change 192.168.0.0/24 with your network or system’s IP address.

    Step 4 – Connect Remote MySQL

    You must have mysql client packages installed on your system to connect remote MySQL server.

    mysql -h 192.168.0.100 -p 3306 -u DBUSER -pPASSWORD 
    

    where:

    • -h Hostname or IP address of the remote MySQL server (default: localhost)
    • -p Port number of the MySQL server (default: 3306)
    • -u MySQL username
    • -p Password for the defined username, No space between -p and the password

    Conclusion

    This tutorial helped you to setup MySQL serer to accept remote connection from remote hosts. Also created a MySQL user account allowed to connect from remote hosts.

    mariadb msyql
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    How to Split Large Archives in Linux using the Command Line

    System.out.println() Method in Java: A Beginner’s Guide

    sleep Command in Linux with Examples

    View 1 Comment

    1 Comment

    1. John on September 12, 2021 4:59 am

      Beautiful and simple explanation. Thanks!

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • How to Split Large Archives in Linux using the Command Line
    • System.out.println() Method in Java: A Beginner’s Guide
    • Split Command in Linux With Examples (Split Large Files)
    • Test Your Internet Speed from the Linux Terminal
    • 11 Practical Example of cat Command in Linux
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.