mysql: how do i make a copy of the entire database?

Question

how do i make an exact duplicate copy of a database in mysql?

create database test1 from test ???????

if this is not possible, how do i copy a table from one database into another"?

Answer 1

One way to do it is taking a dump from the source db and importing it into another db, like this:

mysqldump src_db > src.sql
mysql dest_db < src.sql

Answer 2

You can't per say. You need to create the new database, create the tables and then do inserts i.e. insert into INSERT INTO newdatabase.table1 SELECT * FROM olddatabase.table1;

Alternately, you could do a full database dump to a sql file and then dump this file into a new database.

A third option (easiest really if you have it) is PhpMyAdmin's copy database function. It creates the new database, the tables and inserts the data all automatically.

Answer 3

mysqlhotcopy db_name /path/to/new_directory

Keep in mind that the users will be blocked from updating tables while they are being copied, since mysqlhotcopy locks them.

I don't know if mysqlhotcopy locks them all at once or one at a time. If it locks them all at once, then all users will be blocked until the copy completes.

If it locks them one at a time while users are modifying them, you have a risk of the tables getting out of sync with each other. Like this:

mysqlhotcopy locks table a, and begins copy of table a client attaches to database and tries to update tables a and c. It is temporarily blocked because of lock on table a. mysqlhotcopy completes copy, unlocks table a, locks table b, and begins copy of table b client makes related updates to tables a and c. mysqlhotcopy completes copy, unlocks table b, locks table c, and copies table c

The problem is that the copied version of table a is from before the client's modification, and the version of table c is from after the client's modification, which puts the backup in an inconsistent state.

Update I just looked at mysqlhotcopy (MySQL 4.1.9). It locks all the tables at once.