更新日: 2013年7月27日
CentOS & Linux

DB サーバー（MySQL）のインストールと設定〜 CentOS6

MySQL は CentOS 標準リポジトリのバージョンが少し古いので（5.1）、remi リポジトリからインストールします。

【お知らせ】英単語を画像イメージで楽に暗記できる辞書サイトを作りました。英語学習中の方は、ぜひご利用ください！

画像付き英語辞書 Imagict | 英単語をイメージで暗記
【開発記録】
英単語を画像イメージで暗記できる英語辞書サービスを作って公開しました

このエントリーは、CentOS 6.4 インストール～設定手順の目次の一部です。

MySQL をインストール


# yum info mysql --enablerepo=remi --disablerepo=base,updates
Loaded plugins: fastestmirror, priorities, refresh-packagekit, security
Loading mirror speeds from cached hostfile
 * extras: www.ftp.ne.jp
 * remi: remi.mirrors.arminco.com
Available Packages
Name        : mysql
Arch        : i686
Version     : 5.5.30
Release     : 1.el6.remi
Size        : 5.8 M
Repo        : remi
Summary     : MySQL client programs and shared libraries
URL         : http://www.mysql.com
License     : GPLv2 with exceptions
Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a
            : client/server implementation consisting of a server daemon (mysqld)
            : and many different client programs and libraries. The base package
            : contains the standard MySQL client programs and generic MySQL files.

# yum info mysql --enablerepo=remi --disablerepo=base,updates

Loaded plugins: fastestmirror, priorities, refresh-packagekit, security

Loading mirror speeds from cached hostfile

* extras: www.ftp.ne.jp

* remi: remi.mirrors.arminco.com

Available Packages

Name : mysql

Arch : i686

Version : 5.5.30

Release : 1.el6.remi

Size : 5.8 M

Repo : remi

Summary : MySQL client programs and shared libraries

URL : http://www.mysql.com

License : GPLv2 with exceptions

Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL is a

: client/server implementation consisting of a server daemon (mysqld)

: and many different client programs and libraries. The base package

: contains the standard MySQL client programs and generic MySQL files.

remiリポジトリからMySQL5.5をインストールする。


# yum -y --enablerepo=remi --disablerepo=base,updates install mysql mysql-server mysql-devel

# yum -y --enablerepo=remi --disablerepo=base,updates install mysql mysql-server mysql-devel

で、進めようとしたら、requireでいくつか怒られたので、必要なパッケージをインストール。


# yum -y install perl-DBD-MySQL openssl-devel

# yum -y install perl-DBD-MySQL openssl-devel

改めて、MySQLをインストール。


# yum -y --enablerepo=remi --disablerepo=base,updates install mysql mysql-server mysql-devel

# yum -y --enablerepo=remi --disablerepo=base,updates install mysql mysql-server mysql-devel

設定ファイル /etc/my.cnf を編集して起動

設定ファイル /etc/my.cnf を編集。オリジナルのバックアップをとった後編集。設定ファイルいじる時は必ずオリジナルのバックアップを取っておいたほうが良い。


# cp /etc/my.cnf /etc/my.cnf.org
# vi /etc/my.cnf
[mysqld]
character-set-server = utf8
skip-character-set-client-handshake

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

# cp /etc/my.cnf /etc/my.cnf.org

# vi /etc/my.cnf

[mysqld]

character-set-server = utf8

skip-character-set-client-handshake

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqldump]

default-character-set=utf8

文字コードに関する上記を追加します。

自動起動を設定して、MySQL起動。


# chkconfig --list | grep mysql
mysqld          0:off   1:off   2:off   3:off   4:off   5:off   6:off
# chkconfig mysqld on
# service mysqld start
MySQL データベースを初期化中:  Installing MySQL system tables...
OK
Filling help tables...
OK
...

# chkconfig --list | grep mysql

mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off

# chkconfig mysqld on

# service mysqld start

MySQL データベースを初期化中: Installing MySQL system tables...

Filling help tables...

...

対話モードで初期設定

続いて対話形式にてMySQLのrootなどセキュリティに関する初期設定を行う。


# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

# ここから対話モードで設定を行なっていく。
# 初期設定ではrootパスワードは空なのでそのままEnterを押す。
Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

# rootユーザーにパスワードを設定するか？ Y（Yes）
Set root password? [Y/n] Y
# ここでパスワードを設定
New password:
# パスワード確認のためもう一回入力
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

# 匿名ユーザーを削除するか？ Y（Yes）
Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

# rootによるリモートからのログインを拒否するか？ Y（Yes）
Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

# テスト用データベースとそれへのアクセスを削除するか？ Y（Yes）
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

# 権限テーブルを読み込み直すか？ Y（Yes）
Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current

password for the root user. If you've just installed MySQL, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

# ここから対話モードで設定を行なっていく。

# 初期設定ではrootパスワードは空なのでそのままEnterを押す。

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

# rootユーザーにパスワードを設定するか？ Y（Yes）

Set root password? [Y/n] Y

# ここでパスワードを設定

New password:

# パスワード確認のためもう一回入力

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

# 匿名ユーザーを削除するか？ Y（Yes）

Remove anonymous users? [Y/n] Y

... Success!

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

# rootによるリモートからのログインを拒否するか？ Y（Yes）

Disallow root login remotely? [Y/n] Y

... Success!

By default, MySQL comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

# テスト用データベースとそれへのアクセスを削除するか？ Y（Yes）

Remove test database and access to it? [Y/n] Y

- Dropping test database...

... Success!

- Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

# 権限テーブルを読み込み直すか？ Y（Yes）

Reload privilege tables now? [Y/n] Y

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

これで対話形式での初期設定は終了。

MySQL データベースの中身を確認

続いて root ユーザーでログインしてデータベースの中身を確認してみます。


# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

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

3 rows in set (0.00 sec)

testデータベースはちゃんと削除されています。

続いて匿名ユーザーの確認。


mysql> use mysql;
mysql> SELECT user, password, host FROM user;
+------+-------------------------------------------+-----------+
| user | password                                  | host      |
+------+-------------------------------------------+-----------+
| root | *DA9DA891B21EA3EB77B0620B8DB3ACB889D947EB | localhost |
| root | *DA9DA891B21EA3EB77B0620B8DB3ACB889D947EB | 127.0.0.1 |
| root | *DA9DA891B21EA3EB77B0620B8DB3ACB889D947EB | ::1       |
+------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)