Converting MySQL backup script to work with multiple dbs

PHP
1

Hi there everyone!

I would like to use this script to backup my site’s db’s so my automated file backup solution will download them along with the files.

So the magic bits are:

DB definitions/variables:

define("DB_USER", 'your_username');
define("DB_PASSWORD", 'your_password');
define("DB_NAME", 'your_db_name');

Making it happen:

$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, CHARSET);
$result = $backupDatabase->backupTables(TABLES, BACKUP_DIR) ? 'OK' : 'KO';
$backupDatabase->obfPrint('Backup result: ' . $result, 1);

// Use $output variable for further processing, for example to send it by email
$output = $backupDatabase->getOutput();

My thinking is that I need to make the db credentials an array and I need to convert the last bit to while/foreach through it. The issue I have is that sometimes, there will be multiple DB names attached to the same user, so I need to set that up as a subarray of some sort.

{
    User: bob
    pass: password
    db names: cats, dogs, dolphins
}
{
    User: john
    pass: 11111
    db names: bikes, cars, boats
}
{
    User: jane
    pass: 123456
    db names: pizza
}

But I’m having problems figuring out how to convert this script to work through all available databases. Any help on doing this would be greatly appreciated.

Thanks for your time!

2

I believe you can query MySQL to give you a list of the tables inside each database, and presumably each database. Not sure how, exactly, but I’m sure I’ve seen it.

If you’re happy to hard-code it, inside your foreach for the database, you could just check if the tables entry is an array, if it is, then a nested foreach through the tables, if not, then just use the single table name. Or to make it easier, make the tables list always a sub-array, then just foreach through one or more entries.

3 
SHOW TABLES

(optionally IN dbname)

4

I knew it’d be something complex and obscure like that. :slight_smile:

1 Like
5

Hi there guys, and thanks for the help!

The script I linked actually takes care of all that. I just need to convert the script from backing up one database to backing up multiple databases accessible by multiple users.

I am sure that I need to convert the database connection elements into an array then loop through them. I’m just having problems making that happen.

6

You can put your credentials into an associative array structure like so:

$backups = [
    [
        'user' => 'bob',
        'pass' => 'password',
        'db_names' => ['cats', 'dogs', 'dolphins']
    ],
    [
        'user' => 'john',
        'pass' => '11111',
        'db_names' => ['bikes', 'cars', 'boats']
    ],
    [
        'user' => 'jane',
        'pass' => '123456',
        'db_names' => ['pizza']
    ]
];

Now you can itterate through each top level entry and each nested db_name and use these values instead of the defined constants to pass to the Backup_Database constructor:

foreach ($backups as $backup) {
    $user = $backup['user'];
    $pass = $backup['pass'];
    $dbNames = $backup['db_names'];
    foreach ($dbNames as $dbName) {
        $backupDatabase = new Backup_Database(DB_HOST, $user, $pass, $dbName, CHARSET);
        $result = $backupDatabase->backupTables(TABLES, BACKUP_DIR) ? 'OK' : 'KO';
        $backupDatabase->obfPrint('Backup result: ' . $result, 1);
        // Use $output variable for further processing, for example to send it by email
        $output = $backupDatabase->getOutput();
    }
}
1 Like
7

That just works fantastically, thanks so much for the help!

8

That app sure is a lot of code to do what the database can already do natively in very little code. You should really be using the DB to do backups by running a shell script with CRON. Your backups will happen automatically without you having to remember to do it. The DB can back itself up a lot better than PHP can do it.

Here is a basic shell script to do a DB backup.

mysql_backup.sh

#!/bin/bash
mysqldump --user=YOURUSERNAME --password=YOURPASSWORD --host=YOURSERVER DBNAME > /path/to/save/backups/mysql_backup.sql
1 Like
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.