Lost the credentials to access to my MySQL/MariaDB Database

ℹ️ Support
1

Nextcloud version : 18.0.3.1
Operating system and version: Ubuntu 19.10
Apache or nginx version: Apache/2.4.41
PHP version: 7.3

The issue you are facing:

I want to migrate my database from SQLite to MySql. The problem I’m facing is that I forgot my Database password and user. I made a lot of Nextcloud installations and it seems that somehow I lost the document where this credentials were saved. I know that in older versions of Nextcloud it was possible to retrieve this information from config.php file, but I checked in it and there’s nothing about the Database.

Is there something I can do?

The output of my config.php file:

<?php
$CONFIG = array (
  'instanceid' => 'ocxxtxxxxszr',
  'passwordsalt' => 'qxTx0zxqxyxLxBxDxdxVxT1xQxGtx7',
  'secret' => 'xxyZxxxxDxLFxxxx0xxxplZfxx4Fxhxxx9Sxxxlx9xxxh7zl',
  'trusted_domains' => 
  array (
	0 => 'sub.mydomain',
	1 => 'mydomain',

  ),
  'datadirectory' => '/media/Data/',
  'dbtype' => 'sqlite3',
  'version' => '18.0.1.3',
  'overwrite.cli.url' => 'https://mydomain/nextcloud',
  'installed' => true,
  'has_rebuilt_cache' => true,
  'updater.secret' => '$2xxxxxQCDxxJ7o/h7JQbxxxxFVVeotzyxxxxlAxxxx0bjiOIxxxAHzHv9e',
  'maintenance' => false,
  'theme' => '',
  'loglevel' => 2,
  'memcache.local' => '\OC\Memcache\APCu',
);
2

sqlite doesn’t use username and passwords, it relies on filesystem permissions.

3

So for the migration with occ, what should I use when it asks me for a admin user and a password? Leave it blank? How do I migrate to mysql

4

I assume it’s asking for the mysql username and password, that you created with the mysql_secure_installation command.

5

Ok. So this command needs to be run before doing the migration?

Ps. I’m a noob with nc, sorry if it’s an obvious question.

6

That command needs to be run after installing mysql. I do not know if it can be dangerous running that command after creating data inside mysql.

7

But I’m currently using SQLite on my DB… Is there any kind of problem? I mean, I can’t remember if I ran this command when I first made the installation (which was months ago).

8

If you have no data inside mysql (not even from other apps, be careful) it should be safe.

If you want to be safer, backup /var/lib/mysql before

9

I’ll try. Thank you. I’ll write back the results.

10

You can read this page if you want to know more about that command: https://mariadb.com/kb/en/mysql_secure_installation/

(I really don’t know if you are using mysql or mariadb, but they’re supposed to be drop-in compatible, up to a point)

11

Isn’t there any config file where the credentials can be seen? Like config.php on older versions.

12

The credentials can be seen in config.php in the last version, if the database uses them.

You need to reset your mysql root password (or provide the default empty one, which nc will not accept, I believe) https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

13

I finally managed to solve it. There’s a way to restore a password in MariaDB/MySQL.

You first need to kill all the mysql related processes:

$ sudo systemctl stop mariadb
$ sudo killall mysql
$ sudo killall mysqld_safe

Then you need to start the mysql service with the “skip grant tables” option, which will allow you to avoid using any passwords.

$ sudo mysqld_safe --skip-grant-tables &

Once you’re in you can list the database users and your databases with:

SELECT User FROM mysql.user;
SHOW DATABASES;

Once this is done, you can reset the “root” password for MariaDB/MySQL using:

 use mysql;
 update user SET PASSWORD=PASSWORD("password") WHERE USER='root';
 flush privileges;
 exit;

The first sentence is because we want to use the “mysql” database, where this user is stored.

After this your MariaDB/MySQL root password will be the one you just set.