Backup a MySQL database at IU RDC

• Overview
• Use mysqldump to back up data
  • Access mysqldump
  • Back up your database
  • Back up multiple databases
  • Store connection information in a file
• Performance considerations
• Restore a database
• Store and protect your exported data
  • Storage options
  • Protect your files
• Get help

Overview

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 via Research Desktop (RED).

Use mysqldump to back up data

For MySQL data, UITS recommends the mysqldump backup utility.

Access mysqldump

Open a Terminal window in RED, and then enter:

module load mysql

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

Back up your database

The following example assumes you have an RDC account called myaccount and are 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 --no-tablespaces --routines myaccount_db1 > myaccount_db1_dump.sql

Enter your password when prompted.

mysqldump --no-tablespaces --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, and that the no-tablespaces option is needed to avoid a permission error introduced on MySQL 8.0.21.

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 --no-tablespaces --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>"

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, see Available access to allocated and short-term storage capacity on IU's research systems.

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