How to copy the tables of an existing MySQL database to a new database without the records?

Question

I'm new to MySQL. Right now I'm trying to replicate an already existing PHP website with a SQL database both of my property in a new web hosting. I wish to create a new SQL database with the same structure of the old website database and connect the new PHP website with the new database.

I already know how to connect the PHP file with the database but I don't know how to copy a database tables to another.

This may help. http://stackoverflow.com/questions/6175473/mysql-export-schema-without-data — Kirk Backus, Oct 19 '13 at 22:31

score 2 · Accepted Answer · answered Oct 19 '13 at 22:28

2

You want to use a utility like phpMyAdmin to copy the database directly. You don't want to do it programmatically using PHP. Have a look at this.

answered Oct 19 '13 at 22:28

Nick

5,995
12
54
78

+1 but still utility like PHPMyAdmin... You don't want ... programmatically using PHP.... little bit contradictory don't you think? – Raymond Nijland Oct 19 '13 at 22:32
@RaymondNijland What I was trying to say is that you don't need to write custom PHP script to achieve this end, you can just use a DB management tool. I doesn't read as contradictory to me, but obviously it wasn't clear, given your response :) – Nick Oct 19 '13 at 22:36
trust me i know how PHPMyAdmin works but i avoid it for some good reasons (because it isn't safe to use http://www.cvedetails.com/vulnerability-list/vendor_id-784/Phpmyadmin.html..) but it was some Dutch sarcasm (joke).. – Raymond Nijland Oct 19 '13 at 22:40
@RaymondNijland Sorry! I missed the joke :P I knew you knew how phpMyAdmin works, though! – Nick Oct 20 '13 at 00:16

score 0 · Answer 2 · answered Oct 19 '13 at 22:56

If you want to do this without use of tools like phpMyAdmin you can use SHOW TABLES; and SHOW CREATE TABLE table_name; queries to iterate through the db you want to copy. So if you put this into PHP script you get something like that:

<?php 

$host_name = "localhost";
$user_name = "xfiddlec_user";
$pass_word = "public";
$database_name = "xfiddlec_max";
$port = "3306";

$first_connect = new mysqli($host_name, $user_name, $pass_word, $database_name, $port);
//$second_connect = new mysqli($host_name2, $user_name2, $pass_word2, $database_name2, $port2);
$sql = "SHOW TABLES";

$result = $first_connect->query($sql);
if (($result) && ($result->num_rows > 0))
{
//convert query result into an associative array
while ($row = $result->fetch_row())
{
    $sql2 = "SHOW CREATE TABLE {$row[0]}";
    $res = $first_connect->query($sql2)->fetch_row();
    //echo "<pre>"; print_r($res); echo "</pre>";
    $createSQL = $res[1];
    $second_connect->query($createSQL);
    echo $createSQL;
}

}

I have commented some parts so that you can try it on PHP Fiddle here. However though it's better to use some tool if you can. :)

thanks for the help! I was able to do it with PHPMyAdmin and it worked perfectly. — oquiroz, Oct 19 '13 at 23:41

How to copy the tables of an existing MySQL database to a new database without the records?

2 Answers2