Note: This site might seem inactive… That’s because it is. Don’t worry though, I’m still coding webpages and stuff! If you’re interested, I suggest you get a translator and head over to Qiwi; or you could just check the latest site we’ve been working on: Amiloen. Enjoy!
Creating and restoring MySQL backups using the command line
As some of you, dear visitors, noticed and even pointed out to me (thanks again!), Math Jazz was even more of a total mess than it usually is this morning. Luckily, I had database backups, but because of the friggin’ phpMyAdmin that doesn’t quite seem to support UTF-8 those created another problem. To cut a long story short: this site looked well funky, and in order to fix it I needed a way to execute MySQL queries from a file without messing with its contents first. Eventually, I found a 1337 solution, which I’m now going to share with you. However, first things first.
Backing up MySQL data
Earlier this week, my WordPress dashboard pointed me to this simple MySQL backup script. I’m pretty new to BASH and working with the shell in general, but yet I managed to customize the script for a bit.
export d=$(date +'%Y-%m-%d')
mkdir -p backup/$d
for i in `echo "show tables" | mysql -uusername -ppassword database|grep -v Tables_in_`;
do
echo Backing up $i...
mysqldump --add-drop-table --allow-keywords -q -a -c -uusername -ppassword database $i > backup/$d/$i.sql
done
echo 0m6, d0n3! ph34r my 1337 b4kcUp sk1ll2!!1Store this file on your server as, say, sql-backup.sh. You can then execute it by typing bash sql-backup.sh in the shell.[1] The script will create a new folder, namely backup/ in the current working directory, and then save the backup files in subfolders based on the date. For example, if I’d run the script today, I would have my database backed up in the shiny new backup/2005-07-02/ folder. w00t!
Note: I’m currently using PuTTY for this kind of 1337 stuff, which seems to work fine — but then again I really don’t know of any alternatives.
Restoring MySQL data from backups
Now, let’s put these backups to use. As I mentioned in the preface of this article, I couldn’t simply import the .sql files using phpMyAdmin without messing things up. At first, I thought of writing a PHP script that would take a backup file, parse it contents and then execute the SQL stuff, but then I realised there just had to be an easier way. After Googlin’ around for a while, I stumbled upon mysql, the command-line tool. Especially the information on executing SQL statements from a text file proved itself useful whilst writing the following BASH script.
cd backup/2005-07-02/
for i in *;
do
echo Restoring $i...
mysql -uusername -ppassword database < $i
done
echo 0m6, d0n3! ph34r my 1337 r3570r3 sk1ll2!!1This would restore the SQL data from all files in the backup/2005-07-02/ directory into the specified database. You’ll obviously have to edit the first line to make it match the backup folder you want to use.
Note: In the above scripts, you of course have to replace every instance of username and password with your MySQL username and password respectively, whereas database should be replaced by the MySQL database name you’re working with.
Comments (9)
Listed below are the responses for this entry.
Trackbacks & Pingbacks (1)
Listed below are resources on the web that mention this article.
-
- Ian’s Blog: Creating and restoring MySQL backups using the command line by Math Jazz:
Creating and restoring MySQL backups using the command line by Math Jazz
[…] Back up WordPress MySQL database […]- Pingback made on July 2nd, 2005 @ 4:25 pm
Glad it’s all back to normal. I used the same technique when migrating webhosts.
I didn’t understand a single line of your code; but it reminds me that I had to backup my database, thanks ;)
Ma7h1a5, 17’5 n07 1337 70 k33p u51n6 7h3 w0rd .
Why make the directory with
777permissions, isn’t the default umask good enough?I just have this thing for
CHMOD 777. The default umask would be just as good, but really — all this doesn’t matter as you won’t place your backup files in a location that can be viewed from the web, right?And let’s keep correcting each other; phpMyAdmin does support UTF-8 ;-) At least for the latest version it’s on by default. I remember I had to wreck some configuration files for 2.5.4 (
/libraries/select_lang.lib.php) but I don’t know which version you have and/or if you’re able to edit the source files.Also, if you force your browser to use UTF-8, phpMyAdmin suddenly supports it as well.
Actually, it can be dangerous on shared hosts. A mode of
(0)777means that anyone on the server can read from, write to and execute the file or directory. More than a bit dangerous, I’d say.Thanks for the info, Krijn! Unfortunately, I can’t have my way in the source files, but perhaps I’ll install phpMyAdmin somewhere on mathibus.com rather than on my übersecret server, so I can have that privilege. :) Oh, and the browser tweak trick thing sounds even more cool!
You’re right — I can’t expect all three people who’ll ever use this script not to use shared host, as I do.
mkdir -m 777 -p backup/$dmkdir -p backup/$d.It’s very good and helped me in taking backup of my db.
Thanks a lot