우분투에서 mysql 사용하기

설치하기
mysql 서버와 클라이언트를 설치한다

sudo apt-get install mysql-server mysql-client

설치하는 중 root 사용자 패스워드를 입력하라는 화면이 나오면 사용할 root 패스워드를 입력한다.

mysql 설정파일(/etc/mysql/my.cnf)에서 bind-address 설정부분을 주석처리한다. 

sudo vi /etc/mysql/my.cnf

아래 부분을 주석처리한다.

# bind-address = 127.0.0.1

데이터베이스 생성하기
create 명령어를 사용해서 데이터베이스를 생성한다. 여기에서는 springbook이라는 이름으로 데이터베이스를 생성한다.

mysqladmin -u root create springbook -p

데이터베이스에 접속하기
앞에서 생성한 데이터베이스에 접속한다.

mysql -u root -p springbook

현재 만들어진 테이블을 살펴본다. 아직 테이블을 만드지 않았으므로 아무런 테이블도 표시되지 않는다.

mysql> show tables;
Empty set (0.000 sec)

우분투에서 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 -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/ |

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

이 값에 의해 쿼리문등으로 전송량이 결정된다.

기본값이 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 서버를 최적화하기 위해 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.