[SQL] MySQL uses mysqldump for full backup and recovery [Objective] Use mysqldump for full backup. It is recommended to enable two Terminal functions in combination with the binlog function provided by mysql for Incremental backup, one Mysql-related operation and one linux statement operation. [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:
[sql] /opt/mysql/bin/mysql /opt/mysql/bin/mysqldump
Detailed operation steps: (1) create a form: Create Table Test1 under test. (For the sake of convenience, only set the content to one and the column name to id) (2) Fill in initial data 1, 2, 3 (3) query form data:
[sql] 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:
[sql] mysqldump -hlocalhost -uroot -pjue -l -F test > testtt.sql;
(5) update data after backup:
[sql] mysql> insert into Test1 values(4),(5),(6),(7);
Execution result:
[sql] Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0
(6) query the updated form data:
[sql] mysql> select * from Test1;
Execution result:
[sql] +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | +----+ 7 rows in set (0.00 sec)
(8) read backup records.
[sql] mysql -hlocalhost -uroot -pjue test < testtt.sql;
(9) query the Test1 form after reading the backup
[sql] mysql> select * from Test1;
Execution result:
[sql] +------+ | 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.