Active Web Hosting Logo


How To Import and Export Data From A Database

The easiest way to import and export data into your database is to use phpMyAdmin, which is set up on Active Web Hosting to manage your database. You will need to log in using your database username and password. If you did not create or set up a database yet, you will need to first create a CGI account if you haven't already done so, and then create a databas account. Here are two links that can help you with this:

Importing Data

In the left side menu of the phpMyAdmin screen, click on your domain name.

On the right (largest) area of the screen, click on the SQL tab (second tab from the left).

Now you should see a large text box and a smaller one-line text box under it. Here you have two choices. You can cut and paste the SQL data into the large text box or you can click the Browse button next to the smaller text box and find the file containing the database data you wish to import. Be sure that the file is in plain ASCII text. If you had exported the database previously, then you will have to extract the file from the zip, gzip or bzip file before you can import it. Note that you can not import XML files. Click the Go button to import the data into your database.

Special Note About Importing Large Database Backups

Due to server setup, you will not be able to import very large databases. If your database is over 1,000 lines, you may have problems including time-outs and other errors during the import process. Only a portion or maybe no data at all may actually import to your database.

Do not use extended inserts as this may make it impossible to restore large databases.

To import large databases, you will want to load the .sql database backup file into a text editor that can handle very large text files, such as EditPlus for Windows. Then separate the file into smaller 1,000 line chunks and save each chunk. You'll then have to import each chunk separately until you have imported your entire database.

Another option is to locate and use a file-splitting utility that may do this task for you quickly. Then just import the resulting saved junks one at a time to the database.

Note that when you import even these chunks of data, you still may have to wait a little while for them to fully upload due to their size. This may take quite a bit of time if you have a large database and are using dial-up internet access.

Exporting and Backing Up Data

In the left side menu of the phpMyAdmin screen, click on your domain name.

On the right (largest) area of the screen, click on the Export tab (third tab from the left). You will be taken to a screen where you can export your data.

From the list box under the heading View dump (schema) of database you can select what tables you wish to back up or export. To select more than one, hold down the CTRL key and click on the additional tables you wish to export. Alternatively, you can click on the Select All link just to the bottom right of the list box.

On the right side of the list box you have several options to choose from to export your database:

Structure only: This exports only the table structures (ie. the way each table is set up) but not the data from all the tables that you selected from the list box.

Structure and data: This exports the entire structure and all the data in each table that you selected from the list box.

Data only: This exports only the table data and not the tables themselves or their settings. Export to XML format: This exports all tables, structures, and data into XML format for use in XML-based applications.
Which one should you choose? If you're not sure, then Structure and data is the best to use if you want to back up your entire data and structures of all tables you have selected from the list box.

Under the list box you will see more options:

Add 'drop table': Check this box if you want to create a backup which when imported back into the database will not merge but delete tables and data of the same name, and replace them with the tables and data from the backup. This is nice for backups because you can ensure that no possible corruption from the previous database would be left over when you replace it with your backup.

Complete inserts: This adds the column name for each INSERT command. This results in a bigger file, but better documentation when your table and data is inserted into the database if you decide to import this file later.

Extended inserts: This will result in a smaller file size because the INSERT verb and table name is used only once. Do not use extended inserts as this may make it impossible to restore large databases.

Enclose table and field names with backquotes: Use this option if you have special characters in your table names and want to protect them from being imported wrong later.

Save as file: You get three choices here:

Use zipped if you are downloading your database backup to a windows machine. You can then use an unzip utility like WinZip to extract the database backup file.

Use gzipped if you are using a Linux or Unix machine and your database is too big for normal text download but not too big.

Use bzipped if you are using a Linux or Unix machine and you are downloading a very large database. bzip and gzip are tools that come already installed on most Linux and Unix machines. The bzip format compresses the files more.

In addition, you can leave the box unchecked and you will be downloading a plain ASCII text version of your database backup, which does not need extracting with a special tool. Use this feature if your database is rather small.

Once you've made your choices, simply click the Go button to start your download. The larger the database, the longer you may have to wait before the download starts, especially if you are using one of the compression methods mentioned above.

 


Home - Support - Management - About Us
... Active Web Hosting, 1445 American Pacific Dr. Ste 110-318, Henderson, NV 89074 ...
Toll-Free (800) 946-7764   Fax: (702) 567-1831