MySQL full, incremental backup and recovery

The importance of database backup

1. Improve the system's high availability and disaster recoverability, when the database system crashes, no data backup can not find data.
2. Restoring a database using a database backup is the best solution for minimizing the cost of data recovery when a database crashes.
3. Without a database there is nothing, database backup is a powerful means of disaster prevention.

Classification of database backups

1. Physical backup: Refers to a backup of physical files (such as data files, log files, and so on) of the database operating system. Physical backup visitors are divided into offline backup (cold backup) and online backup (hot backup).
   Cold backup: When the database is closed, the backup operation can ensure the integrity of the database well.
   Hot backup: Operates in the database running state, which relies on the log files of the database.
2. Logical backup: Refers to a backup of a database logical component, such as a database object, such as a table. From a database backup policy perspective, backups can be divided into full, differential, and incremental backups.
   Full backup: Every time a full backup is made to the database. You can back up the entire database, including all database objects, such as user tables, system tables, indexes, views, and stored procedures.
   Differential backup: Backs up files that have been modified since the last full backup, backing up only part of the database.
   Incremental backup: Only those files that were modified after the last full or incremental backup are backed up.

Full Backup (backup operation in Linux mode) full physical backup using Tar Packaged folder backup

• When the database is compressed, it is compressed with a large compression ratio of XZ format.

  rpm -q xz    //检查xz软件包是否安装yum -y install xz   //安装xz软件包

• Package operations on the database folder (/usr/local/mysql/data/)

  tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/     //备份数据库文件夹，并以年月日的形式命名ls /opt/mysql-2018-07-02.tar.xz

• To restore a database file
  Simply unzip the database files and retrieve them even if the entire/data/directory is lost

  mkdir /abctar Jxf mysql-2018-07-03.tar.xz -C /abc/

  Full logical backup with mysqldump tool backup

• Backing up a database

  mysqldump -u root -p stady > /opt/stady.sql//备份stady库到/opt目录下，-p之后可跟密码，也可不写。不写就在回车之后输入密码验证。 **备份文件后缀都为.sql,前面名称见名知意即可**

• View the contents of a backup file
  
  1. back up multiple databases

       mysqldump-u root-p--databases mysql stady >/opt/mysql-stady.sql 

  2. back up all databases

       mysqldump-u root-p--opt--all-databases >/opt//all.sql  

  3. back up tables in database

       mysqldump-u root-p stady Info >/opt/stady-info.sql//Nearly library name, followed by table name  

  4. table structure for backup database

       mysqldump-u root-p-D stady info >/opt/decribe-info.sql//-D Library name + table name  

     data recovery login MySQL , use the source command to restore the
• Restore the Info table in the "Backing up tables in a database" step

  [[email protected] /]# mysql -u root -p Enter password: ···   //省略部分内容mysql> use stady;    //切换到库Database changedmysql> drop table info;   //删除info表Query OK, 0 rows affected (0.02 sec)mysql> show tables;       //查看库中表Empty set (0.00 sec)       //库中为空mysql> source /opt/stady-info.sql   //恢复info表，格式是source+备份文件路径和文件名称Query OK, 0 rows affected (0.02 sec)···   //省略部分内容mysql> show tables;    //查看库中表+-----------------+| Tables_in_stady |+-----------------+| info            |   //info表已经成功恢复+-----------------+1 row in set (0.00 sec)

• Using the MySQL command to restore a table

  [[email protected] /]# mysql -u root -p Enter password: ···   //省略部分内容mysql> use stady;    //切换到库Database changedmysql> drop table info;   //删除info表Query OK, 0 rows affected (0.02 sec)mysql> show tables;       //查看库中表Empty set (0.00 sec)       //库中为空mysql> quit               //退出数据库，切换到linuxBye[[email protected] /]# mysql -u root -p stady < /opt/stady-info.sql   //在linux环境中恢复Enter password: [[email protected] /]# mysql -u root -p   //进入数据库查看Enter password: ···   //省略部分内容mysql> use stady;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-----------------+| Tables_in_stady |+-----------------+| info            |   //info表已恢复+-----------------+1 row in set (0.00 sec)

  Restore the library and restore the table in the same way, but when you restore the data table, you should be aware that if the backup file only back up the table files, when there is no library in MySQL, if the direct recovery will fail, you must first create the library, and then restore.

  Incremental backup

  Create a Stady library in the MySQL database, create an info table, and insert data

  mysql> use stady;mysql> select * from info;+----------+-------+| name     | score |+----------+-------+| zhangsan | 80.00 || lisi     | 90.00 |+----------+-------+2 rows in set (0.00 sec)

• Open Binary Log

  vim /etc/my.cnflog-bin=mysql-bin   //在[mysqld]段插入该语句systemctl restart mysqld.service   //重启mysql服务,会在/data/目录下生成新的日志文件（空文件）

  
  

• Backing up Stady libraries with mysqldump

  mkdir /backupmysqldump -u root -p stady > /backup/stady.sqlmysqladmin -u root -p flush-logs  //刷新生成新的日志文件，存在01中，新存在的02为空文件

• Insert new data, generate a new incremental log file

    mysql> insert INTO info (name,score) VALUES (' Chen ', 90);//Enter the database, insert data into the table Mysqladmin- U root-p flush-logs//Refresh generate new delta file, incremental backup saved in 02 mysql> insert INTO info (name,score) VALUES (' Chen01 ', 96);//Enter database again, insert into table Data mysql> SELECT * from info;+----------+-------+| name | Score |+----------+-------+| Zhangsan | 80.00 | | Lisi | 90.00 | | Chen | 90.00 | | Chen01 | 96.00 |+----------+-------+4 rows in Set (0.00 sec) Mysqladmin-u root-p flush-logs//Refresh again, incremental backup saved in 03  

  Incremental recovery
• Analog data loss, deletion of newly added two records

  [[email protected] data]# mysql -u root -p Enter password: mysql> use stady;Database changedmysql> delete from info where name=‘chen‘;Query OK, 1 row affected (0.00 sec)mysql> delete from info where name=‘chen01‘;Query OK, 1 row affected (0.00 sec)mysql> select * from info;+----------+-------+| name     | score |+----------+-------+| zhangsan | 80.00 || lisi     | 90.00 |+----------+-------+2 rows in set (0.00 sec)

• Recover data using the Mysqlbinlog command

  [[email protected] data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -pEnter password: //输入密码确认恢复数据[[email protected] data]# mysql -u root -p Enter password:   //输入密码登陆系统mysql> use stady;Database changedmysql> select * from info;+----------+-------+| name     | score |+----------+-------+| zhangsan | 80.00 || lisi     | 90.00 || chen     | 90.00 |//增量备份的02数据成功恢复+----------+-------+3 rows in set (0.00 sec)

  Use Mysqlbinlog to view the contents of an incremental backup file

  mysqlbinlog --no-defaults --base64-output-decode-rows -v /usr/local/mysql/data/mysql-bin.000002//--base64-output=decode-rows使用64位编码机制解码，按行读取 -v 显示出来/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;···    //省略部分内容# at 345#180703 21:12:11 server id 1  end_log_pos 389 CRC32 0xb0bc2cb1  Write_rows: table id 118 flags: STMT_END_F### INSERT INTO `stady`.`info`### SET###   @1=‘chen‘###   @2=90.00···//省略部分内容

  After the binary log is turned on, an empty mysql-bin.000001 file is generated in the/data/directory, mysqladmin-u root-p flush-logs using the command The incremental backup is then written to mysql-bin.000001, and a new mysql-bin.000002 empty file is generated, waiting for the next flush to be written, and then creating the mysql-bin.000003 ...

  Incremental Breakpoint Recovery Simulation environment

  The simulated insertion data was mistakenly manipulated, and a correct data was deleted while inserting two data.

  [[email protected] data]# mysql -u root -p Enter password: mysql> use stady;Database changedmysql> insert into info (name,score) values (‘test01‘,88);Query OK, 1 row affected (0.00 sec)mysql> delete from info where name=‘chen‘;Query OK, 1 row affected (0.00 sec)mysql> insert into info (name,score) values (‘test02‘,88);Query OK, 1 row affected (0.01 sec)mysql> select * from info;+----------+-------+| name     | score |+----------+-------+| zhangsan | 80.00 || lisi     | 90.00 || test01   | 88.00 || test02   | 88.00 |+----------+-------+4 rows in set (0.00 sec)    //插入了两条test01和test02，但是chen被操作删除mysql> quit   //退出数据库Bye[[email protected] data]# mysqladmin -u root -p flush-logs  

  Incremental Time Node Recovery
• Decode Export Incremental backup file for easy viewing and recovery

  mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 > /backup/info.txtcd /backup

  Logs the start time of the error action, and the start time of the correct operation, skipping the operation of that period when resuming.
  

• Recovering data that was mistakenly manipulated based on point in time

  mysql> drop table info;   //删除被误操作的表mysql -u root -p stady < /backup/stady.sql  //恢复完全备份的stady库mysqlbinlog --no-defaults --stop-datetime=‘2018-07-03 21:57:08‘ /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p  //恢复误操作前的内容mysqlbinlog --no-defaults --start-datetime=‘2018-07-03 21:57:14‘ /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢复误操作之后的内容

• Recovering data based on location tag numbers
  

  mysql> drop table info;   //删除被误操作的表mysql -u root -p stady < /backup/stady.sql  //恢复完全备份的stady库mysqlbinlog --no-defaults --stop-position=‘1408‘ /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -pmysqlbinlog --no-defaults --start-position=‘1674‘ /usr/local/mysql/data/mysql-bin.000005 | mysql -u root -p

  Data Completion Recovery

MySQL full, incremental backup and recovery