最近在幫公司的一個系統設置 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;
完成收工
你必須 登入 才能發表評論。