Datenbank kopieren/duplizieren ohne mysqldump

Question

Gibt es eine Möglichkeit, ohne lokalen Zugriff auf den Server eine MySQL-DB (mit und ohne Inhalt) in eine andere zu duplizieren/zu klonen, ohne mysqldump ?

Ich verwende derzeit MySQL 4.0.

Answer 1

Der beste Weg, Datenbanktabellen ohne mysqldump zu klonen:

1. Erstellen Sie eine neue Datenbank.

2. Klon-Abfragen mit Abfrage erstellen:

   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. Führen Sie diese Ausgabe aus!

Aber beachten Sie, dass das obige Skript nur schnelle Klontabellen - nicht Views, Triggers und User-Funktionen: Sie können schnell Struktur bekommen, indem Sie mysqldump --no-data --triggers -uroot -ppassword und dann nur die Einfügeanweisung zu klonen.

Warum ist das eigentlich eine Frage? Weil das Hochladen von mysqldumps ist hässlich langsam wenn die DB mehr als 2Gb beträgt. Und Sie können InnoDB-Tabellen nicht klonen, indem Sie einfach DB-Dateien kopieren (wie Snapshot-Backups).

Answer 2

Alle bisherigen Lösungen treffen den Kern der Sache ein wenig, aber sie kopieren nicht alles. Ich habe eine PHP-Funktion erstellt (wenn auch etwas langwierig), die alles kopiert, einschließlich Tabellen, Fremdschlüssel, Daten, Ansichten, Prozeduren, Funktionen, Trigger und Ereignisse. Hier ist der 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]));
        }
    }
}

Answer 3

Eigentlich wollte ich genau das in PHP zu erreichen, aber keine der Antworten hier waren sehr hilfreich, so ist meine - ziemlich geradlinig - Lösung mit 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;

Answer 4

Ich weiß nicht genau, was Sie mit "lokalem Zugriff" meinen. Aber für diese Lösung müssen Sie in der Lage sein, über ssh auf den Server zuzugreifen Kopieren der Dateien, in denen die Datenbank gespeichert ist .

Ich kann mysqldump nicht verwenden, weil meine Datenbank groß ist (7Go, mysqldump schlägt fehl) Wenn die Version der beiden mysql-Datenbanken zu unterschiedlich ist, kann es sein, dass es nicht funktioniert. Sie können Ihre mysql-Version mit mysql -V überprüfen.

1) Kopieren Sie die Daten von Ihrem Remote-Server auf Ihren lokalen Computer (vps ist der Alias für Ihren Remote-Server, kann durch Root@1.2.3.4 ersetzt werden)

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) Importieren Sie die auf Ihren lokalen Computer kopierten Daten

/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

Wenn Sie eine andere Version haben, müssen Sie möglicherweise Folgendes ausführen

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

Answer 5

Ein SQL, das die SQL-Befehle zeigt, die ausgeführt werden müssen, um eine Datenbank von einer Datenbank in eine andere zu duplizieren. Für jede Tabelle gibt es eine Anweisung zum Erstellen einer Tabelle und eine Anweisung zum Einfügen. Es wird davon ausgegangen, dass sich beide Datenbanken auf demselben Server befinden:

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';