website stat

MySQL duplicate database quick tip!

Here’s how you can quickly copy the contents of one database to another. First, create the new database

CREATE DATABASE db2;

Then, from the command-line, do the following:

$ mysqldump -u root --password=pass db1 | mysql -u root --password=pass db2

If anyone knows how to do this directly from the mysql environment it would be nice! :-)


4 Responses to “MySQL duplicate database quick tip!”

Feed for this Entry
  1. andr3
    Published at January 5th, 2006 at 1:50 am

    hmmmm…. Why not system() in php?
    http://www.php.net/system

    I’m not aware of a way to do this through mysql statements… :-\

  2. Carlos Jorge Andrade
    Published at January 5th, 2006 at 2:08 am

    mysql> create database db2; \! /usr/local/mysql/bin/mysqldump -u root –password=pass db1 | /usr/local/mysql/bin/mysql -u root –password=pass db2

  3. Rui Lapa
    Published at January 5th, 2006 at 7:28 pm

    As root:

    cp /var/lib/mysql/db1 /var/lib/mysql/db2

  4. Curitiboca
    Published at September 7th, 2007 at 5:04 pm

    Rui Lapa, that won’t work for Innodb tables. ibdata1 typically resides on /var/lib/mysql/ and it’s the file that stores all Innodb tables from all databases in your box. By doing that you are simply leaving the data behind.