NS8 mariadb problem

Support
1

NethServer Version: Nethserver 8.x
Module: ns8-mariadb

Hi,

I need some help with the mariadb module. I want to test it, so I uploaded some databases to the ./home/mariadb1/.local/share/containers/storage/volumes/mysql-data/_data directory with scp for testing purposes. I set the owner of the uploaded directories and files to match the owner in the directory (428678:428678). I created a user for the uploaded files and assigned them to them in phpmyadmin.

However, in phpmyadmin I cannot see or access the tables of the uploaded databases, it is as if there are no tables in them. However, listing the directories shows the files… What could be the problem?

Thank you for your help

2

hi @steve I hope it helps you

Ensures that the user and group 428678 are the owners

sudo chown -R 428678:428678 /home/mariadb1/.local/share/containers/storage/volumes/mysql-data/_data

Define correct permissions for directories (755) and files (644)

sudo find /home/mariadb1/.local/share/containers/storage/volumes/mysql-data/_data -type d -exec chmod 755 {} ;

sudo find /home/mariadb1/.local/share/containers/storage/volumes/mysql-data/_data -type f -exec chmod 644 {} ;

If you use a Podman container

sudo podman restart <container_name>

sudo systemctl restart married

Copying the files directly to the data directory is the main cause of this type of problem. The InnoDB storage engine, which is the default in MariaDB, uses a central file called ibdata1 to manage the metadata of the tables. If you did not copy that file along with the others, MariaDB does not have the necessary information to view your tables.

The safe and recommended way to migrate a database is by using the mysqldump tool. This process exports the database to a text file (.sql) and then imports it, ensuring that all data and metadata are transferred correctly without risk of corruption.

Export the original database using mysqldump:

  • mysqldump -u -p <database_name> > database.sql

Copy the .sql file to the new server.

Import the database using mysql:

  • mysql -u -p <database_name> <database.sql
1 Like
3

@jgjimenezs Thanks for the advice but the problem is that NS8 doesn’t know the mysql and mysqldump commands. I get the “Command not found” error message…

1 Like
4

In the mariadb container both mysql and mysqldump are available.

Enter the mariadb app environment:

runagent -m mariadb1

To copy a file to the container:

podman cp mysqlfile.sql mariadb-app:/

Enter the mariadb-app container:

podman exec -ti mariadb-app bash

Now you have should have the sql file and the commands available to be able to import the DB.

EDIT:

See also GitHub - NethServer/ns8-mariadb: Configure phpmyadmin and mariadb for ns8

1 Like
5

Thank you @mrmarkuz , I forgot about the container bash, so it’s now available and I have the root password. Now I just need to update my script for reloading the databases…

2 Likes
6

Unfortunately, this shortcut only allows you to copy one file, I would like to load more. I previously created a script for backing up and restoring databases, which creates a sql file for each database. The database files must be uploaded either with a directory or compressed as a single file.

You can only upload it to the host with scp, not to the container. Therefore, it could be uploaded to the /home/mariadb1 directory, which is already accessible from the mariadb-app container. The podman cp command can only copy files one by one by default, but it was possible to copy a directory as well.

Unfortunately, the mariadb container doesn’t have vim, so I can’t edit the database restore script or create it in the container, only on the host. So I uploaded it to the /home/mariadb1 directory and edited it, so it was accessible from the container as well. Running it restored all database files that were accessible via VPN.

7

It’s possible to copy a directory that contains files:

[mariadb1@node state]$ mkdir test
[mariadb1@node state]$ touch test/file1 test/file2
[mariadb1@node state]$ podman cp test mariadb-app:/

It’s possible to install software (like vim) in a container, see Howto manage or customize NS8 podman containers

You could scp to /home/mariadb1/.local/share/containers/storage/volumes/mysql-data/_data/ so the files will be located in the mariadb-app container under /var/lib/mysql

1 Like
8

Thank you @mrmarkuz I finally solved the problem in a similar way.

Unfortunately, I may have messed something up because yum wouldn’t run in the container. Programs installed in a container are usually deleted when the container is restarted, so I didn’t want to bother with it and looked for another solution.

I started with this :slight_smile: but either there was a problem with the backup or with the upload, but the copied libraries were incomplete, the tables disappeared from the databases. Unfortunately, I couldn’t set the owner either because I don’t know what 428678 numeric ownership and group could be. I set it numerically, but that wasn’t the reason for the tables disappearing. That’s why I had to make a backup with mysqldump and load it.

2 Likes