next »

[webinfoguy25]

I have not been successful at trying to restore a backup of my databases when using MySQL.  I fail at doing this for Wordpress, Zen Cart, and Dolphin Social Networking sites.  My question is how can I restore these properly after I switch hosting providers?  I have checked on google, and even read a book with instructions on how to do it, and still no success. 

Please help me 

Thanks,

[nutballs]

I would assume you did a mysqldump?
if so it should be as simple as:
mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Thats not working?

[webinfoguy25]

can I do that command in putty?  I was doing it in phpadmin.  I was copying and pasting the database into a txt file and saving it. 

[nutballs]

AHHHHHHHH OK

Yea. Dont worry. I did the same exact thing, as has pretty much everyone who uses myadmin.
It times out, or something, with big datasets.

Yep, from the shell, via putty in your case, run that command. Obviously replace out the relevant bits as it pertains to you.
To create the dump, its the exact same thing but > instead of <
Create the dump:
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
Restore the dump
mysqldump -u root -p[root_password] [database_name] < dumpfilename.sql

The output will be created where you currently are, so I usually cd /tmp first, just in case I do something stupid.
dump it out, then transfer it to your new server if you are moving physically. Then restore it.

Frankly Mysqldump is slow and sucks for bigger nastier databases.
I use MK Parallel Dump http://4sp.in/2lL
and MK Parallel Restore http://4sp.in/2lM

[webinfoguy25]

uh....

"RISKS

The following section is included to inform users about the potential risks, whether known or unknown, of using this tool. The two main categories of risks are those created by the nature of the tool (e.g. read-only tools vs. read-write tools) and those created by bugs.

mk-parallel-dump is not a backup program! It is only for fast data exports. Do not rely on mk-parallel-dump for your backups. You have been warned. "

[nutballs]

Pffft wuss. Lol

I actually do two things.
I run mk every night, dump every 2 weeks.

I have a few very large innodb tables though so dump is painful slow.

This is on top of the fact that I run master-slave so if master dies, I just promote slave and get running way fast. Triple redundant system, because I have learned my lesson...

[easternfade]

If you have both hosts active, you could also do a straight copy, e.g.

mysqldump --opt -h db.example.com -pmypass -u webusr mydb_prod | mysql -h db.example2.com -pmypass2 -u webusr2 mydb_prod_v2