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 ""

# テーブル情報を含めるか確認
echo -n "DROP CREATE 文含める? (y/n): "
read include_structure

# ダンプファイルの保存先ディレクトリを確認・作成
output_dir="data"
mkdir -p "$output_dir"

# ダンプするデータベースのリスト
databases=(
    "schema1"
    "schema2"
)

# 各データベースのダンプを実行
for db in "${databases[@]}"; do
    if [[ "$include_structure" == "y" || "$include_structure" == "Y" ]]; then
        output_file="${output_dir}/${db}.sql.gz"
        mysqldump -uroot -p"$password" "$db" --default-character-set=utf8mb4 | gzip > "$output_file"
    else
        output_file="${output_dir}/${db}_data.sql.gz"
        mysqldump -uroot -p"$password" -t "$db" --default-character-set=utf8mb4 | gzip > "$output_file"
    fi

    echo "Dumped $db to $output_file"
done

まとめてリストア

#!/bin/bash

# パスワードを一度だけ入力
echo -n "Enter MySQL password: "
read -s password
echo ""

# テーブル情報を含めるか確認
echo -n "DROP CREATE 文含めてリストア? (y/n): "
read include_structure

# リストア対象のデータベースのリスト
databases=(
    "schema1"
    "schema2"
)

# リストア実行
for db in "${databases[@]}"; do
    if [[ "$include_structure" == "y" || "$include_structure" == "Y" ]]; then
        dump_file="data/${db}.sql.gz"
    else
        dump_file="data/${db}_data.sql.gz"
    fi

    # ダンプファイルが存在するか確認
    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

復元中の処理を見る

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