I realized I have to make some character set changes on one of my user databases, and I want to make a backup before I start. Of course there are server backups as well, but I view those as an absolute last resort.
I was planning to use mysqldump for this. Is there a better option that I'm overlooking?
Is there any reason that the output of 'mysqldump -hSERVER DATABASE' with no additional options would have problems restoring on the toolserver databases?
Are there any subtle options that need to be set on mysqldump to make sure the output is usable in that environment?
- Carl
In article AANLkTin7wxBCmGRiTvpo16A8jZgyD_s8+mA5qw-9ZX1V@mail.gmail.com, Carl (CBM) cbm.wikipedia@gmail.com wrote:
I realized I have to make some character set changes on one of my user databases, and I want to make a backup before I start.
I was planning to use mysqldump for this. Is there a better option that I'm overlooking?
Unlikely.
Is there any reason that the output of 'mysqldump -hSERVER DATABASE' with no additional options would have problems restoring on the toolserver databases?
The only reason I've seen for this to fail is if you manually increase the limit on the size of MEMORY tables, then dump and reimport the tables; if the default limit is smaller, the import will fail.
I believe the maximum size of a MEMORY table is now the same as the default limit (to stop people using up massive amounts of memory), so this probably won't be an issue.
Are there any subtle options that need to be set on mysqldump to make sure the output is usable in that environment?
In previous versions of MySQL it was necessary to use 'mysqldump --default-character-set=latin1' if you had tables marked as Latin-1 which actually contained UTF-8 data. Otherwise, the dump would be corrupted and unusable.
I think this has been fixed (since MySQL now has partial Unicode support), but it would probably not hurt to use it anyway if you have such tables.
- river.
River Tarnell r.tarnell@IEEE.ORG wrote:
Are there any subtle options that need to be set on mysqldump to make sure the output is usable in that environment?
In previous versions of MySQL it was necessary to use 'mysqldump --default-character-set=latin1' if you had tables marked as Latin-1 which actually contained UTF-8 data. Otherwise, the dump would be corrupted and unusable.
I would check every table with
SHOW TABLE STATUS;
and check its character set (disguised in the Collation column) and use *that* character set in mysqldump (doing dumps table per table if necessary). Otherwise you are like to lose some data, or at least produce a dump that needs to be converted.
Dumping latin1 table as latin1 (even if it contains UTF-8 binary data) is the way to go (as river say), but dumping utf-8 as latin1 may not work as you want.
It is also a good idea to create another instance and restore tables from backup and see ho it looks like.
//Saper
On 02.02.2011 06:21, Marcin Cieslak wrote:
River Tarnell r.tarnell@IEEE.ORG wrote:
Are there any subtle options that need to be set on mysqldump to make sure the output is usable in that environment?
In previous versions of MySQL it was necessary to use 'mysqldump --default-character-set=latin1' if you had tables marked as Latin-1 which actually contained UTF-8 data. Otherwise, the dump would be corrupted and unusable.
Use --default-character-set BINARY for export with mysqldump *and* for import with the mysql client. This should preserve your original data, no matter what.
-- daniel
toolserver-l@lists.wikimedia.org