MySQL Import and export backup and restore

Rationale: Differential backups are different from incremental backups where the starting point is the point of a full backup, the point at which the most recent backup is the starting point, and a hybrid backup if the traffic is large:

Week 1 0-point full backup once

Week 2 , 3 , 4 0-Point Incremental backup

Week 5 0 points for differential backup

Week 6,7 0-Point Incremental backup

If the week 6 of the 8 Click to delete a table or a record library, etc. can first

Week 1 0-point data into the database

another week . 5 data Import Database for 0-point differential backup

another week . 6 data Import Database for 0-point incremental backup

Then view the current log information to find the point in time of the previous operation at the time of the delete operation

Back up the data for this time period and then import the database to restore

1.mysqldump Common Options

U,-h,-p # Specify the user name password to connect to the database, address

-A,--all-databases # back up all databases

-X,--lock-all-tables # locks All Tables

-L,--lock-tables # Lock the backed up table (this option is recommended only for backup sheets)

-B,--databases db_name1 db_name2 ... # back up the specified database

--single-transaction # start a large single transaction implementation backup (hot standby, only for #InnoDB storage Engine, if there are MyISAM tables in the backed-up tables , it does not make any sense for those tables)

-C,--compress # Compressed transport (consumes more CPU clock cycles on the server side)

-E,--events # backs up the event scheduler for the specified library;

-R,--routines # backup stored procedures and storage functions;

--triggers # backup triggers

-F,--Flush-logs # Executes the flush logs command after locking the table (log scrolling);

--master-data[=num] # is recorded in a backup file of a full backup, at the time of backup (when all tables are locked), the file name of the corresponding binary log file and its location. num=0 means no record,num=1, record changemaster to statement (statement is not commented),num=2, recorded as comment statement;

Full backup

MYSQLDUMP-A-X--master-data=2 >/backup/' Date +%f ' _all.sql

2. Mysqlbinlog:

--start-position=# # Viewing from the specified event location

--stop-position=# # displays only to the specified event location

--start-datetime=yyyy-mm-dd hh:mm:ss # Viewing from a specified time

--stop-datetime=yyyy-mm-dd hh:mm:ss # displays only to the specified time

Incremental backup vs. differential backup based on location (location according to your needs in the binary)

[[Email protected] backup] #mysqlbinlog--start-position=120--stop-position=1169/data/mysql/mysql-bin.000004 >/ backup/' Date +%f ' _inc.sql

Incremental and differential backups based on date (time based on the time you backed up)

/usr/local/mysql/bin/mysqlbinlog--start-date= "2015-09-16 00:00:00"--stop-date= "2015-09-17 00:00:00"/data/mysql/ mysql-bin.000004>/backup/' Date +%f ' _inc.log

if the - Number of Ten : + left and right errors, we can first find the point to restore (such as the time before the deletion of an action point)

( 1 ) pilot out part of the Data View Operation

Mysqlbinlog--start-date= "2015-09-16 10:35:00"--stop-date= "2015-09-1610:45:00"/data/mysql/mysql-bin.000004 >/ Backup/error_time.sql

( 2 ) Find the point of the Operation

Vi/backup/error_time.sql

/*!*/;

# at 1138

#150916 10:40:51 server ID end_log_pos 1169 CRC32 0x49372e7e Xid = 2175

commit/*!*/;

# at 1169

#150916 10:43:14 Server ID end_log_pos 1282 CRC32 0xe413656e Query thread_id=4 exec_time=0 error_code

=0

SET timestamp=1442371394/*!*/;

drop table ' CJ '/* generated by server * /////////////////////////

(3) use Backup to delete before :00:00 Data

Mysqlbinlog--start-date= "2015-09-1600:00:00"--stop-date= "2015-09-16 10:40:52"/data/mysql/mysql-bin.000004 >/ Backup/right.sql/// plus one second restores the action before the delete

(4) import to Database

MySQL first stops the binary log scrolling

Mysql>set sql_log_bin=0;

Queryok, 0 rows Affected (0.00 sec)

Client Operation:

MySQL </backup/2015-09-16_all.sql/// Restore the full backup I made today

MySQL </backup/right.sql/// Restore the backup before error

This article is from the "innovation sharing gallop inside and out" blog, please be sure to keep this source http://10554846.blog.51cto.com/10544846/1695376

MySQL Import and export backup and restore