Copying database from local system to a remote system

[ChPravin]

Hello All,

I have been trying to copy a database from a local system to remote system using the mysqldump command.However, I am not able to do so.I just wanted to know syntactically where am I going wrong?

Here are the commands I have tried:
mysqldump database-name | ssh [e-mail] mysql new-database-name

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
dump database-name|mysql -h mysql.cs.abc.edu new-database-name' at line 1

mysql> mysqldump database-name |mysql -h mysql.cs.abc.edu new- database-name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
dump database-name|ssh [e-mail] new-database name' at line 1

I am using MySQL server 5.1.40.I would be grateful if someone can help me resolve this issue asap.

[maba001]

Hello All,

...

Here are the commands I have tried:
mysqldump database-name | ssh [e-mail] mysql new-database-name

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
dump database-name|mysql -h mysql.cs.abc.edu new-database-name' at line 1
...

I am using MySQL server 5.1.40.I would be grateful if someone can help me resolve this issue asap.

Hello,

its a nice approach to try to do everything in one shot but it makes it a bit hard to spot the problem. First thing: it seems to be going wrong on the side of the receiving machine. This points to the mysql command where you pipe your dump output to. I would check whether executing mysql on its own gives you access to the DB on the receiving server.

Could be you have to do mysql -u username -p password dbname to get access with creator privileges. So that's one piece that could be checked. Once you are in try a "CREATE TABLE ..." to check whether you have sufficient privileges.

If this is all working, the command should work properly. If not, I would do it in steps.

mysqldump yourDBname > transferfile.sql

Then scp or sftp the file over to the new machine.

Start mysql interactively and source the file.
This would probably give you some more output regarding the error.

[ChPravin]

Thanks for your help.