Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy/duplicate database without using mysqldump

Tags:

mysql

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 mysqldump?

I am currently using MySQL 4.0.

like image 878
jhornnes Avatar asked Aug 25 '08 08:08

jhornnes


People also ask

How do you duplicate a database?

Expand Databases, right-click the desired database, point to Tasks, and then select Copy Database... If the Welcome to the Copy Database Wizard splash page appears, select Next. Select a Source Server page: Specify the server with the database to move or copy. Select the authentication method.


2 Answers

I can see you said you didn't want to use mysqldump, 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:

  1. Create the target database using MySQLAdmin or your preferred method. In this example, db2 is the target database, where the source database db1 will be copied.
  2. Execute the following statement on a command line:

mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2

Note: There is NO space between -p and [password]

like image 84
Rafe Avatar answered Oct 05 '22 01:10

Rafe


You can duplicate a table without data by running:

CREATE TABLE x LIKE y; 

(See the MySQL CREATE TABLE Docs)

You could write a script that takes the output from SHOW TABLES from one database and copies the schema to another. You should be able to reference schema+table names like:

CREATE TABLE x LIKE other_db.y; 

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:

INSERT INTO x SELECT * FROM other_db.y; 

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 per table table spaces.

If you do end up doing an INSERT INTO SELECT, be sure to temporarily turn off indexes with ALTER TABLE x DISABLE KEYS!

EDIT Maatkit 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.

like image 27
Gary Richardson Avatar answered Oct 05 '22 01:10

Gary Richardson