Install MySQL
DebianなLinuxでの説明です。
Debian GNU/Linux 6.0.7 (squeeze)
list of dpkg -l
libdbd-mysql-perl 4.061-1 #Perl5 database interface to the MySQL database
libmysqlclient-dev 5.1.66-0+squeeze1 #MySQL database development files
libmysqlclient16 5.1.66-0+squeeze1 #MySQL database client library
mysql-client-5.1 #MySQL database client libraries
mysql-common #MySQL database common files, e.g. /etc/mysql/my.cnf
mysql-server 5.1.66-0+squeeze1 #MySQL database server(metapackage depending on the latest version)
mysql-server-5.1 5.1.66-0+squeeze1 #MySQL database server binaries and system database setup
mysql-server-core-5.1 5.1.66-0+squeeze1 #MySQL database server binaries
php5-mysql 5.3.3-7+squeeze15 #MySQL module for php5
MySQLの構成/var/lib/mysql
MySQLインストール先 /var/lib/mysql
設定ファイル /etc/mysql/my.cnf
文字コード関連
mysql> show variable like 'char%'; の結果
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_set_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
CREATE DATABASE
mysql> create database testdb;
mysql> create database testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| testdb |
+--------------------+
mysql> use testdb;
grant
mysql> use mysql;
現在のuserテーブル内容を表示
mysql> select Host, User,
Select_priv as S, Insert_priv as I, Update_priv as U, Delete_priv as D,
Create_priv as C, Drop_priv as DR, Index_priv as ID, Alter_priv as A,
Super_priv as SP, Grant_priv as GR, Reload_priv as Rel,
Shutdown_priv as Sdwn, Process_priv as Prc, File_priv as Fl,
References_priv as Ref, Show_db_priv as ShDb, Create_user_priv as CU,
Event_priv as Ev, Trigger_priv as Trg from user;
+---------------+--------+---+---+---+---+---+----+----+---+----+----+-----+------+-----+----+-----+------+----+----+-----+
| Host | User | S | I | U | D | C | DR | ID | A | SP | GR | Rel | Sdwn | Prc | Fl | Ref | ShDb | CU | Ev | Trg |
+---------------+--------+---+---+---+---+---+----+----+---+----+----+-----+------+-----+----+-----+------+----+----+-----+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| db | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| 127.0.0.1 | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| db | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| 192.168.11.1 | user01 | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| 192.168.11.2 | user01 | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+---------------+--------+---+---+---+---+---+----+----+---+----+----+-----+------+-----+----+-----+------+----+----+-----+
6 rows in set (0.00 sec)
user01を追加します
mysql> grant all privileges on *.* to user01@'192.168.11.2' IDENTIFIED BY 'hogehoge';
mysql> grant all privileges on *.* to user01@'192.168.11.3' IDENTIFIED BY 'hogehoge';
追加後のuserテーブル内容を表示
+---------------+--------+---+---+---+---+---+----+----+---+----+----+-----+------+-----+----+-----+------+----+----+-----+
| Host | User | S | I | U | D | C | DR | ID | A | SP | GR | Rel | Sdwn | Prc | Fl | Ref | ShDb | CU | Ev | Trg |
+---------------+--------+---+---+---+---+---+----+----+---+----+----+-----+------+-----+----+-----+------+----+----+-----+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| db | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| 127.0.0.1 | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| ::1 | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| localhost | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| db | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
| 192.168.11.1 | user01 | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| 192.168.11.2 | user01 | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y |
+---------------+--------+---+---+---+---+---+----+----+---+----+----+-----+------+-----+----+-----+------+----+----+-----+
8 rows in set (0.00 sec)
ユーザの削除
mysql> drop user user01@'192.168.11.1';
mysql> drop user user01@'192.168.11.2';
Create Table
mysql> create table testtable(
id int auto_increment,
name char(20), PRIMARY KEY(id)
);
mysql> insert into testtable(name) values('test001');
mysql> insert into testtable(name) values('test001');
mysql> insert into testtable(name) values('test001');
mysql> select * from testtable;
+----+---------+
| id | name |
| 1 | test001 |
| 2 | test001 |
| 3 | test001 |
+----+---------+
user_hogeユーザを作成し、選択、更新、挿入、削除の権限を付与
mysql> GRANT SELECT, UPDATE, INSERT, DELETE ON testdb.* TO user_hoge@localhost IDENTIFIED BY 'aaaa';
# mysql -u user_hoge -p
Perlのデータベースインターフェースを介しデータベースへアクセス
#!/usr/bin/perl
use DBI;
print "Content-Type: text/plain\n\n";
$user = 'user_hoge';
$passwd = 'aaaa';
$db = DBI->connect('DBI:mysql:testdb:localhost', $user, $passwd);
$sth = $db->prepare("select id, name from testtable");
$sth->execute;
$num_rows = $sth->rows;
print "hit : $hum_rows\n";
for($i=0; $i<$Num_rows; $i++) {
@a = $sth->fetchrow_array;
print "id=$a[0], name=$a[1]\n";
}
$sth->finish;
$db->disconnect;