MySQL Backup and Restore

Lenka Kasparova-Oracle

How do I backup my database?

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 [http://dev.mysql.com/doc/en/mysqldump.html mysqldump - A Database Backup Program] from the MySQL Manual.

To backup all databases:

mysqldump -uadmin -p --all-databases > databases.sql
Enter your password, and all the databases will be dumped with the --all-databases option.

Adding --add-drop-table to mysqldump

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.

Using mylvmbackup

From the project page: (blog moved to new location in 2014)
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.

Using MySQL Enterprise Backup

Enterprise Subscriptions of MySQL include the MySQL Enterprise Backup tool. This tool is much faster than mysqldump.

How do I backup a table in my database?

This can be done via mysqldump with:
mysqldump -uadmin -p myDatabase table > table.sql
Enter your password, and it will create the table.sql file for you.

MySQL 5.6 and above also support transportable tablespaces for InnoDB.

How do I restore my database?

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.

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 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 http://amanda.zmanda.com/.