HomeWiki

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nextcloud          |
| performance_schema |
| sitedatabase       |
+--------------------+

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+----------+-------------------------------------------+
| host      | user     | password                                  |
+-----------+----------+-------------------------------------------+
| localhost | root     | *AF926838A9A0501BA7017D135C123C007497F85B |
| localhost | www-data | *AF926838A9A0501BA7017D135C123C007497F85B |
| localhost | nc-user  | *AF926838A9A0501BA7017D135C123C007497F85B |
+-----------+----------+-------------------------------------------+

MariaDB [(none)]> SELECT User, Db, Host from mysql.db;
+----------+--------------+-----------+
| User     | Db           | Host      |
+----------+--------------+-----------+
| nc-user  | nextcloud    | localhost |
| www-data | sitedatabase | localhost |
+----------+--------------+-----------+

CREATE USER 'www-data'@'localhost' IDENTIFIED BY 'your_password';
CREATE USER 'nc-user'@'localhost' IDENTIFIED BY 'your_password';

MariaDB [(none)]> show grants for 'root'@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+

MariaDB [(none)]> show grants for 'www-data'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for www-data@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'www-data'@'localhost' IDENTIFIED BY PASSWORD '*AF926838A9A0501BA7017D135C123C007497F85B' |
| GRANT ALL PRIVILEGES ON `sitedatabase`.* TO 'www-data'@'localhost'                                              |
+-----------------------------------------------------------------------------------------------------------------+

MariaDB [(none)]> show grants for 'nc-user'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for nc-user@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nc-user'@'localhost' IDENTIFIED BY PASSWORD '*AF926838A9A0501BA7017D135C123C007497F85B' |
| GRANT ALL PRIVILEGES ON `nextcloud`.* TO 'nc-user'@'localhost'                                                 |
+----------------------------------------------------------------------------------------------------------------+

GRANT ALL PRIVILEGES ON `sitedatabase`.* TO 'www-data'@'localhost'
GRANT ALL PRIVILEGES ON `nextcloud`.* TO 'nc-user'@'localhost'

Dump the databases on the original server. Better extract them one-by-one so we don't migrate anything unnecessary. To dump as single databases, use the following commands:

  # mysqldump -u root -p --all-databases > all_databases.sql          <--- [All Databases]
  # mysqldump -u [user] -p --opt [database name] > database_name.sql  <--- [Single Database]
  
  # mysqldump -u root -p --opt sitedatabase > sitedatabase_dump.sql
  # mysqldump -u root -p --opt nextcloud > nextcloud_dump.sql

Once the dump is completed, you are ready to transfer the databases. But databases need to be created first on target server.

  # mysql -u root -p 
  CREATE DATABASE sitedatabase;
  CREATE DATABASE nextcloud;
  quit;

Retore the data to the newly creates databases:

  # mysql -u [user] -p --all-databases < all_databases.sql           <--- [All Databases]
  # mysql -u [user] -p newdatabase < DUMP-SITE-DATABASE.sql          <--- [Single Database]
  
  # mysql -u root -p sitedatabase < sitedatabase_dump.sql
  # mysql -u root -p nextcloud < nextcloud_dump.sql