Skip to content

Backing up a MySQL database to individual text files

The situation is as follows: you have a MySQL database that backstops your blog, and you'd like to output each database entry as an individual text file. Not as strange an idea as it seems - maybe you'd like to output whatever edits you made to already-published articles, maybe you'd like a bunch of text files you can use as a backup in case your relational database kicks the bucket. Whatever the reason, anyone who's ever written a blog will agree that significant time and effort goes into writing, and the risk of losing all that text is formidable and vaguely hair-raising.

Here is a script I put together that goes through every entry in a blog's database, and outputs the text to a file whose title is created from the data and the name, with dashes, like this:


I run Serendipity (S9Y) so all the writing goes into a single table. Let's say your database is called 'musings' in your MySQL system. Enter that database using the MySQL command prompt and poke around. Type 'show tables' to list all tables that make up that database. On a Serendipity system, all the writing goes into a table called serendipity_entries, specifically into the fields 'body' and 'extended'. No matter whether you're using Wordpress or Joomla or something else, it works the same way: log into your database and poke around until you find out where the text is located, and modify the appropriate fields in this script.

There are a few tricks in play here, and a few obvious ways it could be improved (For example, I know there are fewer than 250 entries, so I used a 1 &endash; 250 loop. It would be smarter to query the database to figure out what the highest number used is. I'll fix that in a next version).

  1. The first trick is using the Unix 'tr' command to change the title's spaces to dashes before outputting.
  2. The second trick is getting the date into a useable YYYY-MM-DD format. The mysql commands 'date_format' and 'from_unixtime' do the leg-work.
  3. The whole thing runs in an 'if then' loop so that empty entries don't crash the script.


while [ $IDNUMBER -lt 250 ]
echo "Counter: $IDNUMBER"
if mysql -u $DBUSER -p$DBPASS -e "select id from serendipity_entries where id='$IDNUMBER' " $DBNAME;
        ARTTITLE=$(echo "select title from serendipity_entries where id=$IDNUMBER" | mysql 
                 $DBNAME -u $DBUSER -p$DBPASS -Ns) 
        echo "ID: $IDNUMBER  Title: $ARTTITLE"

        OUTTITLE=$(echo $ARTTITLE | tr ' ' '-' ).txt
        echo "New Title: $OUTTITLE"

        OUTFECHA=$(echo "select date_format(from_unixtime(timestamp), '%Y-%c-%d') 
                  from serendipity_entries where id=$IDNUMBER" | mysql $DBNAME -u $DBUSER -p$DBPASS -Ns)
        echo "Date: $OUTFECHA"

        mysql -u $DBUSER -p$DBPASS -sN -e "select body from serendipity_entries where 
                id='$IDNUMBER' into outfile '/tmp/tmpbody'; " $DBNAME
        mysql -u $DBUSER -p$DBPASS -sN -e "select extended from serendipity_entries where 
                id='$IDNUMBER' into outfile '/tmp/tmpextended'; " $DBNAME

        cd /tmp
        cat tmpbody tmpextended > tmpwildspacearticle.txt
        cp tmpwildspacearticle.txt /home/randymon/articles/$OUTFECHA-$OUTTITLE

        rm /tmp/tmpbody
        rm /tmp/tmpextended
        echo "$IDNUMBER is empty."



No Trackbacks


Display comments as Linear | Threaded

No comments

The author does not allow comments to this entry