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

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.

转载于:https://stackoverflow.com/questions/25794/copy-duplicate-database-without-using-mysqldump

csdnceshi59
ℙℕℤℝ Yes, it's been years, but... Recently one silly developer tried to make a copy of our live database... What can I say, the live MySQL database is currently about 8 TB (compressed storage)...
3 年多之前 回复
csdnceshi54
hurriedly% you can vote to reopen. I can't reopen unanimously. The question has problems--do my work for me, spam honeypot, not really programming related but more database management, etc which lead to the original closure. You can edit to try and improve as well.
大约 5 年之前 回复
csdnceshi50
三生石@ I know this is an old one, but would you please consider revising your opinion. It doesn't even sound correct, as your closure leads to stackoverflow.com/help/on-topic and there it says "software tools commonly used by programmers".
大约 5 年之前 回复
csdnceshi66
必承其重 | 欲带皇冠 These things can be added later...
5 年多之前 回复
weixin_41568183
零零乙 I understand it is a mistake that this question appears as closed I am not interested in personal stuff between individuals. I believe this is a technical site and should deliver the best service it can. So please SO clean up this mess and do it soon please. I regret I am adding to the ot comments now, but our technical staff blocked the meta sites from being accessed, which I think is a good thing, because we need to get work done. Please make SO a better place.
5 年多之前 回复
csdnceshi73
喵-见缝插针 Now up to 250/114...
接近 6 年之前 回复
csdnceshi67
bug^君 stackoverflow.com/questions/675289/…
6 年多之前 回复
weixin_41568134
MAO-EYE I'll also throw out there that if you're trying to dump a large table and get connection lost errors, you may need so set the timeout global variables on your database: set variable net_read_timeout = (seconds think default is 30) set variable net_write_timeout = (seconds think default is 60) also try adding the --single-transaction and --quick parameters to your mysqldump command. See: dev.mysql.com/doc/refman/5.5/en/mysqldump.html
大约 7 年之前 回复
weixin_41568126
乱世@小熊 stackoverflow.com/questions/9893186/mysql-create-database-like/…
大约 7 年之前 回复
csdnceshi51
旧行李 Closed as off topic is 100% wrong. This is the exact question I have, and it has a well defined technical answer which does not relate to mere opinion. I think moderator must have been doing a such looking for words like "best" to find questions to close.
大约 7 年之前 回复
csdnceshi54
hurriedly% You might want to take that up on Database Administrators, where this would be on topic.
7 年多之前 回复
csdnceshi72
谁还没个明天 100% agree that the "closed as off topic" is wrong and that the guildelines should be updated - more lenience is needed - SO is heading in the wrong direction. It is obvious that @will is completely off the mark, and should have his moderator privileges removed - this single question is proof enough.
7 年多之前 回复
csdnceshi60
℡Wang Yan An off topic question gets 92 upvotes and 37 favorites. Thumbs up for such off topic question. Outdated guidelines.
7 年多之前 回复
weixin_41568196
撒拉嘿哟木头 "without using mysqldump" - the piped version is better than dumping to a file and then uploading the file, but still seems a little roundabout than doing something from within mysql itself.
8 年多之前 回复
csdnceshi70
笑故挽风 what about: server:~# mysqldump dbname | mysql dbnamecopy
接近 9 年之前 回复
csdnceshi56
lrony* InnoDB or MyISAM? For InnoDB, I would suggest xtra-backup.
接近 9 年之前 回复
csdnceshi71
Memor.の mysqldump is fine for small databases but a recent dump of a highly indexed database will take me over 40 hours to recover from a dump. That is why MySQL enterprise has an enterprise backup, with a price tag of $5k. PS: I will supply an answer that keeps the referential integrity by letting us use InnoDB and perform fast backups and recovery.
接近 9 年之前 回复
weixin_41568184
叼花硬汉 Most of the answers below recreate mysqldump, which is sure to break in some cases...
接近 9 年之前 回复
csdnceshi53
Lotus@ I'm using MySQL Workbench. In the MySQL Utilities there is a mysqldbcopy command that worked for me.
大约 9 年之前 回复
csdnceshi69
YaoRaoLov Make sure you don't do this: CREATE TABLE t2 SELECT * FROM t1; as you'll lose your index information, any special stuff like auto_increment etc.. many google's for this copy table sort of thing will lead you to doing this and it'll have un-desired results.
大约 9 年之前 回复
csdnceshi56
lrony* What's wrong with mysqldump?
9 年多之前 回复
csdnceshi74
7*4 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.
大约 12 年之前 回复
csdnceshi75
衫裤跑路 Is there a reason for the 'no mysqldump' requirement? It can be used to connect to remote servers if I remember.
大约 12 年之前 回复

7个回答

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]

csdnceshi64
游.程 Because I dont have mysqldump available on webserver and I dont have priviledges to install too. Please consider askers point when you answer any question. Tons of answer with mysqldump we need the exceptional solution.
2 年多之前 回复
csdnceshi55
~Onlooker OK here's a reason to not use mysqldump... if you want to execute this process from an AWS Lambda instance, you don't have access to mysqldump. Period. Many comments are mande that you just have to load the necessary libraries, but the total lack of examples or success stories implies to me it is impractical or ill-advised.
2 年多之前 回复
csdnceshi51
旧行李 If this command is run a second time, after the data has been modified in db1, will db2 still become a copy of db1 or will the data be added to the data existing in db2?
2 年多之前 回复
csdnceshi70
笑故挽风 I know it's been years, but there are so many ways this may not work... Run command on console - what console? For example most (secure) databases do not allow connections from some random remote locations. No more connection from your command line unless you are on a whitelisted host. Now what?
3 年多之前 回复
weixin_41568131
10.24 god, could please someone explain my why a question stating "without mysqldump" has as first response one that uses mysqldump? with like, 6x more votes than the correct one? c'mon, SO...
接近 4 年之前 回复
weixin_41568174
from.. If you don't want your password in your history in Bash, just put a single space at the start of the command and it won't save it to the history.
5 年多之前 回复
csdnceshi76
斗士狗 How do I know if this is successful or not? I've tried this but there were no changes made nor errors.
大约 6 年之前 回复
weixin_41568127
?yb? Fast and elegant! The copying and piping into the new database took a few seconds. Thanks also about the hint regarding not inserting the space after -p - that's why I always failed at mysqldump
6 年多之前 回复
csdnceshi66
必承其重 | 欲带皇冠 mysqldump -u root -p -v db1 | mysql -u root -p db2 and two times enter pass
6 年多之前 回复
csdnceshi80
胖鸭 The first command should create a file named db1, sounds like this has failed. Maybe you don't have write permissions?
6 年多之前 回复
weixin_41568110
七度&光 the accepted answer works for me but I want to use your method. I get the error -bash: db1: No such file or directory, why is this where do I need to run it from?
6 年多之前 回复
csdnceshi72
谁还没个明天 This won't work when there is trigger inside the tables and the trigger refers to the former database db1.
6 年多之前 回复
csdnceshi50
三生石@ In case someone needs a reference on how to create a database, just read this: stackoverflow.com/a/2428440/2623074
接近 7 年之前 回复
weixin_41568126
乱世@小熊 I <3 you and I think I'm not the only one :D
接近 7 年之前 回复
weixin_41568184
叼花硬汉 Does anyone know why this doesnt work with subprocess.check_output()?
接近 7 年之前 回复
csdnceshi65
larry*wei If you use bash, you can also put a space before the command (and the passwords) to disable it being remembered
大约 7 年之前 回复
weixin_41568134
MAO-EYE One of the most elegant answers I've seen on stackoverflow. Kudos!
7 年多之前 回复
csdnceshi57
perhaps? using mysqldump and mysql from bash becomes much simpler if you set up you .my.cnf file to store your user/host/password files
接近 8 年之前 回复
csdnceshi80
胖鸭 If you don't want to save the password plaintext in your terminals history, you need to split the command: mysqldump -h [server] -u [user] -p db1 > db1, mysql -h [server] -u [user] -p db2 < db1 Otherwise the password prompt messes it up, at least for me when using putty.
大约 8 年之前 回复
csdnceshi58
Didn"t forge The case against mysqldump is that there has to be a faster way then serializing the data into queries, transmitting the queries outside of the process and through the tty back into the exact same process, reparsing the queries, and executing them as statements. That sounds horribly inefficient and unnecessary. We're not talking about crossing between MySQL masters or changing storage engines. It's shocking there is no efficient intraprocess binary transfer.
大约 8 年之前 回复
csdnceshi54
hurriedly% /what would actually be a good reason NOT to use mysqldump?
8 年多之前 回复
csdnceshi77
狐狸.fox mysqldump: Got error: 1449: The user specified as a definer ('root'@'127.0.0.1') does not exist when using LOCK TABLES
8 年多之前 回复

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.

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.

1) Copy the data from your remote server to your local computer (vps is the alias to your remote server, 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 the data copied on your 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 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
weixin_41568184
叼花硬汉 This is the most efficient way to do it but I think that "without local access to the server" means that we can't access to the system. Probably a shared hosting? So this is not the answer.
大约 2 年之前 回复

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
csdnceshi64
游.程 I've cleaned-up this script by @jozjan and applied some of the comments regarding foreign and other keys to create this version on GIST gist.github.com/christopher-hopper/8431737
接近 7 年之前 回复
weixin_41568134
MAO-EYE Seems to work but I am worried about the workaround for the foreign keys. Time will tell.
接近 7 年之前 回复
weixin_41568183
零零乙 ah seems to be a problem with using mysql command in general sorry richard
8 年多之前 回复
weixin_41568183
零零乙 I'm getting mysql: unknown variable 'innodb_data_file_path=ibdata1:100M:autoextend' I'm on persona though which is a update to mysql - is there a fix to it?
8 年多之前 回复
weixin_41568127
?yb? If you get the problem @zirael described it is probably because the script is failing to copy views. You can ignore views from the copy by changing the SHOW TABLES line to SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE' and adding | cut -f 1. The complete line should look something like this but replace the double backticks with single backticks: for TABLE in echo "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" | mysql $DBCONN $DBSNAME | tail -n +2 | cut -f 1; do
8 年多之前 回复
csdnceshi80
胖鸭 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'utf8_general_ci' at line 1
大约 9 年之前 回复
csdnceshi80
胖鸭 Is it me or it doesn't work? It created just a few tables and throw this: Copying database (...) ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-modelowanie-copy' at line 1 ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-modelowanie-copy' at line 1
大约 9 年之前 回复
csdnceshi79
python小菜 It seems so, because he uses a "SHOW CREATE TABLE" statement which generates a CREATE TABLE with all properties of the original.
9 年多之前 回复
csdnceshi70
笑故挽风 Does this also copy constraint data and other properties of tables?
9 年多之前 回复
csdnceshi71
Memor.の 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
10 年多之前 回复

In 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
csdnceshi68
local-host this script don't take views in count
大约 3 年之前 回复
csdnceshi55
~Onlooker If you are working on windows machine. Then kindly use this instead of finding lengthy ways to execute command.
3 年多之前 回复

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:

/* 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]));
        }
    }
}
csdnceshi79
python小菜 Downvoted because the question is not "do not use mysqldump" but "use a better approach than mysqldump". This is even worse of mysqldump in terms of efficiency.
大约 2 年之前 回复

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

csdnceshi53
Lotus@ Your post was probably voted down because you didnt include any other information than a link. Answers are supposed to be more comprehensive.
大约 2 年之前 回复
csdnceshi58
Didn"t forge But there was no restriction saying that wasn't possible.... and it's a commonly installed thing (but optional as you say) If it's not installed, many would find installing that package easier than setting up and running a 60 line Bash script, etc....
大约 2 年之前 回复
csdnceshi53
Lotus@ But it requires installation of an additional package: apt install mysql-utilities
3 年多之前 回复

The best way to clone database tables without mysqldump:

  1. Create a new database.
  2. 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';
    
  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).

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问