トップ 差分 一覧 ソース 検索 ヘルプ PDF RSS ログイン

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 インストール

# 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ファイルをテーブル単位で分割する設定

このラインを 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 バックアップファイル

/*!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 ログファイルの拡張

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 リファレンスマニュアル

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