How To Duplicate Entire MySQL Database From Command Line is a frequently asked question as we often need to backup the database or restore the database quickly. In this short guide on How To Duplicate Entire MySQL Database From Command Line, we are giving example with Rackspace Cloud Server. It will definitely work with any other standard webhost.
How To Duplicate Entire MySQL Database From Command Line : What Can Be done ?
In this guide How To Duplicate Entire MySQL Database From Command Line, we will not only show the commands to backup or duplicate database of any web software or blogging software like WordPress, but also to pipe the output into another mysql command to import it into a different database or to and from localhost to server.
The major advantage is – there is no practical limit of import or export size of database, where as in PHPMyAdmin, there is a limit.
How To Duplicate Entire MySQL Database From Command Line
If you have used the classic way to Install WordPress on Rackspace Cloud Server or any server (there is also video guide for Installing WordPress on Rackspace Cloud Server in this way) or have ssh access with any control panel, then you can use these methods.
Simple way :
First, ssh to your server and login to MySQL. Create a database where you will duplicate it, take the name of the duplicate database is database2 :
CREATE DATABASE <span style="color: #ff0000;">database2</span>;
Run this after changing with real values, databasename is the original database :
mysqldump -u admin -p <span style="color: #008000;">databasename</span> | mysql -u backup -p password database2;
Import it into a different database (mysqldump and pipe the output)
Again, create a database. This is another method, do not run all these on the same database !
mysqldump --add-drop-table -u databasename -p databasename | mysql -u databasename -p database2
mysqldump command is used to create textfile “dumps” of databases managed by MySQL :
mysqldump database_name > database_name.sql
Actually this is the principle :
mysqldump --databases database_1 database_2 > two_databases.sql
mysql database_2 < database_1.sql
Copy to localhost / local computer :
mysqldump originaldatabasename | mysql nameddatabasewherewillbecopied
Copy to Server via SSH :
mysqldump originaldatabase | ssh hostname "mysql seconddtabase"