Transferring MySQL DB?

[LogenWilson]

Hi

Is it possible to transfer MySQL database from one web host to another with the help of SSH? If it's possible, what are the command lines? And how difficult it is?

[SandraJose]

You can follow the below command lines.

shell> mysqladmin -h 'other_hostname' create db_name
shell> mysqldump db_name | mysql -h 'other_hostname' db_name

[Kristine Habeck]

If you need to copy a database from a remote machine over a slow network.

shell> mysqladmin create db_name
shell> mysqldump -h 'other_hostname' --compress db_name | mysql db_name

[Rozanne]

You can also save the dump in a file. Move the file to the target system, and then upload the file into the DB there.

shell> mysqldump --quick db_name | gzip > db_name.gz

[Irene9001]

You can also try this one to copy entire mysql db

mysqldump --alldatabases | mysql -h 'other host name'

If you want to copy one db

mysqldump dbname | mysql -h 'other host name' dbname

To copy only one table

mysqldump dbname tablename | mysql -h 'other host name' dbname

Probably you'll need to use the -u and -p options if you do not have the client login set in your config file. I've assumed that you have password root--. Also you'll need to be a user that has create and drop privileges at whatever level you required to perform the copy. Additionally you'll have to enable mysql access to the DB on the other host's server machine.