Backup CiviCRM database?

This should work out of the box. You can either define a second db connection string in your settings.php (http://drupal.org/node/18429) or just add a new database source under the destinations tab of backup and migrate and it will be available to back up.

Let me know if you need more help with this.

I'm trying to use the version 6.x-2.2 to backup the CiviCRM database on two separate Drupal 6.15 sites. In both cases I''m getting multiple php errors in the database when backup_migrate runs. A couple of examples:

Referrer http://example.com/admin/content/backup_migrate/export/advanced
Message Table 'talldave_ccrm29.url_alias' doesn't exist query: SELECT dst FROM url_alias WHERE src = 'admin/content/backup_migrate/export/advanced' AND language IN('en', '') ORDER BY language DESC in /home/talldave/public_html/example/includes/path.inc on line 69.

Referrer http://example.com/admin/content/backup_migrate/export/advanced
Message Table 'talldave_ccrm29.accesslog' doesn't exist query: INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) values('Backup and Migrate', 'admin/content/backup_migrate/export/advanced', 'http://example.com/admin/content/backup_migrate/export/advanced', '206.180.154.174', 1, 'd2d2ab8789fbda11b1b78b6852605943', 3385, 1263511902) in /home/talldave/public_html/example/modules/statistics/statistics.module on line 64.

It seems that the module is trying to read/write tables that exist in the default (Drupal) database, but, do not exist in the CiviCRM database. On both sites, backup of the default Drupal database works fine, but, backup of the CiviCRM database results in multiple errors.

Suggestions?

sub

Confirming a problem. I got similar warnings, like this:

Warning: Table 'db001234_civicrm.system' doesn't exist query: SELECT * FROM system WHERE type = 'theme' in /home/users/username/mysite.com/includes/database.mysqli.inc on line 128

For over a dozen of the usual Drupal table names. And below those, part of the resulting page with warnings looks like the encoding is wrong, e.g.: "5^����^mU�)z��/�k�[˺{����ٯA�[U��&h�"

I have defined this second database (named "db001234_civicrm") at the Destinations tab in Backup & Migrate, not in settings.php.

P.S. Changing to bug report (unless we're configuring something wrong, this looks like a bug)
P.P.S. and i'm completely in agreement with whiteowl: must-have module, great new features in the 2.x release, thanks!

I've now experienced a problem with a 3^rd site not being able to backup the CiviCRM database even though the Drupal database backup works great. In addition, if the CiviCRM DB backup is enabled, CRON gets hung and manually clearing the cron_semaphore variable is required to get CRON to run again.

Is anyone being successful in getting backup_migrate to successfully backup CiviCRM databases?

^{[Note to self: TNW.org]}

This sounds bad.
I'm in the process of setting up my first CiviCRM project, and need to decide if I should create a separate DB or not.
Backup/migrate would be enough of an argument to use a shared database.

Hi there,

Sorry if this goes off topic but I could really use some help.

I notice from your post on backing up CiviCRM that you have two databases. One for Drupal and one for CiviCRM. How did you manage this? I have a similar setup in my test environment and it works alright. But I can't get Views working with CiviCRM. When creating a view, it complains that it can't find a table civicrm_contacts. That table exists in the CiviCRM database but not in the Drupal database (where I imagine its looking).

Did you ever get CiviCRM and Drupal Views to work together?

I'm on the latest CiviCRM 3.1 and Drupal 6.15.

Any help would be much appreciated.

Thanks!!

@ratinakage check inside your settings.php to make sure you've got information in there about the existence of all the tables in your Civicrm db.

I'm using using views with Civicrm 3.0, and it has its limitations... but very cool when it works.

Shai

Two more things/questions about this.

1. When exluding all Drupal tables from the backup (in my backup Profile for the CiviCRM database), I still get the "Warning: Table 'db001234_civicrm.system' doesn't exist query: SELECT * FROM system WHERE type = 'theme' in /home/users/username/mysite.com/includes/database.mysqli.inc on line 128" errors.
Should Backup_migrate lookup those Drupal tablenames at all, when they are exluded from the backup?

2. My Drupal database tables are MyISAM, the CiviCRM db is InnoDB. Could this be giving any of the trouble?

@ronan,
I've used your 6.x-1.x version many times to migrate Drupal (only) single-DB sites. Works great ... thanks for all your hard work.

I've recently upgraded to 6.x-2.x . Since the new site I am working on has CiviCRM, the multi-DB backup capability was needed. I setup a Destination for the separate CiviCRM DB and a Profile for it. However, I don't see how it's going to work properly. The include/exclude tables/data are not correct for the CiviCRM DB. I think this is because the DB is fixed as "Default Database". Maybe you HAVE TO define the CiviCRM DB in settings.php so that the option to set the database will appear in the Profile config?

@donquixote,
I'm no CiviCRM expert, but I think the widely accepted way to do it is to setup 2 different DBs (one for Drupal, one for CiviCRM). While not as elegant, your host should have some Control Panel tools to backup your DBs for emergencies.

@ratinakage,
I also run 2 separate mySQL DBs for Drupal 6.15 and CiviCRM 3.x . I have used Views in Drupal with success, but I haven't tried bringing Civi data into them yet. Since it sounds like you are, this might help:
http://redspire.net/blog/civicrm-views-2-integration-remote-civicrm-data...
Notice Step 1 is basically the same as the link ronan referenced to add the additional info to settings.php (to help Backup and Migrate to work properly).

Just a suggestion, but since v6.x-2.x works with multiple databases now... would this make more sense?
Destination (tab) = Server Directories
Source (tab) = Various Databases

@Tezla (#11):
Fair point, Drupal is not the only thing that can do backups.

(please be careful changing issue title and settings - this can make all the previous posts look like they were out of place)

After editing the settings.php file it seems to work fine for me (backup file downloads with no errors).

Changed in settings.php:

$db_url = 'mysqli://user1:password1@server/database1';
$db_prefix = '';

TO:

$db_url['default'] = 'mysqli://user1:password1@server/database1';
$db_url['civicrm'] = 'mysqli://user2:password2@server/database2';
$db_prefix = '';

Notes:
- The new Destination (for the new DB) gets created automatically. I only wish the name was shorter and not most of the DB connection string.
- You create a Profile. The Tables in the Database Options are incorrectly listed (it still says Source = Default Database), but I'm guessing they are ignored since there are no errors.
As far as I know, there aren't any Tables to exclude in the CiviCRM DB anyway.

I haven't tried a Restore, but the .mysql files look fine inside. Just wanted to let you know it was working for me.

@donquixote
@ronan
Sorry, I was trying to do a separate Feature Request.

I added the second $db_url to my settings.php and tried, then I still got the errors I described in #5.

But I think the problem is what Tezla notes above in #14: when you create a profile, the source is always the default and you can't specify the other database. So it seems a quick backup for a 2nd database always fails, because the source you select is ignored, since the profile you choose for quick backup has the default db defined as source.

But: Advanced Backup does allow you to specify the source, and then everything works, also without adding the 2nd $db_url to your settings.php!

Possibly related issue: #661728: Quick Backup not working- Fatal Error Message.

@ratinakage To get CiviCRM & Views 2 playing together, see http://redspire.net/content/civicrm-views-2-integration-remote-civicrm-d... and http://drupal.org/node/662860#comment-2391510

I also can't get this to work with a civicrm database. I've tried a variety of settings but always get the following error:
Cannot redeclare db_status_report() (previously declared in /home/mysite/htdocs/includes/database.mysqli.inc:23)

Ummm.

You could always merge the two databases. Obviously do it offline in a test environment using phpmyadmin to export the DB tables.

Once they're merged, B&M can be used easily...

Your idea about checking where the function was being re-declared pointed me in the right direction - thanks for that. I ran shell command grep -r "db_status_report" . and the only locations were pgsql.inc, mysqli.inc, and mysql.inc. Then I rechecked my settings.php file and found I'd managed to enter one database with mysqli and the other with mysql. Set them both to mysql and it works.

I've applied the patch from #18 which is definitely the fix for the fatal error while trying to backup non-drupal databases.

The remaining issue is that there is no ability to select non-drupal tables to exclude because settings profiles are not currently specific to any given database. This is a pretty big problem to solve (with some pretty big UI implications) so I've moved it to it's own ticket #793240: Unable to exclude tables from non drupal db

Hopefully this will solve the most pressing issue for Civicrm DB backups.

Thanks so much for this fix! It would be nice to roll out a 2.3 version since there may be many frustrated people who haven't found this thread. Or if there is a reason not to do that... maybe a note on the project page letting people facing this problem that they should be using the -dev version.

Thanks for the great module.

Shai Gluskin

#22 fixed it for me - I also found http://andrewbrookins.com/redspire/civicrm-views-2-integration-remote-ci... useful for connecting to a seperate CiviCrm database.

The 2nd method in Post #16 solved same problem between Drupal v6.22 and CiviCRM v3.4.5.

Thanks!

Although originally banging my head for half day with includes/database.mysql.inc line 35 error it all worked fine in the end. My issue was declaring

$db_url['default'] = 'mysqli://dev:passw0rd@localhost/drupaldb';
$db_url['civicrm']= 'mysql://dev:passw0rd@localhost/cividb';

I'd put mysqli in the first one and mysql in the second - changed them both to mysqli and zoom. Proabably would have worked with both as mysql just so long as they're the same.

Hi all, clear steps to backup CiviCRM database (if separate from CMS database) using B&M and without having to mess with settings.php.
Drupal 6.29, CiviCRM 4.4.1:

In Backup and Migrate create a new "destination" with the info for your CiviCRM database here: /admin/content/backup_migrate/destination.

Create a new profile for CiviCRM backups. I give the filename the prefix CiviCRM so I can tell it's different from the default backup.

Now in B&M you can select the CiviCRM datbase and the CiviCRM profile and there you go!

Hope this helps.

I believe the syntax in Drupal 7 for adding a second database is slightly different:

$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'xxx',
'username' => 'xxx',
'password' => 'xxx',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
'secondary_db' =>
array (
'civicrm' =>
array (
'database' => 'xxx',
'username' => 'xxx',
'password' => 'xxx',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
);