Backing up your Legacy MySQL for Webserve database

Note:
Support for the Legacy MySQL for Webserve environment is ending May 31, 2018. If you have a Legacy MySQL account, you should migrate your service to the New MySQL for Webserve environment before that date. For more, see Migrate from Legacy to New MySQL for Webserve service.
Note:
The New MySQL for Webserve service is scheduled for automatic backups. If you are an account owner needing assistance with recovering backups on the New MySQL for Webserve service, email Web Services Support.
Important:
On Webserve, the default version of PHP is 7.1; however, PHP 5.6 will still be available until it is retired at the end of December 2018. Webtest servers are already using PHP 7.1. For help specifying which version of PHP to use, see PHP server-side scripting language.
Important:
You are responsible for performing and maintaining your own backups on mysql.iu.edu and mysql-test.iu.edu.

Following is an example of setting up an automatic backup script with a scheduled crontab. Be sure to replace all occurences of database_name, account, port, and root_password with your corresponding values. Optionally, you can replace the root user/password with any MySQL user you've set up that has proper privileges to access this database.

Backup script

The following code is a modified version of Julien Garino's auto_mysql_dump.sh, which can be found in the MySQL reference manual. The following example shows how to backup a database and place it in a backup folder on the account.

  1. Create the backup directory:
      mkdir /ip/account/backups
  2. Create the shell script that performs the backup:
      #!/bin/sh
      # Script name : auto_mysql_dump.sh
      # Backup database
      dir=`date +%Y-%m-%d`
      if [ -d /ip/account/backups ]; then
      mkdir /ip/account/backups/$dir
      /usr/local/mysql/bin/mysqldump -B --user=root --password='root_password' --host=localhost database_name > 
      /ip/account/backups/$dir/database_name.sql
      fi
      # End of script auto_mysql_dump.sh
  3. Place this file in /ip/account/auto_mysql_dump.sh and make sure it is executable by typing:
      chmod +x auto_mysql_dump.sh

Crontab

The following crontab example runs the backup script weekly and logs the results. For a list of predefined scheduling definitions, see the Cron Wikipedia entry. For more about using crontabs, see Cron: Scheduling jobs on the IU WWW servers.

  @weekly /ip/account/auto_mysql_dump.sh >> /ip/account/backups/backup.log 2>&1
  # run the backup script weekly

Restoring

The following example shows how to restore a database from a backup that was created in the backup section above. SSH into mysql.iu.edu, and move to the directory where your backup was saved. Then, type this command:

  mysql --user=root --password='root_password' database_name < backup_filename.sql
  # be sure to replace root_password, database_name, and backup_filename

If the SQL backup file has been zipped, run this command:

  gunzip database_name.sql.gz

Optionally, you can replace the root user/password with any MySQL user you've set up with proper privileges to access this database.

This is document bfod in the Knowledge Base.
Last modified on 2018-04-11 08:19:36.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.