Importing a 400mb .sql file via command line

[DirtyBirdNJ]

Hello, I am trying to do a conversion from Groupee/Eve forums to Vbulletin 3.8. I have installed VB, and it is working fine.

My issue has been how to import the data from the other database. I created a .sql backup of the DB, and have downloaded it from the original host (still running) forum and put the .sql backup file on our new development server.

First try, I used SQL Manager Lite... unfortounately their demo version is limited to databases 100mb or smaller. Arg.

Second try, I finally figured out how to access the windows MySQL command line. I ran this command:

mysql eve_import --user=MYUSERNAME --password=MYPASSWORD < D:\eve_backup.sql

This executed, but unfortounately it executed for 5 minutes and reached the server's exeuction time limit. Running the .sql file pinned the CPU and took up about 170mb of memory, I'm glad I'm the only one working on this server!

I don't know if it's possible to specify a timeout limit in the same SQL query you're executing.

Can anybody suggest a way to overcome this?

[Marco van Herwaarden]

First of all you should not import into 3.8 but 3.7. Unless i missed something ImpEx will only support production versions.

The only reliable method of backing up and restoring a database is with shell access via telnet or ssh. This is because backing up with a PHP script like that in the Admin CP or phpMyAdmin can result in PHP timeouts errors and an incomplete backup file. Please see the instructions here:

Backup:
http://www.vbulletin.com/docs/html/m...nce_ssh_backup

Restore:
http://www.vbulletin.com/docs/html/m...ce_ssh_restore

P.S. If you don't have shell access, some people have also reported success with these scripts:

MySQLDumper:
http://www.mysqldumper.de/en/index.php

MySQLHotxcopy:
http://www.vbulletin.com/forum/showt...t=mysqlhotcopy

Bigdump:
http://www.ozerov.de/bigdump.php

PS Increasing the limits in your SQL config might also be needed.

[DirtyBirdNJ]

Thank you for those suggestions Marco.

I would love to change the mysql config file, but I don't know where it is. I am using a Windows 2003 Server and MySQL 4.1. I also scared the pants off myself, I did not install 3.8, I have 3.7.4 set up.

One of my friends suggested writing a .php file that would:

1. call a function to set the timeout to unlimited
2. execute a shell command to do the database import
3. call a function to set the timeout back to 300 seconds

It sounds like some of those scripts you referenced will do the trick. I have this habit where I like to read the code of scripts before I run them, so now I have some fun work stuff to do over the holidays... woo

Happy Holidays!

[vbplusme]

IMHO, use the wheel that has already been created and working with success. Hacking to change php will cause you are lot more headaches that its worth. I often use the shell to import database with great success though I do use tools like bigdump that has been mentioned as well. With a 400 meg database, I think you would be wise to stick with "known" solutions rather than trying to use or develop hacks that will shoot you in the head and cause a lot of pain before you finish the job and then you end up with an "unknown" state that is nearly impossible to troubleshoot.

HTH

[DirtyBirdNJ]

Thanks for the reply vbplusme.

The problem is there is no "known" way to do this, at least for me. The pieces of software linked to above are ok, but one needs perl... and the last thing I want to do is mess with my environment by installing new languages.

Using PHP to modify settings during the execution of a script might work- but I agree with you. Introducing PHP into the mix only further confuses things, it's one more variable in the equation.

Ok so now the question is how to modify the MySQL conf file.

Where is that file?

SO...here's what I think I need to do now.

1. Change the execution timeout from 300(seconds) to 0(unlimited)
2. Run the import straight from the mysql cmd line interpreter
3. Change the execution timeout back to 300(seconds).

Thoughts?

[Marco van Herwaarden]

On a typical linux configuration the MySL config file will be /etc/my.cnf

[DirtyBirdNJ]

Thanks for the suggestion Marco. Unfortunately, I'm stuck with a windows 2003 server. On one hand I would prefer linux, but I am not a server admin and having somebody who can set the environment up for me (and fix things when I break them) is worth it.

I found the mysql config file, my.ini, in Program Files\MySQL\MySQL Server 4.1\

I can't find the line/entry that specifies the timeout in my.ini. I've been googling, searching MySQL's 4.1 documentation... and I can't find anything on how to modify the execution timeout. The only links I find are people (trying) to modify it via PHP... which is NOT what I want to do.

There are several other .ini files in the directory where my.ini is, and they are labled things like my-large.ini, my-small.ini... etc.

None of these files have any mention of the word "timeout", "time out", "time_out" or the value 300 (what the timeout is set to, as mentioned in the error message I get) do not appear anywhere in these files.

WHERE IS THIS #$*#&@! SETTING?!?!?

[SEOvB]

Originally Posted by DirtyBirdNJ

Thanks for the suggestion Marco. Unfortunately, I'm stuck with a windows 2003 server. On one hand I would prefer linux, but I am not a server admin and having somebody who can set the environment up for me (and fix things when I break them) is worth it.

I found the mysql config file, my.ini, in Program Files\MySQL\MySQL Server 4.1\

I can't find the line/entry that specifies the timeout in my.ini. I've been googling, searching MySQL's 4.1 documentation... and I can't find anything on how to modify the execution timeout. The only links I find are people (trying) to modify it via PHP... which is NOT what I want to do.

There are several other .ini files in the directory where my.ini is, and they are labled things like my-large.ini, my-small.ini... etc.

None of these files have any mention of the word "timeout", "time out", "time_out" or the value 300 (what the timeout is set to, as mentioned in the error message I get) do not appear anywhere in these files.

WHERE IS THIS #$*#&@! SETTING?!?!?

If the setting isn't in your my.cnf file you can add it yourself. I'm not real familar with the MySQL for windows or if the configuration format is different, but you usually just have to add to your my.cnf

wait_timeout = 90
connect_timeout = 10

Then modify the times accordingly

[pein87]

try using your mysql import from the console type IMPORT FILE INTO yourtable c:\your\files\location\and\extension.sqli; in the comand console to import the sql.

or try this one

mysql -u username -p database /path/to/file/textfile.sql

that way your database does all the work

[DirtyBirdNJ]

Thanks for the suggestion pein87.

I tried what you suggested, and that's how I got to my execution time-limit hurdle.

FRDS, thanks very much. Do you know if those lines have to go anywhere specific in the my.ini/cnf file?

Can I just put them as the last two lines of the file, and then delete them and restart the MySQL service again after I've done the import?