Automatic and regular backup of the MySQL database

I've recently set up a few databases for my company using MySQL. It all resides on one server and as such I would like to back up all the databases on that very server for error recovery purposes.

Now the first thing we need to know is how to make a backup of all the databases residing in the server. This is easily accomplished with an application called mysqldump whose sole purpose is to back up your databases. It's also part of the community edition of MySQL so you probably already have it installed if you are currently running MySQL.

So let's see how it works in the example below!

mysqldump -u root --single-transaction --all-databases > alldatabases.sql

This will effectively dump all your databases in SQL format to the file alldatabases.sql which you could later import in case you lose your data.

Now this is enough to backup your databases once, but you probably want to do this every day. So I wrote a small bash script to do the job. It's listed below and keeps 10 days of full backups.


#!/bin/bash

BACKUPDIR=/var/Backup/database
cd "$BACKUPDIR";
FILECOUNT=$(ls | wc -l)

echo "Backing up database....";
nice -19 mysqldump -u root -pmysecret --single-transaction \
--all-databases > alldatabases-$(date +%F).sql

if [ "$FILECOUNT" -gt 10 ]
then
OLDFILE=$(ls -tr | head -n 1)
rm -f "$OLDFILE";
echo "Removed oldest file $OLDFILE";
else
echo "We don't have 10 days of backup yet: Not removing anything.";
fi


Note that you will have to exchange the username and password in the mysqldump application call to match your own setup. Also it might be a good idea to set the BACKUPDIR variable so something more useful.

About selecting a number of columns from a Text file

Today I had an interesting issue. I received a really big file with lots of columns in it. I only needed a few of them though, so I set out to write a quick Perl script to do the job, but then I thought to myself: "I'd better Google before I do this since it seems like a common enough problem.". Of course someone had already written a program like that. It's called cut and can be used from your terminal. I needed columns 1 to 22 from the file so I just issued

cut -f 1-22 myfile.txt > mynewfile.txt

This made the file "mynewfile.txt" contain columns 1 to 22. You can also specify what kind of separator you want. If you don't choose any the program will assume TAB as the field separator.

Converting an mpeg2 file to a DVD

I got an interesting question from my brother the other day. He wanted to know whether I could help him convert his wedding movie, in mpeg format, to a DVD. The reason for this was that his friends, and himself, had old DVD players that did not support anything other than plain DVD format. So this is just a small description of how I did it.

I used dvdauthor to solve this problem. Dvdauthor does just what it says: it helps you author DVD's. There is a GUI for it, but I used it from command line since I only needed the basic functionality.

No of course I couldn't apply dvdauthor directly since it requires the mpeg's to be in VOB format. So the first thing I did was to convert my mpeg file to VOB format with the following line:

ffmpeg -i videofile.mpeg -target dvd videofile.vob

Now we have a workable format in the file called "videofile.vob". This is the file we will let dvdauthor work with. In order for dvdauthor to be able to do it's job we need to let it know the basic structure of the DVD we want to create. We will describe this structure using an XML file. In principle you can just open up your favourite editor and enter the following:

<dvdauthor>
 <vmgm />
   <titleset>
     <titles>
       <pgc>
         <vob file="your_video.mpg" />
       </pgc>
     </titles>
   </titleset>
</dvdauthor>

and then save the file as dvd.xml. This will give you only on chapter, i.e., no chapters which can be rather limiting, but I didn't need anything fancy. So basically we can call dvdauthor now like this:

dvdauthor -o dvd -x dvd.xml

If you look in your directory now, you will have a new folder called dvd containing the AUDIO_TS and VIDEO_TS folders. You can now burn this to a dvd with growisofs like this:

growisofs -dvd-compat -Z yourdvddevice -dvd-video ./dvd/

Well, that did it for me. I hope you'll find it useful. Thank you and good night. :)