Compare data in multiple databases from the command line

Last modified: August 8, 2024

dbForge Data Compare for MySQL allows automating and scheduling routine daily comparison and synchronization tasks using the command-line interface.

Could dbForge Data Compare for MySQL synchronize a database schema from a single source database to multiple target databases on different servers?

The tool effectively compares data across multiple MySQL databases. It also provides seamless automation to synchronize multiple databases across different MySQL instances directly from the command line.

Comparing databases using the command line includes the following steps:

1. Create a text file with source and target databases and servers

2. Create a .bat file

3. Compare source and target databases from the command line

In our example, we’ll compare databases located on different servers.

Create a text file with source and target databases and servers

1. Open any third-party text editor and enter the source server connection, database name, username, and password separated by commas.

TestUser1,Database_name1,Server_name1,Port1,Password1
TestUser2,Database_name2,Server_name2,Port2,Password2

where:

• Server_name1 and Server_name2 are the names of the source servers to which the source databases are connected.
• Database_name1 and Database_name2 are the names of the source databases you want to compare.
• Port1 and Port2 are the port numbers of the source servers you connect.
• TestUser1, TestUser2, Password1, and Password2 are the login credentials of the MySQL source servers you connect to.

Here is an example of the source databases to be compared:

Note

You can add as many servers and databases as you need.

TestUser1, Database_name1, Server_name1, Port1, Password1
TestUser2, Database_name2, Server_name2, Port2, Password2
TestUser3, Database_name3, Server_name3, Port3, Password3
TestUserN, Database_nameN, Server_nameN, PortN, PasswordN

2. Save the file, for example, as source_servers_databases.txt.

3. In a new document of the text editor, repeat the same step for the target databases and save the file, for example, as target_servers_databases.txt.

Here is an example of the target databases to be compared:

Create a .bat file

In the third-party text editor, enter the script for comparing databases on the source and target servers and save the file.

Set Compare="C:\Program Files\Devart\dbForge Compare Bundle for MySQL Standard\dbForge Data Compare for MySQL\datacompare.com"

FOR /F "eol=; tokens=1,2,3,4* delims=, " %%e in (source_servers_databases.txt) do (

FOR /F "eol=; tokens=1,2,3,4* delims=, " %%j in (target_servers_databases.txt) do (

%Compare% /datacompare /source connection:"User Id=%%e; Database=%%f; Host=%%g; Port=%%h; Password=%%i; Character Set=utf8" /target connection:"User Id=%%j; Database=%%k; Host=%%l; Port=%%m;  Password=%%n; Character Set=utf8" /log:"data_compare_mysql.log" 

) 

)
pause

where

• source_servers_databases.txt is a file name with the list of source servers and databases.
• target_servers_databases.txt is a file name with the list of target servers and databases.
• data_compare_mysql.log is a name of the file to store the output result. It will be saved to the folder where the .bat file is located.

Note

The Set Compare command specifies the default installation path for dbForge Data Compare for MySQL as part of dbForge Compare Bundle. If you have changed the path, specify the correct path to the datacompare.com file.

To compare one database with multiple databases, proceed with these steps:

1. Add the database you want to compare to source_servers_databases.txt and save the changes.

2. Add required databases to the target_servers_databases.txt file. Ensure to save the changes.

In case you need to compare one database with multiple databases and another database with multiple databases, follow these steps:

1. Create copies of the source_servers_databases.txt and target_servers_databases.txt, and .bat files, but with different names. For example, source_servers_databases01.txt, target_servers_databases01.txt, run01.bat.

2. Add the necessary databases to the source and target files (source_servers_databases01.txt, target_servers_databases01.txt).

3. Create a new .bat file to run other .bat files in sequence. For example:

call run01.bat 
call run02.bat 

Note

Ensure no pause command exists in the run01.bat and run02.bat files; otherwise, the next run operation will not start.

Compare source and target databases from the command line

1. Run the Command Prompt.

Note

To run the Command Line in Windows 10:

From the Desktop Mode

• Move the mouse cursor to the lower left corner until the desktop shortcuts menu appears.
• Right-click to see the shortcut menu and select Run.

From the Charms Bar

• Move your mouse to the upper right corner until the Charms Bar appears.
• Select Apps from the list and type run in the search box.
• Click Run from the search results.

Use Windows + R Shortcut Keys

• In desktop mode, press the Windows + R key combination to show the Run command line.
• In the window that opens, type cmd and press Enter.

2. Execute the .bat file. The log file containing the output result will automatically be generated.

The screenshot shows the comparison between the bikestoresprod and bikestoresdev databases.

The screenshot shows the comparison between the sakila databases located on the different servers.