Connect to the MySQL database of Cloudron

[potemkin_ai]

I need to connect directly to the MySQL database of Cloudron, where the users stored - to get my hands on when the password has been last updated.

I've been trying to connect with sudo docker exec -ti mysql mysql -u root -p, with the password from sudo docker exec mysql env | grep CLOUDRON_MYSQL_ROOT_PASSWORD - but it didn't work.

Could you please, assist me with that?

Thanks!

[james]

Hello @potemkin_ai
Try to connect to your MySQL container with:

docker exec -ti mysql /bin/bash

and then to the server with:

mysql -uroot -p"${CLOUDRON_MYSQL_ROOT_PASSWORD}"

EDIT: Sorry, this would be for connecting to the MySQL Service that hosts all databases that apps use.

For connecting to the MySQL server that has the box and Cloudron users, you have to simply run this while being connected via ssh.

ssh root@my.$DOMAIN.$TLD

mysql -uroot -ppassword

[potemkin_ai]

@james , thanks a lot! Especially for such a prompt answer!

I've found out, that one can do sudo su - root instead of ssh, guess it might be of use to someone.

If you don't mind me asking: I can see the users tables contains two timestamp fields:

mysql> describe users;
+--------------------------------+---------------+------+-----+-------------------+-------------------+
| Field                          | Type          | Null | Key | Default           | Extra             |
+--------------------------------+---------------+------+-----+-------------------+-------------------+
| id                             | varchar(128)  | NO   | PRI | NULL              |                   |
| username                       | varchar(254)  | YES  | UNI | NULL              |                   |
| email                          | varchar(254)  | NO   | UNI | NULL              |                   |
| password                       | varchar(1024) | NO   |     | NULL              |                   |
| salt                           | varchar(512)  | NO   |     | NULL              |                   |
| resetToken                     | varchar(128)  | YES  |     |                   |                   |
| displayName                    | varchar(512)  | YES  |     |                   |                   |
| fallbackEmail                  | varchar(512)  | YES  |     |                   |                   |
| twoFactorAuthenticationSecret  | varchar(128)  | YES  |     |                   |                   |
| twoFactorAuthenticationEnabled | tinyint(1)    | YES  |     | 0                 |                   |
| active                         | tinyint(1)    | YES  |     | 1                 |                   |
| source                         | varchar(128)  | YES  |     |                   |                   |
| role                           | varchar(32)   | NO   |     | NULL              |                   |
| resetTokenCreationTime         | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| ts                             | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| avatar                         | mediumblob    | NO   |     | NULL              |                   |
| loginLocationsJson             | mediumtext    | YES  |     | NULL              |                   |
| creationTime                   | timestamp     | NO   | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| inviteToken                    | varchar(128)  | YES  |     |                   |                   |
| backgroundImage                | mediumblob    | YES  |     | NULL              |                   |
| language                       | varchar(8)    | NO   |     |                   |                   |
| notificationConfigJson         | text          | YES  |     | NULL              |                   |
+--------------------------------+---------------+------+-----+-------------------+-------------------+
22 rows in set (0.00 sec)

the ts and creationTime - from the name alone, I would expect creationTime to be a timestamp of when the user has been created and ts - like a timestamp of the last update.

But in my case those both fields are exactly the same.

Is there some way to determine the time the entry has been updated?

[james]

Hello @potemkin_ai
You could also check the Cloudron event log to see if and when the password was changed. Have you checked that?