mysqy dump from one server into a new server?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

mysqy dump from one server into a new server?

Post by Vegan »

I was wondering if I have 2 server, old one and a new one, is it possible to use mysqldump and clone the old database

this way when i need to use a different server i can dump my files into the new one faster?
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: mysqy dump from one server into a new server?

Post by Christopher »

Yes.
(#10850)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: mysqy dump from one server into a new server?

Post by requinix »

Code: Select all

mysqldump --whatever --options --you --need | gzip > filename.sql.gz
Tip: you probably want options to drop and re/create tables, possibly database(s) too, and if you have large blobs of data then you might need to increase max_allowed_packet.

Transfer the file, and

Code: Select all

gunzip filename.sql.gz -c | mysql --options...
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

Re: mysqy dump from one server into a new server?

Post by Vegan »

requinix wrote:

Code: Select all

mysqldump --whatever --options --you --need | gzip > filename.sql.gz
Tip: you probably want options to drop and re/create tables, possibly database(s) too, and if you have large blobs of data then you might need to increase max_allowed_packet.

Transfer the file, and

Code: Select all

gunzip filename.sql.gz -c | mysql --options...
I have long ago learned to increase max_allowed_packet to be much larger, when using vast amounts of data it's a wise choice

as time goes by I am adding more capability to my website, limited time is the only problem

Code: Select all

sudo mysqldump -u root -p --all-databases | gzip > backup.sql.gz
while we are at it, anybody know how to data stamp the file name, so it's like backup-02-16-2017.zwl.gz

--binary-mode is the best option for restoring a dump, in case you have a complex range of tables etc
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
Post Reply