- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 6 Backup and Recovery
- 6.1 Database Backups
- 6.2 Example Backup and Recovery Strategy
- 6.3 Using BACKUP DATABASE and RESTORE
- 6.4 Point-in-Time Recovery
- 6.5 Table Maintenance and Crash Recovery
[+/-]
The BACKUP DATABASE and
RESTORE statements are available as of MySQL
6.0.5. They provide a way to make a copy of a database or set of
databases at a given point in time, and a way to restore each
database to its state as of that time. Restoring a backup can be
combined with use of the binary log to accomplish point-in-time
recovery: If the restore operation is done because data loss has
occurred after the backup was made, restored databases can be
brought up to the time of data loss by executing the data changes
in the binary log between the times when the backup was made and
when the data loss occurred.
A goal of the BACKUP DATABASE and
RESTORE statements is to enable other database
operations to proceed concurrently, to make it unnecessary to take
databases offline or prevent clients from accessing them.
BACKUP DATABASE must block some operations from
occurring (such as dropping tables from a database while it is
being backed up), but the attempt is made to keep blocking to a
minimum. Generally, blocked operations are those involving Data
Definition Language (DDL) statements. RESTORE
must do more blocking because it writes database contents rather
than just reading them.
The following discussion covers these aspects of BACKUP
DATABASE and RESTORE:
Quick guide to making backups and restoring them
How
BACKUP DATABASEandRESTOREworkStatus reporting and monitoring for backup and restore operations
For additional information about the BACKUP
DATABASE and RESTORE statements, see
these sections of the manual:
Section 12.5.3.1, “
BACKUP DATABASESyntax”, and Section 12.5.3.2, “RESTORESyntax”, describes the syntax for these statements.Limitations on the use of these statements are discussed in Section D.7, “Restrictions on
BACKUP DATABASEandRESTORE”.
User Comments
If you are using mysql version 5 and above then this backup stretagy may
help you. This is useful to backup all the databases of mysql with 2 or
more commands. Steps are as follow.
1) connect to your local mysql with any utility.
2) select database "information_schema"
3) Issue the following query :
select distinct concat("md c:\\backup\\",table_schema,char(13)) into outfile "c:\\test.bat" from tables
-- This will create a file test.bat on c:\
4) Goto command prompt and issue command a) md "c:\backup" (b) "C:\test.bat"
5) Now come back to mysql and issue another query.
select concat("backup table ",table_schema,".",table_name," to
'c:\\backup\\",table_schema,"';") from tables where table_schema not in
('information_schema','mysql')
-- This will generate backup command for all tables for respective
directories.
6) Copy the result of above query result and paste into the query window.
Execute all the queries by selecting it.
That's done. Bakcup will be availbale on the respective directories in .
This backup may or may not work depending on the type of table.
Add your own comment.