phpMyAdmin Backup Settings

[kobra]

I am having issues using phpMyAdmin to create a backup file that will actually restore without an error.

There are many settings in phpMyAdmin and I am fairly sure that I am not creating the correct set to allow the created file to restore properly. I have gone over the manual for it and it covers the functions of each yet I still can not determine what set of settings to enable.

Is there a prescribed set of parameters one must use to allow a successful restore?

I am attempting this on a host that limits support and access. I have installed the mod to attach the backup in the admin but this errors as described in the docs and I can not seem to correct the path to the mysqldump location. I use this for other hosting with success but not with this particular host.

I would also welcome a text query that would perform the backup if that is available.

I am able to create a backup file with success, but it will not restore and errors usually with the 127 error. I am fairly sure that I just have not created the correct type of backup file.

If it is version specific; it is phpMyadmin ver 2.7.0-pl2

Thanks for your assistance

[JFD]

Have you tried the settings described here?: http://www.zen-cart.com/wiki/index.php/LocalCopySite

[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...

[kobra]

Thanks for all the experiences... I as well as many rely upon menu driven interfaces to manipulate their DB's. So when presented with a more terse interface become lost.

This is not an easy subject to address as there are many different types of interfaces, even when named simiarily one can be presented with a different set of choices or choices missing from what they have been accustomed to or have seen elsewhere.

While this forum is not for the specific purpose of making individuals MySQL experts, it is a necessary function for those that are maintaining, upgrading or moving their ZenCarts.

Thanks to Dr. Byte for the normally suggested phpMyadmin settings for accomplishing this. I am sure that it will assist many and hope that through searching tht this thread will be included.