1. MySQL 튜닝
 
1) 슬로우 쿼리 설정
    long_query_time=5
    log-slow-queries='/usr/local/mysql/data/slow-queries.log'
    > 쿼리응답이 5초 이상 지연될때 slow-queries.log 파일에 기록이 되게됩니다.
 
2) 메모리 및 콘텐츠에 따른 설정값 조정
    key_buffer_size=64M       // 기존 25M
    인덱스블락에서 사용하는 버퍼의 크기입니다.
    인덱스가 많은 테이블에서 insert, delete의 작업이 많다면 증가해 주는 것이 좋습니다.
 
    sort_buffer_size=512K      // 기존 256K
    정렬시 각 쓰레드에 할당하는 버퍼크기입니다.
    order by, group by 를 빠르게 하려면 증가시키는 것이 좋습니다.
 
    thread_cache_size=64     // 기존 8
    이미 사용한 쿼리를 기억해 놓는 캐시의 할당 크기입니다.
    같은 쿼리를 반복적으로 많이 하는 경우 크게 하는 것이 좋으나

    다른 설정을 위해 과도하게 높은 설정은 하지 않습니다.
 
    tmp_table_size=64M       // 기존 17M
    메모리 기반의 임시테이블을 디스크 기반의 임시테이블로 변환할 때의 크기입니다.
    복잡한 쿼리를 실행할 경우 변환되는 경우가 많으며 여유있게 설정하는 것이 좋습니다.
 
    table_cache=512            // 기존 256
 
 
2. MySQL 실제 사용량 체크
 
[root@sysdocu public_html] # mysqladmin -u root -p extended-status
Enter password: ****
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 0         |
| Aborted_connects                  | 34        |
 
+ Aborted_clients : 클라이언트에서 연결이 적절하게 닫지 못해서 죽어서 취소된 연결 횟수입니다. 이 값이 많다면 네트워크 연결에 문제가 있을 가능성이 있습니다. 네트웍을 확인해 보는 것이 좋으나 경우에 따라서 프로그래밍시 연결을 적절하게 끊어주지 않아 많이 발생하는 경우도 있으므로 크게 신경 쓸 필요는 없습니다.
 
+ Aborted_connects : 연결을 시도해서 실패한 횟수로 최대 동시접속자 수나 네트웍을 확인해보아야 합니다.
 
| Binlog_cache_disk_use             | 0         |
| Binlog_cache_use                  | 0         |
| Bytes_received                    | 95817058  |
| Bytes_sent                        | 561551477 |
| Com_admin_commands                | 0         |
| Com_alter_db                      | 0         |
| Com_alter_table                   | 50        |
| Com_analyze                       | 0         |
| Com_backup_table                  | 0         |
| Com_begin                         | 0         |
| Com_call_procedure                | 0         |
| Com_change_db                     | 6432      |
| Com_change_master                 | 0         |
| Com_check                         | 0         |
| Com_checksum                      | 0         |
| Com_commit                        | 0         |
| Com_create_db                     | 1         |
| Com_create_function               | 0         |
| Com_create_index                  | 0         |
| Com_create_table                  | 25        |
| Com_create_user                   | 0         |
| Com_dealloc_sql                   | 0         |
| Com_delete                        | 0         |
| Com_delete_multi                  | 0         |
| Com_do                            | 0         |
| Com_drop_db                       | 0         |
| Com_drop_function                 | 0         |
| Com_drop_index                    | 0         |
| Com_drop_table                    | 25        |
| Com_drop_user                     | 0         |
| Com_execute_sql                   | 0         |
| Com_flush                         | 0         |
| Com_grant                         | 2         |
| Com_ha_close                      | 0         |
| Com_ha_open                       | 0         |
| Com_ha_read                       | 0         |
| Com_help                          | 0         |
| Com_insert                        | 3416      |
| Com_insert_select                 | 0         |
| Com_kill                          | 0         |
| Com_load                          | 0         |
| Com_load_master_data              | 0         |
| Com_load_master_table             | 0         |
| Com_lock_tables                   | 25        |
| Com_optimize                      | 0         |
| Com_preload_keys                  | 0         |
| Com_prepare_sql                   | 0         |
| Com_purge                         | 0         |
| Com_purge_before_date             | 0         |
| Com_rename_table                  | 0         |
| Com_repair                        | 0         |
| Com_replace                       | 0         |
| Com_replace_select                | 0         |
| Com_reset                         | 0         |
| Com_restore_table                 | 0         |
| Com_revoke                        | 0         |
| Com_revoke_all                    | 0         |
| Com_rollback                      | 0         |
| Com_savepoint                     | 0         |
| Com_select                        | 1846884   |
| Com_set_option                    | 6443      |
| Com_show_binlog_events            | 0         |
| Com_show_binlogs                  | 0         |
| Com_show_charsets                 | 0         |
| Com_show_collations               | 0         |
| Com_show_column_types             | 0         |
| Com_show_create_db                | 0         |
| Com_show_create_table             | 0         |
| Com_show_databases                | 0         |
| Com_show_errors                   | 0         |
| Com_show_fields                   | 0         |
| Com_show_grants                   | 0         |
| Com_show_innodb_status            | 0         |
| Com_show_keys                     | 0         |
| Com_show_logs                     | 0         |
| Com_show_master_status            | 0         |
| Com_show_ndb_status               | 0         |
| Com_show_new_master               | 0         |
| Com_show_open_tables              | 0         |
| Com_show_privileges               | 0         |
| Com_show_processlist              | 0         |
| Com_show_slave_hosts              | 0         |
| Com_show_slave_status             | 0         |
| Com_show_status                   | 1         |
| Com_show_storage_engines          | 0         |
| Com_show_tables                   | 0         |
| Com_show_triggers                 | 0         |
| Com_show_variables                | 0         |
| Com_show_warnings                 | 0         |
| Com_slave_start                   | 0         |
| Com_slave_stop                    | 0         |
| Com_stmt_close                    | 0         |
| Com_stmt_execute                  | 0         |
| Com_stmt_fetch                    | 0         |
| Com_stmt_prepare                  | 0         |
| Com_stmt_reset                    | 0         |
| Com_stmt_send_long_data           | 0         |
| Com_truncate                      | 0         |
| Com_unlock_tables                 | 25        |
| Com_update                        | 42        |
| Com_update_multi                  | 0         |
| Com_xa_commit                     | 0         |
| Com_xa_end                        | 0         |
| Com_xa_prepare                    | 0         |
| Com_xa_recover                    | 0         |
| Com_xa_rollback                   | 0         |
| Com_xa_start                      | 0         |
| Compression                       | OFF       |
| Connections                       | 6471      |
 
+ Connections : 연결을 시도한 횟수로 사용량을 알 수 있습니다.
 
| Created_tmp_disk_tables           | 0         |
| Created_tmp_files                 | 5         |
| Created_tmp_tables                | 1         |
| Delayed_errors                    | 0         |
| Delayed_insert_threads            | 0         |
| Delayed_writes                    | 0         |
| Flush_commands                    | 1         |
| Handler_commit                    | 3461      |
| Handler_delete                    | 0         |
| Handler_discover                  | 0         |
| Handler_prepare                   | 0         |
| Handler_read_first                | 157453    |
| Handler_read_key                  | 2106624   |
| Handler_read_next                 | 4459      |
| Handler_read_prev                 | 822       |
| Handler_read_rnd                  | 1839502   |
| Handler_read_rnd_next             | 433799767 |
| Handler_rollback                  | 0         |
| Handler_savepoint                 | 0         |
| Handler_savepoint_rollback        | 0         |
| Handler_update                    | 32        |
| Handler_write                     | 78830     |
| Innodb_buffer_pool_pages_data     | 349       |
| Innodb_buffer_pool_pages_dirty    | 0         |
| Innodb_buffer_pool_pages_flushed  | 4731      |
| Innodb_buffer_pool_pages_free     | 2658      |
| Innodb_buffer_pool_pages_latched  | 0         |
| Innodb_buffer_pool_pages_misc     | 1         |
| Innodb_buffer_pool_pages_total    | 3008      |
| Innodb_buffer_pool_read_ahead_rnd | 1         |
| Innodb_buffer_pool_read_ahead_seq | 0         |
| Innodb_buffer_pool_read_requests  | 1173563   |
| Innodb_buffer_pool_reads          | 13        |
| Innodb_buffer_pool_wait_free      | 0         |
| Innodb_buffer_pool_write_requests | 149059    |
| Innodb_data_fsyncs                | 11751     |
| Innodb_data_pending_fsyncs        | 0         |
| Innodb_data_pending_reads         | 0         |
| Innodb_data_pending_writes        | 0         |
| Innodb_data_read                  | 3231744   |
| Innodb_data_reads                 | 74        |
| Innodb_data_writes                | 12541     |
| Innodb_data_written               | 164910080 |
| Innodb_dblwr_pages_written        | 4731      |
| Innodb_dblwr_writes               | 2187      |
| Innodb_log_waits                  | 0         |
| Innodb_log_write_requests         | 13804     |
| Innodb_log_writes                 | 3422      |
| Innodb_os_log_fsyncs              | 5602      |
| Innodb_os_log_pending_fsyncs      | 0         |
| Innodb_os_log_pending_writes      | 0         |
| Innodb_os_log_written             | 8762880   |
| Innodb_page_size                  | 16384     |
| Innodb_pages_created              | 285       |
| Innodb_pages_read                 | 64        |
| Innodb_pages_written              | 4731      |
| Innodb_row_lock_current_waits     | 0         |
| Innodb_row_lock_time              | 0         |
| Innodb_row_lock_time_avg          | 0         |
| Innodb_row_lock_time_max          | 0         |
| Innodb_row_lock_waits             | 0         |
| Innodb_rows_deleted               | 0         |
| Innodb_rows_inserted              | 68961     |
| Innodb_rows_read                  | 630954    |
| Innodb_rows_updated               | 0         |
| Key_blocks_not_flushed            | 0         |
| Key_blocks_unused                 | 22471     |
| Key_blocks_used                   | 128       |
| Key_read_requests                 | 3621680   |
| Key_reads                         | 685       |
 
+ Key_reads : 디스크에서 물리적으로 키 블락을 읽어들인 횟수로 이 값이 크면 key_buffer_size이 너무 작게 설정되어 있는 경우가 대부분이며 이 값이 크면 성능에 큰 문제가 될 수 있으므로 key_buffer_size를 크게 설정해야 하며 시스템의 성능이 늘릴 수 없을 경우에는 시스템을 업그레이드 하는 것이 좋습니다.
key_buffer_size이 시스템의 성능에 비해 너무 크게 잡을 경우 계속적인 페이징을 하게되어 더 느려질 수 있으므로 잘 고려해야 합니다.
 
+ Key_reads, Key_read_request : Key_reads/Key_read_request로 계산하면 캐시 히트율을 알 수 있습니다. 0.01보다는 작아야 하며 무조건 작을 수록 좋습니다. 0.01보다 클 경우 key_buffer_size를 조정해줍니다.
 
| Key_write_requests                | 11607     |
| Key_writes                        | 155       |
| Last_query_cost                   | 0.000000  |
| Max_used_connections              | 10        |
 
+ Max_used_connections : 최대 동시 접속자수이며 이 값을 보고 max_connections의 값을 조정합니다.
 
| Not_flushed_delayed_rows          | 0         |
| Open_files                        | 13        |
| Open_streams                      | 0         |
| Open_tables                       | 13        |
| Opened_tables                     | 643       |
 
+ Opened_tables : 열었던 테이블 수로 이 값이 너무 클 경우 table_cache의 값이 작은 경우일 수 있으므로 table_cache의 값을 늘려 주어야 합니다.
 
| Prepared_stmt_count               | 0         |
| Qcache_free_blocks                | 0         |
| Qcache_free_memory                | 0         |
| Qcache_hits                       | 0         |
| Qcache_inserts                    | 0         |
| Qcache_lowmem_prunes              | 0         |
| Qcache_not_cached                 | 0         |
| Qcache_queries_in_cache           | 0         |
| Qcache_total_blocks               | 0         |
| Questions                         | 1869806   |
 
+ Questions : 서버로 보낸 질의 횟수로 이 값으로 앞으로의 사용량을 예측해보아 앞으로의 업그레으드의 계획을 세우는 것이 좋습니다. Questions와 Uptime을 이용하여 초당 질의 수를 알아볼 수 있습니다.
 
| Rpl_status                        | NULL      |
| Select_full_join                  | 0         |
 
+ Select_full_join : 인덱스를 사용하지 않은 조인으로 최대한 0에 가깝게 해주는 것이 좋으며 값이 너무 클 경우 테이블의 인덱스를 재설정해주거나 쿼리문은 수정해야 합니다.
 
| Select_full_range_join            | 0         |
| Select_range                      | 0         |
| Select_range_check                | 0         |
| Select_scan                       | 55666     |
| Slave_open_temp_tables            | 0         |
| Slave_retried_transactions        | 0         |
| Slave_running                     | OFF       |
| Slow_launch_threads               | 0         |
| Slow_queries                      | 0         |
| Sort_merge_passes                 | 0         |
| Sort_range                        | 0         |
| Sort_rows                         | 1843741   |
| Sort_scan                         | 53628     |
| Ssl_accept_renegotiates           | 0         |
| Ssl_accepts                       | 0         |
| Ssl_callback_cache_hits           | 0         |
| Ssl_cipher                        |           |
| Ssl_cipher_list                   |           |
| Ssl_client_connects               | 0         |
| Ssl_connect_renegotiates          | 0         |
| Ssl_ctx_verify_depth              | 0         |
| Ssl_ctx_verify_mode               | 0         |
| Ssl_default_timeout               | 0         |
| Ssl_finished_accepts              | 0         |
| Ssl_finished_connects             | 0         |
| Ssl_session_cache_hits            | 0         |
| Ssl_session_cache_misses          | 0         |
| Ssl_session_cache_mode            | NONE      |
| Ssl_session_cache_overflows       | 0         |
| Ssl_session_cache_size            | 0         |
| Ssl_session_cache_timeouts        | 0         |
| Ssl_sessions_reused               | 0         |
| Ssl_used_session_cache_entries    | 0         |
| Ssl_verify_depth                  | 0         |
| Ssl_verify_mode                   | 0         |
| Ssl_version                       |           |
| Table_locks_immediate             | 1850713   |
| Table_locks_waited                | 1         |
 
+ Table_locks_waited : 테이블 락을 위해 대기한 시간으로 최대한 작아야 합니다. 설정 값만으로 줄일 수는 없으며 너무 클 경우 데이타베이스의 설계나 쿼리문에 문제가 있을 수 있으며 속도저하의 원인이 대부분 여기서 찾을 수 있습니다.
 
| Tc_log_max_pages_used             | 0         |
| Tc_log_page_size                  | 0         |
| Tc_log_page_waits                 | 0         |
| Threads_cached                    | 7         |
| Threads_connected                 | 1         |
| Threads_created                   | 10        |
| Threads_running                   | 1         |
| Uptime                            | 70572     |
| Uptime_since_flush_status         | 70572     |
+-----------------------------------+-----------+

my.cnf 파일의 [mysqld] 항목에 아래 옵션을 추가해줍니다.

expire_logs_days = 3        // 3일치 저장
 

* 바이너리 로그파일은 쿼리 로그 분석 및 데이타 복구에 사용됩니다.

   하지만 초기파일(로그 앞번호)이 삭제 될경우 데이타 복구를 온전히 할 수 없게되니 유의하시기 바랍니다.

mysql> select * from sysdocu limit 1;

mysql> select * from sysdocu order by no desc limit 1;    // 역순

limit 뒤의 숫자로 몇개의 행만 가져올지 정할 수 있습니다.

innodb 설정값을 변경하려면 기존의 데이타는 dump 받고
설정 적용(my.cnf 수정 및 mysqld restart) 한뒤

dump 받은 데이타를 import 하는것이 안정적입니다.

my.cnf 파일에 아래 내용을 삽입합니다.

root@sysdocu:~# vi /etc/my.cnf

[에러]
Client does not support authentication protocol requested by server; consider upgrading MySQL client

[원인]
설치한 버전의 mysql 버전이 달라서 생기는 경우

[해결]
mysql 로그인 후 아래와 같이 OLD_PASSWORD 설정을 합니다.

mysql> use mysql;
mysql> set password for sysdocu@localhost = OLD_PASSWORD('sysdocu!@#$');
mysql> flush privileges;

my.ini 파일을 수정해도 변경이 되지 않을경우

[mysqld] 항목에 아래 한줄을 더 추가해봅니다.

character-set-client-handshake = FALSE

이름이 sysdocu 인 사람을 제외하고 모두 삭제합니다.

mysql> delete from members where not (name='sysdocu');

20120420012035 형식을 2012-04-20 01:20:35 로 바꾸는 예 입니다.

참고로 varchar(19) 로 길이를 맞추어놓아야 잘리거나 불필요한 숫자가 들어가지 않습니다.

mysql> update member set register=DATE_FORMAT(register, '%Y-%m-%d %h:%m:%s');

mysql 고버전에서 mysqldump 시 한 행으로 여러 데이타를 묶어서 INSERT 시키게 되어있습니다.

그래서 데이타 이전이나 필드 또는 값 수정시 별도의 INSERT 구문이 필요하다면

mysqldump 할때 아래와 같은 옵션을 이용해보세요.

--skip-extended-insert

예) mysqldump -p --skip-extended-insert sysdocu > sysdocu.sql

원격에서 백업이나 로컬백업 모두 동일합니다.

mysql 5.5.14 에서 테스트 함

# mysqldump -u sysdocu -p -h 192.168.10.2 sysdocu > sysdocu.sql
Enter password:
mysqldump: Got error: 1045: Access denied for user 'sysdocu'@'191.168.10.3' (using password: YES) when using LOCK TABLES

이경우 아래와 같이 백업하면 됩니다.

# mysqldump -u sysdocu -p -h 192.168.10.2 --single-transaction sysdocu > sysdocu.sql

/etc/my.cnf 의 [mysqld] 항목에 아래 내용을 추가하고 mysqld 를 재시작만 하면 됩니다.

default-storage-engine = myisam