Mysql-overall backup and Incremental Backup

Mysql-overall backup and Incremental Backup

Overall backup:

Back up the entire table, entire database, or even all databases.

Incremental Backup:

Back up data within a certain range.

1. Overall backup:

Back up a table:

For tables whose storage engine is myisam, you can directly copy the three files frm, myd, and myi for backup. When you need to restore the image, copy it back to achieve the restoration effect.

If the storage engine is an innodb table, this is not easy because the data and indexes of all tables exist together (tablespace ). Once the tablespace is copied, the data and indexes of all tables are copied together.

How to back up data? You can use the mysqldump Tool

Create a table and insert some data.

You need to exit mysql before backup, and use mysqldump-u user-p database name Table Name> output backup path

Enter the password and export the backup file.

The table 1 file appears in the Backup Directory of the E disk.

Open the table1 file. You can see that the file actually stores the table creation statements and the SQL statements for inserting data.

Now we delete the tab_one table and use table1 to restore the tab_one table.

Check the tab_one table. The table has been restored.

How do I back up multiple tables?

A: mysqldump-u user-pDatabase Table 1 Table 2 ...... Table N> Backup File Path

How do I back up one database?

A: mysqldump-u user-p-Database B> Backup File Path

How do I back up multiple databases?

A: mysqldump-u user-p-Database B, database 1, database 2 ...... Database N> Backup File Path

How to back up all data?

A: mysqldump-u user-p-> Backup File Path

2. Incremental Backup

First, start the binary log function by setting my. ini or my. conf

Add a binary backup path under mysqld(Note that the path is a left slash '/' instead of '\', which is different from that in windows)

Restart mysql Service

Two files are added to the beifen directory of the elastic drive.

Open the index file. The content is the path of the log file. There can be multiple log files. The naming rule is to add six digits after testBei set in log-bin (increasing from 000001)

The log file cannot be opened directly. You can use mysqlbinlog to open it.

Log out of the mysql client on the windows Console (Make Sure You Have set environment variables, otherwise you need to enter the mysql bin directory) and enter

Mysqlbinlog Log File Path

The binary file records the vast majority of operations except the select Operation (I am not quite sure about it, but the basic addition, deletion, modification, and query operations must be recorded)

Because the time and location of each operation are recorded. Therefore, there are two ways to restore data through "time" or "location ".

Restore by Time:

-- Start-datetime = "start Time of data restoration"

-- Stop-datetime = "End Time of data restoration"

Mysqlbinlog -- start-datetime = "time" log file path | mysql-u user-p restores from the specified start time to the present

Mysqlbinlog -- stop-datetime = "time" log file path | mysql-u user-p restores from the beginning to the specified end time

Mysqlbinlog -- start-datetime = "time" -- stop-datetime = "time" log file path | mysql-u user-p restores from the specified start time to the specified end time

Restore by location:

-- Start-position = "start position of restoring data"

-- Stop-position = "end position of restored data"

Mysqlbinlog -- start-position = "location" log file path | mysql-u user-p restores from the specified start position to the present

Mysqlbinlog -- stop-Position = "position"Log File Path | mysql-u user-p is restored from the beginning to the specified end position

Mysqlbinlog -- start-Position = "position"-- Stop-Position = "position"Log File Path | mysql-u user-p restores from the specified starting position to the specified ending position

Delete the tab_two table now

You can view the log and find the SQL statement used to delete tab_two.

To restore tab_two, set the time range (Table Creation Time-delete time ).

Let's look at the results.