LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 02-27-2009, 02:52 AM   #1
brianmcgee
Member
 
Registered: Jun 2007
Location: Munich, Germany
Distribution: RHEL, CentOS, Fedora, SLES (...)
Posts: 399

Rep: Reputation: 40
Migrate a MySQL database without special character hell


I have two systems at hand. One a Debian box, the other a RHEL5 box.

On the Debian box there is a MySQL server (5.0.32-Debian_7etch1) running which hosts Databases from a customized content management system.

This system has grown over time and the tables are stored with different collations.

Now I want to export and reimport the data on the RHEL5 MysQL server (5.0.45).

And here the special character hell begins. It doesn't matter if I reimport the data as utf8, as latin1 or latin1_swedish_ci. Everytime the special characters are wrong and distorted.

Surprisingly the data that is shipped with the unmodified CMS displays the special chars correctly after installation.

But I have to import the modified version from the old server as there are lots of modifications to import.

Does anyone know how to export and reimport such data?

Old server:
Quote:
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
New server:
Quote:
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
 
Old 02-27-2009, 10:35 AM   #2
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 26,477

Rep: Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937Reputation: 7937
Quote:
Originally Posted by brianmcgee View Post
I have two systems at hand. One a Debian box, the other a RHEL5 box.

On the Debian box there is a MySQL server (5.0.32-Debian_7etch1) running which hosts Databases from a customized content management system.

This system has grown over time and the tables are stored with different collations.

Now I want to export and reimport the data on the RHEL5 MysQL server (5.0.45).

And here the special character hell begins. It doesn't matter if I reimport the data as utf8, as latin1 or latin1_swedish_ci. Everytime the special characters are wrong and distorted.

Surprisingly the data that is shipped with the unmodified CMS displays the special chars correctly after installation.

But I have to import the modified version from the old server as there are lots of modifications to import.

Does anyone know how to export and reimport such data?
I've not had to deal with that particular problem, but two things come to mind that you might want to try.

You don't say how you're exporting the data, but are you using mysqldump? That should just dump everything to a file, which SHOULD let you read it right back in with "mysql -u <user id> -D <database name> < <export file name>", such as "mysql -u root -D mydatabase < mydatabase.sql".

If that doesn't work...you could try to low-tech it, by shutting down mysql, and tar'ring up the /var/lib/mysql directory (at least that's where mysql lives on my system), and copying it to the new server, then un-tar it. That should copy the database files, with everything intact.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
block special and character special files s_shenbaga Linux - Newbie 4 06-23-2015 03:16 AM
Migrate MySQL database to new server babal Linux - Server 4 11-21-2007 02:31 AM
Migrate MySQL database to new server babal LinuxQuestions.org Member Intro 1 11-17-2007 05:45 AM
using perl to insert special characters into mysql database ihopeto Programming 5 12-25-2006 05:08 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 04:09 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration