Jeewhizz
07-08-2003, 01:50 AM
Importing and exporting data with phpMyAdmin.
At some point, we all need to import a table or textfile into our database, or migrate tables from one MySQL-server to another. There are a lot of ways to do this, but the most common way is through phpMyAdmin. This web-front-end for MySQL, creates a dumpfile (--> a textfile that contains all the SQL statements that are necessary to recreate the database/table and/or to add the data into a table).
The process has 2 or 3 steps:
1. Exporting the data/structure --> creating the dumpfile
2. (Moving the dumpfile --> uploading the dumpfile)
3. Importing the dumpfile and executing it.
Step 1 and 3 can be done using phpMyAdmin. (search the MySQL site or use the links below for alternatives) This info is for phpMyAdmin 2.4. You can download it here:
http://sourceforge.net/project/showfiles.php?group_id=23067
There are newer version, but few hosts already supply 2.5 :(
Step 1:
Open phpMyAdmin and select the database from where you want to export data and/or structure. Go to the tab with "Export". Select the table(s) you want to save. Select the option you need: Data only or data and structure or structure only:
--> if you only want to update a table on another server, which has a table with the same structure, then you choose data only
--> if you want to create a new table on another server (for instance, if you have a development environment and you now want to put your application in production) then choose structure only
--> if you are moving a complete table, choose data and structure
Select "save as file"
Hit "go"
You can now enter a filename and specify where the file needs to be saved. It's recommended to choose a meaningful filename that includes the date. Like "shop_05_07_03.sql"
This creates the dumpfile. If you open it in notepad or any other texteditor, it looks something like:
# phpMyAdmin MySQL-Dump
# version 2.4.0
# http://www.phpmyadmin.net/ (download page)
#
# Host: thehostname would be here
# Generation Time: Jul 05, 2003 at 02:39 PM
# Server version: 4.0.13
# PHP Version: 4.2.3
# Database : `db-name would be here`
# --------------------------------------------------------
#
# Table structure for table `test`
#
CREATE TABLE test (
siteID int(6) NOT NULL auto_increment,
uname tinytext NOT NULL,
upwd tinytext NOT NULL,
nick tinytext NOT NULL,
sitename text NOT NULL,
regdate date NOT NULL default '9999-01-01',
numtrial int(7) unsigned NOT NULL default '0',
PRIMARY KEY (siteID)
) TYPE=MyISAM COMMENT='Table with userinfo';
#
# Dumping data for table `useradmin`
#
INSERT INTO test VALUES (1, 'raftest', 'testt', 'rafke', 'www.raf.com/site/test.html', '0000-00-00', 0);
This is the dumpfile for table 'test' that contains one record. As you can see, it's just a serie of sql-statement to recreate the table and insert all records.
Step 2:
If necessary, can can upload this file to a server or move it to another PC (on a disk or whatever)
Step 3 :
Open phpMyAdmin. Select the database you want to import the table(s) in.
Go to the SQL tab.
Look at the bottom for "Or location of the textfile" and browse to the dumpfile. Doubleclick on it so that the fileadress appears in the textbox. Then hit "go"
All sql-statement will be executed and you will get a notification after the file is processed.
Links for more info and comments:
phpMyAdmin downloads : http://www.phpmyadmin.net
phpMyAdmin documentation : http://www.phpmyadmin.net/documentation/
Other MySQL frontends and ETL-tools : http://www.mysql.com/portal/software/products/index.html
MySQL command to create a dumpfile : http://www.mysql.com/doc/en/LOAD_DATA.html , http://www.mysql.com/doc/en/SELECT.html
Created by: raf
At some point, we all need to import a table or textfile into our database, or migrate tables from one MySQL-server to another. There are a lot of ways to do this, but the most common way is through phpMyAdmin. This web-front-end for MySQL, creates a dumpfile (--> a textfile that contains all the SQL statements that are necessary to recreate the database/table and/or to add the data into a table).
The process has 2 or 3 steps:
1. Exporting the data/structure --> creating the dumpfile
2. (Moving the dumpfile --> uploading the dumpfile)
3. Importing the dumpfile and executing it.
Step 1 and 3 can be done using phpMyAdmin. (search the MySQL site or use the links below for alternatives) This info is for phpMyAdmin 2.4. You can download it here:
http://sourceforge.net/project/showfiles.php?group_id=23067
There are newer version, but few hosts already supply 2.5 :(
Step 1:
Open phpMyAdmin and select the database from where you want to export data and/or structure. Go to the tab with "Export". Select the table(s) you want to save. Select the option you need: Data only or data and structure or structure only:
--> if you only want to update a table on another server, which has a table with the same structure, then you choose data only
--> if you want to create a new table on another server (for instance, if you have a development environment and you now want to put your application in production) then choose structure only
--> if you are moving a complete table, choose data and structure
Select "save as file"
Hit "go"
You can now enter a filename and specify where the file needs to be saved. It's recommended to choose a meaningful filename that includes the date. Like "shop_05_07_03.sql"
This creates the dumpfile. If you open it in notepad or any other texteditor, it looks something like:
# phpMyAdmin MySQL-Dump
# version 2.4.0
# http://www.phpmyadmin.net/ (download page)
#
# Host: thehostname would be here
# Generation Time: Jul 05, 2003 at 02:39 PM
# Server version: 4.0.13
# PHP Version: 4.2.3
# Database : `db-name would be here`
# --------------------------------------------------------
#
# Table structure for table `test`
#
CREATE TABLE test (
siteID int(6) NOT NULL auto_increment,
uname tinytext NOT NULL,
upwd tinytext NOT NULL,
nick tinytext NOT NULL,
sitename text NOT NULL,
regdate date NOT NULL default '9999-01-01',
numtrial int(7) unsigned NOT NULL default '0',
PRIMARY KEY (siteID)
) TYPE=MyISAM COMMENT='Table with userinfo';
#
# Dumping data for table `useradmin`
#
INSERT INTO test VALUES (1, 'raftest', 'testt', 'rafke', 'www.raf.com/site/test.html', '0000-00-00', 0);
This is the dumpfile for table 'test' that contains one record. As you can see, it's just a serie of sql-statement to recreate the table and insert all records.
Step 2:
If necessary, can can upload this file to a server or move it to another PC (on a disk or whatever)
Step 3 :
Open phpMyAdmin. Select the database you want to import the table(s) in.
Go to the SQL tab.
Look at the bottom for "Or location of the textfile" and browse to the dumpfile. Doubleclick on it so that the fileadress appears in the textbox. Then hit "go"
All sql-statement will be executed and you will get a notification after the file is processed.
Links for more info and comments:
phpMyAdmin downloads : http://www.phpmyadmin.net
phpMyAdmin documentation : http://www.phpmyadmin.net/documentation/
Other MySQL frontends and ETL-tools : http://www.mysql.com/portal/software/products/index.html
MySQL command to create a dumpfile : http://www.mysql.com/doc/en/LOAD_DATA.html , http://www.mysql.com/doc/en/SELECT.html
Created by: raf