How to copy mysql database from one server to other server using command prompt
2Answer
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 -premoteserver
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.
- answered 8 years ago
- G John
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
- answered 8 years ago
- G John
Your Answer