Mysql/ダンプ・リストア

バックアップと復元です。

バックアップ(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/

参考：https://qiita.com/mamy1326/items/c0aa9252d61ffed31a6e