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?
-
are these built in commands? I'm brand new to this.FozzieBeer– FozzieBeer2011-06-08 18:18:15 +00:00Commented Jun 8, 2011 at 18:18
-
If you have any kind of control panel like CPanel, Plesk, etc., on the servers you are using, then chances are that you have access to phpMyAdmin which has export and import features.Kevin Nelson– Kevin Nelson2011-06-08 18:21:44 +00:00Commented Jun 8, 2011 at 18:21
-
@Kevin Nelson thanks, we only have shell accessFozzieBeer– FozzieBeer2011-06-08 18:31:33 +00:00Commented Jun 8, 2011 at 18:31
Add a comment
|
3 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
2 Comments
Clain Dsilva
This worked like a charm for me, copied all databases real quickly to my new system!!
Equidamoid
How is adding "user@" correlated to use of ssh keys? Just curious.
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
4 Comments
lethalMango
have added the syntax above for you - I shall double check that now
lethalMango
This website should help you do it step by step :) devshed.com/c/a/MySQL/…
Thomas Weller
This is only a half answer. Where's the import part?
eerrzz
I moved database this method and events gone. Leave this note here to help someone before it's too late.
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.
6 Comments
Wrikken
I you do this, there are a lot of different little things that can go wrong, especially if there are InnoDB tables. I'd suggest using Percona's xtrabackup for a task like that.
FozzieBeer
Sadly the architecture is different, though I'm sure others will appreciate this advice.
bhamby
@Wrikken Do you know of any documentation on *what might go wrong using this method? I believe you (there are lots of little things that could go wrong), but I was wondering if there was a definitive list on why not to do it like this.
Wrikken
Sadly I'm not aware of a definitive list, but innodb-logs yet to play out is a biggie for instance, afaik percona's solution is just scripts, so you could delve around to see what kinds of problems they are expecting & preventing.
bhamby
@Wrikken If the major concern is InnoDB logs not being played out, then you can have them flushed to disk on shutdown. I updated the answer to include how you could do that.
|