11

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.

Improve this question
4
  • stackoverflow.com/questions/25794/mysql-copy-duplicate-database Commented Feb 27, 2013 at 11:22
  • This is answered here: stackoverflow.com/questions/1887964/… Commented Feb 27, 2013 at 11:23
  • I think this solution is for command line. I need to use that within phpmyadmin SQL query window using SQL statemens. Commented Feb 27, 2013 at 11:27
  • The restriction "It need to be done from SQL query window in phpmyadmin and not from a shell command line." is, quite frankly, not productive. Please explain why you insist on the most complicated method. Commented Feb 22, 2021 at 10:45

4 Answers 4

Reset to default
20

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
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Could you show me how it will loks like if I have user called root and no password on my localhost?
Will it work in SQL query window in phpmyadmin or only on a commandline?
yes, I am on windows. Is command line the only possibility? What about SQL query commands within phpmyadmin? Could it be done like that?
using cmd.exe go to MySQL directory, go to bin then execute the command above.
Your example does not consider the duplicated name inside sql file. Right?
|
12

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
Improve this answer

15 Comments

Thanks, I will try this solution and let you know if it works. Is this all within SQL query window in phpmyadmin?
yes it is all the sql window and if you know function you can also make that using function but i don't like sql user function
I have a problem with this part: show 2012_myolddatabase.tables; create table 2013_mynewdatabase.table select * from 2012_myolddatabase.table; It shows something liek that 2013_mynewdatabase.table is worng statement or something. Any advice how to fix it? I guess that there is a problem with "chaining" is this possible in SQL?
.table means .table_name to copy
ok now it looks like this: CREATE DATABASE 2013_new_db CHARACTER SET utf8 COLLATE utf8_general_ci; SHOW 2012_old_db; CREATE TABLE 2013_new_db SELECT * FROM 2012_old_db; and it throws error: "SQL error (1064) bla bla check the right syntax to use near 2012_old_db at line 1" Any idea what is wrong?
|
7

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

Improve this answer

1 Comment

This question comes very close to what the OP asks (using phpMyAdmin) but it does not use the SQL query window of phpMyAdmin. Nonetheless it is a very useful answer so I upvoted, and I think that the question is bad by adding a constraint that makes a solution more complicated than needed.
0
  1. export your chosen database using phpmyadmin (export.sql)
  2. import it using terminal/cmd: mysql -u username -p databasename < export.sql

and get a cup of coffee...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.