Xtrabackup Database Hot backup record

Xtrabackup Database Hot backup record

• Installing Xtrabackup
  • wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb
  • sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb
  • sudo apt-get update
  • sudo apt-get install percona-xtrabackup-24
• Create DATABASE Backup user and database backup storage directory
  • Backing up user users
    • If you already have a backup user, skip to the next step
      • CREATE USER ‘bkpuser‘@‘localhost‘ IDENTIFIED BY ‘bkppassword‘;
    • Increase permissions for backup users
      • GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘bkpuser‘@‘localhost‘;
      • flush privileges;
  • Create a storage directory
    • mkdir -p /home/dw/backup/fullbak
    • mkdir -p /home/dw/backup/incbak
• Full backup
  • innobackupex --user=dw --password=123 --no-timestamp path_to_fullbak;
• Incremental backup
  • innobackupex --user=dw --password=123 --no-timestamp --incremental path_to_increbak --incremental-basedir=which_backup_to_base;
  • Note that each incremental backup needs to specify the increment it is based on which backup to make, either for a full backup or for the previous incremental backup
• Data Recovery
  • Backup prepare
    • full backup restore
    • innobackupex--apply-log--red O-only Path_to_fullbak;
    • Incremental backup restore
    • Innobackupex--apply-log--redo-only path_to_fgullbak--incremental-dir=path_to_each_increbak;
    • Note that the prepare of the last backup does not require a--redo-only parameter
  • Backup Restore
    • shut down the MySQL service before executing the RESTORE command, and empty the MySQL data_dir
    • innobackupex--copy-back Path_to_fullbak;
    After
    • is complete, first chown-r mysql:mysql/data_dir, and then restart the MySQL service
  The increment for
  • recovery binlog
    • Xtrabackup is performed periodically, so to recover data from either node, you must use Binlog restore
    • in the last incremental folder to view Xtrab Ackup_binlog_info, gets the Binlog file and Stop-pos
    • mysqlbinlog--no-defaults Abs_path_to_binlog at the time of the last incremental backup-- Start-position=pos | MYSQL-UDW Recover all data

Xtrabackup database Hot backup record