Database Backup - How do I get one ?

[chasingame]

Hi,
  What is the best way for me to get a database backup (sql script file) for my coppermine database ?  Do you have a php utility that I can run?

I tried using mysqldump.exe and got a file.  The file contained quite a few "comments?".  These are lines like the following which would not execute when I ran them against an empty database:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

The error would be "empy sql statement" or something like that.

In any case what is the "best" way to get a sql script dump with my current data from the database that I can run against an empty database and end up with all tables, with data ?

Thanks,
Anthony

[Paver]

phpMyAdmin is the best free MySQL tool around: http://www.phpmyadmin.net.

There's an export function with lots of options to backup your database.  If your webhost doesn't already have phpMyAdmin installed, download it yourself, put it on your server, and configure it.  Very simple.

[Joachim Müller]

we also recommend mySqlDumper, especially for large databases, where phpMyAdmin might run into timeouts

[ortem]

Hello!

In using PHPMyAdmin, can any of the admins here give the correct export settings? Before I upgrade, I would like to know that I am creating a clean backup that I can acutally use if I have to. Thank you !

[Joachim Müller]

this actually depends on your database setup - you may need to enable complete inserts or not. Basically, you need to backup both structure and data, and you have to make sure not to drop your database during the backup.
See http://coppermine-gallery.net/demo/cpg14x/docs/faq.htm#BackupDatabase

[debragrant]

I've looked at the doc linked about for backing up database but I don't have the option 'save as' only:

Copy database to: 
Structure only 
Structure and data 
Data only 
CREATE DATABASE before copying
Add AUTO_INCREMENT value
Add constraints
Switch to copied database   

[Joachim Müller]

This is not a coppermine issue, but a phpMyAdmin issue. I suggest reading the phpMyAdmin docs. Maybe your webhost is providing you with a "crippled" version of phpMyAdmin. You may find it easier to use mySqlDumper instead of phpMyAdmin.

[debragrant]

i've looked at mySqlDumper but I'm unsure how to install/use as most of the doc's aren't in English