DB/MySQL の履歴(No.37)

日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始

binログからの復旧
レプリケーション
文字コード
管理ツール
データベース作成、ユーザ作成などの主に管理系タスク
- データベース
  - mysqlのコマンドラインから
  - コマンドラインから
- ユーザ作成、権限付与
コマンド集
テーブル構造やデータ型
注意点
- データベース名やテーブル名は大文字小文字を区別する。
GUI管理ツール
- phpMyAdmin
インポート、エクスポート
- CSVインポート
バックアップ&リカバリー
トラブルシューティング
- パスワードを忘れて再設定したい
FAQ
- パスワード忘れた

binログからの復旧†

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分後でも平気)ので救われた。

↑

レプリケーション†

参考ページ https://www.server-world.info/query?os=CentOS_7&p=mariadb&f=3 http://qiita.com/suzutsuki0220/items/e5be03ea8f44ad2f6533

↑

RDSの場合はスレーブの設定がちと違う†

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オプションを利用すべし！

↑

マスターの設定†

↑

ユーザーの追加(IPをスレーブのものに限定)†

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.2/255.255.255.255' IDENTIFIED BY 'hogehoge';

↑

マスターのmy.cnf変更して再起動†

[mysqld]
log-bin=mysql-bin
server-id=1
expire_logs_days=7

mysql-binでバイナリログを有効にして、server-idはかぶらないものをつける。expire_log_daysは7日間はバイナリログを保存する（それ以上にレプリケーションがとまるとダンプしなおし)

↑

スレーブの設定†

[mysqld]
log-bin=mysql-bin
server-id=102
read_only=1
# 自身のホスト名を定義
report-host=slavehost

↑

ダンプとリカバリー†

↑

マスター†

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;

状態の確認

↑

スレーブ†

$ mysql -u root -p < /var/tmp/mysql_dump.sql

MariaDB [(none)]> change master to master_host='MASTER_IP', master_user='repl', master_password='hogehoge',master_log_file='mysql-bin.000001',master_log_pos=22826177;

レプリケーションスタート

MariaDB [(none)]> start slave;

状態の確認

show slave status\G

↑

文字コード†

PostgreSQL等と違ってテーブルクリエート時に指定できないのだろうか？/etc/my.cnfをいちいち書き換えないと駄目なのか？

mysqlのコンソールの状態でも見ることができる。

mysql> status

以下のコマンドを打ち込むと現状が見える。

SHOW VARIABLES LIKE 'character\_set\_%';

テーブル作成の時に指定する(5.0で確認）

create database codezine default charset utf8;

テーブル作成時の文字コードをみる
```
show create database データベース名
```

↑

クライアント側の設定†

通常のmysqlで実行される文字コードは初期設定だとlatin1。これで文字化けしないわけがないので適宜文字コードを設定しておく。

mysql> status;
mysql> set character set utf8;

↑

mysql.iniでの文字コードの設定†

デフォルトのcharsetを設定ファイルに記載しておくと手間が省ける

[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード

↑

接続ログを出したい†

/etc/my.cnf

[mysqld]
general_log=1
general_log_file="/var/log/mysql_general.log"

↑

最大接続数・現在の接続数を確認したい†

show global variables like '%connection%';
show status like 'Threads_connected';

↑

デフォルト接続設定 ~/.my.cnf†

[client]
host=localhost
database=mysql
user=root
password=hogehoge

↑

管理ツール†

Windowsで出来るGUIツールを開発元が配布しているのが心強い。MySQL AdministratorやMySQL Query Browser等がある。

↑

データベース作成、ユーザ作成などの主に管理系タスク†

↑

データベース†

↑

mysqlのコマンドラインから†

文字コードを指定しないとlatin1になってしまう。

DB作成	mysql> create database wordpress default character set utf8;

↑

コマンドラインから†

DB一覧	mysqlshow
DB作成	mysqladmin -u root create TEST
DB削除	mysqladmin -u root drop TEST
オートコミット確認。0以外はオートコミット	SELECT @@autocommit;
オートコミット無効	SET AUTOCOMMIT=0;

↑

ユーザ作成、権限付与†

ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス元ホスト名分だけユーザが存在すると考えておいた方がよい。ワイルドカードで指定する事も出来るが、localhostとその他のホストは分けることが出来ない。自宅サーバなら殆どlocalhostのユーザだけで事足りるのだろうが、他の場所からアクセスさせるなら要注意。

↑

コマンド集†

インポート	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());

↑

テーブル構造やデータ型†

↑

Tableの形式†

InnoDB
- トランザクション対応

MyISAM
- トランザクション非対応で速度重視。

INNODB形式でテーブルを作成するサンプル

CREATE TABLE animals (
            id MEDIUMINT NOT NULL AUTO_INCREMENT,
            name CHAR(30) NOT NULL,
            PRIMARY KEY (id)
            ) engine=InnoDB;

↑

データ型†

CHAR,VARCHARは4.1以降文字数。それ以前はバイト数なので日本語扱うときは注意
最大以上のサイズを入れた場合、はみだし分が切り落とされるだけでエラーにはならないので注意。数値型のデータも同様

↑

順序型†

フィールドにAUTO_INCREMENTをつける。ただしひとつのテーブルに付ひとつまで

↑

順序に任意の値を設定†

alter table table_name auto_increment=12345;

↑

注意点†

↑

データベース名やテーブル名は大文字小文字を区別する。†

これはOSが大文字小文字区別するならば、DBとdbは別のデータベースとして取扱われる。テーブルに付いても同様である。WINDOWSのように区別しないOSの場合はその心配はない

↑

GUI管理ツール†

↑

phpMyAdmin †

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にしないと、パスワードなしログインはできない。

↑

インポート、エクスポート†

↑

CSVインポート†

デリミターを指定しない場合はタブ区切りとなる。
日本語関係のフィールドをロードする場合はクライアントの文字コードをセットしておいた方が良いかも。

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);

↑

バックアップ&リカバリー†

↑

エクスポート†

標準出力にでるのでリダイレクトで保存すること。以下のコマンドを追加して、圧縮しつつバックアップを取るとよい。

| gzip > dump.sql.gz

単位	コマンド	備考
テーブル	mysqldump -u root -p --add-drop-table DB名テーブル名	drop tableを入れるオプション付き
データベース	mysqldump -u root -p DB名	dropテーブルは自動で入るようなので、空じゃないDBに入れるときは既存データなくなる！

↑

テーブル単位†

主キーがかぶらないようにdrop tableして作り直す構文つきで出力

mysqldump -u root -p --add-drop-table DB名 テーブル名 > /var/tmp/rooms.sql

戻すときはデータベースの指定だけでOK

mysql -u root -p DB名 < /var/tmp/rooms.sql

↑

データベース単位†

↑

バックアップ†

バックアップ対象はデータベースmysqlとし、出力ファイル名はmysql.sqlとする。

mysqldump -u root -p データベース名 > mysql.sql

危険を承知でパスワードをコマンドに入れてしまう場合はこちらで

mysqldump -u root --password=パスワード データベース名 > mysql.sql

圧縮しつつバックアップは以下の通り

mysqldump -u root -p データベース名 | gzip > mysql.sql.gz

↑

リカバリー†

リカバリー対象データベースはcreateしておく。

mysql> create database mysql2;
mysql> exit
mysql -u root -p mysql2 < mysql.sql

圧縮したもののリカバリーは以下の通り

zcat mysql.sql.gz  | mysql -u root -p データベース名

↑

全部のデータベース†

↑

バックアップ†

全部のデータベースを出力

mysqldump -u root -x --all-databases > dump.sql

↑

リカバリ†

全部のデータベースをインポート(もとあるＤＢは名前がかぶらなければ残る）

mysql -u root -p < dump.sql

リカバリーした後はrootでログインして、flush privilegesすること。さもないと移行されたユーザでログインできない。もしくはMySQLの再起動で反映される。

↑

トラブルシューティング†

↑

パスワードを忘れて再設定したい†

起動引数に--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†

↑

パスワード忘れた†

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する

Counter: 18602, today: 3, yesterday: 2

DB/MySQL の履歴(No.37)

binログからの復旧†

レプリケーション†

RDSの場合はスレーブの設定がちと違う†

マスターの設定†

ユーザーの追加(IPをスレーブのものに限定)†

マスターのmy.cnf変更して再起動†

スレーブの設定†

ダンプとリカバリー†

マスター†

スレーブ†

文字コード†

クライアント側の設定†

mysql.iniでの文字コードの設定†

接続ログを出したい†

最大接続数・現在の接続数を確認したい†

デフォルト接続設定 ~/.my.cnf†

管理ツール†

データベース作成、ユーザ作成などの主に管理系タスク†

データベース†

mysqlのコマンドラインから†

コマンドラインから†

ユーザ作成、権限付与†

コマンド集†

テーブル構造やデータ型†

Tableの形式†

データ型†

順序型†

順序に任意の値を設定†

注意点†

データベース名やテーブル名は大文字小文字を区別する。†

GUI管理ツール†

phpMyAdmin†

インポート、エクスポート†

CSVインポート†

バックアップ&リカバリー†

エクスポート†

テーブル単位†

データベース単位†

バックアップ†

リカバリー†

全部のデータベース†

バックアップ†

リカバリ†

トラブルシューティング†

パスワードを忘れて再設定したい†

FAQ†

パスワード忘れた†

phpMyAdmin †