ARCHIVED: Backing up your Legacy MySQL for Webserve database

This content has been archived, and is no longer maintained by Indiana University. Resources linked from this page 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 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:
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-08-15 08:54:08.

Contact us

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