Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    May 2002
    Location
    London, England
    Posts
    369
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing and exporting data with phpMyAdmin.

    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/showf...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...cts/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
    Jeewhizz - MySQL Moderator
    http://www.sitehq.co.uk
    PHP and MySQL Hosting

  • #2
    New to the CF scene
    Join Date
    Feb 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Query Regarding Da Same...

    Forum Powered By vBulletin
    PHP version: 4.3.9
    MySQL upgraded to 4.0.22
    Apache version:2.0.52

    here's the problem guys,
    our admin. has a local backup of da database, when hes' trying to import the gzipped forum backup, hes' facin some errors like timeout viz.,

    Fatal error: Maximum execution time of 300 seconds exceeded in C:\Fast\www\phpmyadmin\libraries\read_dump.lib.php on line 91

    what might b da problem...

    http://go4i.net/forum

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fatal error: Maximum execution time of 300 seconds exceeded in C:\Fast\www\phpmyadmin\libraries\read_dump.lib.php on line 91

    Maybe the problem is that the script timed out ...
    Anyway, please, start a new thred for your question.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #4
    New Coder
    Join Date
    Apr 2004
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Saving and replacing MySql database with new one

    Hi,
    I am not to good at mysql and am trying to preform a simple operation of downloading my database to excel and then reloading it after I make a few changes. So far I have not been able to do this . The form says to add data from text file and when I tryed an excel or a text file I am failing miserably.
    I tryed using a heading row and that did not work. I changed the upload specs to include only replace and comma seperated and still no luck.
    What am I doing wrong?
    Sincerely,
    Mike Harmon

  • #5
    New to the CF scene
    Join Date
    Apr 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TECHNO - MAX execution time of 300 seconds solved

    This was something that made me tear my hair out b4 i realized wot it was.
    i exported a table in phpmyadmin..all was good
    i logged into ssh to do a dump of the sql file
    i got the following after it had dumped 447,000 records out of 1.2 million

    ERROR 1064 at line 477660: you have an error in your sql syntax near'<br />
    <b>fatal error</b>; MAXIMUM EXECUTION TIME OF 300 SECONDS EXCEEDED IN <b' at line 1

    I thought dam it, it must be ssh timing out or something
    But in fact SSH was actually showing me wot phpmyadmin had printed at the bottom of my sql file...which i realized after i checked the sql file.
    it stated
    MAXIMUM EXECUTION TIME OF 300 SECONDS EXCEEDED etc etc

    phpmyadmin had only exported half of my records before stopping and printing that dam message which caused the SSH error. so it wasnt SSH

    HOW TO FIX THIS

    you need to open the httpd.conf file in your apache conf folder.
    Next (to speed this up use ctrl F to bring up the search box as the file is quite long) search for a key word like "Timeout" (or "300" which is your default setting)
    change the value and save the file
    restart the server
    DONE !!!!! http://www.codingforums.com/images/icons/icon14.gif
    Thumbs up

  • #6
    New to the CF scene
    Join Date
    Apr 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Also

    Also you may need to change the timeout variable for PHP itself
    (if locally is ususlly found in c:/windows/php.ini )
    open this file and look for -- > max_execution_time =
    in the script and change the variable for it

  • #7
    New to the CF scene
    Join Date
    May 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how can I export and import a large database

    Hi,
    I want to move my database to another server so Iwas trying to export it by using phpmyadmin and then imoprt it in the other server by using the same way u explained

    but I had an error says
    this page contains secure and nonsecure items

    this error appear only when I export a big tables or the whole Database

    if any body know another way to export the wole database or how can I solve this error

    thanks in advance

    GNabeeh

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Welcome here GNabeeh !

    It's not realy a good idea to post questions/problems in this sticky --> betrer start a new thread.

    Anuway, the errors you get have nothing to do with MySQL. It's a browser-related message trigggered by the pagecontent that can contain some applets or whatever. This message normally has some dialog-buttons ('ok', 'continue' etc) to open the page with the non-secure content executed. Or you can chage your browser-settings to always run applets etc (but that's not a realy good idea).

    there are a lott of other ways to migrate your data, like dumping all content in a textfile (run a search at mysql.com for "select into outfile" and then ftp the file to the server and run a load infile. Or you could use another db-frontend like MySQL-Front...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #9
    New Coder
    Join Date
    Mar 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    phpmyadmin access denied

    hi,

    scene : development envir -> production environment

    I exported the database structure using the phpmyadmin as harry.sql from my local system.now I went to company server/cpanel /mysql database.created a user with username and password. I would like to import this file and I tried the same way that I used to do in local server but it says Access denied for name@localhost.com some #1044 blah blah.



    I have tried with already existing users and the privileges for all the users I tried as ALL Priveleges.



    Please tell me a detailed step by step for solving this(bro a bit urgent).

    thanking you in advance
    Harish


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •