Z
Zerops•5d ago
david

Problems with restoring Mariadb backup

Hi everyone, I got stuck trying to restore Mariadb backup in Mariadb zerops service. Here is what I have tried so far: 1. Restoring backup by uploading it to adminer - this has failed due to size of the .gz archive which is 130MB. 2. Using mariadb-backup command and zerops vpn from my local PC - I was unable to use this command with the zerops Mariadb service. Are there any suggestions on what might work for me? Thanks a lot for all answers!
7 Replies
Jan Saidl
Jan Saidl•5d ago
Hi @david , we currently use the mariadb-backup tool for backing up MariaDB databases. Unfortunately, the created backup does not allow restoration (yet) via GUI or remotely over the network without direct access to the running database. However, there is a way to create an SQL dump. Please give me some time, and I’ll prepare instructions on how to do it.
Jan Saidl
Jan Saidl•4d ago
Hi @david , I've prepared a recipe that allows importing a MariaDB backup. The source of the recipe is here: https://github.com/zeropsio/recipe-mariadb-restore How to use the recipe can be seen in the video. If anything isn't clear, feel free to ask.
Jan Saidl
Jan Saidl•4d ago
If the export is large and the export/import has higher demands, it would be good to scale up, possibly the RAM in the container, before the action. zsc scale ram +2GB 10m There is one problem, that after export the terminal stops being responsive so a terminal refresh will help. @Petra Can it be added to the documentation? For now, until it's integrated into the GUI. Thanks.
david
davidOP•3d ago
Wow, that looks amazing and it was really fast. Thanks a lot! I am going to try. Hi, I was able to create the restore service sucessfully, but the service does not download the backup somehow, the resulting .sql file contains only some comments and I can't see the data there. My backup file has 130MB, 1.2GB unpacked and I have increased the disk size of the service to 10GB and ram to 3GB.
Michal Saloň
Michal Saloň•3d ago
Hi, may I try to run the script in your container to see what is happening? May I ask, do you use the main mariadb schema, or some other randomly named schemas prefixed by an underscore (similar to _Aas5d4ASad545)? The current dump script only dumps the main database schema, which appears to be empty in your DB.
david
davidOP•2d ago
Sorry for the previous comment. You are actually right, I have a named schema in my database in this case. Although how do I verify your identity before we would proceed with hands-on support?
Michal Saloň
Michal Saloň•2d ago
You can see that my role is "Dev team", so I am one of the Zerops' devs. Anyway, I have verified the backup restore, and you can use the following commands (it's basically what the backup.sh does, just slightly modified): Replace the BACKUP_URL with the actual url of the backup file, copied from the frontend: 
# download the backup
rm -rf "/var/www/backup"
mkdir -p "/var/www/backup"
curl "BACKUP_URL" | gzip -d -c - | mbstream -x -C "/var/www/backup"

# prepare the backup
mariabackup --prepare --target-dir="/var/www/backup"

# start the temporary maria from the backup
sudo mariadbd --datadir="/var/www/backup" --log-bin --skip-grant-tables --user=root --socket="/tmp/mysql-restore-manual.sock" --port="3307" --pid-file=/var/www/mariadbd-restore.pid &

# list the database/schema names
mariadb -S /tmp/mysql-restore-manual.sock -e "SHOW DATABASES;"

# dump the schemas you want
mariadb-dump -S /tmp/mysql-restore-manual.sock -B "_50xxxxxxxx" "_84yyyyyyyy" > ./backup.sql

# check if the file contains the data
head -n 50 ./backup.sql

# restore the backup (substitute the correct values for your new database)
mariadb -u USER -h HOST -pPASSWORD < backup.sql
# download the backup
rm -rf "/var/www/backup"
mkdir -p "/var/www/backup"
curl "BACKUP_URL" | gzip -d -c - | mbstream -x -C "/var/www/backup"

# prepare the backup
mariabackup --prepare --target-dir="/var/www/backup"

# start the temporary maria from the backup
sudo mariadbd --datadir="/var/www/backup" --log-bin --skip-grant-tables --user=root --socket="/tmp/mysql-restore-manual.sock" --port="3307" --pid-file=/var/www/mariadbd-restore.pid &

# list the database/schema names
mariadb -S /tmp/mysql-restore-manual.sock -e "SHOW DATABASES;"

# dump the schemas you want
mariadb-dump -S /tmp/mysql-restore-manual.sock -B "_50xxxxxxxx" "_84yyyyyyyy" > ./backup.sql

# check if the file contains the data
head -n 50 ./backup.sql

# restore the backup (substitute the correct values for your new database)
mariadb -u USER -h HOST -pPASSWORD < backup.sql

Did you find this page helpful?