MySQLの操作まとめページです。
見出し
ダンプ（エクスポート・インポート）
エクスポート
|
1 |
$ mysqldump -u[ユーザ名] -p[パスワード] [DB名] > [エクスポートファイル.sql] |
エクスポート（圧縮）
|
1 |
$ mysqldump -u[ユーザ名] -p[パスワード] [DB名] | gzip > [DB名].sql.gz |
エクスポート（外部ホストから）
|
1 |
$ mysqldump -u[ユーザ] -h [データベースホスト] -p [DB名] > [エクスポートファイル.sql] |
エクスポート（CSV出力）
|
1 2 3 4 |
mysql> SELECT * FROM test_tables INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'; |
エクスポート（ロック）
テーブルをロックして整合性の取れたダンプを取得します。
|
1 |
$ mysqldump --lock-all-tables -u[ユーザ名] -p[パスワード] [DB名] > [エクスポートファイル.sql] |
エクスポート（インポート時dropしてくれる）database
|
1 |
mysqldump --add-drop-database --add-drop-table --lock-all-tables -u root -p test_local > test_local.dmp |
–add-drop-databaseオプションを指定するとdrop database文が最初に追加されるのでインポート時におすすめ。
エクスポート（マスタのバイナリログのpos記録）
|
1 |
mysqldump --add-drop-database --add-drop-table --master-data --events -u root -p -A > test_local.dmp |
–master-dataオプションを指定します。
エクスポートエラー
MySQLのダンプを取得したらエラーが出ました。
$ mysqldump -u -h -p > /tmp/dump.sql
E...
mysql(mysql-community-devel-5.6.29-2.el6.x86_64)をインストールしたので、mysqldumpの...
インポート
|
1 |
$ mysql -u[ユーザ名] -p[パスワード] [DB名] < [インポートファイル].sql |
インポート（解凍）
|
1 |
$ zcat [インポートファイル].sql.gz | mysql -u[ユーザ名] -p[パスワード] [DB名] |
インポートエラー
mysqldumpをしたファイルをインポートしようとしたところ、以下のエラーになりました。
ERROR 1071 Specified ke...
ログインせずコマンド実行
mysqlにログインせずコマンド実行
|
1 |
$ mysql -u[ユーザ名] -p[パスワード] [DB名] -e "SHOW TABLES;" |
mysqlにログインせずSQLを実行して結果ファイルに出力
|
1 |
$ mysql -u[ユーザ名] -p[パスワード] [DB名] -e "SHOW TABLES;" > out.txt |
mysqlにログインせずSQLファイルを実行して結果ファイルに出力
|
1 |
$ mysql -u[ユーザ名] -p[パスワード] [DB名] < hoge.sql > out.txt |
起動・停止・再起動
起動
|
1 |
# /etc/init.d/mysql start |
停止
|
1 |
# /etc/init.d/mysql stop |
再起動
|
1 |
# /etc/init.d/mysql restart |
テーブル生成文（create文）確認
|
1 |
mysql> show create table table_name; |
権限関連
権限追加
|
1 |
mysql> grant all on [DB名].* to [ユーザ名]; |
システム確認関連
容量確認
|
1 2 3 4 |
mysql> SELECT table_schema "DB名", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DBサイズ(MB)" FROM information_schema.tables GROUP BY table_schema; |
稼働確認
|
1 |
$ mysqladmin ping -u[user] -p[pass] |
ユーザー関連
ユーザ一覧確認
|
1 |
mysql> select Host, User, Password from mysql.user; |
レプリケーション関連
読み込み専用確認
|
1 |
mysql> select @@read_only; |
1or0でかえってきます。slaveに対する読み込み専用になっているかの確認で使用しています。
レプリケーションエラー（Duplicate entry）
MySQLのレプリケーションエラー「slave の Duplicate entry」でレプリケーションが停止し、mysqlbinlogを見た...
レプリケーションエラー（スキップする）
|
1 2 |
mysql > set global sql_slave_skip_counter=1; mysql > start slave sql_thread; |
注意点（バイナリログ生成）
子を親にして孫を作る場合、子のmy.cnfにlog_slave_updatesを書かないと孫用のバイナリログが生成されないので注意。
バイナリログ関連
バイナリログの中身を確認
|
1 2 |
# mysqlbinlog /var/lib/mysql/mysql-bin.xxxxxx > /tmp/test # vi /tmp/test |
バイナリログの削除
|
1 2 3 |
mysql > show master logs; mysql > purge master logs to 'mysql-bin.xxxx'; ←最後から３番目くらいにしておきました。 mysql > show master logs; |
|
1 2 |
$ vi /etc/my.cnf で以下が14だったので7に修正 expire_logs_days = 7 |
|
1 2 3 |
mysql > show global variables like ‘expire_logs_days’; mysql > set global expire_logs_days = 7; mysql > show global variables like ‘expire_logs_days’; |
|
1 |
# /etc/init.d/mysql restart |
この記事でVagrantの容量肥大化の為の拡張と圧縮を行いました。
ですが、容量上限は増えたものの、現在の容量の圧縮がうまくいきません...
バイナリログを出力しない
開発環境での話です。MySQLのバイナリログが増え続けて容量を食ってしまっており、ダンプをインポートした際などには毎回バイナリログのpurg...
バイナリログのフォーマット確認
|
1 2 3 |
mysql > show variables like 'binlog_format'; または mysql > select @@binlog_format; |
バイナリログのフォーマット指定
|
1 2 3 4 5 |
mysql > set global binlog_format = 'statement'; または mysql > set global binlog_format = 'mixed'; または mysql > set global binlog_format = 'row'; |
または
/etc/my.cnfを修正
|
1 2 3 4 5 |
binlog_format='statement' または binlog_format='mixed' または binlog_format='row' |
InnoDBログ関連
MySQLのbinlog（バイナリログ）とInnoDBlog（InnoDBログ）について調べたので記録しておきます。
バイナリログ
公式...
スローログ関連
|
1 |
# vi /etc/my.cnf |
で以下を追記
出力する
|
1 |
slow_query_log=1 |
出力対象（秒
※1.0秒の場合
|
1 |
long_query_time=1.0 |
出力形式
|
1 2 3 4 5 |
log_output=TABLE または log_output=FILE または log_output=TABLE,FILE |
出力ファイル名
|
1 |
slow_query_log_file='/var/lib/mysql/mysql-slow.log' |
ログローテート
|
1 |
# /usr/bin/mysqladmin -u[user] -p[password] flush-logs |
ストレージ関連
InnoDBかMyISAMか確認
|
1 2 |
mysql> use information_schema; mysql> select table_name, engine from tables where table_schema = 'test_db'; |
MyISAMとInnoDBとではロック形式が異なる
MySQLでテーブルが排他ロックになり、全部待ち状態になり動かなくなりました。
先ず、画面（ブラウザ）に何も応答がなくなってしまったの...
MyISAMからInnoDBへ変更
|
1 |
mysql> ALTER TABLE test_table ENGINE=InnoDB; |
MyISAMからInnoDBに乗り換える事について調べた事のまとめです。
対象のテーブル単位で変換可能です。
簡単に変更出来ます...
設定確認
|
1 |
mysql > show variables; |
外部キー制約
|
1 2 |
mysql > truncate users; ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint |
となってtruncate出来ない場合
|
1 2 3 |
mysql > set foreign_key_checks = 0; mysql > truncate users; mysql > set foreign_key_checks = 1; |
MySQL記事
SQLはこちらにまとめました。
MySQLで使用したSQL文のサンプルを記録していきたいと思います。自分用にまとめておくページです。
MySQL操作関連はこちらにまと...