[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

-- 登錄 MySQL 查詢 binlog 是否開啟
SHOW VARIABLES LIKE '%log_bin%';
-- 示例輸出：
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | log_bin       | ON    |
-- +---------------+-------+

-- 查詢 binlog 格式
SHOW VARIABLES LIKE 'binlog_format';
-- 示例輸出：
-- +---------------+-------+
-- | Variable_name | Value |
-- +---------------+-------+
-- | binlog_format | ROW   |
-- +---------------+-------+

-- 查詢 binlog 存儲路徑
SHOW VARIABLES LIKE 'datadir';
-- 示例輸出：
-- +---------------+-----------------------------+
-- | Variable_name | Value                       |
-- +---------------+-----------------------------+
-- | datadir       | /var/lib/mysql/             |
-- +---------------+-----------------------------+

# 示例：解析 2025-06-20 18:00:00 到 2025-06-20 19:00:00 的 binlog
mysqlbinlog \
  --no-defaults \
  --database=your_database \
  --start-datetime="2025-06-20 18:00:00" \
  --stop-datetime="2025-06-20 19:00:00" \
  /var/lib/mysql/mysql-bin.000015 > recovery.sql

# 查看 recovery.sql 內(nèi)容，找到誤刪的 DELETE/DROP 語句
cat recovery.sql | grep -A 5 "DELETE FROM your_table"

# 手動修改 SQL 語句為 INSERT 或 ROLLBACK
# 示例：將 DELETE 替換為 INSERT
sed 's/DELETE/INSERT/' recovery.sql > filtered.sql

# 導(dǎo)入恢復(fù)數(shù)據(jù)
mysql -u root -p your_database < filtered.sql

# 1. 恢復(fù)全量備份
gzip -d backup.sql.gz | mysql -u root -p

# 2. 恢復(fù)單個(gè)數(shù)據(jù)庫
mysql -u root -p your_database < backup.sql

# 3. 恢復(fù)特定表（需備份文件中包含 CREATE TABLE）
mysql -u root -p your_database < backup.sql

# 1. 解壓備份文件
innobackupex --decompress /path/to/backup

# 2. 應(yīng)用日志
innobackupex --apply-log /path/to/backup

# 3. 復(fù)制數(shù)據(jù)到 MySQL 數(shù)據(jù)目錄
innobackupex --copy-back /path/to/backup
# 需停止 MySQL 服務(wù)后再執(zhí)行
systemctl stop mysql
innobackupex --copy-back /path/to/backup
systemctl start mysql

# 1. 復(fù)制 .ibd 文件到臨時(shí)目錄
cp /var/lib/mysql/your_table.ibd /tmp/

# 2. 修改 my.cnf 啟用 innodb_force_recovery
echo "[mysqld]" >> /etc/my.cnf
echo "innodb_force_recovery = 4" >> /etc/my.cnf

# 3. 啟動 MySQL 并導(dǎo)出數(shù)據(jù)
systemctl restart mysql
mysqldump -u root -p your_database your_table > rescue.sql

# 4. 恢復(fù)數(shù)據(jù)
mysql -u root -p your_database < rescue.sql

# 安裝 ibd2sql（需 Python 3 環(huán)境）
pip install ibd2sql

# 解析 .ibd 文件
ibd2sql -f /var/lib/mysql/your_table.ibd -o output.sql

# 導(dǎo)入恢復(fù)數(shù)據(jù)
mysql -u root -p your_database < output.sql

# 下載并解壓工具
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/tarball/percona-xtrabackup-2.4.18-Linux-x86_64.libgcrypt153.tar.gz
tar -zxvf percona-xtrabackup-2.4.18-Linux-x86_64.libgcrypt153.tar.gz

# 創(chuàng)建備份
./xtrabackup --backup --target-dir=/path/to/backup

# 恢復(fù)備份
./xtrabackup --prepare --target-dir=/path/to/backup
./xtrabackup --copy-back --target-dir=/path/to/backup

# 1. 找到誤刪時(shí)間點(diǎn)（假設(shè)為 2025-06-20 18:30:00）
# 2. 解析 binlog
mysqlbinlog \
  --no-defaults \
  --database=your_database \
  --start-datetime="2025-06-20 18:20:00" \
  --stop-datetime="2025-06-20 19:00:00" \
  /var/lib/mysql/mysql-bin.000015 > recovery.sql

# 3. 編輯 recovery.sql，將 DELETE 替換為 INSERT
sed 's/DELETE/INSERT/' recovery.sql > filtered.sql

# 4. 導(dǎo)入數(shù)據(jù)
mysql -u root -p your_database < filtered.sql

# 1. 停止 MySQL 服務(wù)
systemctl stop mysql

# 2. 復(fù)制備份文件到數(shù)據(jù)目錄
cp -r /backup/mysql_data /var/lib/mysql/

# 3. 修改權(quán)限并啟動
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# 每日全備腳本
0 2 * * * mysqldump -u backup -pP@ssw0rd --all-databases | gzip > /backups/full_$(date +%F).sql.gz

# 每小時(shí) binlog 備份
*/60 * * * * rsync -av /var/log/mysql/mysql-bin.* s3://backup-bucket/binlog/

-- 創(chuàng)建只讀用戶
CREATE USER 'read_only'@'localhost' IDENTIFIED BY 'ReadOnly@123!';
GRANT SELECT ON your_database.* TO 'read_only'@'localhost';

-- 阻止 DELETE 操作
DELIMITER //
CREATE TRIGGER prevent_delete BEFORE DELETE ON your_table
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Delete operation is not allowed!';
END //
DELIMITER ;