<p>Without local access to the server, is there any way to duplicate/clone a MySQL db (with content and without content) into another without using <code>mysqldump</code>?</p>

<p>I am currently using MySQL 4.0.</p>

[{'answer_id': 26091, 'author': 'Gary Richardson', 'author_id': 2506, 'author_profile': 'https://Stackoverflow.com/users/2506', 'pm_score': 7, 'selected': False, 'text': '<p>You can duplicate a table without data by running:</p>\n\n<pre><code>CREATE TABLE x LIKE y;\n</code></pre>\n\n<p>(See the <a href="http://dev.mysql.com/doc/refman/5.0/en/create-table.html" rel="noreferrer">MySQL CREATE TABLE</a> Docs)</p>\n\n<p>You could write a script that takes the output from <code>SHOW TABLES</code> from one database and copies the schema to another. You should be able to reference schema+table names like:</p>\n\n<pre><code>CREATE TABLE x LIKE other_db.y;\n</code></pre>\n\n<p>As far as the data goes, you can also do it in MySQL, but it\'s not necessarily fast. After you\'ve created the references, you can run the following to copy the data:</p>\n\n<pre><code>INSERT INTO x SELECT * FROM other_db.y;\n</code></pre>\n\n<p>If you\'re using MyISAM, you\'re better off to copy the table files; it\'ll be much faster. You should be able to do the same if you\'re using INNODB with <a href="http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html" rel="noreferrer">per table table spaces</a>.</p>\n\n<p>If you do end up doing an <code>INSERT INTO SELECT</code>, be sure to temporarily <a href="http://dev.mysql.com/doc/refman/5.0/en/alter-table.html" rel="noreferrer">turn off indexes</a> with <code>ALTER TABLE x DISABLE KEYS</code>!</p>\n\n<p><strong>EDIT</strong> <a href="http://www.maatkit.org/" rel="noreferrer">Maatkit</a> also has some scripts that may be helpful for syncing data. It may not be faster, but you could probably run their syncing scripts on live data without much locking. </p>\n'}, {'answer_id': 1995186, 'author': 'jozjan', 'author_id': 1843623, 'author_profile': 'https://Stackoverflow.com/users/1843623', 'pm_score': 6, 'selected': False, 'text': '<p>If you are using Linux, you can use this bash script:\n(it perhaps needs some additional code cleaning but it works ... and it\'s much faster then mysqldump|mysql)</p>\n\n<pre><code>#!/bin/bash\n\nDBUSER=user\nDBPASSWORD=pwd\nDBSNAME=sourceDb\nDBNAME=destinationDb\nDBSERVER=db.example.com\n\nfCreateTable=""\nfInsertData=""\necho "Copying database ... (may take a while ...)"\nDBCONN="-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}"\necho "DROP DATABASE IF EXISTS ${DBNAME}" | mysql ${DBCONN}\necho "CREATE DATABASE ${DBNAME}" | mysql ${DBCONN}\nfor TABLE in `echo "SHOW TABLES" | mysql $DBCONN $DBSNAME | tail -n +2`; do\n        createTable=`echo "SHOW CREATE TABLE ${TABLE}"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-`\n        fCreateTable="${fCreateTable} ; ${createTable}"\n        insertData="INSERT INTO ${DBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}"\n        fInsertData="${fInsertData} ; ${insertData}"\ndone;\necho "$fCreateTable ; $fInsertData" | mysql $DBCONN $DBNAME\n</code></pre>\n'}, {'answer_id': 7111224, 'author': 'Rafe', 'author_id': 163884, 'author_profile': 'https://Stackoverflow.com/users/163884', 'pm_score': 9, 'selected': False, 'text': "<p>I can see you said you didn't want to use <code>mysqldump</code>, but I reached this page while looking for a similar solution and others might find it as well. With that in mind, here is a simple way to duplicate a database from the command line of a windows server:</p>\n\n<ol>\n<li>Create the target database using MySQLAdmin or your preferred method.  In this example, <code>db2</code> is the target database, where the source database <code>db1</code> will be copied.</li>\n<li>Execute the following statement on a command line:</li>\n</ol>\n\n<p><code>mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2</code></p>\n\n<p>Note: There is NO space between <code>-p</code> and <code>[password]</code></p>\n"}, {'answer_id': 7256328, 'author': 'mr_app', 'author_id': 919557, 'author_profile': 'https://Stackoverflow.com/users/919557', 'pm_score': 4, 'selected': False, 'text': '<p>In PHP:</p>\n\n<pre><code>function cloneDatabase($dbName, $newDbName){\n    global $admin;\n    $db_check = @mysql_select_db ( $dbName );\n    $getTables  =   $admin-&gt;query("SHOW TABLES");   \n    $tables =   array();\n    while($row = mysql_fetch_row($getTables)){\n        $tables[]   =   $row[0];\n    }\n    $createTable    =   mysql_query("CREATE DATABASE `$newDbName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;") or die(mysql_error());\n    foreach($tables as $cTable){\n        $db_check   =   @mysql_select_db ( $newDbName );\n        $create     =   $admin-&gt;query("CREATE TABLE $cTable LIKE ".$dbName.".".$cTable);\n        if(!$create) {\n            $error  =   true;\n        }\n        $insert     =   $admin-&gt;query("INSERT INTO $cTable SELECT * FROM ".$dbName.".".$cTable);\n    }\n    return !isset($error);\n}\n\n\n// usage\n$clone  = cloneDatabase(\'dbname\',\'newdbname\');  // first: toCopy, second: new database\n</code></pre>\n'}, {'answer_id': 44181545, 'author': 'furicle', 'author_id': 1193174, 'author_profile': 'https://Stackoverflow.com/users/1193174', 'pm_score': 2, 'selected': False, 'text': '<p>Note there is a mysqldbcopy command as part of the add on mysql utilities....\n<a href="https://dev.mysql.com/doc/mysql-utilities/1.5/en/utils-task-clone-db.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/mysql-utilities/1.5/en/utils-task-clone-db.html</a></p>\n'}, {'answer_id': 44257011, 'author': 'Remy Mellet', 'author_id': 352173, 'author_profile': 'https://Stackoverflow.com/users/352173', 'pm_score': 1, 'selected': False, 'text': '<p>I don\'t really know what you mean by "local access".\nBut for that solution you need to be able to access over ssh the server to <strong>copy the files where is database is stored</strong>.</p>\n\n<p>I cannot use mysqldump, because my database is big (7Go, mysqldump fail)\nIf the version of the 2 mysql database is too different it might not work, you can check your mysql version using mysql -V.</p>\n\n<p>1) Copy the data from your remote server to your local computer (vps is the alias to your remote server, can be replaced by root@1.2.3.4) </p>\n\n<pre><code>ssh vps:/etc/init.d/mysql stop\nscp -rC vps:/var/lib/mysql/ /tmp/var_lib_mysql\nssh vps:/etc/init.d/apache2 start\n</code></pre>\n\n<p>2) Import the data copied on your local computer</p>\n\n<pre><code>/etc/init.d/mysql stop\nsudo chown -R mysql:mysql /tmp/var_lib_mysql\nsudo nano /etc/mysql/my.cnf\n-&gt; [mysqld]\n-&gt; datadir=/tmp/var_lib_mysql\n/etc/init.d/mysql start\n</code></pre>\n\n<p>If you have a different version, you may need to run</p>\n\n<pre><code>/etc/init.d/mysql stop\nmysql_upgrade -u root -pPASSWORD --force #that step took almost 1hrs\n/etc/init.d/mysql start\n</code></pre>\n'}, {'answer_id': 47740658, 'author': 'Alexander Goncharov', 'author_id': 3654176, 'author_profile': 'https://Stackoverflow.com/users/3654176', 'pm_score': 1, 'selected': False, 'text': "<p>The best way to clone database tables without mysqldump:</p>\n\n<ol>\n<li>Create a new database.</li>\n<li><p>Create clone-queries with query:</p>\n\n<pre><code>SET @NewSchema = 'your_new_db';\nSET @OldSchema = 'your_exists_db';\nSELECT CONCAT('CREATE TABLE ',@NewSchema,'.',table_name, ' LIKE ', TABLE_SCHEMA ,'.',table_name,';INSERT INTO ',@NewSchema,'.',table_name,' SELECT * FROM ', TABLE_SCHEMA ,'.',table_name,';') \nFROM information_schema.TABLES where TABLE_SCHEMA = @OldSchema AND TABLE_TYPE != 'VIEW';\n</code></pre></li>\n<li><p>Run that output! </p></li>\n</ol>\n\n<p>But note, script above just <strong>fast clone tables</strong> - not views, triggers and user-functions: you can fast get structure by <code>mysqldump --no-data --triggers -uroot -ppassword</code> , and then use to clone only insert statement .</p>\n\n<p>Why it is actual question? Because <strong>uploading of mysqldumps is ugly slow</strong> if DB is over 2Gb. And you can't clone InnoDB tables just by copying DB files (like snapshot backuping).</p>\n"}, {'answer_id': 48144503, 'author': 'Dustin', 'author_id': 6519977, 'author_profile': 'https://Stackoverflow.com/users/6519977', 'pm_score': 2, 'selected': False, 'text': '<p>All of the prior solutions get at the point a little, however, they just don\'t copy everything over. I created a PHP function (albeit somewhat lengthy) that copies everything including tables, foreign keys, data, views, procedures, functions, triggers, and events. Here is the code:</p>\n\n<pre><code>/* This function takes the database connection, an existing database, and the new database and duplicates everything in the new database. */\nfunction copyDatabase($c, $oldDB, $newDB) {\n\n    // creates the schema if it does not exist\n    $schema = "CREATE SCHEMA IF NOT EXISTS {$newDB};";\n    mysqli_query($c, $schema);\n\n    // selects the new schema\n    mysqli_select_db($c, $newDB);\n\n    // gets all tables in the old schema\n    $tables = "SELECT table_name\n               FROM information_schema.tables\n               WHERE table_schema = \'{$oldDB}\'\n               AND table_type = \'BASE TABLE\'";\n    $results = mysqli_query($c, $tables);\n\n    // checks if any tables were returned and recreates them in the new schema, adds the foreign keys, and inserts the associated data\n    if (mysqli_num_rows($results) &gt; 0) {\n\n        // recreates all tables first\n        while ($row = mysqli_fetch_array($results)) {\n            $table = "CREATE TABLE {$newDB}.{$row[0]} LIKE {$oldDB}.{$row[0]}";\n            mysqli_query($c, $table);\n        }\n\n        // resets the results to loop through again\n        mysqli_data_seek($results, 0);\n\n        // loops through each table to add foreign key and insert data\n        while ($row = mysqli_fetch_array($results)) {\n\n            // inserts the data into each table\n            $data = "INSERT IGNORE INTO {$newDB}.{$row[0]} SELECT * FROM {$oldDB}.{$row[0]}";\n            mysqli_query($c, $data);\n\n            // gets all foreign keys for a particular table in the old schema\n            $fks = "SELECT constraint_name, column_name, table_name, referenced_table_name, referenced_column_name\n                    FROM information_schema.key_column_usage\n                    WHERE referenced_table_name IS NOT NULL\n                    AND table_schema = \'{$oldDB}\'\n                    AND table_name = \'{$row[0]}\'";\n            $fkResults = mysqli_query($c, $fks);\n\n            // checks if any foreign keys were returned and recreates them in the new schema\n            // Note: ON UPDATE and ON DELETE are not pulled from the original so you would have to change this to your liking\n            if (mysqli_num_rows($fkResults) &gt; 0) {\n                while ($fkRow = mysqli_fetch_array($fkResults)) {\n                    $fkQuery = "ALTER TABLE {$newDB}.{$row[0]}                              \n                                ADD CONSTRAINT {$fkRow[0]}\n                                FOREIGN KEY ({$fkRow[1]}) REFERENCES {$newDB}.{$fkRow[3]}({$fkRow[1]})\n                                ON UPDATE CASCADE\n                                ON DELETE CASCADE;";\n                    mysqli_query($c, $fkQuery);\n                }\n            }\n        }   \n    }\n\n    // gets all views in the old schema\n    $views = "SHOW FULL TABLES IN {$oldDB} WHERE table_type LIKE \'VIEW\'";                \n    $results = mysqli_query($c, $views);\n\n    // checks if any views were returned and recreates them in the new schema\n    if (mysqli_num_rows($results) &gt; 0) {\n        while ($row = mysqli_fetch_array($results)) {\n            $view = "SHOW CREATE VIEW {$oldDB}.{$row[0]}";\n            $viewResults = mysqli_query($c, $view);\n            $viewRow = mysqli_fetch_array($viewResults);\n            mysqli_query($c, preg_replace("/CREATE(.*?)VIEW/", "CREATE VIEW", str_replace($oldDB, $newDB, $viewRow[1])));\n        }\n    }\n\n    // gets all triggers in the old schema\n    $triggers = "SELECT trigger_name, action_timing, event_manipulation, event_object_table, created\n                 FROM information_schema.triggers\n                 WHERE trigger_schema = \'{$oldDB}\'";                 \n    $results = mysqli_query($c, $triggers);\n\n    // checks if any triggers were returned and recreates them in the new schema\n    if (mysqli_num_rows($results) &gt; 0) {\n        while ($row = mysqli_fetch_array($results)) {\n            $trigger = "SHOW CREATE TRIGGER {$oldDB}.{$row[0]}";\n            $triggerResults = mysqli_query($c, $trigger);\n            $triggerRow = mysqli_fetch_array($triggerResults);\n            mysqli_query($c, str_replace($oldDB, $newDB, $triggerRow[2]));\n        }\n    }\n\n    // gets all procedures in the old schema\n    $procedures = "SHOW PROCEDURE STATUS WHERE db = \'{$oldDB}\'";\n    $results = mysqli_query($c, $procedures);\n\n    // checks if any procedures were returned and recreates them in the new schema\n    if (mysqli_num_rows($results) &gt; 0) {\n        while ($row = mysqli_fetch_array($results)) {\n            $procedure = "SHOW CREATE PROCEDURE {$oldDB}.{$row[1]}";\n            $procedureResults = mysqli_query($c, $procedure);\n            $procedureRow = mysqli_fetch_array($procedureResults);\n            mysqli_query($c, str_replace($oldDB, $newDB, $procedureRow[2]));\n        }\n    }\n\n    // gets all functions in the old schema\n    $functions = "SHOW FUNCTION STATUS WHERE db = \'{$oldDB}\'";\n    $results = mysqli_query($c, $functions);\n\n    // checks if any functions were returned and recreates them in the new schema\n    if (mysqli_num_rows($results) &gt; 0) {\n        while ($row = mysqli_fetch_array($results)) {\n            $function = "SHOW CREATE FUNCTION {$oldDB}.{$row[1]}";\n            $functionResults = mysqli_query($c, $function);\n            $functionRow = mysqli_fetch_array($functionResults);\n            mysqli_query($c, str_replace($oldDB, $newDB, $functionRow[2]));\n        }\n    }\n\n    // selects the old schema (a must for copying events)\n    mysqli_select_db($c, $oldDB);\n\n    // gets all events in the old schema\n    $query = "SHOW EVENTS\n              WHERE db = \'{$oldDB}\';";\n    $results = mysqli_query($c, $query);\n\n    // selects the new schema again\n    mysqli_select_db($c, $newDB);\n\n    // checks if any events were returned and recreates them in the new schema\n    if (mysqli_num_rows($results) &gt; 0) {\n        while ($row = mysqli_fetch_array($results)) {\n            $event = "SHOW CREATE EVENT {$oldDB}.{$row[1]}";\n            $eventResults = mysqli_query($c, $event);\n            $eventRow = mysqli_fetch_array($eventResults);\n            mysqli_query($c, str_replace($oldDB, $newDB, $eventRow[3]));\n        }\n    }\n}\n</code></pre>\n'}, {'answer_id': 55256951, 'author': 'Shimon Doodkin', 'author_id': 466363, 'author_profile': 'https://Stackoverflow.com/users/466363', 'pm_score': 0, 'selected': False, 'text': '<p>an SQL that shows SQL commands, need to run to duplicate a database from one database to another. for each table there is create a table statement and an insert statement. it assumes both databases are on the same server:</p>\n\n<pre><code>select @fromdb:="crm";\nselect @todb:="crmen";\n\nSET group_concat_max_len=100000000;\n\n\nSELECT  GROUP_CONCAT( concat("CREATE TABLE `",@todb,"`.`",table_name,"` LIKE `",@fromdb,"`.`",table_name,"`;\\n",\n"INSERT INTO `",@todb,"`.`",table_name,"` SELECT * FROM `",@fromdb,"`.`",table_name,"`;") \n\nSEPARATOR \'\\n\\n\')\n\nas sqlstatement\n FROM information_schema.tables where table_schema=@fromdb and TABLE_TYPE=\'BASE TABLE\';\n</code></pre>\n'}, {'answer_id': 57918255, 'author': 'GDY', 'author_id': 2593099, 'author_profile': 'https://Stackoverflow.com/users/2593099', 'pm_score': 2, 'selected': False, 'text': '<p>Actually i wanted to achieve exactly that in PHP but none of the answers here were very helpful so here\'s my – pretty straightforward – solution using MySQLi:</p>\n\n<pre><code>// Database variables\n\n$DB_HOST = \'localhost\';\n$DB_USER = \'root\';\n$DB_PASS = \'1234\';\n\n$DB_SRC = \'existing_db\';\n$DB_DST = \'newly_created_db\';\n\n\n\n// MYSQL Connect\n\n$mysqli = new mysqli( $DB_HOST, $DB_USER, $DB_PASS ) or die( $mysqli-&gt;error );\n\n\n\n// Create destination database\n\n$mysqli-&gt;query( "CREATE DATABASE $DB_DST" ) or die( $mysqli-&gt;error );\n\n\n\n// Iterate through tables of source database\n\n$tables = $mysqli-&gt;query( "SHOW TABLES FROM $DB_SRC" ) or die( $mysqli-&gt;error );\n\nwhile( $table = $tables-&gt;fetch_array() ): $TABLE = $table[0];\n\n\n    // Copy table and contents in destination database\n\n    $mysqli-&gt;query( "CREATE TABLE $DB_DST.$TABLE LIKE $DB_SRC.$TABLE" ) or die( $mysqli-&gt;error );\n    $mysqli-&gt;query( "INSERT INTO $DB_DST.$TABLE SELECT * FROM $DB_SRC.$TABLE" ) or die( $mysqli-&gt;error );\n\n\nendwhile;\n</code></pre>\n'}, {'answer_id': 59750583, 'author': 'Andy Al', 'author_id': 2874743, 'author_profile': 'https://Stackoverflow.com/users/2874743', 'pm_score': -1, 'selected': False, 'text': "<p>Mysqldump isn't bad solution. Simplest way to duplicate database:</p>\n\n<p><code>mysqldump -uusername -ppass dbname1 | mysql -uusername -ppass dbname2</code></p>\n\n<p>Also, you can change storage engine by this way:</p>\n\n<p><code>mysqldump -uusername -ppass dbname1 | sed 's/InnoDB/RocksDB/' | mysql -uusername -ppass dbname2</code></p>\n"}]
['https://Stackoverflow.com/questions/25794', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/211/']