MySQL---Overall backup and incremental backup

Overall Backup:

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

Incremental backup:

Back up the data in a range.

1, the overall backup:

To back up a table:

For tables with storage engine MyISAM, you can copy the three files of frm, myd, myi directly to the backup effect. When you need to restore, and then copy back to achieve the restore effect.

If the storage engine is a InnoDB table, it is not as simple as the data and indexes of all tables exist together (tablespace). Once the table space is replicated, all table data and indexes are copied along with it.

How do I make a backup? You can use the Mysqldump tool

First, create a table and insert some data

Need to exit MySQL before backup, take advantage of mysqldump-u user-P Library Name table name > Output backup path

Export the backup file after entering the password

The Table1 file appears in the backup directory of the E-drive

Open the Table1 file, and you can see that the file actually stores the SQL statement that created the table statement and inserted the data.

Now we delete the Tab_one table and use Table1 to restore the Tab_one

Check the Tab_one table again, the table has been restored back

How do I back up multiple tables?

Answer: Mysqldump-u user-P database table 1 Table 2 ... Table n > backup file path

How do I back up 1 databases?

Answer: Mysqldump-u user-P- B database > backup file path

How do I back up multiple databases?

Answer: Mysqldump-u user-P- B library 1 Library 2 ... Library n > backup file path

How do I back up all the data?

Answer: Mysqldump-u user -P-A > backup file path

2. Incremental backup

Start the binary logging function first by setting My.ini or my.conf

Add a binary backup path below mysqld (Note that the path is left slash '/' instead of ' \ ', unlike Windows)

Restart MySQL Service

You'll see 2 more files in the Beifen directory on the e-drive.

Open the index file, and the content is the path to the log file. The log file can have more than one, and the naming convention is Log-bin set in the Testbei followed by a 6-digit number (straight increment from 11)

Log files cannot be opened directly and can be opened using the Mysqlbinlog tool

Exit the MySQL client in the Windows console (make sure the environment variable is set, or go to the MySQL bin directory) to enter

Mysqlbinlog log file path

The binaries record most operations except the select operation (I am not sure, the basic additions and deletions are sure to be recorded)

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

Restore by Time:

--start-datetime= "Starting Time to restore data"

--stop-datetime= "End time to restore data"

Mysqlbinlog--start-datetime= "Time" Log file path | Mysql-u User-P restore from the specified start time to the present

Mysqlbinlog--stop-datetime= "Time" Log file path | Mysql-u User-P restore from the beginning to the specified end time

Mysqlbinlog--start-datetime= "Time" --stop-datetime= "time " log file path | Mysql-u User-P restore from the specified start time to the specified end time

Restore by Location:

--start-position= "Starting location for restoring data"

--stop-position= "Ending location of restore data"

Mysqlbinlog--start-position= "Location" Log file path | Mysql-u User-P restore from the specified starting position to the present

Mysqlbinlog--stop-position= "Location" log file path | Mysql-u User-P restore from the beginning to the specified end position

Mysqlbinlog--start-position= "Location "--stop-position= "Location" log file path | Mysql-u User-P restore from the specified starting position to the specified end position

Now delete the Tab_two table

View logs to discover SQL that deletes tab_two

Now that you want to restore Tab_two, you should set the time range (time to build-time to delete).

Look at the results again.