不使用mysqldump复制/复制数据库

运行以下命令可以复制一个没有数据的表:

CREATE TABLE x LIKE y;

(参见MySQL创建表文档)

你可以编写一个脚本，从一个数据库中获取SHOW TABLES的输出，并将模式复制到另一个数据库中。你应该能够像这样引用模式+表名:

CREATE TABLE x LIKE other_db.y;

至于数据，你也可以在MySQL中做，但不一定快。创建引用后，可以运行以下命令复制数据:

INSERT INTO x SELECT * FROM other_db.y;

如果使用MyISAM，最好复制表文件;这样会快得多。如果你用每个表的表空间来使用INNODB，你应该也能做到这一点。

如果你最终做了INSERT INTO SELECT，一定要临时用ALTER TABLE x DISABLE KEYS 关闭索引 !

编辑 Maatkit也有一些脚本，可能有助于同步数据。它可能不会更快，但您可能可以在没有太多锁定的情况下对实时数据运行他们的同步脚本。

#!/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

我可以看到你说你不想使用mysqldump，但我在寻找类似的解决方案时到达了这个页面，其他人也可能会找到它。考虑到这一点，这里有一个从windows服务器的命令行复制数据库的简单方法:

1. 使用MySQLAdmin或您喜欢的方法创建目标数据库。在本例中，db2是目标数据库，源数据库db1将在其中复制。
2. 在命令行中执行以下语句:

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

注意:-p和[password]之间没有空格

在PHP中:

function cloneDatabase($dbName, $newDbName){
global $admin;
$db_check = @mysql_select_db ( $dbName );
$getTables  =   $admin->query("SHOW TABLES");
$tables =   array();
while($row = mysql_fetch_row($getTables)){
$tables[]   =   $row[0];
}
$createTable    =   mysql_query("CREATE DATABASE `$newDbName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;") or die(mysql_error());
foreach($tables as $cTable){
$db_check   =   @mysql_select_db ( $newDbName );
$create     =   $admin->query("CREATE TABLE $cTable LIKE ".$dbName.".".$cTable);
if(!$create) {
$error  =   true;
}
$insert     =   $admin->query("INSERT INTO $cTable SELECT * FROM ".$dbName.".".$cTable);
}
return !isset($error);
}




// usage
$clone  = cloneDatabase('dbname','newdbname');  // first: toCopy, second: new database

1)将数据从远程服务器复制到本地计算机(vps是远程服务器的别名，可以用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)导入在本地复制的数据

/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

如果您有不同的版本，则可能需要运行

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

没有mysqldump克隆数据库表的最好方法:

1. 创建一个新数据库。

2. 创建克隆查询:

   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 that output!

But note, script above just fast clone tables - not views, triggers and user-functions: you can fast get structure by mysqldump --no-data --triggers -uroot -ppassword , and then use to clone only insert statement .

Why it is actual question? Because uploading of mysqldumps is ugly slow if DB is over 2Gb. And you can't clone InnoDB tables just by copying DB files (like snapshot backuping).

所有先前的解都在这一点上，但是，它们并没有完全复制。我创建了一个PHP函数(尽管有些冗长)，它复制所有内容，包括表、外键、数据、视图、过程、函数、触发器和事件。代码如下:

/* This function takes the database connection, an existing database, and the new database and duplicates everything in the new database. */
function copyDatabase($c, $oldDB, $newDB) {


// creates the schema if it does not exist
$schema = "CREATE SCHEMA IF NOT EXISTS {$newDB};";
mysqli_query($c, $schema);


// selects the new schema
mysqli_select_db($c, $newDB);


// gets all tables in the old schema
$tables = "SELECT table_name
FROM information_schema.tables
WHERE table_schema = '{$oldDB}'
AND table_type = 'BASE TABLE'";
$results = mysqli_query($c, $tables);


// checks if any tables were returned and recreates them in the new schema, adds the foreign keys, and inserts the associated data
if (mysqli_num_rows($results) > 0) {


// recreates all tables first
while ($row = mysqli_fetch_array($results)) {
$table = "CREATE TABLE {$newDB}.{$row[0]} LIKE {$oldDB}.{$row[0]}";
mysqli_query($c, $table);
}


// resets the results to loop through again
mysqli_data_seek($results, 0);


// loops through each table to add foreign key and insert data
while ($row = mysqli_fetch_array($results)) {


// inserts the data into each table
$data = "INSERT IGNORE INTO {$newDB}.{$row[0]} SELECT * FROM {$oldDB}.{$row[0]}";
mysqli_query($c, $data);


// gets all foreign keys for a particular table in the old schema
$fks = "SELECT constraint_name, column_name, table_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL
AND table_schema = '{$oldDB}'
AND table_name = '{$row[0]}'";
$fkResults = mysqli_query($c, $fks);


// checks if any foreign keys were returned and recreates them in the new schema
// Note: ON UPDATE and ON DELETE are not pulled from the original so you would have to change this to your liking
if (mysqli_num_rows($fkResults) > 0) {
while ($fkRow = mysqli_fetch_array($fkResults)) {
$fkQuery = "ALTER TABLE {$newDB}.{$row[0]}
ADD CONSTRAINT {$fkRow[0]}
FOREIGN KEY ({$fkRow[1]}) REFERENCES {$newDB}.{$fkRow[3]}({$fkRow[1]})
ON UPDATE CASCADE
ON DELETE CASCADE;";
mysqli_query($c, $fkQuery);
}
}
}
}


// gets all views in the old schema
$views = "SHOW FULL TABLES IN {$oldDB} WHERE table_type LIKE 'VIEW'";
$results = mysqli_query($c, $views);


// checks if any views were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$view = "SHOW CREATE VIEW {$oldDB}.{$row[0]}";
$viewResults = mysqli_query($c, $view);
$viewRow = mysqli_fetch_array($viewResults);
mysqli_query($c, preg_replace("/CREATE(.*?)VIEW/", "CREATE VIEW", str_replace($oldDB, $newDB, $viewRow[1])));
}
}


// gets all triggers in the old schema
$triggers = "SELECT trigger_name, action_timing, event_manipulation, event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = '{$oldDB}'";
$results = mysqli_query($c, $triggers);


// checks if any triggers were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$trigger = "SHOW CREATE TRIGGER {$oldDB}.{$row[0]}";
$triggerResults = mysqli_query($c, $trigger);
$triggerRow = mysqli_fetch_array($triggerResults);
mysqli_query($c, str_replace($oldDB, $newDB, $triggerRow[2]));
}
}


// gets all procedures in the old schema
$procedures = "SHOW PROCEDURE STATUS WHERE db = '{$oldDB}'";
$results = mysqli_query($c, $procedures);


// checks if any procedures were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$procedure = "SHOW CREATE PROCEDURE {$oldDB}.{$row[1]}";
$procedureResults = mysqli_query($c, $procedure);
$procedureRow = mysqli_fetch_array($procedureResults);
mysqli_query($c, str_replace($oldDB, $newDB, $procedureRow[2]));
}
}


// gets all functions in the old schema
$functions = "SHOW FUNCTION STATUS WHERE db = '{$oldDB}'";
$results = mysqli_query($c, $functions);


// checks if any functions were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$function = "SHOW CREATE FUNCTION {$oldDB}.{$row[1]}";
$functionResults = mysqli_query($c, $function);
$functionRow = mysqli_fetch_array($functionResults);
mysqli_query($c, str_replace($oldDB, $newDB, $functionRow[2]));
}
}


// selects the old schema (a must for copying events)
mysqli_select_db($c, $oldDB);


// gets all events in the old schema
$query = "SHOW EVENTS
WHERE db = '{$oldDB}';";
$results = mysqli_query($c, $query);


// selects the new schema again
mysqli_select_db($c, $newDB);


// checks if any events were returned and recreates them in the new schema
if (mysqli_num_rows($results) > 0) {
while ($row = mysqli_fetch_array($results)) {
$event = "SHOW CREATE EVENT {$oldDB}.{$row[1]}";
$eventResults = mysqli_query($c, $event);
$eventRow = mysqli_fetch_array($eventResults);
mysqli_query($c, str_replace($oldDB, $newDB, $eventRow[3]));
}
}
}

一个SQL，显示SQL命令，需要运行复制一个数据库从一个数据库到另一个。对于每个表，都有创建表语句和插入语句。它假设两个数据库都在同一台服务器上:

select @fromdb:="crm";
select @todb:="crmen";


SET group_concat_max_len=100000000;




SELECT  GROUP_CONCAT( concat("CREATE TABLE `",@todb,"`.`",table_name,"` LIKE `",@fromdb,"`.`",table_name,"`;\n",
"INSERT INTO `",@todb,"`.`",table_name,"` SELECT * FROM `",@fromdb,"`.`",table_name,"`;")


SEPARATOR '\n\n')


as sqlstatement
FROM information_schema.tables where table_schema=@fromdb and TABLE_TYPE='BASE TABLE';

实际上，我想在PHP中实现这一点，但这里没有一个答案是非常有用的，所以这里是我的-相当直接-使用MySQLi的解决方案:

// Database variables


$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '1234';


$DB_SRC = 'existing_db';
$DB_DST = 'newly_created_db';






// MYSQL Connect


$mysqli = new mysqli( $DB_HOST, $DB_USER, $DB_PASS ) or die( $mysqli->error );






// Create destination database


$mysqli->query( "CREATE DATABASE $DB_DST" ) or die( $mysqli->error );






// Iterate through tables of source database


$tables = $mysqli->query( "SHOW TABLES FROM $DB_SRC" ) or die( $mysqli->error );


while( $table = $tables->fetch_array() ): $TABLE = $table[0];




// Copy table and contents in destination database


$mysqli->query( "CREATE TABLE $DB_DST.$TABLE LIKE $DB_SRC.$TABLE" ) or die( $mysqli->error );
$mysqli->query( "INSERT INTO $DB_DST.$TABLE SELECT * FROM $DB_SRC.$TABLE" ) or die( $mysqli->error );




endwhile;

Mysqldump是个不错的解决方案。复制数据库的最简单方法:

mysqldump -uusername -ppass dbname1 | mysql -uusername -ppass dbname2

此外，您可以通过以下方式更改存储引擎:

mysqldump -uusername -ppass dbname1 | sed 's/InnoDB/RocksDB/' | mysql -uusername -ppass dbname2