Moving forum and mysql to another provider

[m3power]

I am having some difficulties moving my sql from one provider to another. My current database is mysql 4.1 and the new provider is mysql 5. The database just wont import.

I have used mysqldumper to backup the database but when using mysqldumper to restore the database it crashes on the main postindex (table 80) everytime but at different points.

Unfortunately mysqldumper doesn't give any error messages it just seem to timeout.

I'm lost as to what to do next and I'm looking for any good suggestions as to how to solve the problem.

Thanks

[Marco van Herwaarden]

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/maintenance_ssh_backup

Restore:
http://www.vbulletin.com/docs/html/maintenance_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/showthread.php?t=134821&highlight=mysqlhotcopy

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

[m3power]

I have ssh access with the new provider but don't have access with the existing hostin. I used mysqldumper but that doesn't work on the restore. Perhaps I will try one of the others now.
Thanks Marco

Ok So I tried big dump and got the following error:

BigDump: Staggered MySQL Dump Importer v0.28b
Processing file: astrasriforums.sql

Starting from line: 51001

Error at the line 52156: ) ENGINE=MyISAM ;

Query: CREATE TABLE `attachment` (
`attachmentid` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`dateline` int(10) unsigned NOT NULL default '0',
`filename` varchar(100) NOT NULL default '',
`filedata` mediumblob,
`visible` smallint(5) unsigned NOT NULL default '0',
`counter` int(10) unsigned NOT NULL default '0',
`postid` int(10) unsigned NOT NULL default '0',
`filesize` int(10) unsigned NOT NULL default '0',
`thumbnail` mediumblob,
`filehash` varchar(32) NOT NULL default '',
`posthash` varchar(32) NOT NULL default '',
`thumbnail_dateline` int(10) unsigned NOT NULL default '0',
`thumbnail_filesize` int(10) unsigned NOT NULL default '0',
`extension` varchar(20) character set latin1 collate latin1_bin NOT NULL default '',
PRIMARY KEY (`attachmentid`),
KEY `posthash` (`posthash`,`userid`),
KEY `postid` (`postid`),
KEY `filesize` (`filesize`),
KEY `filehash` (`filehash`),
KEY `userid` (`userid`),
KEY `visible` (`visible`)
) ENGINE=MyISAM ;

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 'collate latin1_bin NOT NULL default '', PRIMARY KEY (`attach

Stopped on error


it's basically the same error that happens when you you use mysql when using ssh! Ideas please?

[Marco van Herwaarden]

Are you sure that the new server is running MySQL 5?

[m3power]

It's mysql 4.0. Just looked at it now! The original one is mysql 4, cant find out if it's mysql 4.0 or 4.1 though.

[Marco van Herwaarden]

Then that explains all your problems. Characterset and collation support was added in MySQL 4.1.

If possible you should really ask your host to upgrade MySQL, as 4.0 is really outdated.

If that is absolutly not possible we can give some instruction to overcome this problem.

[m3power]

Ok, they are offering me an upgrade to version 5. This shouldn't be a problem to move the current forum across to it???

I have already moved a forum across though. Will this affect my current live forum on the hosting if they upgrade from v4 to v5?

[Paul M]

It shouldn't, but then you always have backups (don't you ...... )

[m3power]

Ok......My hosting provider has said that they cannot upgrade our dedicated server to MySql 5 as they would have to upgrde all others (sounds like a cop out to me). Since signing up with 1and1 I am most dissapointed with their services to be honest!

So it looks like I am in the hands of you guys to give me some help if possible :)

[Paul M]

Ok......My hosting provider has said that they cannot upgrade our dedicated server to MySql 5 as they would have to upgrde all others (sounds like a cop out to me).

It's not a cop out, mysql is a server application, so it would affect everyone on the server your account is on.

[Marco van Herwaarden]

He say that it is a dedicated server Paul. ;)

I am not sure what the use is of a dedicated server if you don't even have control over the versions of the software installed. I would first try to put a bit of pressure on them to upgrade. This will make things a lot easier in the future.

[m3power]

Yeah I think I'm going to do that. Yes it's definately a dedicated server and we have had to sign up to a 12 month contract to it as well.

[Paul M]

He say that it is a dedicated server Paul. ;)
Oops, sorry, I missed that. :)

Yeah I think I'm going to do that. Yes it's definately a dedicated server and we have had to sign up to a 12 month contract to it as well.

If it's a dedicated server then just upgrade it yourself. We have two 1&1 dedicated root servers, we just upgrade them using yum.

[m3power]

We have ssh access to the server can I get yum on it using that? Not sure where to go with this one. Some advice needed I think.

[m3power]

I have been speaking to 1and1 today and asked them if they will upgrade to mysql 5 or cancel the account, so they offered to cancel the account! I'm on the lookout for a new provider now. Got my eye on a dedicated server from fasthosts but want to make sure I make the right decision this time.

[Eikinskjaldi]

I have been speaking to 1and1 today and asked them if they will upgrade to mysql 5 or cancel the account, so they offered to cancel the account! I'm on the lookout for a new provider now. Got my eye on a dedicated server from fasthosts but want to make sure I make the right decision this time.

If it comes with cpanel/whm then upgrading is quite literally a click of a few buttons.