- 382.3K All Categories
- 2.1K Data
- 209 Big Data Appliance
- 1.9K Data Science
- 447.9K Databases
- 220.9K General Database Discussions
- 25 Multilingual Engine
- 520 MySQL Community Space
- 465 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 492 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.2K SQL & PL/SQL
- 21K SQL Developer
- 293.3K Development
- 7 Developer Projects
- 128 Programming Languages
- 290K Development Tools
- 95 DevOps
- 3K QA/Testing
- 645.5K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 149 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 144 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 198 Java User Groups
- 265 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 229 Portuguese
MySQL Backup and Restore
How do I backup my database?
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.
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.
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.
Enterprise Subscriptions of MySQL include the MySQL Enterprise Backup tool. This tool is much faster than mysqldump.
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.
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/.