## Mysql user table ##
insert into user values ('localhost','user_name',password('xxxxx'),'n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','','','','','0','0','0');

mysql 5.x
insert into user values ('localhost','user_name',password('xxxxx'),'n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','n','','','','','0','0','0');

## Mysql db table ##
insert into db values ('localhost','db_name','user_name','y','y','y','y','y','y','y','y','y','y','y','y');

mysql 5.x
insert into db values ('localhost','db_name','user_name','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y');

## Mysql 갱신 ##
flush privileges;

## 리로드 ##
/usr/local/mysql/bin/mysqladmin -u root -p reload

### mysqldump ###
백업 : mysqldump -u [id] -p [dbname] > [filename].sql
복구 : mysql -u [id] -p [dbname] < [filename].sql

캐릭터셋지정 mysqldump
mysqldump -u [id] -p 데이터베이스 --default-character-set=euc_kr > 데이터베이스.sql

그밖의 option
mysqldump --help

## Replace 함수 사용방법 ##
update tablename set column = replace(column,'old','new');

## 랜덤하게 출력 방법  ##
~ order by rand() desc ~

## 기존 Mysql 4.0.26 - > 5 로 바꿀때..  ##
기존 Mysql 4.0.26 - > 5 로 바꿀때..
작성자 KPSYL 작성일 2006-07-23 23:02:46 조회수 433


처음부터 mysql5 쓸걸 잘못했습니다 정말 - _ ;;

Mysql 4.0.26 쓰다가 (Php setup for iis 사용) 제로보드 5 이용을 위해 mysql5 로 제거하고 설치했는데요

기존에 쓰던 mysql4 의 db를 mysql5 에 쓰도록 utf-8 로 변환시켜야하는데

도저히 어떻게 해야할지 모르겟군요



일단 각 db마다 mysqldump -u -p 데이터베이스 --default-character-set=euc_kr > 데이터베이스.sql

으로 sql파일으로 백업은 해두었는데

utf-8 로 변환하는것좀.. 누가 알려주시길 ㅠㅠ



제발 부탁드립니다... 그럼 수고..



이 글에 대한 댓글이 총 2건 있습니다.2대의 서버 또는 2개의 mysql 버전을 동시에 돌릴수있다면,



mysqldump 를 이용한방법을 권해드리고싶습니다.





기존의 euc-kr 를 덤프 받으실때 utf-8로 캐릭셋을 지정하여 받으시면

되실꺼 같네요



--default-character-set=euc_kr

이부분을

--default-character-set=utf-8

로 변경하시면 되지 않을까여?



직접해보지 않아서 장담을 못드리나,

예전에 utf-8 데이터를 euc-kr로 덤프받아 안전하게 돌린적은 있습니다.



mysqldump 사용방법을 확인하여보세요.

이창민(Prosper)님이 2006-07-24 19:18:41에 작성한 댓글입니다.
이 댓글은 2006-07-24 19:19:37에 마지막으로 수정되었습니다.기존에 덤프해놓은 파일 aaa.sql 을 에디터로 열고,


아래와 같이 맨위에 적으세요.





SET character_set_client = 'euckr';


SET character_set_results = 'euckr';


SET collation_connection = @@collation_database;





그리고 저장하세요.





쉘이나 phpMyAdmin 등등을 이용해 MySQL 5.X 에 입력하세요.





mysql -u -p 데이터베이스 < aaa.sql





sql 문에 호환되지 않는 부분이 있을 수도 있는데,


예를 들면,


- AUTO_INCREMENT 컬럼에 DEFAULT 값이 지정되어 있을 경우 에러


- 테이블이나 컬럼 이름에 ASCII 128 이상의 문자를 사용했을 경우 에러


- password 컬럼의 길이 16 => 41바이트.


   old_password()로 CGI 대체해야 할 수도...


등이 있을 수 있죠.


자세한 것은 업글 관련 문서를 참고하시길. ^^

#윈도우에서 사용할경우 my.ini 파일을열어서 수정하면됨.

# vi /etc/my.cnf
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
 
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

default-character-set=euckr

# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
 
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
 
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1
 
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname
 
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 64M
#bdb_max_lock = 100000
 
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
 
character-set-client-handshake=FALSE
init_connect="SET collation_connection = euckr_korean_ci"
init_connect="SET NAMES euckr"
default-character-set = euckr
character-set-server = euckr
collation-server = euckr_korean_ci

[mysqldump]
quick
max_allowed_packet = 16M

default-character-set=euckr

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates 

default-character-set=euckr

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout
#