MySQL full backup + incremental backup

MySQL full backup + incremental backup

• Profile:
  • Manage MySQL configuration file, open Binlog log
    • for MySQL database:
      • Log_bi n =/var/log/mysql/mysql_bin.log
      • server-id = 1 (must have, otherwise restart fail)
    li> for MARIADB database:
    • log_bin =/var/log/mysql/mysql-bin.log
  • the MySQL service needs to be restarted after the configuration file is modified:
    • service mysql restart |/etc/init.d/mysql restart
    li>

  • To log in to the database to see if Binlog is enabled:

    • show variables like "log_%";

  • To view the Binlog log currently being written:

    • show master status;
    • you may need to grant REPLICATION client permissions to the current user here: Grant REPLICATION Client on *. * to user;
• full backup: using mysqldump
  • mysqldump -F -uxxx -p --databases anheisg > /home/dw/backup/anheisg.sql
  • -F means refreshing the Binlog log for incremental backups
• Incremental backup:
  • Incremental backup data is obtained from the Binlog log
• Data recovery:
  • show master status; View Binlog logs currently being written, such as mysql-bin.000005
  • full backup restore
    • mysql-udw-pxxx ANHEISG
  • flush logs; Writes the modifications of the database to the new Binlog log so that it is easy to view and process the mysql-bin.000005
  • Incremental Backup Restore
    • method One:
      • Show Binlog Events in ' mysql-bin.000005 '; View POS and End_pos for error actions
      • mysqlbinlog--no-defaults/var/log/mysql/mysql_bin.000005--stop-position=878 | my Sql-uroot-p anheisg
      • Restore an incremental backup using the Mysqlbinlog command,--start-position=xxx and--stop-position=xxx can specify the range of recovery
    • Method Two:
      • mysqlbinlog--no-defaults/var/log/mysql/mysql_bin.000005-d ANHEISG > Anheihei.sql
      • Delete the misoperation command in the exported SQL file and restore it
      • mysql-udw-pxxx ANHEISG < Anheih Ei.sql

MySQL full backup + incremental backup