Copy MySQL DB From Server A to Server B

Ref mysqldump to a tar.gz Show archive.org snapshot

1. Dump the DB

The easiest and fastest way is to dump the remote source DB directly into the target server in a file.

In Server B:

Copy[kiat@emgsdev ~]$ ssh -C kiat@{Server A} -p{optional port number} "mysqldump -u{user name to login to remote DB} -p{optional password} --databases {db name} | gzip -c" > {db name}.sql.gz

Replace text { ... }, without the curly brackets.

2. Import the DB

In Server B, create the DB {db name} if it doesn't exists. If it exists, drop all the tables in it, which can be easily done in PHPMyAdmin.

Now we can import the dump file into the DB in Server B:

Copy[kiat@emgsdev ~]$ zcat {db name}.sql.gz | mysql -uroot -p{optional password} {db name}

The above command first unzip the dump file and then populate the DB.