MySQL
- 利用コマンド記録
参考サイト
mysql-4.1 日本語リファレンスマニュアル
mysql-5.1 日本語リファレンスマニュアル
トラブル対応
ぼくとMySQLの3日間戦争(予告編) / GMOメディア エンジニアブログ
ある日、突然、MySQLが
落ちた。
しかもまたすぐ
落ちた。
バックアップ用スレーブなのにmax_connections=3000とかなんだよとか思いつつも、threads_connected=0ってことは外部クエリー起因じゃなさそう。てか大体外部からトラフィック来ないし。バッチにも使ってないから。
…困った時は、coreだよね！
# vim ./my.cnf .. core-file ..
# ulimit -c 0 # ulimit -c unlimited # ulimit -c unlimited
取り敢えずulimit -c 0でDISKを食い潰されないように出力を止めつつ、
- core ファイル確認
- gdb でスタックトレース
# gdb /usr/local/mysql4026/libexec/mysqld ./core.26263 (gdb) bt (gdb) thread apply all bt
- my.cnf に core-file を指定すると、クラッシュ時に core ファイルを生成する。
- core ファイルがたくさん発生してディスクが満杯にならないように
- core ファイルを gdb で解析し、どの関数で異常が発生しているかを確認する。
- 異常を発生する関数を使用しないよう、mysql を設定しクラッシュしなくなった。
日本語全文検索
MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽 日本語全文検索 について / たたみラボ
FULLTEXTとNgramを駆使した日本語全文検索についてまとめてみました。
LIKE検索に比べて数倍から数十倍の速度 (語句によっては百倍近い速さ) で実行できるのが大きな特徴です。
MySQL full-text parser plugin collection
MySQL 5.1 以降ではプラグイン機構が導入されて、全文検索機能のパーサがプラグインできるようになりました。MySQL full-text parser plugin collection project (mysqlftppc) では現在次のプラグインをメンテナンスしています。
インストール
FreeBSDでは
- portsnap 更新済み 2010-01 では mysql-server-4.0.27 4.1.25 5.0.89 5.1.42 から選択
ports インストール
- FreeBSD ports
# cd /usr/ports/databases/mysql50-server # make install WITH_CHARSET=utf8
- 起動設定 /etc/rc.conf
mysql_enable="YES" mysql_dbdir="/var/db/mysql" (必要に応じて) mysql_limits="YES" (必要に応じて)
- 初期化はディレクトリがない場合に自動で行われる
ports アンインストール
- pkg_delete mysql-server
- /var/db/mysql 削除
RHEL-5
- 5.0.77-4.el5_4.1 at 2010-01
インストール
yum install mysql-server
RHEL-4
- mysql-4.1.22-2.el4 at 2010-01
インストール
up2date --install mysql-server
管理
データベース管理
データベース作成
mysqladmin create <<データベース名>>
データベース名表示
mysqlshow
データベース削除
mysqladmin drop <<データベース名>>
DBファイルをテーブル単位で分割する設定
- 通常はDBのデータは複数テーブルで1ファイル
- http://dev.mysql.com/doc/refman/5.1/ja/multiple-tablespaces.html
- 13.5.3.1. Per-Table テーブルスペースを利用する
このラインを my.cnf の [mysqld] セクションに追加する事で、複数のテーブルスペースを有効にする事ができます:
[mysqld] innodb_file_per_table
- 設定を有効にした後に作成したテーブルから有効
- 既存のテーブルはそのまま。ダンプ・レストアし、ibdata* ファイルを手で削除する必要がある。
- mysql ダンプ取得
mysqldump -u root -p -opt --all-databases > mysql.YYYYMMDD.dump
- 移行対象データベース削除
mysqladmin -u root -p drop <<データベース名>> mysqladmin -u root -p drop zabbix (例)
- drop できない場合は、db停止+対象DBディレクトリ削除+db再開(念のため)
- mysql 停止
- InnoDB データファイル削除
- mysql_dbdir/ibdata1
- mysql 再開
- mysql レストア
mysql -u root -p < mysql.YYYYMMDD.dump
mysql の 管理者接続
shell> mysql -u root -p mysql shell> mysql --user=root --password mysql
ユーザー管理
ユーザー一覧表示
SELECT USER,HOST FROM MYSQL.USER ;
ユーザー追加
CREATE USER <<ユーザー>> IDENTIFIED BY PASSWORD '<<パスワード>>' ;
権限指定
GRANT ALL ON <<データベース名>>.* to <<ユーザー名>> ;
ユーザー削除
DROP USER <<ユーザー>> ;
ユーザーパスワード変更
SET PASSFORD FOR 'user1' = PASSWORD('password') ;
データベースのメンテナンス
mysqlcheck ― テーブル メンテナンスと修復プログラム / MySQL 5.1 リファレンスマニュアル
shell> mysqlcheck --all-databases shell> mysqlcheck -u root -p --all-databases
- 全DB最適化
shell> mysqlcheck --optimize --all-databases shell> mysqlcheck -u root -p --optimize --all-databases
- 全DB統計情報収集
shell> mysqlcheck --analyze --all-databases shell> mysqlcheck -u root -p --analyze --all-databases
OPTIMIZE TABLE 構文 / MySQL 5.1 リファレンスマニュアル
もしテーブルの大部分を削除したり、変数長行で何箇所もテーブルを変更した場合は(VARCHAR、VARBINARY、BLOB、または TEXT カラムを持つテーブル)、OPTIMIZE TABLE を利用しなければいけません。
mysql> OPTIMIZE TABLE tbl_name [, tbl_name]
バックアップ
全体のバックアップ
mysqldump -A --opt -u root -p > バックアップファイル
全体のレストア
- mysql データベースを除くデータベースを削除
> drop database testdb; > drop database test02;
- レストアコマンド
mysql -u root -p < バックアップファイル
全体のレストア時のエラー対応
- レストア時エラー
ERROR 1146(42S02) at line 12: Table 'mysql.time_zone_name' doesn't exist
ERROR 1146(42S02) at line 12: Table 'mysql.time_zone' doesn't exist
ERROR 1146(42S02) at line 12: Table 'mysql.time_zone_transistion_type' doesn't exist
ERROR 1146(42S02) at line 12: Table 'mysql.time_zone_transistion' doesn't exist
- 原因 「mysql」データベースを drop してしまっていた。
mysqladmin -u root -p drop mysqlNG!!!
- エラーが発生するSQLコマンドを確認
less バックアップファイル
- バックアップファイル 12行目
/*!40103 SET TIME_ZONE='+00:00' */;
- タイムゾーン設定時に mysql データベースに time_zone_ 関連テーブルが必要
- エラーが出るテーブルの create table SQL コマンドを確認
less バックアップファイル
- 必要なテーブルを作成
mysql -u root -p mysql
- mysql.time_zone
- mysql.time_zone_name
- mysql.time_zone_transition
- mysql.time_zone_transition_type
- 4つのテーブルを作成後に
mysql -u root -p < バックアップファイル
を実行できた。
InnoDB ログファイルの拡張
- http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_log_file_size
- パラメータを変更するだけだと、エラーを吐いて起動しない。
- パラメータを変更し、innodb ログファイルを削除して起動すると、自動作成される。
Red Hat Enterprise Linux 4
- Red Hat Enterprise Linux 4.8 の up2date でインストール、更新した mysql-server-4.1.22-2.el4 で確認
- mysqld 停止
/sbin/service mysqld stop
- /etc/my.cnf ファイルの編集・追加
innodb_log_file_size=33554432 innodb_log_files_in_group=4
- InnoDB ログファイルの削除
rm /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1
- mysqld 起動
/sbin/service mysqld start
- InnoDB ログファイルは自動作成される
# ls -l /var/lib/mysql/ib_logfile* -rw-rw---- 1 mysql mysql 33554432 1月 4日 19:36 /var/lib/mysql/ib_logfile0 -rw-rw---- 1 mysql mysql 33554432 1月 4日 19:13 /var/lib/mysql/ib_logfile1 -rw-rw---- 1 mysql mysql 33554432 1月 4日 19:26 /var/lib/mysql/ib_logfile2 -rw-rw---- 1 mysql mysql 33554432 1月 4日 19:36 /var/lib/mysql/ib_logfile3
- 起動時ログ
091217 17:28:54 mysqld started 091217 17:28:54 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 32 MB InnoDB: Database physically writes the file full: wait... 091217 17:28:54 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 32 MB InnoDB: Database physically writes the file full: wait... 091217 17:28:54 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 32 MB InnoDB: Database physically writes the file full: wait... 091217 17:28:55 InnoDB: Log file ./ib_logfile3 did not exist: new to be created InnoDB: Setting log file ./ib_logfile3 size to 32 MB InnoDB: Database physically writes the file full: wait...
FreeBSD
- FreeBSD-7-Stable ports インストールした mysql-server-5.0.89 で確認
- mysqld 停止
/usr/local/etc/rc.d/mysql-server stop
- /var/db/mysql/my.cnf 編集
innodb_log_file_size=33554432 innodb_log_files_in_group=4
- InnoDB ログファイルの削除
rm /var/db/mysql/ib_logfile0 /var/db/mysql/ib_logfile1
- mysqld 起動
/usr/local/etc/rc.d/mysql-server start
デフォルトのDBエンジン指定
- my.cnf
mysql_table_type=InnoDB default_storage_engine=InnoDB
- どちらか
- default_storage_engine 推奨? セッションでの変更は
SET storage_engine=MYISAM;
ユーザーでの mysql コマンド
接続
mysql --user=<<ユーザー>> --password=<<パスワード>> <<データベース名>> mysql -u <<ユーザー>> -p <<データベース名>>
テーブル管理
テーブル一覧表示
show tables;
テーブル作成時のDBエンジン指定
CREATE TABLE <<テーブル名>> (... ) ENGINE=InnoDB;
デフォルトのストレージエンジン指定変更
SET storage_engine=MYISAM; SET storage_engine=INNODB;
テーブルのフィールド表示
SHOW COLUMNS FROM <<テーブル名>>
テーブルのインデックス表示
SHOW INDEX FROM <<テーブル名>>
view 管理
view 一覧
select * from information_schema.views;
view 設定の確認
show create view <view名>;
パスワード管理
自分のパスワード変更
SET PASSWORD = PASSWORD('password');
ユーザーのパスワード変更
SET PASSFORD FOR 'user1' = PASSWORD('password');
または
mysqladmin password
文字コード関連
文字コード確認
- データベース
show create database <<データベース名>>
- テーブル
show create table <<テーブル名>>
データベース文字コード指定
create database <<データベース名>> default character set <<キャラクタセット名>>
データベース文字コード変更
alter database default character set <<キャラクタセット名>>
テーブル文字コード変更
ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
クライアント文字コード状況確認
mysql> show variables like "%char%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
バックアップ関連
DB指定バックアップ
mysqldump <<データベース名>> > <<バックアップファイル>>
DB指定レストア
mysqldump <<データベース名>> < <<バックアップファイル>>
管理コマンドパスワード指定
- ファイル作成 /path/to/mysql_ini
[client] password=P@$$w0rd
- ファイル権限設定
shell> chmod 400 /path/to/mysql_ini
- コマンド
/usr/bin/mysqladmin --defaults-extra-file=/path/to/mysql_ini -u root
クライアント初期設定
[client] password=P@$$w0rd default-character-set=utf8
- コマンド
mysql --defaults-file=another.my.cnf
参考
MySQL 5.1 リファレンスマニュアル
- http://dev.mysql.com/doc/refman/5.1/ja/
- 日本語版に記述がなくて英語版にある場合が多い。
- 英語のリファレンスマニュアルの参照は必須
MySQL5の既存DB文字コード変更（latin1からujis) / Wingnotes
# mysqldump --default-character-set=binary -u ユーザ名 DB名 --password=パスワード > ダンプの出力ファイルパス
# sed 's/latin1/utf8/g' ダンプファイル
# mysql -u ユーザ名 -p DB名 --default-character-set=ujis < ダンプファイル名
mysql> alter database DB名 character set ujis;
# vi my.cnf [mysqld] default-character-set=ujis skip-character-set-client-handshake
- ??
# service mysqld start
障害
MySQL のデータベース zabbix でテーブル history 破損
zabbix の history テーブル破損確認
[root@sysbkup01 zabbix]# time mysqlcheck -u root -p zabbix history Enter password: zabbix.history error : Corrupt real 195m47.145s user 0m0.006s sys 0m0.002s
repair 実施
mysql> repair table history; +----------------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+--------+----------+---------------------------------------------------------+ | zabbix.history | repair | note | The storage engine for the table doesn't support repair | +----------------+--------+----------+---------------------------------------------------------+ 1 row in set (0.00 sec)
- 修復コマンド 調査中
mysql> show create table history; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | history | CREATE TABLE `history` ( `itemid` bigint(20) unsigned NOT NULL default '0', `clock` int(11) NOT NULL default '0', `value` double(16,4) NOT NULL default '0.0000', KEY `history_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.25 sec)
mysqld.log
- /var/log/mysqld.log
120411 15:32:06 InnoDB: error clustered record for sec rec not found InnoDB: index `history_1` of table `zabbix/history` InnoDB: sec index record PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0 0: len 8; hex 0000000000004d61; asc Ma;; 1: len 4; hex ca6280fa; asc b ;; 2: len 6; hex 000092cbaf7a; asc z;; InnoDB: clust index record PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;; TRANSACTION 7 351447463, ACTIVE 0 sec, process no 14667, OS thread id 1865653152 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id 13, query id 32248 localhost zabbix updating delete from history where itemid=19809 and clock<1247983930 InnoDB: Submit a detailed bug report to http://bugs.mysql.com