The only problem I face with this set up, though, is that I have multiple production servers out there. So this only works if this backup server could be a slave for multiple machines.

This is not possible, though, because, of course, no slave can serve two masters. Fortunately, a server can have multiple instances of MySQL running on it! So, in a sense, we have a server with multiple MySQL instances, to which a master can replicate. More about that set up in an upcoming post.

A how to on this blog, shows how this can be done. I’ll replicate the process below.

STEPS TO MULTIPLE MYSQL MADNESS

On the slave server

Step 1. Install MySQL

We’ll be working with CentOS 5.8, but this could really apply for any OS. First we’ll need to install MySQL like normal.

yum install mysql mysql-server

There are plenty of good tutorials out there on how to install the specific version of MySQL you want on the specific OS you’re running.

Step 2. Set up the data area.

You’ll need to have a different folder for each of the MySQL instances, say /dbases/master-a/, /dbases/master-b/, and /dbases/master-c/.

mkdir -p /dbases/{master-a,master-b,master-c}

Step 3. Copy the default my.cnf file

This is the default MySQL config file, it may be named differently on other OSes.

cp /etc/my.cnf /etc/master-a.cnf; cp /etc/my.cnf /etc/master-b.cnf; cp /etc/my.cnf /etc/master-c.cnf

Step 4. Edit the new MySQL config files.

For each new config file, you’ll need to specify some unique variables.

[mysqld]
port=3307
datadir=/dbases/master-a
socket=/dbases/master-a/mysql.sock
user=mysql
server_id=3307
log-bin=/dbases/master/mysql-bin.log

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links=0

[mysqld_safe]
log-error=/dbases/master-a/mysqld.log
pid-file=/dbases/master-a/mysqld.pid

The port option sets this MySQL instance on a different port than the default 3306. The datadir, socket, log-bin, log-error, and pid-file options make sure the necessary files are not using the default files.

Step 5. Create new init scripts.

The init script allows the server to start and stop the service at boot time, and allows for easy start up and shutdown (on CentOS/RedHat, at least – with an easy service mysqld start).

cp /etc/init.d/mysqld /etc/init.d/mysqld-master-a

Just do one for now. We’ll copy the new one to create the others, then just do a quick search and replace in those files to change the master-a to master-b and master-c.

Step 6. Edit the init script

#!/bin/bash
#
# mysqld        This shell script takes care of starting and stopping
#               the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description:  MySQL database server.
# processname: mysqld
# config: /etc/master-a.cnf
# pidfile: /dbases/master-a/mysqld.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

prog="MySQL"

# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
        result=/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1
        if [ -z "$result" ]; then
            # not found, use default
            result="$3"
        fi
}

get_mysql_option mysqld datadir "/dbases/master-a"
datadir="/dbases/master-a"
get_mysql_option mysqld socket "/dbases/master-a/mysql.sock"
socketfile="/dbases/master-a/mysql.sock"
get_mysql_option mysqld_safe log-error "/dbases/master-a/mysqld.log"
errlogfile="/dbases/master-a/mysqld.log"
get_mysql_option mysqld_safe pid-file "/dbases/master-a/mysqld.pid"
mypidfile="/dbases/master-a/mysqld.pid"

defaultfile="/etc/master-a.cnf"

start(){
        touch "$errlogfile"
        chown mysql:mysql "$errlogfile"
        chmod 0640 "$errlogfile"
        [ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
        if [ ! -d "$datadir/mysql" ] ; then
            action $"Initializing MySQL database: " /usr/bin/mysql_install_db --datadir="$datadir" --user=mysql
            ret=$?
            chown -R mysql:mysql "$datadir"
            if [ $ret -ne 0 ] ; then
                return $ret
            fi
        fi
        chown mysql:mysql "$datadir"
        chmod 0755 "$datadir"
        # Pass all the options determined above, to ensure consistent behavior.
        # In many cases mysqld_safe would arrive at the same conclusions anyway
        # but we need to be sure.
        /usr/bin/mysqld_safe  --defaults-file="$defaultfile" --datadir="$datadir" --socket="$socketfile" \
                --log-error="$errlogfile" --pid-file="$mypidfile" \
                --user=mysql >/dev/null 2>&1 &
        ret=$?
        # Spin for a maximum of N seconds waiting for the server to come up.
        # Rather than assuming we know a valid username, accept an "access
        # denied" response as meaning the server is functioning.        
        if [ $ret -eq 0 ]; then
            STARTTIMEOUT=30
            while [ $STARTTIMEOUT -gt 0 ]; do
                RESPONSE=/usr/bin/mysqladmin --socket="$socketfile" --user=UNKNOWN_MYSQL_USER ping 2>&1 && break
                echo "$RESPONSE" | grep -q "Access denied for user" && break
                sleep 1
                let STARTTIMEOUT=${STARTTIMEOUT}-1
            done
            if [ $STARTTIMEOUT -eq 0 ]; then
                    echo "Timeout error occurred trying to start MySQL Daemon."
                    action $"Starting $prog: " /bin/false
                    ret=1
            else
                    action $"Starting $prog: " /bin/true
            fi
        else
            action $"Starting $prog: " /bin/false
        fi
        [ $ret -eq 0 ] && touch /dbases/master-a/mysqld
        return $ret
}

stop(){ 
        MYSQLPID=cat "$mypidfile"  2>/dev/null 
        if [ -n "$MYSQLPID" ]; then
            /bin/kill "$MYSQLPID" >/dev/null 2>&1
            ret=$?
            if [ $ret -eq 0 ]; then
                STOPTIMEOUT=60
                while [ $STOPTIMEOUT -gt 0 ]; do
                    /bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
                    sleep 1
                    let STOPTIMEOUT=${STOPTIMEOUT}-1
                done
                if [ $STOPTIMEOUT -eq 0 ]; then
                    echo "Timeout error occurred trying to stop MySQL Daemon."
                    ret=1
                    action $"Stopping $prog: " /bin/false
                else
                    rm -f /dbases/master-a/mysqld
                    rm -f "$socketfile"
                    action $"Stopping $prog: " /bin/true
                fi
            else
                action $"Stopping $prog: " /bin/false
            fi
        else
            ret=1
            action $"Stopping $prog: " /bin/false
        fi
        return $ret
}

restart(){
    stop
    start
}

condrestart(){
    [ -e /dbases/master-a/mysqld ] && restart || :
}

# See how we were called.
case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  status)
    status mysqld
    ;;
  restart)
    restart
    ;;
  condrestart)
    condrestart
    ;;
  *)
    echo $"Usage: $0 {start|stop|status|condrestart|restart}"
    exit 1
esac

exit $?

Step 7. Start each MySQL instance.

Now you can start each instance with the handy service command.

service mysqld-master-a start

Step 8. Connect to MySQL instances.

Now, to connect to each MySQL instance, you’ll need to specify the port and/or socket file.

mysql -P3307 --socket="/dbases/mysql-master-a/mysql.sock"

If you have a live production MySQL server, stopping it to make a backup is not really an option. Fortunately there are a few options. Before you decide on which option to choose, here is a list of things to keep in mind when choosing a backup solution (from the MySQL gurus at Percona):

WHAT TO LOOK FOR

http://www.mysqlperformanceblog.com/2009/03/03/10-things-you-need-to-know-about-backup-solutions-for-mysql/

1. Does the backup require shutting down MySQL? If not, what is the impact on the running server? Blocking, I/O load, cache pollution, etc?
2. What technique is used for the backup? Is it mysqldump or a custom product that does something similar? Is it a filesystem copy?
3. Does the backup system understand that you cannot back up InnoDB by simply copying its files?
4. Does the backup use FLUSH TABLES, LOCK TABLES, or FLUSH TABLES WITH READ LOCK? These all interrupt processing.
5. What other effects are there on MySQL? I’ve seen systems that do a RESET MASTER, which immediately breaks replication. Are there any FLUSH commands at all, like FLUSH LOGS?
6. How does the system guarantee that you can perform point-in-time recovery?
7. How does the system guarantee consistency with the binary log, InnoDB logs, and replication?
8. Can you use the system to set up new MySQL replication slaves? How?
9. Does the system verify that the backup is restorable, e.g. does it run InnoDB recovery before declaring success?
10. Does anyone stand behind it with support, and guarantee working, recoverable backups? How strong is the legal guarantee of this and how much insurance do they have?

BACKUP PROGRAMS

There are a few MySQL backup products out there as well. I have used the first two on this list.

• AutoMySQLBackup script (handy for making a rotating incremental backup of your MySQL databases).
• Percona XtraBackup (nice way to ensure InnoDB and MyISAM tables are backed up properly, also does it incrementally)
• Zmanda (seems to be similar to Percona’s set up)

There’s probably a gazillion more out there. Google’s your friend in finding things you need.

HOW TO DO IT

There are several options. You could use a script above, or create a slave of the database (basically an exact copy of the production MySQL server – all changes that occur in the master are sent to the slave), or some combination. I’ll use a combination. I’ll replicate the production server onto the backup server, then run the incremental backups from there. This first part will walk through the process of setting up MySQL replication.

To give proper credit, here are several other how to’s I found helpful.

• http://www.badllama.com/content/mysql-backups-using-lvm-snapshots
• http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
• http://www.softwareprojects.com/resources/programming/t-how-to-move-copy-a-live-mysql-database-and-what-1257.html
• http://www.clusterdb.com/mysql-cluster/get-mysql-replication-up-and-running-in-5-minutes/

On the master server

Step 1. Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.

[mysqld]
server_id=1
innodb_flush_log_at_trx_commit=1
log_bin=mysql-bin.log
sync_binlog=1

Step 2. Make a MySQL user for the slave to use.

GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'localhost' IDENTIFIED BY 'passwordhere';

Step 3. Open a terminal session and log in to a MySQL prompt. Type the following command and hit enter.

FLUSH TABLES WITH READ LOCK

Step 4. After the FLUSH TABLES command finishes, run the following command and press enter.

SHOW MASTER STATUS

Step 5.  Make a copy of the database files.

lvcreate -L10G -s -n mysql-backup /dev/mapper/dbases

mkdir -p /mnt/mysql-backup

mount -o nouuid /dev/mapper/mysql-backup /mnt/mysql-backup

rsync -avz -e "ssh -c blowfish" /mnt/mysql-backup [email protected]:/backup/location

rsync -avz -e "ssh -c blowfish" /dbases/mysql [email protected]:/backup/location

mysqldump -uuser -p --all-databases > mysql-backup.sql

scp mysql-backup.sql [email protected]:/backup/location

Step 6. Once the lvcreate command has finished, you can unlock the database.

UNLOCK TABLES

Step 7. If you haven’t already, copy the copy of the database files to the backup server.

On the slave server

Step 1. Edit the my.cnf file to include at least the following (if needed) lines. Note: you will have to restart MySQL for these changes to take affect.

[mysqld]
server_id=2

Step 2. Start MySQL and run the following commands in a mysql session to start the MySQL slave.

CHANGE MASTER TO
MASTER_HOST = "master.server.com",
MASTER_USER = "rep_user",
MASTER_PASSWORD = "passwordhere",
MASTER_LOG_FILE = "mysql-bin.log",
MASTER_LOG_POS = 2341234;

START SLAVE;

Long-winded Introduction

It took me a couple of days to figure this out due to lack of decent tutorials and not enough confidence in my Linux skills to build programs from source. I think I have the hang of it now, and write this up with the intent on providing another, or the only, tutorial on setting up CentOS 5 with multiple instances of PHP using one Apache install. That being said, there are a number of good tutorials out there, just none of them explicitly for CentOS and some leave out some details that n00bs like me get confused about.

PHP4 and PHP5 on SuSE 10.1 – This was by far the most helpful of the tutorials. Even though it was written for SuSE, it works almost straight across for CentOS.

There is also a great list of instructions in the comments on the php.net site under installing PHP for Apache 2.0 on Unix systems (see http://www.php.net/manual/en/install.unix.apache2.php#90478).

I found this one after I wrote up this tutorial at http://cuadradevelopment.com. It’s a bit different, but should work as well.

There are basically two different ways I could have done this. 1- run a single instance of Apache, and run one instance of PHP as a module, and other installs as CGI. 2- run several instances of Apache, each with it’s own instance of PHP as a module. I chose to do the first method for no particular reason. Dreamhost has a post about the good and bad with running PHP as CGI.

So basically, the steps are: 1. Set up Apache and have PHP install as a module. 2. Configure and make another instance of PHP to run as CGI. 3. Add a virtual host to Apache running under a different port to access the PHP as CGI.

Set up Apache with PHP module

So here’s what I did to get the basic Apache, PHP and MySQL working. This sets up the first PHP install to run as a module in Apache:

From a clean install of CentOS 5 (virtually no packages selected during initial install), I installed the following packages:

$ yum install gcc make subversion ImageMagick php php-cli php-common php-ldap php-mysql php-pdo php-pear php-devel bzip2-devel libxml2-devel mysql mysql-server mysql-devel mod_auth_mysql httpd httpd-manual

From there I needed to get PHP 5.2.x, so I did the following to get PHP, Apache, MySQL and PEAR all set up.

1. Step 1: GET PHP 5.2.x
   Check out instructions and packages here: http://blog.famillecollet.com/pages/Config-en

   $ wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-2.noarch.rpm
   $ wget http://rpms.famillecollet.com/el5.i386/remi-release-5-4.el5.remi.noarch.rpm
   $ rpm -Uvh remi-release-5.rpm epel-release-5.rpm
   $ yum—enablerepo=remi update php-pear php

   Copy the /etc/php.ini file from the /etc/php.ini.default:

   $ cp /etc/php.ini.default /etc/php.ini

   Change the following lines:

   • 1. upload_max_filesize = 20M #line 573
   • 2. mysql.default_socket =/path/to/mysql/mysql.sock #about line 736
   • 3. mysqli.default_socket =/path/to/mysql/mysql.sock #about line 771
2. Step 2:Edit /etc/httpd/conf/httpd.conf by changing the following lines
   • 1. Listen xxx.xxx.xxx.xxx:80 #line 134
   • 2. ServerAdmin [email protected] #line 251
   • 3. ServerName somesite.org #line 265
   • 4. DocumentRoot ”/path/to/htdocs” #line 282
   • 5. <Directory ”/path/to/htdocs”> #line 307
   • 6. AllowOverride All #line 328
   • 7. DirectoryIndex index.php index.html index.html.var #line 392
3. Step 3: Create the /etc/my.cnf file for MySQL
   [code]

   [mysqld]
   datadir=/path/to/mysql
   socket=/path/to/mysql/mysql.sock
   user=mysql
   # Default to using old password format for compatibility with mysql 3.x
   # clients (those using the mysqlclient10 compatibility package).
   old_passwords=1
   
   [client]
   socket=/path/to/mysql/mysql.sock
   
   [mysqld_safe]
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid

   [/code]

4. Step 4: Start apache and mysql and set them to start on boot up:
   

   $ service httpd start
   $ service mysqld start
   $ chkconfig mysqld on
   $ chkconfig httpd on

5. Step 5: Set the MySQL password for root
   

   $ mysqladmin -u root password ‘XXXXXX’

6. Step 6: install Phing and other PEAR packages
   

   $ pear channel-discover pear.phing.info
   $ pear channel-discover pear.phpunit.de
   $ pear install phing/phing
   $ pear install PhpDocumentor
   $ pear install XML_Beautifier
   $ pear install PHPUnit
   $ pecl install Xdebug

Configure second version of PHP

From here we need to install a second version of PHP. Grab the version you want from http://www.php.net/releases/, and stick that any where you want to (usually your root’s home directory is fine). I’m installing PHP 5.2.4, so I’ll use that in my examples.

Unpack the tarball and enter the directory it created.

$ tar -xjf php-5.2.4.tar.bz2

Now, you’ll need to determine which modules you need to install. For this part I used the steps from the php.net comment under “my approach for determining required modules” (copied here, without permission, but with lots of gratitude and full credit going to the author of the comment).

my approach for determining required modules
------------------------------------
1. get the list of the modules
     $  php -m | grep -v -e Modules] -e ^$ > php-default-modules

2. create the configure script
$  for i in $(cat php-default-modules); do echo -n "--with-$i ">> phpconfigure.sh ;done

     2.2 add #!/bin/bash to the top line, and ./configure to the second line.
          Each of the --with-* need to be on the second line.

3. run the configure script, and iterate through the "Configure script errors"
    section below until it completes properly

    $ ./phpconfigure.sh

4. at the end of the output, look for a notice of unknown options

     Notice: Following unknown configure options were used:
     --with-date
     --with-gum-disease

     Check './configure --help' for available options

5. as suggested, execute '$ ./configure --help' and correct the options. The
     "for" command above indiscriminately inserts "--with-" for all modules,
     but bundled modules may require "--enable-" instead, so mostly you'll
     be changing those. For modules that are enabled by default you'll need
     to remove the entry.

6. Add anything else you personally want or need. I like to add "--enable-safe-mode".

After doing all of that, I had the following in phpconfigure.sh
[code lang=”bash”]
#!/bin/bash
./configure –prefix=/usr/share/ –datadir=/usr/share/php –libdir=/usr/share –includedir=/usr/include –bindir=/usr/bin –enable-safe-mode –with-config-file-path=/etc/php542 –enable-force-cgi-redirect –enable-discard-path –with-bz2 –enable-calendar –with-curl –enable-dbase –enable-exif –enable-ftp –with-gettext –with-gmp –with-iconv –with-ldap –with-libxml-dir=/usr/lib/ –enable-mbstring –with-mime_magic –with-mysql –with-mysqli –with-openssl –enable-pcntl –with-pcre-dir=/usr/lib/ –with-pdo_mysql –with-pdo_sqlite –with-readline –enable-shmop –enable-sockets –with-SQLite –enable-wddx –with-xsl –enable-zip –with-zlib

# Changes from what php -m spits out. You don’t need the info below in your phpconfigure.sh script
#–enable-calendar
#–with-ctype # default
#–with-date # not found, default?
#–enable-dbase
#–with-dom # default
#–enable-exif
#–with-filter #default
#–with-ftp
#–with-hash #default
#–with-json #default
#–with-libxml-dir=/usr/lib/
#–enable-mbstring
#–with-memcache #not found, default?
#–enable-pcntl
#–with-pcre-dir=/usr/lib/
#–with-PDO #taken care of with the pdo_mysql and pdo_sqlite
#–with-Reflection #default
#–with-session #default
#–enable-shmop
#–with-SimpleXML #default
#–enable-sockets
#–with-SPL #default
#–with-standard #not found, is it SPL? default?
#–with-tokenizer #default
#–enable-wddx
#–with-xdebug #not found, not needed
#–with-xml #default
#–with-xmlreader #default
#–with-xmlwriter #default
#–enable-zip
#–with-Xdebug #not found, not needed

[/code]

NOTE: make sure you do not include ‘–with-apxs2=/usr/sbin/apxs’. This is what installs PHP as an Apache module. Also, since you have the original PHP running, you can theoretically make a phpinfo file (with phpinfo() ) in it, and grab the configure entries from that, making sure to change ‘–with-config-file-path=/etc’ ‘–with-config-file-scan-dir=/etc/php.d’

During the configure, you might run into some errors. Again from the php.net comment:

Configure script errors
--------------------------------------------
In my experience, these errors have been due (with any software, PHP included) mostly to missing
development packages, which contain the libraries and headers needed to compile support for that
library's function into the application.

This becomes a process of:
-executing the ./configure script and looking at the error
-installing the devel package providing the resource referenced by the error (google using the error
     as search term as needed)
-repeat until the ./configure script makes it through without error

Upshot: identify the software referenced by the error, and install it.

Example
-----------
Example error:
     configure: error: Cannot find OpenSSL's
Example explanation
     configure is looking for a header (and probably a lot of other stuff) from a missing openssl package.
Example solution:
php-5.2.9]$sudo yum install openssl-devel

The previous yum command should take care of most of those dependencies.

After the phpconfigure script runs without errors, then simply run

$ make

As the JpGraph tutorial explains, there is no need to run “make install”. Just simply copy the php-cgi executable to the proper place. We’ll get to that step shortly.

Set up Apache VirtualHost and website directories

Now you need to create two directories to handle the PHP as CGI. They can be virtually wherever, but should be in the same directory where you have the main html content. So if you set the path to the website data (in the httpd.conf) to /path/to/htdocs/, then you’ll need to make a /path/to/php524/ and a /path/to/php524-cgi/

$ mkdir /path/to/php524/

and

$ mkdir /path/to/php524-cgi/

After you have those directories, you can add the VirtualHost information to the Apache config (httpd.conf). I like to have a separate file for the VirtualHosts, so I added this to the end of the httpd.conf file.

Include conf/XXXXX_vhosts.conf

And to allow VirtualHosts, uncomment this line:

NameVirtualHost *:80

To allow Apache to listen on (or accept requests from) different ports besides the default 80, add another Listen line to the httpd.conf file:

Listen XXX.XXX.XXX.XXX:8524

I used port 8524 to correspond to version 5.2.4 of PHP

Now create the XXXXX_vhosts.conf file

[code lang=”bash”]
#this doesn’t really seem to be needed, but it’s there
NameVirtualHost *:8524

# this is the original and runs the PHP as a module

DocumentRoot /path/to/htdocs/
ServerName somesite.org

####### Add other Virtual Hosts below here #######

# Setup PHP 5.2.4 on port 8524

DocumentRoot /path/to/php524/
# We use a separate CGI directory
ScriptAlias /cgi-bin/ /path/to/php524-cgi/

# These are the two critical statements for this virtual
# host. This activates PHP 5.2.4 as a CGI module
Action php524-cgi /cgi-bin/php-cgi
AddHandler php524-cgi .php5 .php

#Options None
Options FollowSymLinks
#AllowOverride None
AllowOverride All
Order allow,deny
Allow from all
# For good measure we also add recognition of PHP5 index
DirectoryIndex index.html index.php index.php5

[/code]

Now, you need to copy the php-cgi binary/executable to the /path/to/php524-cgi/ directory. The php-cgi file is located in the file where you ran the configure and make for the new php install. So if you did all that in the /opt/php-5.2.4/ directory, the php-cgi will be located at /opt/php-5.2.4/sapi/cgi/php-cgi.

$ cp /opt/php-5.2.4/sapi/cgi/php-cgi /path/to/php524-cgi/

Finally, copy the php.ini file to the right place. And configure as needed.

$ cp /opt/php-5.2.4/php.ini-dist /path/to/php524-cgi/php.ini

Test the apache configs to make sure they work:

$ /usr/sbin/apachectl configtest

If that returns OK restart Apache.

$ /etc/init.d/httpd graceful

You can make a phpinfo page to test that it’s using the new PHP version.
[code lang=”php”]
< ?php
phpinfo();
? >
[/code]
Then check out your new site: http://somesite.org:8524/phpinfo.php

In order to get the different versions of PHP to interact with MySQL, you’ll have to use the URL on port 80 as the MySQL host. So, for example, in a WordPress install at http://somesite.org:8524/blog, the wp-config.php will have to have the following for the MySQL hostname:

define('DB_HOST', 'dev.omeka.org');

There is some issue with mod_rewrite on the different versions of PHP. I’ll replace this paragraph with a fix when I have one.
UPDATE: 9/9/09 – I figured out how to get the .htaccess working for the Omeka installs we were working with. I needed to change the AllowOverride lines in the vhost.conf (or httpd.conf) file from None, to All.

Well, there you go. Hope that’s enough detail to get you going.

It has an array of database table names (without the common prefix) in the script. Then it prompts for the mysql user, database, and prefix. It could be changed to prompt for a file that contains a list or array of table names.

Anyhow, here it is for anyone’s use:

[code lang=”Bash”]
#!/bin/bash

#—————————————–#
# Ammon Shepherd #
# 09.05.07 #
# Dump a database with only the tables #
# containing the prefix given. #
#—————————————–#

echo “This will dump just the tables with the specified prefix from the specified database.”

echo -n “Enter the database name: ”
read dbase

echo -n “Enter the table prefix: ”
read prefix

echo -n “The mysql user: ”
read sqluser
echo -n “The mysql pass: ”
read -s sqlpass

# Get list of tables with the desired prefix
list=( $(mysql -u$sqluser -p$sqlpass $dbase –raw –silent –silent –execute=”SHOW TABLES;”) )

for tablename in ${list[@]}
do
if [[ “$tablename” =~ $prefix ]]; then
tablelist+=”$tablename ”
fi
done

`mysqldump -u$sqluser -p$sqlpass –opt $dbase $tablelist > $dbase.$prefix.bak.sql`

echo

exit 0

[/code]