HUGE mysql import

ChicoMendez

hi

i have to import a huge database onto another server... however the size of it is huge so i cant seem to be able to import it anyhow.
the problem really is only one table that measures an sql file of 30mb+

how do i import this into the new db ??

tnx
chico

misterq

Back up the table using phpmyadmin on the old server.

FTP the backup file to the new server.

ssh in, run mysql from command line and run the backup file

louie

A similar tread that solved the problem here:

http://www.irishwebmasterforum.com/forum-management/1217-restoring-database.html#post9058

Rollo Tamasi

It doesn't need to be that complicated Misterq.

Chico, check out bigdump, it is exactly what you're looking for. But be carefull when moving about databases. You need to make sure you're using the right charsets.

misterq

I didn't realize my solution was complicated!

ChicoMendez

Rollo Tamasi wrote:

It doesn't need to be that complicated Misterq.

Chico, check out bigdump, it is exactly what you're looking for. But be carefull when moving about databases. You need to make sure you're using the right charsets.

Rollo,

ive tryed this but it dont work... would seem bigdump only works for files under 2mb


tnx

ChicoMendez

im trying bigdump again and im getting an error :

Error at the line 44: ) TYPE=MyISAM AUTO_INCREMENT=61060 ;

Query: CREATE TABLE `smf_messages` (
`ID_MSG` int(10) unsigned NOT NULL auto_increment,
`ID_TOPIC` mediumint(8) unsigned NOT NULL default ''0'',
`ID_BOARD` smallint(5) unsigned NOT NULL default ''0'',
`posterTime` int(10) unsigned NOT NULL default ''0'',
`ID_MEMBER` mediumint(8) unsigned NOT NULL default ''0'',
`ID_MSG_MODIFIED` int(10) unsigned NOT NULL default ''0'',
`subject` tinytext NOT NULL,
`posterName` tinytext NOT NULL,
`posterEmail` tinytext NOT NULL,
`posterIP` tinytext NOT NULL,
`smileysEnabled` tinyint(4) NOT NULL default ''1'',
`modifiedTime` int(10) unsigned NOT NULL default ''0'',
`modifiedName` tinytext NOT NULL,
`body` text NOT NULL,
`icon` varchar(16) NOT NULL default ''xx'',
PRIMARY KEY (`ID_MSG`),
UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_MSG`),
UNIQUE KEY `topic` (`ID_TOPIC`,`ID_MSG`),
UNIQUE KEY `ID_BOARD` (`ID_BOARD`,`ID_MSG`),
KEY `ID_TOPIC` (`ID_TOPIC`),
KEY `participation` (`ID_MEMBER`,`ID_TOPIC`),
KEY `showPosts` (`ID_MEMBER`,`ID_BOARD`),
KEY `ipIndex` (`posterIP`(15),`ID_TOPIC`)
) TYPE=MyISAM AUTO_INCREMENT=61060 ;

MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0'', `ID_BOARD` smallint(5) unsigned NOT NULL default ''0'', `posterTime` in' at line 3





i can see on the new new db (MySQL 4.1.20 ):
MySQL charset: UTF-8 Unicode (utf8)

however on old db i cant see a charset (MySQL 3.23.58 )

can anyone work out why im getting the above error ??

tnx

Rollo Tamasi

It's really hard to know what's going on based on the info you have given. I'm no expert on it, I have used it maybe twice but if you wish I can give it a go for you?

PM me if you want me to take a look at it. I'm a trustworthy guy.

flywheel

assuming original database a MySQL database based on your last post...

... so Vbulletin have a nice step by step which goes through the main mysql commands on moving a database (and VB databases can be massive, i.e. way bigger than huge but best not to get into db size envy ) from one server to another, so should help you out. Goes thru backup, transfer and restore...

http://www.vbulletin.com/docs/html/moving_servers

... if it wasn't a MySQL databse then you'd have to use your original database's tools to export a 'MySQL friendly' formatted file, and follow the import instructions...