Search:     Advanced search

Backing Up your Database

Article ID: 114
Last updated: 24 Sep, 2012
Revision: 1
Print
Export to PDF
Email to friend
Add comment
Views: 0
Comments: 0

This section helps you with the back up of your existing database. We strongly recommend you back up your existing database incase of any failures during the upgrade. The right way to back up your database depends on which database system you are using. The instructions below are one way to back up a MySQL database. Another option would be to use a tool like phpMyAdmin to manually make a backup. The documentation for your database will give more options. 

There are many ways to do such backups. Here is an outline of a little script you can run on Unix to backup the database (it works well to have such a script run daily via a cron task):
You can back up your MySQL database in the following manner:
  1. From the Hosting Control Panel e.g cPanel
  2. From MySQL CLI
  3. Tools for database backups ( phpMyAdmin or MySQLDumper etc )

From Hosting Control Panel  (cPanel)

To backup the databases from cPanel please do the following:
  1. Login to your control panel and click Backups.
  2. Under Download a MySQL Database Backup, click the name of the database.
  3. Click Save As.
  4. Select a destination for where you would like the back up to be saved, locally.
  5. Once you have a backup, if you ever inadvertently lose your data, then you can restore the database easily and quickly.
Other control panel softwares such as plesk, helm, dotpanel, H-sphere etc has their own respective database backup tools that offer easy web interface that allows downloading backup of database.  

From MySQL CLI :

In order to back up your database from MySQL CLI you will require 2 things:
• SSH or Telnet access to your site. You will need to check with your hosting company to see if this is available.
• An SSH/Telnet Client, such as PuTTy.
Open your SSH/Telnet client and log into your website. The command line prompt you will see will vary by OS. For most hosting companies, this will bring you into the FTP root folder.
cd /my/backup/directory
mv moodle-database.sql.gz moodle-database-old.sql.gz
mysqldump -h example.com -u myusername --password=mypassword -C -Q -e --create-options mydatabasename > moodle-database.sql
gzip moodle-database.sql

Character encoding

Make sure that a database backup uses the correct character encoding. In most databases, use UTF-8.

When dumping the entire Moodle database, check for possible character encoding issues. In some instances, backups created with mysqldump or phpMyAdmin may not properly encode all of the data. This will result in non-readable characters when the database is restored.

Tip: One solution is to use MySQL Administrator 1.1 or another tool that will force a UTF-8 dump of the data.

From Tools for database backups :

You can also generate a back up of your database using a third party software like phpMyAdmin. We are assuming phpMyAdmin is already installed on your server. Go to phpMyAdmin in your web browser and select the database you wish to back up by clicking on the name. If you have multiple databases, you will need to select the name from the drop menu.
In the right-hand frame, you will see a row of links. Click on Export.
Now in the right-hand frame you will see three sections. In the first section called Export you select the table(s) you wish to back up by selecting them from the list. To select multiple tables, hold the Ctrl key and lick the table names. To select all table, click the Select All link.
In the second section called SQL Options, make sure you have the following boxes checked:
Structure
  Add ‘drop table’
    Add AUTO_INCREMENT value
     Enclose table and field names with backquotes
        Data 
In the third section check Save as file and type a name for the backup in the text box adjacent to File name template. If your system supports it, you may also choose a compression type. None is selected by default.
Click on Go and you will be prompted to save the backup on your local computer. Save the back up file on your computer.
phpMyAdmin
phpMyAdmin is the tool of choice with most web hosting providers. 
MySQLDumper
MySQLDumper is a backup script for MySQL databases, written in PHP and Perl. MySQLDumper uses a proprietary technique to avoid execution interruption when running PHP scripts (the max. execution time is usually set to 30 seconds). MySQLDumper also cares for the encoding problems mentioned above. It also works with compressed files and allows setting up regular cron jobs for updating and updating to a remote FTP site.

This article was:  
Also listed in
folder Modules Wise Documentation -> Installation - Upgrades
folder Modules Wise Documentation -> Getting started
folder Troubleshooting
folder Upgrading

Prev   Next
Managing comments and feedback     Making a listing as featured and un-publish it as draft.