- 追加された行はこの色です。
- 削除された行はこの色です。
-日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始
#contents
*レプリケーション [#g7a9565f]
参考ページ
https://www.server-world.info/query?os=CentOS_7&p=mariadb&f=3
http://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533
**マスターの設定 [#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
$ mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql
-終わったら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