Back up a MySQL database in the RDC at IU

On this page:


Overview

The Indiana University Research Database Complex (RDC) supports research-related MySQL databases and data-intensive applications that require databases. The RDC is strictly devoted to supporting research, and is not an instructional, classroom environment.

Individual database backups are not provided in the Research Database Complex (RDC). UITS maintains system-level backups for disaster recovery, but these don't allow point-in-time data recovery for an individual database or schema. For this reason, UITS strongly encourages individual database owners to back up their data regularly. You can access backup utilities on Carbonate, Karst, and the Research Desktop (RED).

Use mysqldump to back up data

For MySQL data, UITS recommends the mysqldump backup utility.

Access mysqldump

To access mysqldump on the HPC platforms listed above, enter:

$ module load mysql

This loads the MySQL client and related utilities, including mysqldump.

Back up your database

The following example assumes that you have an RDC account called myaccount and that you're using the myaccount_root login to manage the account's databases.

To back up the myaccount_db1 database, enter:

$ mysqldump --host=sasrdsmp01.uits.iu.edu --port=3306 –u myaccount_root –p --routines myaccount_db1 > myaccount_db1_dump.sql

Enter your password when prompted.

Note:

If you store connection information in a .my.cnf file, you can simplify the above mysql command to:

$ mysqldump --routines myaccount_db1 > myaccount_db1_dump.sql

The mysqldump utility will begin exporting your database to the specified file. Note that the routines option includes any stored procedures or functions in the export.

The process creates a readable text file that contains both data definition language (DDL) and data insert statements. Be sure to protect the file; for help, see Store and protect your exported data below.

Back up multiple databases

To back up multiple databases with mysqldump, enter the –databases option along with a list of database names separated by spaces. For example:

$ mysqldump --routines -databases myaccount_db1 myaccount_db2 > myaccount_backup.dump

If you don't have a .my.cnf file, you will need to add connection information to the mysql command above.

Store connection information in a file

For convenience, you can store connection information in a file called .my.cnf in your home directory. Here is an example file:

[client]

host=sasrdsmp01.uits.iu.edu

port=3306

user=myaccount_root

password="<mypassword>"

Note:
If you include your password in the .my.cnf configuration file, be sure to change permissions so that the file isn't world-readable. For help, see Change permissions for a file in Unix.

Performance considerations

If your database is large, the export could take time. In this scenario, consider running mysqldump in the background. UITS strongly recommends running mysqldump on RED. For help, see Run a Unix process in the background.

Restore a database

The dump file contains drop statements. Databases, tables, and routines are dropped and recreated, and any exported data is reloaded. Your database objects will be restored to the state they were in when the dump file was created. Consider the timing of backups and restores, and use caution when executing these processes.

To restore your database from a mysqldump backup, enter:

$ mysql < myaccount_backup.dump

If you don't have a .my.cnf file, you will need to add connection information to the mysql command above.

Store and protect your exported data

Consider the following options for storing and protecting your export files.

Storage options

Because they contain both DDL and your data, mysqldump files can become large. Be sure to choose a location that can accommodate the expected size of your export file. If your database doesn't exceed the size of your home directory, it's simplest to keep your exports there.

If you need additional space, consider using DC2 scratch or project space. However, be aware that DC2 is not intended for long-term storage; files are not backed up and will eventually be purged. Only use DC2 as a temporary storage location for your backups. For more, see Available access to allocated and short-term storage capacity on IU's research systems.

If you need to maintain an archive of your data, consider moving it to the Scholarly Data Archive (SDA).

Protect your files

Because export files contain raw data, you should take appropriate steps to protect them. Ensure that your files have the appropriate permissions, and consider encrypting any that contain sensitive data. This is especially important if you choose to move or copy them to the SDA or other storage locations.

Get help

If you have questions, or need help connecting to or administering your RDC MySQL database, email the RDC Administration team.

This is document atnc in the Knowledge Base.
Last modified on 2019-05-16 13:40:56.

Contact us

For help or to comment, email the UITS Support Center.