MySQL Data backup and recovery

* * Verify users and permissions regardless of backup or restore * *

First, data backup

1, backup method of data backup

Physical backup : CP, TAR, tar+gzip-> compatibility, high destination address space, time consuming

System comes with physical backup tool mysqlhotcopy -Lock table when backing up

logical Backup: backup of SQL statements that produce data good compatibility, small footprint

system comes with full backup tool Mysqldump(mysql-client package generation) - lock table at backup

2 , backup strategy for data backup

Full backup: full backup, backing up all tables on the database server for all libraries

Differential backup: after the ' full backup ', the overlay backs up all the new data that is generated.

Pros and Cons: backup data is redundant, takes up space, and is easy to recover

For example: The 1th day to do a full backup, the 2nd day to produce 3 records, the differential backup needs to back up 3 records, 3rd day to generate 2 records, differential backup should be backed up 5 records

Full backup 1 records 1day backup 1 records

Differential backup 3 records 2day backup 3 records

2 Records 3day backup 5 records

......... ...............

Incremental Backup:' Since last backup ', the backup produces new data.

Pros and Cons: backup saves space and restores the number of files that are called

Full Backup 1 records 1day 1

Incremental Backup 3 2day 3

2 3day 2

......... ............

3, the work of the usual backup policy usage:

full backup + differential backup

Full backup + Incremental backup

4 , Backup Tools

1) full backup tool mysqldump

* * Provided by MySQL package, the amount of data is not too large to use,

* * Lock table when backing up, no one else can manipulate backup data during backup

Format:mysqldump [–h database ip/host name] - u Login user name-p password database name > Path/backup file name. SQL /c20>

Default backup to the directory where the current command is located when the path is not written

If you write a path, you need to create it beforehand.

If the database administrator is logged on from the computer and does not have a password: mysqldump database name > Path/xxx.sql

The user to connect to the database has permission to the appropriate database

The representation format of the database name:

All Libraries --all-databases

Specify all table database names under a single library

A table database name table name under a library

back up a few libraries - b Database name 1 database name 2 database name N

Example:mysqldump-uroot-p --all-databases >/mysqlbak/alldb.sql//Backing up all libraries on the database server

WORKAROUND: After the backup is written to the scheduled task, the backup name will cover the day before, and you can use the "time" make a backup file name.

Get time command: #date +%f→2014-07-21

#date +%t →17:30:17

Mysqldump webdb > WebDB-' date +%f '. sql

Mysqldump-h192.168.10.20-uroot-ptarena webdb >/mysqllog/webdb-' date+%f '. SQL//Remote backup

Second, data recovery

format: Mysql–h Server ip/hostname-U login user name –p password database name < backup file name  

MySQL Database name < backup file name //If the login database does not require a user name password

Note: when recovering data "<" must be written to be careful not to write counter, if the recovery is written ">" will automatically clear the contents of the backup file

when backing up the library, the front plus - b back-up files with "Create library" SQL statement, recovery time without in the database in the creation of the library

The database name is optional and you must look at the SQL statements in the backup file when you restore the data, and use the Backup Files in a build a database SQL statement, Database name can be omitted, Conversely, there are no SQL statements built into the database must be "into the database manually built a library (library name can be written casually)" And then in the restoration of the time to write a new Database name .

Cases:

MySQL WebDB < Webdb-2014-01-06.sql

Mysql-h192.168.10.20-uroot-ptarena WebDB < /mysqllog/webdb-2014-01-06.sql

This article is from the "Dave-Technology blog" blog, please be sure to keep this source http://davewang.blog.51cto.com/6974997/1858713

MySQL Data backup and recovery