I have actually been hard at work in my so-called spare time, developing some rather interesting pieces of software some of which I hope to share eventually. The end of the month does sneak up on one, though, and I often find myself in a rush to finish the second of my monthly quota of blog posts. Maybe leaving my blogging duties to the last minute is beneficial, though, as it stops me from devoting time to it which would be more profitably spent on programming and socialising. Here then is, I hope, a satisfactory collection of technical hints and tips, or if you are reading this right after I press “Save” but before I get around to finishing the post, an unsatisfactory skeleton of a blog post, starting with information about backing up Serendipity.
S9y MySQL backup
As a fellow blogger points out, you can use the command line client mysqldump to produce a long SQL text file which, when run against a database, will recreate your Serendipity blog posts, comments and so on. That blogger suggests zipping this file (which is a good idea), and then emailing it to yourself, which is potentially a bad idea. Even assuming your hosting provider lets you run cron jobs, the idea of sending your entire MySQL database as a plain text file over unencrypted email protocols is a security nightmare, especially as the database dumps will contain unsalted password hashes which can be looked up in rainbow tables. Also, the advice does not cover the common scenario of having several different programs creating tables in the same database, or even two installs of Serendipity sharing a database. This use case is common enough to have motivated the “serendipity_” table prefix, customisable at install time.
My solution is:
mysqlshow -p $DBNAME | grep $TABLEPREFIX | sed s/[^a-z_]//g | tr ‘\n’ ’ ‘
to get the names of your tables, and then:
mysqldump -ce -p $DBNAME serendipity_access serendipity_authorgroups serendipity_authors serendipity_category serendipity_comments serendipity_config serendipity_entries serendipity_entrycat serendipity_entryproperties serendipity_groupconfig serendipity_groups serendipity_images serendipity_mediaproperties serendipity_options serendipity_permalinks serendipity_plugincategories serendipity_pluginlist serendipity_plugins serendipity_references serendipity_referrers serendipity_spamblock_htaccess serendipity_spamblocklog serendipity_suppress | gzip > serendipity-YYYY-MM-DD.sql.gz
using the list of tables from the first step.
Backslashes in MySQL
Have you ever noticed how you have to backslash your backslashes to get them into MySQL:
INSERT INTO users VALUES (5, "foo\bar");
SELECT * FROM users WHERE name LIKE "%foo%";
+----+----------+
| id | name |
+----+----------+
| 5 | foo\bar |
+----+----------+
but then backslash your backslashed backslashes to get them back out again:
SELECT * FROM users WHERE name LIKE "%\\%";
+----+---------+
| id | name |
+----+---------+
| 5 | foo\bar |
+----+---------+
?
Now imagine trying to write to write an SQL query which outputs lines that contain quoted arguments that are correctly escaped for running in a bash shell. The annoying thing is, it’s not technically impossible.
And finally…
Have you ever thought about trying to view the last lines of a file first on the command line, a bit like tail, but realised you wanted something like cat that worked backwards? If you have, did you guess that there must be a command called tac and then thought “Wow!” when you typed in man tac and found it was right there? Well, you should have. My only regret is that I didn’t think of this little gem when challenged to reverse a string using a bash one-liner:
echo "red rum" | sed ’s/./&\n/g’ | tac | tr -d ‘\n’ | xargs echo
mur der
As you can see, it takes a bit of inspiration from or was inspired by the above mysqlshow command.
Is this a sign of bash’s serendipity?
