mysqldumpを使用してmysqlデータベースをバックアップおよび復元する方法

このチュートリアルでは、mysqldumpユーティリティを使用して、コマンドラインからMySQLまたはMariaDBデータベースをバックアップおよび復元する方法について説明します。

mysqldumpユーティリティによって作成されるバックアップファイルは、基本的に元のデータベースを再作成するために使用できるSQLステートメントのセットです。 mysqldumpコマンドは、CSVおよびXML形式のファイルも生成できます。

mysqldumpユーティリティを使用して、MySQLデータベースを別のMySQLサーバーに転送することもできます。

Mysqldumpコマンド構文

mysqldumpコマンドの使用方法に入る前に、基本的な構文を確認してみましょう。

mysqldumpユーティリティの式は次の形式を取ります。

mysqldump > file.sql

• options -mysqldumpオプション file.sql ダンプ（バックアップ）ファイル

mysqldumpコマンドを使用するには、MySQLサーバーがアクセス可能で実行されている必要があります。

単一のMySQLデータベースのバックアップ

mysqldumpツールの最も一般的な使用例は、単一のデータベースのバックアップです。

たとえば、ユーザー root を使用してdatabase_nameという名前の database_name バックアップを作成し、database_name.sqlという名前のファイルに保存するには、次のコマンドを実行します。

mysqldump -u root -p database_name > database_name.sql

ルートパスワードの入力を求められます。 認証に成功すると、ダンププロセスが開始されます。 データベースのサイズによっては、プロセスに時間がかかる場合があります。

mysqldump database_name > database_name.sql

複数のMySQLデータベースのバックアップ

1つのコマンドで複数のMySQLデータベースをバックアップするには、 --database オプションの後にバックアップするデータベースのリストを使用する必要があります。 各データベース名はスペースで区切る必要があります。

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

上記のコマンドは、両方のデータベースを含むダンプファイルを作成します。

すべてのMySQLデータベースのバックアップ

--all-databases オプションを使用して、すべてのMySQLデータベースをバックアップします。

mysqldump -u root -p --all-databases > all_databases.sql

前の例と同じように、上記のコマンドはすべてのデータベースを含む単一のダンプファイルを作成します。

すべてのMySQLデータベースを個別のファイルにバックアップします

mysqldump ユーティリティには、すべてのデータベースを個別のファイルにバックアップするオプションはありませんが、単純なbash FOR ループで簡単に実現できます。

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

上記のコマンドは、データベース名をファイル名として使用して、データベースごとに個別のダンプファイルを作成します。

圧縮されたMySQLデータベースバックアップを作成する

データベースのサイズが非常に大きい場合は、出力を圧縮することをお勧めします。 これを行うには、単に出力を gzip ユーティリティにパイプし、次に示すようにファイルにリダイレクトします。

mysqldump database_name | gzip > database_name.sql.gz

タイムスタンプを使用してバックアップを作成する

mysqldump database_name > database_name-$(date +%Y%m%d).sql

上記のコマンドは、次の形式のファイルを作成します database_name-20180617.sql

MySQLダンプの復元

mysql ツールを使用してMySQLダンプを復元できます。 コマンドの一般的な構文は次のとおりです。

mysqld database_name < file.sql

ほとんどの場合、インポートするデータベースを作成する必要があります。 データベースが既に存在する場合、最初に削除する必要があります。

次の例では、最初のコマンドはdatabase_nameという名前の database_name を作成してから、そこにダンプ database_name.sql をインポートし database_name.sql 。

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

完全なMySQLダンプから単一のMySQLデータベースを復元する

mysql --one-database database_name < all_databases.sql

1つのコマンドでMySQLデータベースをエクスポートおよびインポートする

あるデータベースからダンプファイルを作成してから、別のMySQLデータベースにバックアップをインポートする代わりに、次のワンライナーを使用できます。

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

上記のコマンドは、出力をリモートホスト上のmysqlクライアントにパイプし、 remote_database_name という名前のデータベースにインポートします。 コマンドを実行する前に、データベースがリモートサーバーに既に存在することを確認してください。

Cronでバックアップを自動化する

データベースのバックアッププロセスの自動化は、指定された時間にmysqldumpコマンドを実行するcronジョブを作成するのと同じくらい簡単です。

cronjobを使用してMySQLデータベースの自動バックアップを設定するには、次の手順に従います。

1. ユーザーのホームディレクトリに .my.cnf という名前のファイルを作成します。

   sudo nano ~/.my.cnf

   次のテキストをコピーして、.my.cnfファイルに貼り付けます。

   user = dbuser password = dbpasswd

   dbuser と dbpasswd をデータベースユーザーとユーザーのパスワードに置き換えることを忘れないでください。

   ユーザーのみがアクセスできるように、資格情報ファイルのアクセス許可を制限します。

   chmod 600 ~/.my.cnf

   バックアップを保存するディレクトリを作成します。

   mkdir ~/db_backups

   ユーザーのcrontabファイルを開きます。

   crontab -e

   データベース名 mydb バックアップを毎日午前3時に作成する次のcronジョブを追加します。

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   username を実際のユーザー名に置き換えることを忘れないでください。

別のcronjobを作成して、30日より古いバックアップを削除することもできます。

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

もちろん、バックアップの場所とファイル名に従ってコマンドを調整する必要があります。 findコマンドの詳細については、コマンドラインガイドを使用してLinuxでファイルを検索する方法をご覧ください。

結論

このチュートリアルでは基本事項のみを説明しますが、mysqldumpユーティリティを使用してコマンドラインからMySQLデータベースを作成および復元する方法を学習したい人にとっては良いスタートになるはずです。

また、MySQLルートパスワードを忘れた場合にリセットする方法についてのチュートリアルを確認することもできます。

mysql mariadbバックアップmysqldump