Page 1 of 1

mysqy dump from one server into a new server?

Posted: Tue Feb 14, 2017 7:02 pm
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?

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

Posted: Tue Feb 14, 2017 11:22 pm
by Christopher
Yes.

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

Posted: Wed Feb 15, 2017 2:36 am
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...

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

Posted: Wed Feb 15, 2017 7:17 pm
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