- 追加された行はこのように表示されます。
- 削除された行は
このように表示されます。
*利用コマンド記録
!!!参考サイト
!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==<<NG!!!>>
*エラーが発生する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 <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