TỔNG QUAN CHƯƠNG TRÌNH THỰC HÀNH

⚙️ PHẦN A — THỰC HÀNH ĐỒNG BỘ (REPLICATION)

🎯 Mục tiêu

• Hiểu cơ chế replication (binlog → relay log → SQL thread).

• Cấu hình master – replica cơ bản.

• Kiểm tra trạng thái và xử lý lỗi đồng bộ.

🧩 Bài 1 — Thiết lập replication master–replica (cơ bản)

1️⃣ Chuẩn bị

• Hai máy (hoặc container):

  • master: 192.168.1.10, MySQL 8.x

  • replica: 192.168.1.11, MySQL 8.x

• Cả hai dùng InnoDB, server-id khác nhau.

2️⃣ Cấu hình my.cnf

Trên master:

Trên replica:

3️⃣ Tạo user replication

4️⃣ Lấy vị trí binlog

Kết quả mẫu:

5️⃣ Cấu hình replica

Trên máy replica:

6️⃣ Kiểm tra trạng thái

✅ Nếu Replica_IO_Running và Replica_SQL_Running = Yes, replication hoạt động.

7️⃣ Kiểm thử

Trên master:

Trên replica:

🧩 Bài 2 — Sử dụng GTID (Global Transaction ID)

Trên cả master & replica, bật GTID:

Cấu hình lại replication:

Kiểm tra GTID:

🧩 Bài 3 — Xử lý sự cố replication

Tình huống: Replica bị lỗi Duplicate entry
Hướng dẫn khắc phục:

Hoặc dùng:

💾 PHẦN B — BACKUP & RESTORE CƠ BẢN

🎯 Mục tiêu

• Hiểu sự khác biệt giữa backup logical và physical.

• Thực hành sao lưu & phục hồi với mysqldump và xtrabackup.

🧩 Bài 4 — Backup bằng mysqldump

Sao lưu toàn bộ DB:

Sao lưu riêng một schema:

Phục hồi:

🧩 Bài 5 — Backup vật lý với Percona XtraBackup (nâng cao)

Cài đặt (nếu chưa có):

Backup:

Chuẩn bị restore:

Phục hồi:

⏳ PHẦN C — PHỤC HỒI THEO THỜI ĐIỂM (PITR)

🎯 Mục tiêu

• Thực hành khôi phục đến một thời điểm cụ thể trước khi lỗi xảy ra.

• Dùng binary log để áp dụng giao dịch.

🧩 Bài 6 — Phục hồi PITR

1️⃣ Sao lưu cơ sở

2️⃣ Lưu lại vị trí binlog hiện tại

3️⃣ Giả lập lỗi

4️⃣ Phục hồi cơ sở backup

5️⃣ Áp dụng binlog đến thời điểm mong muốn

✅ Kết quả: dữ liệu được khôi phục đến trước thời điểm lỗi.

BỘ BÀI TẬP NÂNG CAO (MYSQL REPLICATION – BACKUP – AUTOMATION)

🧩 Bài 1: Thiết lập Multi-Source Replication (1 replica từ 2 master)

🎯 Mục tiêu

• Cấu hình 1 replica đồng bộ dữ liệu từ hai master khác nhau (multi-source replication).
• Quan sát cơ chế tách biệt channel trên replica.

🔧 Bước 1: Chuẩn bị môi trường

Cấu hình chung:

• MySQL 8.x
• InnoDB
• gtid_mode=ON, enforce_gtid_consistency=ON trên cả 3 node.

⚙️ Bước 2: Cấu hình my.cnf

Trên replica:

[mysqld]
server-id = 3
relay_log = mysql-relay-bin
log_bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1

🔐 Bước 3: Tạo user replication trên hai master

Trên mỗi master:

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplPass!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

🧭 Bước 4: Cấu hình 2 channel replication

Trên replica:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.10',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='ReplPass!',
  SOURCE_AUTO_POSITION=1
FOR CHANNEL 'master1';

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.11',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='ReplPass!',
  SOURCE_AUTO_POSITION=1
FOR CHANNEL 'master2';

Khởi động:

START REPLICA FOR CHANNEL 'master1';
START REPLICA FOR CHANNEL 'master2';

✅ Bước 5: Kiểm tra trạng thái

SHOW REPLICAS STATUS;

(hoặc SHOW REPLICA STATUS FOR CHANNEL 'master1'\G)

Nếu cả hai channel đều Running, replication hoạt động song song.

🔍 Kiểm thử

• Trên master1:

  CREATE DATABASE db_sales;
  

• Trên master2:

  CREATE DATABASE db_hr;
  

• Trên replica:

  SHOW DATABASES;
  

  ➜ Thấy cả db_sales và db_hr xuất hiện.

⚠️ Ghi chú

• Tránh trùng tên DB giữa hai master.
• Khi gặp xung đột, nên chỉ định replication filter hoặc dùng schema riêng biệt.
• Dùng STOP REPLICA FOR CHANNEL 'masterX' để tạm dừng từng luồng.

🧩 Bài 2: Tự động sao lưu hằng ngày bằng Event Scheduler

🎯 Mục tiêu

• Tạo event tự động chạy lệnh backup mỗi đêm.
• Ghi log backup vào ADMIN_DB.backup_log.

🔧 Bước 1: Bật event scheduler

SET GLOBAL event_scheduler = ON;

⚙️ Bước 2: Tạo stored procedure sao lưu

DELIMITER $$
CREATE PROCEDURE sp_auto_backup()
BEGIN
  SET @bk_file = CONCAT('/backups/full_backup_', DATE_FORMAT(NOW(),'%Y%m%d'), '.sql');
  SET @cmd = CONCAT('mysqldump --all-databases > ', @bk_file);
  SET @res = sys_exec(@cmd); -- (nếu dùng plugin sys)
  
  INSERT INTO ADMIN_DB.backup_log (db_name, backup_type, file_path, backup_size_mb, duration_sec, status)
  VALUES ('ALL', 'FULL', @bk_file, 0, 0, 'SUCCESS');
END$$
DELIMITER ;

💡 Nếu không có sys_exec, bạn có thể chạy script shell bên ngoài hoặc dùng event chỉ ghi log.

⏰ Bước 3: Tạo event tự động

CREATE EVENT ev_daily_backup
ON SCHEDULE EVERY 1 DAY STARTS '2025-10-24 00:00:00'
DO CALL sp_auto_backup();

🔍 Kiểm thử

SHOW EVENTS FROM ADMIN_DB;
CALL sp_auto_backup();
SELECT * FROM ADMIN_DB.backup_log ORDER BY recorded_at DESC;

⚠️ Ghi chú

• Đảm bảo MySQL user có quyền ghi file (OUTFILE).
• Nên lưu backup ra thư mục mount ngoài container / server.

🧩 Bài 3: Tạo script kiểm tra replication & gửi cảnh báo

🎯 Mục tiêu

• Kiểm tra trạng thái replication định kỳ.
• Nếu có lỗi, ghi alert vào ADMIN_DB.alert_log.

⚙️ Bước 1: Tạo procedure kiểm tra

DELIMITER $$
CREATE PROCEDURE sp_check_replication()
BEGIN
  DECLARE io_state VARCHAR(10);
  DECLARE sql_state VARCHAR(10);

  SELECT Replica_IO_Running, Replica_SQL_Running
  INTO io_state, sql_state
  FROM performance_schema.replication_applier_status_by_worker
  LIMIT 1;

  IF io_state <> 'YES' OR sql_state <> 'YES' THEN
    INSERT INTO ADMIN_DB.alert_log (alert_type, severity, message, status)
    VALUES ('REPLICATION_ERROR','CRITICAL','Replica thread stopped!','OPEN');
  END IF;
END$$
DELIMITER ;

⏰ Bước 2: Lên lịch kiểm tra mỗi 5 phút

CREATE EVENT ev_check_replica
ON SCHEDULE EVERY 5 MINUTE
DO CALL sp_check_replication();

🔍 Bước 3: Kiểm thử

• Tạm dừng replica:

  STOP REPLICA;
  

• Đợi 5 phút hoặc gọi thủ công:

  CALL sp_check_replication();
  SELECT * FROM ADMIN_DB.alert_log WHERE alert_type='REPLICATION_ERROR';
  

⚠️ Ghi chú

• Có thể mở rộng thêm: gửi email bằng script ngoài hoặc webhook API.
• DBA nên kiểm tra thủ công mỗi ngày nếu chưa có automation tool.

🧩 Bài 4: Ghi lịch sử backup & giám sát dung lượng

🎯 Mục tiêu

• Ghi lại metadata mỗi lần backup.
• So sánh kích thước DB với ngưỡng trong quota_threshold.

⚙️ Bước 1: Bổ sung bảng ghi log

CREATE TABLE IF NOT EXISTS ADMIN_DB.backup_audit (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  db_name VARCHAR(100),
  backup_path VARCHAR(255),
  backup_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  size_mb DECIMAL(10,2),
  verified ENUM('YES','NO') DEFAULT 'NO'
);

⚙️ Bước 2: Stored procedure ghi log sau mỗi backup

DELIMITER $$
CREATE PROCEDURE sp_record_backup(IN p_db VARCHAR(100), IN p_path VARCHAR(255), IN p_size DECIMAL(10,2))
BEGIN
  INSERT INTO ADMIN_DB.backup_audit (db_name, backup_path, size_mb, verified)
  VALUES (p_db, p_path, p_size, 'NO');
END$$
DELIMITER ;

⚙️ Bước 3: Kiểm tra quota và tạo cảnh báo

DELIMITER $$
CREATE PROCEDURE sp_check_quota_usage()
BEGIN
  INSERT INTO ADMIN_DB.alert_log (alert_type, severity, message, db_name)
  SELECT 'QUOTA_EXCEEDED','CRITICAL',
         CONCAT('DB ', s.db_name, ' size ', s.total_size_mb, 'MB > threshold ', q.threshold_mb, 'MB'),
         s.db_name
  FROM ADMIN_DB.space_usage s
  JOIN ADMIN_DB.quota_threshold q ON s.db_name=q.db_name
  WHERE s.total_size_mb > q.threshold_mb;
END$$
DELIMITER ;

⏰ Bước 4: Lên lịch kiểm tra mỗi ngày

CREATE EVENT ev_check_quota_daily
ON SCHEDULE EVERY 1 DAY
DO CALL sp_check_quota_usage();

🔍 Kiểm thử

• Giả lập DB vượt ngưỡng bằng cách:

  UPDATE ADMIN_DB.space_usage
  SET total_size_mb = 200
  WHERE db_name='sales_db';
  CALL sp_check_quota_usage();
  SELECT * FROM ADMIN_DB.alert_log WHERE alert_type='QUOTA_EXCEEDED';
  

⚠️ Ghi chú

• Mỗi cảnh báo phải có severity rõ ràng để phân loại phản ứng.
• Có thể thêm trigger tự động cập nhật verified='YES' khi restore thành công.

🧠 TỔNG KẾT SAU 4 BÀI NÂNG CAO

🌟 Bài luyện tập mở rộng (tùy chọn)

1. Tạo procedure tổng hợp nhật ký backup/alert gửi email hàng ngày.
2. Dùng view kết hợp (v_admin_dashboard) hiển thị tình trạng toàn hệ thống (replication, quota, alert count).
3. Tích hợp Prometheus exporter đọc dữ liệu từ bảng space_usage để vẽ dashboard trong Grafana.

#!/usr/bin/env python3
"""
mysql_backup.py
- Usage: python mysql_backup.py
- Config at top of file
"""
import os
import subprocess
import gzip
import shutil
from datetime import datetime, timedelta

# === CONFIG ===
MYSQLDUMP_PATH = r"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"  # or "mysqldump" if in PATH
BACKUP_DIR = r"D:\MySQL_Backup"
DB_NAME = "giangtt"
DB_USER = "hocvien"
DB_PASS = "hocvien@123"
KEEP_DAYS = 7
# =============

os.makedirs(BACKUP_DIR, exist_ok=True)
ts = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
sqlfile = os.path.join(BACKUP_DIR, f"{DB_NAME}_full_{ts}.sql")
gzfile = sqlfile + ".gz"

# Build mysqldump command
cmd = [
    MYSQLDUMP_PATH,
    f"-u{DB_USER}",
    f"-p{DB_PASS}",
    "--single-transaction",
    "--routines",
    "--events",
    "--triggers",
    DB_NAME
]

print("Running mysqldump...")
with open(sqlfile, "wb") as out:
    proc = subprocess.run(cmd, stdout=out, stderr=subprocess.PIPE)
    if proc.returncode != 0:
        print("ERROR: mysqldump failed:", proc.stderr.decode())
        # remove incomplete file
        if os.path.exists(sqlfile):
            os.remove(sqlfile)
        raise SystemExit(1)

print("Compressing...")
with open(sqlfile, "rb") as f_in, gzip.open(gzfile, "wb") as f_out:
    shutil.copyfileobj(f_in, f_out)
os.remove(sqlfile)

print("Backup created:", gzfile)

# Cleanup old backups
cutoff = datetime.now() - timedelta(days=KEEP_DAYS)
for fname in os.listdir(BACKUP_DIR):
    if fname.endswith(".gz"):
        path = os.path.join(BACKUP_DIR, fname)
        mtime = datetime.fromtimestamp(os.path.getmtime(path))
        if mtime < cutoff:
            print("Removing old backup:", path)
            os.remove(path)

print("Done.")

python "C:\scripts\mysql_backup.py"

gzip -d D:\MySQL_Backup\giangtt_full_2025-10-23_02-00-00.sql.gz
mysql -u hocvien -p"hocvien@123" giangtt < giangtt_full_2025-10-23_02-00-00.sql

// dump_giangtt.js
// Run with: mysqlsh --uri root@localhost:3306 --file=dump_giangtt.js
var util = require('util'); // may not be necessary in some mysqlsh versions

// Config
var targetDir = "C:/temp/mysqlsh_dumps";
var dbList = ["giangtt"];

// Create folder if needed (Node fs not always available in mysqlsh; use shell command)
shell.run("mkdir -p \"" + targetDir + "\"");

// Run dumpSchemas
print("Starting dump of schemas:", dbList);
util.dumpSchemas(dbList, targetDir, {compression: "gzip"})
  .then(function() {
    print("Dump completed to:", targetDir);
  })
  .catch(function(err) {
    print("ERROR during dump:", err);
  });

cd "C:\Program Files\MySQL\MySQL Shell 8.0\bin"
mysqlsh --uri hocvien@127.0.0.1:3306 --password --file "C:\path\to\dump_giangtt.js"
# mysqlsh sẽ prompt password; hoặc use --password=YOURPWD (không khuyến nghị)

# dump_giangtt.py
target_dir = r"C:\temp\mysqlsh_dumps"
db_list = ["giangtt"]

import os
os.makedirs(target_dir, exist_ok=True)

print("Starting dump...")
util.dumpSchemas(db_list, target_dir, {"compression":"gzip"})
print("Done.")

mysqlsh --uri hocvien@127.0.0.1:3306 --password --py --file C:\path\to\dump_giangtt.py

// trong mysqlsh prompt hoặc file .js
util.loadDump("C:/temp/mysqlsh_dumps/giangtt/", { schema: "giangtt", skipCreateSchema: false });

mysqlsh --uri hocvien@127.0.0.1:3306 --py -e "util.loadDump('C:/temp/mysqlsh_dumps/giangtt/')" --password

BACKUP VỚI MYSQL SHELL (JAVASCRIPT)

⚙️ 1. Cài MySQL Shell

Nếu chưa có:

• Tải tại https://dev.mysql.com/downloads/shell/
• Sau khi cài, mở Command Prompt và gõ thử:

  mysqlsh --version
  

🧠 2. Tạo file script backup.js

Mở Notepad → dán nội dung sau:

// backup.js
// Backup dữ liệu MySQL bằng MySQL Shell

// Kết nối tới MySQL
shell.connect('hocvien@192.168.1.62:3306', 'hocvien@123');

// Khai báo thư mục backup (ví dụ D:\Backup)
var backupDir = "D:/Backup";

// Gọi hàm dump toàn bộ dữ liệu
var dumpInstance = util.dumpInstance(backupDir, {
    threads: 4,
    dryRun: false
});

print("✅ Backup hoàn tất! File được lưu tại: " + backupDir);

💡 util.dumpInstance() là hàm tích hợp trong MySQL Shell dùng để dump toàn bộ instance — tương đương mysqldump nhưng nhanh hơn, nén tốt hơn.

🚀 3. Chạy script

Mở Command Prompt, di chuyển đến thư mục chứa file backup.js, rồi chạy:

mysqlsh --js -f backup.js

Kết quả: MySQL Shell sẽ tạo một thư mục chứa các file .sql hoặc .zst (nếu nén), lưu tại D:\Backup.

🧩 PHẦN 2. BACKUP VỚI PYTHON (MySQL Shell Mode)

MySQL Shell cũng chạy được Python mode.

1️⃣ Tạo file backup.py

# backup.py
# Backup dữ liệu MySQL bằng Python trong MySQL Shell

shell.connect('hocvien@192.168.1.62:3306', 'hocvien@123')

backup_dir = "D:/Backup_Python"

util.dumpInstance(backup_dir, {
    threads: 4,
    dryRun: False
})

print("✅ Backup hoàn tất! File được lưu tại:", backup_dir)

2️⃣ Chạy file:

mysqlsh --py -f backup.py

🕐 3. Thiết lập lịch backup tự động (Windows Task Scheduler)

1. Mở Task Scheduler → Create Basic Task
2. Đặt tên: “MySQL Auto Backup JS”
3. Chọn Daily (hoặc Weekly)
4. Action → Start a program
5. Trong “Program/script”, nhập:

   mysqlsh
   

6. Trong “Add arguments”, nhập:

   --js -f "D:\Scripts\backup.js"
   

7. Chọn Finish.

✅ Từ nay Windows sẽ tự động chạy backup định kỳ qua MySQL Shell.

📦 4. Phục hồi lại (Restore)

Sau khi có thư mục backup, cô có thể dùng lại MySQL Shell:

mysqlsh --js

Rồi chạy:

util.loadDump("D:/Backup", {threads:4, dryRun:false});

🎯 Tóm tắt: