[SQL] MySQL full backup and recovery process using mysqldump

[Objective] Use mysqldump for full backup, and use the BINLOG function provided by MySQL to implement Incremental Backup

For ease of operation, we recommend that you enable two terminal commands, one for MySQL and the other for Linux statements.

[Note]

Before performing the following steps, you must configure the environment variables to ensure that MySQL and mysqldump can run directly.

If no environment variable is configured, you must add the MySQL installation path before MySQL and mysqldump commands.

For example:

 /opt/mysql/bin/mysql /opt/mysql/bin/mysqldump

Detailed operation steps:

(1) create a form: Create Table test1 under test. (For convenience, set only the content to one and the column name to ID)

(2) Fill in initial data 1, 2, 3

(3) query form data:

mysql> select * from Test1;+----+| id |+----+|  1 ||  2 ||  3 |+----+3 rows in set (0.01 sec)

(4) back up the database test as testtt. SQL. Data Update-F indicates that a new log file is generated:

mysqldump -hlocalhost -uroot -pjue -l -F test > testtt.sql;

(5) update data after backup:

mysql> insert into Test1 values(4),(5),(6),(7);

Execution result:

Query OK, 4 rows affected (0.04 sec)Records: 4  Duplicates: 0  Warnings: 0

(6) query the updated form data:

mysql> select * from Test1;

Execution result:

+----+| id |+----+|  1 ||  2 ||  3 ||  4 ||  5 ||  6 ||  7 |+----+7 rows in set (0.00 sec)

(8) read backup records.

mysql -hlocalhost -uroot -pjue test < testtt.sql;

(9) query the test1 form after reading the backup

 mysql> select * from Test1;

Execution result:

+------+| id   |+------+|    1 ||    2 ||    3 |+------+

We can see that the four new records we added later disappear. Our current records are the records backed up earlier.

This indicates that our full backup is successful.