How to import big SQL?
 

I'm changing my vbulletin3.07 to a new sharing hosting plan. So I backed up my database in using CPANEL of the old sever. Now je just don't know how to import this 300M sql file to my new sharing sever? I've tried some importing tool but always failed. Any successful experiences? :disappointed:

Split it up.

after each statement is a semicolon eg.
[sql]CREATE TABLE `faq` (
`faqname` varchar(250) binary NOT NULL default '',
`faqparent` varchar(50) NOT NULL default '',
`displayorder` smallint(5) unsigned NOT NULL default '0',
`volatile` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`faqname`),
KEY `faqparent` (`faqparent`)
) TYPE=MyISAM;[/sql]
[sql]INSERT INTO `faq` VALUES (0x76625f666171, 'faqroot', 100, 1);[/sql]

Open your sql file in an edior (use wordpard instead of notepad because wordpad seperates the lines better or use an editor your like.)


cut about 600 lines and place it into another file named vb_1.sql then go cut another 600 lines and paste it into a file named vb_2.sql and so on... (make sure you end on a semiolon.)

Now upload the sql files in order.

I hope that helps.

OR you can just use SSH and do it in about 30 seconds.

what command line to use in SSH. I have tried SSH before but the character not correct. For example, the forum is utf-8 unicode but after the restore the character wasn't utf8. How to fix that?

I always use this nice little tool to upload a big database.
Takes you 1 minute to config and off you go!

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

What the largest size you have uploaded with it and did you do it as a gz or sql file? ;)

Any special recommendations for the settings? ;)

My vbulletin database is 120mb (uncompressed), but bigger should be no problem at all.
I uploaded my database as gz file which was only 26mb or so.

Only recomendation:

* make sure you import in the correct database. ;)
* delete de dumbdir after the succesfull import or protect that directory using htaccess :)

Good luck.

Bellinis

<a href="http://www.vbwebmaster.com/forums/showthread.php?t=1183&highlight=backup" target="_blank">How to backup your forum with SSH and Cron</a>

Wow... I'm surprised no one has mentioned MySql Administrator. I have the same issues with my remotely hosted system, but there seems to be no limit to what you can do with this tool. It is free and from the MySql group:

http://www.mysql.com/products/tools/administrator/

Don't you have to have server access for this? Some don't have that capability and must do it from their home computers.

Telnet to your server.
Make an empty database named 'forum'.
cd to directory mysql file is in.

The following assumes:
Your mysql password is: XXXXXXXX
Your empty database is named: forum
The sql file you want to import is named: forum_bup.sql

mysql --password=XXXXXXXX forum < forum_bup.sql

Yes Telnet/SSH is the way to go. My database backup is over 200 meg and once I put the backup on the server and execute the restore command then it takes about 15 seconds or less to restore.

Yup, and to backup it's the same, basically:

mysqldump --opt -c --password=XXXXXXXX -q -v forum > forum_bup-051106am.sql
gzip -v forum_bup-051106am.sql

Then FTP the ZIP file to another server. The key is to get a copy on another drive either within your server (RAID Level 1 - Mirroring) or on another server - Main cause of catestrophic, 'no warning' total data loss is drive failure.

This has also been the best solution for me. The largest database I have imported was 1,4 gb large.

Mine only runs about 300 megabytes - So I'm a little guy.... I have attachments outside the database so that's a secondary backup issue. But - a nightly FTP offsite backup of both attachments and the main forum database can be done with a server cron script. I run freebsd and it's pretty simple.

What do you mean off-site backup?

A backup in a different location. For example, my host is a server farm in Texas. I back up my database every night to my home computer which is 'off site'. If anything happens to them or their server farm, the most I would lose would be a day of posts on my forums.

Just to thank you for the bigdump script... Really usefull ;)