UBB.Developers Forums General Discussion Suggestions and Feedback Moving Database to a new host, HELP!

OK, I will be the first to admit I am not Telnet or SSH savvy...so I rely on my phpMyAdmin to adminstrate my databases. I am trying to move a database that is about 17MB big, and the server keeps timing out when I try to DUMP. Here is the setup.

w3t_AddressBook Browse Select Insert Properties Drop Empty 501 16.7 KB
w3t_Banned Browse Select Insert Properties Drop Empty 0 1.2 KB
w3t_Boards Browse Select Insert Properties Drop Empty 16 8.4 KB
w3t_Category Browse Select Insert Properties Drop Empty 4 3.5 KB
w3t_Favorites Browse Select Insert Properties Drop Empty 12 7.5 KB
w3t_Groups Browse Select Insert Properties Drop Empty 14 2.3 KB
w3t_Last Browse Select Insert Properties Drop Empty 1,754 90.4 KB
w3t_Messages Browse Select Insert Properties Drop Empty 339 1.0 MB
w3t_ModNotify Browse Select Insert Properties Drop Empty 1 2.0 KB
w3t_Moderators Browse Select Insert Properties Drop Empty 24 2.6 KB
w3t_Online Browse Select Insert Properties Drop Empty 11 7.8 KB
w3t_PollData Browse Select Insert Properties Drop Empty 619 322.5 KB
w3t_Polls Browse Select Insert Properties Drop Empty 102 71.7 KB
w3t_Posts Browse Select Insert Properties Drop Empty 14,669 15.2 MB
w3t_Ratings Browse Select Insert Properties Drop Empty 284 18.1 KB
w3t_Subscribe Browse Select Insert Properties Drop Empty 24 2.9 KB
w3t_Users Browse Select Insert Properties Drop Empty 257 372.2 KB

Not too big really...

I have successfully moved over ALL tables but my w3t_Posts, which is the 15.2MB line above. Each time I try to use phpMyAdmin to DUMP just the table data - it times out. It will not send either. I have already pared this w3t_Posts table down from 34,000+ posts to 14,669 above...and it still will not allow me to grab it.

Any help would be appreciated. Again, if you tell me to use SSH - remember I am a NEWB to commands there...and have no idea how to gzip and move a database.

Thanks for any suggestions.

Medar,

telnet/ssh to your host

type: mysqldump -u<dbuser> -p <databasename> > dumpfilename.dmp
then enter your dbpassword at the prompt
when that's done, type gzip dumpfilename.dmp
then you can ftp that file (in binary mode) to your new host... telnet/ssh there and:
type gunzip dumpfilename.dmp.gz
mysql -u<dbuser> -p <databasename> < dumpfilename.dmp
enter your dbpassword at the prompt.

this will load the entire database (all tables and data...)

I do this with my site all the time (120+MB) and it runs no problem...

If you have any other questions, feel free to ask!

To save some confusion, as I know someone will try it..
[] type: mysqldump -u *dbuser* -p *databasename* > dumpfilename.dmp
then enter your dbpassword at the prompt
when that's done, type gzip dumpfilename.dmp
then you can ftp that file (in binary mode) to your new host... telnet/ssh there and:
type gunzip dumpfilename.dmp.gz
mysql -u *dbuser* -p *databasename* < dumpfilename.dmp
enter your dbpassword at the prompt.

---

I reformatted the above -- anything enclosed in *'s should be replaced with username/database/whatever, but don't include the *'s.

I figured since the command does contain a triangle brace, it'd be an idea not to display examples containing triangle braces that are only there to indicate where to insert info.

Hope I'm not confusing anyone.

thanks for cleaning that up, Rob... I was on a conference call and not thinking about my answer as hard as I should have....

Thanks! That worked like a charm...and believe me, I saved this post!

One problem with moving a w3t db to a new host is that all the polls break because the poll form has an absolute url to the old site. Inter-forum links to other posts also break if they used a full url, which they always seem to.

Even this site has broken polls...

The best way to fix this bug (before it bites you) is to change the pollmarkup code to use a relative link in the form tag. Otherwise you're stuck doing a perilous replace on the posts table, or scrutinizing posts dated before the move for old stinky urls to replace (with a shiny new relative url, don't get bit twice!).

I've been working on a script to update absolute urls and such in posts but it isn't quite finished. I need it because the poll button and some urls will become broken when I upgrade from the perl version to php. I've tried to do it as general as possible so that it is possible to fix different types of things. I won't be able to finish this until I've got my server working though, so it will be a week or so.