Edit
MySQLについて

MySQLとは、厳密に言えば半分フリーのデータベースです。PostgreSQLに比べるとクラスタリング(分散DB)機能がすでに実装されており、オラクル相当DBの無料版として利用できますが、企業が提供しているので、リリースも早いのですが将来の突然の有料化が心配されます。

Edit
MySQLの導入

Edit
インストール

ちなみにソースをダウンロードするときに、ユーザー登録が必要になるようだ。

# wget http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.6/mysql-5.6.29.tar.gz
# tar zxvf mysql-5.6.29.tar.gz
# cd mysql-5.6.29
# yum install cmake ncurses-devel
# cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci 
                 -DENABLED_LOCAL_INFILE=true -DWITH_INNOBASE_STORAGE_ENGINE=1
                 -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=O -DINSTALL_MYSQLTESTDIR=
                 -DINSTALL_SQLBENCHDIR=
# make
# make install

Cmakeオプションの説明、但し一部は、my.cnfで設定できる。詳細はこちら

コマンド引数説明
-DCMAKE_INSTALL_PREFIX/usr/local/mysqlインストール先
-DDEFAULT_CHARSETutf8デフォルトのキャラクタセット
-DDEFAULT_COLLATIONutf8_general_ciキャラクタセットのデフォルト照合順序
-DWITH_EXTRA_CHARSETSallエキストラキャラクタセットの指定
-DMYSQL_UNIX_ADDR/var/lib/mysql/mysql.sock(初期値)ソケット名を指定
-DMYSQL_TCP_PORT5506(初期値)ポートを指定
-DMYSQL_DATADIR/var/lib/mysqlデータディレクトリ
-DENABLED_LOCAL_INFILEtrueLOAD DATA INFILE文の実効可否
-DSYSCONFDIR/etc/my.conf(初期値)my.cnfの指定
-DWITH_INNOBASE_STORAGE_ENGINEtrueデータベースエンジンにInnoDBを指定
-DWITH_READLINEOFF(初期値)コマンドラインにて過去に実行したコマンドの履歴表示や編集を可能
-DCMAKE_INCLUDE_PATH/usr/local/ssl/includeコンパイル時のinludeを追加したい場合
-DCMAKE_C_FLAGS-I/usr/local/ssl/includeコンパイル時のコンパイルオプションを付けたい場合
-DWITH_SSLsystemSSLオプションの指定
-DOPENSSL_LIBRARIES/usr/local/ssl/lib/libssl.solibsslの指定
-DCRYPTO_LIBRARY/usr/local/ssl/lib/libcrypto.solibcryptoの指定
-DINSTALL_MYSQLTESTDIR=引数の指定なしで、mysql-test一式を削除できる
-DINSTALL_SQLBENCHDIR=引数の指定なしで、sql-bench一式を削除できる

※ /usr/local/mysql/bin にpathを通すことを忘れない事。
※ --libexecdir=/usr/local/mysql/bin オプションの追加で、/usr/local/libexec が生成されないが、この作業は未検証。
※ --prifexを指定した場合、mysqlに関するファイルはこのフォルダにまとめられる。

Edit
ユーザーとグループの作成

<ユーザー追加>

# groupadd mysql
# useradd -g mysql -d /usr/local/mysql -s /bin/false mysql
# passwd mysql
# chown -R mysql:mysql /usr/local/mysql/

<ユーザー削除>

# userdel mysql

Edit
サービスの登録

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

Edit
Libraryの登録

# vi /etc/ld.so.conf.d/mysql.conf
  /usr/local/mysql/lib
# ldconfig

Edit
必要フォルダの作成

# cd /usr/local/mysql
# mkdir run
# mkdir data
# mkdir log
# chown mysql:mysql run data log

Edit
定義ファイルの設定

Edit
定義ファイルの参照順番(初期値)

MySQLの定義ファイルであるmy.cnfの優先参照は、下記のリストの上から順に適用していく。

番号位置適用
1/etc/my.cnfMySQL 全体的に影響する定義ファイル
2<each-data-dir>/my.cnf特定のDBに適用するように、各データファイルの中に設定する
3'~/.my.cnfユーザー毎に設定ファイルを記述

Edit
参考定義ファイル

# vi /etc/my.cnf
  [mysqld]
  datadir=/usr/local/mysql
  socket=/usr/local/mysql/run/mysql.sock
  user=mysql
  pid-file=/usr/local/mysql/run/mysqld.pid
  character-set-server=utf8
  explicit_defaults_for_timestamp = true        <- これを指定しないとワーニングがでる。
  log-bin                                                               <- バイナリログを取ることを指定
  log-slave-updates
  server-id=1                                                       <- 任意の識別用ID。他のサーバーと重複禁止。
  #gtid-mode=ON                                                 <- GTID(Global Transaction ID)機能を有効にする
  #enforce-gtid-consistency
  #skip-character-set-client-handshake
  #old_passwords=1
  #bind-address = 192.168.0.100                       <- IPアドレスは1つだけ指定可能。指定無しだと制限無し。
  #skip-grant-tables                                              <- 有効にするとパスワードチェックなどを無視する。

  [mysqld_safe]
  log-error=/usr/local/mysql/log/mysqld.log
  pid-file=/usr/local/mysql/run/mysqld.pid

  [client]
  socket=/usr/local/mysql/run/mysql.sock

  [mysql]
  #default-character-set=utf8                  < 最新だとエラーに。

※[mysql] と、[mysqld] のキャラクター設定方法(character-set)は同じ名前だと問題がおこるため注意が必要。(上記設定を参照)

Edit
mysql.sockについて

ローカルデーモンのソケットにアクセスする際には、MySQLではファイルを通して通信する。もちろんネットワークでは通常に処理される。そこでmysqladminなどのクライアントコマンドを利用する際には、このファイルの設定が上手く言っていないと、ネットワーク越しではアクセスできても、ローカルからアクセスができないという状態になる。

-/etc/my.cnf の socket記述の位置
-環境変数 MYSQL_UNIX_PORTを参照
-デフォルト値 /tmp/mysql.sock でアクセス
''以上の順番でソケットの位置を確認される。''

※Socketファイルは[mysqld]と[client]を合わせておく。本来場所は自由に選択できるのだが、クライアントソフトによってはこのファイルを参照せず、変更できなくなっている場合もある。

Edit
sql_modeについて

MySQLの設定ファイル、my.cnf に下記のような行を追加することで sql_mode を指定できる。これは、MySQL 5.6以降から採用された。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION   (これは初期値)

※設定しても変更されないときは、ほかの my.cnf に上書きされているため、 # find / | grep my.cnf などで、my.cnf を総洗いしたほうが良い。

定義名概要
STRICT_TRANS_TABLESトランザクションストレージエンジンの厳密モードを有効。挿入・更新におけるパラメータ及び型を厳密にチェックし、以前ならワーニングのものをエラーとする
NO_ENGINE_SUBSTITUTIONNO_ENGINE_SUBSTITUTION を無効にすると、CREATE TABLE については、目的のエンジンが利用できない場合にデフォルトエンジンが使用されて警告が発生します。ALTER TABLE では、警告が発生してテーブルは変更されません。有効だとエラーになります
TRADITIONAL組み合わせモード。STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、および NO_ENGINE_SUBSTITUTIONをまとめたもの

※これら組み合わせや設定の詳細は、このページを参照のこと。

mysqlコマンドによるモード変更

mysql > SET GLOBAL sql_mode = '';

mysqlコマンドによるモード確認

mysql > SELECT @@GLOBAL.sql_mode;

Edit
iptablesの開放

MySQLの標準ポートである、3306番に穴を開けておく。もしポート番号を変更している場合には、この数字も合わせて変更する。但し外部からアクセスがなくローカル内だけで完結する場合、通常 linux socket を利用するため、ポートは利用しないので設定の必要は無い。

# emacs /etc/sysconfig/iptables
  -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

Edit
DBの初期化

dataフォルダが空になっていることを確認し実行

# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

※DNS解決に関するエラーが出たらとりあえず、--force で通すのもあり。

Edit
ユーザー管理

MySQLのログインに関する基本ユーザー情報は mysql.user テーブルに記録されている。このuserテーブルは、ユーザー毎のアクセス可能ホスト別にレコードが作成される。ようするに同じuser名でも、アクセス可能ホストが違う場合は2つのレコードとして登録される。(これはhost項目に複数のアドレスを登録できないため)また以下の制約が存在する。

  1. ユーザー名ではワイルドカードを使用できない。(但し危険だが、名無しユーザは作成できる。)
  2. host項目にはワイルドカードが使え、'_'は任意の一文字を表し、'%' は任意の長さの文字列に一致する。(例:user@"%.mydomain.com" user@"192.168.100.10_" など)
  3. ホスト名を省略すると、user@'%' と同じ。

また上記のhost項目によるアクセス制限の他、データベースやテーブルのアクセスに関するアクセス制限も設定できる。

権限レベルレベルの説明各権限の保存先
グローバルレベル特定のサーバ上にある全データベースに適用されます。GRANT ALL ON *.*は、グローバル権限の付与のみを行います。mysql.user
データベースレベル指定したデータベースの全テーブルに適用されます。GRANT ALL ON db.*は、データベース権限の付与のみを行います。mysql.db
テーブルレベル指定したテーブルの全カラムに適用されます。GRANT ALL ON db.tableは、テーブル権限の付与のみを行います。mysql.tables_priv
カラムレベル指定したテーブルの特定のカラムに適用されます。mysql.columns_priv

Edit
注意点

<ユーザー関係のGRANTを変更したときは以下を必ず実行>

mysql > FLUSH PRIVILEGES;
# service mysqld reload

※注意!、パスワードは値がハッシュされるため、update などで直接数値を指定しないこと。

<一時的にGRANTテーブルを無視する方法 [#t6a5f425]>

# vi /etc/my.cnf
 [mysqld]
 skip-grant-tables                     <- これを追加

Edit
mysqlコマンドについて

mysqldデータベースを操作するには、mysqlコマンドを利用するが、その際の設定ファイルは、mysqld の起動時に使った設定ファイルを参照しようと以下の順番で設定ファイルが検索される。

  1.  /etc/my.cnf
  2.  /etc/mysql/my.cnf
  3.  /usr/local/mysql/etc/my.cnf
  4.  ~/.my.cnf

特にこの設定ファイルで指定される socket ファイルのファイル位置は、サーバー側とクライアントの設定を同じにしないと接続できなくなる。

Edit
ユーザーの作成と削除

ユーザー作成には必ず GRANT構文を利用する。またユーザー名の変更はできない。変更する場合はユーザーを削除してから再度作成する。

Edit
ユーザーを作成。

<パスワード無しユーザー作成>

mysql> GRANT ALL PRIVILEGES ON *.* TO user;

<ホスト制限ありユーザー作成>

mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost;

<ホスト制限+パスワード指定あり>

mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'pass';

<DB制限+パスワード指定>

mysql> GRANT ALL PRIVILEGES ON mydb.* TO user@localhost IDENTIFIED BY "pass";

<管理者ユーザー(GRANT権利の付与)の作成>

mysql> GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION;

<セレクトのみ許可>

mysql> GRANT SELECT ON *.* TO user;

Edit
ユーザーの削除

<ユーザーを削除:ホスト名も付けること>

mysql > DROP USER user@localhost;

<特定のホストからのユーザーを削除:非推奨>

mysql > DELETE FROM mysql.user WHERE user='user' AND host='localhost';

※DROPを使わない場合、mysql.db などユーザーにひも付けされている権限情報との整合性にトラブルが発生する。

<匿名ユーザー削除> 必ずやっておくこと。

mysql > delete from mysql.user where user='';
mysql > drop database test;  <-- ついでに不要なテストDBも削除

<全ユーザーの削除:※注意!rootも削除される

mysql > TRUNCATE TABLE mysql.user;

Edit
パスワードの変更

mysql > SET PASSWORD FOR user@localst=PASSWORD('pass');

※パスワードを '' にしたら、パスワード削除

Edit
登録ユーザーの簡易一覧

表示したい項目にあわせて項目名を追加。

mysql> SELECT user,host,password,Grant_priv FROM mysql.user;

Edit
接続中ユーザーの簡易一覧

mysql> SELECT * FROM information_schema.processlist WHERE id = CONNECTION_ID();

Edit
権限の付与・剥奪・確認

ユーザの権限認証は、mysql.user -> mysql.db ->mysql.tables_priv -> mysql.columns_priv の順で認証される。上位で許可されている場合は、下位の認証は無視される。またユーザーはアクセス可能ホストによって複数レコードが、mysql.user に作られるように、アクセスホストによって、細かく権限を設定することができる。

Edit
権限の付与

<指定データベースへのアクセス付与>

mysql > GRANT ALL PRIVILEGES ON mysql.* TO user;

<特定の操作のみの権限付与:下記の場合は参照と更新のみ許諾>

mysql> GRANT SELECT,UPDATE ON *.* TO user;

<ユーザーを管理者に変更>

mysel > GRANT ALL PRIVILEGES ON *.* TO user@localhost WITH GRANT OPTION;

※ユーザー名とホスト名が合致していないと新レコードが追加されてしまうので注意。

Edit
権限の剥奪

<すべてのアクセス権限の破棄>

mysql > REVOKE ALL PRIVILEGES ON *.* FROM user@localhost;

<特定のデータベースのアクセス権限の破棄>

mysql > REVOKE ALL PRIVILEGES ON mysql.* FROM user@localhost;

<特定の操作権限の破棄>

mysql > REVOKE SELECT,UPDATE ON *.* TO user@localhost;

※REVOKE構文は基本的に DELETE構文と同じで存在しているレコードを削除する機能であり、判断を必要とする複雑な設定はできない。
(例:DBが、db1, db2, db3 と3つある場合に)

mysql > GRANT ALL PRIVILEGES ON *.* TO user;                <- 全部DBアクセス許可
mysql > REVOKE ALL PRIVILEGES ON db1.* FROM user;     <- db1だけ不許可(これはエラーになる)

これは、mysql.db に user が db1へのアクセスを許可するレコードが存在しないためである。全体への許可は、上位の mysql.user で管理許可されているため、下位の権限テーブルには許可レコードが明示されないためである。上記内容を実現したい場合には、

mysql > REVOKE ALL PRIVILEGES ON *.* FROM user;           <- 全DBを不許可にして
mysql > GRANT ALL PRIVILEGES ON db2.* TO user;
mysql > GRANT ALL PRIVILEGES ON db3.* TO user;             <- 個別のDBを許可する。

Edit
権限の確認

ユーザーを指定しない場合はログインしているユーザーの情報表示

mysql > SHOW GRANTS FOR user;

※表示されたときに末尾に WITH GRANT OPTIONが付いている場合は権限付与が行える管理者ユーザー。

Edit
その他の情報表示

<テーブルの項目表示>

mysql > DESCRIBE db_name.tables_name;

<コメント込みの項目全部表示>

mysql > SHOW FULL COLUMNS FROM db_name.tables_name;

Edit
mysql のroot パスワードを忘れた。

パスワードリセットの為には、mysql をパスワードファイルを参照させずに起動させる。やり方としては、my.cnf の設定ファイルに以下を追加する。

[mysqld]
   skip-grant-tables

Edit
その1

上記のパスワードを無視する設定をして起動後

# mysql -u root mysql                                                       <- root でmysqlDBに接続する。
 mysql> UPDATE user SET Password=PASSWORD('newpasswd') WHERE User='root';   <- newpasswd に新しいパスワード。
 mysql> FLUSH PRIVILEGES;

Edit
その2

# mysql mysql
mysql> update user set Password=null where Host='*' and User='root';
mysql> exit;

パスワード無視の設定を解除し、通常モードで起動後

Edit
MySQLの起動

# chkconfig mysql on
# service mysqld start
# mysqladmin -p status              <- 起動チェック

※mysql.sockが何やら言って来たら、すでにmysqlが立ち上がっている場合が多い。一度、すべてのDaemonをKillしてから再度行うと上手くいく場合がある。

Edit
MySQLを使ってみる。

登録されているデータベースの一覧表示

mysql> show databases;

操作するデータベースの変更(上記で一覧表示されたものが<database-name>となる)

mysql> use <database-name>;

操作しているデータベースに登録されているテーブルの一覧表示

mysql> show tables;

テーブルの項目名などの詳細を表示する。(2つとも同じ結果になる)

mysql> describe <table-name>;
mysql> show columns from <table-name>;

テーブルの中のレコードの中身をすべて表示(件数が多いとまともに見れない)

mysql> select * from <table-name>;

Edit
バイナリログの一覧を確認

mysql> SHOW MASTER LOGS;

Edit
バイナリログの内容を確認する。

# mysqlbinlog --no-defaults mysql-bin.000001

※注意:このログでは、画像などのバイナリデータがあると、そのまま出力するので、コマンドライン上などで文字化けしたり、停止したりする。

Edit
バイナリログの自動削除を設定

# vi /etc/my.cnf
  [mysqld]
  ...
  expire_logs_days = 5  <- 5日以前のログ削除。スレーブ同期は無いのでゆとり設定
  ...

設定の確認

mysql > SHOW GLOBAL VARIABLES like 'expire_logs_days';

Edit
バイナリログの強制削除

mysql> SHOW MASTER LOGS;                         <- ファイル名を確認
mysql> PURGE MASTER LOGS TO 'mysqld-bin.000014'  <- 14番以前を削除

Edit
phpMyAdmin

次に、サーバーが無事起動できたら、apache+phpで起動する管理者ページを作成して管理をできるように設定する。phpMyAdminの公式ページはここ

phpと、mysqlをつなげるライブラリをインストール

# yum install php-mysql

Edit
インストール

# cd /usr/local/apache2/htdocs/    <- できればSSLと認証の掛かっている場所に。
# wget http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/4.2.10.1/phpMyAdmin-4.2.10.1-all-languages.tar.gz
# tar zxvf phpMyAdmin-4.2.10.1-all-languages.tar.gz
# mv phpMyAdmin-4.2.10.1 phpMyAdmin
# cd phpMyAdmin
# mkdir config
# chown apache:apache config
# mkdir /var/lib/php
# mkdir /var/lib/php/session
# chmod -R 777 /var/lib/php
# cp config.sample.inc.php config.inc.php

インストールしたフォルダをapacheを通してアクセスし最終的な設定を完了させる。

http://localhost/phpMyAdmin/setup/

Edit
config.inc.phpの設定

項目名機能設定例
auth_type認証方式の指定cookie, http
hostホストの指定localhost
connect_typeMySQLへの接続方法tcp, socket
compress通信に圧縮を利用するかtrue, false
extension拡張コマンドの種類mysqli, mysql
porttcp接続の場合のポート番号3306

Edit
pmaデータテーブルの追加

# mysql -u root < examples/create_tables.sql
# mysql -u root
mysql> grant all on phpmyadmin.* to pma@localhost identified by "password";

※この場合、config.inc.conf のパスワード名も、このパスワードに合わせておくこと。

Edit
バージョンアップ

# config.inc.php をコピー

Edit
PHPのチェック

PHPのコンパイル時に、MySQLオプションの指定が無い場合、起動しない。PHPのコンパイル時のオプションは下記の通り。

# ./configure --with-mysql=/usr/local/mysql

※ここでは他のオプションは割愛しているので、自分の環境によって他のオプションは付けられたし。

Edit
php.iniの修正も忘れない。

phpMyAdminはphpを利用してMySQLにアクセスするが、phpinfo(); 関数に表示されている、php.ini ファイルには、mySQLに関する設定ファイルが存在する、このファイルの

mysql.default_socket =
mysqli.default_socket = 

という行に、/etc/my.cnf で設定した socket ファイルと同じファイルを指定しないと正常に動作しない。

Edit
起動確認

apacheのドキュメントフォルダに設置したphpMyAdminのページをブラウザを使って表示させると起動確認ができる。

例: http://localhost/phpMyAdmin/

Edit
トラブルシューティング

Edit
Error on rename(#1025)など、名前変更や削除ができなくなる。

テーブルの項目に、外部キーが設定されていると操作ができなくなるので、SQLコマンドで外部キーを削除する。まずは外部キーの名前を取得するため、以下のコマンドを実行する。

mysql> SHOW CREATE TABLE job_type;
     CREATE TABLE `job_type` 
     ......
         ?CONSTRAINT `job_type_ibfk_1` FOREIGN KEY (`job_type_id`) REFERENCES `job_type` (`job_type_id`)
     .......

ここに出てきた、job_type_ibfk_1 というのが外部キーになる。このキーを削除するため、以下のコマンドを実行。

mysql> ALTER TABLE job_type DROP FOREIGN KEY `job_type_ibfk_1`;

Edit
mysqld起動時には、前のDBが残っていない状態とする。

前のDBが残っている場合、mysql_upgradeを行えと指示が出るが、新規でのインストールであれば、データフォルダのデータは消してから起動した方が早い。

Edit
Access denied for user 'root'@'localhost' (using password: NO)というエラー発生時

インストールしたての場合は、mysqlのrootユーザーにパスワードが設定されていない。そのため、以下の手順でパスワードを設定する。

# service mysqld stop
# mysqld --skip-grant-tables &
#(1回リターンキーを押すと再度プロンプトがでる)
# mysql -u root
mysql> use mysql;
mysql> select * from  user;
mysql> truncate table user;
mysql> flush privileges;
mysql> grant all privileges on *.* to root@localhost identified by 'パスワード' with grant option;
mysql> flush privileges;
mysql> select host, user from user;
mysql> quit;

以後、変更削除できるようになる。

Edit
どうしても mysql に、php からアクセスできない。

SELinux を切ってみる。

Edit
MySQLにおける注意点

Edit
auto increment指定の項目について

auto_increment指定されている項目にデータ0を設定することはできない、auto_increment指定の場合、0は特別な数字となっており、仮に0を指定すると、一番大きい番号の後ろに新レコードとして追加される。

Edit
データベース設定における照合順序

utf8_unicode_ci タイプにおける識別とは、「あ」に対する「ぁ」はもちろんの事、「は」に対する「ば」「ぱ」などの濁点、半濁点、「ハ」や「ハ」などのカタカナや半角カタカナも同一文字として検索時に処理される。

最初の文字列の意味
utf8UTF-8コードとして認識する
2番目の文字列の意味
binバイナリーとして比較する。そのため、「A」と「a」は識別される。
general全角と半角の英数字は同じとみなさない。これはバイトコード比較のため複数バイトを持つ同一文字を認識できない。例:「A」と「a」は識別されない(_ciの場合、同一文字とみなす)が、「A」「A」は識別される。
unicode全角と半角の英数字も同じとする(_ciの場合、文字コード比較)。例:「A」「a」「A」はすべて同じ文字として処理される。
最後の文字列(2番目がBINの場合は存在しない)
ciCase Insensitive 大文字小文字を区別しない
csCase Sensitive 大文字小文字を区別する。


注)ドイツ語などの2文字が同じものを示すような場合において、utf8_unicode_ciでは"ß" = "ss"に対して、utf8_general_ciでは"ß" = "s"と、間違った結果になる。

Edit
項目の型について

Edit
数字の型

バイト最小値最大値備考
TINYINT1-128127bool型はこれを使う
SMALLINT2-3276832767C言語のShort型、16bitCPU時のint型
MEDIUMINT3-83886088388607
INT4-21474836482147483647いわゆる、C言語のlong や 32bitCPU時のint型
BIGINT8-92233720368547758089223372036854775807C言語のlonglong型

※int(11)など、括弧で数字が指定されている場合は、上記数字型の場合に限り「表示上」の桁数を指定する機能になっている。指定がなければ、その型の最大桁+1(マイナス記号用)が指定される。

型の装飾できる内容

装飾式内容
auto_incrementレコードが作成されたときに、自動的に連番をつける項目
NOT NULLNULLを指定できない項目
default NULL初期値がNULLの項目
unsignedマイナスの範囲を取らない項目
zerofill括弧で指定された桁分を0で埋めるようにする項目

Edit
日付の型

概要
date日付のみ
datetime日付と時間の両方
timestampこの項目を設定すると自動的にレコード作成時間が記録される。同時にデフォルト値に、CURRENT_TIMESTAMPと明記される
time時間のみ
year西暦の年のみ

Edit
トラブルシューティング

Edit
ステータスを表示する。

# mysqladmin extended-status

Edit
現在ログインしているユーザーを確認

mysql > select current_user();

Edit
InnoDB: Cannot allocate memory for the buffer poolのエラーが出て起動しない。

メモリ不足の為、メモリを増やすか、スワップ領域を増やす。(下記では4GB増やしている)

# dd if=/dev/zero of=/swapfile bs=1M count=4096
# mkswap /swapfile
# swapon /swapfile
# swapon -s                  <- 確認用

各プロセスのメモリ利用量

# ps -xl --sort -vsize | head

または、設定を変更

# vi /etc/my.cnf
 [mysqld]
 table_definition_cache=400             <- 1400 から 400 に。
 performance_schema=off                <- 基本OFFだが念のため。

Edit
SSL接続でエラー

Edit
my.cnfの確認

# vi /etc/my.cnf
 [mysqld]
 ...
 ssl-ca=/etc/mysql-ssl/server.ca
 ssl-cert=/etc/mysql-ssl/server.crt
 ssl-key=/etc/mysql-ssl/server.key
 
 [client]
 ...
 ssl
 ssl-cipher=DHE-RSA-AES256-SHA

Edit
Unable to get private key from

サーバーのkey ファイルが、rsa 形式になっていない。

# openssl rsa -in server.key -out server-rsa.key

再起動後、確認

mysql > SHOW VARIABLES LIKE '%ssl%';

Edit
utf8は使わず、utf8mb4 を使う。

utfmb4 とは、MySQL専用の文字コードで、4バイト utf にも対応した文字セット(そもそもutf8では、1-4バイトのコードなので、4バイトに対応しているのが標準だが、MySQLでは分かれている)。絵文字などが入力されると、utf8 ではエラーになって処理が止まる。そのため、互換性を考えると、MySQLで utf8 を使う場合、すべて utf8mb4 であると文字コードトラブルがない。

Edit
リプリケーションサーバーの立ち上げ

リプリケーションは、反復や複製の意味を持つ単語であり、MySQLでもリプリケーションサーバーとは、マスターDBを複製しスレーブDBとして稼働することを意味する。またMySQLのリプリケーション機能には下記の特徴がある。

<メリット>

<デメリット>

<結論>

Edit
マスタースレーブ共通の設定

Edit
my.cnfの確認

[mysqld]
log-bin
server-id=1

上記の設定が漏れていないか確認
※但し、server-idは、各サーバー毎にユニークにする必要がある。

Edit
Master側の設定

Edit
マスター側に、リプリケーション用アカウントの作成

mysql > GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.100.%' IDENTIFIED BY 'pass';

※データを丸々コピーする強力なアカウントなので極力IP制限を掛けたほうが良い。

Edit
マスター側のデータの初期コピー

<データベースのロック>

mysql > FLUSH TABLES WITH READ LOCK;          <- コピーの為、テーブルロック。
mysql > SHOW MASTER STATUS;                          <- バイナリログの状態を確認(ロック後)
mysql > SHOW BINLOG EVENTS;                            <- バイナリログの中身確認

<マスター側のデータ領域をスレーブ側にコピー>

# cd /usr/local/mysql
# tar zcvf data.tar.gz data

<圧縮ファイル(スレーブDB用データフィイル)が完成後、マスターDBのロックを解除>

mysql > UNLOCK TABLES;                                      <- テーブルのアンロック(解除)

Edit
マスターの状態確認

mysql > show variables like '%gtid%';

Edit
スレーブ側の設定

基本的にスレーブDB側は複数のDBを起動できる。

Edit
マスターデータの展開

上記のマスター設定の中でコピーしたデータファイルを、展開する。

# cd /usr/local/mysql
# rm -rf data                                                         <- 注意!元のデータが全部消えます。
# tar zxvf data.tar.gz
# rm -f data/auto.cnf                                            <- 中の server-uuid がマスターと重複してしまうので削除(自動で再作成される)。

Edit
スレーブモードに切り替える。

mysql > CHANGE MASTER TO MASTER_HOST='192.168.111.107',     <- Masterのホスト名/IPアドレス
       MASTER_USER='rep',                                                                        <- Master接続に使用するユーザー名
       MASTER_PASSWORD='password',                                                   <- Master接続に使用するユーザーのパスワード
       MASTER_LOG_FILE='www-bin.000001',                                           <- Masterで、SHOW MASTER STATUS; した時のファイル名
       MASTER_LOG_POS=120;                                                                  <- Masterで、SHOW MASTER STATUS; した時のPosition番号

※コピペ用:CHANGE MASTER TO MASTER_HOST='www.mydomain.com', MASTER_USER='rep', MASTER_PASSWORD='pass', MASTER_LOG_FILE='www-bin.000001', MASTER_LOG_POS=120;

Edit
スレーブの起動

mysql > START SLAVE;                                           <- スレーブの起動
mysql > SHOW SLAVE STATUS;                             <- 現在のスレーブ状況の確認

Edit
GTIDの設定

MySQL 5.6.9 移行に使えるようになった。トランザクションを絶対値にすることで、トランザクションの管理が楽になる機能。作った本人のブログページ

Edit
my.cnfの設定

GTIDを導入するには設定ファイルに下記の2つを追加する。

# vi /etc/my.cnf
  [mysqld]
  gtid-mode=ON
  enforce-gtid-consistency

Edit
最後のSQLのGTIDの確認

mysql > SELECT @@GLOBAL.GTID_DONE;

Edit
運用について

Edit
データベースのバックアップ

# mysqldump -u root -phogehoge --databases mydatabase > mysql_`date +%Y%m%d-%H%M%S`.sql
option概要
-uログインユーザー名を指定-u root
-pパスワード指定-phogehoge
-hホスト名指定-h localhost
--databases対象データベース名の指定--databases mydatabase
--default-character-setキャラクターセットの指定--default-character-set=utf8

※注)-pの後にパスワード入力する場合には、スペースを入れない。

Edit
データベースのリストア

# mysql -u root -phogehoge < mysql.sql

※Database名を指定しても、ダンプファイルの先頭にダンプ時のDB名が入っていると、データベース名をコマンドラインから指定しても意味がなくなる。

Edit
バイナリログ(トランザクションログ)

MySQLバイナリログの仕様

Edit
利用できるホストを追加する。

mysql> grant all privileges on *.* to username@"new.server.com";
mysql> FLUSH PRIVILEGES;

Edit
他のバックアップ

OSのLVMやZFSのスナップショット機能を使ったり、InnoDB Hot Backupを使ったバックアップ方法もあります。

Edit
コマンドラインでのmysqlコマンド

外部の mysql に接続

# mysql -u user -h www.mydomain.com -ppassword     <-  -pとパスワードの間にスペースは入れない。

Edit
一覧表示

データベースの表示

mysql> SHOW DATABASES;
mysql> SHOW DATABASES LIKE 'ca%';      <- 頭がcaの一覧表示

データベースの切替

mysql> use データベース名

データベースの追加

mysql > CREATE DATABASE データベース名 CHARACTER SET utf8;

テーブルの一覧

mysql> SHOW TABLES;
mysql> SHOW TABLE STATUS;         <- 詳細データ付きの一覧データ

Edit
CSVファイルの読み込み

# mysql --local-infile -u root -p         <- ローカルファイルアクセス許可
mysql > LOAD DATA LOCAL INFILE "./test.csv"  REPLACE INTO TABLE 'tablename' FIELDS TERMINATED BY ',' ENCLOSED BY '"' (name, age, @dummy, address);   <- 項目がcsvのどのような順番にならんでいるか。

※ファイルの改行コードは、Linux用(LF)を利用すること。文字はUTF-8で。

Edit
MySQL関係

MySQL 公式ページ
日本MySQLユーザ会
MySQL to ODBC
i386用ダウンロードページ
MySQLの小技

Edit
SQLコマンド

Edit
集計・平均・極値の取得

mysql> SELECT item_id, SUM(price) FROM `payment` GROUP BY item_id ORDER BY sum(price) DESC;
GROUP BY と対になる、このSUMの部分にとり得るコマンド
SUM合計値
MAX最大値
MIN最小値
AVG平均値
COUNTデータの個数

※GROUP BY とORDER BYを1行にいれる場合は、ORDER BY を後ろにしないとエラーになる。
詳細説明ページ

Edit
後から複数のカラムにUNIQUEを設定する

ALTER TABLE テーブル名 ADD UNIQUE (カラム名,カラム名);

Edit
後から複数のカラムにPRIMARY KEYを設定する

ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名,カラム名);

Edit
後から外部キーを設定する。

ALTER TABLE テーブル名 ADD FOREIGN KEY (カラム名) REFERENCES 参照テーブル名 (参照カラム名);

Edit
テクニック

Edit
utf8_general_ciとutf8_unicode_ciの違い

照合順序概要
utf8_general_ciアルファベットの大文字小文字は区別しない。全角・半角は区別
utf8_unicode_ci大文字・小文字・全角・全角カタカナ・半角・半角カタカナを区別しない。例:「AAA」「aaa」「AAA」「aaa」は同じ。「あああ」「アアア」「アアア」も同じ。

utf8_general_ci の照合順序のテーブルで、utf8_unicode_ci の全角半角の区別無し検索をさせたい時、select句に、collate utf8_unicode_ci を追加する。但し当然遅くなる。