You may have an AWS Lightsail instance with a MySQL database server, and you may have been trying to connect AWS Lightsail MySQL remotely from another web application or web server. We will show you the easiest and 100% working method that will guide you to connect AWS Lightsail MySQL remotely with four easy steps.
Don’t worry, you don’t have to be the master of Linux to connect AWS Lightsail MySQL remotely, but we recommend skipping this tutorial if you don’t have a basic knowledge of Linux/Unix Shell Command. Because AWS provides an instance’s root access, making a single mistake can ruin your whole day. But for education purposes, to learn how to connect AWS Lightsail MySQL remotely, you can go ahead.
So let’s get started if you’re confident.
Table of Content
Steps on How to Connect AWS Lightsail MySQL Remotely
Step 1: Find MySQL Configuration
All you need, connect your Amazon Web Service Lightsail Instance using SSH or connect directly from AWS Lightsail instance panel. Follow the instructions.
First, install MySQL on your Lightsail instance if it’s not installed yet. Learn how to install MySQL on AWS Lightsail.
Next, find the MySQL configuration file. The file name should be my.cnf
Common possible paths where the file can be
- /etc/my.cnf
- /etc/mysql/my.cnf
- $MYSQL_HOME/my.cnf
- [datadir]/my.cnf
- ~/.my.cnf
- /opt/bitnami/mysql/conf/my.cnf
- /opt/bitnami/mariadb/conf/my.cnf
Don’t waste your time looking for every directory. You can find the configuration file using the command line.
Run the command below
mysql --help
The output should be like this.
Default options are read from the following files in the given order:
/opt/bitnami/mariadb/conf/my.cnf ~/.my.cnf
…
From the beginning of the output, you will get the configuration file link. Or you may also use this command.
find . -name my.cnf
For Debian based Operating System, the file location is /opt/bitnami/mariadb/conf/my.cnf
Step 2: Updating MySQL Configuration
By default MySQL Server only allows requests from the same server. Here, we need to rewrite the rule so that it accepts all anonymous request.
Run the command below.
nano <MYSQL_CONFIGURATION_PATH>
For example:
nano /opt/bitnami/mariadb/conf/my.cnf
If returns permission is denied, you can alter the command like this.
sudo nano /opt/bitnami/mariadb/conf/my.cnf
The content of my.cnf file should appear in the terminal. Now replace the link below with the second one.
bind_address=127.0.0.1
Change to
#bind_address=127.0.0.1
Simply comment in the bind_address variable; MySQL will skip this in the future. If you don’t want to allow anonymous requests, then simply add another line below with the IP address of your trusted server/application.
bind_address=127.0.0.1
bind_address=XX.XX.XX.XX # replace XX with your trusted IP address.
Now press Ctrl + o to write down and then press Enter to save the file. Then press Ctrl + X to exit from the terminal. This part can be different for other distributions. Apply ordinary senses here.
Step 3: Restart Services
To connect AWS Lightsail MySQL remotely, run the command below to restart all the Bitnami services like apache, MySQL, etc
sudo /opt/bitnami/ctlscript.sh restart
The expected output is
Restarting services…
Wait a minute. If no error returns, everything went well. Now you may close the terminal.
Step 4: Open MySQL Port for IPv4 Firewall
Now it’s time to play with firewalls.
Go to the manage page of your instance. Goto Networking tab and scroll down to IPv4 Firewall
Click on Add a rule
Select MySQL/Aurora from the application list. The protocol should be TCP, and the port should be 3306 automatically.
For security reasons, you should restrict IP addresses for remote connections.
Create the rule and reboot your instance by clicking on the Reboot button.
Conclusion
Tada! You are ready now. Now you can connect AWS Lightsail MySQL Remotely.
Another useful article for AWS developers 10 reasons to use WP Dark Mode plugin on your site
