New to the community? Start here

how to backup multiple databases?

Hello

I am after installation multi-db-0.6.2 plugin (It’s great! Congratulation :slight_smile: )

Now I would make backup my databases, but which plugin will make me all databases backup automaticly? (for example one per week or month…:wink:

Tue, 22 Apr 2008 10:49:28
  • trent
    • Site Builder, Child of Zeus

    You created create a bash script that runs through your cron then drmike? I am sure there is one out there to “modify”, but I just have my snapshots backups taking full snapshots of my server and am able to put the entire backup onto my testbed if needed to pull out the necessary data. Not a “fast” solution, but only one I have thus far other than complete data dumps :slight_smile:

    Trent

  • Andrew
    • Champion of Loops

    Wow, you really dug up an old thread here!

    I believe we’re just running one mysqldump run per server at the moment. However, I’d have to check with our sysadmin to verify that.

    Do I really have to run a mysqldump command for each db?

    You can just run mysqldump once per server. I believe the command is:

    mysqldump –all-databases

    That’s just off the top of my head though so definitely verify it.

    And what about locking or db changes between each mysqldump command?

    It hasn’t really been a problem for us. If you have 500K blogs and are using 4096 databases then you should only have 1-2K tables per db depending on plugin tables. Of course with that many databases and tables it’s impossible to get a complete snapshot of all data. There will always be changes made in the time the backup is running. Frequent backups cover you there though.

    There are ways to get a complete snapshot but it’s really not worth the extra hassle unless you’re dealing with a wp.com size install.

    Thanks,

    Andrew

  • drmike
    • DEV MAN’s Mascot

    We cheat. The databases sit in a separate hosting account away from the wpmu install. If you;re on a CPanel or Direct Admin box and if you know the mysql username, passwords, and the table names, you can access any mysql database on a server. Some folks (including myself) see that as a security concern but CPanel’s response was that if they know all the info, it should be allowed.

    To do a backup, we just do a complete account backup. Since it;s just the databases, that’s all you get. Along with the normal CPanel files.

    For the webroot, we just point index.html and the error page to the home page of the wpmu install.

    Makes doing site backups easier as well since the databases aren’t included. Smaller backup.

    We’ve been thinking about doing that for uploads as well since you can repoint the blogs.dir directory now. Haven’t had a change to look into it yet.

    Tried explaining this once on the mu forums but 1) was in a hurry and 2) got a ‘huh?’ but so I let it drop.

    Just to throw this in, we do automated separate account backups on even days and server *.iso backups on odd days and save both on a separate server on a different rack. (I think those are right.) Clients are allowed to do backups as well.

  • e.yatsik
    • WPMU DEV Initiate

    I writed simple script wich make backup multi-DB WPMU databases,

    created with Multi-DB plugin.

    Usage:

    1. Copy this file to wp-content/scripts directory

    2. Create directory wp-content/scripts/backup with 777 permission

    3. Point your browser to http://yoursite/wp-content/scripts/multi-db-backup.php

    4. After execution database backup will be located in wp-content/scripts/backup directory.

    I suppose it will be useful.

    <?php
    
//  WordPress Multi-DB Backup
    
//	Author: Evgeny Yatsik ([email protected])
    
//	Version: 0.1
    
//
    
//	Backup multi-DB WPMU databases,
    
//  created with Multi-DB plugin version 2.7.5 by Andrew Billits
    
//
    
//  Usage:
    
//  1. Copy this file to wp-content/scripts directory
    
//  2. Create directory wp-content/scripts/backup with 777 permission
    
//  3. Point your browser to http://your_site/wp-content/scripts/multi-db-backup.php
    

    
/*
    
Copyright 2009 Evgeniy Yatsik
    

    
This program is free software; you can redistribute it and/or modify
    
it under the terms of the GNU General Public License (Version 2 - GPLv2) as published by
    
the Free Software Foundation.
    

    
This program is distributed in the hope that it will be useful,
    
but WITHOUT ANY WARRANTY; without even the implied warranty of
    
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    
GNU General Public License for more details.
    

    
You should have received a copy of the GNU General Public License
    
along with this program; if not, write to the Free Software
    
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
    
*/
    

    
$backup_dir = 'backup'; // Location of directory where the backup files will be placed
    

    
$db_servers = array();
    
require('../db-config.php');
    

    
foreach ($db_servers as $db_server) {
    
$timestamp = date('YmdHis');
    
$sql_file = "$backup_dir/{$db_server}-$timestamp.sql";
    
$tgz_file = "$backup_dir/{$db_server}-$timestamp.tgz";;
    
exec("/usr/bin/mysqldump --user={$db_server} --password={$db_server} --host={$db_server} {$db_server} > $sql_file");
    
exec("tar cvzf $tgz_file $sql_file");
    
exec("unlink $sql_file");
    
echo "Database {$db_server} backup file: <a href="$tgz_file">$tgz_file</a><br />";
    
}
    
echo '<br />Done.';
    

    
// add_db_server(DS, DC, READ, WRITE, HOST, LAN_HOST, NAME, USER, PASS)
    
// ex. add_db_server('vip1', 'dc1', 1, 1,'localhost','localhost', 'etob_vip1', 'etob',  'quo482jKH~');
    
function add_db_server($ds, $dc, $read, $write, $host, $lhost, $name, $user, $password) {
    
global $db_servers;
    
$server = compact('ds', 'dc', 'read', 'write', 'host', 'name', 'user', 'password');
    
if ( !empty($lhost) ) $server = $lhost;
    
$db_servers[] = $server;
    
}
    
//	add_global_table(TABLE_NAME) dummy
    
function add_global_table($table_name) {
    
}
    
//	add_vip_blog(BLOG_ID, DS) dummy
    
function add_vip_blog($blog_id, $dataset) {
    
}
    
//	add_dc_ip(IP, DC) dummy
    
function add_dc_ip($ip, $dc) {
    
}
    
?>

  • drmike
    • DEV MAN’s Mascot

    We can do something similiar with Direct Admin. There’s a plugin that sends off backups to ftp sites and the like.

    I’m not big on automating baclups though when it comes to large databases in general. I’ve had a few elgg, gallery, and whatnot sites get their backups trashed because the hosting software gets confused.

    I seem to recall a few wp.com threads where they admitted that they couldn’t get to their own backups. Sounds like the same issue.

  • becky
    • The Incredible Code Injector

    I tried yatsik’s script and it works in creating the multi db backup. Probably a good idea to include a .htaccess file in the backup folder so that no one else could access the backup files while you are downloading them for archive.

  • nickd32
    • The Incredible Code Injector

    Any one had issues with yatsik’s script. We’ve set it up and it created a backup of the db’s but there is no content, they are all empty. Any ideas or recommendation on backing up multiDB’s?

    Having a similar issue now. I think it’s related to having the PHP “exec” function turned on/off. It needs to be on for that script to run.

    Here’s an FAQ I found about it — this relates to BackupBuddy, but the PHP exec stuff is helpful.

    http://ithemes.com/codex/page/BackupBuddy:_Frequent_Support_Issues#Compatibility_Mode