Without local access to the server, is there any way to copy / clone MySQL database (including content and excluding content) to another database without using mysqldump?

I am currently using MySQL 4.0.

#1 building

If you're using Linux, you can use the following bash script: (it may need some other code cleansing capabilities, but it works And much faster than 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

#2 building

You can copy tables without data by running the following command:

CREATE TABLE x LIKE y;

(please refer to MySQL CREATE TABLE Docs)

You can write a script to get the output of SHOW TABLES from one database, and then copy the schema to another database. You should be able to reference schema + table names, for example:

CREATE TABLE x LIKE other_db.y;

In terms of data, you can also operate in MySQL, but this is not necessarily fast. After you create a reference, you can run the following command to copy the data:

INSERT INTO x SELECT * FROM other_db.y;

If you are using MyISAM, it is best to copy the table file. It will be faster. If yes Table space per table With INNODB, you should be able to do the same.

If you do want to execute INSERT INTO SELECT in the end, make sure to use ALTER TABLE x DISABLE KEYS temporarily Close index !

EDIT Maatkit There are also scripts that may be useful for synchronizing data. It may not be faster, but you can run their synchronization scripts on real-time data without too much locking.

#3 building

Note that as part of the mysql utility addition, there is a mysql dbcopy command. ... https ://dev.mysql.com/doc/mysql-utilities/1.5/en/utils-task-clone-db.html

#4 building

I really don't know what you mean by "local visit". But for this solution, you need to be able to access the server through ssh to copy the files to the location where the database is stored.

I can't use mysqldump because my database is large (7Go, mysqldump failed). If the version of 2 mysql database is too different and may not work properly, you can use mysql-v to check the MySQL version.

1) Copy the data from the remote server to the local computer (vps is the alias of the remote server, which can be replaced by root@1.2.3.4)

ssh vps:/etc/init.d/mysql stop
scp -rC vps:/var/lib/mysql/ /tmp/var_lib_mysql
ssh vps:/etc/init.d/apache2 start

2) Import data copied to the local computer

/etc/init.d/mysql stop
sudo chown -R mysql:mysql /tmp/var_lib_mysql
sudo nano /etc/mysql/my.cnf
-> [mysqld]
-> datadir=/tmp/var_lib_mysql
/etc/init.d/mysql start

If you use a different version, you may need to run

/etc/init.d/mysql stop
mysql_upgrade -u root -pPASSWORD --force #that step took almost 1hrs
/etc/init.d/mysql start

#5 building

The best way to clone a database table without mysqldump:

1. Create a new database.

2. To create a clone query using a query:

   SET @NewSchema = 'your_new_db'; SET @OldSchema = 'your_exists_db'; SELECT CONCAT('CREATE TABLE ',@NewSchema,'.',table_name, ' LIKE ', TABLE_SCHEMA ,'.',table_name,';INSERT INTO ',@NewSchema,'.',table_name,' SELECT * FROM ', TABLE_SCHEMA ,'.',table_name,';') FROM information_schema.TABLES where TABLE_SCHEMA = @OldSchema AND TABLE_TYPE != 'VIEW';

3. Run the output!

Note, however, that the above script is just a quick clone of tables, not views, triggers and user functions: you can quickly get the structure through mysqldump -- no data -- triggers - uroot - ppassword, and then only use it to clone insert statements.

Why is it a practical problem? Because if the database is more than 2Gb, mysqldumps will be slow to upload. Furthermore, you cannot clone InnoDB tables by copying only database files, such as snapshot backups.