Scripting Backups of MySQL with Perl via mysqldump

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup does, and there may be issues with mysqldump locking tables during the backup process. So for critical data, you should consider MySQL Enterprise Backup.

There are too many options when using mysqldump to backup your data for me to discuss here, so this post will only provide you with a script to perform a basic mysqldump of your databases. For more information on mysqldump, see the mysqldump documentation page.

You run mysqldump from the command line. And while running a backup from the command line is easy, it is easier to have a script that will do this process for you at a regular interval. This script reads a list of the tables that you want to backup from a file, and then backs up each table separately into a named directory. I have been using this script on my home MySQL server for a couple of years now. Since my data isn’t critical, I have a cron job that runs once a day to backup my data.

You will still have to remember to clean out the directory where the backups are stored. For me, I use a simple Unix command to find any backup directories over seven days old (via the -ctime +7 option), and delete them. I run this command as a cron job and it runs once a week. Here is the command:

find /usr/local/backups/mysqlbak -depth 1 -ctime +7 -print -exec rm -rf {} \; 2>/dev/null >/usr/local/backups/mysqlbak/rm.txt

I use a text file named mysql_bak.config that contains a list of the database tables that I want to backup. I place this file in the same directory as the Perl script, but you may modify the Perl script to place this file anywhere on your system (as long as your user has permissions to access this file). Instead of using a list of databases from a file, you could use a mysql command “show databases” to find a list of your databases and incorporate that command into the Perl script. However, since my list of databases do not change often, I can use a static list.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| address            |
| cbgc               |
| twtr               |
+--------------------+

You may also tell the Perl script to ignore any database with a comment (#) before the database name. Using the list above, my backup list file looks like this:

information_schema
# address
cbgc
twtr

For this example, I don’t want to backup the address database. The rest of the databases will be backed up individually, and placed in a directory. The backup directory will be created by the Perl script with this naming convention: year-month-day-time, like this:

# pwd
/usr/local/backups/mysqlbak
# ls -lt
drwxr-xr-x 10 root staff 340 Aug 19 00:03 2011-08-19-0003
drwxr-xr-x 10 root staff 340 Aug 18 00:03 2011-08-18-0003
drwxr-xr-x 10 root staff 340 Aug 17 00:03 2011-08-17-0003
drwxr-xr-x 10 root staff 340 Aug 16 00:03 2011-08-16-0003

Inside each directory is a list of the databases that were backed up:

# pwd
/usr/local/backups/mysqlbak
# cd 2011-08-19-0003
# ls -l
total 318136
-rw-r--r-- 1 root staff 104043 Aug 19 00:03 cbgc.sql.Z
-rw-r--r-- 1 root staff 539 Aug 19 00:03 information_schema.sql.Z
-rw-r--r-- 1 root staff 159574979 Aug 19 00:04 twtr.sql.Z

And here is the Perl script. You will have to change parts of the script to match your system configuration.

#!/usr/bin/perl
# Perform a mysqldump on all the databases specified in the dbbackup.config file

use warnings;
use File::Basename;

# example config file
# You may use a comment to bypass any database that you don't want to backup
# # Unwanted_DB (commented - will not be backed up)
# twtr
# cbgc

# set the directory where you will keep the backup files
# make sure that the directory exists
$backup_folder = '/usr/local/backups/mysqlbak';

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);

$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min < 10;

# create the name of the backup folder that will contain all of the backup files
my $folder = "$year-$mon-$mday-$hour$min";
mkdir($folder) or die("Cannot create a folder called '$folder'");

# backup each database contained in the @databases array
foreach my $database (@databases) {
next if ($database eq '');
chomp($database);

my $table = '';
# Get just 1 table in the database - if there is a ' '(space) in the db name
if(index($database,' ')+1) {
my @parts = split(' ',$database);
$database = $parts[0];
$table = $parts[1];
}

# you may comment out this print statement if you don't want to see this information
print "Backing up $database ... ";

my $file = $database;
$file .= '_' . $table if($table ne '');
$file .= ".sql";

# perform a mysqldump on each database
# change the path of mysqldump to match your system's location
# make sure that you change the root password to match the correct password
`/usr/local/mysql/bin/mysqldump -R --triggers -u root --password=password $database $table | compress > $folder/$file.Z`;

# you may comment out this print statement if you don't want to see this information
print "Done\n";
}
# you may comment out this print statement if you don't want to see this information
print "Done\n\n";

exit;

# this subroutine simply creates an array of the list of the databases

sub getFileContents {
my $file = shift;
open (FILE,$file) || die("Can't open '$file': $!");
my @lines=<FILE>;
close(FILE);
return @lines;
}

# remove any commented tables from the @lines array

sub removeComments {
my @lines = @_;
@cleaned = grep(!/^\s*#/, @lines); #Remove Comments
@cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines
return @cleaned;
}

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Retrieving Data from MySQL with Perl, Print in HTML Table

In our last post about connecting to MySQL with Perl, we simply connected to the database and retrieved the MySQL version information. We have also looked at inserting data into MySQL via Perl.

In this post, we will connect to a MySQL database, retrieve multiple rows of data, and print a table in HTML with the data.

As in previous posts, we will continue to use an address table. Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(20) NOT NULL,
`address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.


insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');

This script isn’t much different than the one mentioned in the earlier post. The data from the MySQL database is returned as an array, and this time we will loop through the array and print the results.

For this example, we will retrieve all of the fields from the address table. Again, we are using a text file to store our database information ($database, $host, $userid and $passwd). See the earlier post for more information about this file. The text file should be named accessDB and should be located in the parent directory from the script. The file should contain:

my_database_name
192.168.1.1
mysql_user
password

Here is the Perl script:

- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl -w

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href="http://mysql.com/" />MySQL</a> database
use DBI;
use DBD::mysql;

# we use CGI since this will be executed in a browser
use CGI qw(:standard);

use warnings;

#----------------------------------------------------------------------
# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# close the accessDB file
close(ACCESS_INFO);
#----------------------------------------------------------------------

# print the header
print header;

# HTML for the beginning of the table
# we are putting a border around the table for effect
print "<table border=\"1\" width=\"800\"> \n";

# print your table column headers
print "<tr><td>First</td><td>Last</td><td>Address 1</td><td>Address 2</td><td>City</td><td>State</td><td>Postal Code</td></tr>\n";

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
while (@data = $statement->fetchrow_array()) {
$name_first = $data[0];
$name_last = $data[1];
$address_01 = $data[2];
$address_02 = $data[3];
$address_city = $data[4];
$address_state = $data[5];
$address_postal_code = $data[6];

# print your table rows
print "<tr><td>$name_first</td><td>$name_last</td><td>$address_01</td><td>$address_02</td><td>$address_city</td><td>$address_state</td><td>$address_postal_code</td></tr>\n";

}

# close your table
print "</table>\n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------
- - - - STOP SCRIPT - - - - (do not include this line in the script)

Here is what the script looks like when I run it from a browser:

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Retrieving Data from MySQL via PHP

So far, we have looked at connecting to a MySQL database via Perl and Python. Next, we will look at connecting via PHP. I am assuming that you have already installed PHP or have the ability to run PHP scripts on your server, and that you have MySQL installed as well.

This example will use the same address table from the previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(20) NOT NULL,
`address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

Be sure to populate your address table with some dummy data.


insert into `address` values('John','Smith','100 Main Street','Suite 500','Buffalo','NY','14201'),
 ('Dave','Jones','500 Second Avenue','Suite 100','Atlanta','GA','30303'),
 ('Tom','Watson','123 Golf Course Lane','Suite 1','Macon','GA','31066'),
 ('Jack','Nicklaus','400 Laurel Oak Dr','Suite 49','Suwanee','GA','31044');

Here is a very simple PHP script which will connect to MySQL and display the results of a query. We aren’t going to build any HTML tables to display the data – we are simply going to perform a query and return the results. Be sure to change the database values in the PHP script for the variables host_name_or_IP_Address, username, password and database_name. Insert the following script into your HTML body tag.


<?php

//connect to the database
mysql_connect ("host_name_or_IP_Address","username","password") or die ('Cannot connect to MySQL: ' . mysql_error());
mysql_select_db ("database_name") or die ('Cannot connect to the database: ' . mysql_error());

//query
$query = mysql_query("select name_first, name_last from address") or die ('Query is invalid: ' . mysql_error());

//write the results

while ($row = mysql_fetch_array($query)) {
	echo $row['name_first'] . " " . $row['name_last'] . "
"; // close the loop } ?>

Here are the results when executed in a browser:

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world's most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.

Inserting Data into MySQL with Perl

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
– use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. Here is the SQL statement that we used to create the table:



SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;


We are only going to insert one address line with our data. You can modify this script to loop the process and insert multiple rows of data.



- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;

use warnings;

$database = "scripting_mysql";

#----------------------------------------------------------------------
# insert the values into the database
#----------------------------------------------------------------------


# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code) 
			values (?, ?, ?, ?, ?, ?, ?) ";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute('John', 'Smith', '100 Main Street', 'Suite 500', 'Buffalo', 'NY', '14201');

#----------------------------------------------------------------------


#----------------------------------------------------------------------
# retrieve the values from the database
#----------------------------------------------------------------------

# set the value of your SQL query
$query2 = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address where name_last = 'Smith'";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query2);

# execute your SQL statement
$statement->execute();

# we will loop through the returned results that are in the @data array
# even though, for this example, we will only be returning one row of data

   while (@data = $statement->fetchrow_array()) {
      $name_first = $data[0];
      $name_last = $data[1];
      $address_01 = $data[2];
      $address_02 = $data[3];
      $address_city = $data[4];
      $address_state = $data[5];
      $address_postal_code = $data[6];

print "RESULTS - $name_first, $name_last, $address_01, $address_02, $address_city, $address_state, $address_postal_code\n";

}

#----------------------------------------------------------------------


# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessAdd") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# close the accessDB file
close(ACCESS_INFO);

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Follow

Get every new post delivered to your Inbox.

Join 39 other followers