Need Urgent help with SQL DB import

[RockMTP]

I need to copy everything from my old db to a new db as I get loads of db errors (over 8700 so far)

I have been working on this for nearly two days and its been driving me up the wall. My server company haven't really been much help and this is the first time i've ever had to do anything like this.

I've been getting database errors on my forum. For some reason the db would also boot all the users off, them show a database error screen on the index page, then 5 mins later the forum come back.

My server company told me to create a new database and import the old one.

I did this, changed the config.php file and the forum index page showed database error.
I changed the config file back to the old details and the forum came back.

VB looked at this also and changed the config file, got the same errors and told me:

I adjusted the config file quickly to see what the error was, I've now changed it back.

It seems the connection is fine, but the database is either missing tables or is empty.

Make sure the database backup is restored correctly into the new database before adjusting config.php

Now I can see the difference between the old db and the new db, there are 135 tables in the old and 9 in the new but how do I copy this db to the new one?

I've been talking to my server company for the last 2 days and they can't seem to help me.

The last thing they told me was:

You are getting these error as two database usernames are different. In our system database name and db username are same for a database.
Please check the .sql/backup file and correct the statement in the username field and then try to restore.

Or the best way you can do, take the babk (not needed if you have already), then delete the old database, after sometime create the same database (with the same name) and then import so that you will not get the errors as you are getting now.

What does that mean?

I'm losing the will to live!

My members are getting seriously fed up as well now!

I'm using a shared server with mysql V5,

When i backed up the old db it was 493kb. I exported the db from server to my desktop direct from sql and it was 15mb.

I saved everything on the root, as far as i can see i did everything right!

Please pleasae please help as I don't know what else to do!

[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

[RockMTP]

Thanks for that.

I don't have SSH access and I just tried mysqldumper and it didn't work. I am looking to see if I can use the other methods.

The problem is my db is 8.7gig, 8.6 gig of that is in the attachment table! Because I'm on a shared server access is restricted.

Are there any other tips you can offer me in the mean time as my forum is suffering.

Thanks

[harmor19]

Before I discovered MySQLDumper I used to split my SQL files into smaller files. I found a program that'll automatically split files. If you do use the automatic file splitter you'll need to go and edit each individual file to make sure the queries are complete.

[RockMTP]

Thanks harmor. The problem I have is that I've never done anything like this before and i'm a bit concerned that i'll mess something up. Ideally if I could do it in one go or look at a simple walk-through i'd probably do ok. I just downloaded bigdump and phpmyadmin but I didn't know what i was doing or where to put things so stopped playing with that!

[Marco van Herwaarden]

You might want to consider moving your attachments to the filesystem as this would reduce you database size. You would however need to download all attachments using FTP to save them then.