Skip to main content

Step-by-Step Guide to Quickly Compare MySQL Data

This article introduces how to compare data between two MySQL databases from scratch using NineData's data comparison feature, identify inconsistencies, and quickly fix them.

Please select your scenario to jump to the appropriate operation steps

Step One: Register for a NineData Account​

  1. Open the NineData Registration Page. registration

  2. Enter your phone number and click Get Verification Code, then enter the verification code sent by the NineData system.

    tip

    Only phone numbers from Mainland China are currently supported. If you are using a phone number outside the +86 area, you can only receive the verification code via email.

  3. Enter your username and password.

    tip

    The password strength requirements are as follows:

    • The password can contain letters, numbers, and special characters.

    • Length is 8 to 16 characters.

    • Must contain both letters and numbers.

    • Cannot contain empty characters (spaces).

  4. Enter your email and company name, read the Terms of Service and Privacy Policy carefully, then check the box in front of , and click .

    tip

    If you have already entered your email in the above steps, you do not need to enter it again.

Step Two: Log in to NineData​

  1. Open the NineData Login Page. login

  2. You can choose to log in via , , , or .

    • : Enter the phone number or email used during registration, and enter the password, then click .
      tip
      • If you are using a phone number outside the +86 area, you cannot log in via phone number at the moment. Please use the email you registered with to log in.
      • If you have forgotten your login password, you can click in the lower right corner to .
    • Click , enter the phone number used during registration, click , and enter the verification code sent by the NineData system, then click .
      tip

      If you are using a phone number outside the +86 area, please enter the email you registered with to receive the verification code.

    • : Log in to NineData via SSO. For more information, please refer to Log in to NineData via SSO.

    • : Log in to NineData by scanning the WeChat QR code.

Step Three: Apply for Two Test Data Sources​

  1. In the NineData console, click on the left navigation bar , and click on the page.

    tip

    New users can also click on the page after logging in. This entry is only displayed when the current user or organization has not added any data sources.

  2. On the page, select the number of data sources under , up to 2.

  3. Click to complete the application for a free data source, and you will be redirected to the page of the data source. The system will start importing the test dataset, which will take about 30 seconds. After the import is complete, the SQL Console will prompt .

    tip

    You can also click the download icon in the upper right corner of the SQL Console page, and click to manually import the test dataset.

Step Four: Create a Comparison Task​

  1. In the NineData console, click on Database Comparison > Data Comparison, and then click Create Data Comparison on the page.

  2. On the Select Data Source page, configure according to the table below, and click .

    Parameter
    		Description
    Enter the name of the comparison task. To facilitate subsequent searching and management, please use a meaningful name. Up to 64 characters are supported.
    Source Data SourceSelect 01-ninedata-sample-datasource.
    Target Data SourceSelect 02-ninedata-sample-datasource.
    Comparison FrequencySelect One-time comparison.
    Comparison MethodSelect Full Data Comparison.

    comparison_step1

  3. On the Select Comparison Objects tab, select the objects to be compared (in this example, sample_employees_4551), and then click .

    comparison_step2

  4. On the Configure Mapping page, click the Target checkbox, and in the pop-up database list, click the target database name for comparison (in this example, sample_employees_4552), and then click Save and Pre-check.

    comparison_step3

  5. On the tab, click .

    tip

    If the check does not pass, you need to manually investigate the items that did not pass and then re-precheck.

    comparison_step4

  6. On the page, it prompts , click View Details to enter the data comparison details page.

    backup_step4

Step Five: View Comparison Results and Quickly Fix Inconsistent Data​

After the comparison task is completed, you can view the inconsistent content in the data and quickly fix it.

  1. On the comparison details page, you can view the details of the comparison results. In the screenshot below, you can see that there are 4 inconsistent data in one table. Click the Frame icon on the right side of the operation column to open the Table Comparison Details page.

  2. On the Table Comparison Details page, you can view all the details of the inconsistent data. Click the sql1 icon on the right side of the target inconsistent data in the operation column, and then copy the automatically generated inconsistent repair statement.

    tip

    If you want to batch repair all inconsistent content in the table, you can close the Table Comparison Details page, and then click the sql1 icon on the right side of the target table in the operation column, and copy the automatically generated inconsistent repair statement.

  3. Click Database DevOps > SQL Console in the left navigation bar, select the target data source of the comparison task (in this example, 02-ninedata-sample-datasource), and then click Start Query.

  4. Paste the copied inconsistent repair statement into the editor, press the Ctrl+A (Mac is Command+A) key combination to select all, and then click Execute.

  5. After successful execution, the execution result will be displayed in the Execution Information area below, as shown in the figure. At this point, all inconsistencies have been successfully repaired.

  6. Click Database Comparison > Data Comparison in the left navigation bar to open the comparison task page again, click the Immediate Comparison in the operation column on the right side of the target comparison task, and click OK in the pop-up window to re-execute the comparison.

  7. After the task is completed, you can see that all the inconsistent results have been repaired.

Last updated on