[Ubuntu] 우분투 MySQL 설정

우분투에서 MySQL 관련 설정을 알아봅니다.

기본 문자셋 설정

언어셋을 따로 설정하지 않고 DB를 생성하게 되면 latin1로 설정된다. 이 경우 게시판등에 한글이 출력될때는 문제가 없을수도 있으나 DB 자료 자체를 출력해보면 ??? 와 같은 문자로 출력된다.

sudo vi /etc/mysql/my.cnf

  [client]
  default-character-set = utf8

  [mysqld]
  character-set-client-handshake=FALSE
  init_connect="SET collation_connection = utf8_general_ci"
  init_connect="SET NAMES utf8"
  character-set-server = utf8 
  collation-server = utf8_general_ci

  [mysqldump]
  default-character-set = utf8

  [mysql]
  default-character-set = utf8

수정한 후에는 MySQL을 재시작한다.

sudo service mysql restart

우선 mysql 로그인을 한다.

mysql -uroot -p

문자셋을 확인한다.

mysql> status

  Server characterset:	utf8
  Db     characterset:	utf8

mysql> show variables like '%char%';

  +--------------------------+----------------------------+
  | 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       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+

max_allowed_packet 설정

이 값에 의해 쿼리문등으로 전송량이 결정된다. 기본값이 16M인데 이 값이 작아 오류를 내기도 한다.

sudo vi /etc/mysql/my.cnf

  [mysqld]
  max_allowed_packet    = 256M

MySQL 재시작

sudo service mysql restart

서버 이전시 DB 문자셋이 깨질경우 참고할 내용

http://blog.naver.com/PostView.nhn?blogId=nanobox&logNo=130165727244

MySQL 최적화

MySQL 서버를 최적화하기 위해 Percona's my.cnf generating tool을 사용하여 my.cnf 파일을 생성할 수 있다. 각각의 단계별 질문과 대답의 형식으로 my.cnf 파일을 생성해 준다. 이미 데이터가 들어있고 운영중인 MySQL 서버라면 백업하고 MySQL을 멈춘후 파일을 교체해야 한다.

백업하기

mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql

MySQL 서비스중지

sudo service mysql stop

원본 my.cnf 백업하고 새 파일로 교체

sudo cp /etc/my.cnf /etc/my.cnf.backup
sudo cp /path/to/new/my.cnf /etc/my.cnf

기존의 데이터베이스를 삭제하고 재설치

sudo rm -rf /var/lib/mysql/*
sudo mysql_install_db
sudo chown -R mysql: /var/lib/mysql
sudo service start mysql

Finally all that's left is to re-import your data. To give us an idea of how far the import process has got you may find the 'Pipe Viewer' utility, pv, useful. The following shows how to install and use pv for this case, but if you'd rather not use it just replace pv with cat in the following command. Ignore any ETA times produced by pv, they're based on the average time taken to handle each row of the file, but the speed of inserting can vary wildly from row to row with mysqldumps:

sudo apt-get install pv

pv ~/fulldump.sql | mysql

Once that is complete all is good to go!

This is not necessary for all my.cnf changes. Most of the variables you may wish to change to improve performance are adjustable even whilst the server is running. As with anything, make sure to have a good backup copy of config files and data before making changes.

MySQL Tuner is a useful tool that will connect to a running MySQL instance and offer suggestions for how it can be best configured for your workload. The longer the server has been running for, the better the advice mysqltuner can provide. In a production environment, consider waiting for at least 24 hours before running the tool. You can get install mysqltuner from the Ubuntu repositories:

sudo apt-get install mysqltuner

Then once its been installed, run it:

mysqltuner

and wait for its final report. The top section provides general information about the database server, and the bottom section provides tuning suggestions to alter in your my.cnf. Most of these can be altered live on the server without restarting, look through the official MySQL documentation (link in Resources section) for the relevant variables to change in production. The following is part of an example report from a production database which shows there may be some benefit from increasing the amount of query cache:

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

    key_buffer_size (> 1.4G)

    query_cache_size (> 32M)

    table_cache (> 64)

    innodb_buffer_pool_size (>= 22G)

One final comment on tuning databases: Whilst we can broadly say that certain settings are the best, performance can vary from application to application. For example, what works best for Wordpress might not be the best for Drupal, Joomla or proprietary applications. Performance is dependent on the types of queries, use of indexes, how efficient the database design is and so on. You may find it useful to spend some time searching for database tuning tips based on what applications you're using it for. Once you get past a certain point any adjustments you make will only result in minor improvements, and you'll be better off either improving the application, or looking at scaling up your database environment through either using more powerful hardware or by adding slave servers.