はじめに
こんにちは、シルです。
SQLの学習にあたり、学んだことメモしておきます。
学習にお役立てください。
参考までに…
SQLコマンド集：基本から応用
前回までの内容です
SQL#1 環境準備と基本操作
SQL#2 テーブルとデータベースの管理
SQL#3 ユーザー管理と権限の設定
SQL#4 サーバーステータスとインデックス管理
SQL#5 データベースのサイズ管理と構造確認
SQL#6 基本的なデータ操作
SQL#7 データ検索と削除の拡張
SQL#8 ウィンドウ関数を使ったデータ分析
SQL#9 集計と結合
SQL#10 インデックス管理と最適化
学習の概要
データベースは重要な情報を管理するため、データの損失や破損を防ぐために定期的なバックアップと適切なリカバリ手法が必要です。本記事では、MySQLのバックアップとリカバリの基本から、実務で使える高度な手法までを学びます。
バックアップを適切に取得し、障害発生時に迅速にデータを復元できるようにすることで、システムの可用性を高めることができます。また、バイナリログを活用した高度な復旧手法や、データのエクスポート・インポート方法についても解説します。
含まれるSQL操作
- mysqldump – データベース全体のバックアップ
- テーブル単位のバックアップ – 特定のテーブルのみをバックアップ
- バックアップファイルからのリストア – データベースの復元
- SELECT INTO OUTFILE – データをファイルへエクスポート
- LOAD DATA INFILE – ファイルからデータをインポート
- SHOW MASTER STATUS – バイナリログの確認
- バイナリログからのバックアップ – 変更履歴を使った復旧
- テーブルのクローン作成 – テーブルの複製
1. 環境構築
① MySQLのインストール
MySQLがインストールされていない場合は、以下の手順でインストールしてください。
Windowsの場合
- MySQL公式サイト からMySQLをダウンロードし、インストール。
mysql.exeまたは MySQL Workbench で接続できることを確認。
Linux/macOSの場合
ターミナルで以下のコマンドを実行：
sudo apt update # Ubuntuの場合
sudo apt install mysql-server -y # MySQLをインストール
起動の確認：
sudo systemctl start mysql
sudo systemctl enable mysql
MySQLにログイン：
mysql -u root -p
② データベースとテーブルの作成
バックアップ・リカバリを試すために、EmployeeDBデータベースとEmployeesテーブルを作成します。
CREATE DATABASE EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
データの挿入：
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES
('John', 'Doe', 'HR', 50000),
('Jane', 'Smith', 'IT', 60000),
('Mike', 'Johnson', 'Finance', 55000),
('Sara', 'Williams', 'HR', 52000),
('James', 'Brown', 'IT', 61000);実行後のイメージ：
2. 実践の準備
① mysqldumpの設定
mysqldumpを利用するには、ターミナルまたはコマンドプロンプトでMySQLにログインできることを確認。
mysql -u root -p
確認後、データベース全体のバックアップができるかテスト：
mysqldump -u root -p EmployeeDB > employee_backup.sql
バックアップファイルが正しく作成されたか確認：
ls -lh | grep employee_backup.sql # Linux/macOS
dir employee_backup.sql # Windows
（Windowsならエクスプローラーで該当フォルダを開いて確認）
② LOAD DATA INFILE の利用準備
ファイルからデータをインポートする際、MySQLの セキュリティ設定 によりエラーが出る可能性があります。
1. 設定変更
エラーを防ぐため、MySQLの設定を変更します。
SET GLOBAL local_infile = 1;
設定が適用されたか確認：
SHOW VARIABLES LIKE 'local_infile';
もしlocal_infileが OFF のままなら、my.cnf または my.ini ファイルの [mysqld] セクションに以下を追加し、MySQLを再起動。
[mysqld]
local-infile=1
MySQLの再起動（Linux/macOS）：
sudo systemctl restart mysql
MySQLの再起動（Windows）：
net stop MySQL80
net start MySQL80
③ SELECT INTO OUTFILE のパス設定
データをエクスポートする際、MySQLが書き込めるディレクトリに制限があるため、適切なフォルダを指定する。
- MySQLのデータディレクトリを確認：
SHOW VARIABLES LIKE 'secure_file_priv';
（例: /var/lib/mysql-files/ または C:\ProgramData\MySQL\MySQL Server X.X\Data）
- そのフォルダ内での操作を許可：
SELECT * FROM Employees
INTO OUTFILE '/var/lib/mysql-files/employees.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
- Windowsなら、Cドライブ直下に書き込む：
SELECT * FROM Employees
INTO OUTFILE 'C:/mysql_export/employees.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
・エクスポートされたファイルが存在するか確認
・内容を確認
・notepad で開く
3. バイナリログの設定
バイナリログを利用したリカバリを行う場合、設定を変更する必要があります。
① バイナリログの有効化
my.cnf または my.ini の編集方法
MySQLの バイナリログを有効化 するためには、MySQLの設定ファイル (my.cnf または my.ini) に以下の設定を追加する必要があります。
[mysqld]
log-bin=mysql-bin
server-id=1
🔍 設定ファイルの場所を確認
Windows の場合
MySQLの my.ini は通常 以下のパスに存在 します：
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
確認方法
dir "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
Linux/macOS の場合
MySQLの my.cnf は通常 以下のどこかに存在 します：
/etc/my.cnf/etc/mysql/my.cnf/usr/local/mysql/my.cnf
確認方法
bashコピーする編集するfind /etc /usr/local -name "my.cnf"
✏️ ファイルを開いて編集
Windows の場合
- 管理者権限で
my.iniを開く
コマンドプロンプト (cmd) または PowerShell を管理者として開く
コマンドを実行：notepad "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" - ファイル内の
[mysqld]セクションを探し、以下を追加[mysqld]log-bin=mysql-binserver-id=1 - ファイルを保存して閉じる
Linux/macOS の場合
- 管理者権限で
my.cnfを開くbashコピーする編集するsudo nano /etc/my.cnfまたはbashコピーする編集するsudo nano /etc/mysql/my.cnf [mysqld]セクションを探し、以下を追加iniコピーする編集する[mysqld] log-bin=mysql-bin server-id=1- Ctrl + X → Y → Enter で保存して閉じる
🚀 MySQLを再起動
設定を反映するために MySQLを再起動 します。
Windows の場合
net stop MySQL80
net start MySQL80
または サービスを再起動：
sc stop MySQL80
sc start MySQL80Linux/macOS の場合
sudo systemctl restart mysql
または
sudo service mysql restart
✅ 設定が反映されたか確認
MySQLにログインして、バイナリログが有効になっているか確認：
SHOW VARIABLES LIKE 'log_bin';
結果：
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| log_bin | ON |
+---------------+-----------+
ON になっていれば バイナリログが有効化 されています。
② SHOW MASTER STATUS の確認
バイナリログの状態を確認。
SHOW MASTER STATUS;
出力の解釈
| 項目 | 説明 |
|---|---|
| File | 現在のバイナリログファイルの名前 ( |
| Position | バイナリログ内の最新の書き込み位置 (1152) |
| Binlog_Do_DB | バイナリログに記録する対象のデータベース（空白なので全データベースが対象） |
| Binlog_Ignore_DB | バイナリログに記録しないデータベース（空白なので無指定） |
| Executed_Gtid_Set | GTID (Global Transaction ID) の情報（空白なのでGTIDは未使用） |
現在の状態
- バイナリログ (
YOURNAME-bin.000008) が有効
→ バイナリログが記録されており、障害時の復旧やレプリケーションに使用可能。 - 最新の書き込み位置 (
Position: 1152)
→ この位置までの変更履歴がバイナリログに記録されている。 Binlog_Do_DBとBinlog_Ignore_DBが空
→ 全データベースの変更がバイナリログに記録される設定。Executed_Gtid_Setが空白
→ GTID (Global Transaction ID) ベースのレプリケーションは使用されていない。
③ バイナリログのバックアップ
特定の時点までの変更をバックアップ。
mysqlbinlog C:\ProgramData\MySQL\MySQL Server 8.0\Data\SUBLIMATION01-bin.000008 > C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog_backup.sql
MySQLに適用するには：
mysql -u root -p EmployeeDB < binlog_backup.sql
4. すべての環境準備チェックリスト
✅ MySQLがインストールされている
✅ データベースEmployeeDBとEmployeesテーブルを作成し、データを挿入
✅ mysqldump コマンドが動作する
✅ local_infile = 1 が有効化されている
✅ SELECT INTO OUTFILE を正しく実行できるディレクトリを確認
✅ バイナリログが有効化されている (SHOW MASTER STATUS が動作する)
実践
mysqldump – データベース全体のバックアップ
MySQLのmysqldumpコマンドを使用して、データベース全体のバックアップを取得します。
mysqldump -u root -p EmployeeDB > employee_backup.sql
このコマンドを実行すると、employee_backup.sqlというファイルが作成され、データベースの完全なスナップショットが保存されます。
テーブル単位のバックアップ
特定のテーブルのみをバックアップしたい場合は、以下のように指定します。
mysqldump -u root -p EmployeeDB Employees > employees_backup.sql
このコマンドでは、「Employees」テーブルのみをバックアップします。
バックアップファイルからのリストア
バックアップファイルを使ってデータベースを復元するには、以下のコマンドを実行します。
mysql -u root -p EmployeeDB < employee_backup.sql
このコマンドは、バックアップファイルの内容をデータベースに復元します。
SELECT INTO OUTFILE – データをファイルへエクスポート
特定のテーブルのデータをCSVファイルとしてエクスポートすることも可能です。
SELECT * FROM Employees
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employees.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
これにより、「Employees」テーブルのデータがCSVファイルとしてエクスポートされます。
LOAD DATA INFILE – ファイルからデータをインポート
エクスポートしたデータを再度データベースに取り込むには、以下のコマンドを使用します。
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employees.csv'
INTO TABLE Employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
これにより、エクスポートしたCSVファイルのデータが「Employees」テーブルにインポートされます。
SHOW MASTER STATUS – バイナリログの確認
バイナリログは、データベース内で発生した変更を記録するログファイルです。SHOW MASTER STATUS コマンドを実行すると、現在のバイナリログの状態を確認できます。
SHOW MASTER STATUS;
この情報は、データの復旧やレプリケーションの設定時に役立ちます。
バイナリログからのバックアップ
バイナリログを利用して、データベースの変更を復元する方法もあります。
mysqlbinlog C:/ProgramData/MySQL/MySQL Server 8.0/Data | mysql -u root -p EmployeeDB
これにより、指定したバイナリログの変更内容をデータベースに適用できます。
テーブルのクローン作成
テーブルを複製したい場合、以下のSQLを使用します。
CREATE TABLE Employees_backup AS
SELECT * FROM Employees;
このクエリにより、「Employees」テーブルのコピーが「Employees_backup」という名前で作成されます。
テストデータの準備
まず、「EmployeeDB」データベースと「Employees」テーブルを作成し、データを挿入します。
CREATE DATABASE EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
INSERT INTO Employees (FirstName, LastName, Department, Salary)
VALUES
('John', 'Doe', 'HR', 50000),
('Jane', 'Smith', 'IT', 60000),
('Mike', 'Johnson', 'Finance', 55000),
('Sara', 'Williams', 'HR', 52000),
('James', 'Brown', 'IT', 61000);
このデータをもとに、各バックアップおよびリカバリ操作を実践してください。
課題
- mysqldumpを使ってEmployeeDBのバックアップを取得し、適切なファイル名で保存してください。
- 「Employees」テーブルだけをバックアップする方法を試してください。
- バックアップファイルをリストアし、元のデータが復元できるか確認してください。
- 「Employees」テーブルのデータをCSVファイルにエクスポートし、再度インポートしてください。
- SHOW MASTER STATUSコマンドを実行し、バイナリログの情報を確認してください。
- 「Employees」テーブルのクローンを作成し、データを複製してください。
まとめ
この記事では、MySQLにおけるバックアップとリカバリの方法について学びました。
mysqldumpを使用したデータベース全体およびテーブル単位のバックアップSELECT INTO OUTFILEを用いたデータのエクスポートLOAD DATA INFILEを利用したデータのインポート- バイナリログを活用したバックアップとリカバリ
- クローンテーブルの作成
これらの手法を適切に組み合わせることで、データの安全性を高め、万が一の障害時にも迅速に復旧できるようになります。次回は、さらに高度なデータ管理テクニックを学んでいきましょう！
コメント