Remote Connect to MySql AWS Lightsail
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:
- 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;
- 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)
- exit
service mysql restart(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.