10

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.

Derfder
  • 3,204
  • 11
  • 50
  • 85
  • http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database – Nanne Feb 27 '13 at 11:22
  • This is answered here: http://stackoverflow.com/questions/1887964/duplicate-entire-mysql-database?rq=1 – ojs Feb 27 '13 at 11:23
  • I think this solution is for command line. I need to use that within phpmyadmin SQL query window using SQL statemens. – Derfder Feb 27 '13 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. – Amedee Van Gasse Feb 22 '21 at 10:45

4 Answers4

19

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
Jason
  • 11,709
  • 9
  • 66
  • 82
Freddie Fabregas
  • 1,162
  • 1
  • 7
  • 17
  • Could you show me how it will loks like if I have user called root and no password on my localhost? – Derfder Feb 27 '13 at 11:24
  • Will it work in SQL query window in phpmyadmin or only on a commandline? – Derfder Feb 27 '13 at 11:25
  • yes, I am on windows. Is command line the only possibility? What about SQL query commands within phpmyadmin? Could it be done like that? – Derfder Feb 27 '13 at 11:29
  • using `cmd.exe` go to MySQL directory, go to `bin` then execute the command above. – Freddie Fabregas Feb 27 '13 at 11:31
  • Your example does not consider the duplicated name inside sql file. Right? – NaN Aug 03 '16 at 11:21
  • This answer is not related to the question asked ("...using SQL query"). – Alberto Feb 07 '18 at 15:53
  • This answer was useful _for me_, so I upvoted, but it does indeed not answer the original question which has, quite frankly, some constraints that I do not agree with. – Amedee Van Gasse Feb 22 '21 at 10:30
  • This can be done in two commands, by first creating the new database, and then piping the output of `mysqldump` into the stdin of `mysql`. I recommend to have a `~/.my.cnf` file, to avoid having to type the user and password. That way they aren't even in your Bash history. And your `~/.my.cnf` can be `chmod 600`, so only you can read and write it. – Amedee Van Gasse Feb 23 '21 at 23:16
  • I also recommend to use `buffer` or even `mbuffer` in between (`sudo apt install buffer` or `sudo apt install mbuffer`, read `man` pages for more info) – Amedee Van Gasse Feb 23 '21 at 23:21
10

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
Vineet1982
  • 7,730
  • 4
  • 32
  • 67
  • Thanks, I will try this solution and let you know if it works. Is this all within SQL query window in phpmyadmin? – Derfder Feb 27 '13 at 11:33
  • 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 – Vineet1982 Feb 27 '13 at 11:35
  • 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? – Derfder Feb 27 '13 at 11:43
  • .table means .table_name to copy – Vineet1982 Feb 27 '13 at 11:45
  • 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? – Derfder Feb 27 '13 at 11:50
  • create table destination_DB.table_name select * from source_db.table_name; – Vineet1982 Feb 27 '13 at 11:54
  • table name? I have 65 tables in my db? how to automate this process? – Derfder Feb 27 '13 at 11:55
  • Automate through PHP by loop – Vineet1982 Feb 27 '13 at 11:58
  • I cannot use PHP in SQL query window in phpmyadmin. Or can I? – Derfder Feb 27 '13 at 11:59
  • I basically need to exoport old db with all tables and content and import to newly created db using SQL statement in phpmyadmin sql query window. Now, we have created the new db, but the export and import part is needed to do. How to do that? – Derfder Feb 27 '13 at 12:02
  • Any idea how to achieve that in SQ query window in phpmyadmin? – Derfder Feb 27 '13 at 12:17
  • This method is very cumbersome and error prone, so I downvoted. Instead I upvoted two other answers (mysqldump, and "copy database" in phpMyAdmin), in the hope that one of them becomes the top answer. – Amedee Van Gasse Feb 22 '21 at 10:36
  • @AmedeeVanGasse I agree with you but the OP wants to through SQL phpmyadmin window without using command line and other options and thus it is only option available – Vineet1982 Feb 23 '21 at 02:45
  • @Vineet1982 then the answer of Syed Abdul Qadeer is much better. – Amedee Van Gasse Feb 23 '21 at 09:03
  • @AmedeeVanGasse question is about SQL commands in phpmyadmin and not about options available in phpmyadmin. – Vineet1982 Feb 23 '21 at 15:24
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

Philipp Maurer
  • 2,480
  • 6
  • 18
  • 25
Syed Abdul Qadeer
  • 331
  • 4
  • 7
  • 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. – Amedee Van Gasse Feb 22 '21 at 10:33
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...

Arif Hidayat
  • 11