ARCHIVED: Backing up your Legacy MySQL for Webserve database

This content has been archived, and is no longer maintained by Indiana University. Information here may no longer be accurate, and links may no longer be available or reliable.

Note:

The Legacy MySQL for Webserve environment has been discontinued for most users, and will be completely retired July 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 ARCHIVED: Migrate from Legacy to 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:

The Webserve and Webtest servers use PHP 7.1; PHP 5.6 has been retired and is no longer available. For more about PHP at IU, 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.

Create the backup directory:
```
  mkdir /ip/account/backups
```

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

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 ARCHIVED: Cron: Schedule 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.