How to copy tables or databases from one MySQL server to another MySQL server?

If we want to copy tables or databases from one MySQL server to another, then use the mysqldump with database name and table name.

Run the following command at the source host. This will dump the complete database into dump.txt file.

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

We can copy complete database without using a particular table name as explained above.

Now, ftp dump.txt file on another host and use the following command. Before running this command, make sure we have created database_name on the destination server.

$ mysql -u root -p database_name < dump.txt
password *****

Another way to accomplish this without using an intermediary file is to send the output of the MySQL dump directly over the network to the remote MySQL server. If we can connect to both the servers from the host where the source database resides, use the following command (Make sure we have access to both the servers).

$ mysqldump -u root -p database_name \
| mysql -h other-host.com database_name

In mysqldump, half of the command connects to the local server and writes the dump output to the pipe. The remaining half of the command connects to the remote MySQL server on the other-host.com. It reads the pipe for input and sends each statement to the other-host.com server.