Compare and synchronize scripts folders in MySQL

A scripts folder is a set of scripts that represent a database schema and optionally can include data. An object creation script file is generated for each object. These script files can be organized in user-defined subfolders based on object types. They can be utilized for:

• Version-controlling databases by storing the script files in a version-control system to track object modifications
• Comparing database objects on unconnected servers

A scripts folder stores the following information:

• Database objects that are grouped into folders based on their types in the database, such as Functions, Procedures, Tables, Triggers, or Views. Each folder contains individual .sql files with creation scripts for each object within that group.
• A config.xml file that contains configuration information:
  • MySQL server version
  • Collation of the database from which the scripts folder is created
  • Entity, which refers to the database these scripts are associated with
  • Configuration setting that specifies whether the system or application supports (true) or not (false) a component or functionality referred to as the connect engine.
  • SQL mode that determines how MySQL handles various aspects of SQL syntax, data validation, and error handling

Note

dbForge Schema Compare can compare scripts folders with different structures, allowing for scenarios where objects are either organized in separate folders or generated within a single .sql file. It should be noted that scripts folders can be created using the dbForge tool or any third-party tool. However, when synchronizing data, dbForge Schema Compare will write objects according to the folder structure specified in the dbForge tool.

Create a scripts folder

dbForge Schema Compare supports creating a scripts folder in the Create Scripts Folder dialog or in the New Schema Comparison wizard when comparing and synchronizing scripts folders.

Create a scripts folder using the Create Scripts Folder dialog

1. On the ribbon, select Database > Create Scripts Folder to open the Create Scripts Folder dialog.

2. Under Source, choose a database as a source type, a server connection, and a database from which you want to create a scripts folder.

3. Under Destination, choose a scripts folder as a destination type and specify a path to the output file name to store the scripts folder.

Note

The default path to the output file to store the scripts folder is C:\Users\Username\Documents\Devart\dbForge Schema Compare for MySQL\scriptFolder. To change the path, click Browse, specify the folder, and click Select Folder.

4. Optional: Click Scripts Folder Structure to customize the scripts folder structure and file name templates. In the window that opens, all object types are selected by default. Clear the checkbox next to the type you want to exclude from the scripts folder structure. To save the changes, click OK.

5. Click Create to create a scripts folder. The notification popup window will display, informing you that the scripts folder has been created. Click OK to close the window.

Compare and synchronize scripts folders

When comparing and synchronizing scripts folders as a Target or a Source, a synchronization script is created. You can save it to a file or open it in an internal editor.

When a scripts folder is selected as the Source, you can execute the synchronization script directly against the target database or generate a migration script.

When a scripts folder is selected as the Target, you can update the scripts folder after synchronization or generate a migration script.

Note

When you need to compare data in a scripts folder, refer to Compare and synchronize scripts folders and static data.

To compare and synchronize a scripts folder as the source

1. On the standard toolbar, click New Schema Comparison to open the New Schema Comparison wizard.

2. On the Source and Target page of the wizard, choose a scripts folder as a source type and a database as a target type.

3. Under Source:

• In the Database scripts folder field, you can do either of the following:
  • From the dropdown list, choose the previously connected scripts folder.
  • Click New to create a scripts folder.
  • Click Browse to select the scripts folder stored on your machine.
• The server version and default collation are automatically inherited from the current server connection.
• Optional: Select the Use target database properties checkbox to apply the properties of the target database.

4. Under Target:

• In the Connection field, you can do either of the following:
  • From the dropdown list, choose the previously established server connection or select Manage to open the Connection Manager and create a new connection.
  • Click New to establish a new connection.
  • Click Edit to modify the existing connection.
• Choose a target database you want to compare.

5. Optional: On the Options page, you can customize the default behavior of the schema comparison process by selecting additional options.

6. Click Compare to run the comparison process. The Schema Comparison progress window opens, showing the stages of the comparison process.

7. In the Schema Comparison document that opens, all objects for schema synchronization are selected by default. To exclude the objects from synchronization, clear the checkboxes next to the corresponding objects and click Synchronize data to the target database to open the Schema Synchronization Wizard.

8. On the Output page of the wizard, select Execute the script directly against the target database and click Synchronize to synchronize the scripts folder with the database.

To compare and synchronize a scripts folder as the target

1. On the standard toolbar, click New Schema Comparison to open the New Schema Comparison wizard.

2. On the Source and Target page of the wizard, choose a database as a source type and a scripts folder as a target type.

3. Under Source:

• In the Connection field, you can do either of the following:
  • From the dropdown list, choose the previously established server connection or select Manage to open the Connection Manager and create a new connection.
  • Click New to establish a new connection.
  • Click Edit to modify the existing connection.
• Choose a source database to be compared.

4. Under Target:

• In the Database scripts folder field, you can do either of the following:
  • From the dropdown list, choose the previously connected scripts folder.
  • Click New to create a scripts folder.
  • Click Browse to select the scripts folder stored on your machine.
• The server version and default collation are automatically inherited from the current server connection.
• Optional: Select the Use source database properties checkbox to apply the properties of the source database.

5. Optional: On the Options page, you can select additional options to customize the default comparison process.

6. Click Compare to run the comparison process. The Schema Comparison progress window opens, showing the stages of the comparison process.

8. In the Schema Comparison document that opens, all objects for schema synchronization are selected by default. To exclude the objects from synchronization, clear the checkboxes next to the corresponding objects and click Synchronize data to the target database to open the Schema Synchronization Wizard.

9. On the Output page of the wizard, select Update the scripts folder and click Synchronize to synchronize the database with the scripts folder.

Scripts folder synchronization issues

• Certificates, symmetric keys, and asymmetric keys are not supported when you use a scripts folder as a data source.

• When you select a scripts folder as a target data source, comments that are part of a table definition will be lost when the table is modified and the object creation script updated.

• You can use the scripts folder comparison feature to copy or transfer database data. To move data, it’s required to utilize the scripts folder feature provided by dbForge Data Compare. To learn how to do it, see Compare and synchronize scripts folders and static data.

• We recommend you select the Ignore spaces in object names and Ignore white spaces options on the Options page when you use a scripts folder as a data source to avoid any issues for objects such as views, stored procedures and functions, triggers, user types, defaults, and rules.

Note

To ensure scalability and reliable backups, you can use Microsoft Azure Files and/or Microsoft OneDrive as storage for your scripts folders.