How can I move a MySQL database from one server to another?
My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance,
mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword You can do all databases with
mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver The only problem is when the database is too big and the pipe collapses. In that case, you can do table by table or any of the other methods mentioned below.
=======================
I recently moved a 30GB database with the following stragegy:
Old Server
- Stop mysql server
- Copy contents of datadir to another location on disk (
~/mysqldata/*) - Start mysql server again (downtime was 10-15 minutes)
- compress the data (
tar -czvf mysqldata.tar.gz ~/mysqldata) - copy the compressed file to new server
New Server
- install mysql (don't start)
- unzip compressed file (
tar -xzvf mysqldata.tar.gz) - move contents of mysqldata to the datadir
- Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (mysql will generate these)
- Start mysql
=======================
You don't even need mysqldump if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
- Stop the database (or lock it)
- Go to the directory where the mysql data files are.
- Transfer over the folder (and its contents) over to the new server's mysql data directory
- Start back up the database
- On the new server, issue a 'create database' command.'
- Re-create the users & grant permissions.
I can't remember if mysqldump handles users and permissions, or just the data ... but even if it does, this is way faster than doing a dump & running it. I'd only use that if I needed to dump a mysql database to then re-insert into some other RDBMS, if I needed to change storage options (innodb vs. myisam), or maybe if I was changing major versins of mysql (but I think I've done this between 4 & 5, though)
=======================
=======================
REF:
https://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another
