Introduction Show
It is not uncommon to host databases and web servers on the same local machine. However, many organizations are now moving to a more distributed environment. A separate database server can improve security, hardware performance, and enable you to scale resources quickly. In such use cases, learning how to manage remote resources effectively is a priority. This tutorial shows you how to enable remote connections to a MySQL database. Prerequisites
Note: If you do not have direct access to your MySQL server, you need to establish a secure SSH connection. In case you need assistance, we have prepared a comprehensive tutorial on how to use SSH to connect to a remote server. This article a must-read for anyone new to the process. MySQL Server Remote ConnectionAllowing connections to a remote MySQL server is set up in 3 steps: 1. Edit MySQL config file. 2. Configure firewall. 3. Connect to remote MySQL server. Step 1: Edit MySQL Config File1.1 Access mysqld.cnf File Use your preferred text editor to open the mysqld.cnf file. This example uses the nano text editor in Ubuntu 18.04. Enter the following command in your command-line interface to access the MySQL server configuration file:
The location of the file may vary based on the
distribution and version in use. If the MySQL configuration file is not it its default location try using the Linux 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:
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 ConnectionWhile editing the configuration file, you probably observed that the default MySQL port is 3306. If you have already configured a firewall on your MySQL server, you need to open traffic for this specific port. Follow the instructions below that correspond to your firewall service in use. Option 1: UFW (Uncomplicated Firewall)UFW is the default firewall tool in Ubuntu. In a terminal window, type the following command to allow traffic and match the IP and port:
The system confirms that the rules were successfully updated. Option 2: FirewallDThe firewalld management tool in CentOS uses zones to dictate what traffic is to be allowed. Create a new zone to set the rules for the MySQL server traffic. The name of the zone in our example is mysqlrule, and we used the IP address from our previous example 133.155.44.103:
You have successfully opened port 3306 on your firewall. Option 3: Open Port 3306 with iptablesThe iptables utility is available on most Linux distributions by default. Type the following command to open MySQL port 3306 to unrestricted traffic:
To limit access to a specific IP address, use the following command instead:
This command grants access to 133.155.44.103. You would need to substitute it with the IP for your remote connection. It is necessary to save the changes made to the iptables rules. In an Ubuntu-based distribution type the following commands:
Note: If the previous commands do not work, try installing the program with:
Type the ensuing command to save the new iptables rules in CentOS:
Step 3: Connect to Remote MySQL ServerYour remote server is now ready to accept connections. Use the following command to establish a connection with your remote MySQL server:
The You should see an output similar to the one below:
How to Grant Remote Access to New MySQL Database?If you do not have any databases yet, you can easily create a database by typing the following command in your MySQL shell:
To grant remote user access to a specific database:
The name of the database, the username, remote IP, and password need to match the information you want to use for the remote connection. How to Grant Remote Access to Existing MySQL DatabaseGranting remote access to a user for an existing database requires a set of two commands:
User1 is now able to access yourDB from a remote location identified by the IP 133.155.44.103. Conclusion In this article, you have gained valuable insight into the general principles of a remote MySQL connection. With the appropriate credentials, a user originating from the specified IP address can now access your MySQL server from a remote machine. How do I access an existing MySQL database?To access a specific database, type the following command at the mysql> prompt, replacing dbname with the name of the database that you want to access: Copy use dbname; Make sure you do not forget the semicolon at the end of the statement. After you access a database, you can run SQL queries, list tables, and so on.
How do I connect to a MySQL connection?To connect to MySQL Server:. Locate the MySQL Command-Line Client. ... . Run the client. ... . Enter your password. ... . Get a list of databases. ... . Create a database. ... . Select the database you want to use. ... . Create a table and insert data. ... . Finish working with the MySQL Command-Line Client.. How do I access my localhost from another computer?If you want to communicate with that computer from somewhere else, you'll need to use its real IP address. With Windows, you should be able to get your IP address with ipconfig ( ifconfig under most UNIX-type systems) and just plug that into your connection parameters where you currently have localhost .
How do I connect to another user in MySQL?If you want to login as a different user on MySQL, you need to use “mysql -u -p command”. The syntax is as follows to login as a different user.
|