Mail::Toaster::Mysql - so much more than just installing mysql

Functions for installing, starting, stopping, querying, and otherwise interacting with MySQL.

I find myself using MySQL for a lot of things. Geographically distributed dns systems (MySQL replication), mail servers, and all the other fun stuff you'd use a RDBMS for. As such, I've got a growing pile of scripts that have lots of duplicated code in them. As such, the need for this perl module grew.

       Currently used in:
  mysql_replicate_manager v1.5+
  uron.net user_*.pl
  polls.pl
  nt_export_djb_update.pl
  toaster_setup.pl 

new

	use Mail::Toaster::Mysql;
	my $mysql = Mail::Toaster::Mysql->new();

autocommit

backup

Back up your mysql databases

   $mysql->backup( $dot );

The default location for backups is /var/backups/mysql. If you want them stored elsewhere, set backupdir = /path/to/backups in your .my.cnf (as shown in the FAQ) or pass it via -d on the command line.

You will need to have cronolog, gzip, and mysqldump installed in a "normal" location. Your backups will be stored in a directory based on the date, such as /var/backups/mysql/2003/09/11/mysql_full_dump.gz. Make sure that path is configured to be backed up by your backup software.

 arguments required:
    dot - a hashref of values from a .my.cnf file

connect

    my ($dbh, $dsn, $drh) = $mysql->connect($dot, $warn, $debug);

$dot is a hashref of key/value pairs in the same format you'd find in ~/.my.cnf. Not coincidentally, that's where it expects you'll be getting them from.

$warn allows you to determine whether to die or warn on failure or error. To warn, set $warn to a non-zero value.

$debug will print out helpful debugging messages should you be having problems.

db_vars

This sub is called internally by $mysql->connect and is used principally to set some reasonable defaults should you not pass along enough connection parameters in $dot.

flush_logs

	$mysql->flush_logs($dbh, $debug)

runs the mysql "FLUSH LOGS" query on the server. This commits any pending (memory cached writes) to disk.

get_hashes

Gets results from a mysql query as an array of hashes

   my @r = $mysql->get_hashes($dbh, $sql);

$dbh is a database handle

$sql is query

install

Installs MySQL

is_newer

	my $ver   = $mysql->version($dbh);
	my $newer = $mysql->is_newer("4.1.0", $ver);

if ($newer) { print "you are brave!" };

As you can see, is_newer can be very useful, especially when you need to execute queries with syntax differences between versions of Mysql.

parse_dot_file

 $mysql->parse_dot_file ($file, $start, $debug)

Example:

 my $dot = $mysql->parse_dot_file(".my.cnf", "[mysql_replicate_manager]", 0);

 $file is the file to be parsed.

$start is the [identifier] where we begin looking for settings. This expects the format used in .my.cnf MySQL configuration files.

A hashref is returned wih key value pairs

phpmyadmin_install

Install PhpMyAdmin from FreeBSD ports.

	$mysql->phpmyadmin_install($conf);

$conf is a hash of configuration values. See toaster-watcher.conf for configuring the optional values to pass along.

query

    my $sth = $mysql->query ($dbh, $query, $warn)

$dbh is the database handle you've already acquired via $mysql->connect.

$query is the SQL statement to execute.

If $warn is set, we don't die if the query fails. This way you can decide when you call the sub whether you want it to die or return a failed $sth (and likely an error message).

 execute performs whats necessary to execute a statement
 Always returns true regardless of # of rows affected.
 For non-Select, returns # of rows affected: No rows = 0E0
 For Select, simply starts query. Follow with fetch_*

query_confirm

	$mysql->query_confirm($dbh, $query, $debug);

Use this if you want to interactively get user confirmation before executing a query.

sanity

A place to do validation tests on values to make sure they are reasonable

Currently we only check to assure the password is less than 32 characters and the username is less than 16. More tests will come.

shutdown_mysqld

Shuts down mysql using a $drh handle.

   my $rc = $mysql->shutdown_mysqld($dbvs, $drh);

$dbvs is a hashref containing: host, user, pass

returns error_code 200 on success, 500 on error. See error_desc for details.

tables_lock

	my $sth = $mysql->tables_lock($dbh, $debug);
	# do some mysql stuff
	$mysql->tables_unlock($dbh, $sth);

Takes a statement handle and does a global lock on all tables. Quite useful when you want do do things like make a tarball of the database directory, back up the server, etc.

tables_unlock

	$mysql->tables_unlock($dbh, $sth, $debug);

Takes a statement handle and does a global unlock on all tables. Quite useful after you've used $mysql->tables_lock, done your deeds and wish to release your lock.

status

version

	my $ver = $mysql->version($dbh);

Returns a string representing the version of MySQL running.

   DBI.pm     - /usr/ports/databases/p5-DBI
   DBD::mysql - /usr/ports/databases/p5-DBD-mysql

In order to use this module, you must have DBI.pm and DBD::Mysql installed. If they are not installed and you attempt to use this module, you should get some helpful error messages telling you how to install them.

Matt Simerson <matt@tnpi.net>

None known. Report any to author.

The following are all man/perldoc pages:

 Mail::Toaster 
 Mail::Toaster::Conf
 toaster.conf
 toaster-watcher.conf

 http://www.mail-toaster.com/

Copyright (c) 2003-2008, The Network People, Inc. All Rights Reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

Neither the name of the The Network People, Inc. nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.