MySQL copy/duplicate database

Question

What is the best way to duplicate a mysql db into another without using mysqldump? With content and without content.

I am currently using mysql 4.0

UPDATE: Witout local access to the server.

Answer 1

If you want to do it in SQL you can use

SHOW TABLES

to get a list of all the tables in the db See the MYSQL Manual and then use

SHOW CREATE TABLE tbl_name

to get the meta information, but you will need sufficient access rights to do it. this will help you get the empty database, but i am unsure if there is one for getting the data out.

you can also use

SHOW COLUMNS FROM mytable FROM mydb;

to get the column names if you need.

Answer 2

Is there a reason for the 'no mysqldump' requirement? It can be used to connect to remote servers if I remember.

Answer 3

Can you access your database with phpMyAdmin? If you can, there are an export and an import function in there (off the top of my head, so I can't tell the exact name).

Pay attention to the compatibility option if both your database servers are not the same version.

Answer 4

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.

Answer 5

If you're on Windows and connect to your database from your Windows-machine, you can use a tool like SQLyog Community (free) to do a dump of your table.

It'll do mysqldump format or CSV and it can do structure+data or just structure.

Answer 6

If you are using Linux, you can use this bash script: (it perhaps needs some additional code cleaning but it works ... and it's much faster then mysqldump|mysql)

#!/bin/bash

DBUSER=user
DBPASSWORD=pwd
DBSNAME=sourceDb
DBNAME=destinationDb
DBSERVER=db.example.com

fCreateTable=""
fInsertData=""
echo "Copying database ... (may take a while ...)"
DBCONN="-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}"
echo "DROP DATABASE IF EXISTS ${DBNAME}" | mysql ${DBCONN}
echo "CREATE DATABASE ${DBNAME}" | mysql ${DBCONN}
for TABLE in `echo "SHOW TABLES" | mysql $DBCONN $DBSNAME | tail -n +2`; do
        createTable=`echo "SHOW CREATE TABLE ${TABLE}"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-`
        fCreateTable="${fCreateTable} ; ${createTable}"
        insertData="INSERT INTO ${DBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}"
        fInsertData="${fInsertData} ; ${insertData}"
done;
echo "$fCreateTable ; $fInsertData" | mysql $DBCONN $DBNAME

Answer 7

If you're using the script above with InnoDB tables and have foreign keys, change the last line to the following:

echo "set foreign_key_checks = 0; $fCreateTable ; $fInsertData ; set foreign_key_checks = 1;" | mysql $DBCONN $DBNAME