Remote Connect to MySql AWS Lightsail

Question

I'm setting up a new server on AWS Lightsail and all is good, but in my LEMP stack, I installed MySql and created my DB. For some reason though I cannot remote into that DB via my DB client (using TablePlus).

In the past using Vultr or Digital Ocean servers I had to add the IP address to the etc/mysql/mysql.conf.d/mysqld.cnf file, like this:

# bind-address = 127.0.0.1
  bind-address = 34.xxx.xxx.xxx

This would allow me to login with my DB username and pass that I created.

Does AWS Lightsail not allow for this? They have a Create DB Instance service that they want you to buy, but trying to stay cheap on this and only need the DB I built in my server.

Any help on how to connect remotely with a DB client would be extremely appreciative.

Using Ubuntu 18.04 LTS

Answer 1

So I figured it out. If anyone comes across this issue try the following.

AWS Lightsail gives you 2 IP's

• Static IP = 34.xxx.xxx.xxx
• Private IP = 172.xx.xx.xx

In your etc/mysql/mysql.conf.d/mysqld.cnf file do the following:

# bind-address = 127.0.0.1 (Disable this)
# bind-address = 34.xxx.xxx.xxx (Don't use Static IP)
  bind-address = 172.xx.xx.xx (Use Private IP)

In AWS Lightsail Firewall Add MySQL/Aurora | TCP | 3306

Run the following commands on server:

sudo service mysql stop
sudo service mysql start

Connecting With DB Client:

• Host/Socket = 34.xxx.xxx.xxx (Use Static IP)
• Port 3306
• User: admin (Your created user account (See below how to do this))
• Password your_new_pass_here (Your created password)
• Database: mydatabase (Your created DB name)

That's it. All should work now.

---

As mentioned below, you do need to create a new MySql User. You can do so like this:

1. Create DB User so we can remote into it from local machine/ database client:

mysql -u root -p'' (Login to MySql with the credentials you used to create MySql, -u might be different)

2.

CREATE USER 'admin'@'34.xxx.xxx.xxx' IDENTIFIED BY 'your_new_pass_here';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'34.xxx.xxx.xxx' IDENTIFIED BY 'your_new_pass_here';

CREATE USER 'admin'@'%' IDENTIFIED BY 'your_new_pass_here';

GRANT ALL PRIVILEGES ON *.* TO 'admin' IDENTIFIED BY 'your_new_pass_here';

3.

FLUSH PRIVILEGES;

4. Bind Address

To get Database working remotely go to /etc/mysql/mysql.conf/mysqld.cnf and change the bind-address:

# bind-address = 127.0.0.1 (Disable this)
# bind-address = 34.xxx.xxx.xxx (Don't use Static IP)
  bind-address = 172.xx.xx.xx (Use Private IP)

5. exit

6. service mysql restart

7. (Add to new .env if using Laravel)

DB_CONNECTION=mysql
DB_HOST=34.xxx.xxx.xxx
DB_PORT=3306
DB_DATABASE=database_name
DB_USERNAME=admin
DB_PASSWORD=your_new_pass_here

---

If you need to get a list of your MySql User do this: SELECT User FROM mysql.user; Helpful to double check your new user is in the system.

Answer 2

You need to tell Lightsail that the MySQL port should be available publicly. Documentation is here.

I haven't used Lightsail myself (I use full AWS), but see if you can whitelist only your specific IP using the either the Lightsail firewall, or using iptables on Linux.