득중유고: mysql

1 백업복구
2 UTF8
3 인덱스
4 쿼리 성능
5 MySQL Migration Toolkit

1 백업복구 ¶

참고 :

백업

$ mysqldump --default-character-set=utf8 -u root -p secret stocksdb > stocksdb.sql

혹은 --default-character-set=euckr

복구

$ mysql -u setzer setzer < create_tables.sql -p

mysqlimport / mysqldump(to csv)는 해당 사용자에게 file privilages가 있어야 함.
csv 파일은 반드시 삽입할 테이블의 캐릭터셋에 맞게 해 준다.
(ex: ANSI/DOS)

테스트 (bookcafe 기준)

# mysql -u setzer setzer < create_tables.sql -p

# mysqlimport -u setzer -p --fields-terminated-by="," setzer C:\RWAPM\www\sql\mssql\lib_attrib.csv

# mysql -u setzer setzer < drop_tables.sql -p

2 UTF8 ¶

my.cnf:

[client]

default-character-set=utf8

[mysqld]

lower_case_table_names=0

default-character-set=utf8

[mysqldump]

default-character-set=utf8

db:

DROP TABLE IF EXISTS test_table;

CREATE TABLE test_table(

        col1 SMALLINT UNSIGNED NOT NULL DEFAULT 500,

--중략

        col9 LONGTEXT NOT NULL

) CHARACTER SET utf8 COLLATE utf8_general_ci;

php(pdo):

$mdb = new PDO('mysql:host=localhost;dbname=testdb', 'testuser', 'testpass');

$mdb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$mdb->exec('SET NAMES utf8');

$mdb->exec('SET CHARACTER SET utf8');

마지막으로 확인 :

mysql> show variables like 'c%';

+--------------------------+-----------------------------+

| Variable_name            | Value                       |

+--------------------------+-----------------------------+

| character_set_client     | utf8                        |

| character_set_connection | utf8                        |

| character_set_database   | utf8                        |

| character_set_filesystem | binary                      |

| character_set_results    | utf8                        |

| character_set_server     | utf8                        |

| character_set_system     | utf8                        |

| character_sets_dir       | C:\RWAPM\DB\share\charsets\ |

| collation_connection     | utf8_general_ci             |

| collation_database       | utf8_general_ci             |

| collation_server         | utf8_general_ci             |

| completion_type          | 0                           |

| concurrent_insert        | 1                           |

| connect_timeout          | 5                           |

+--------------------------+-----------------------------+

14 rows in set (0.20 sec)

3 인덱스 ¶

생성시

CREATE TABLE test_table(

        col1 SMALLINT NULL,

--중략

        col9 LONGTEXT NOT NULL,

        INDEX(col1),

        INDEX(col9)

--혹은

        INDEX(col1, col9)

);

수정시

ALTER TABLE test_table ADD INDEX(col1, col2,...);

--혹은

ALTER TABLE test_table ADD INDEX index_name(col1, col2,...);

삭제시

ALTER TABLE tablename DROP INDEX col1;

ALTER TABLE tablename DROP INDEX index_name;

확인

DESCRIBE city;

+------------+----------+------+-----+---------+----------------+

| Field      | Type     | Null | Key | Default | Extra          |

+------------+----------+------+-----+---------+----------------+

| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |

| Name       | char(35) | NO   |     |         |                |

| Country    | char(3)  | NO   | UNI |         |                |

| District   | char(20) | YES  | MUL |         |                |

| Population | int(11)  | NO   |     | 0       |                |

+------------+----------+------+-----+---------+----------------+

PRI, UNI, MUL 등에 대한 자세한 내용은 [http]

MySQL 5.0 Reference Manual : 11.3.1. DESCRIBE Syntax 참고

4 쿼리 성능 ¶

explain문을 사용, 인덱스 지정 전-후를 비교해본다.

mysql> explain select * from vLib_detail order by regDate desc;

+----+-------------+--------------------+--------+---------------+---------+---------+----------------------------------------+-------+---------------------------------+

| id | select_type | table              | type   | possible_keys | key     | key_len | ref                                    | rows  | Extra                           |

+----+-------------+--------------------+--------+---------------+---------+---------+----------------------------------------+-------+---------------------------------+

|  1 | SIMPLE      | lib_main           | ALL    | NULL          | NULL    | NULL    | NULL                                   |  8450 | Using temporary; Using filesort |

|  1 | SIMPLE      | lib_multi          | eq_ref | PRIMARY       | PRIMARY | 4       | bookcafe.lib_main.dataNum              |     1 |                                 |

|  1 | SIMPLE      | lib_book           | eq_ref | PRIMARY       | PRIMARY | 4       | bookcafe.lib_main.dataNum              |     1 |                                 |

|  1 | SIMPLE      | lib_mainToCategory | ALL    | categoryId    | NULL    | NULL    | NULL                                   | 12182 |                                 |

|  1 | SIMPLE      | lib_category       | eq_ref | PRIMARY       | PRIMARY | 4       | bookcafe.lib_mainToCategory.categoryId |     1 |                                 |

+----+-------------+--------------------+--------+---------------+---------+---------+----------------------------------------+-------+---------------------------------+

5 rows in set (0.00 sec)

Using temporary, Using filesort 부분을 주의하여 제거한다.

참고 문서 :

MySQL 쿼리 최적화

5 MySQL Migration Toolkit ¶

진행 과정중 db를 선택할 수 없음. 기본적으로 소스 DB와 같은 이름의 DB에 테이블을 삽입하려고 시도한다.

간단한 방법은, 소스 DB의 이름을 타겟 DB와 동일하게 변경하는 것이다.(혹은 복사)
ms access 파일인 mdb를 사용할 때에는 그냥 파일 이름을 타겟 db와 동일하게 변경해주면 된다.

Packet for query is too large (1405337 > 1047552). You can change this value on the server by setting the max_allowed_packet' variable. 라는 메시지가 발생할 때에는 my.ini에

mysqld
max_allowed_packet = 4M
식으로 삽입하면 된다.

Contents

1 백업복구 ¶

2 UTF8 ¶

3 인덱스 ¶

4 쿼리 성능 ¶

5 MySQL Migration Toolkit ¶