Inserting Data from a Source Database into a Destination Database, with Different Servers

As databases become increasingly important for storing and managing data, the need to transfer data between them becomes more pressing. In this scenario, we have two database servers: a source server and a destination server. The source server contains data that needs to be transferred to the destination server, which is currently empty or has outdated data.

The goal of this article is to provide an accurate method for inserting data from the source server into the destination server without missing any records. We will explore various approaches and technologies to achieve this task.

Understanding the Current Script

Before we dive into the solution, let’s analyze the current script provided by the OP:

$latest_result_summary = $conn2->query("SELECT MAX(`NOTABLECALLID`) FROM `ambition`.`callsummary`");
$latest_row_summary = $latest_result_summary->fetch_row();
$latest_summary_id = $latest_row_summary[0];

//select All rows from the source phone database(callsummary)
$source_data_summary = mysqli_query($conn, "SELECT * FROM `cdrdb`.`callsummary` WHERE `NOTABLECALLID` > $latest_summary_id");

// Loop on the results
while ($source_summary = $source_data_summary->fetch_assoc()) {
    // Check if row exists in destination phone database, cdrdb.callsummary
    $row_exists_summary = $conn2->query("SELECT NOTABLECALLID FROM ambition.callsummary WHERE NOTABLECALLID = '".$source_summary['NOTABLECALLID']."' ")
    or die(mysqli_error($conn2));

    //if query returns false, rows don't exist with that new ID.
    if ($row_exists_summary->num_rows == 0) {
        //Insert new rows into ambition.callsummary
        $stmt_summary = $conn2->prepare("INSERT INTO ambition.callsummary (NOTABLECALLID, STARTTIME, ENDTIME, DURATION, ANSWERED, ts, firstcallid) VALUES ( ?, ?, ?, ?, ?, ?, ?)")
        or die(mysqli_error($conn2));

        mysqli_stmt_bind_param($stmt_summary, "issiisi", 
            $source_summary['NOTABLECALLID'], 
            $source_summary['STARTTIME'],
            $source_summary['ENDTIME'], 
            $source_summary['DURATION'],
            $source_summary['ANSWERED'], 
            $source_summary['ts'],
            $source_summary['firstcallid'] 
        );

        $stmt_summary->execute() or die(mysqli_error($conn2));
    }
}

This script fetches the maximum ID from the source database, then selects all rows from the source database with an ID greater than the maximum ID. It checks if each row exists in the destination database and inserts new rows into the destination database only if they do not exist.

Limitations of the Current Script

The current script has several limitations:

• It is prone to errors due to user input (e.g., SQL injection attacks).
• It may miss records that are inserted by other users or scripts between the time it checks for existing rows and inserts new ones.
• It uses a complex loop to check if each row exists in the destination database, which can be inefficient.

Alternative Approach Using mysqldump

The provided answer suggests using mysqldump as an alternative approach:

mysqldump cdrdb callsummary
  --host sourcedbhost
  --single-transaction 
  --no-create-info 
  --insert-ignore 
  --where "notablecallid > $latest_summary_id"
  > callsummary-YYMMDD.sql

mysql
  --host destdbhost
  --execute "source callsummary-YYMMDD.sql"

This approach involves:

• Dumping the data from the source database using mysqldump with options to limit the data to rows greater than $latest_summary_id. The --insert-ignore option ensures that any existing rows are skipped without throwing an error.
• Importing the dump into the destination database using mysql.

This approach is more efficient and accurate than the original script, as it:

• Uses a single command to fetch data from multiple tables (using the --where clause).
• Avoids complex loops and user input-related errors.

Conclusion

In conclusion, inserting data from a source database into a destination database without missing any records requires careful consideration of various factors, including efficiency, accuracy, and security. The approach outlined in this article provides an accurate and efficient method using mysqldump and mysql. By leveraging these tools, developers can create reliable and scalable solutions for data transfer between databases.

Last modified on 2023-06-24