Duplicate a MySQL table – Copy Table / Duplicate Database / PHP Script

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 Database.

Create Table `NewTable` Like `OldTable`;

Copy the Data to New Table

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:

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:

// 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;
}

DevOps / Site Reliability Engineering

• How to Clean Up NVM Node Versions Except One?
• Monitoring 28 VPS Machines including a Raspberry Pi with Nezha Dashboard
• Python/Bash Script to Print the Optimized Parameters for MySQL Servers
• Learn to Manage Your MySQL Database with a Python Script
• A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database
• How to Print MySQL Table Summary using PHP?
• Secure the Linux Server by Disallow the Remote Root Login (SSH and FTP and MySQL database)
• Bash Script to Check, Repair, Optimise and Backup MySQL database
• Duplicate a MySQL table - Copy Table / Duplicate Database / PHP Script
• MySQL server stopped due of out of memory exception on Ubuntu VPS
• Running Apache Server (PHP + MySQL) on Raspberry PI
• How to Optimise SQL Queries? Quick Tips
• Recovery Models in SQL Server
• Database Optimisation Script in PHP

–EOF (The Ultimate Computing & Technology Blog) —