データベースサーバー構築・管理・運用 MySQL

(最終更新日時:2012-11-20 12:16:21)
オープンソースのリレーショナルデータベース管理システム(RDBMS) MySQL Community Server をKVMによる仮想サーバー上にインストールし、ホストマシンから操作します。
仮想サーバーの構築は、KVMによる仮想サーバーの構築 を参照してください。

以前は、MySQLをソースからインストールしていましたが、サードパーティリポジトリ Remi から新しいバージョンのMySQLパッケージが入手できますので、RPMパッケージによるインストールに変更しました。

Remiリポジトリの追加については、KVMによる仮想サーバーの構築・ゲストマシンのリポジトリ設定 を参照してください。

インストール

RemiリポジトリからMySQLパッケージをインストールします。
※優先順位および対象外パッケージの設定によってMySQL関連のパッケージはRemiリポジトリからインストールされます。
[vm]$ # MySQLマシン上での操作です
[vm]$ sudo yum -y install mysql-server

※ 以下の内容は mysql-server-5.5.28-1.el6.remi.x86_64.rpm を対象に記述しています。
※ 下線部は 2011/11/18 現在の最新バージョンです。

自動更新の抑止

MySQLサーバーのバージョンを上げる場合は、バックアップを取ってから行った方が安全です。
手動でバージョンアップを行うため、パッケージの自動更新を抑止します。
[vm]$ sudo vi /etc/yum.conf
----------(vi ここから)----------
     :
distroverpkg=centos-release

exclude=mysql*

#  This is the default, if you make this bigger yum won't see if the metadata
     :
----------(vi ここまで)----------

MySQLサーバーの起動

定義ファイルの設定とMySQLサーバーの起動を行います。
[vm]$ # MySQLマシン上での操作です
[vm]$ # 定義ファイルの設定を行います
[vm]$ sudo vi /etc/my.cnf
----------(vi ここから)----------
[mysqld]
     :
user=mysql

datadir=/var/mysql/data                                       # データベースディレクトリを変更しています
     :
;performance_schema

character-set-server=utf8                                     # データベースサーバーの文字セットをUTF-8にしています
collation-server=utf8_general_ci                              # データベースサーバーの文字照合順をUTF-8にしています


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[client]
default-character-set=utf8                                    # 既定の文字セットをUTF-8にしています


[mysql]
auto-rehash                                                   # TABでテーブル名やカラム名が補完されるようにしています
default-character-set=utf8                                    # 既定の文字セットをUTF-8にしています
prompt=[\d]> \                                                # プロンプトに操作中のデータベース名を表示するようにしています


[mysqldump]
default-character-set=utf8                                    # 既定の文字セットをUTF-8にしています
----------(vi ここまで)----------

[vm]$ # データベースを初期化します
[vm]$ sudo mysql_install_db --user=mysql --datadir=/var/mysql/data

[vm]$ # MySQLサーバーを起動します
[vm]$ sudo chkconfig mysqld on
[vm]$ sudo service mysqld start

データーベースユーザーの初期設定

MySQLデーターベースの起動を行った後、セキュリティの観点からデーターベースユーザー root のパスワードを設定し、パスワードを持たない初期ユーザーを削除します。
[vm]$ # MySQLマシン上での操作です
[vm]$ # MySQLユーザー root によるホストマシンからのMySQLサーバーの操作を許可します
[vm]$ mysql -u root mysql -e 'GRANT ALL PRIVILEGES ON *.* TO root@gateway.server.localnet WITH GRANT OPTION;'

[vm]$ # MySQLユーザー root のパスワードを設定します
[vm]$ sudo mysqladmin -u root password 'MySQLユーザー root に設定するパスワード'

[vm]$ # パスワードを持たない初期ユーザーを削除します
[vm]$ sudo mysql -u root -p mysql -e "DELETE FROM user WHERE password = '';"
Enter password: 設定したMySQLユーザー root のパスワード

MySQLクライアントのインストール

ホストマシンからMySQLサーバーへリモートでアクセスするため、ホストマシンにMySQLクライアントと開発用パッケージをRemiリポジトリからインストールします。
※優先順位および対象外パッケージの設定によってMySQL関連のパッケージはRemiリポジトリからインストールされます。
$ # ホストマシン上での操作です
$ # MySQLパッケージをインストールします
$ sudo yum -y install mysql mysql-devel

[vm]$ # 定義ファイルの設定を行います
[vm]$ sudo vi /etc/my.cnf
----------(vi ここから)----------
     :
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[mysql]
auto-rehash                                                   # TABでテーブル名やカラム名が補完されるようにしています
default-character-set=utf8                                    # 既定の文字セットをUTF-8にしています
prompt=[\d]> \                                                # プロンプトに操作中のデータベース名を表示するようにしています


[mysqldump]
default-character-set=utf8                                    # 既定の文字セットをUTF-8にしています
----------(vi ここまで)----------

データベースの操作

MySQLマシンにログインして、新規にユーザーデータベースを作成し、アクセス権限を設定します。
$ # ホストマシン上での操作です
$ # MySQLマシンのデータベースにMySQLユーザー root でログインします
$ mysql -h actmysql.server.localnet -u root -p
Enter password: MySQLユーザー root のパスワード
       :
-- 例として、ユーザーデータベース testdb を作成します
[(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

-- testdb を管理するMySQLユーザーとして testuser を作成します
[(none)]> GRANT ALL ON testdb.*
    -> TO testuser@localhost
    -> IDENTIFIED BY 'testuser@localhost に設定するパスワード';
Query OK, 0 rows affected (0.00 sec)

-- リモートでデータベースを操作する場合は、リモートクライアント毎に管理ユーザーやアクセスユーザーを登録する必要があります
-- リモートクライアントの指定は以下の方法で行います
--   ホスト名       : (例) gateway.server.localnet
--   IP アドレス    : (例) 192.168.201.11
--                         192.168.201.0/255.255.255.0
--                  ただし 192.168.201.0/255.255.255.240 は NG
--                  マスク可能なアドレス長は 8, 16, 24, 32 bit のみになります
--   ワイルドカード : (例) "%.server.localnet"
--                         "192.168.201.%"
--                  ワイルドカード文字等の記号文字を使用する場合は、引用符でくくります
-- 以下は、ホスト名 gateway.server.localnet を指定しています
[(none)]> GRANT ALL ON testdb.*
    -> TO testuser@gateway.server.localnet
    -> IDENTIFIED BY 'testuser@gateway.server.localnet に設定するパスワード';
Query OK, 0 rows affected (0.00 sec)

[(none)]> quit
Bye

作成したユーザーデータベースをホストマシンから操作します。
$ # ホストマシン上での操作です
$ mysql -h actmysql.server.localnet -u testuser -p testdb
Enter password: MySQLユーザー testuser のパスワード
       :
[testdb]> ... SQL etc. ...
       :
[testdb]> quit

データベースのバックアップをホストマシンから行います。
バックアップファイルはホストマシンに作成されます。
$ # ホストマシン上での操作です
$ # 全データベースを backup.sql にフルバックアップします
$ mysqldump -h actmysql.server.localnet -A --flush-privileges -u root -p > backup.sql
Enter password: MySQLユーザー root のパスワード

$ # データベース testdbbackup_testdb.sql にバックアップします
$ mysqldump -h actmysql.server.localnet -B --add-drop-database -u root -p testdb > backup_testdb.sql
Enter password: MySQLユーザー root のパスワード

ホストマシンのバックアップファイルからデータベースの復元(リストア)を行います。
復元(リストア)はホストマシンから行います。
$ # ホストマシン上での操作です
$ # バックアップファイル backup.sql から復元します
$ mysql -h actmysql.server.localnet -u root -p < backup.sql
Enter password: MySQLユーザー root のパスワード

同一サーバー内にバックアップ用データベースを新たに作成し、そのデータベースにバックアップします。
$ # ホストマシン上での操作です
$ mysql -h actmysql.server.localnet -u root -p
Enter password: MySQLユーザー root のパスワード
       :
-- バックアップ用データベース testdbback を作成します
[(none)]> CREATE DATABASE testdbback;
Query OK, 1 row affected (0.00 sec)

-- testdbback の管理ユーザーを既存の testuser にします
[(none)]> GRANT ALL ON testdb.*
    -> TO testuser@localhost;
Query OK, 0 rows affected (0.00 sec)

[(none)]> quit
Bye

$ # データベース testdbtestdbback にバックアップします
$ # 異なるデータベースにリストアしますので -B --add-drop-database オプションは指定しません
$ mysqldump -h actmysql.server.localnet -u root -p testdb > backup_testdb.sql
Enter password: MySQL ユーザー root のパスワード

$ mysql -h actmysql.server.localnet -u root -p testdbback < backup_testdb.sql
Enter password: MySQL ユーザー root のパスワード

$ rm backup_testdb.sql

メジャーバージョンアップ

インストール済MySQLのメジャーバージョンを上げる場合は、以下の手順で行います。
[vm]$ # MySQLマシン上での操作です
[vm]$ # データベースのフルバックアップを行います
[vm]$ mysqldump -A --flush-privileges -u root -p > バックアップファイル名
Enter password: MySQLユーザー root のパスワード

[vm]$ # サービスを停止します
[vm]$ sudo service mysql stop

[vm]$ # 念のためデータベースフォルダごとバックアップします
[vm]$ sudo mv /var/mysql/data /var/mysql/backup

[vm]$ # バージョンアップ版のRPMパッケージをインストールします
[vm]$ sudo yum -y update mysql-server

[vm]$ # データベースを初期化します
[vm]$ sudo mysql_install_db --user=mysql --datadir=/var/mysql/data

[vm]$ # MySQLサーバーを起動します
[vm]$ sudo service mysql start

[vm]$ # フルバックアップのリストアを行います
[vm]$ mysql -u root < バックアップファイル名

フィードバック

記事の内容についてのご質問、ご指摘、その他ご意見等は、Weblog/Twitter にて受け付けております。

System House ACT Weblog 内記事 : データベースサーバー MySQL の構築・管理・運用

トラックバックまたはコメントにてお寄せください。

Twitter では .@systemact 宛につぶやいてください。
また、こちら から、この記事についてつぶやくことができます。
a System House to build an Accounting system by the Computer Technology