Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Move MySQL database to a new server

Tags:

mysql

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?

like image 767
FozzieBeer Avatar asked Jun 08 '11 18:06

FozzieBeer


People also ask

Can I copy MySQL data directory to another server?

You have to shut down mysql server (which is not good, if it's a production server) You have to make sure the permission of data (mysql) directory is same as the previous one. You will have to monitor the mysql_error log while starting the second server.


2 Answers

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
like image 170
myimedia Avatar answered Sep 28 '22 03:09

myimedia


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

like image 21
lethalMango Avatar answered Sep 28 '22 03:09

lethalMango