CODEWITHSUNDEEP
mysql
FozzieBeer
FozzieBeer

Reputation: 153

Move MySQL database to a new server

What's the easiest way to move mysql schemas (tables, data, everything) from one server to another?

Is there an easy method move all this from one server running mysql to another also already running mysql?

Upvotes: 15

Views: 17252

Answers (3)

lethalMango
lethalMango

Reputation: 4491

Dump the Database either using mysqldump or if you are using PHPMyAdmin then Export the structure and data.

For mysqldump you will require the console and use the following command:

mysqldump -u <user> -p -h <host> <dbname> > /path/to/dump.sql

Then in the other server:

mysql -u <user> -p <dbname> < /path/to/dump.sql

Upvotes: 13

myimedia
myimedia

Reputation: 161

If you are using SSH keys:

$ mysqldump --all-databases -u[user] -p[pwd] | ssh [host/IP] mysql -u[user] -p[pwd]

If you are NOT using SSH keys:

$ mysqldump --all-databases -u[user] -p[pwd] | ssh user@[host/IP] mysql -u[user] -p[pwd]

WARNING: You'll want to clear your history after this to avoid anyone finding your passwords.

$ history -c

Upvotes: 16

bhamby
bhamby

Reputation: 15450

If you're moving from the same architecture to the same architecture (x86->x86, x86_64 -> x86_64), you can just rsync your MySQL datadir from one server to the other. Obviously, you should not run this while your old MySQL daemon is running.

If your databases are InnoDB-based, then you will want to make sure that your InnoDB log files have been purged and their contents merged to disk before you copy files. You can do this by setting innodb_fast_shutdown to 0 (the default is 1, which will not flush the logs to disk), which will cause the log file to be flushed on the next server shutdown. You can do this by logging on to MySQL as root, and in the MySQL shell, do:

SET GLOBAL innodb_fast_shutdown=0

Or by setting the option in your my.cnf and restarting the server to pull in the change, then shutting down to flush the log.

Do something like:

#On old server (notice the ending slash and lack thereof, it's very important)
rsync -vrplogDtH /var/mysql [email protected]:/var/mysql/
#Get your my.cnf
scp /etc/my.cnf [email protected]:/etc/my.cnf

After that you might want to run mysql_upgrade [-p your_root_password] to make sure the databases are up-to-date.

I will say it's worked for me in the (very recent) past (moving from an old server to a new one, both running FreeBSD 8.x), but YMMV depending on how many versions you were in the past.

Upvotes: 5

Related Questions