I have a MySQL server running on ubuntu server. I've been connecting remotely to it with mysql workbench for months. I recently rebooted my Ubuntu server and afterwards I am now unable to connect remotely to the MySQL database with MySQL workbench. I can still login locally to the MySQL database with the same user once I've ssh in to the Ubuntu server. I'm trying to follow the trouble shooting steps in the link below. Does anyone see what the issue might be?
following steps here:
https://devanswers.co/cant-connect-mysql-server-remotely/
Port Scanning host: xxxxxxxx
Open TCP Port: 3306 mysql
Port Scan has completed…
looks like the firewall already had rules for mysql
sudo ufw status
[sudo] password for scotsditch:
Status: active
To Action From
-- ------ ----
22/tcp ALLOW Anywhere
3306 ALLOW Anywhere
22/tcp (v6) ALLOW Anywhere (v6)
3306 (v6) ALLOW Anywhere (v6)
not finding bind-address
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
update:
MySQL Workbench error:
Your connection attempt failed for user 'user' to the MySQL server at xxx.xxx.xx.xxx:3306:
Access denied for user 'user'@'%%' (using password: YES)
Please:
1 Check that MySQL is running on address xxx.xxx.xx.xxx
2 Check that MySQL is reachable on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the user user has rights to connect to xxx.xxx.xx.xxx from your address (MySQL rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for xxx.xxx.xx.xxx connecting from the host address you're connecting from
update:
sudo mysqldump --all-databases > dump_042021.sql
mysqldump: Error: 'The user specified as a definer ('mysql.infoschema'@'localhost') does not exist' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW DATABASES': The user specified as a definer ('mysql.infoschema'@'localhost') does not exist (1449)
update:
sudo mysqld --upgrade=FORCE
2021-04-21T15:25:00.312253Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 10803
2021-04-21T15:25:00.313486Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2021-04-21T15:25:00.313520Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-21T15:25:00.313620Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server - GPL.
update:
select host,user,grant_priv,super_priv from mysql.user;
+-----------+------------------+------------+------------+
| host | user | grant_priv | super_priv |
+-----------+------------------+------------+------------+
| localhost | root | Y | Y |
| localhost | mysql.session | N | Y |
| localhost | mysql.sys | N | N |
| localhost | debian-sys-maint | Y | Y |
| % | user2 | N | N |
| % | user1 | N | N |
| 0.0.0.0 | user1 | N | N |
| % | user3 | N | N |
+-----------+------------------+------------+------------+
mysqldump -u user1 -p --all-databases > dump_042121.sql
Enter password:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW DATABASES': Access denied for user 'user1'@'%' (using password: YES) (1045)
