*利用コマンド記録 !!!参考サイト !mysql-4.1 日本語リファレンスマニュアル *http://dev.mysql.com/doc/refman/4.1/ja/index.html !mysql-5.1 日本語リファレンスマニュアル *http://dev.mysql.com/doc/refman/5.1/ja/index.html !!トラブル対応 !ぼくとMySQLの3日間戦争(予告編) / GMOメディア エンジニアブログ *http://tech.gmo-media.jp/post/57388878013/mysql-three-days-war-1 ""ある日、突然、MySQLが ""落ちた。 ""しかもまたすぐ ""落ちた。 *ぼくとMySQLの3日間戦争(調査編) *http://tech.gmo-media.jp/post/57389739685/mysql-three-days-war-2 ""バックアップ用スレーブなのに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検索より数十倍高速な、お手軽 日本語全文検索 について / たたみラボ *http://www.tatamilab.jp/rnd/archives/000390.html ""FULLTEXTとNgramを駆使した日本語全文検索についてまとめてみました。 ""LIKE検索に比べて数倍から数十倍の速度 (語句によっては百倍近い速さ) で実行できるのが大きな特徴です。 !MySQL full-text parser plugin collection ""MySQL 5.1 以降ではプラグイン機構が導入されて、全文検索機能のパーサがプラグインできるようになりました。MySQL full-text parser plugin collection project (mysqlftppc) では現在次のプラグインをメンテナンスしています。 *http://sourceforge.net/apps/mediawiki/mysqlftppc/index.php?title=Home-j !!!インストール !!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 リファレンスマニュアル *http://dev.mysql.com/doc/refman/5.1/ja/mysqlcheck.html *全DBチェック 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 リファレンスマニュアル *http://dev.mysql.com/doc/refman/5.1/ja/optimize-table.html ""もしテーブルの大部分を削除したり、変数長行で何箇所もテーブルを変更した場合は(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 mysql==<> *エラーが発生する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エンジン指定 *http://dev.mysql.com/doc/refman/5.1/ja/storage-engine-setting.html 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 ; !!パスワード管理 !自分のパスワード変更 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 *http://www.wingnotes.net/linux_server/mysql_latin1_ujis.html # 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