In this article, I will show you how to dump a mysql database from one server to another remote server without having to export and import your SQL structure and data. We’ll dump a database directly to another empty database on another, remote server. In order to do this, you’ll need shell access to both servers and also create an empty database (with a valid login) on the remote server. Below is the command.
mysqldump -v -uxxxxx -pxxxxx database_name | ssh [email protected] mysql -uxxxxx -pxxxxx database_name
So in the command above, we first execute a mysqldump to create an SQL dump of the current database. You’ll need to replace “xxxxx” with the actual username and password of the current database. Lastly, we specify the name of the database to dump.
Immediately, we connect to the remote server via SSH and execute a mysql command which will import the SQL dump which was just created, into the remote database. Same as with the current database, you’ll need to replace “xxxxx” with the username and password and of course the name of the remote database.
You will be prompted for password once the current database has been dumped. The password is the ROOT password for connecting to the remote server via SSH.
You’ll also notice that I used the “-v” option which executes the command in verbose mode. This prevents all the SQL code from being output in your terminal. It will just show a summary of the connection established, the tables dumped and then the disconnection from the mysql server.
And that’s it! You’ve just transferred a database from one server to another.