mysql backup and restore problem

Problems with the Windows version of XAMPP, questions, comments, and anything related.

mysql backup and restore problem

Postby hanpedro » 16. August 2010 16:36

I have a serious problem.
I can see mysql data at web page, but not at sql.

My database is "utf8" and "utf8_genral_ci", but web program's charset is "euc-kr".
The problem is backup and restore.

The command "mysqldump -uroot -p --default-character-set=euc-kr mydb > /my-backup.sql" can produce backup sql, but I could not read and restoring does not works. Also " --default-character-set=utf8" shows same result.

Code: Select all
mysqldump -uroot -p --default-character-set=euc-kr my-db > my-backup.sql
mysqldump -uroot -p --default-character-set=utf8 my-db > my-backup.sql


my.cnf is here;
[client]
# password = your_password
port = 3306
socket = mysql
default-character-set=utf8

[mysqld]
basedir="I:/apm_packages/xampp/mysql"
tmpdir="I:/apm_packages/xampp/tmp"
datadir="I:/apm_packages/xampp/mysql/data"
language=I:/apm_packages/xampp/mysql/share/korean/
#default-character-set=euckr

port = 3306
socket = mysql

#old-passwords
character-set-server = utf8
collation-server = utf8_general_ci

skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

server-id = 1

innodb_data_home_dir = I:/apm_packages/xampp/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = I:/apm_packages/xampp/mysql/data/

set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
max_allowed_packet=16M
default-character-set=utf8

[mysql]
no-auto-rehash
safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

[WinMySQLadmin]
Server="I:/apm_packages/xampp/mysql/bin/mysqld.exe"
#user=root
#password=


And the result of "show variables like 'c%'"
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/lampp/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
+--------------------------+----------------------------------+
hanpedro
 
Posts: 133
Joined: 09. February 2005 06:03
Operating System: SCientific Linux 6.1 x86

Re: mysql backup and restore problem

Postby JonB » 16. August 2010 17:02

Was the data input through a web app using the euc-kr character set?

I'm pretty sure you will have to re-encode your data to make it readable by other tools in a direct dump (becasue that's what it is) -- but -- at the moment -- I'm not 100% sure I am right.

What program are you using to examine the data from the backup?
What character set is your browser using?
What's your Windows code page?

this is a good 'clarifier'

http://htmlpurifier.org/docs/enduser-utf8.html

read through down to the part 'configuring your database'

I'm also hoping I am wrong -

Your best bet for a good answer would proabably be the MySQL forums - I go there for advanced WTF's all the time.

http://forums.mysql.com/

Good Luck

Jon B
8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: mysql backup and restore problem

Postby hanpedro » 16. August 2010 17:19

JonB/ Thanks for your kind reply.

1. Was the data input through a web app using the euc-kr character set?
- Yes.

2. What program are you using to examine the data from the backup?
- notepad ++ and uestudio, and phpmyadmin

3. What character set is your browser using?
- euc-kr

4. What's your Windows code page?
- cp949 (UHC)

And thanks for kind additional information. I'll study.
hanpedro
 
Posts: 133
Joined: 09. February 2005 06:03
Operating System: SCientific Linux 6.1 x86

Re: mysql backup and restore problem

Postby JonB » 16. August 2010 17:46

Hi again

I'm pretty sure you could mrror your database (its technically called a Replication Server or slave) -- you would now have a database you could experiment with re-encoding (if its needed) without trashing your data.

read this blurb:
http://www.astahost.com/info.php/Mirror ... t5155.html

and Google
mysql replicate database

here's another spot for very, very techie queries:

http://www.daniweb.com/

:shock:
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Re: mysql backup and restore problem

Postby hanpedro » 16. August 2010 18:58

Jonb,
Many thanks for your additional information.
I'm studying your links.
hanpedro
 
Posts: 133
Joined: 09. February 2005 06:03
Operating System: SCientific Linux 6.1 x86


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 76 guests