First, backup common Operation Basic command
1. Backup Command mysqldump format
Format: MYSQLDUMP-H host name-P Port-u user name-p password –database database name > file name. sql
2. Backup MySQL database as a format with delete table
Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.
Mysqldump--add-drop-table-uusername-ppassword-database databasename > Backupfile.sql
3, the MySQL database directly compressed backup
Mysqldump-hhostname-uusername-ppassword-database DatabaseName | gzip > backupfile.sql.gz
4. Back up some (some) tables of MySQL Database
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql
5. Back up multiple MySQL databases at the same time
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql only 6, backup copy database structure only
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
7. Back up all databases on the server
Mysqldump–all-databases > Allbackupfile.sql
8. command to restore MySQL database
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql
9. Restore the compressed MySQL database
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName
10. Transfer the database to the new server
Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName
One by one 、--Master-data and--single-transaction
Using--master-data=2 in mysqldump, binlog files and position information are recorded. --single-transaction will set the isolation level to repeatable-commited
12. Import the Database
Common source command, with use to enter into a database, Mysql>source D:\test.sql, the following parameters are the script file.
13. View Binlog Log
View Binlog logs available with command mysqlbinlog binlog log name |more
14, General_log
General_log Record any operation of the database, view general_log status and location can be command show variables like "general_log%", open General_log can use command set global General_log=on
Two, incremental backup
A small number of databases can be fully backed up every day, as this will not take much time, but when the database is large, it is unlikely that a full backup will take place every day, and an incremental backup can be used. The principle of incremental backup is to use the Binlog log of MySQL.
1, first do a full backup:
mysqldump-h10.6.208.183-utest2-p123-p3310--single-transaction--master-data=2 test> Test.sql will get a fully-prepared file by this time Test.sql
In the SQL file we will see:
--Change MASTER to master_log_file= ' bin-log.000002 ', master_log_pos=107; means that all changes are saved to the bin-log.000002 binaries after the backup is made.
2. Add two records to the T_student table in the test library, and then execute the flush logs command. This will result in a new binary log file, bin-log.000003,bin-log.000002, which saves all the changes that were made after the full backup, as well as the addition of the recorded operations to the bin-log.00002.
3. Add two records to the a table in test library and delete the T_student table and a table by mistake. The operations for adding records in a and deleting tables A and t_student are recorded in bin-log.000003.
Third, recovery
1, first import full data
mysql-h10.6.208.183-utest2-p123-p3310 < Test.sql can also be imported directly under the MySQL command line with source
2, Restore bin-log.000002
Mysqlbinlog bin-log.000002 |mysql-h10.6.208.183-utest2-p123-p3310
3, Recovery part bin-log.000003
In the General_log, find the time point of the accidental deletion, and then the corresponding point in time to bin-log.000003 to find the corresponding position point, you need to revert to the first position point of the accidental deletion.
You can use the following parameters to control the interval of the Binlog
--start-position start point--stop-position end point
--start-date start time--stop-date end time
After you find the recovery point, you can start the recovery.
Mysqlbinlog mysql-bin.000003--stop-position=208 |mysql-h10.6.208.183-utest2-p123-p3310
MySQL Backup and restore