CGI::Applicationメモ

CGI::Applicationを使って、MySQLに登録されているデータに対して、「追加」、「削除」、「更新」、「一覧表示」する
シンプルなWebアプリケーションを作った時のメモです。

↓を参考にしました。
http://codezine.jp/article/detail/449
http://rt.air-nifty.com/pods/CGI/Application.html

★テーブルの構成

mysql> DESC music;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(4)       | NO   | PRI | NULL    |       |
| name     | varchar(100) | NO   |     | NULL    |       |
| title    | varchar(100) | NO   |     | NULL    |       |
| comments | text         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM music;
+----+-------------------------------------+----------------------+----------+
| id | name                                | title                | comments |
+----+-------------------------------------+----------------------+----------+
|  1 | Ramones                             | It's Alive           | US       |
|  2 | Buzzcocks                           | Singles Going Steady | UK       |
|  3 | Vibrators                           | Pure Mania           | UK       |
|  4 | Stiff Little Fingers                | Inflammable Material | UK       |
|  5 | The Damned                          | Damned Damned Damned | UK       |
|  6 | Generation X                        | Generation X         | UK       |
|  7 | X Ray Spex                          | Germfree Adolescents | UK       |
|  8 | Johnny Thunders & the Heartbreakers | L.A.M.F              | US       |
|  9 | Slaughter & The Dogs                | Do It Dog Style      | UK       |
| 10 | The Undertones                      | The Undertones       | UK       |
+----+-------------------------------------+----------------------+----------+
10 rows in set (0.00 sec)

mysql>

テーブル作成

mysql> CREATE TABLE music(
    -> id int(4) PRIMARY KEY NOT NULL,
    -> name varchar(100) NOT NULL,
    -> title varchar(100) NOT NULL,
    -> comments text
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

行の追加

mysql> INSERT INTO music VALUES ('1','Ramones', "It's Alive",'US');
mysql> INSERT INTO music VALUES ('2','Buzzcocks', 'Singles Going Steady','UK');
mysql> INSERT INTO music VALUES ('3','Vibrators', 'Pure Mania','UK');
mysql> INSERT INTO music VALUES ('4','Stiff Little Fingers', 'Inflammable Material','UK');
mysql> INSERT INTO music VALUES ('5','The Damned', 'Damned Damned Damned','UK');
mysql> INSERT INTO music VALUES ('6','Generation X', 'Generation X','UK');
mysql> INSERT INTO music VALUES ('7','X Ray Spex', 'Germfree Adolescents','UK');
mysql> INSERT INTO music VALUES ('8','Johnny Thunders & the Heartbreakers','L.A.M.F','US');
mysql> INSERT INTO music VALUES ('9','Slaughter & The Dogs', 'Do It Dog Style','UK');
mysql> INSERT INTO music VALUES ('10','The Undertones', 'The Undertones','UK');

★ファイルの構成

[root@ha-01 WebApp]# tree
.
|-- WebApp.pm
|-- dbapp.pl
|-- delete_input.tmpl
|-- insert_input.tmpl
|-- update_input.tmpl
`-- view.tmpl

0 directories, 6 files
[root@ha-01 WebApp]#

dbapp.pl

#!/usr/bin/perl
use strict;
use warnings;
use WebApp;

my $app = WebApp->new(
    PARAMS => {
        db_dsn => 'dbi:mysql:db1:localhost;mysql_read_default_file=/etc/my.cnf',
        db_user => 'user1',
        db_pass => 'abcd1234'
    }
);
$app->run();

WebApp.pm

package WebApp;
use strict;
use warnings;
use base qw(CGI::Application);
use CGI::Application::Plugin::DBH qw(dbh_config dbh);
use CGI::Application::Plugin::Forward;

sub cgiapp_init {
    my $self = shift;
    $self->query->charset('UTF-8');
    $self->dbh_config(
            $self->param('db_dsn'),
            $self->param('db_user'),
            $self->param('db_pass')
    );
}

sub setup {
    my $self = shift;

    $self->error_mode('error');
    $self->start_mode('view');
    $self->run_modes(
        'view'          => 'view',
        'insert_input'  => 'insert_input',
        'insert_finish' => 'insert_finish',
        'update_input'  => 'update_input',
        'update_finish' => 'update_finish',
        'delete_input'  => 'delete_input',
        'delete_finish' => 'delete_finish',
    );
}

sub error {
    my($self, $err) = @_;

    return $err;
}

sub view {
    my($self, $err) = @_;

    my $sth = $self->dbh->prepare('SELECT * FROM music')
                or die ($DBI::errstr);
    $sth->execute() or die ($DBI::errstr);

    my @rows = ();

    while (my $hash_ref = $sth->fetchrow_hashref()) {
        push (@rows, $hash_ref);
    }

    my $tmpl = $self->load_tmpl('view.tmpl');
    $tmpl->param(rows => \@rows, %$err);
    return $tmpl->output;
}

sub insert_input {
    my($self, $err) = @_;

    my $tmpl = $self->load_tmpl('insert_input.tmpl');
    return $tmpl->output;
}

sub insert_finish {
    my $self = shift;

    my $id =  $self->query->param('id');
    my $title = $self->query->param('title');
    my $name = $self->query->param('name');
    my $comments= $self->query->param('comments');
    my $query = "INSERT music VALUES('$id' , '$title' , '$name', '$comments')";

    $self->dbh->do($query) or die ($DBI::errstr);

    return $self->forward('view', { 'info' => '追加しました'});
}

sub update_input {
    my ($self, $err) = @_;

    my $hash_ref = $self->dbh->selectrow_hashref(
    'SELECT * FROM music WHERE id = ? LIMIT 1',undef,
    $self->query->param('id')
    ) or die ($DBI::errstr);

    my $tmpl = $self->load_tmpl('update_input.tmpl');
    $tmpl->param(%$hash_ref);
    return $tmpl->output;
}

sub update_finish {
    my $self = shift;

    $self->dbh->do(
    'UPDATE music SET title = ?, name = ?, comments = ? WHERE id = ?',
    undef,
    $self->query->param('title'),
    $self->query->param('name'),
    $self->query->param('comments'),
    $self->query->param('id'),
    ) or die ($DBI::errstr);

    return $self->forward('view', {'info' => '更新完了'});
}

sub delete_input {
    my ($self, $err) = @_;

    my $hash_ref = $self->dbh->selectrow_hashref(
    'SELECT * FROM music WHERE id = ? LIMIT 1',undef,
    $self->query->param('id')
    ) or die ($DBI::errstr);

    my $tmpl = $self->load_tmpl('delete_input.tmpl');
    $tmpl->param(%$hash_ref);
    return $tmpl->output;
}

sub delete_finish {
    my $self = shift;

    $self->dbh->do(
    'DELETE FROM music WHERE id = ?',
    undef,
    $self->query->param('id')
    ) or die ($DBI::errstr);

    return $self->forward('view' , {'info' => '削除完了'});
}

1;

view.tmpl

<html>
<head>
<title>WebApp</title>
</head>
<body>
<h1>70s punk bands</h1>
<TMPL_IF NAME="info"><p class="info"><TMPL_VAR NAME="info"></p></TMPL_IF>
<table border="1">
<tr>
    <td>ID</td>
    <td>タイトル</td>
    <td>アーティスト</td>
    <td>コメント</td>
    <td>編集</td>
    <td>削除</td>
</tr>
<TMPL_LOOP NAME="rows">
<tr>
<td><TMPL_VAR NAME="id" ></td>
<td><TMPL_VAR NAME="title" ></td>
<td><TMPL_VAR NAME="name" ></td>
<td><TMPL_VAR NAME="comments" ></td>
<td>
<form action="dbapp.pl">
<input type="hidden" name="rm" value="update_input">
<input type="hidden" name="id" value="<tmpl_var name="id">">
<input type="submit" value="編集">
</form>
</td>
<td>
<form action="dbapp.pl">
<input type="hidden" name="rm" value="delete_input">
<input type="hidden" name="id" value="<tmpl_var name="id">">
<input type="submit" value="削除">
</form>
</td>
</tr>
</TMPL_LOOP>
</table>
<form action="dbapp.pl">
<input type="hidden" name="rm" value="insert_input">
<input type="submit" value="追加">
</form>
</body>
</html>

insert_input.tmpl

<html>
<head>
<title>WebApp</title>
</head>
<h1>追加画面</h1>
<body>
<form action="dbapp.pl" method="post">
<input type="hidden" name="rm" value="insert_finish">
<table border="1">
<tr>
    <td>ID</td>
    <td>タイトル</td>
    <td>アーティスト</td>
    <td>コメント</td>
</tr>
<tr>
<td><input type="text" name="id" value=""size="10"></td>
<td><input type="text" name="name" size="10"></td>
<td><input type="text" name="title" size="10"></td>
<td><input type="text" name="comments" size="10"></td>
</tr>
</table>
<input type="submit" value="完了">
<input type="reset" value="取消">
</form>
<p><a href="dbapp.pl">戻る</a></p>
</body>
</html>

update_input.tmpl

<html>
<head>
<title>WebApp</title>
</head>
<body>
<h1>編集画面</h1>
<form action="dbapp.pl" method="post">
<input type="hidden" name="rm" value="update_finish">
ID:             <input type="text" name="id" value="<TMPL_VAR NAME="id">">
タイトル:       <input type="text" name="title" value="<TMPL_VAR NAME="title">">
アーティスト:   <input type="text" name="name" value="<TMPL_VAR NAME="name">">
コメント:       <input type="text" name="comments" value="<TMPL_VAR NAME="comments">">
</tr>
<br>
<input type="submit" value="完了">
<input type="reset" value="取消">
</form>
<p><a href="dbapp.pl">戻る</a></p>
</body>
</html>

delete_input.tmpl

<html>
<head>
<title>WebApp</title>
</head>
<body>
<h1>削除確認画面</h1>
<table border="1">
<tr>
    <td>ID</td>
    <td>タイトル</td>
    <td>アーティスト</td>
    <td>コメント</td>
</tr>
<tr>
<td><TMPL_VAR NAME="id" ></td>
<td><TMPL_VAR NAME="title" ></td>
<td><TMPL_VAR NAME="name" ></td>
<td><TMPL_VAR NAME="comments" ></td>
<form action="dbapp.pl" method="post">
<input type="hidden" name="rm" value="delete_finish">
<input type="hidden" name="id" value="<TMPL_VAR NAME="id">">
</tr>
</table>
<br>
<input type="submit" value="確定">
</form>
<p><a href="dbapp.pl">戻る</a></p>
</body>
</html>

HTML::TemplateとDBIでテーブル表示

★選択したフィールドを表示する

用意するファイル

[root@ha-01 HTML]# tree
.
|-- sample_ht.pl
`-- template.tmpl

0 directories, 2 files
[root@ha-01 HTML]#

sample_ht.pl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use HTML::Template;

my $hostname = "localhost";
my $user = "user1";
my $passwd = "abcd1234";
my $databse = "db1";

my $tmpl_file = './template.tmpl';
my $template = new HTML::Template(filename => $tmpl_file);

my $dsn = "dbi:mysql:database=$databse;host=$hostname;mysql_read_default_file=/etc/my.cnf";
my $dbh = DBI->connect($dsn,$user,$passwd,
                        { 'RaiseError' =>1, 'PrintError' => 0});

my $sth = $dbh->prepare("SELECT postal_code, prefecture,city,town FROM tokyo_postal_code")
            or die $DBI::errstr;

$sth->execute() or die $DBI::errstr;

my @array;

while (my @row = $sth->fetchrow_array) {
    push(@array,\@row);
}

my @table;

foreach my $column (@array) {
    my %hash = ( postal_code => $column->[0],
                 prefecture  => $column->[1],
                 city        => $column->[2],
                 town        => $column->[3] );
    push (@table, \%hash);
}
$dbh->disconnect;

print "Content-type: text/html\n\n";
$template->param(postal => \@table);
print $template->output;

template.tmpl

<table BORDER="1" >
<TMPL_LOOP NAME=postal>
<td><TMPL_VAR NAME=postal_code> </td>
<td><TMPL_VAR NAME=prefecture> </td>
<td><TMPL_VAR NAME=city></td>
<td><TMPL_VAR NAME=town></td>
</tr>
</TMPL_LOOP>
</talbe>

Perl でMySQL接続メモ

★前回作ったサンプルデータ（http://d.hatena.ne.jp/mk_1211/20120929/1348880579）をもとに、PerlのDBIとDBD::mysqlモジュールを使ってみました。

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $hostname = "localhost";
my $user     = "user1";
my $passwd   = "abcd1234";
my $database = "db1";

my $postal = shift;
$postal =~ s/-//g;

die "Usage: $0 POSTAL_CODE" unless defined $postal;

#データベースへ接続
my $dsn = "DBI:mysql:database=$database;host=$hostname;mysql_read_default_file=/etc/my.cnf";

my $dbh = DBI->connect($dsn, $user, $passwd,
                      { 'RaiseError' => 1, 'PrintError' => 0, 'AutoCommit' => 1});

eval {

#行の選択
my $sth = $dbh->prepare("SELECT * FROM tokyo_postal_code WHERE postal_code = ?");

$sth->execute("$postal");

#データの取得
    while (my $arr_ref = $sth->fetchrow_arrayref) {
        my $prefecutre = $$arr_ref[6];
        my $city = $$arr_ref[7];
        my $town = $$arr_ref[8];
        $town =~ s/以下に掲載がない場合|（次のビルを除く）//g;
        print $prefecutre.$city.$town ."\n";
    }
};
die "Error: $@" if($@);
$dbh->disconnect();

★実行結果

引数に東京の郵便番号を指定すると、住所がでる感じです。

[root@ha-01 Perl_Script]# ./postal_code_get.pl 100-0002
東京都千代田区皇居外苑
[root@ha-01 Perl_Script]#

MySQL サンプルデータの作成

★サンプルデータとして、郵便番号をインポートした時のメモ

１．ファイルのダウンロードと解凍

[root@ha-01 tmp]# wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/13tokyo.zip

[root@ha-01 tmp]# unzip 13tokyo.zip

２．改行コードをCRLFからLFへ変換

[root@ha-01 tmp]# nkf -Lu 13TOKYO.CSV >tokyo.csv

３．文字コードをShift-JISからUTF-8へ変換

[root@ha-01 tmp]# nkf -w --overwrite tokyo.csv

４．MySQLサーバへ接続

[root@ha-01 tmp]# mysql -u root -p

５．データベースの選択

mysql> USE db1

６．テーブル作成

mysql> CREATE TABLE tokyo_postal_code (
    -> public_code char(5) NOT NULL,
    -> old_postal char(5) NOT NULL,
    -> postal_code char(7) NOT NULL,
    -> prefecture_kana varchar(10),
    -> city_kana varchar(100),
    -> town_kana varchar(100),
    -> prefecture varchar(10),
    -> city varchar(100),
    -> town varchar(200),
    -> flag1 int,
    -> flag2 int,
    -> flag3 int,
    -> flag4 int,
    -> flag5 int,
    -> flag6 int
    -> );
Query OK, 0 rows affected (0.00 sec)

７．csvファイルのインポートと確認

mysql> LOAD DATA INFILE '/tmp/tokyo.csv' INTO TABLE tokyo_postal_code
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

mysql> SELECT * FROM tokyo_postal_code LIMIT 1 \G
*************************** 1. row ***************************
    public_code: 13101
     old_postal: 100
    postal_code: 1000000
prefecture_kana: トウキョウト
      city_kana: チヨダク
      town_kana: イカニケイサイガナイバアイ
     prefecture: 東京都
           city: 千代田区
           town: 以下に掲載がない場合
          flag1: 0
          flag2: 0
          flag3: 0
          flag4: 0
          flag5: 0
          flag6: 0
1 row in set (0.00 sec)

mysql>

MySQL5.5 トランザクションとAUTO COMMITモードのメモ

MySQLはデフォルトで、1つのSQL文を実行した後すぐにコミットを行う。このモードのことをAUTO COMMITモードと呼び、
デフォルトで「1」が設定されており、有効になっている。

★AUTO COMMITモードの確認

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql>

AUTO COMMITモードが有効になっている状態でトランザクションを開始する際は、BEGIN文もしくはSTART TRANSACTION文が必要になる。
トランザクションの終了は、COMMIT文かROLLBACK文を実行する。

★トランザクションの確認（AUTO COMMITは「1」）

mysql> SELECT * FROM list WHERE id=1;
+----+-----------+-------------+
| id | name      | title       |
+----+-----------+-------------+
|  1 | Discharge | Never Again |
+----+-----------+-------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE list SET name='Amy Winehouse', title='Back To Black' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM list WHERE id=1;
+----+---------------+---------------+
| id | name          | title         |
+----+---------------+---------------+
|  1 | Amy Winehouse | Back To Black |
+----+---------------+---------------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM list WHERE id=1;
+----+-----------+-------------+
| id | name      | title       |
+----+-----------+-------------+
|  1 | Discharge | Never Again |
+----+-----------+-------------+
1 row in set (0.00 sec)

mysql>

★AUTO COMMITモードを変更するには、SET AUTOCOMMIT文を実行する

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

AUTO COMMITモードが「0」のときは、BEGIN文またはSTART TRANSACTION文がなくても自動的にトランザクションが始まる。
COMMIT文またはROLLBACK文を実行すると、それまでのトランザクションが終了し自動的に次の新しいトランザクションが始まる。

★トランザクションの確認（AUTO COMMITは「0」）

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM list;
+----+------------+--------------+
| id | name       | title        |
+----+------------+--------------+
|  1 | Discharge  | Never Again  |
|  2 | Aphex Twin | Ambient Work |
|  3 | Bjork      | Debut        |
+----+------------+--------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO list (name,title) VALUES ('Motorhead','Ace of Spades');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM list;
+----+------------+---------------+
| id | name       | title         |
+----+------------+---------------+
|  1 | Discharge  | Never Again   |
|  2 | Aphex Twin | Ambient Work  |
|  3 | Bjork      | Debut         |
|  5 | Motorhead  | Ace of Spades |
+----+------------+---------------+
4 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM list;
+----+------------+--------------+
| id | name       | title        |
+----+------------+--------------+
|  1 | Discharge  | Never Again  |
|  2 | Aphex Twin | Ambient Work |
|  3 | Bjork      | Debut        |
+----+------------+--------------+
3 rows in set (0.00 sec)

mysql>

MySQL 5.5インストールとSQL文のメモ

■MySQL 5.5のインストール

１．以下のURLより、rpmパッケージをダウンロード
http://dev.mysql.com/downloads/mysql/#downloads

MySQL-server-5.5.27-1.linux2.6.x86_64.rpm
MySQL-client-5.5.27-1.linux2.6.x86_64.rpm
MySQL-devel-5.5.27-1.linux2.6.x86_64.rpm
MySQL-shared-5.5.27-1.linux2.6.x86_64.rpm

２．rpmパッケージをインストール

[root@ha-01 tmp]# rpm -ivh MySQL-client-5.5.27-1.linux2.6.x86_64.rpm

[root@ha-01 tmp]# rpm -ivh MySQL-server-5.5.27-1.linux2.6.x86_64.rpm

[root@ha-01 tmp]# rpm -ivh MySQL-devel-5.5.27-1.linux2.6.x86_64.rpm

[root@ha-01 tmp]# rpm -ivh MySQL-shared-5.5.27-1.linux2.6.x86_64.rpm

３．MySQLサーバーの起動

[root@ha-01 tmp]# /etc/init.d/mysql start

４．初期設定

[root@ha-01 tmp]# 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.

Enter current password for root (enter for none):           ← 「Enter」
OK, successfully used password, moving on...

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

Set root password? [Y/n] Y                                  ← rootユーザーのパスワード設定
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.

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.

Disallow root login remotely? [Y/n] Y                       ← リモートからrootユーザーへのアクセスを拒否する
 ... 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.

Remove test database and access to it? [Y/n] Y              ← testデータベースの削除
 - 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.

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!


[root@ha-01 tmp]#

５．MySQL Serverへの接続

[root@ha-01 tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

６．MySQL Serverへの切断

mysql> quit

■SQL文の基本操作

データベースの作成

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

データベースの選択

mysql> USE db1;

データベースの一覧表示

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

テーブルの作成

mysql> CREATE TABLE list (id INT , dep INT, name CHAR(30));

フィールドの構造を表示

mysql> DESC list;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| dep   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.04 sec)

フィールドの削除

mysql> ALTER TABLE list DROP dep;

mysql> DESC list;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

フィールドの追加

mysql> ALTER TABLE list ADD title CHAR(30) AFTER name;

mysql> DESC list;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| title | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

データの挿入

mysql> INSERT INTO list (id,name,title) VALUES (1, 'Discharge','Never Again');

データの検索

mysql> SELECT * FROM list;
+------+-----------+-------------+
| id   | name      | title       |
+------+-----------+-------------+
|    1 | Discharge | Never Again |
+------+-----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT name,title FROM list;
+-----------+-------------+
| name      | title       |
+-----------+-------------+
| Discharge | Never Again |
+-----------+-------------+
1 row in set (0.00 sec)

データの削除

mysql> DELETE FROM list WHERE id=1;

テーブルの削除

mysql> DROP TABLE list;

AUTO INCREMENTの使用

mysql> CREATE TABLE list ( id INT PRIMARY KEY AUTO_INCREMENT , name CHAR(30) NOT NULL, title CHAR(30) NOT NULL );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC list;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(30) | NO   |     | NULL    |                |
| title | char(30) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO list (name,title) VALUES ('Discharge','Never Again'),('Aphex Twin','Ambient Work'),('Bjork','Debut');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM list;
+----+------------+--------------+
| id | name       | title        |
+----+------------+--------------+
|  1 | Discharge  | Never Again  |
|  2 | Aphex Twin | Ambient Work |
|  3 | Bjork      | Debut        |
+----+------------+--------------+
3 rows in set (0.00 sec)

mysql>

管理者権限を持つユーザーの作成

mysql> GRANT ALL PRIVILEGES ON *.* TO user1@localhost IDENTIFIED BY 'xxxxxx';
mysql> GRANT ALL PRIVILEGES ON *.* TO user1@'%' IDENTIFIED BY 'xxxxxx';
mysql> FLUSH PRIVILEGES;

PXEブート〜Puppetマスターの環境設定メモ

★HAクラスタを想定した設定で、共有するディスクのパスは「/SHARE/data/」とする

■TFTPサーバの設定
１．TFTPサーバのインストール確認

[root@ha-01 ~]# rpm -qa | grep tftp
tftp-server-0.49-2.el5.centos
[root@ha-01 ~]#

２．tftpの設定

[root@ha-01 ~]# cat /etc/xinetd.d/tftp
service tftp
{
        socket_type             = dgram
        protocol                = udp
        wait                    = yes
        user                    = root
        server                  = /usr/sbin/in.tftpd
        server_args             = -s /SHARE/data/tftpboot
        disable                 = no
        per_source              = 11
        cps                     = 100 2
        flags                   = IPv4
}
[root@ha-01 ~]#

■DHCPサーバの設定
３．DHCPサーバのインストール確認

[root@ha-01 ~]# rpm -qa | grep dhcp | grep -v client
dhcp-3.0.5-23.el5_5.2
[root@ha-01 ~]#

４．dhcpd.confの設定

[root@ha-01 ~]# cat /etc/dhcpd.conf
ddns-update-style none;
ignore client-updates;

subnet 192.168.11.0 netmask 255.255.255.0 {

        option routers                  192.168.11.1;
        option subnet-mask              255.255.255.0;
        filename                        "/pxeboot/pxelinux.0";
        next-server                     192.168.11.101;
        range dynamic-bootp 192.168.11.150 192.168.11.254;
        default-lease-time 21600;
        max-lease-time 43200;
}
[root@ha-01 ~]#

■HTTPDの設定
５．httpdインストール確認

[root@ha-01 ~]# rpm -qa | grep httpd
httpd-2.2.3-45.el5.centos
[root@ha-01 ~]#

６．httpd.confで指定されるドキュメントルートを、共有するディスクのパスへ変更

[root@ha-01 ~]# sed -i 's%/var/www/html%/SHARE/data/netinst%' /etc/httpd/conf/httpd.conf

■OSメディアのマウントと配置
７．CentOSメディア配置ディレクトリの作成とコピー

[root@ha-01 ~]# mkdir -p /SHARE/data/netinst/centos5.6-server-x86_64

[root@ha-01 ~]# mount -t iso9660 -o ro  /dev/cdrom  /mnt

[root@ha-01 ~]# cp -a /mnt/* /SHARE/data/netinst/centos5.6-server-x86_64/

[root@ha-01 ~]# umount /mnt

■Puppetクライアント用の資材配置とYUMリポジトリの作成
８．Puppet関連パッケージ配置ディレクトリの作成

[root@ha-01 ~]# mkdir -p /SHARE/data/netinst/puppet_setup

９．Puppet関連パッケージを配置

[root@ha-01 ~]# ll /SHARE/data/netinst/puppet_setup
合計 1256
-rw-r--r-- 1 root root 336912  7月 26 01:28 augeas-libs-0.8.1-2.el5.x86_64.rpm
-rw-r--r-- 1 root root  62594  7月 26 01:27 facter-1.5.9-1.el5.noarch.rpm
-rw-r--r-- 1 root root 803124  7月 26 01:27 puppet-0.25.5-1.el5.noarch.rpm
-rw-r--r-- 1 root root  21808  7月 26 01:27 puppet-server-0.25.5-1.el5.noarch.rpm
-rw-r--r-- 1 root root  21440  7月 26 01:29 ruby-augeas-0.4.1-1.el5.x86_64.rpm
-rw-r--r-- 1 root root   9856  7月 26 01:29 ruby-shadow-1.4.1-7.el5.x86_64.rpm
[root@ha-01 ~]#

１０．YUMリポジトリの作成

[root@ha-01 ~]# createrepo -v /SHARE/data/netinst/puppet_setup/

■PXEブート時に使用する各ファイルを配置
１１．ブートローダ、vmlinuz、initrdを配置

[root@ha-01 ~]# cp -p /usr/lib/syslinux/pxelinux.0 /SHARE/data/tftpboot/pxeboot/

[root@ha-01 ~]# cp -a /SHARE/data/netinst/centos5.6-server-x86_64/images/pxeboot/vmlinuz \
/SHARE/data/tftpboot/pxeboot/

[root@ha-01 ~]# cp -a /SHARE/data/netinst/centos5.6-server-x86_64/images/pxeboot/initrd.img \
/SHARE/data/tftpboot/pxeboot/

１２．PXEブート設定ファイルを作成

[root@ha-01 ~]# mkdir -p /SHARE/data/tftpboot/pxeboot/pxelinux.cfg

[root@ha-01 ~]# cat /SHARE/data/tftpboot/pxeboot/pxelinux.cfg/default
prompt 0
timeout 20

default hadoop
label hadoop
  kernel vmlinuz
  append initrd=initrd.img ks=http://192.168.11.101/ks/ha-slave1.ks ksdevice=bootif
  IPAPPEND 2
[root@ha-01 ~]#

■kickstartファイルの配置とPuppetクライアント用のhosts配置
１３．kickstartファイルを作成

[root@ha-01 ~]# mkdir -p /SHARE/data/netinst/ks

[root@ha-01 ~]# cat /SHARE/data/netinst/ks/ha-slave1.ks
install
text
url --url http://192.168.11.101/centos5.6-server-x86_64
lang ja_JP.UTF-8
keyboard jp106
network --bootproto=static --ip=192.168.11.105 --gateway=192.168.11.1 --netmask=255.255.255.0 --hostname=ha-slave1 --onboot=yes
rootpw abcdefg
firewall --disabled
authconfig --enableshadow --enablemd5
selinux --disabled
timezone Asia/Tokyo
reboot

bootloader --location=mbr --driveorder=vda
clearpart --all --initlabel --drives=vda
part /boot --fstype ext3 --size=512 --ondisk=vda
part pv.1 --size=0 --grow --ondisk=vda
volgroup vg00 pv.1
logvol /        --fstype ext3   --name=lv00     --vgname=vg00   --size=8192     --grow
logvol swap                     --name=lv01     --vgname=vg00   --size=1024
logvol /log     --fstype ext3   --name=lv02     --vgname=vg00   --size=1024
logvol /var     --fstype ext3   --name=lv03     --vgname=vg00   --size=5120
logvol /home    --fstype ext3   --name=lv04     --vgname=vg00   --size=5120


%packages
@development-libs
@editors
@system-tools
@japanese-support
@text-internet
@core
@base
@base-x
@ruby
@admin-tools
@development-tools
@graphical-internet

%post --log=/root/kickstart-post.log


mv /etc/hosts /etc/hosts.org
/usr/bin/wget -o /dev/null -P /etc http://192.168.11.101/hosts

test -f /etc/yum.repos.d/CentOS-Base.repo && /bin/mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.old

cat << EOF > /etc/yum.repos.d/custum.repo
[diskimage]
name=install image
baseurl=http://192.168.11.101/centos5.6-server-x86_64
enabled=1
gpgcheck=0

[puppet_setup]
name=additional packages
baseurl=http://192.168.11.101/puppet_setup
enabled=1
gpgcheck=0

EOF

yum clean all

/usr/bin/yum -y install puppet

cat << EOF > /etc/puppet/namespaceauth.conf
[puppetmaster]
    allow *

[puppetd]
    allow *

[puppetmaster]
    allow *

[fileserver]
    allow *

EOF

cat << 'EOF' > /etc/puppet/puppet.conf
[main]
    vardir = /var/lib/puppet
    logdir = /var/log/puppet
    rundir = /var/run/puppet
    ssldir = \$vardir/ssl

[puppetd]
    certname = ha-slave1
EOF

/usr/sbin/ntpdate ntpサーバ

export RUBYLIB=/var/lib/puppet/lib ; puppetd --no-daemonize --onetime --verbose --server ha-server
[root@ha-01 ~]#

１４．Pupppetクライアント用のhostsを配置

[root@ha-01 ~]# cp /etc/hosts /SHARE/data/netinst/

■Puppetマスターの設定
１５．Puppet関連パッケージのインストール確認

[root@ha-01 puppet_setup]# rpm -qa | grep -e augeas-libs-0.8.1-2.el5 -e ruby-augeas-0.4.1-1.el5 \
-e ruby-shadow-1.4.1-7.el5 -e facter-1.5.9-1.el5 -e puppet-0.25.5-1.el5 -e puppet-server-0.25.5-1.el5
augeas-libs-0.8.1-2.el5
facter-1.5.9-1.el5
ruby-shadow-1.4.1-7.el5
ruby-augeas-0.4.1-1.el5
puppet-server-0.25.5-1.el5
puppet-0.25.5-1.el5
[root@ha-01 puppet_setup]#

１６．Puppetで使用されるSSL関連ディレクトリを作成

[root@ha-01 data]# mkdir -p /SHARE/data/puppet_ssl

１７．Puppet各種ファイルを設定

[root@ha-01 ~]# cat /etc/puppet/puppet.conf
[main]
    vardir = /var/lib/puppet
    logdir = /var/log/puppet
    rundir = /var/run/puppet
    ssldir = /SHARE/data/puppet_ssl

[puppetmasterd]
    autosign = true
    certname = ha-server

[puppetd]
    certname = ha-01
[root@ha-01 ~]#

[root@ha-01 ~]# cat /etc/puppet/namespaceauth.conf
[puppetmaster]
    allow *

[puppetd]
    allow *

[puppetrunner]
    allow *

[fileserver]
    allow *
[root@ha-01 ~]#

[root@ha-01 ~]# cat /etc/puppet/autosign.conf
*
[root@ha-01 ~]#

★各環境に合わせてPuppetマニフェストを用意すれば準備OK