使用MEB(MySQL Enterprise Backup)還原Slave Database 及設置 replication

最近在幫公司的一個系統設置 MySQL Master-Slave Database 環境，之前大多是使用mysqldump 來做備份，因此用法大致上沒有問題，但由於MySQL 官方推薦的備份工具是使用 MEB, 所以在還原資料庫上使用感覺好像更方便點

記錄如下

依你MEB 還原的指令，我的習慣是會將備份及還原的過程記錄導至文本檔案內 以供其它腳本或後續查詢，我的還原指令如下：

# nohup /usr/local/bin/mysqlbackup --defaults-file=/opt/backup/backupDB/incremental/2020-06-08_05-30-02/server-all.cnf --backup-dir=/opt/backup/backupDB/incremental/2020-06-08_05-30-02 --incremental  --force --verbose copy-back-and-apply-log 2>&1 | tee -a /var/log/restore_increment.log &

待還原後檢視最後幾行處

# tail -10  /var/log/restore_increment.log

.....
....
200608 11:07:24 PCR1 INFO: We were able to parse ibbackup_logfile up to
lsn 1946415056823.
200608 11:07:24 PCR1 INFO: Last MySQL binlog file position 0 48470147, file name etmall-bin.000001
200608 11:07:24 PCR1 INFO: The first data file is '/var/lib/mysql/ibdata1'
and the new created log files are at '/var/lib/mysql/'
200608 11:07:24 MAIN INFO: No Keyring file to process.
200608 11:07:24 MAIN INFO: Apply-log operation completed successfully.
200608 11:07:24 MAIN INFO: Incremental backup applied successfully.

在你的 mysql> 下建立 replication user

USE mysql;
CREATE USER 'replication'@'%' IDENTIFIED BY 'XXXXXXXX';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

在 slave 上執行

STOP SLAVE;
 
CHANGE MASTER TO
  MASTER_HOST='10.0.0.6',
  MASTER_USER='replication',
  MASTER_PASSWORD='XXXXXXXX',
  MASTER_LOG_FILE='etmall-bin.000001',
  MASTER_LOG_POS=48470147;
 
START SLAVE;

完成收工