SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy import .sql file into mysql

    How can import a .sql file (with some create table and insert statements) into mysql.
    if a do mysql> mysql<test.sql or something similar it doesn't work.In which dir must be the .sql file.
    Thank you very much for your help.

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2000
    Location
    Norcross, GA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From your command prompt go to the directory where you have your .sql file. Type:

    mysql -u username -p password databasename < file.sql

    and press enter. If you say it is not working and do not supply any further details, nobody here can help you because we can't see what's on your screen
    - Mike
    http://www.georgiaoffroad.com

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2002
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also you may want to consider using PHPAdmin. It will give you the change to see your database laid out in a browser versus the command line.

    Also importing and dumping databases is simply a matter of a few clicks. Checkout PHPAdmin at http://www.phpwizard.net/projects/phpMyAdmin/ --- Its pretty easy to install. Just follow the setup instructions and good luck.

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2000
    Location
    Norcross, GA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great tool for adminstration, and even for learning (everything you do is translated into a sql command that's printed at the top of the screen).

    For data importing, however, I still prefer command line. phpMyAdmin has failed on a large percentage of sql dump files that command line processed without a hitch.

    For that reason, I use both
    - Mike
    http://www.georgiaoffroad.com

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2002
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHPADMIN does have that problem. I have also noticed that. I break up the dump in smaller chunks and it seems to work just fine. ;-)

    Good for learners though. The dump problem doesnt seem to be as bad as it used to be on older versions however. Command line is allways good though too.

  6. #6
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another way would be:

    mysql> script dump.sql

  7. #7
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Robo
    Another way would be:

    mysql> script dump.sql
    You'll find:

    mysql> source dump.sql

    will work a lot better!
    Oh no! the coots are eating my nodes!

  8. #8
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoops

  9. #9
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Frisco, Texas
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm going to jump into this thread because I've been having the exact same problem. But, when I attempt to upload the .sql file through the command line, it launches the MySQL Client and then returns to the command prompt. The tables are still not there.

    So, I try it through phpMyAdmin but there is no option for uploading into the database. Someone mentioned that my php.ini file might not be configured properly to allow uploads to the server. Can you someone explain that and offer a fix?
    signature

  10. #10
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you try FTPing your sql file to the server, then import it to MySQL with

    mysql -u username -p password databasename < /home/username/db-dump.sql
    Work smarter, not harder. -Scrooge McDuck

  11. #11
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First off, can you clarify for us, whether you are trying to do this locally or on a remote server, Robo is providing an alternative if you are trying this on a remote server, however, if we knew if this was local or not, then we can launch in and tell you what else you can try/do depending on the server.

    I'm presuming that right now that you are doing this locally on a Windows box?
    Oh no! the coots are eating my nodes!

  12. #12
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Frisco, Texas
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm doing this via a Windows computer but the server is Unix and I'm virtual hosting. So, I guess that means its a remote location that I'm connecting from, right?

    Even though I'm virtual hosting, I do have root access to the server if that helps.

    And I've tried both methods. I FTP'd the SQL file to the server and tried that command.

    Again, I also tried to use phpMyAdmin but there was no option to upload anything. This led some others to tell me that it could be a php.ini problem. I checked my php.ini file but it was blank! That's right, no code was in it at all!

    I asked my tech support about this and they said that was the way it was supposed to be.

    I hope that information helps. If you need to see my phpinfo page, it can be found here.
    signature

  13. #13
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your php.ini blank... strange.

    Although, if it is virtual hosting, not too sure how this would work if everyone had different php.ini's.

    You PHP configuration looks okay to accept uploads, (but who am I to talk!), so in phpmyadmin, under the box to type queries in to query your database, is there no file input where you can load an sql file from?

    If that is not there, I'm not sure why it isn't, but you could always try, and this I'm not sure if it would work, given phpmyadmin's inability to process large SQL queries, copying and pasting all the code to make the database's (from the .sql file) into the textarea, and then cliking go, and creating the tables that way.

    Other than that, I dunno

    And yip, you are dealing with a "remote" server, I was just pondering whether or not this was on your webserver that you might have had running locally, or as you have told us, on your virtual hosting.
    Last edited by timnz; Mar 5, 2002 at 04:18.
    Oh no! the coots are eating my nodes!

  14. #14
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what errors do you get when you tried importing the sql file using my command with the FTP upload?
    Work smarter, not harder. -Scrooge McDuck

  15. #15
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Frisco, Texas
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Robo
    Have you try FTPing your sql file to the server, then import it to MySQL with

    mysql -u username -p password databasename < /home/username/db-dump.sql
    Yeah, I tried this and here's what it returned:

    mysql Ver 9.38 Distrib 3.22.32, for -freebsd4.1.1 (i386)
    By TCX Datakonsult AB, by Monty
    This software comes with ABSOLUTELY NO WARRANTY.

    Usage: mysql [OPTIONS] [database]

    -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to
    get table and field completion. This gives a quicker
    start of mysql.
    -B, --batch Print results with a tab as separator, each row on
    a new line. Doesn't use history file
    -C, --compress Use compression in server/client protocol
    -T, --debug-info Print some debug info at exit
    -D, --database=.. Database to use
    -e, --execute=... Execute command and quit.(Output like with --batch)
    -f, --force Continue even if we get an sql error.
    -i, --ignore-space Ignore space after function names
    -?, --help Display this help and exit
    -h, --host=... Connect to host
    -H, --html Produce HTML output
    -n, --unbuffered Flush buffer after each query
    -O, --set-variable var=option
    Give a variable an value. --help lists variables
    -o, --one-database Only update the default database. This is useful
    for skipping updates to other database in the update
    log.
    -p[password], --password[=...]
    Password to use when connecting to server
    If password is not given it's asked from the tty.

    -P --port=... Port number to use for connection
    -q, --quick Don't cache result, print it row by row. This may
    slow down the server if the output is suspended.
    Doesn't use history file
    -r, --raw Write fields without conversion. Used with --batch
    -s, --silent Be more silent.
    -L, --skip-line-numbers Don't write line number for errors
    -N, --skip-column-names Don't write column names in results
    -S --socket=... Socket file to use for connection
    -t --table Output in table format
    -u, --user=# User for login if not current user
    -v, --verbose Write more (-v -v -v gives the table output format)
    -V, --version Output version information and exit
    -E, --vertical Print the output of a query (rows) vertically
    -w, --wait Wait and retry if connection is down

    Default options are read from the following files in the given order:
    /etc/my.cnf ~/.my.cnf
    The following groups are read: mysql client
    The following options may be given as the first argument:
    --print-defaults Print the program argument list and exit
    --no-defaults Don't read default options from any options file
    --defaults-file=# Only read default options from the given file #

    Possible variables for option --set-variable (-O) are:
    max_allowed_packet current value: 25165824
    net_buffer_length current value: 16384
    >
    So then its just back at the command prompt.
    signature

  16. #16
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See if you can use mysqldump then

    mysqldump /path/to/db-dump.sql
    Work smarter, not harder. -Scrooge McDuck

  17. #17
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Norway
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The error is that there should be no blank space after the -p (the password thing). It looks strange, but so it is. The statement is:

    mysql -u username -ppassword databasename < /home/username/db-dump.sql

    You can also leave the -p without any blanks before "password", and that is recomended of safety reasons. You will then be asked for a password.
    Regards,
    Are

  18. #18
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    I've followed the advice above to import an existing database in to mysql via the command line but I'm getting the following error.

    C:\Downloaded>mysql -u root -p enscrx oncou_db < oncou_db.sql
    'mysql' is not recognized as an internal or external command,
    operable program or batch file.

    What am I doing wrong?

  19. #19
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is the mysql executable in the "Downloaded" folder of your C drive? You need to run the command from the bin dir of wherever you installed MySQL.
    Oh no! the coots are eating my nodes!

  20. #20
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great thanks for the quick response.
    Mysql in stalled in the following directory

    C:\apachefriends\xampp\mysql\bin>mysql -u root -p enscrx oncou_db < C:\downloade
    d\oncou_db.sql

    And the file I want to upload is in c:\downloaded\oncou_db.sql

    I'm getting the following eror

    mysql: unknown variable 'basedir=c:/Program Files/mysql/'

    What am I doing wrong?

  21. #21
    SitePoint Evangelist lance_vincent's Avatar
    Join Date
    Aug 2004
    Location
    philippines
    Posts
    574
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fuel Hosting
    Also you may want to consider using PHPAdmin. It will give you the change to see your database laid out in a browser versus the command line.

    Also importing and dumping databases is simply a matter of a few clicks. Checkout PHPAdmin at http://www.phpwizard.net/projects/phpMyAdmin/ --- Its pretty easy to install. Just follow the setup instructions and good luck.

    i know phpmyadmin can back up your database and restore it. How is this done? If anyone here know, please post it. It will be easier to use php backing up a database rather than on the commandprompt right?

  22. #22
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lance_vincent
    i know phpmyadmin can back up your database and restore it. How is this done? If anyone here know, please post it. It will be easier to use php backing up a database rather than on the commandprompt right?
    If you want to backup using PHPMyAdmin then select your database, go to the export tab on the right and select how you wish to export it using the options given.

    To restore the database you're basically going to go on the SQL tab and then select a location of a textfile with the SQL to insert the records again in it.

    Quote Originally Posted by fredbear
    C:\apachefriends\xampp\mysql\bin>mysql -u root -p enscrx oncou_db < C:\downloade
    d\oncou_db.sql

    And the file I want to upload is in c:\downloaded\oncou_db.sql

    mysql: unknown variable 'basedir=c:/Program Files/mysql/'
    Have you configured your my.cnf properly? (Most common place people put it is in C:\ )

    The basedir needs to be the same as your installation path of Apache.
    Oh no! the coots are eating my nodes!

  23. #23
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you configured your my.cnf properly? (Most common place people put it is in C:\ )

    The basedir needs to be the same as your installation path of Apache.

    I placed the my.cnf in C:\ as directed.

    When I try to import the database via PHP Myadmin it always times out. (It's a 15 mb database).

    Now when I use the command line it generates a large response with all the different commands to use etc but doesn't upload the file.


Bookmarks

Posting Permissions

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