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:
2014-10-05-backing-up-a-mysql-database-to-individual-text-files.txt
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).
- The first trick is using the Unix 'tr' command to change the title's spaces to dashes before outputting.
- 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.
- The whole thing runs in an 'if then' loop so that empty entries don't crash the script.
#!/usr/local/bin/bash DBUSER='MY_USER_NAME' DBPASS='MY_DB_PASSWORD' DBNAME='MY_DB_NAME' IDNUMBER=1 while [ $IDNUMBER -lt 250 ] do echo "Counter: $IDNUMBER" if mysql -u $DBUSER -p$DBPASS -e "select id from serendipity_entries where id='$IDNUMBER' " $DBNAME; then 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 else echo "$IDNUMBER is empty." fi let IDNUMBER=IDNUMBER+1 done
Trackbacks
The author does not allow comments to this entry
Comments
Display comments as Linear | Threaded