概要


テーブル名とフィールド名以外は大文字でも小文字でも構わない。

参考


インストール

管理権限を持つ場合

You should use「apt-get」 or 「yum」 in order to install mysql.

管理権限を持たない場合

現時点で成功せず

sourceからインストールする。

参考文献

libncursesの確認

先ず、環境によっては「libncurses」が無い場合があるため、これを確認する。 無ければ、例えば

apt-get install libncurses5 libncurses5-dev

でインストール可能であるが、今回は「管理権限を持たない場合のインストール」である為、これは使えない。 よって「libncurses」もsourceからインストールする。

通常のインストール方法でインストール可能:

wget http://ftp.gnu.org/pub/gnu/ncurses/ncurses-5.9.tar.gz
tar zxvf ncurses-5.9.tar.gz
cd ncurses-5.9
./configure --prefix=/home/hoge/local
make
make install
export LD_LIBRARY_PATH=${HOME}/local/lib:${LD_LIBRARY_PATH}

mysqlのmake

オフィシャルサイトからsourceを落として来る。例えば「mysql-5.5.15.tar.gz」。 ver5.5からcmakeでのコンパイルに変更されている為、 「./configure」での設定、それに付随するオプションを変更する必要がある:

tar zxvf mysql-5.5.15.tar.gz
cd mysql-5.5.15
cmake . -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DMYSQL_DATADIR=/home/hoge/local/share/mysql/data \
-DCMAKE_INSTALL_PREFIX=/home/hoge/local/share/mysql/ -DCURSES_LIBRARY=/home/hoge/local/lib/libncurses.a \
-DCURSES_INCLUDE_PATH=/home/hoge/local/include \
-DINSTALL_LAYOUT=STANDALONE -DENABLED_PROFILING=ON \
-DMYSQL_MAINTAINER_MODE=OFF -DWITH_DEBUG=OFF

OR

例えば「mysql-5.1.58.tar.gz」。 「./configure」で以下のオプションを設定:

./configure --prefix=/home/hoge/local/share/mysql \
--enable-assembler \
--with-mysqld-ldflags=-all-static \
--with-client-ldflags=-all-static \
--with-mysqld-user=hoge \
--with-unix-socket-path=/home/hoge/local/share/mysql/tmp/mysql.sock \
--localstatedir=/home/hoge/local/share/mysql/data \
--with-named-curses-libs=/home/hoge/local/lib/libncurses.a \
CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"

mysqlのinstall and setting

現時点で成功せず

以下の様に「/home/hoge/local/share/mysql/my.cnf」を作成:

[mysqld]
user=hoge
basedir=/home/hoge/local/share/mysql
datadir=/home/hoge/local/share/mysql/data
port=3307
socket=/home/hoge/local/share/mysql/tmp/mysql.sock

[mysqld_safe]
log-error=/home/hoge/local/share/mysql/log/mysqld.log
pid-file=/home/hoge/local/share/mysql/mysqld.pid

[client]
gport=3307
user=hoge
socket=/home/hoge/local/share/mysql/tmp/mysql.sock

[mysqladmin]
user=root
port=3307
socket=/home/hoge/local/share/mysql/tmp/mysql.sock

[mysql]
port=3307
socket=/home/hoge/local/share/mysql/tmp/mysql.sock

[mysql_install_db]
user=hoge
port=3307
basedir=/home/hoge/local/share/mysql
datadir=/home/hoge/local/share/mysql/data
socket=/home/hoge/local/share/mysql/tmp/mysql.sock

また、デフォルトの設定ファイル(ディレクトリ)を指定する環境変数「MYSQL_HOME」を設定:

export MYSQL_HOME=/home/hoge/local/share/mysql/

これもうまく機能していない。対応は後述。


databaseの初期化を行う。for 5.5:

cd /home/hoge/local/share/mysql/scripts
./mysql_install_db --defaults-file=/home/hoge/local/share/mysql/my.cnf

for 5.1:

cd /home/hoge/local/share/mysql/bin
./mysql_install_db --defaults-file=/home/hoge/local/share/mysql/my.cnf

mysqldの起動:

cd ../bin
./mysqld_safe --defaults-file=/home/chinoney/local/share/mysql/my.cnf

「$MYSQL_HOME」が設定されていれば、いずれの「--defaults-file」の設定は不要なはずだが、そうはなっていな。 設定しないと「chown: cannot access `/var/run/mysqld': No such file or directory」の様なエラーが出る。


mysqld立ち上げ後、アクセスを試みるも以下の様に「Segmentation Fault」が発生し、使用できなかった:

hoge@server:~$ mysql -u root
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15 Source distribution

Copyright (c) 2000, 2010, 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.

Segmentation fault

基本的な使用方法

クライアントの実行

プロンプト上で

mysql -u username -D database_name -p
Enter password: 

を実行。

mysqlプロンプトは行の後ろに「;」で確定

show databases ;
show tables ;
exit ;

データベースを作成する

mysql -u root -p
create database database_name ;

テーブルの作成

create table test_table ( id INT PRIMARY KEY, mjd DOUBLE, memo char) ;

既存のテーブルにcolumnを追加

alter table test_table add column add_column double after column_name ;

テーブル行の中の指定した場所にカラムを追加するには、FIRST か AFTER col_name を利用してください。 デフォルトはカラムを最後に追加します。

既存のテーブルのcolumnを削除

alter [ignore] table test_table drop [column] column_name

データの挿入

insert into test_table ( id, mjd, memo) VALUES( 1, 54920.5, 'hoge') ;

データの抽出

select文を使いテーブルのデータ(レコード)を抽出する:

select field_name,,,,
from tabale_name
where 条件式
order by field_name [desc]
limit オフセット, 行数
field_name
抽出するフィールド名をカンマで区切り指定する。フィールド名の変わりに「*」を指定すると全フィールドが抽出される。 フィールドには別名を付けることができる(field_name as hoge)。
from
対象のテーブル名をカンマ区切りで指定する。field_name同様に別名をつけることが可能。 テーブルを複数指定した場合、このテーブルのフィールドにアクセスするためにはtable_name.field_nameのように指定する。
where
抽出する為の条件式:
演算子説明
=等しければid = 1
<>!=等しくなければid <> 1 または id != 1
>大きければid > 10
>=以上ならid >= 10
<小さければid < 10
<=以下ならid <= 10
is nullnullならzip is null
is not nullnullでなければzip is not null
inどれかに等しければid in ( 1, 2, 3 )
between 値1 and 値2値1から値2の間ならid between 1 and 10
likeワイルドカードによるあいまい検索handle_name like '%wind%'windという文字列が含まれていれば真になります。
and二つの条件が真ならid = 1 and zip is null
orどちらかの条件が真ならid = 1 or zip is null
not条件の反転not( id = 1 )
order by
結果をfield_nameでソートする。デフォルトでは昇順。「desc」で降順。
limit
抽出する件数を指定。「order by」「rand()」と組み合わせることで、ランダムな抽出が可能に。「order by rand() limit 0, 10」ランダムに10個抽出。
別名
field_name, tabale_nameは「name (AS) 別名」(ASは無くてもよい)で別名指定ができる。

データの更新

update test_table set memo='hogehoge' where id='1' ;

データの削除

delete from test_table where id='1' ;

テーブルにフィールドを追加

ALTER table test_table add date DATETIME ;

テーブルをリネーム

RENAME TABLE table_old TO table_new ;

テーブルの削除

DROP TABLE table_name

テーブルの"コピー"

CREATE TABLE hoge_bak LIKE hoge;
INSERT INTO hoge_bak SELECT * FROM hoge;

データベースの削除

DROP DATABASE database_name ;

存在しないDBを指定した場合、エラーとなる。この際既存のどのDBに対して何の影響も無い。 歩かないか分からない場合は、「if exists」オプションを使う:

drop database if exists database_name ;

VIEW

作成

MySQLでは実体を持たない仮想tableを作成することができる:

DROP VIEW IF EXISTS view_name ;
CREATE VIEW view_name(FIELD1, FIELD2,...) AS SELECT field0, 0.9*field2,... FROM table_name WHERE field5='hoge' ;

table名の簡単なalias:

CREATE VIEW alias_table AS SELECT * FROM original_table ;

確認

tableに実体があるかそれともviewなのか確認:

mysql> show full tables ;
+----------------------------------+------------+
| Tables_in_quiet_quality          | Table_type |
+----------------------------------+------------+
| q19_ces_usable                   | BASE TABLE |
| q19_diodes                       | BASE TABLE |
| q19_diodes_new                   | BASE TABLE |
| q19_gaussianity                  | BASE TABLE |
| q19_housekeeping                 | BASE TABLE |
| q19_map_chi2                     | VIEW       | 

作成時の設定を確認:

mysql> SHOW CREATE VIEW q19_map_chi2 ;

STORED PROCEDURE/FUNCTION

ストアドプロシージャとは、複数のSQL文をひとまとめにした関数をDB上に保存したり何らかの処理を関数としてDB上に保存する。 ストアドプロシージャとストアドファンクションの違いは、呼び出し側に戻り値を返すかどうかの違いだけである。

作り方

構文:

CREATE PROCEDURE ストアド名 ([引数[,...]])
    [characteristic ...]
    処理

又は

CREATE PROCEDURE ストアド名 ([引数[,...]])
    [characteristic ...]
    BEGIN
        処理
    END
CREATE FUNCTION ストアド名 ([引数[,...]])
    RETURNS 戻り値の型
    [characteristic ...]
    処理

又は

CREATE FUNCTION ストアド名 ([引数[,...]])
    RETURNS 戻り値の型
    [characteristic ...]
    BEGIN
        処理
    END

引数は、入力用、出力用、入出力用の特性、名称、型を指定:

[ IN | OUT | INOUT ] 引数名 型

確認

SHOW CREATE FUNCTION <function_name>
SHOW CREATE PROCEDURE <procedure_name>

dump

mysqldumpのデフォルトでは

  • routines – FALSE by default
  • triggers – TRUE by default

となっている。なので「routines」をtrueにする必要がある(-R, --routines):

mysqldump <other mysqldump options> --routines > outputfile.sql

上の場合だと、function/procedure以外もdumpしてしまう。 function/procedureだけdumpしたいときは以下の様にする:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

restore:

mysql <database> < outputfile.sql

参考

リダイレクト

入力

mysqlプロンプト内でタイプできるものは、外部にファイルとして書き込み、リダイレクトすることで実行可能:

cat batch.sql
CREATE TABLE
test_table(
  id int(10),
  name char(20),
  memo char(240)
);
mysql -D database_name -p < batch.sql

出力

結果をファイルに書き出すことも可能:

mysql -D database_name -p > result.dmp
select * from database_name ;

入出力

mysql -D database_name -p < input > output

シェルスクリプト

<<EOFでつなぐ(正しく閉じていれば、「EOF」である必要は無い。たとえば「!」等でもよい。):

#!/bin/sh
# how to use: ./create_table_w91_taua_yuji.sh 127.0.0.1 3306

if [ $# != 2 ]
then
    echo "how to use: ./create_table_w91_taua_yuji.sh 127.0.0.1 3306"
    exit
fi

hostname=$1
port=$2
passwd='hoge'
username='huga'
database='quiet_cal'

mysql -u $username -D $database -h $hostname --password=$passwd --port=$port <<EOF
DROP TABLE IF EXISTS w91_taua_yuji ;
CREATE TABLE w91_taua_yuji
( id INT UNSIGNED AUTO_INCREMENT NOT NULL,
  time DATETIME NOT NULL,
  module tinyint(2) NOT NULL,
  diode char(2) NOT NULL,
  demod_gain FLOAT NULL DEFAULT NULL,
  demod_gain_err FLOAT NULL DEFAULT NULL,
  leakage FLOAT NULL DEFAULT NULL,
  leakage_err FLOAT NULL DEFAULT NULL,
  pol_angle FLOAT NULL DEFAULT NULL,
  pol_angle_err FLOAT NULL DEFAULT NULL,
  reduced_chi2  FLOAT NULL DEFAULT NULL,
  ndf INT UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY(id, time, module, diode)
)
EOF

権限管理

参考

mysql

mysqlではユーザー設定等をmysqlデータベース自身で行なっている。 (ユーザー情報ををmysqlのデータベースとして保持している)。 つまり、権限管理は通常のデータベース操作と変わらない。 但し、権限管理により特化したコマンドも存在する。

付与

grant priv_type on {table name|*|*.*|database_name.*} to username identified by passwd ;

例:

grant usage on *.* to username@localhost identified by "passwd" ;
grant select,execute,show view on database_name.* to username@localhost identified by "passwd" ;
show grants for username@localhost ;
+-------------------------------------------------------------------------------------+
| Grants for quiet@localhost                                                          |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'quiet'@'localhost' IDENTIFIED BY PASSWORD 'hoge'             | 
| GRANT SELECT, EXECUTE, SHOW VIEW ON `quiet_cal`.* TO 'quiet'@'localhost'            | 
+-------------------------------------------------------------------------------------+

priv_type

基本:

priv_type説明
USAGE権限なしのシノニム(権限なしのユーザを作成する)
ALL [PRIVILEGES]WITH GRANT OPTION以外の全て
CREATECREATE TABLEの使用を許可
DELETEDELETEの使用を許可
DROPDROPの使用を許可
EXECUTEストアドプロシージャ/ファンクションの実行を許可
INSERTINSERTの使用を許可
SELECTSELECTの使用を許可
SHOW VIEWSHOW CREATE VIEWの使用を許可

extra:

ALTERALTER TABLEの使用を許可
ALTER ROUTINEストアドプロシージャ/ファンクションの変更と削除を許可
CREATE ROUTINEストアドプロシージャ/ファンクションの作成を許可
CREATE TEMPORARY TABLESCREATE TEMPORARY TABLEの使用を許可
CREATE USERCREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGESの使用を許可
CREATE VIEWCREATE VIEWの使用を許可
FILESELECT ... INTO OUTFILE 及び LOAD DATA INFILEの使用を許可
INDEXCREATE INDEX 及び DROP INDEXの使用を許可
LOCK TABLESSELECT権限を持つテーブルでLOCK TABLESの使用を許可
PROCESSSHOW FULL PROCESSLISTの使用を許可
REFERENCES定義のみ(未実装)
RELOADFLUSHの使用を許可
REPLICATION CLIENTSHOW MASTER STATUS、SHOW SLAVE STATUSの使用を許可
REPLICATION SLAVEインスタンスをレプリケーションのスレーブとして動作させることを許可(マスタからバイナリログを読み取る為)
SHOW DATABASESSHOW DATABASEによりすべてのデータベースが表示される
SHOTDOWNmysqladmin shutdownの使用を許可
UPDATEUPDATE の使用を許可
GRANT OPTIONWITH GRANT OPTION のシノニム

削除

drop user username@'hostname' ;

OR

revoke priv_type on {table name|*|*.*|database_name.*} from username ;
flush privileges ;

参考(Poturi: MySQL)

バックアップ

mysqldump

mysqldumpを使って既存のDB(table)をSQLコードとしてdumpする。 実行にはroot権限ユーザーもしくは、mysqldum可能権限(FILE)が必要:

mysqldump [options] db_name [tables]
mysqldump [options] --databases db_name1 [db_name2 db_name3...]
mysqldump [options] --all-databases

[option]には例えば、ユーザー指定、パスワード使用の場合などは

-u root -p

となる。[tables]を指定しない場合には、[db_name]すべてがダンプされる。 最後の使用法ではすべてのDBがダンプされる。

上記以外のオプションの例としては、

  • 「DROP DATABASE」ステートメントを「CREATE DATABASE」ステートメントの前にに置く:
    --add-drop-database
    これは、すでに存在するDBを、mysqldumpで作成したdump fileを使って上書きする際に便利である。 通常、すで同名のDBが存在する場合は、警告がで上書きは行われない。
  • 上記のTABLE version:
    --add-drop-table

restore

「mysql」コマンドにSQLを喰わせることができるので、 「mysqldump」の出力を喰わせることでDB (table)のリストアが可能である。

mysqldump [option] db_name > db_name.sql
mysql [option] -D db_name < db_name.sql

mysqldump [option] db_name table > db_name_table.sql
mysql [option] -D db_name < db_name_table.sql

ところで

mysqldump [option] db_name > db_name.sql
mysql [option] db_name table < db_name.sql

は不可?


[db_name]でdumpしたDBを別名(db_name_k)でリストアしたい場合は、 dumpしたファイルに含まれる[db_name]を別名で置換することで(sed等で)対応できる: 「--add-create-table」オプションをつけなかった場合は、当然事前にDBを作成しておく必要がある。

外部のMySQLに接続する

接続先のサーバーで認証を与えておく:

grant all on hoge_db.* to foo@'hoge.com' identified by 'password';

注意事項:

  • /etc/mysql/my.cnf の"bind-address"の行があれば、コメントアウト。
  • /etc/hosts.deny, /etc/hosts.allowでアクセスの制御をしている場合には/etc/hosts.allowに以下を追記:
    mysqld: xxx.xxx.xxx.xxx
mysql --host=hoge.com -u foo -D hoge_db -p

外部のMySQLに安全に接続する

sshのポート転送機能を利用する:

ssh -L 3366:localhost:3306 username@hostname
mysql -u mysql_username -p -h 127.0.0.1 --port=3366

これで暗号化されたトンネルを使って外部のMySQLにアクセスできる。

Replication

SHOW MASTER STATUS 構文

SHOW SLAVE STATUS 構文

CHANGE MASTER TO 構文

参考

MySQLに於けるlocalhostの扱い

通常Unix likeなシステムであれば、「/etc/hosts」で

127.0.0.1       localhost.localdomain localhost

の様に定義され、localhost127.0.0.1のaliasとして働く。 しかしMySQLではそうではない。 MySQL上でlocalhostはローカルのソケット(ファイル)を通して接続を行なうが、 MySQL上で127.0.0.1はTCPソケットで接続を行なう。

このことから、以下の様な予期せぬ動作をする:

リモートサーバーの3306 port(mysql default)を、 localhostのport 13306へport forwardを行なう。 この転送されたポートに接続することで、リモートサーバーのmysqlに安全にアクセスすることができる。 この際、

mysql -u root -h localhost -P 13306 -p

mysql -u root -h 127.0.0.1 -P 13306 -p

とでは動作が異なる。前者はローカルにあるmysqldに接続され、 後者はリモートのmysqldに接続される。

mysql_localhost.png

添付ファイル: filemysql_localhost.png 396件 [詳細]