CODEWITHSUNDEEP
mysqldatabase-backups
Ankur
Ankur

Reputation: 51100

Copying a mysql database from localhost to remote server using mysqldump.exe

I want to copy a mysql database from my local computer to a remote server.

I am trying to use the mysql dump command. All the examples on the internet suggest doing something like

The initial mysql> is just the prompt I get after logging in.

mysql> mysqldump -u user -p pass myDBName | NewDBName.out;

But when I do this I get You have an error in your SQL syntax; check the manual that corresponds ... to use near 'mysqldump -u user -p pass myDBName | NewDBName.out'

Since I have already logged in do I need to use -u and -p? Not doing so gives me the same error. Can you see what is wrong?

Upvotes: 1

Views: 4536

Answers (3)

xamox
xamox

Reputation: 2779

I have had to dump large sets of data recently. From what I have found on a 200Mb database with 10,000+ records in many of the tables is the following. I used the linux 'time' command to get actual time.

12 minutes using: mysqldump -u user -p pass myDBName > db-backups.sql

7 minutes to clone the database: mysqldump -u user -p pass myDBName | mysql -u user -p pass cloneDBName

And in less than a second: mysqlhotcopy -u user -p pass myDBName cloneDBName

The last one blew my mind, but you have to be logged in locally where the database server resides. Personally I think this is much faster than remotely doing a dump, the you can compress the .sql file and transfer it manually.

Upvotes: 2

Alex Jasmin
Alex Jasmin

Reputation: 39496

mysqldump is not an SQL statement that you execute inside a mysql session but a distinct binary that should be started from your OS shell.

The are a few ways to use this. One of them is to pipe the output of mysqldump to another MySQL instance:

echo CREATE DATABASE remote_db | mysql -h remote_host -u remote_user -premote_password
mysqldump -h source_host -u root -ppassword source_db | mysql -h remote_host -u remote_user -premote_password -D remote_db

Upvotes: 2

Eric J.
Eric J.

Reputation: 150108

In addition to what Alexandre said, you probably don't want to pipe (|) output to NewDBName.out, but rather redirect it there (>).

So from the Windows/Unix command line:

mysqldump -u user -p pass myDBName > NewDBName.out

Note that if you have large binary fields (e.g. BLOBS) in some columns you may need to set an additional option (I think it was --hex-blob, but there might have been another option too). If that applies to you, add a comment and I'll research the setting.

Upvotes: 2

Related Questions