Entry

How to copy the contents of one database to another database(locally)

Feb 13th, 2008 00:32
dman, Scott Patten, Thenu sarathy, http://sturly.com

mysqladmin create <new database>
mysqldump --opt <source database> | mysql <new database>

From the MySQL docs:

The easiest (although not the fastest) way to move a database between
two machines is to run the following commands on the machine on which
the database is located:

shell> mysqladmin -h 'other hostname' create db_name
shell> mysqldump --opt db_name \
        | mysql -h 'other hostname' db_name

Also from the MySQL docs:

--opt  Same as --quick --add-drop-table --add-locks --extended-insert
--lock-tables. Should give you the fastest possible dump for reading
into a MySQL server.

Also check out mysqlhotcopy if you want to just backup the MySQL datafiles.

www.mysql.com/doc/m/y/mysqldump.html
www.mysql.com/doc/U/p/Upgrading-to-arch.html
www.mysql.com/doc/m/y/mysqlhotcopy.html