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

Question

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?

Answer 1

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

Answer 2

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.