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オプションを指定します。
エクスポートエラー
https://normalblog.net/system/sql_quote_show_create/
https://normalblog.net/system/mysqldump-unknown-variable-symbolic-links0/
インポート
$ mysql -u[ユーザ名] -p[パスワード] [DB名] < [インポートファイル].sql
インポート（解凍）
$ zcat [インポートファイル].sql.gz | mysql -u[ユーザ名] -p[パスワード] [DB名]
インポートエラー
https://normalblog.net/system/error_1071/
ログインせずコマンド実行
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）
https://normalblog.net/system/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'; ←最後から３番目くらいにしておきました。 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
https://normalblog.net/system/binarylog/
バイナリログを出力しない
https://normalblog.net/system/mysql-over-capacity/
バイナリログのフォーマット確認
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ログ関連
https://normalblog.net/system/binlog-innodblog/
スローログ関連
# 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とではロック形式が異なる
https://normalblog.net/system/myisam-state-locked/
MyISAMからInnoDBへ変更
mysql> ALTER TABLE test_table ENGINE=InnoDB;
https://normalblog.net/system/alter-table-engine/
設定確認
mysql > show variables;
外部キー制約
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記事
SQLはこちらにまとめました。
https://normalblog.net/system/sample_sql/
