- 追加された行はこの色です。
- 削除された行はこの色です。
-日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始。Linux界隈ではMariaDBに分離してソースレベルで別れたのでどうしましょ（今の所MariaDBのみの機能を使っていないので戻れるが）
#contents
*Percona(5.7)移行 [#y2ff26d0]
**install [#odfb9a2c]
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
**trouble [#te5162ed]
-mysqlのgeneral_logの定義でエラー！
-wordpressで「お使いのサーバーの PHP では WordPress に必要な MySQL 拡張を利用できないようです」
*オンラインDDL変更 [#cd1b7d48]
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メモ [#ab0d657d]
-シーケンスが上限に達するとエラーになる！
-Heap テーブルスペースはメモリ上に展開する領域。一時テーブルにどうぞ！
-safe_updateのオプションを有効にするとwhereなしupdateなどに確認が入る
-TIMESTAMP型はdefault値を指定しないと、更新時に勝手に時刻が入るので注意。
-Oracle DBLink=Federated Table
**便利な関数 [#vc92d783]
-DATEDIFF
-group_concat
-group by RAND()
-SELECT USER()
**便利なツール [#i12ccc72]
-mysql workbench(移行にも)
-mysqlsh(SHELLやJS,Python)
**セキュリティのために [#v5159d43]
-unixアカウント連携
-rootで動かさない
-FILE権限は最小限に
-接続数の上限
*binログからの復旧 [#jebaaf2d]
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分後でも平気)ので救われた。
*文字コード [#c8b6524f]
PostgreSQL等と違ってテーブルクリエート時に指定できないのだろうか？/etc/my.cnfをいちいち書き換えないと駄目なのか？
-mysqlのコンソールの状態でも見ることができる。
mysql> status
-以下のコマンドを打ち込むと現状が見える。
SHOW VARIABLES LIKE 'character\_set\_%';
-テーブル作成の時に指定する(5.0で確認）
create database codezine default charset utf8;
-テーブル作成時の文字コードをみる
show create database データベース名
**クライアント側の設定 [#fa9032fd]
通常のmysqlで実行される文字コードは初期設定だとlatin1。これで文字化けしないわけがないので適宜文字コードを設定しておく。
mysql> status;
mysql> set character set utf8;
**mysql.iniでの文字コードの設定 [#hb3b0d76]
デフォルトのcharsetを設定ファイルに記載しておくと手間が省ける
[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード
**接続ログを出したい [#p59c8f97]
-/etc/my.cnf
[mysqld]
general_log=1
general_log_file="/var/log/mysql_general.log"
*最大接続数・現在の接続数を確認したい [#hdb813c0]
show global variables like '%connection%';
show status like 'Threads_connected';
**ERROR 1040 (08004): Too many connectionsがでて繋げない場合はnetstatにて [#q9235c36]
netstat -anp | grep 3306
**デフォルト接続設定 ~/.my.cnf [#g2ec61cb]
[client]
host=localhost
database=mysql
user=root
password=hogehoge
*管理ツール [#i83ef508]
Windowsで出来るGUIツールを開発元が配布しているのが心強い。MySQL AdministratorやMySQL Query Browser等がある。
*データベース作成、ユーザ作成などの主に管理系タスク [#a52a84e0]
**データベース [#x0dddb01]
***mysqlのコマンドラインから [#w8b16c78]
文字コードを指定しないとlatin1になってしまう。
|DB作成|mysql> create database wordpress default character set utf8;|
***コマンドラインから [#g6e08aac]
|DB一覧|mysqlshow|
|DB作成|mysqladmin -u root create TEST|
|DB削除|mysqladmin -u root drop TEST|
|オートコミット確認。0以外はオートコミット|SELECT @@autocommit;|
|オートコミット無効|SET AUTOCOMMIT=0;|
|ファイルの実行|mysql -u root -phogehoge < sql.sql > out.txt|
|mysqlクライアントから実行|mysql>source sql.sql|
**ユーザ作成、権限付与 [#g5bf94f7]
ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス元ホスト名分だけユーザが存在すると考えておいた方がよい。ワイルドカードで指定する事も出来るが、localhostとその他のホストは分けることが出来ない。自宅サーバなら殆どlocalhostのユーザだけで事足りるのだろうが、他の場所からアクセスさせるなら要注意。
*コマンド集 [#sc19b022]
|インポート|load data infile 'c:/ken_all.csv' into table テーブル名|
|エクスポート|select * from テーブル名 into outfile '絶対パスでファイル名'|
|データベース毎の使用量|select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables group by table_schema order by sum(data_length+index_length) desc|
|DB接続|mysql -u root -p DB_NAME|
|ユーザ作成|mysql> grant select,insert,delete,update,create,drop,file,alter,index on *.* to ユーザ名 identified by 'パスワード';|
|バッチ処理|mysql -u root -p DB_NAME < sql.txt|
|バッチ処理 on シェル|mysql> source ファイル名|
|操作ログをファイルも記録|mysql> \t ログファイル名|
|TABLE一覧|SHOW TABLES;|
|プロセスの確認|SHOW processlist|
|DBダンプ|$ mysqldump -u root データベース名 > dump.sql|
|列追加|ALTER TABLE テーブル名 ADD フィールド名 varchar(200) not null [DEFAULT 0] [AFTER <カラム名>];|
|列削除|ALTER TABLE テーブル名 DROP フィールド名|
|シーケンスを任意の値に|ALTER TABLE tbl AUTO_INCREMENT = 100;|
|曜日取得(日曜1-土曜7)|SELECT DAYOFWEEK(now());|
|曜日取得(月曜0-日曜6)|SELECT WEEKDAY(now());|
*テーブル構造やデータ型 [#x177f3ed]
**Tableの形式 [#v6a964eb]
-InnoDB
--トランザクション対応
-MyISAM
--トランザクション非対応で速度重視。
-INNODB形式でテーブルを作成するサンプル
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) engine=InnoDB;
**データ型 [#r35b4386]
-CHAR,VARCHARは4.1以降文字数。それ以前はバイト数なので日本語扱うときは注意
-最大以上のサイズを入れた場合、はみだし分が切り落とされるだけでエラーにはならないので注意。数値型のデータも同様
**順序型 [#w169db1c]
-フィールドにAUTO_INCREMENTをつける。ただしひとつのテーブルに付ひとつまで
**順序に任意の値を設定 [#pacf79b9]
alter table table_name auto_increment=12345;
*注意点 [#kea134ff]
**データベース名やテーブル名は大文字小文字を区別する。 [#y60b9d30]
これはOSが大文字小文字区別するならば、DBとdbは別のデータベースとして取扱われる。テーブルに付いても同様である。WINDOWSのように区別しないOSの場合はその心配はない
*総使用量 [#m38311bf]
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管理ツール [#d0d01049]
**[[phpMyAdmin:http://www.phpmyadmin.net/home_page/index.php]] [#kea3dc6a]
WEBからDB管理ができてしまうというツール。日本語化されていて、解凍即実行可能。海外ではよく使われているのかphpmyadminへの不正アクセスが多数・・・・
PHP 4,5.1では2.9系、PHP 5.2では3.x系を利用できる。
CentOS5では5.1.6なので2.xを使う。
その他MySQLのバージョン指定もかなり厳しくCentOSを使っているなら古いバージョンを使ったほうがよい。
-インストール
+解凍する。3.x系ならアクセスすれば自動で設定画面に飛ぶと思った。
-設定
config.inc.phpでAllowNoPasswordをtrueにしないと、パスワードなしログインはできない。
*インポート、エクスポート [#a221f3ad]
**CSVインポート [#n9393c5b]
-デリミターを指定しない場合はタブ区切りとなる。
-日本語関係のフィールドをロードする場合はクライアントの文字コードをセットしておいた方が良いかも。
LOAD DATA LOCAL INFILE 'car_import.csv' INTO TABLE テーブル名 FIELDS TERMINATED BY ',';
-フィールドを指定する場合
LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
-autoincrement列を除外する場合はその列を除外すれば自動的に連番がふられる（insert分でも同様)
LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE test (col1);
*バックアップ&リカバリー [#e852c036]
**エクスポート [#e41d46fb]
標準出力にでるのでリダイレクトで保存すること。以下のコマンドを追加して、圧縮しつつバックアップを取るとよい。
| gzip > dump.sql.gz
|単位|コマンド|備考|
|テーブル|mysqldump -u root -p --add-drop-table DB名 テーブル名|drop tableを入れるオプション付き|
|データベース|mysqldump -u root -p DB名|dropテーブルは自動で入るようなので、空じゃないDBに入れるときは既存データなくなる！|
**テーブル単位 [#la55d36c]
-主キーがかぶらないようにdrop tableして作り直す構文つきで出力
mysqldump -u root -p --add-drop-table DB名 テーブル名 > /var/tmp/rooms.sql
-戻すときはデータベースの指定だけでOK
mysql -u root -p DB名 < /var/tmp/rooms.sql
**データベース単位 [#lf547451]
***バックアップ [#b7780581]
-バックアップ対象はデータベースmysqlとし、出力ファイル名はmysql.sqlとする。
mysqldump -u root -p データベース名 > mysql.sql
-危険を承知でパスワードをコマンドに入れてしまう場合はこちらで
mysqldump -u root --password=パスワード データベース名 > mysql.sql
-圧縮しつつバックアップは以下の通り
mysqldump -u root -p データベース名 | gzip > mysql.sql.gz
***リカバリー [#t7f42718]
-リカバリー対象データベースはcreateしておく。
mysql> create database mysql2;
mysql> exit
mysql -u root -p mysql2 < mysql.sql
-圧縮したもののリカバリーは以下の通り
zcat mysql.sql.gz | mysql -u root -p データベース名
**全部のデータベース [#k87164b0]
***バックアップ [#bfdcb4d8]
-全部のデータベースを出力
mysqldump -u root -x --all-databases > dump.sql
***リカバリ [#d9c135f3]
-全部のデータベースをインポート(もとあるＤＢは名前がかぶらなければ残る）
mysql -u root -p < dump.sql
-リカバリーした後は''rootでログインして、flush privilegesすること''。さもないと移行されたユーザでログインできない。もしくはMySQLの再起動で反映される。
*トラブルシューティング [#y36815c1]
**文字コード間違えてテーブル作ってしまった。 [#c3f56127]
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
**パスワードを忘れて再設定したい [#a54a1c1d]
起動引数に--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 [#s3050dca]
**パスワード忘れた [#j79ba2da]
-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 root@localhost identified by "パスワード";
これでパスワード認証を求められる
mysql -u root -p xoops
/var/lib/mysql/mysql以下を全部消してinitする
**SQLファイル、Javaからの実行時のみ文字化け [#u4a16b93]
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\_%';
-ただし上記は一時的にそのセッションだけ変更するので、再起動すると元に戻る。
#counter
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
