title: A mysqldump script link: https://onemoretech.wordpress.com/2013/09/23/a-mysqldump-script/ author: phil2nc description: post_id: 6392 created: 2013/09/23 11:59:18 created_gmt: 2013/09/23 15:59:18 comment_status: closed post_name: a-mysqldump-script status: publish post_type: post

A mysqldump script

This one was customized for backing up the data for a WordPress instance, but you'll get the idea. Keep in mind that the actual MySQL schema, and database-wide user accounts with their permissions, are kept in their own databases (information_schema, mysql). If you're running multiple MySQL databases with a long list of users that connect to them you may want to include those in the list you back up. If you want to backup all databases in one big file (not recommended), you would use the "- -all-databases" option for mysqldump rather than "- -database $dbName" below. [code language="perl"] #!/usr/bin/perl use strict; my $HOME = $ENV{'HOME'}; our($myAdmin,$myPass); require "/usr/local/etc/admin.conf"; my ($minute,$hour,$day,$month,$year) = (localtime)[1,2,3,4,5]; my $timestamp = sprintf("%04d%02d%02d%02d%02d", $year + 1900, $month + 1, $day, $hour, $minute); my $DBHOME = "/data/app/mysql/var"; my $dbName = "wordpress1"; my $dumpName = "$dbName.$timestamp.sql"; my $dumpPath = "/data/backup/mysql"; print "Backing up MySQL database\n"; system("/usr/bin/mysqldump --user=$myAdmin --password="$myPass" --database $dbName >$dumpPath/$dumpName"); system("chown devusr:devgroup $dumpPath/$dumpName"); system("chmod g+w $dumpPath/$dumpName"); system("gzip $dumpPath/$dumpName"); [/code] Briefly, when run as root this script will save a MySQL text dump of the named database (in the example, "wordpress1") in the designated directory, attach a timestamp, re-permission so they can be accessed by a non-root user (here "devusr" and any member of "devgroup"), and gzip it to save space. Note that the script uses a config file located at /usr/local/bin/admin.conf. This file would look something like:

# Config file
$myAdmin = "root";
$myPass = "rootpassword";

1;

The "1;" is required to let the script know it has reached the end of the config file.

Copyright 2004-2019 Phil Lembo