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;
以下の３つは 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
があるのでどれか１つを/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 の起動
sudo /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 に知らせる
sudo 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 オプションを使用する。
sudo 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にログイン
sudo mysql -u root mysql
パスワードを設定
ログインが済んだら、SET PASSWORD 構文でパスワードの設定だ。
mysql> SET PASSWORD FOR root=PASSWORD('new_password');
mysql>flush privileges;
shell コンソールからの設定 mysqladminで root のパスワード設定
コマンドラインでも、mysqladmin コマンドを使ってパスワードを設定することができる。
sudo mysqladmin -u root password new_password sudo 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>