copying mysql db to another machine

amychan784 · 12-20-2013, 03:44 AM

I would like to move mysql db from existing server to another new installed server , I found the below link , but I think this method need to do export data and import for each db , could advise any method that do all db copying for one time ? thanks

http://dev.mysql.com/doc/refman/5.0/...databases.html

acid_kewpie · 12-20-2013, 03:47 AM

How hard is it to just run a few commands once for each database? Script it if you need to.

chrism01 · 12-20-2013, 03:48 AM

Try this page https://dev.mysql.com/doc/refman/5.1...ql-format.html.
you dump any/all dbs, your choice.
Gives you a backup as a side effect

and also defrags the new copies as well.

kapilbajpai88 · 12-20-2013, 05:04 AM

In Summary, it is a three step process :
a) Step One—Perform a MySQL Dump
mysqldump -u root -p --opt [database name] > [database name].sql

b) Step Two—Copy the Database
scp [database name].sql [username]@[servername]

ath/to/database/

c) Step Three—Import the Database
mysql -u root -p newdatabase < /path/to/newdatabase.sql

Most links available online will summarise the things around these only.

zhjim · 12-20-2013, 05:19 AM

For the daring rsync the content while the db is stopped to the other machine. Run checkusm over tables to see if all went well. Only works if both database servers are of the same version.

coldbeer · 12-20-2013, 05:42 AM

In addition to the above you may need to run mysql_install_db on a new installation of mysql. Also if you find your downloads exiting during import or export check out the config item max_allowed_packet in /etc/my.cnf and set it higher.