Hackviking He killed Chuck Norris, he ruled dancing so he took up a new hobby…

20Mar/170

Kodi central db backup

Using a central database for all your Kodi media players is convenient. Only one of them need to scan for new content or you can even update the database straight away. It holds state across all the devices like paused movies, watched episodes etc. If you have a large library it takes time to scan it all again so you should keep it backed up. I didn't but now I do!

To use a centralized database for Kodi you need MySQL server. In my case I used my Wester Digital MyCloud EX2 as MySQL server. It rairly happens in Sweden but we had a power outage, some guy in hardhat was trusted with an excavator. After the power outage I never got the MySQL service up and running again on the WD so I just gave up and did the same solution I had in my San Francisco apartment, Raspberry Pi. Setting it up to host the shared database is easy, read all about it in my Raspberry PI: Central kodi database post. The only time consuming part is scanning of the contents to add it to the database, that's why you should have a backup.

Running several web servers and other system relying on MySQL I already have a backup script that I usually utilize but the WD have limited support for running custom stuff like scripts etc. Now this isn't a problem anymore since I'm moving over to the Pi.

Storage

So let's allow the NAS to do what it does best, storage and leave the rest to the new MySQL server. I created a backup share, a user with read/write access and enabled the share to be accessible over NFS.

On the Pi we need to mount the share. There are different ways of doing this depending on if you use SMB or NFS. In my case I'm using NFS. SO we start by making a mounting point for the the backup store/share.

sudo mkdir /mnt/backup

Then we open up and edit fstab to make sure it's mounted on every reboot.

sudo nano /etc/fstab

We add this line:

192.168.6.5:/nfs/Backups /mnt/backup nfs nolock 0 0

First is the IP of my NAS and then the NFS path of the share. Second parameter is the mount location. Then we specify that we want to use nfs and nolock for it to work with the NAS in question. This will prevent remote file locks and might cause issues if several clients try to write to the same file on the server. In this case it will only be the MySQL Pi writing to this directory so it shouldn't be an issue. Then we test that the fstab specs are working

sudo mount -a

Then just create a file or a folder in the directory and see if you can see it from another client, then you know for sure it's mounted and that you have write access. I usually do a reboot just to see that it mounts properly after a reboot.

The script

First we need to know the name of the databases. So we access the MySQL server.

mysql -u root -p

Enter the password when prompted and then list the databases.

show databases;

So in my case the databases where named kodi_music52 and kodi_video93. We are now ready to create our script. Go to your home directory and create a backup.sh script.

cd /home/pi
nano backup.sh

So the script is easy enough.

#! /bin/bash
_now=$(date +"%m_%d_%Y")
mysqldump --user root --password=supersecret kodi_music52 > /mnt/backup/kodisqlpi/music_$_now.sql
mysqldump --user root --password=supersecret kodi_video93 > /mnt/backup/kodisqlpi/video_$_now.sql
find /mnt/backup/kodisqlpi/* -mtime +7 -exec rm {} \;

The script line by line:

  1. Specifies that it's a bash script. It tells the system what interpeter to use.
  2. Using mysqldump to create a sql file of the entire database. This file contains all the sql commands for recreating the database and all it's data.
  3. Same as above.
  4. To make sure we don't fill up our storage with backup files we delete all the files older then 7 days. We use find to get files older then 7 days then use -exec flag to execute rm. The {} sends in each filename found by the find command.

Then we need to make the script executable and test run it. If you have used a sub directory in your mounted folder, like I have (kodisqlpi), you have to make sure it's exists otherwise the script will fail.

mkdir /mnt/backup/kodisqlpi
chmod +x backup.sh
/home/pi/backup.sh

Scheduling the script

If the script executed without errors you should now see the backup files on the share. Now we need to schedule the backup to run automatically. In linux we do this with cron jobs. I want to run it daily so we do crontab -e to edit and select 2 to use nano as the editor. Then we add our line specifying that the script should run every day at midnight.

crontab -e
0 0 * * * /home/pi/backup.sh

Then just save the file and you are good to go!

Posted by Kristofer Källsbo

Comments (0) Trackbacks (0)

No comments yet.


Leave a Reply

No trackbacks yet.