-日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始 #contents *レプリケーション [#g7a9565f] 参考ページ https://www.server-world.info/query?os=CentOS_7&p=mariadb&f=3 http://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533 **RDSの場合はスレーブの設定がちと違う [#uee4e1c2] http://takeshiyako.blogspot.jp/2014/12/mysqlamazon-rds.html mysql> CALL mysql.rds_set_external_master('100.100.100.100',3306,'userrds','passwordrds','mysql-bin.000855',846543983,0); mysql> CALL mysql.rds_start_replication; すべてのデータベースをdumpするとmysqlデータベースインポート中にエラーになるので個別にやるしかない。databasesオプションを利用すべし! **マスターの設定 [#rc7bc224] ***ユーザーの追加(IPをスレーブのものに限定) [#i34c3211] GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.2/255.255.255.255' IDENTIFIED BY 'hogehoge'; ***マスターのmy.cnf変更して再起動 [#y0107012] [mysqld] log-bin=mysql-bin server-id=1 expire_logs_days=7 mysql-binでバイナリログを有効にして、server-idはかぶらないものをつける。expire_log_daysは7日間はバイナリログを保存する(それ以上にレプリケーションがとまるとダンプしなおし) **スレーブの設定 [#b66bdc60] [mysqld] log-bin=mysql-bin server-id=102 read_only=1 # 自身のホスト名を定義 report-host=slavehost **ダンプとリカバリー [#b313a633] ***マスター [#ka233b30] MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 22826177 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) $ mysqldump -u root -p --all-databases --lock-all-tables --events | gzip > mysql_dump.sql.gz -終わったらunlock unlock tables; -状態の確認 ***スレーブ [#d7195fc2] $ mysql -u root -p < /var/tmp/mysql_dump.sql MariaDB [(none)]> change master to master_host='160.16.63.43', master_user='repl', master_password='hogehoge',master_log_file='mysql-bin.000001',master_log_pos=22826177; -状態の確認 show slave status\G *文字コード [#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の場合はその心配はない *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