MySQL 不使用 mysqldump 复制/复制数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy/duplicate database without using mysqldump
提问by jhornnes
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?
如果没有对服务器的本地访问，有没有办法在不使用的情况下将 MySQL 数据库（有内容和没有内容）复制/克隆到另一个数据库中mysqldump？
I am currently using MySQL 4.0.
我目前正在使用 MySQL 4.0。
回答by Rafe
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:
我可以看到您说您不想使用mysqldump，但我在寻找类似解决方案时访问了此页面，其他人也可能会找到它。考虑到这一点，这里有一种从 Windows 服务器的命令行复制数据库的简单方法：
- Create the target database using MySQLAdmin or your preferred method. In this example,
db2is the target database, where the source databasedb1will be copied. - Execute the following statement on a command line:
- 使用 MySQLAdmin 或您的首选方法创建目标数据库。在本例中，
db2是目标数据库，源数据库db1将被复制到其中。 - 在命令行上执行以下语句：
mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2
mysqldump -h [server] -u [user] -p[password] db1 | mysql -h [server] -u [user] -p[password] db2
Note: There is NO space between -pand [password]
注意：-p和之间没有空格[password]
回答by Gary Richardson
You can duplicate a table without data by running:
您可以通过运行以下命令复制没有数据的表：
CREATE TABLE x LIKE y;
(See the MySQL CREATE TABLEDocs)
（请参阅MySQL CREATE TABLE文档）
You could write a script that takes the output from SHOW TABLESfrom one database and copies the schema to another. You should be able to reference schema+table names like:
您可以编写一个脚本，SHOW TABLES从一个数据库获取输出并将模式复制到另一个数据库。您应该能够引用架构+表名称，例如：
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:
就数据而言，您也可以在MySQL中进行，但不一定很快。创建引用后，您可以运行以下命令来复制数据：
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.
如果您使用的是 MyISAM，最好复制表文件；它会快得多。如果您将 INNODB 与每个表表空间一起使用，您应该能够做同样的事情。
If you do end up doing an INSERT INTO SELECT, be sure to temporarily turn off indexeswith ALTER TABLE x DISABLE KEYS!
如果你最终做的INSERT INTO SELECT，一定要暂时关闭指标有ALTER TABLE x DISABLE KEYS！
EDITMaatkitalso 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.
编辑Maatkit也有一些可能有助于同步数据的脚本。它可能不会更快，但您可能可以在没有太多锁定的情况下在实时数据上运行他们的同步脚本。
回答by jozjan
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)
如果你使用的是 Linux，你可以使用这个 bash 脚本：（它可能需要一些额外的代码清理，但它可以工作......而且它比 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
回答by mr_app
In PHP:
在 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
回答by furicle
Note there is a mysqldbcopy command as part of the add on mysql utilities.... https://dev.mysql.com/doc/mysql-utilities/1.5/en/utils-task-clone-db.html
请注意，有一个 mysqldbcopy 命令作为添加 mysql 实用程序的一部分.... https://dev.mysql.com/doc/mysql-utilities/1.5/en/utils-task-clone-db.html
回答by Remy Mellet
I don't really know what you mean by "local access". But for that solution you need to be able to access over ssh the server to copy the files where is database is stored.
我真的不知道您所说的“本地访问”是什么意思。但是对于该解决方案，您需要能够通过 ssh 访问服务器以复制存储数据库的文件。
I cannot use mysqldump, because my database is big (7Go, mysqldump fail) If the version of the 2 mysql database is too different it might not work, you can check your mysql version using mysql -V.
我无法使用mysqldump，因为我的数据库很大（7Go，mysqldump 失败） 如果2 mysql 数据库的版本差异太大可能无法使用，您可以使用mysql -V 检查您的mysql 版本。
1) Copy the data from your remote server to your local computer (vps is the alias to your remote server, can be replaced by [email protected])
1) 将远程服务器上的数据复制到本地计算机（vps是远程服务器的别名，可以用[email protected]代替）
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 the data copied on your local computer
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
If you have 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
回答by Dustin
All of the prior solutions get at the point a little, however, they just don't copy everything over. I created a PHP function (albeit somewhat lengthy) that copies everything including tables, foreign keys, data, views, procedures, functions, triggers, and events. Here is the code:
所有先前的解决方案都在一点点上，但是，他们只是没有复制所有内容。我创建了一个 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]));
}
}
}
回答by GDY
Actually i wanted to achieve exactly that in PHP but none of the answers here were very helpful so here's my – pretty straightforward – solution using MySQLi:
实际上，我想在 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;
回答by Shimon Doodkin
an SQL that shows SQL commands, need to run to duplicate a database from one database to another. for each table there is create a table statement and an insert statement. it assumes both databases are on the same server:
显示 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';
回答by Alexander Goncharov
The best way to clone database tables without mysqldump:
在没有 mysqldump 的情况下克隆数据库表的最佳方法：
- Create a new database.
Create clone-queries with 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';Run that output!
- 创建一个新的数据库。
使用查询创建克隆查询：
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';运行那个输出！
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 .
但请注意，上面的脚本只是快速克隆表- 而不是视图、触发器和用户函数：您可以通过 快速获取结构mysqldump --no-data --triggers -uroot -ppassword，然后仅用于克隆插入语句。
Why it is actual question? Because uploading of mysqldumps is ugly slowif DB is over 2Gb. And you can't clone InnoDB tables just by copying DB files (like snapshot backuping).
为什么是实际问题？因为如果数据库超过 2Gb ，mysqldump 的上传速度会很慢。而且您不能仅通过复制数据库文件（如快照备份）来克隆 InnoDB 表。
