-日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始 #contents *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'; **デフォルト接続設定 ~/.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;| **ユーザ作成、権限付与 [#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] -全部のデータベースをインポート(もとあるDBは名前がかぶらなければ残る) mysql -u root -p < dump.sql -リカバリーした後は''rootでログインして、flush privilegesすること''。さもないと移行されたユーザでログインできない。もしくはMySQLの再起動で反映される。 *トラブルシューティング [#y36815c1] **パスワードを忘れて再設定したい [#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 をつける(ちょっとめんどくさい) しかしこれだと固まりやがる場合があり(超不安定DB) mysql> grant all on *.* to root@localhost identified by "パスワード"; これでパスワード認証を求められる mysql -u root -p xoops /var/lib/mysql/mysql以下を全部消してinitする #counter