Create a proper database dump

The first step is to create a MySQL database dump. When following directions from other websites, note the following shortcomings and / or limitations of those instructions:

Unless your database is hosted on localhost (default on cPanel), you must provide -h hostname details for the export

Database password usually needs to be provided after the export command is given.

This is the proper mysqldump command if your database is not stored on localhost:

mysqldump -u USERNAME -h HOSTNAME -p --no-create-db=true DATABASENAME > creativedumpfilenamewithdatastamp.sql

or

mysqldump -u=USERNAME -h=HOSTNAME --password=PASSWORD --no-create-db=true DATABASENAME > creativedumpfilenamewithdatastamp.sql

which are equivalent.

 

However, for databases with a problematic encoding, or for MySQL version 4 databases, we must tell the dump command to both treat its input data as being latin1 and to output data which does not contain a specific charset:

mysqldump -h db.host.com -u dbusername -p --opt --quote-names --no-create-db=true --skip-set-charset --default-character-set=latin1 dbname > creativedumpfilenamewithdatastamp.sql

or some others suggest:

mysqldump -h db.host.com -u dbusername -p -c --insert-ignore --no-create-db=true --skip-set-charset --default-character-set=latin1 dbname > creativedumpfilenamewithdatastamp.sql

 

Where,

--default-character-set=latin1 stands for "Use charset_name as the default character set. See Section 9.5, "Character Set Configuration". If no character set is specified, mysqldump uses utf8, and earlier versions use latin1."

-c stands for "--complete-insert, -c: Use complete INSERT statements that include column names."

--insert-ignore stands for "Write INSERT IGNORE statements rather than INSERT statements."

--skip-set-charset stands for: "--set-charset:

Add SET NAMES default_character_set to the output. This option is enabled by default.
To suppress the SET NAMES statement, use --skip-set-charset."

--opt stands for:

"This option is shorthand. It is the same as specifying --add-drop-table --add-locks
--create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
It should give you a fast dump operation and produce a dump file that can be reloaded
into a MySQL server quickly.

The --opt option is enabled by default. Use --skip-opt to disable it.  See the
discussion at the beginning of this section for information about selectively enabling
or disabling a subset of the options affected by --opt."

 

Some other websites specify this option, but this does not work:

-r stands for: "--result-file=file_name, -r file_name

Direct output to a given file. This option should be used on Windows to prevent
newline «\n» characters from being converted to «\r\n» carriage return/newline
sequences. The result file is created and its previous contents overwritten, even if
an error occurs while generating the dump."

 

Note: I have tested this with a latin1 encoded database containing both English and Cyrillic text. If your database contains non-Latin (non-European) characters not covered by Latin1 encoding you will need to do additional work. I have provided links in the Preliminary and Further Reading pages.

If characters inside a table are double-encoded, then you might need to do additional work. I have provided links in the Preliminary and Further Reading pages.



˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅

 

Did you like the article? Let Google Search know by clicking this button: . Please link to content that you find useful on this website on your own website, forum or blog! You can also comment on this page below, or to ask a question or suggest a topic for me to research. There is a user-editable Wiki available on my website, as well as a Forum that you can contribute to. Site Map.

Page last modified 06-Jan-13 21:08:20 EST
Comments on this page: