How to clone MySQL database under a different name with the same name and the same tables and rows/content using SQL query

Question

I know how to clone tables e.g.:

CREATE TABLE recipes_new LIKE production.recipes; 
INSERT recipes_new 
SELECT * FROM production.recipes;

But I don't know how to clone e.g. a database_old to database_new database with all the tables and rows from database_old.

So, only the name of the database will change. Everything else stays the same.

Right now I am cloning it by exporting the database in phpmyadmin ad then creating a new database and importing it to the new database.

But I guess there must be a more efficient way of doing this task via SQL query like that one for cloning tables.

IMPORTANT! It need to be done from SQL query window in phpmyadmin and not from a shell command line.

Thanks in advance for you suggestion how to do that.

Answer 1

have you tried using MySQL Dump?

$ mysqldump yourFirstDatabase -u user -ppassword > yourDatabase.sql
$ echo "create database yourSecondDatabase" | mysql -u user -ppassword
$ mysql yourSecondDatabase -u user -ppassword < yourDatabase.sql

Answer 2

IMPORTANT! It need to be done from SQL query window in phpmyadmin and not from a shell command line.

First create a blank database:

 CREATE DATABASE `destination` DEFAULT CHARACTER SET 
    latin1 COLLATE latin1_swedish_ci;

Then use the command show tables;

 show source.tables;

and then run the command for each DB table (Optimized Create table and inserting rows) as:

 create table destination.table select * from source.table;

and other way is using like command:

  create table destination.table like source.table

and then inserting rows;

  insert into destination.table select * from source.table

Answer 3

If phpMyAdmin is available for the database, you can clone it by following these steps:

1. Select required database
2. Click on the operation tab
3. In the operation tab, go to the "copy database"-option and type your desired clone-db-name into the input field
4. Select "Structure and data" (Depends on your requirement)
5. Check the boxes, "CREATE DATABASE before copying" and "Add AUTO_INCREMENT value"
6. Click "GO"

Tested with phpMyAdmin 4.2.13.3