BackupFAQ
This is the MySQL Backup and Restore FAQ.
Contents |
[edit] How do I backup my database?
[edit] Using mysqldump
The output of the mysqldump tool will include all the SQL syntax and the data from your database and place it into a text file. It can simple be run as:
mysqldump -uadmin -p myDatabase > myDatabase.sql
Once you enter your password, the database will get dumped to the myDatabase.sql file. Keep this file safely, as it can be used to restore your database. For more information, please read mysqldump - A Database Backup Program from the MySQL Manual.
[edit] Using --add-drop-table
When it comes to restoring later, you might find that it made sense to drop the existing tables, so you get everything fresh from the backup. Keep in mind that adding this option, will add syntax to overwrite the tables upon restoring, and this could include data too!
mysqldump -uadmin -p --add-drop-table myDatabase > myDatabase.sql
Using this option however, will make your restores a lot more convenient.
[edit] Using mysqlhotcopy
From the MySQL reference manual:
mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.
[edit] Using mylvmbackup
From the project page:
mylvmbackup is a tool for quickly creating backups of MySQL server's data files. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, makes an LVM snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.
[edit] How do I backup a table in my database?
This can be done via running:
mysqldump -uadmin -p myDatabase table > table.sql
Enter your password, and it will create the table.sql file for you.
[edit] I run a web-hosting company and I would like to backup all my clients databases. Can MySQL do this?
Yes! Since you're the database administrator, run:
mysqldump -uadmin -p --all-databases > databases.sql
Enter your password, and all the databases will be dumped with the --all-databases option.
[edit] How do I restore my database?
[edit] If mysqldump was used to make the backup
If you used mysqldump to make the backup, you will have a text file containing all your data and SQL statements. To do the restore, perform:
mysql -uadmin -p myDatabase < myDatabaseDump.sql
Enter your password, and your database will be populated.
[edit] How do I backup MySQL database and Application files?
You will have to use network backup and recovery program, Amanda to backup multiple databases and application configuration, data files. For MySQL database full backups, use mysqldump or mysqlhotcopy commands and incremental backups, use MySQL binary logging. MySQL database backup commands can be integrated with the Amanda backup programs. For more information on Amanda, see ProjectPage Amanda.