• support@answerspoint.com

How to copy mysql database from one server to other server using command prompt

538

what is the process of copy or move , backup , export mysql database from one server to another remote server .

How to copy mysql database from one server to other remote  server using command prompt

2Answer


0

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 2 years ago
  • G John

0

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 2 years ago
  • G John

Your Answer

    Facebook Share