MariaDB를 CentOS 6에yum으로 인스톨하는 방법

MariaDB란？

MariaDB는、MySQL파생의 오픈소스 관계형 데이터베이스 관리시스템（RDBMS)이다。
MariaDB의 개발은、MySQL의 오리지널 코드의 저자인MySQL AB의 창시자인 Michael “Monty” Widenius에 의해、 현재 오라클에 의해 소유되어있는 MySQL을 본떠 세운 프로젝트에 의해 행해졌다.

 

MariaDB을 인스톨

 

CentOS 6.3에서 시험해봅니다.

$ cat /etc/redhat-release CentOS release 6.3 (Final)

MariaDB의 PGP key를 인스톨

# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

Repogitory의 설정을 준비.

baseurl은 http://yum.mariadb.org/로부터、자신의 환경에 있는 URL을 선택해라.

# vi /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.0.1/centos6-x86 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 enabled=1

MariaDB를yum으로 인스톨한다.

# yum install MariaDB-devel MariaDB-client MariaDB-server ... Is this ok [y/N]: y

화면에서 、Is this ok [y/N]:에서 y를 입력한다.  
혹시, 이하와 같은 에러가 나오면, mysql-libs를 삭제할 필요가 있다.

Transaction Check Error: file /etc/my.cnf conflicts between attempted installs of mysql-libs-5.1.67-1.el6_3.x86_64 and MariaDB-common-10.0.1-1.i686 file /usr/share/mysql/charsets/Index.xml conflicts between attempted installs of mysql-libs-5.1.67-1.el6_3.x86_64 and MariaDB-common-10.0.1-1.i686 file /usr/share/mysql/czech/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/danish/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/dutch/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/english/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/estonian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/french/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/german/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/greek/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/hungarian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/italian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/japanese/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/korean/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/norwegian-ny/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/norwegian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/polish/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/portuguese/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/romanian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/russian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/serbian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/slovak/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/spanish/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/swedish/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64 file /usr/share/mysql/ukrainian/errmsg.sys conflicts between attempted installs of MariaDB-server-10.0.1-1.i686 and mysql-libs-5.1.67-1.el6_3.x86_64   Error Summary -------------

mysql-libs를 삭제해서, 다시한번 인스톨한다.

# rpm -e --nodeps mysql-libs # yum install MariaDB-devel MariaDB-client MariaDB-server

무사히 인스톨이 완료했습니까?

 

MariaDB를 START한다.

 

MariaDB를 구동시켜보자.

# /etc/init.d/mysql start Starting MySQL. SUCCESS!

SUCCESS!
root 패스워드를 설정한다.

패스워드는 다시 설정해주세요.

/usr/bin/mysqladmin -u root password 'new-password'

콘솔을 구동시켜보자.

# mysql -uroot -pnew-password Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.0.1-MariaDB MariaDB Server   Copyright (c) 2000, 2012, Oracle, Monty Program Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed MariaDB [mysql]>

[(none)]의 부분에는, 사용하고 있는 데이터베이스명이 들어간다.

 

스토리지엔진을 확인한다.

MariaDB [(none)]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec)

InnoDB가 디폴트입니다.

 

상태를 확인

MariaDB [(none)]> STATUS; -------------- mysql Ver 15.1 Distrib 10.0.1-MariaDB, for Linux (i686) using readline 5.1   Connection id: 11 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.0.1-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 7 min 11 sec   Threads: 1 Questions: 626 Slow queries: 0 Opens: 383 Flush tables: 2 Open tables: 32 Queries per second avg: 1.452 --------------

UNIX socket은 mysql.sock입니다.

 

MariaDB의 설정파일

 

MariaDB은, 클라이언트와 서버에 설정파일을 나누는것이 가능하다。
디폴트로는 my.cnf는 이하와 같은 기술이 있어, .cnf를 포함한다.

# cat /etc/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server]   # # include all files from the config directory # !includedir /etc/my.cnf.d

클라이언트의 설정은 이 설정파일이다.

/etc/my.cnf.d/mysql-clients.cnf

서버의 설정은 이 설정파일이다.

/etc/my.cnf.d/server.cnf

시험삼아,문자코드를 utf8로 한다.

# vi /etc/my.cnf.d/server.cnf [mysqld] character-set-server = utf8

설정이 되면, MariaDB를 다시 구동한다.

# /etc/init.d/mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

확인한다.

$ mysql -uroot -pnew-password Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1 Server version: 10.0.1-MariaDB MariaDB Server   Copyright (c) 2000, 2012, Oracle, Monty Program Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> status; -------------- mysql Ver 15.1 Distrib 10.0.1-MariaDB, for Linux (i686) using readline 5.1   Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.0.1-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 1 min 3 sec   Threads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 58 Queries per second avg: 0.063 --------------

Server/Db characterset가utf8로 되었다。

환경에 맞추어 튜닝해보자.

 

MariaDB로HandlerSocket을 유효로 하는 방법

 

HandlerSocket이란？

간단히 말하면、MySQL 데이터베이스에의 접속을 고속화하기 위한 플러그인이다. MySQL의 SQL서버를 구동시켜、네트워크 통신과 멀티쓰레드처리주변을 치환하는것에의해、InnoDB등의 데이터베이스 엔진의 성능을한계까지 끌어올린다.

이 HandlerSocket이지만, 이미 모바게타운에서 실제로 운용하고 있다.
종래의 MySQL과 memcached의 구성으로 운용하고 있는 부분을, HanderSocket을 조합한 MySQL만의 구성으로 치환했다.

그 결과, MySQL서버의 부하경감, memcached의 부하경감, 네트워크 트래픽감소의 효과가 있었다.
또, 단순히 HandlerSocket로 치환한것만으로 효과가있는것은 아니지만, Application의 CPU부하도 대폭 감소하고 있다.

 

MariaDB는, 디폴트로 HandlerSocket플러그인이 포함되어있다.

명령어로INSTALL PLUGIN구문을 치면, 설정파일.cnf로 기술하는것만으로 HandlerSocket을 유효로 하는것이 가능하다.

 

INSTALL PLUGIN 구문을 실행하는 경우

MariaDB [(none)]> INSTALL PLUGIN handlersocket SONAME 'handlersocket.so';

설정파일.cnf에 기술하는 경우

# vi /etc/my.cnf.d/server.cnf [mysqld] plugin-load=handlersocket.so

플러그인을 확인해보자.

MariaDB [(none)]> SHOW PLUGINS; +----------------------------+----------+--------------------+------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INSERTED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | Aria | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | handlersocket | ACTIVE | DAEMON | handlersocket.so | BSD | +----------------------------+----------+--------------------+------------------+---------+ 42 rows in set (0.01 sec)

최후에  handlersocket라고 출력된다. HandlerSocket가 유효로 되었다.