Skip to Main Content

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MySQL Backup and Restore

Lenka Kasparova-OracleMay 19 2015 — edited May 26 2015

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/.

Comments

InoL

That is not how APEX works. When you upload a csv file, you can either use the Data Load Wizard or your own process to parse and load the data in the file using APEX_DATA_PARSER.
Oracle Application Express 18: Creating a Data Load Wizard for Your Application (0 Bytes)
APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns. (0 Bytes)
SQLLoader would typically run on the database server to load a file on the database server. I don't think that is what you are looking for.

User_C0JD6

SQLLoader would typically run on the database server to load a file on the database server. I don't think that is what you are looking for.

this is what i was after tbh. Apex accepting the CSV file as an upload to a director and sql loader getting kicked off. But I’m guessing it will have to be a combination of files uploaded to a directory and a continuous daemon checking for new files and the daemon kicking off the sql loader

AndyH

If you don't want to process the file (using APEX_DATA_PARSER, your own CSV processing, etc.) you could write the uploaded blob to the database file system and then access it as an external table.

User_C0JD6

Thank you Andy and Inoll

InoL

Apex accepting the CSV file as an upload to a director and sql loader getting kicked off.
That seems a bit strange: upload the file to the database (that is what APEX does), write the file to a database directory and then load the file back into the database. I think it is so much easier to skip the part of writing and reading the file from a database directory.
If you really want to do that, I agree with AndyH: access it as an external table, don't use SQLLoader.

1 - 5

Post Details

Added on May 19 2015
0 comments
1,528 views