Home / server / Questions / 1014142
Accepted
daugaard47
Asked: 2020-04-26 08:13:04 +0800 CST

Remote Connect to MySql AWS Lightsail

  • 772

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

ubuntu mysql amazon-web-services amazon-lightsail

2 Answers

  1. Best Answer

    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;
    1. 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)
    1. exit

    2. service mysql restart

    3. (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.

    • 5

  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.

    • 0