phpMyAdmin Backup Settings

[kobra]

Thanks JFD, but this is copied from the wiki and still leaves me without what settings or specific edits that are necessary??? and I guess this means no gzip if one is to edit it.

Quote the wiki on phpMyAdmin:

It is very flexible, though the default settings will force the copy when imported to create another db named the same as the original. (It is possible to get around this if desired, e.g. making two copies locally, by editing the .sql file to remove the CREATE statement and change the ?SET and USE statements to your desired copy name.) [Someone please describe what settings will be good for various circumstances.]

[DrByte]

What is the exact error message text you're getting?

[kobra]

I have tried several ways to accomplish this.

Moving a DB from a live store with a DB name like DB1 to another/second install to DB2

This is on a host with a limited phpmyadmin panel. There is an 'export' but no 'import' tab. In its place is the sql entry box

Error
-- Host: 10.0.11.29
-- Generation Time: May 30, 2006 at 09:57 PM
-- Server version: 4.0.27
-- PHP Version: 4.3.2
--
-- Database: `DBNAME`
--
CREATE DATABASE `DBNAME` ;

MySQL said:

1007: Can't create database 'databaseName'. Database exists

or at other times

MySQL said:

1049: Unknown database 'databaseName'

So I am thinking that the created backup is trying to 'create' theDB again and can not. And in the wiki it was referenced but left the settings up in the air.

I understand that there are many variations of what is available per host & or interface.

I think I have to either edit the backup to not create the DB but only write the information to one that exists and is empty. Or use a different mysql statement that will accomplish the same.

This is where I can not determine what is required.

I hope that this is explained by me in a manner that can be followed.

[gjh42]

When I did that SQL edit I was stabbing blindly. It worked, but I didn't want to tell people "the" way when I didn't know the proper way myself. I would appreciate knowledgeable input. (I looked at the structure of statements and removed what looked like a complete sequence including the CREATE. The SET and USE edits were straightforward.)

Also, I have since done a backup that did update cleanly, and I'm not sure what if any differences there were in settings between the cases.

I hoped someone expert in MySQL would put more info in the wiki article, but so far nobody has.

PS - I did download the db as a gzip. My edits were to the file after I unzipped it.

[DrByte]

The ideal settings to do a backup via phpMyAdmin are shown below.

NOTE: BEFORE clicking on "Export", you should select your database from the dropdown, so that its tables are displayed. THEN click on the "Export" tab.

- CHECKED: Structure
- CHECKED: Add DROP TABLE
- CHECKED: Add AUTO_INCREMENT value
- CHECKED: Enclose table and fieldnames with backquotes
- CHECKED: Data
- CHECKED: Complete Inserts
- CHECKED: Extended Inserts
- CHECKED: Use hexadecimal for binary fields
- Export Type: INSERT
- CHECKED: Save as File
- Filename Template: __DB__
- Compression: "gzipped" (or whatever you prefer... gzip makes smaller files)



TO RESTORE THE DATABASE:
- open phpMyAdmin
- select the database you wish to restore INTO, from the dropdown menu
- click on the SQL tab
- click on the Browse button, and select the file you downloaded when making the backup
- click Go

[gjh42]

Thank you so much, DrByte, for putting all this info in one concise package!

[Wils]

As acomment if you have problems with:

MySQL said:

1007: Can't create database 'databaseName'. Database exists

This may because some hosts does not allow DROP for sequrity reasons. I have to comment out:

DROP DATABASE `dbbame`;
CREATE DATABASE `dbname`;

and only leave

USE dbname;

in the header. I also normally drop the tables manually before import. This is the only way I got it to work on some hosts, there may be other and better ways...