CODEWITHSUNDEEP
mysql
Anton
Anton

Reputation: 105

MySQL: Create database like?

In MySQL I want to clone certain databases. Is there such a thing as a 

CREATE DATABASE LIKE <template database here>

command?
I know, there is

CREATE TABLE LIKE

Upvotes: 10

Views: 26258

Answers (3)

Angelin Nadar
Angelin Nadar

Reputation: 9300

  1. I will prefer taking backup and restoring
  2. If it's just for cloning, I will go for MySQLDump

This is just one another way of doing:

mysql> create database new_dbname;

mysql> select concat('create table
new_dbname.',TABLE_NAME,' like wings2020.',TABLE_NAME,'; insert into
new_dbname.',TABLE_NAME,' select * from ol_dbname.',TABLE_NAME,';')
from information_schema.tables where table_schema = 'old_dbname'  into
outfile '/tmp/a.txt'; 

mysql> source /tmp/a.txt;

Upvotes: 1

rgenito
rgenito

Reputation: 1781

If you want to clone a database, my_database for example, you'll want to run a mysql query and then run a command in the linux terminal:

mysql> CREATE DATABASE my_database_copy;

linux terminal (on the machine with the database): mysqldump -u username -p my_database | mysql -u username -p my_database_copy

From there you'll likely get 2 "Enter password:" prompts....just input your password, press enter, and wait :)

Upvotes: 4

Devart
Devart

Reputation: 121912

There is no such command.

But you can create a backup (SQL file) of your database, and then restore all objects in the new database.

Also, you can use GUI tools in dbForge Studio for MySQL (free express edition) - Backup or restore a database. It will help you quickly recreate database and its contents.

Upvotes: 2

Related Questions