j.modjeska

I was in the middle of upgrading the Wasington Parkour forum software to the new phpBB 3.0 when I got distracted by a MySQL limitation I wasn’t previously aware of: it seems you can’t drop more than one table at a time in the MySQL command line interface. This curious limitation begs for a command-line script solution, and indeed I was not surprised to find that there are plenty of them out there. As usual, though, I’m reinventing the wheel for my own entertainment.

For today’s exercise, I experimented with PHP on the command line (PHP CLI). Some of the features I wanted to include weren’t in the scripts I found elsewhere, so I built them in order to make this useful for my current phpBB upgrade and on into the future for various other situations:

• Allow the user to store db login information in the script if he really wants to, but if he’s a security-conscious and relatively sane person, leave it empty in the script and prompt him for the login information when the script is called.
• Allow the user to specify table prefixes so only certain tables are deleted. When I experienced epic failure on my first attempted phpBB 2 -> phpbb3 conversion, I had to go back and remove all the bad tables that had been created. They were prefixed with phpbb3_ while the old tables were prefixed with phpbb_. Obviously I wanted to leave the old ones alone.
• Provide some useful reporting on any errors encountered and results of the batch drop.

So, here’s what I have for you today: a PHP CLI script to batch drop all your unwanted MySQL tables (after the fold).

read more …

I present to you a short script to automate backup and compression of a MySQL database using perl on a Linux system. I had to solve this problem today and figured there might be others out there in need of the same solution. Store the script in a safe location, hidden from the Internets, and chmod it to 700 to prevent other users from viewing the source (because it contains db login information). Customize the variables at the top of the script and test it out (perl sqlBackup.pl). If it runs without outputting any errors, add a line to your crontab file to run the backup at the desired interval.

There are of course better ways of doing this, namely this one. I am doing it my way because I’m more comfortable with perl and this script gives me more flexibility and a bit of debugging in case something goes wrong. This script assumes you’ve got cron setup to email you any output, so you should receive notification if something goes wrong.

read more …