Knowledgebase: Database
Backup/Restore MySQL Database
Posted by Support on 22 February 2008 07:14 AM
To back up your database or tables, there are two ways to achieve this.

  1. For dedicated server/co-location users - use the mysqldump command line tool.

    The syntax to dump an entire database is:
    mysqldump -u username -p --databases mydatabase > mydatabase.sql
    (enter password when prompted)

    You can also choose to dump only certain tables:
    mysqldump -u username -p mydatabase mytable1 mytable2 > mydatabase.sql
    (enter password when prompted)

    The mysqldump command prints out all the SQL commands necessary to reload your database (or specified tables), which in the examples above is redirected to the file mydatabase.sql. The mysqldump tool is a standard part of the MySQL distribution, and it uses the same options format as the other tools. The documentation is contained in the MySQL manual. mysqldump --help provides a quick option list.

  2. Shared hosting clients - use phpMyAdmin - our MySQL administration tool. If you're not sure about accessing phpMyAdmin, please refer to your control panel or setup e-mail.
    • Click on your database name in the left hand navigation bar.

    • Click on EXPORT (top tab).

    • Highlight the table/s you want to back up.

    • Select STRUCTURE and DATA radio button.

    • Select "Enclose table and field names with backquotes".

    • Select "Save as file" and "zipped" check boxes.

    • Click "Go" and a zipped archive file will be generated.

There are two ways to restore/import your MySQL database to our server:

Please note that you will need a web hosting plan that supports a MySQL database (they are created for you automatically if your plan supports it), for dedicated/co-lcation server clients you need to create a database first.

  1. Using phpMyAdmin
    • Log into phpMyAdmin.

    • Choose the database name on the left.

    • Click SQL (query window) on the top.

    • Paste your sql dumb file there.

    • Click "Go".

    If your database is large, loading it using the mysql shell program (described below) will be more efficient than using phpMyAdmin, which could time out. We also have a maximum file upload limit of around 8MB on the server.

  2. Using mysql command from unix shell

    Use the following command to load the database dump file:

    mysql -u userame -p database < dump_file

    where userame is your MySQL username, database the name of the database you want to import the scheme into and dump_file the name of your SQL dump file (dump.sql, for example).
(1327 vote(s))
Not helpful