![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
#author("2020-10-24T07:38:38+00:00","default:wikiadmin","wikiadmin") #author("2021-01-12T04:19:07+00:00","default:wikiadmin","wikiadmin") -日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始。Linux界隈ではMariaDBに分離してソースレベルで別れたのでどうしましょ（今の所MariaDBのみの機能を使っていないので戻れるが） #contents *サブクエリ limit [#je80204b] select * from road_times where id in ( select id from ( select id from road_times where road_id = 1 limit 10 ) as tmp ) *ツール [#abf9e455] -Adminer (1ファイル管理ツールでPHPMyAdmin対抗) *利用履歴 [#g4c42f5d] |2010-2015|MySQL 5.1| |2015-2019|Mariadb 5.5(MySQL 5.5相当)| |2018-2019|仕事場でMariaDB 10.2.9| *8.0 [#f1c1ece5] -パスワードが初回起動時に設定される。/var/lib/mysql A temporary password is generated for root@localhost: A.rr9-,mUtyX -パスワードポリシーが厳しい ALTER USER 'root'@'localhost' identified BY 'Txxxxxxx9999_'; *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 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 [#te5162ed] -mysqlのgeneral_logの定義でエラー！ -wordpressで「お使いのサーバーの PHP では WordPress に必要な MySQL 拡張を利用できないようです」 *オンラインDDL変更 [#cd1b7d48] 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 **概念 [#g058f302] -空の新テーブル作成 -トリガーを新テーブルに向ける -全データコピーが終わったら元のテーブルをリネームして、新テーブルが昇格する *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にしないと、パスワードなしログインはできない。 *トラブルシューティング [#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
