Backup and Restore MariaDB

[mcguirepr89]

Hello! This is a discussion dedicated to backing up and restoring the MariaDB. The steps I outline here are the recommended backup and restoration methods.

Using the "Database Maintenance" Tool (Adminer)

You can very easily create a backup of your database using the "System" > "Tools" > "Database Maintenance" tool.

Create the backup

1. To do so, open the "Database Maintenance" tool and connect to the database (i.e., login to Adminer to connect to the database)
   
2. Once connected, click the "Export" link on the left side of the window, which will bring you to the page you see below.
   
3. Choose "gzip" as the Output, "SQL" as the Format, and select "DROP+CREATE" for both the Database and the Tables leave everything else as is.
4. Finally, click Export and you will begin downloading a copy of your database.

Restore the backup

1. To restore the backup, you will again use the "Database Maintenance" tool to connect to the database (login to Adminer)
   
2. Once connected, click the "Import" link on the left side of the window, which will bring you to the page you see below
   
3. Click the "Choose Files" button to select the database backup you created (you will see the file name to the right of "Choose Files" after you have selected it).
   
4. Finally, click Execute and you will see that the database has been restored.
   
   That's it! You restored the database!

Using mysqldump and mysql

You can also create and restore backups in the command line, though it is not quite as easy as the method above.

Create the backup

1. First, ensure you have your database password handy (preferably in the clipboard ready to paste).
2. With your database password handy, issue the command below, which will prompt you for the password:

   mysqldump -ubirder -p birds detections > /home/pi/BirdNET-Pi/scripts/detections.sql
   

   The command above created a backup of the detections table of the birds database and placed that file in the scripts directory.
3. Use the "System" > "Tools" > "File Manager" tool to navigate to the scripts directory and download a copy of the detections.sql file.

Restore the backup

1. Using the "System" > "Tools" > "Database Maintenance" tool, upload the detections.sql backup file to the scripts directory.
2. In a terminal, change the permissions of the backup so that pi owns and can use the backup.

   sudo chown pi:pi /home/pi/BirdNET-Pi/scripts/detections.sql
   

3. Then restore the backup (the command below will prompt you for your new installation's database password, so don't use the password from the old database).

   mysql -ubirder -p birds < /home/pi/BirdNET-Pi/scripts/detections.sql
   

That's it! You restored the database!

[Svardsten53]

Today I did a new installation and planned to restore the old database from a backup. The instructions above seems very simple and straightforward, but I failed. I tried both with Adminer tool and manual commands. I believe it's very important this instruction works. Better safe than sorry.

[mcguirepr89]

On your new installation, login to the database (that is presumably empty) and delete the existing database.
Go to the "Alter Database" link near the top:


And choose to "drop" the existing birds database.

Then try to import the backup and let me know if that worked. (There is a step in the backup process I will alter to make sure the dumped backup knows to drop any existing databases named "birds").

[Svardsten53]

That worked! Thanks for the support Patrick.

[mcguirepr89]

You are very welcome! I've adjusted the original post to show that the backup needs to drop the existing birds database