Duplicate mysql database from mysql?

Question

This is probably long, but .. is it possible to duplicate the entire database layout (tables, views, procedures and all) with a query or multiple queries in a stored procedure?

Basically looking for the same functionality as if I used mysqldump, like this

# mysqldump -u root -ppassword --no-data --routines dbname > file //create database copyofdbname # mysql -u root -ppassword copyofdbname < file 

Is it possible to do this in mysql procedure without any external tools?

I think I could make tables using "show tables" and then iterate over the results to get the "create table" statutes for each table and forward them to a new database. This is just an assumption, although I don’t know how I will copy stored procedures from the database in this way.

Answer 1

Basically no, MYSql does not have a duplicate database. Some external tools may, but I don’t know anything. I did this process using php, but I'm sure it can be done using stored procedures or any other mid-level application. Here are the steps that I followed at a high level. I assume that you know the details of how to take each step.

• Create a new DB
• To request all the tables of this db, I had access to information_schema, so I just made a choice.
• Iterate over tables.
  • run something like this CREATE TABLE dbnew.tableA LIKE dbold.tableA It will completely duplicate the structure of the table.
  • Paste Select db / table * from your old db / table in your new selection
• Have lunch or, depending on the size of your database, watch a movie or repeat the “IT crowd”.
• enjoy the copied database.

Correction: In my research that did this before, I found that there is a version of mySql that has a duplicate database command, but it was a bug and was introduced in future versions. Even if you are using a version with this available command, you are still better off following these steps than using this command.

Answer 2

Copy table works for me using this procedure (new db name as argument)

 BEGIN DECLARE done1 INT DEFAULT FALSE; DECLARE tablename TEXT; DECLARE cursortable CURSOR FOR ( SELECT table_name FROM information_schema.tables WHERE table_schema='sansentinel' ORDER BY table_name ASC ); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE; START TRANSACTION; # create db set @createinstance := concat("CREATE DATABASE `",INSTANCENAME,"`"); prepare createinstance from @createinstance; execute createinstance; OPEN cursortable; read_loop: LOOP FETCH cursortable INTO tablename; IF done1 THEN LEAVE read_loop; END IF; set @createtable := concat("CREATE TABLE `",INSTANCENAME,"`.`",tablename,"` LIKE `sansentinel`.`",tablename,"`"); prepare createtable from @createtable; execute createtable; END LOOP; CLOSE cursortable; COMMIT; END