メモ > サーバ > 各論: コマンド > MySQLのバックアップ（エクスポートとインポート）
MySQLのバックアップ（エクスポートとインポート）
■エクスポート
$ mysqldump -u ユーザー名 データベース名 > ダンプファイル名
$ mysqldump -u webmaster -p test > /var/www/backups/mysql_test_20140130.sql
$ mysqldump -u webmaster -p test --default-character-set=binary > /var/www/backups/mysql_test_20140130.sql
「--default-character-set」に「binary」を指定しても文字化けする場合、「utf8」や「latin1」も試す。
dump時に「Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces」のようなエラーが表示される場合、
「--no-tablespaces」を付与して実行する。
具体的には以下のようになる。
$ mysqldump -u webmaster -p test --default-character-set=binary --no-tablespaces > /var/www/backups/mysql_test_20140130.sql
MySQL5.7から、dumpにはPROCESS権限が必要になった。
よってdumpを実行したいユーザにこの権限を追加で付与してもいいが、上記のように「--no-tablespaces」を付与することでも対応できる。
バックアップの手順を見直したほうがよさそうでしょうか？
https://www.chatwork.com/#!rid119727508-1472084389675421696
mysqldumpで「Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces」となる場合の対処: 小粋空間
https://www.koikikukan.com/archives/2021/02/17-235555.php
MySQL | ユーザーに設定できる権限の種類と一覧
https://www.dbonline.jp/mysql/user/index5.html
mysqldumpでPROCESS権限(PROCESS privilege)を要求される - いっさいがっさい
https://isgs-lab.com/424/
dump時に「Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS」のようなエラーが表示される場合、
「--skip-column-statistics」を付与して実行する。
mysqldumpコマンドで "Unknown table 'COLUMN_STATISTICS' in information_schema (1109)" と怒られる原因と対策 - モヒカンメモ
https://blog.pinkumohikan.com/entry/mysqldump-disable-column-statistics
dump時に「database 'XXX' when using LOCK TABLES」のようなエラーが表示される場合、
「--single-transaction」を付与して実行する。
【MySQL】 mysqldumpしようとしたら 「 when doing LOCK TABLES 」でdumpできない - 旅するえんじにあ - Engineers to Travel -
https://deadcode.hatenablog.jp/entry/2014/05/08/223627
■テーブルを指定してエクスポート
$ mysqldump -u ユーザー名 -p データベース名 テーブル名 > ダンプファイル名
$ mysqldump -u ユーザー名 -p データベース名 テーブル名1 テーブル名2 > ダンプファイル名
MySQLで特定のテーブルをdumpする方法。 - Qiita
https://qiita.com/kooohei/items/530f46b6ef8909227dc8
■テーブルを除外してエクスポート
※未検証。
$ mysqldump -u username -p -t database_name --ignore-table=database_name.table_name
$ mysqldump -u username -p -t database_name --ignore-table=database_name.table_name --ignore-table=database_name.another_table_namme
指定したテーブル以外のレコードをdumpする #MySQL - Qiita
https://qiita.com/kadoppe/items/ca54fdc57097e109a151
■一行ずつのINSERTでエクスポート
dump時に「--skip-extended-insert」を付けると、一行ずつのINSERTでエクスポートされる。
具体的には以下のようになる。
# mysqldump -u ユーザー名 -p データベース名 --skip-extended-insert > /var/www/backups/mysql_test_20140130.sql
mysqldumpで一行ずつのINSERT文を出力する方法 - ハックノート
https://hacknote.jp/archives/5557/
■検索結果をエクスポート
$ echo 'SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100;' | mysql -u ユーザー名 -p データベース名 > ダンプファイル名
MySQLで抽出結果をファイルに出力する方法 - Qiita
https://qiita.com/tasmas256/items/ec7e23278ee2b40aad79
■検索結果をCSVとしてエクスポート
$ SELECT id, name FROM staffs INTO OUTFILE ダンプファイル名 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
RDSなど「INTO OUTFILE」が使えない環境の場合、以下のようにする。
$ echo 'SELECT id, name FROM staffs;' | mysql -u ユーザー名 -p データベース名 | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ダンプファイル名
MySQLのデータをCSVに吐こうとするとエラーになる。 - H_Yamaguchiのブログ
http://h-yamaguchi.hatenablog.com/entry/2013/09/19/155205
mysql - Exporting table from Amazon RDS into a csv file - Stack Overflow
https://stackoverflow.com/questions/9536224/exporting-table-from-amazon-rds-into-a-csv-file
■インポート
$ mysql -u root データベース名 < ダンプファイル名
$ mysql -u webmaster -p test < mysql_test_20140130.sql
$ mysql -u webmaster -p test --default-character-set=binary < mysql_test_20140130.sql
■WindowsのXAMPP環境での実行例
C:\xampp\mysql\bin\mysqldump -u root -p test --default-character-set=binary > mysql_test_20140130.sql
C:\xampp\mysql\bin\mysql -u root -p test --default-character-set=binary < mysql_test_20140130.sql
■PHPからの実行例
<?php
echo shell_exec('mysqldump -u webmaster -p"1234" test --default-character-set=binary > mysql_test_20140130.sql');
exit('Complete');
PHPで独自にSQLエクスポートの仕組みを作るより、圧倒的に高速。
データが巨大すぎて独自システムからエクスポートできなかったものも、難なくエクスポートできた。
同様に、以下のようにしてインポートもできた。
<?php
echo shell_exec('mysql -u webmaster -p"1234" twitter_test --default-character-set=binary < mysql_test_20140130.sql');
exit('Complete');
■文字化け対策
文字化けする場合は「--default-character-set=binary」を指定して試す。
mysqldumpで文字化けデータを頑張ってダンプ&リストア
http://koexuka.blogspot.jp/2009/04/mysqldumpdump.html
■SOURCEコマンド
MySQLにログイン後、SOURCEコマンドを実行してインポートすることもできる。
mysql> SOURCE mysql_test_20140130.sql
ファイル読込｜データのインポート・エクスポート｜MySQL｜PHP & JavaScript Room
http://phpjavascriptroom.com/?t=mysql&p=mysqlimport
■LOADコマンド
LOADコマンドでの登録が一番高速らしい。
CSVを作成する際に、バリデーションなどは行っておく必要はある。
$ cat /tmp/eccube.csv
1,'アイスクリーム',NULL,1,中略,NULL,0,2,'2016-09-13 09:22:23','2016-09-13 09:22:23',2
LOAD DATA INFILE "/tmp/eccube.csv" INTO TABLE dtb_products FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";
MySQLに大量のデータを入れるときに最適な方法は？
http://naoberry.com/tech/mysqldata/
■MySQLを定期バックアップ
MySQLデータベース自動バックアップ運用(mysqldump)
http://centossrv.com/mysql-backup.shtml