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

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

見出し

1 ダンプ（エクスポート・インポート）
2 ログインせずコマンド実行
3 起動・停止・再起動
4 テーブル生成文（create文）確認
5 権限関連
- 5.1 権限追加
6 システム確認関連
- 6.1 容量確認
- 6.2 稼働確認
7 ユーザー関連
- 7.1 ユーザ一覧確認
8 レプリケーション関連
9 バイナリログ関連
10 InnoDBログ関連
11 スローログ関連
12 ストレージ関連
13 設定確認
14 外部キー制約
15 MySQL記事
- 15.1 SQLはこちらにまとめました。
- 15.2 MySQL記事一覧はこちら

ダンプ（エクスポート・インポート）

エクスポート

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

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

エクスポート（圧縮）

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

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

エクスポート（外部ホストから）

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

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

エクスポート（CSV出力）

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

mysql> SELECT * FROM test_tables

INTO OUTFILE '/tmp/test.csv'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

エクスポート（ロック）

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

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

1	$ 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

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記録）

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

1	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...

normalblog.net

2016-03-31 00:57

mysqldump: unknown variable 'symbolic-links=0'

mysql(mysql-community-devel-5.6.29-2.el6.x86_64)をインストールしたので、mysqldumpの...

normalblog.net

2016-03-31 00:51

インポート

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

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

インポート（解凍）

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

1	$ 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...

normalblog.net

2016-05-26 01:29

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

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

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

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

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

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

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

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

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

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

起動・停止・再起動

起動

# /etc/init.d/mysql start

1	# /etc/init.d/mysql start

停止

# /etc/init.d/mysql stop

1	# /etc/init.d/mysql stop

再起動

# /etc/init.d/mysql restart

1	# /etc/init.d/mysql restart

テーブル生成文（create文）確認

mysql> show create table table_name;

1	mysql> show create table table_name;

権限関連

権限追加

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

1	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;

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]

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

ユーザー関連

ユーザ一覧確認

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

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

レプリケーション関連

読み込み専用確認

mysql> select @@read_only;

1	mysql> select @@read_only;

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

レプリケーションエラー（Duplicate entry）

MySQLレプリケーションエラー「Duplicate entry」の原因をmysqlbinlogで特定

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

normalblog.net

2016-11-29 00:37

レプリケーションエラー（スキップする）

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

1 2	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

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

バイナリログの削除

mysql > show master logs;
mysql > purge master logs to 'mysql-bin.xxxx'; ←最後から３番目くらいにしておきました。
mysql > show master logs;

mysql > show master logs;

mysql > purge master logs to 'mysql-bin.xxxx'; ←最後から３番目くらいにしておきました。

mysql > show master logs;

$ vi /etc/my.cnf　で以下が14だったので7に修正
expire_logs_days = 7

1 2	$ 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’;

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

1	# /etc/init.d/mysql restart

Vagrantの容量が減らなかったのはMySQLのバイナリログが原因だった[Windows VirtualBox]

この記事でVagrantの容量肥大化の為の拡張と圧縮を行いました。ですが、容量上限は増えたものの、現在の容量の圧縮がうまくいきません...

normalblog.net

2016-03-20 00:52

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

MySQLのバイナリログが増え続けて容量を食ってしまうのでbinlogを出力しないようにした

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

normalblog.net

2017-01-29 11:45

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

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

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';

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'

binlog_format='statement'

または

binlog_format='mixed'

または

binlog_format='row'

InnoDBログ関連

MySQLバイナリログとInnoDBログとは

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

normalblog.net

2016-11-29 01:31

スローログ関連

# vi /etc/my.cnf

1	# vi /etc/my.cnf

で以下を追記

出力する

slow_query_log=1

1	slow_query_log=1

出力対象（秒

※1.0秒の場合

long_query_time=1.0

1	long_query_time=1.0

出力形式

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

log_output=TABLE

または

log_output=FILE

または

log_output=TABLE,FILE

出力ファイル名

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

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

ログローテート

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

1	# /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';

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

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

MySQLでテーブルが排他ロック（stateがLocked）になり全部待ち状態になった原因まとめ

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

normalblog.net

2016-11-20 01:54

MyISAMからInnoDBへ変更

mysql> ALTER TABLE test_table ENGINE=InnoDB;

1	mysql> ALTER TABLE test_table ENGINE=InnoDB;

MyISAMからInnoDBに乗り換えるまとめ

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

normalblog.net

2016-11-20 02:51

設定確認

mysql > show variables;

1	mysql > show variables;

外部キー制約

mysql > truncate users;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

1 2	mysql > truncate users; ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

となってtruncate出来ない場合

mysql > set foreign_key_checks = 0;
mysql > truncate users;
mysql > set foreign_key_checks =  1;

mysql > set foreign_key_checks = 0;

mysql > truncate users;

mysql > set foreign_key_checks = 1;

MySQL記事

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

良く使うSQL例文まとめ16選

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

normalblog.net

2016-06-17 01:19

MySQL記事一覧はこちら

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