Mysql/ダンプ・リストア
提供: 初心者エンジニアの簡易メモ
バックアップと復元です。
目次
- 1 バックアップ(db全体)
- 2 バックアップ(table指定)
- 3 バックアップ(table指定&データのみ)
- 4 バックアップ(table指定&データのみ条件付)
- 5 オンラインバックアップ
- 6 テーブル定義のみバックアップ
- 7 バックアップgzip圧縮
- 8 復元
- 9 復元(db指定)
- 10 強制的に復元
- 11 復元(gzipから)
- 12 復元パス付(非推奨)
- 13 復元パス付(非推奨)パスにカッコなどがある場合
- 14 一時的な外部キーの有効無効
- 15 まとめてダンプ
- 16 まとめてリストア
- 17 復元中の処理を見る
- 18 windowsのmysqlからの読み込み
- 19 mysql5.7からのdumpコマンド
- 20 クレジットカード番号検索
- 21 定義書作成
バックアップ(db全体)
mysqldump -uroot -p project1 --default-character-set=utf8 > project1.sql
バックアップ(table指定)
mysqldump -uroot -p project1 table1 table2 --default-character-set=utf8 > project1_table1_2.sql
バックアップ(table指定&データのみ)
mysqldump -uroot -p -t project1 table1 > project1_table1.sql
バックアップ(table指定&データのみ条件付)
mysqldump -uroot -p -t database1 table1 --where 'id=1 ORDER BY id DESC LIMIT 1' > project1_table1_id1.sql
オンラインバックアップ
書き込みロックがかからなくなる
mysqldump -uroot -p --database project1 --default-character-set=utf8 --single-transaction > project1.sql
テーブル定義のみバックアップ
mysqldump -d -uroot -p database1 table1
バックアップgzip圧縮
mysqldump -uroot -p --database project1 --default-character-set=utf8 | gzip > project1.sql.gz
復元
mysql -uroot -p < project1.sql
復元(db指定)
mysql -uroot -p database1 < project1.sql
強制的に復元
mysql -uroot -p -f < project1.sql
復元(gzipから)
zcat project1.sql.gz | mysql -uroot -p project1
zcatが使えない場合は、gzcatを試してみる。
復元パス付(非推奨)
mysql -uroot -ppass1 < project1.sql
復元パス付(非推奨)パスにカッコなどがある場合
mysql -uroot -p'pass1(' < project1.sql
一時的な外部キーの有効無効
SET FOREIGN_KEY_CHECKS=0; // 外部キーの無効 SET FOREIGN_KEY_CHECKS=1; //外部キーの有効
外部キー制約を解除してまとめてリストア
find ./schema1 -type f | xargs cat > schema1.sql #ファイル結合 sed -i '1iSET FOREIGN_KEY_CHECKS=0;' schema1.sql #外部キーの制約offを、ファイルの先頭に追加(時間がかかるようなvimで、先頭に入れてもよい) echo 'SET FOREIGN_KEY_CHECKS=1;' >> schema1.sql #外部キーの制約onを、ファイルの最後尾に追加 mysql -uroot -ppass1 blog < schema1.sql
まとめてダンプ
#!/bin/bash # パスワードを一度だけ入力 echo -n "Enter MySQL password: " read -s password echo "" # ダンプ対象のデータベースのリスト databases=( "schema1" "schema2" ) # 出力ディレクトリの作成 output_dir="data" mkdir -p "$output_dir" # ダンプ処理 for db in "${databases[@]}"; do # 1. テーブル定義とデータのダンプ output_file="${output_dir}/${db}_full.sql.gz" echo "Dumping full (structure and data) for database: $db to $output_file" mysqldump -uroot -p"$password" --default-character-set=utf8mb4 "$db" | gzip > "$output_file" # 2. データのみのダンプ output_file="${output_dir}/${db}_data_only.sql.gz" echo "Dumping data only for database: $db to $output_file" mysqldump -uroot -p"$password" --no-create-info --default-character-set=utf8mb4 "$db" | gzip > "$output_file" # 3. テーブル定義のみのダンプ output_file="${output_dir}/${db}_structure_only.sql.gz" echo "Dumping structure only for database: $db to $output_file" mysqldump -uroot -p"$password" --no-data --default-character-set=utf8mb4 "$db" | gzip > "$output_file" done echo "All dumps completed."
まとめてリストア
#!/bin/bash # パスワードを一度だけ入力 echo -n "Enter MySQL password: " read -s password echo "" # リストア対象のデータベースのリスト databases=( "schema1" "schema2" ) # リストアタイプを確認 echo "Choose the type of restore:" echo "1) Full (structure and data)" echo "2) Data only" echo "3) Structure only" read -p "Enter your choice (1/2/3): " restore_type # リストアファイルの設定 case $restore_type in 1) suffix="_full.sql.gz" ;; 2) suffix="_data_only.sql.gz" ;; 3) suffix="_structure_only.sql.gz" ;; *) echo "Invalid choice. Exiting."; exit 1 ;; esac # リストア処理 for db in "${databases[@]}"; do # 対応するファイル名の設定 dump_file="data/${db}${suffix}" # ダンプファイルが存在するか確認 if [[ ! -f "$dump_file" ]]; then echo "Error: File $dump_file not found. Skipping restore for $db." continue fi # 自動的にリストアを実行 echo "Restoring $db from $dump_file..." gunzip < "$dump_file" | mysql -uroot -p"$password" "$db" if [[ $? -eq 0 ]]; then echo "Restore completed successfully for $db." else echo "Error occurred during restore for $db." fi done echo "All selected restores completed."
復元中の処理を見る
show processlist; +----+------+-----------+----------------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----------------------+---------+------+-----------+------------------------------------------------------------------------------------------------------+ | 7 | root | localhost | test1 | Query | 0 | query end | INSERT INTO `test` VALUES (1557549,1056447,NULL,NULL,NULL, | | 9 | root | localhost | NULL | Query | 0 | starting | show processlist |
windowsのmysqlからの読み込み
mysql -uroot -p < C:\documents\test.sql
mysql5.7からのdumpコマンド
- コマンド内の"--database"は不要
- 削除すれば、そのまま実行できる
クレジットカード番号検索
grep -E ",'[0-9]{16}'," ./ -r grep -E ",'[0-9]{4}-[0-9]{4}-[0-9]{4}-[0-9]{4}'," ./ -r grep -E ",[0-9]{16}," ./ -r
定義書作成
mysqldump --no-data --xml -uuser1 -p schema1 > schema1.xml
できたxmlをここに入れる http://mysql-doc.mariyudu.net/