phpMyAdmin Backup Settings

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