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.
|
