Totally Zenned
- Join Date:
- Jun 2005
- Location:
- Cumbria, UK
- Posts:
- 10,327
- Plugin Contributions:
- 0
How to use phpMyAdmin to get a copy of your raw dbase sql
I just posted this in another thread, but feel there could be interest in it in this General Section:
I am often asked how to get a "copy" of the database's raw SQL, to be used either as a backup, or as a means of "populating" a new empty database.
Here's my technique and while it works for me, the following is not a gospel, but a method I have found to be reliable and consistently effective.
Here's my procedure:
-
Go to the database in phpMyAdmin, so that the screen displays all the tables (left pane) and all the tables (right pane). This is the default display when you select the database from the menu.
-
Click EXPORT tab (Right Pane... top).
-
Select ALL tables, click Radio Button SQL.
-
Under OPTIONS:
Add custom comment into header (\n splits lines) [EMPTY]
Enclose export in a transaction [UNCHECKED]
Disable foreign key checks [UNCHECKED]
SQL compatibility mode [NONE]
Structure [CHECKED]
Add DROP TABLE / VIEW / PROCEDURE / FUNCTION [UNCHECKED]
Add IF NOT EXISTS [CHECKED]
Add AUTO_INCREMENT value [CHECKED]
Enclose table and field names with backquotes [CHECKED]
Add CREATE PROCEDURE / FUNCTION [UNCHECKED]
Add into comments (Creation/Update/Check dates) [UNCHECKED] But can be checked if you want.
Data [CHECKED]
Complete inserts [CHECKED]
Extended inserts [CHECKED]
Maximal length of created query [50000] You may need to change this, but this is default and should be OK.
Use delayed inserts [UNCHECKED]
Use ignore inserts [UNCHECKED]
Use hexadecimal for BLOB [CHECKED]
Export type [INSERT] (If my db crashes badly, I generally create a brand new one, so INSERT is appropriate for me.
This will DUMP a lot of "SQL TXT" into the frame. The bigger your db, the more the TXT.
I just then COPY <ctrl + c> and then PASTE <ctrl + v> into a blank notepad++ file, and save it as .sql with an appropriate name that includes the DATE and TIME I did the dump.
I use the above to CLONE databases all the time and over many years, havev never had a failure. On one occasion the version of MySQL was OLDER than my dump, so in SQL compatibility mode, I made necessary adjustments...
