Recommended Servers for MySQLThe world's most popular open source database
- 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
[+/-]
MySQL Backup is available as of MySQL 6.0.5. This feature
comprises the BACKUP DATABASE and
RESTORE statements. 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.
A backup operation can include tables for different storage
engines and the backup image will still be consistent. That is,
you need not care which storage engines you're using.
BACKUP DATABASE saves the data in a
consistent backup image with respect to its “validity
point.”
The validity point ties the backup to the binary log. 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 (that is, after the validity point), 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.3, “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.