Importing data

[Bengal313]

I am new a MYSQL. I have been using Access. Now, I am converting to MYSQL. I got a tool that takes a Access file and converts it to a dump file call "filename.sql" Now how do I import that into MYSQL? After logging into mysql what should I do next and what is the command to import this file? Also, I tried using PHPMYadmin. Because this is a huge file (50,000 records) it times out. So is there a way I can adjust the time so it doesn't time out. I want to learn how to do this both ways. via the command line and via phpmyadmin. thanx

[Scrowler]

Yup. Simply open the .sql file in notepad and seperate it into seperate statements :) May be daunting but it's the only way I can think of :)

[raf]

search the forum for doing in an the commandmline (in batchmode). I gave an example here :
http://www.codingforums.com/showthread.php?s=&threadid=23162&highlight=batchmode
You'll also see why phpMyAdmin isn't the best option --> no need to do this over the webserver + it's the php-script that times out --> has got nothing to do with the MySQL db. You'd need to raise the timout-setting to be able to use phpMyAdmin, but that wouldn't be the best sollution.
Like i said, there is no need to do this over the webserver.

[Bengal313]

What about using the command line. I am trying to really learn that. I have seen the syntax somewhere on the web. But i really didn't understand it. Can someone help explain or show me how I wound write it. Here is the info. I created a database called "dog_license" via phpmyadmin. Now I have converted my access to "data.sql" which is located at c:/data.sql From the command line what sould I type given that.

MYSQL>?????

Also after I create and populate the data I need to update the info. Since it is a hugh file How can I write a sql statement that would dump all the data, keep the table structure, and repopulate it with the data from a new "data.sql" file.

All this would have been much easy if the stupid phpmyadmin does not time out, since the data file is soo huge 950,00 records). I was told that it is a php issue and that I can adjust it on the server. Any thought on which file to change on a linux machine?

[raf]

Did you read my post ?

Changing your php settings to run this sql-file isn't the way to go. You should run this command directly against the MySQL server, and not over the webserver. ecause this will create extra load that will surely slow down your app a lot more, while the operation is running. Increasing the scripttimout could have negative
effect if you have bugs in your script.

So just check out the thread, read my examplecode (it's only one line) or follow the link to the relavant page at mysql.com. Just ask if you need more info on it (after reading everything through).

Another option is to instal a non-web db-frontend for MySQL, like MySQL-front. That project was discontinued, but is you want, i can send you the installfiles.


The same for your update --> you need to run the commands in commandmode to spare the webserver
About that update. I don't quite understand what you try to do. You want to empty the table and then repopulate it. This means you can not have relationships in other tables to this table. (since the ID's in this table will change, you can not use them as a foreign key inside other tables) so you're not really having a relational db.
Can't you just update the existing records and insert new records ? The replace statement does exactly that. Look it up here
http://www.mysql.com/doc/en/REPLACE.html
If you wan't the dump thing, you have 2 option:
1. (create a dumpfile file), delete all records, import new records --> if you don't need to archive the older tables
2. Create a dumpfile (structure only), rename the original table, run the dumpfile to create the new table and rename it to the original tables-name, import the new records --> if you need to archive the older tables.

[Bengal313]

I am still not find the exact syntax to type in after I log into MYSQL. I am at this promt.
MYSQL>????

Sorry about being so elemental. I am try to learn!

[raf]

mysql -h localhost -u root -p -H < test.sql


where
test.sql = the dumpfile (file with the sql-statements that nee to be executed.
root = the user
-p = a parameter that will prompt you for a password for that user
localhost = the server where the MySQL s'erver runs on

[Bengal313]

This is what I typed:

mysql linux.warren.gov -u -p -H < c:\data.sql;

I got this error:

-bash c: data.sql: No such file or directory.

I do has a file called data.sql in the C: on my local machine.

[raf]

Move the batch-file (= data.sql) to the c:\mysql\bin directory
Also, you have:
mysql linux.warren.gov -u -p -H < c:\data.sql;

(all bold = probable error)
You best run the command-line on the machine where the MySQL server runs on, so you then have
mysql -h localhost -u your username -p -H <data.sql


With your current command, this would mean that everyone can execute script on your MySQL serer, without needing a username or password ...

[Bengal313]

Well, My MYSQL is on that server. (linux.warren.gov). I can log into via telnet. But my data.sql is on my local machine (desktop). Should I move the data.sql to ther server. If so, move it to where and how? Also I have been reading about "mysqlimport" command and "Load data infile" what can you tell me about both of those options.

[raf]

i'd move the .sql file to the server, under the bin-directory and then open the commandline on the server there. This way, the scriptfile will be executed faster and you don't use the connection while it's running (so you can't have problems with the connection either)

mysqlimport works different. If you create a dumpfile with phpMyAdmin, then you create a scriptfile that contains all sql-commands you need to (recreate the table and) insert all records.
For mysqlimport, you don't need to create a dumpfile, but a textfile (with 'select into outfile' http://www.mysql.com/doc/en/SELECT.html ). Like a csv (comma seperated value) file. Then you can run mysqlimport (which is a command that uses 'load data infile' sql-command
Using musqlimport will be a lot faster then executing he dumpfile, but you can only use it for importiung rows. Not for creating databases etc..
http://www.mysql.com/doc/en/mysqlimport.html