MySQL操作まとめ(エクスポート、ダンプ、インポート、レプリケーション、起動、権限、確認、ユーザ、バイナリログ、InnoDBログ、MyISAM、InnoDB、SQL)

MySQLの操作まとめページです。

見出し

スポンサーリンク

ダンプ(エクスポート・インポート)

エクスポート

$ mysqldump -u[ユーザ名] -p[パスワード] [DB名] > [エクスポートファイル.sql]

エクスポート(圧縮)

$ mysqldump -u[ユーザ名] -p[パスワード] [DB名] | gzip > [DB名].sql.gz

エクスポート(外部ホストから)

$ mysqldump -u[ユーザ] -h [データベースホスト] -p [DB名] > [エクスポートファイル.sql]

エクスポート(CSV出力)

mysql> SELECT * FROM test_tables
 INTO OUTFILE '/tmp/test.csv'
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"';

エクスポート(ロック)

テーブルをロックして整合性の取れたダンプを取得します。

$ mysqldump --lock-all-tables -u[ユーザ名] -p[パスワード] [DB名] > [エクスポートファイル.sql]

エクスポート(インポート時dropしてくれる)database

mysqldump --add-drop-database --add-drop-table --lock-all-tables -u root -p test_local > test_local.dmp

–add-drop-databaseオプションを指定するとdrop database文が最初に追加されるのでインポート時におすすめ。

エクスポート(マスタのバイナリログのpos記録)

mysqldump --add-drop-database --add-drop-table --master-data --events -u root -p -A > test_local.dmp

–master-dataオプションを指定します。

エクスポートエラー

mysqldumpをしたらエラーになった(Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1')
MySQLのダンプを取得したらエラーが出ました。 $ mysqldump -u -h -p  > /tmp/dump.sql E...
mysqldump: unknown variable 'symbolic-links=0'
mysql(mysql-community-devel-5.6.29-2.el6.x86_64)をインストールしたので、mysqldumpの...

インポート

$ mysql -u[ユーザ名] -p[パスワード] [DB名] < [インポートファイル].sql

インポート(解凍)

$ zcat [インポートファイル].sql.gz | mysql -u[ユーザ名] -p[パスワード] [DB名]

インポートエラー

mysqldumpのインポートでエラー発生「Specified key was too long; max key length is 1000 bytes」解消方法はinnodbの設定・削除でした
mysqldumpをしたファイルをインポートしようとしたところ、以下のエラーになりました。 ERROR 1071 Specified ke...

ログインせずコマンド実行

mysqlにログインせずコマンド実行

$ mysql -u[ユーザ名] -p[パスワード] [DB名] -e "SHOW TABLES;"

mysqlにログインせずSQLを実行して結果ファイルに出力

$ mysql -u[ユーザ名] -p[パスワード] [DB名] -e "SHOW TABLES;" > out.txt

mysqlにログインせずSQLファイルを実行して結果ファイルに出力

$ mysql -u[ユーザ名] -p[パスワード] [DB名] < hoge.sql > out.txt

起動・停止・再起動

起動

# /etc/init.d/mysql start

停止

# /etc/init.d/mysql stop

再起動

# /etc/init.d/mysql restart

テーブル生成文(create文)確認

mysql> show create table table_name;

権限関連

権限追加

mysql> grant all on [DB名].* to [ユーザ名];

システム確認関連

容量確認

mysql> SELECT table_schema "DB名",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DBサイズ(MB)"
FROM information_schema.tables
GROUP BY table_schema;

稼働確認

$ mysqladmin ping -u[user] -p[pass]

ユーザー関連

ユーザ一覧確認

mysql> select Host, User, Password from mysql.user;

レプリケーション関連

読み込み専用確認

mysql> select @@read_only;

1or0でかえってきます。slaveに対する読み込み専用になっているかの確認で使用しています。

レプリケーションエラー(Duplicate entry)

MySQLレプリケーションエラー「Duplicate entry」の原因をmysqlbinlogで特定
MySQLのレプリケーションエラー「slave の Duplicate entry」でレプリケーションが停止し、mysqlbinlogを見た...

レプリケーションエラー(スキップする)

mysql > set global sql_slave_skip_counter=1;
mysql > start slave sql_thread;

注意点(バイナリログ生成)

子を親にして孫を作る場合、子のmy.cnfにlog_slave_updatesを書かないと孫用のバイナリログが生成されないので注意。

バイナリログ関連

バイナリログの中身を確認

# mysqlbinlog /var/lib/mysql/mysql-bin.xxxxxx > /tmp/test
# vi /tmp/test

バイナリログの削除

mysql > show master logs;
mysql > purge master logs to 'mysql-bin.xxxx'; ←最後から3番目くらいにしておきました。
mysql > show master logs;
$ vi /etc/my.cnf で以下が14だったので7に修正
expire_logs_days = 7
mysql > show global variables like ‘expire_logs_days’;
mysql > set global expire_logs_days = 7;
mysql > show global variables like ‘expire_logs_days’;
# /etc/init.d/mysql restart
Vagrantの容量が減らなかったのはMySQLのバイナリログが原因だった[Windows VirtualBox]
この記事でVagrantの容量肥大化の為の拡張と圧縮を行いました。 ですが、容量上限は増えたものの、現在の容量の圧縮がうまくいきません...

バイナリログを出力しない

MySQLのバイナリログが増え続けて容量を食ってしまうのでbinlogを出力しないようにした
開発環境での話です。MySQLのバイナリログが増え続けて容量を食ってしまっており、ダンプをインポートした際などには毎回バイナリログのpurg...

バイナリログのフォーマット確認

mysql > show variables like 'binlog_format';
または
mysql > select @@binlog_format;

バイナリログのフォーマット指定

mysql > set global binlog_format = 'statement';
または
mysql > set global binlog_format = 'mixed';
または
mysql > set global binlog_format = 'row';

または

/etc/my.cnfを修正

binlog_format='statement'
または
binlog_format='mixed'
または
binlog_format='row'

InnoDBログ関連

MySQLバイナリログとInnoDBログとは
MySQLのbinlog(バイナリログ)とInnoDBlog(InnoDBログ)について調べたので記録しておきます。 バイナリログ 公式...

スローログ関連

# vi /etc/my.cnf

で以下を追記

出力する

slow_query_log=1

出力対象(秒

※1.0秒の場合

long_query_time=1.0

出力形式

log_output=TABLE
または
log_output=FILE
または
log_output=TABLE,FILE

出力ファイル名

slow_query_log_file='/var/lib/mysql/mysql-slow.log'

ログローテート

# /usr/bin/mysqladmin -u[user] -p[password] flush-logs

ストレージ関連

InnoDBかMyISAMか確認

mysql> use information_schema;
mysql> select table_name, engine from tables where table_schema = 'test_db';

MyISAMとInnoDBとではロック形式が異なる

MySQLでテーブルが排他ロック(stateがLocked)になり全部待ち状態になった原因まとめ
MySQLでテーブルが排他ロックになり、全部待ち状態になり動かなくなりました。 先ず、画面(ブラウザ)に何も応答がなくなってしまったの...

MyISAMからInnoDBへ変更

mysql> ALTER TABLE test_table ENGINE=InnoDB;
MyISAMからInnoDBに乗り換えるまとめ
MyISAMからInnoDBに乗り換える事について調べた事のまとめです。 対象のテーブル単位で変換可能です。 簡単に変更出来ます...

設定確認

mysql > show variables;

MySQL記事

SQLはこちらにまとめました。

良く使うSQL例文まとめ13選
MySQLで使用したSQL文のサンプルを記録していきたいと思います。自分用にまとめておくページです。 MySQL操作関連はこちらにまと...

MySQL記事一覧はこちら

http://normalblog.net/system/category/mysql/