PC-UNIXに関する記事など

MySQL についてメモ

基本的なSQL

SELECT

SELECT colname FROM tablename WHERE colname = 'val';

INSERT

INSERT INTO tablename (colname, colname) VALUES ('value', 'value');

UPDATE

UPDATE tablename SET colname = 'value' WHERE colname = 'value';

DELETE

DELETE FROM tablename WHERE colname = 'value';

TABLE 作成

CREATE TABLE tablename (colname type, colname type, ...);

MySQL コンソールでのDatabase操作コマンド等

default のインストール状態ではDataBaseの作成やテーブル作成は root 権限(MySQL内でのadmin)が必要である。各ユーザやデータベースのアクセス権はDataBase mysql 内の user,db テーブルで定義されているので、これを変更する必要がある。

各ユーザの mysql コンソールへのログイン方法

$ mysql -u username -p

database 選択

mysql>USE dbname;

database 作成

mysql>CREATE DATABASE dbname;

database 作成(コンソールから)

# mysqladmin CREATE dbname

database 削除

mysql>DROP DATABASE dbname;

以下の3つは USE dbname; として database をセットしてからの作業となる

テーブル作成

mysql>CREATE TABLE tablename (colname type, colname type, ...);

テーブル名変更

mysql>ALTER TABLE tablename RENAME AS newtablename;

テーブル削除

mysql>DROP TABLE tablename;

データベース及びテーブルの情報に関すること(DBセット後:mysql>USE dbname;)

テーブル情報表示

mysql>desc tablename;

データベース内のテーブル名を表示

mysql>SHOW TABLES;

database 内テーブルのCSV形式によるインポートとエクスポート

csvインポート

mysql>LOAD DATA INFILE "/path/to/file.csv" INTO TABLE tablename FIELDS TERMINATED BY ",";

csvエクスポート

mysql>SELECT * FROM test INTO OUTFILE "/tmp/t.csv" FIELDS TERMINATED BY ',';

FreeBSD に MySQL をインストール

FreeBSD では mysql-client 及び mysql-server のportsに分かれています。またバージョンも 3.23 4.0 4.1 5.0 5.1 と複数ありますのでお好みのものを入れて下さい。

(2008-06現在)

portinstall databases/mysql**-client
portinstall databases/mysql**-server

config file の設定 /usr/local/share/mysql 以下に数種類の config file がある

my-large.cfg
my-medium.cfg
my-small.cfg

があるのでどれか1つを/var/db/mysql にmy.cfgとしてコピーして使います。

rc.confに以下を追加して自動起動する設定

mysql_enable="YES"

localhost 以外からの接続を受け付ける場合(windows の MySQL QueryBrowser 等から接続)は TCP Wrapper を介した接続になるので hosts.allow に以下の記述が必要です。

設定をしなかった場合 conectionエラー「mysql error no 2013」が発生します。また mysqld にも bind-address を設定する必要があります。

設定例)

  • MySQL Server:192.168.0.10
  • MySQL Client:192.168.0.6

/etc/hosts.allow

mysqld : 192.168.0.6 : allow
mysqld : ALL :deny

/var/db/mysql/my.cfg

[mysqld]
bind-address = 127.0.0.1 192.168.0.10

MySQL server の起動

# /usr/local/etc/rc.d/mysql-server start

MySQL を起動できるユーザの登録

1.登録

# mysql -u root -p 
mysql> USE mysql; 
mysql> INSERT INTO user SET user = "addname", password = password("pswd"),host = "localhost";

2.登録情報変更を MySQL に知らせる

# mysqladmin -u root -p reload
又は、 
# mysqladmin flush-privileges -p 
あるいは、 
mysql>flush privileges;

なおここで新規に登録したユーザはdatabaseを操作する権限を持っていないため何も出来ないに等しい。そこで GRANT を使い利用可能にするコマンドを設定しなければいけない。

3.GRANT を使い通常ユーザにmysqlを使えるようにする

$ mysql -u root -p 
Password: 
mysql> GRANT SELECT ON dbname.tablename TO username;

ここでは、データベース dbname のテーブル tablename に対してユーザ username が SELECT コマンドを利用できるようになる。データベース全体のテーブルについてアクセス可にする為には dbname.* のようにワイルドカードも利用できる。また、SELECT 以外のコマンドも利用可にするために以下のようにすると良い。

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON dbname.* TO username;

MySQL へ自動ログイン

通常 MySQL にログインするには下記のように -p オプションを使用する。

# mysql -u user_name -p
Enter password: ********

これを自動でログインするには .my.cnf 設定ファイルにログイン情報を書いておけば mysql とタイプするだけでログインできる。 そうすることにより、毎回毎回コマンドラインに引数を与えなくてすむようになる。

まず、自分のホームディレクトリに .my.cnf を作り、そのファイルの中の [client] セクションに

接続用のパラメターを記述する。

~/.my.cnf

[client]
host=host_name
user=user_name
password=your_pass

セキュリティを守るため .my.cnf ファイルはグループやその他のユーザーが読み書きできないようにしよう。

ファイルのパーミッションは 600 が良いでしょう。

$ chmod 0600 .my.cnf

root のパスワード設定

MySQL コンソールからの設定 rootでmysqlにログイン

# mysql -u root mysql

パスワードを設定

ログインが済んだら、SET PASSWORD 構文でパスワードの設定だ。

mysql> SET PASSWORD FOR root=PASSWORD('new_password');
mysql>flush privileges;

shell コンソールからの設定 mysqladminで root のパスワード設定

コマンドラインでも、mysqladmin コマンドを使ってパスワードを設定することができる。

# mysqladmin -u root password new_password
# mysqladmin reload

DATABASE のバックアップとレストア

Backup

SQL データベースのバックアップは、mysqldump コマンドを実行して行います。次のように実行するとDatabaseNameの内容が FILENAME.mysql ファイルにバックアップが作成されます。

$ mysqldump -a --user=USERNAME --password=PASSWORD DatabaseName > FILENAME.mysql

Restore

MySQLデータベースの復旧は、次のようにmysqlコマンドを実行します。バックアップファイル FILENAME.mysql から DatabaseName へ内容を復元します。

$ mysql --user=USERNAME --password=PASSWORD DatabaseName < FILENAME.mysql

table の設定を見る

mysql コンソールから database を選択して describe コマンドで見たいテーブルを指定します。以下はmysql database の user テーブルの情報を表示しています。

mysql> use mysql;
Database changed
mysql> describe user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60)                       |      | PRI |         |       |
| User                  | varchar(16)                       |      | PRI |         |       |
| Password              | varchar(41)                       |      |     |         |       |
| Select_priv           | enum('N','Y')                     |      |     | N       |       |
| Insert_priv           | enum('N','Y')                     |      |     | N       |       |
| Update_priv           | enum('N','Y')                     |      |     | N       |       |
| Delete_priv           | enum('N','Y')                     |      |     | N       |       |
| Create_priv           | enum('N','Y')                     |      |     | N       |       |
| Drop_priv             | enum('N','Y')                     |      |     | N       |       |
| Reload_priv           | enum('N','Y')                     |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       |
| Process_priv          | enum('N','Y')                     |      |     | N       |       |
| File_priv             | enum('N','Y')                     |      |     | N       |       |
| Grant_priv            | enum('N','Y')                     |      |     | N       |       |
| References_priv       | enum('N','Y')                     |      |     | N       |       |
| Index_priv            | enum('N','Y')                     |      |     | N       |       |
| Alter_priv            | enum('N','Y')                     |      |     | N       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11) unsigned                  |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.01 sec)

mysql>

このエントリーをはてなブックマークに追加

トップ  »  RDBMS  »  MySQL についてメモ

トラックバック(0)

トラックバックURL: http://www.omakase.org/mt/mt-tb.cgi/3

コメントする

カテゴリRDBMSの記事一覧