トップ | リロード | 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ

MySQLについて †

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

MySQLの導入 †

インストール †

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

# 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で設定できる。詳細はこちら

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

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

＜ユーザー追加＞

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

＜ユーザー削除＞

# userdel mysql

サービスの登録 †

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

Libraryの登録 †

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

必要フォルダの作成 †

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

定義ファイルの設定 †

定義ファイルの参照順番（初期値） †

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

参考定義ファイル †

# 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アドレスは１つだけ指定可能。指定無しだと制限無し。
  #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）は同じ名前だと問題がおこるため注意が必要。（上記設定を参照）

mysql.sockについて †

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

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

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

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 を総洗いしたほうが良い。

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

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

mysql > SET GLOBAL sql_mode = '';

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

mysql > SELECT @@GLOBAL.sql_mode;

iptablesの開放 †

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

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

DBの初期化 †

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

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

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

ユーザー管理 †

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

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

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

注意点 †

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

mysql > FLUSH PRIVILEGES;
# service mysqld reload

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

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

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

mysqlコマンドについて †

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

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

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

ユーザーの作成と削除 †

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

ユーザーを作成。 †

＜パスワード無しユーザー作成＞

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;

ユーザーの削除 †

＜ユーザーを削除：ホスト名も付けること＞

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;  <-- ついでに不要なテストＤＢも削除

＜全ユーザーの削除：※注意！rootも削除される＞

mysql > TRUNCATE TABLE mysql.user;

パスワードの変更 †

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

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

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

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

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

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

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

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

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

権限の付与 †

＜指定データベースへのアクセス付与＞

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;

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

権限の剥奪 †

＜すべてのアクセス権限の破棄＞

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 と３つある場合に）

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を許可する。

権限の確認 †

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

mysql > SHOW GRANTS FOR user;

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

その他の情報表示 †

＜テーブルの項目表示＞

mysql > DESCRIBE db_name.tables_name;

＜コメント込みの項目全部表示＞

mysql > SHOW FULL COLUMNS FROM db_name.tables_name;

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

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

[mysqld]
   skip-grant-tables

その１ †

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

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

その２ †

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

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

MySQLの起動 †

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

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

MySQLを使ってみる。 †

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

mysql> show databases;

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

mysql> use <database-name>;

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

mysql> show tables;

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

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

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

mysql> select * from <table-name>;

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

mysql> SHOW MASTER LOGS;

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

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

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

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

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

設定の確認

mysql > SHOW GLOBAL VARIABLES like 'expire_logs_days';

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

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

phpMyAdmin †

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

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

# yum install php-mysql

インストール †

# 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/

config.inc.phpの設定 †

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 のパスワード名も、このパスワードに合わせておくこと。

バージョンアップ †

# config.inc.php をコピー

PHPのチェック †

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

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

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

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

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

mysql.default_socket =
mysqli.default_socket = 

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

起動確認 †

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

例: http://localhost/phpMyAdmin/

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

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`;

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

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

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

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

# service mysqld stop
# mysqld --skip-grant-tables &
#（１回リターンキーを押すと再度プロンプトがでる）
# 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;

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

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

SELinux を切ってみる。

MySQLにおける注意点 †

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

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

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

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

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

項目の型について †

数字の型 †

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

型の装飾できる内容

日付の型 †

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

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

# mysqladmin extended-status

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

mysql > select current_user();

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

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

# 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だが念のため。

SSL接続でエラー †

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

Unable to get private key from †

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

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

再起動後、確認

mysql > SHOW VARIABLES LIKE '%ssl%';

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

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

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

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

• スレーブDBを立ち上げるのに、マスターDBをロック後、マスターDBのデータフォルダを丸々コピーして、スレーブを立ち上げる。
• マスターDBからスレーブDBに更新されるタイミングは非同期で、マスターDBが故障するタイミングによっては、更新データが失われる。
• Clusterと違い、データ読込はスレーブDBでも分散できるが、データ書き込みはマスターDBでしかできない。
• 正式なフェイルオーバー機能は無く、マスターDB故障時にスレーブをマスターに切り替えるには、手作業による多くのステップが必要。
• また、複数のスレーブDBが存在すると、その数に置いじてフェイルオーバー時の切替作業が比例して発生する。

＜メリット＞

• 設定ファイルの修正量が少なく、導入が容易い。
• マスター側もスレーブ側も同じMySQLで稼働するため、学習コストが低い。
• ダンプバックアップを行いたい場合、スレーブ側で行うことでマスターのリソース（CPUやバスバンド）を使わずにすむ。

＜デメリット＞

• 停止させられないサービスでは、自動フェイルオーバー対応を実装させるために、アプリ側・システム構成も含めてかなりの手間とコードをかける必要がある。
• マスター、スレーブが１対１の場合は、比較的、復旧時の手間は少ないが、スレーブの数に応じて復旧の手間が比例し、ミスも多くなりがち。
• MySQL Clusterや、Redisと違いディスクアクセスが中心な為、更新の多いアプリではパフォーマンスが悪い。
• 書き込みタイミングによっては、一部データが飛ぶ。（但しダンプバックアップよりは更新頻度の点でマシ）

＜結論＞

• 小規模なシステムでの利用向けであり、課金などのデータの重要度の高いものの場合、アプリ側で２重化の対応を行うなどの注意が必要。
• 読込が多く書き込みが少ないものが得意。

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

my.cnfの確認 †

[mysqld]
log-bin
server-id=1

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

Master側の設定 †

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

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

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

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

＜データベースのロック＞

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;                                      <- テーブルのアンロック（解除）

マスターの状態確認 †

mysql > show variables like '%gtid%';

スレーブ側の設定 †

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

マスターデータの展開 †

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

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

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

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;

スレーブの起動 †

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

GTIDの設定 †

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

my.cnfの設定 †

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

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

最後のSQLのGTIDの確認 †

mysql > SELECT @@GLOBAL.GTID_DONE;

運用について †

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

# mysqldump -u root -phogehoge --databases mydatabase > mysql_`date +%Y%m%d-%H%M%S`.sql

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

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

# mysql -u root -phogehoge < mysql.sql

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

バイナリログ（トランザクションログ） †

MySQLバイナリログの仕様

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

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

他のバックアップ †

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

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

外部の mysql に接続

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

一覧表示 †

データベースの表示

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

データベースの切替

mysql> use データベース名

データベースの追加

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

テーブルの一覧

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

ＣＳＶファイルの読み込み †

# 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のどのような順番にならんでいるか。

• ※FIELDS TERMINATED BY ',' 区切り文字指定（デフォルトは\t）
• ※ENCLOSED BY '"' は FIELDS項目の一部。文字列の囲いマークの指定
• ※REPLACE は同じレコードの場合に上書き処理する。（ほかにはIGNOREがある）
• ※Mysqlの詳細ページ
• ※@dummyのように、ダミーのユーザー変数を指定することで読み込み項目を無視できる。
• ※SET も利用できる。

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

MySQL関係 †

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

SQLコマンド †

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

mysql> SELECT item_id, SUM(price) FROM `payment` GROUP BY item_id ORDER BY sum(price) DESC;

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

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

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

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

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

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

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

テクニック †

utf8_general_ciとutf8_unicode_ciの違い †

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