How to implement full backup and incremental backup of MySQL database

Definition

Full backup is to back up all the data and all objects in the database.

Since the data files in the MySQL server are disk-based text files, a full backup is to copy the database file, which is the simplest and fastest way.

However, the data files of the MySQL server are always open during the running of the server. In order to achieve a true full backup, the MySQL database server needs to be stopped first.

In order to ensure the integrity of the data, before stopping the MySQL server, you need to execute the flush tables statement to write all data to the data file. Students only need to know that this method is not feasible because it is not advisable to stop the database for backup in a production environment.

Use the mysqldump command to back up tables, databases, and database systems:

mysqldump [-h主机名] –u用户名 –p密码 --lock-all-tables --database [tables] > 文件名

-h host name, which can be omitted, indicates the local server, --lock-all-tables for the files to be backed up Read locks are applied to all tables in the database (during this process, the database is strictly in the read only state). You can add the table that needs to be backed up after --database. If the table name is not specified, it means backing up the entire database.

Full backup and recovery demonstration

Prepare a student table and build the table in the world database.

Create table:

CREATE DATABASE world;
USE world;
CREATE TABLE student(
    stuId INT(10) NOT NULL,
    stuName VARCHAR(10) NOT NULL,
    stuAge INT(10) NOT NULL,
    PRIMARY KEY(stuId)
    );

Insert data:

INSERT INTO student(stuId, stuName, stuAge) VALUES(1, 'zhangsan', 18), (2, 'lisi', 19),(3, 'wangwu', 18);

Use flush tables; statement writes all data to the data file:

FLUSH TABLES;

Exit the mysql environment and use the mysqldump command to fully back up the database world:

mysqldump -u root -p --lock-all-tables --databases world > /tmp/world.sql

Enter /tmp directory, view the backup file:

cd /tmp
ls

Now, we have fully backed up the world library and are not afraid of data loss.

Simulate the loss of the student table in the world database:

DROP TABLE student;

Confirm that the table is deleted

SHOW TABLES;

Use the mysql command to restore the database:

mysql -uroot -p < /tmp/world.sql

Enter the mysql environment and view the recovery results:

SHOW TABLES;

Output results:

Verify the data in the table:

SELECT * FROM student;

The incremental backup is the change since the last full backup or incremental backup Data backup relies on binary log files, and the binlog log of the database needs to be turned on. First perform a full backup of the database, and refresh the binlog log at the same time. All operations after this backup will be recorded in the new binlog log. We only need to back up the added binlog to realize the continuous increase of content. A perfect backup of the database. When an abnormality occurs in the database, we can first restore the most recent full backup, and then restore the incremental backup files one by one in sequence to achieve database recovery.

The above is the detailed content of How to implement full backup and incremental backup of MySQL database. For more information, please follow other related articles on the PHP Chinese website!