CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Daredevil
Daredevil

Reputation: 1792

MySQL Workbench - How to clone a database on the same server with different name?

I am using MYSQL Workbench and I want to clone a database on the same server with different name. It should duplicate the all the tables structure and data into the new database.

I know the usual way is probably using data export to generate a sql script of the database and then run the script on the new database but I encounter some issues with it.

Anyway, is there any better way or easier way to do so?

Upvotes: 36

Views: 61275

Answers (3)

Miłosz Bodzek
Miłosz Bodzek

Reputation: 1369

You can use migration wizard from MySQL Workbench. Just choose the same local connection in both source and target selection, then change schema name on manual editing step. If nothing appears on manual editing step click next and the source and targets will appear. Click slowly on the source database name and edit to the correct name. Go thorough to the end and voilà - you have two identical databases with different names. Note you must have created the target database already and granted permissions to it for the MySQL Workbench user.

Upvotes: 62

Andreas
Andreas

Reputation: 444

I tried to do it in MySQL Workbench 8.0. However I kept receiving an error regarding column-statics. The main idea is to use mysqldump.exe, located in the installation directory of MySQL Workbench, to export the data. So, supposing a Windows oriented platform:

  1. Open Powershell, navigate to mysqldump.exe directory. In my case the command is:

                    cd C:\Program Files\MySQL\MySQL Workbench 8.0 CE

  2. Export database by executing mysqldump providing the right arguments: 

    ./mysqldump.exe --host=[hostServerIP] --protocol=tcp --user=[nameOfUser] --password=[yourPassword] --dump-date=FALSE --disable-keys=FALSE --port=[portOfMysqlServer] --default-character-set=utf8 --skip-triggers --column-statistics=0 "[databaseName]"

  3. Without changing directory, import the exported file (.sql) by using the following command in Powershell:

    Get-Content "[pathToExportedDataFile]" | ./mysql.exe --user=[nameOfUser] --password=[yourPassword] --port=[portOfMysqlServer] --host=[hostServerIP]  --database=[nameOfNewDatabase] --binary-mode=1

You can check in the documentation here for more information regarding the mysqldump options.

Please note the following:

  • Do not forget to replace the values in [] with your own values and remove the []. Do not remove the quotes("") where the are present.
  • Do not switch Powershell for cmd or something like git-bash, since the above will not work.
  • As far as step 3 is concerned, I created the new database from MySQL Workbench and then ran the powershell command.

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

  • List item First, create a new database using CREATE DATABASE statement.
  • Second, export all the database objects and data of the database from which you want to copy using mysqldump tool.
  • Third, import the SQL dump file into the new database.

Upvotes: 1

Related Questions