I have tried suggestions in what seems like every post about this topic and none of them have worked for me, so I'm posting my own in case anyone spots anything that I missed.
I'm migrating a wordpress site into a LEMP (ubuntu, apache) stack instance on Digital Ocean, and I need temporary access to the database via MySQL Workbench or something similar on my local machine. I'm planning to turn it off once the migration is done. However I am having a very weird issue I can't figure out the cause for.
If I attempt to sign into the sql instance using
mysql -u user -h my.ip.address -p
I can get access from my local machine just fine.
I can also SSH into the server itself via terminal just fine, I have it set up in my ssh config file using the same ip address and the same user and ssh key i'm attempting to use for my workbench connection.
However doing the same via an SSH connection on workbench I constantly get the error:
Authentication failed, access denied.
Failed to connect to MySql at my.ip.address:3306 through ssh tunnel at [email protected] with user mysqluser
Things I've done to attempt to rectify this:
- set bind-address in mysql config to
0.0.0.0 on the server and restarted server's mysql
- set bind-address in mysql config to
my.ip.address on the server and restarted
- allowed tcp connection
- allowed connection via ufw firewall to 3306 and 33060 ports
- created a mysql user with all privileges granted for 'localhost', '%', and '0.0.0.0' hosts
- tried sshing in as root user
- tried using the root user for the mysql user
I am probably missing some things on that list as I feel like I've tried a million things already!
I even have a different server on a LAMP stack (not LEMP) that I set up a few days ago and was able to gain access to the mysql db on workbench by following the same steps, and even attempted backtracing and copying what I did on the LAMP server, but no go.
I checked to see the netstats of the mysql instance and match the port and the ip and that didn't work, I made sure the port of the SSH was correct, nothing is working.
Does anyone have any tips? I've been using all of the "allow remote mysql connection on digital ocean" articles and questions i can find but nothing is working.
Here is my Workbench connection config at the moment but i have tried what feels like every iteration of it i could think of (re users, passwords, IP addresses, and ports)
SSH Hostname: my.ip.address
SSH Username: user
SSH Password: user-password (also tried empty, since i have a key)
SSH Key File: same key file i use to ssh on terminal
Mysql Hostname: 127.0.0.1 (also tried my.ip.address)
MySql Server Port: 3306 (also tried 33060)
Username: MySqlUser
Password: MySqlUser-Password (works when connecting to mysql via terminal)
