The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 01:05:57 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Easiest, Quickest Way To Restore A MySQL Backup  (Read 2245 times)
webinfoguy25
Journeyman
***
Offline Offline

Posts: 56


View Profile
« on: October 04, 2009, 01:25:31 PM »

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  Need Help

Thanks,
Logged
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #1 on: October 04, 2009, 03:15:31 PM »

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?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
webinfoguy25
Journeyman
***
Offline Offline

Posts: 56


View Profile
« Reply #2 on: October 04, 2009, 05:37:43 PM »

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. 
Logged
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: October 04, 2009, 07:27:49 PM »

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
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
webinfoguy25
Journeyman
***
Offline Offline

Posts: 56


View Profile
« Reply #4 on: October 09, 2009, 06:00:10 PM »

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. "
Logged
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #5 on: October 09, 2009, 06:10:50 PM »

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...
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
easternfade
Rookie
**
Offline Offline

Posts: 10


View Profile
« Reply #6 on: October 15, 2009, 08:32:10 PM »

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
Logged

No links in signatures please
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!