Recently, I decided to change the way I made updates to MySQL on my virtual machines. Originally, I was content with using the command line interface to interact with the servers, but it was becoming very cumbersome. Instead, I will shift my workflow to MySQL Workbench on my Windows machine in order to help speed up changes. In order to use a GUI however, I needed to make MySQL accessible remotely.
Before I get in to the steps, I want to highlight my system configurations pre-update.
- CentOS Stream 8 Operating System on two VMs on the Windows machine mentioned above
- One machine is used for testing; the other is production
- Web application accesses MySQL on the same machine
- Python based application; Using mysql-connect module
In the process of making these changes, the solutions I came across were not a perfect fit for my configuration. While I go through the steps that worked for me, I will also highlight what other solutions might work in your case. Additionally, I want to note that these changes are being made on systems I am using as a home project. For more robust and security focused configurations, I encourage you to continue your search on the web.
Change MySQL Configuration Files
A good place to start is the configuration file for MySQL, which can be found in one of these locations based on my research:
- /etc/my.cnf.d/mysql-server.cnf (my location)
- /etc/mysql/mysql.conf.d/mysqld.cnf
- /etc/mysql/my.cnf
Open the file with the editor of your choice and once inside, the following information must be added/changed:
- port=default or unique port number (remember for next step)
- bind-address=any, local, or remote address
In my case, I have set up the port number to 3309 (default is 3306) and bind-address to 0.0.0.0 (default is 127.0.0.1 aka localhost). Having the bind-address the way it is allows for both remote and local connections. Should you only need a remote connection, input the address from the remote machine.
When the changes have been made, save the file and restart MySQL service with the following command:
- sudo systemctl restart mysqld
- service name can be different from mysqld
You can verify these changes by using either command below:
- netstat -tlpn
- systemctl status mysqld
Allow MySQL Connections through your Firewall
The next step I want to highlight is making changes to your firewall. This will allow you to specify which connections are allowed to access your MySQL server. First, you’ll need to identify which firewall service works for your machine. Some services are:
- firewall-cmd (my service)
- iptables
- ufw
For info on how to update your firewall via firewall-cmd, iptables or ufw click here. I followed the steps for firewall-cmd as it was the main firewall service for my machine. In addition to those steps, I added a second source to my firewall rules to allow a second IP to make a connection. Furthermore, this process requires you to input the port address you used in your configuration file. Be sure to have it memorized if it’s not the default.
Granting User Access in MySQL
The final step in this process is to give access to users in MySQL. Access can be given to the whole server or to a specified database. In my case, I gave myself access to the whole server and its databases via the following commands within MySQL:
- CREATE USER ‘username’@’remotehostip’ IDENITIFIED BY ‘password’;
- GRANT ALL PRIVILEGES on *.* to ‘username’@’remotehostip’;
- *.* can be changed to databaseName.* to specify a database
- FLUSH PRIVILEGES;
Once the last command is entered, the created user will now have remote access to your MySQL server. If, however, you have an application on the same machine as MySQL that also needs access, another user must be created as follows:
- CREATE USER ‘username’@’localhost’ IDENITIFIED BY ‘password’;
Connecting to your Server with MySQL Workbench
As mentioned above, I chose MySQL Workbench as the GUI for my server. I chose this application purely out of familiarity, having used it at university. With that said, there are probably countless other platforms to use, each with their own pros and cons I’m sure. If you do find yourself preferring MySQL Workbench, the process to add a server is very straight forward.
After clicking the ‘+’ icon on the MySQL Workbench home screen, you’ll see the dialog box pictured above. Here you’ll provide a name for your connection and are able to select the connection method (standard works fine). After which you’ll see a tab named “parameters” set as the default view. This is where you’ll provide the hostname (server address) and port number to use. Next is the username and password which should match the user you created within your MySQL server. There are other tabs and fields located in this dialog box, but if you stick to the basics mentioned above, the connection will work.
Once you click the ‘ok’ button, MySQL Workbench will test the connection to make sure it is valid. Should an error pop up, review the data you have entered and make sure it matches the information you entered previously.
Conclusion
When all is said and done you should now have remote access to your MySQL server. If not, make sure to check that the port numbers and IP addresses were entered correctly and that they match at each step. I hope I was able to elaborate on this process, but I will include the links to the resources I used while making these changes. Thanks for reading!