Suppose if you want to copy a Table to another, this can be done in MySQL with two steps. The first one is to copy the table structure, and the second step is to fill in the data with the original table.
Copy the Table Structure
The syntax is straightforward in MYSQL.
1 | Create Table `NewTable` Like `OldTable`; |
Create Table `NewTable` Like `OldTable`;
Copy the Data to New Table
1 | Insert Into `NewTable` Select * From `OldTable`; |
Insert Into `NewTable` Select * From `OldTable`;
Duplicate Database
The above shows how to copy/duplicate one single table. So to duplicate/copy entire database, you would need the following to show all tables in a single database:
1 | Show Tables; |
Show Tables;
Then, write a script in any programming language to copy each single table. For example, the following is the PHP script that copies all tables one by one as given by ‘Show Tables’ result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | // helloacm.com function duplicate($originalDB, $newDB) { $db_check = @mysql_select_db ( $originalDB ); $getTables = @mysql_query("SHOW TABLES") or return(mysql_error()); $originalDBs = []; while($row = mysql_fetch_row( $getTables )) { $originalDBs[] = $row[0]; } @mysql_query("CREATE DATABASE `$newDB`") or return(mysql_error()); foreach( $originalDBs as $tab ) { @mysql_select_db ( $newDB ) or return(mysql_error()); @mysql_query("CREATE TABLE $tab LIKE ".$originalDB.".".$tab) or return(mysql_error()); @mysql_query("INSERT INTO $tab SELECT * FROM ".$originalDB.".".$tab) or return(mysql_error()); } return true; } |
// helloacm.com function duplicate($originalDB, $newDB) { $db_check = @mysql_select_db ( $originalDB ); $getTables = @mysql_query("SHOW TABLES") or return(mysql_error()); $originalDBs = []; while($row = mysql_fetch_row( $getTables )) { $originalDBs[] = $row[0]; } @mysql_query("CREATE DATABASE `$newDB`") or return(mysql_error()); foreach( $originalDBs as $tab ) { @mysql_select_db ( $newDB ) or return(mysql_error()); @mysql_query("CREATE TABLE $tab LIKE ".$originalDB.".".$tab) or return(mysql_error()); @mysql_query("INSERT INTO $tab SELECT * FROM ".$originalDB.".".$tab) or return(mysql_error()); } return true; }
–EOF (The Ultimate Computing & Technology Blog) —
GD Star Rating
loading...
281 wordsloading...
Last Post: MySQL server stopped due of out of memory exception on Ubuntu VPS
Next Post: How to Print Pascal Triangle in C++ (with Source Code)
Awesome. I was looking for this script, as the way it is..!!
Thanks 🙂
You are welcome
I need some guidance. I have implemented this method in my project.
I want to take backup whenever I want, which is working fine with this script. Need few clarification:
1) How can I restore the data from copy db to original db when I want?
2) Is there a way that I frequently UPDATE the copy my db via cron job rather than using CREATE DATABASE $newDB?
1. you can login to mysql console and run source db.sql
2. you can drop table if exists first then create database.
Actually I want to the system to create the backup and restore automatically. I don’t want to involve in this process to export and import the sql file. That’s why I am implementing the copy all data to new database.
For the restore, I have assumed that I would drop the original database and copy the new database’s data into the original one, Just like the backup process.
THANKS A MILLION FOR THE CODE YOU SHARED 🙂
Because when I re-run the script. It says “Can’t create database ‘XXXXXX’; database exists”..
Thats why I need to update it frequently. How can I do this?
drop database if exists?
Yep, for the time being I have done this thing. !!
I am getting access denied for the new database. can you guide me with this?
This generally means that you don’t have permissions, you could ask your Database Administrator to grant you permissions on e.g. creating new database.
I just need to make a copy of database on same server.I have cpanel. can You please tell from where I can do this? 🙁
sorry. Cpanel is not my expertise… I only know how to do it at command line/console
Give the proper permissions to the DB user you have assigned to handle the DB.
this script only copying tables, How to copy all views and procedures
Views and Stored Procedures are ‘tables’ in MySQL:
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE ‘VIEW’;
show procedure status