Using awk, grep, sed, and mysqldump to script and backup your MySQL databases on the command line
In this tutorial I’ll show how you can use awk, grep, and sed (my favorite command line tools) to backup and archive your MySQL databases. This can be useful to schedule a cron job, transfer your databases to another server, or any other type of scripting.
First you’ll have to get acquainted with connecting to and dumping your database on the command line. Depending on your user, credentials, and where the databases are located, your command might look something like this. Please note, there is no space between the password and the “-p” flag.
To simplify my example I’m going to shorten the mysqldump command to the follow.
Now that we’re MySQL command line pros, I’ll break down each command. I’ll start by showing all the databases.
Now I’ll “pipe” the output from the previous command into awk to show the first column data.
And use grep to remove the first line that says “Database”.
And use sed to build the mysqldump command. This one is kinda tricky, sorry. As you can see, I also embedded the date command in there to generate today’s date in the format: YYYYMMDD.
Last if everything looks good, you can pipe the output back to the command line.
You could even take this one step further and pipe the output through gzip to compress the dumps :)