Can anyone please advice me step by step how to backup and restore mysql database from one host to another host in a virtual hosting? ie transfering all existing mysql database to another host. I don't have root access. What should I do? Please help?

Thank you.

If you have shell access it's pretty easy:

mysqldump -u USER-ID -p DATABASE-NAME > DUMP-FILE

Where:

USER-ID is your mysql user-id
DATBASE-NAME is your mysql database
DUMP-FILE is the dump (backup) file you want to create

This will backup all the table structures as well as create insert statements for all your data. Then ftp DUMP-FILE to the new host.

On the new host you run the following:

mysql -u USER-ID -p DATABASE-NAME < DUMP-FILE

This will reload the data.

You can do the same thing with phpMyAdmin.

if you are moving from one server to another then beware of differences in mysqldump versions - you may find that cat is the only way to re-populate the new database

cat backup.sql | mysql -u root -p new_database

Where backup.sql is the backup file and new_database is obviuosly the name of the new database.

Than you ffeingol & Bluebox

ffeingol : I don't have shell access so I cannot do that.

But I manage to get it done. My old server allows me to dump the database onto the screen (Bunch of CREATE tables and INSERT statements) then I copied and pasted it onto a txt file. Through phpMyAdmin, bang!!! it goes, everything was there.
I manage to get it right the second time, because it has errors when translating ' QuotationMark in the text fields (eg. int'l ).
So I deleted those QuotationMark in the text fields, then everything was transfered.

Many thanks for all the information provided, it definately give me the ideal to accomplish it.

Thanks

I've done all that is said about, managed to create a mysql dump but when I put in into phpmyadmin it doesn't work (I had a look at the dump and it had a very big table, any ideas?

hoot

What errors did phpMyAdmin report when you tried to import the SQL dump?

Best regards,
Frank Rietta

It just seems to go on for ever without answering. How long should it take? its a 500kb file and I've got a good 60Kb/s connection !
I've already given it about 5 minutes with no success ...

hoot

Dunno what happened but seems to be ok now :)

I think you have to create a database first in your phpMyAdmin then click on your database and go to bottom where you can type in your file name or Browse the file in your local hard disk and then click go.

->>> Or Location of the textfile :
filename.txt Browse

Go

If it stops somewhere during translation or importing, check to see where it stops, maybe some statements or character it does't recognise.

Originally posted by brandon
My old server allows me to dump the database onto the screen (Bunch of CREATE tables and INSERT statements) then I copied and pasted it onto a txt file.

Ooh ... that's not so ideal ... One of my clients has about 550MB of mysql db ... wonder if he needs to do that to backup his mysql :eek:

cheers,
:beer:

Ooh ... that's not so ideal ... One of my clients has about 550MB of mysql db ... wonder if he needs to do that to backup his mysql


Yes, you are definitely right. The control panel that I was using has only that function. Luckily, my files is not big.