DB/MySQL

• 日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始。Linux界隈ではMariaDBに分離してソースレベルで別れたのでどうしましょ（今の所MariaDBのみの機能を使っていないので戻れるが）

サブクエリ limit †

select * from road_times where id  in (
  select id from (
    select id from road_times where road_id = 1 limit 10
  ) as tmp
)

ツール †

• Adminer (1ファイル管理ツールでPHPMyAdmin対抗)

利用履歴 †

8.0 †

• パスワードが初回起動時に設定される。/var/lib/mysql

A temporary password is generated for [email protected]: A.rr9-,mUtyX

• パスワードポリシーが厳しい

　ALTER USER 'root'@'localhost' identified BY 'Txxxxxxx9999_';

Percona(5.7)移行 †

install †

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum install Percona-Server-server-57.x86_64

pt-online-schema-change --ask-pass --alter="ADD TEST VARCHAR(100)" h=localhost,D=cakephp,t=words,u=root --dry-run

pt-online-schema-change --ask-pass --alter="add column comment2 varchar(10) not null default 'fuga'" h=localhost,D=test,t=dummy,u=root --execute

trouble †

• mysqlのgeneral_logの定義でエラー！
• wordpressで「お使いのサーバーの PHP では WordPress に必要な MySQL 拡張を利用できないようです」

オンラインDDL変更 †

MySQL 5.6からオンラインDDL変更ができるようになったが、pt-online-schema-changeも健在 Percona Toolkitに同梱

• pt-online-schema-change(旧テーブルにトリガーを作成して、ロックを防ぐ）

https://www.percona.com/downloads/percona-toolkit/3.0.10/binary/redhat/7/x86_64/percona-toolkit-3.0.10-1.el7.x86_64.rpm

概念 †

• 空の新テーブル作成
• トリガーを新テーブルに向ける
• 全データコピーが終わったら元のテーブルをリネームして、新テーブルが昇格する

Top 50メモ †

• シーケンスが上限に達するとエラーになる！
• Heap テーブルスペースはメモリ上に展開する領域。一時テーブルにどうぞ！
• safe_updateのオプションを有効にするとwhereなしupdateなどに確認が入る
• TIMESTAMP型はdefault値を指定しないと、更新時に勝手に時刻が入るので注意。
• Oracle DBLink=Federated Table

便利な関数 †

• DATEDIFF
• group_concat
• group by RAND()
• SELECT USER()

便利なツール †

• mysql workbench(移行にも)
• mysqlsh(SHELLやJS,Python)

セキュリティのために †

• unixアカウント連携
• rootで動かさない
• FILE権限は最小限に
• 接続数の上限

binログからの復旧 †

mysqlbinlog -uroot -p -D /var/lib/mysql/mysql-bin.000002 --start-datetime="2017-05-12 20:30:00" --stop-datetime="2017-05-12 20:40:00" > 2030.sql

オペレーションミスで消すというアフォなことをしてしまった。たまたまレプリケーションしてたほうがdelete反映が遅れた（60分後でも平気)ので救われた。

文字コード †

PostgreSQL等と違ってテーブルクリエート時に指定できないのだろうか？/etc/my.cnfをいちいち書き換えないと駄目なのか？

• mysqlのコンソールの状態でも見ることができる。

mysql> status

• 以下のコマンドを打ち込むと現状が見える。

SHOW VARIABLES LIKE 'character\_set\_%';

• テーブル作成の時に指定する(5.0で確認）

create database codezine default charset utf8;

• テーブル作成時の文字コードをみる

  show create database データベース名

クライアント側の設定 †

通常のmysqlで実行される文字コードは初期設定だとlatin1。これで文字化けしないわけがないので適宜文字コードを設定しておく。

mysql> status;
mysql> set character set utf8;

mysql.iniでの文字コードの設定 †

デフォルトのcharsetを設定ファイルに記載しておくと手間が省ける

[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード

接続ログを出したい †

• /etc/my.cnf

  [mysqld]
  general_log=1
  general_log_file="/var/log/mysql_general.log"

最大接続数・現在の接続数を確認したい †

show global variables like '%connection%';
show status like 'Threads_connected';

ERROR 1040 (08004): Too many connectionsがでて繋げない場合はnetstatにて †

netstat -anp | grep 3306 

デフォルト接続設定 ~/.my.cnf †

[client]
host=localhost
database=mysql
user=root
password=hogehoge

管理ツール †

Windowsで出来るGUIツールを開発元が配布しているのが心強い。MySQL AdministratorやMySQL Query Browser等がある。

データベース作成、ユーザ作成などの主に管理系タスク †

データベース †

mysqlのコマンドラインから †

文字コードを指定しないとlatin1になってしまう。

コマンドラインから †

ユーザ作成、権限付与 †

ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス元ホスト名分だけユーザが存在すると考えておいた方がよい。ワイルドカードで指定する事も出来るが、localhostとその他のホストは分けることが出来ない。自宅サーバなら殆どlocalhostのユーザだけで事足りるのだろうが、他の場所からアクセスさせるなら要注意。

コマンド集 †

テーブル構造やデータ型 †

Tableの形式 †

• InnoDB
  • トランザクション対応

• MyISAM
  • トランザクション非対応で速度重視。

• INNODB形式でテーブルを作成するサンプル

CREATE TABLE animals (
            id MEDIUMINT NOT NULL AUTO_INCREMENT,
            name CHAR(30) NOT NULL,
            PRIMARY KEY (id)
            ) engine=InnoDB;

データ型 †

• CHAR,VARCHARは4.1以降文字数。それ以前はバイト数なので日本語扱うときは注意
• 最大以上のサイズを入れた場合、はみだし分が切り落とされるだけでエラーにはならないので注意。数値型のデータも同様

順序型 †

• フィールドにAUTO_INCREMENTをつける。ただしひとつのテーブルに付ひとつまで

順序に任意の値を設定 †

alter table table_name auto_increment=12345;

注意点 †

データベース名やテーブル名は大文字小文字を区別する。 †

これはOSが大文字小文字区別するならば、DBとdbは別のデータベースとして取扱われる。テーブルに付いても同様である。WINDOWSのように区別しないOSの場合はその心配はない

総使用量 †

select table_schema, sum(data_length+index_length) /1024 /1024/1024 as GB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;

GUI管理ツール †

phpMyAdmin †

WEBからDB管理ができてしまうというツール。日本語化されていて、解凍即実行可能。海外ではよく使われているのかphpmyadminへの不正アクセスが多数・・・・

PHP 4,5.1では2.9系、PHP 5.2では3.x系を利用できる。

CentOS5では5.1.6なので2.xを使う。

その他MySQLのバージョン指定もかなり厳しくCentOSを使っているなら古いバージョンを使ったほうがよい。

• インストール

1. 解凍する。3.x系ならアクセスすれば自動で設定画面に飛ぶと思った。

• 設定 config.inc.phpでAllowNoPasswordをtrueにしないと、パスワードなしログインはできない。

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

文字コード間違えてテーブル作ってしまった。 †

ALTER TABLE  テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

パスワードを忘れて再設定したい †

起動引数に--skip-grant-tablesをつける。 centos4.8なら以下のようにする。

/usr/bin/mysqld_safe --skip-grant-tables

mysql -u root
mysql> use mysql
mysql>  UPDATE user SET Password=PASSWORD('') WHERE User='root';
mysql> flush privileges;

FAQ †

パスワード忘れた †

• 1./etc/my.cnfを編集

  [mysqld]
  skip-grant-tables

• 2.mysqlを再起動

• 3.mysqlデータベースに接続

mysql -u root mysql

• 4.以下のSQLでパスワード変更

mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
mysql> flush-privileges;

• mysqldの引数に --skip-grant-tables をつける(ちょっとめんどくさい）

  しかしこれだと固まりやがる場合があり（超不安定ＤＢ）
  mysql> grant all on *.* to [email protected] identified by "パスワード";

これでパスワード認証を求められる
mysql -u root -p xoops

/var/lib/mysql/mysql以下を全部消してinitする

SQLファイル、Javaからの実行時のみ文字化け †

mysqlコンソールからは大丈夫だが、ファイル実行させるとだめ、設定がほとんどlatin1になっていたのが原因。

MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

• Clientから利用する場合はclient,connection,resultsの３つを一致させておく。幸いにして３つを一気に変更するコマンドがある。

mysql> CHARSET utf8
Charset changed
mysql> SHOW VARIABLES LIKE 'character\_set\_%';

• ただし上記は一時的にそのセッションだけ変更するので、再起動すると元に戻る。