Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder allows anyone to create and edit queries with only a cursory knowledge of SQL. In Part 1, we used it to write a query to fetch a list of actors that appeared in movies released during a given year. Today's blog will provide a more detailed overview on selecting output fields.

Today's Query

The query that we'll be building here today will again run against the Sakila sample database. It contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.

Much like the previous blog, we will be building a query to fetch a list of actors that appeared in movies released during a given year. The difference is that this time we will make use of a view that lists actors for each title as a comma-delimited list.

Setting Field Associations

Dragging a table/view from the left pane to the Diagram Design pane or double-clicking it adds the table or view to query. The Query Builder will automatically include entity relationships where foreign key constraints have been declared. In this case, we'll be needing the film table and film_list view. They do not have a defined association, so we have to add one ourselves. To do that, just drag one field from one object to another and a line will appear between the linked fields - i.e. between film.film_id and film_list.FID.

The Query Builder will not only draw the association between the objects, but it will also add an INNER JOIN to the query:

With the tables/views selected, we are ready to choose out output fields.

Click the checkbox beside each field that you want to appear in your query results - i.e. film.title, film.film_id, film.release_year, and film_list.actors.

The fields you have selected in the Diagram Design pane will then be displayed in the Syntax pane, where then may then be modified clicking on the <Distinct>, <func> and <Alias> modifiers.

Using Functions

Clicking the <func> modifier opens a list of SUM, MAX, MIX, AVG, and COUNT aggregate functions. You may also enter another function via the Edit tab. For example, we could select the film_list.price field and enter "concat('$', film_list.price)" in the Edit tab to format the price. We can also move the field position by dragging it - for instance, before the actor list:

Field Aliases

When using functions, it's always a good idea to choose a more descriptive field name using an alias. For example, in this case we can simply go with the original field name of "price":

Here is the final query produced by the Query Builder:

SELECTfilm.title,film.film_id,film.release_year,concat('$', film_list.price) AS price,film_list.actorsFROMfilmINNER JOIN film_list ON film.film_id = film_list.FID

And here are the results:

The majority of duplicate records fall into one of two categories: Duplicate Meaning and Non-unique Keys. The How to Spot and Delete Values with Duplicate Meaning in MySQL blog dealt with Duplicate Meaning; the follow-up addressed how to identify Non-unique Keys. Thats where two records in the same table have the same key, but may or may not have different values and meanings. Todays blog will cover how to delete rows with duplicated data, but with different keys.

Identifying Duplicates by Type

The last query presented in the How to Identify Duplicates with Non-unique Keys in MySQL blog listed all the duplicates in a format that was easy to visually scan through:

Having identified all of the duplicated keys and values, we can decide how best to deal with the redundant data.

JENNIFER DAVIS appears in two records with the same key of 22, making those rows exact duplicates. Nick Walbergs name fields are duplicated, but the IDs are not. There is also a duplicated key that is associated with two unrelated actors: #23 for JOHNNY LOLLOBRIGIDA and CHRISTIAN GABLE. With regards to the duplicated keys of 22 and 23, the first is a true duplicate, whereas the second only needs a new key to be generated for one of the records.

Deleting Rows using DELETE JOIN

In the How to Spot and Delete Values with Duplicate Meaning in MySQL blog, we removed duplicates from SELECT result sets by performing a Search & Replace on values. Here we will permanently delete one of the duplicated rows using the DELETE JOIN statement.

Since we are comparing fields from the same table, we have to join the table to itself. We can choose to keep either the lower or higher id number by comparing the ids in the WHERE clause. The following statement keeps the highest id:

DELETE a FROM wp.amalgamated_actors a  INNER JOIN wp.amalgamated_actors a2WHERE a.id < a2.idAND   a.first_name = a2.first_nameAND   a.last_name  = a2.last_name;1 row(s) affected0.093 sec

In this case, the affected (deleted) row is NICK WAHLBERG with an id of 12. A quick SELECT confirms the result:

If you wanted to keep the lowest id, you would just change the a.id < a2.id expression to a.id > a2.id.

Deleting Rows with Non-unique Keys

In the case of JENNIFER DAVIS, who appears twice with the same id of 22, we would need to employ a different approach because running the above statement with a.id = a2.id will target every row in the table! The reason is that we are essentially matching every row against itself! In the next blog, well learn how to delete rows with non-unique keys such as these.

Unlike synchronization, which is a one-time process that brings the schema and data of two databases in sync, replication is a process that continuously (automatically) reproduces data between two databases (although schema updates are also possible). Replication may either be done asynchronously, so that a permanent connection between the two databases is not required, or during non-peak hours, when there is little traffic on the database server, for instance, during the late-night hours.

The main role of replication is to create an amalgamated repository of all user databases and/or disseminate the same level of information amongst all users. In either case the result is a distributed database in which users can access data relevant to their tasks without interfering with the work of others. The implementation of database replication for the purpose of eliminating data ambiguity or inconsistency among users is known as normalization.

In the Database Synchronization Strategies whitepaper, we explored some strategies for synchronizing two databases that are of the same and of dissimilar type, using the Navicat Premium Database Management System. In todays follow-up, well cover how to automate database replication using Navicat Premiums new Automation utility.

Replication Types

Database replication can be done in at least three different ways:

• Snapshot replication: Data on one server is simply copied to another database on the same or on a different server.
• Merging replication: Data from two or more databases is combined into a single database.
• Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.

In a distributed database management system (DDBMS) changes, additions, and deletions performed on the data at one location are automatically reflected in the data stored at all the other locations. Doing so ensures that every user accesses the same data set as all the other users.

Like synchronization, replication can be either Homogenous or Heterogeneous:

• Homogenous: Same source and target DBs, i.e. Percona to Percona, MariaDB to MariaDB, MySQL to MySQL.
• Heterogeneous: Dissimilar source and target DBs, i.e. Oracle to Microsoft SQL Server, PostgresSQL, to Amazon DynamoDB, MySQL to Amazon Aurora

A scenario where Heterogeneous replication would be required would be where one or more external business partners employ a different database type than our own. Automated regular data replication between the two environments is often an integral part of such an arrangement.

Navicat Premiums Automation Utility

Introduced in version 12, Navicat Premiums new Automation utility features an easy-to-use and intuitive interface for creating automated batch jobs. Automation is the execution of a process at one or more regular intervals, beginning and ending at a specific date and time, much like Windows Task Scheduler. In addition to replication, it can be utilized for a variety of jobs, including backups, queries, and reports.

Figure 1: Navicat Premium 12 Automation utility in Windows

Figure 2: Navicat Premium 12 Automation utility in macOS

The User Database

Well be using the Sakila Sample MySQL Database as our user database. It was developed by Mike Hillyer, a former member of the MySQL AB documentation team, and was created specifically for the purpose of providing a standard schema for use in books, tutorials, articles, and the like.

Its themed around the film industry and covers everything from actors and film studios to video rental stores. The full schema structure can be viewed on the MySQL Dev site, if youre interested.

For instructions on setting up the Sakila database using Navicat, see the Generating Reports on MySQL Data article on databasejournal.com.

Sakila MySQL database structure in Navicat Premium 12

Snapshot replication

As described above, Snapshot Replication puts two databases in sync by copying data from one database to another on the same or on a different server. It is the simplest of the three types.

Creating a Data Synchronization Job

A data synchronization profile must first be created in order to automate it as a replication process. The steps to achieve both Homogenous and Heterogeneous synchronization in Navicat Premium 12 were described in the Database Synchronization Strategies whitepaper. For the purposes of this tutorial, well use the first example on Homogenous synchronization between the sakila and sakila2 databases.

Hint: Once youve created the sakila database, you can create the sakila2 database by right-clicking the connection in the Navigation pane and choosing New Database. Then enter the database name (sakila2) in the pop-up window.

To open the Data Synchronization wizard:

• Select Tools -> Data Synchronization from the menu bar.

• The Data Synchronization Options tab contains only a few Compare Options checkboxes. We can leave them as is:

• The next step of the Data Synchronization wizard is for mapping tables. Target tables may be selected via a dropdown list. In this case, we dont need to provide any mapping instructions as the tables in both databases are identical:


• After comparing data, the window shows the number of records that will be inserted, updated or deleted in the target tables. You can uncheck the Show identical table and others option if you dont want to include tables with identical data or tables with different structures in other words, tables that wont be updated. There are also checkboxes to deselect the tables or the actions you do not wish to apply to the target.

  Selecting a table in the list displays the source and target tables data in the bottom pane. Values that differ between source and target are highlighted. As in the top pane, you can uncheck the records that you do not want to apply to the target.

Difference Options

The kind of differences to show may be selected from a dropdown list. Here are the possible options:

• Difference: Show all records that are different in source and target tables.
• Insert: Only show the records that do not exist in the target table.
• Update: Only show the records that exist in both source and target tables having different values.
• Delete: Only show the records that do not exist in the source table.
• Same: Show the records that exist in both source and target tables having identical values.
• All Rows: Show all records in source and target tables.

In our case, selecting Update or Same would show zero rows because there are no rows to update (only insert) and none the same:

As before, clicking the Deploy button generates and displays the Deployment Script:

This screen, like all the previous ones, contains a Save Profile button that allows you to save your settings for future use. This particular screen also has a button for saving the Deployment Script.

You may still Recompare the two databases, or proceed to Execute the deployment script. There is a checkbox to Continue on error so that deployment does not halt upon encountering an error.

As the script executes, you may view its progress in the Message Log. It displays both the number of records processed and completed percentage:

After closing the dialog, we can confirm that the sakila2 database tables now contain data:

Creating a Batch Job

We will now employ Navicats Automation tool to setup a recurring replication between the sakila and sakila2 databases.

• To begin, click the Automation button in the main toolbar.
• Then click on New Batch Job in the Objects toolbar to open a New Batch Job tab.
• Browse the source connection, database and/or schema on the in the Objects pane. That will make saved jobs for that database appear in the Available Jobs bottom pane.
  In the Available Jobs pane, select the Data Synchronization job type, and then move the job from the Available Jobs list to the Selected Jobs list above by double-clicking or dragging it. (You can delete the jobs from the Selected Jobs list in the same way.)

• Click the Save button on the Automation toolbar and provide a descriptive name in the Save dialog.

That will enable the Set Task Schedule and Delete Task Schedule buttons.

The General tab

In the General tab of the Task Schedule dialog, you may provide a description for the task as well as provide several options for its execution.

• Within the Security Options frame, you may configure which user or group account to run the task under. There is also an option to run the task whether the user is logged on or not. If you do choose that option, you'll have provide your OS user password in Windows Scheduler when you save the schedule.


You may also choose to run the task as Hidden as well as configure it to run on a specific operating system.

Triggering the Task

The Triggers tab lists the task's schedule. Tasks may be configured to run on a variety of schedules, including One Time, Daily, Weekly, Monthly, and according to just about any permutation of each.

Click the New... button to bring up the New Trigger dialog:

The same task may run according to numerous schedules. For instance, we could schedule our database synchronization task to run every first of the month as well as on every second Sunday:

Set Email Notification

Navicat allows you to generate and send personalized emails with results returned from a schedule. The results can be emailed to multiple recipients. Check the Send Email option in the Advanced tab and enter the required information.

From

Specify the email address of sender. For example, someone@navicat.com.

To, CC

Specify the email address of each recipient, separating them with a comma or a semicolon (;).

Subject

Specify the email subject with customized format.

Body

Write email content.

Host (SMTP Server)

Enter your Simple Mail Transfer Protocol (SMTP) server for outgoing messages.

Port

Enter the port number you connect to your outgoing email (SMTP) server.

Use authentication

Check this option and enter User Name and Password if your SMTP server requires authorization to send emails.

Secure connection

Specify the connection to use TLS, SSL secure connection or Never.

Send Test Mail

Navicat will send you a test mail indicating success or failure.

Once you've finished configuring your automated job, you can test it by clicking the Start button in the Automation toolbar.

Merging Replication

As the name suggests, Merging Replication consists of combining data from two or more databases into a single database. As an exercise, we will merge the contents of the sakila and sakla2 databases into a third database named sakila_merged that will store the merged dataset.

The Required Data Synchronization Jobs

The Merging Replication job will require us to create and save two Data Synchronization profiles: one for each source database. The steps will be exactly the same as in the Creating a Data Synchronization Job section above, so we wont reiterate them here.

Creating the Batch Job

Batch jobs may be triggered by the source databases or by the target, as we did in the previous section on Snapshot Replication. However, it is usually easiest to trigger batch jobs from the target database since they will all reside on the same server. We'll do that here as well.

• Click the Automation button in the main toolbar.
• Then click on New Batch Job in the Objects toolbar to open a new batch job tab.
• Browse the source connection, database and/or schema on the in the Objects pane. That will make saved jobs for that database appear in the Available Jobs bottom pane.
• In the Available Jobs pane, select the Data Synchronization job type, and then move the job from the Available Jobs list to the Selected Jobs list above by double-clicking or dragging it.

• Click the Save button on the Automation toolbar and provide a descriptive name in the Save dialog. That will enable the Set Task Schedule and Delete Task Schedule buttons.
• In the General tab of the Task Schedule dialog, you may again provide a description for the task as well as provide several options for its execution.
• Within the Triggers tab, Tasks may be configured to run on a variety of schedules, including One Time, Daily, Weekly, Monthly, and within just about any permutation of each. This time, two jobs will execute rather than one.
• Once you've finished configuring your automated job, you can test it by clicking the Start button in the Automation toolbar.

Transactional replication

In Merged Replication, only the merged database contains all of the latest data. Each source database contains only the baseline data, plus whatever was inserted since it was first populated. In Transactional Replication, users receive full initial copies of the database and then receive periodic updates as data changes so that all databases are working with the same dataset. Keeping multiple databases in synch makes this the most complex replication type.

The Required Data Synchronization Jobs

With Transactional replication, the number of required Data Synchronization Jobs increases substantially because data must be replicated across all of the user databases. For example, say that we had three databases called sakila, sakila2, and sakila3. We could merge and propagate the full dataset across all of the user databases using a total of six Data Synchronization Jobs: three to merge the user databases, and another three to update them with the merged dataset.

Here is the Automation wizard with all six jobs:

To allow sufficient time for the data merging to complete, it is best to split the jobs into two parts where the first merges the data and the second updates the user databases with the full dataset after a specified delay.

Here is what the Automation Job that propagates the merged dataset to the user databases might look like:

Running this job two hours after the first should provide plenty of time for the merging to complete. Hence, if the first job was scheduled to run at midnight, we would set this job to start at 2 Am:

Conclusion

In this follow-up to the Database Synchronization Strategies whitepaper, we covered how to automate database replication using Navicat Premium 12s Automation utility. Used in conjunction with its Synchronization tool, it allows DBAs to automate various types of replication to run on a predefined schedule.

For more information about Navicat Premium 12, visit the product page.

Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder allows anyone to create and edit queries with only a cursory knowledge of SQL. In today's blog, we'll use it to write a query to fetch a list of actors that appeared in movies released during a given year.

The Source Database

The query that we'll be building will run against the Sakila sample database. A former member of the MySQL AB documentation team named Mike Hillyer created the Sakila database specifically for the purpose of providing a standard schema for use in books, tutorials, and articles just like the one you're reading.

The database contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the Generating Reports on MySQL Data tutorial for instructions on downloading and installing the Sakila database.

Opening the Query Builder

You can think of the Query Builder as a tool for building queries visually. It's accessible from the Query Designer screen. Let's bring it up by opening a new query:

• Click the Query icon on the main toolbar, followed by the New Query button from the Object toolbar:

• In the Query Designer, click the Query Builder button to open the visual SQL Builder.

  The database objects are displayed in left pane, whereas the right pane is divided into two portions: the upper Diagram Design pane, and the lower Syntax pane:

  

Constructing the Actors for Year's Films Query

It's a good idea to select the tables first, so that the Query Builder knows which fields to present for the field list:

• Drag a table/view from the left pane to the Diagram Design pane or double-click it to add it to query. We'll be needing the actor, film_actor, and film tables.
• You can assign table aliases by clicking on "<alias>" beside each table. To add the table alias, simply double-click the table name and enter the alias in the Diagram Design pane.

Note how the Query Builder already knows the table relationships. That's because foreign key constraints have already been declared on Table objects:


• To include a field in the query, check the left of the field name in the Diagram Design pane. To include all the fields, click at the left of the object caption. Select the actor first and last names as well as the film title.

Adding WHERE Criteria

Clicking on "<Click here to add conditions>" beside the WHERE keyword adds a default WHERE condition of "<--> = <-->".

• Click on the left-hand "<--> = <-->" to select a field. That opens a popup dialog that contains a List of fields as well as an Edit tab.
• Click the List tab and choose the f.release_year field.
• Click OK to close the dialog.
• Next, click on the right-hand "<--> = <-->" to set the release year. This time enter a value of "2006" in the Edit tab. Click OK to close the dialog.
• Click OK to close the Query Builder. You should now see the generated SELECT statement in the Query Editor:

SELECTa.first_name,a.last_name,f.titleFROMactor AS aINNER JOIN film_actor AS fa ON fa.actor_id = a.actor_idINNER JOIN film AS f ON fa.film_id = f.film_idWHEREf.release_year = 2006

• Click the Run button to execute the query. The results will be sorted by Film title:

Conclusion

Whether you're a novice or experience DBA, Navicat's Query Builder makes writing SELECT queries easier than ever before. In an upcoming blog, we'll get into some of its more advanced features.

The majority of duplicate records fall into one of two categories: Duplicate Meaning and Non-unique Keys. The How to Spot and Delete Values with Duplicate Meaning in MySQL blog dealt with Duplicate Meaning; in today's follow-up, we'll address how to identify Non-unique Keys. That's where two records in the same table have the same key, but may or may not have different values and meanings.

How Does this Happen?

Even a well-designed database can accumulate non-unique key duplicates. It often happens as a result of data that is imported from external sources such as text, csv, or excel files as well as data feeds. Even merging data from two different databases might create duplicate keys if you are combining each in some way to generate a new key assuming of course that the new key column supports non-unique values. For example, concatenating two numbers to generate a new key could prove problematic:

An Example Table

In databases that support complex systems, it isn't always feasible to prevent duplicate keys from occurring. What's important is being able to deal with them quickly and effectively before they taint your data.

Let's begin by separating the true duplicate values from overlapping keys.

Here's the product of amalgamating two data sources of actors. You'll notice that there a couple of duplicated names, specifically JENNIFER DAVIS and NICK WAHLBERG:

Nick Walberg would be an instance of Duplicate Meaning, which we explored in the last blog. JENNIFER DAVIS, on the other hand, appears in two records with the same key of 22. There is also a duplicated key that is associated with two unrelated actors: #23 for JOHNNY LOLLOBRIGIDA and CHRISTIAN GABLE. With regards to the duplicated keys of 22 and 23, the first is a true duplicate, whereas the second only needs a new key to be generated for one of the records.

Identifying and Counting Duplicates

The following query will identify all of the records of the above table that share a common id. I recommend using the MySQL group_concat() function to format duplicated rows together on one line:

If you ever wanted to find all duplicates - that is Duplicate Meaning and Non-unique Key duplicates - at the same time, you can combine the above query with one that checks for duplicated names using the UNION operator:

That highlights all the duplicates in one result set:

Conclusion

Crafting a query to identify duplicate keys in MySQL is relatively simple because you only need to group on the key field and include the Having COUNT(*) > 1 clause. In a future article, we'll review some different approaches for deleting duplicate rows and updating keys.

One of the DBA's biggest annoyances is dealing with duplicate data. No matter how much we try to guard against it, duplicates always mange to find their way into our tables. Duplicate data is a big problem because it can affect application views (where each item is supposed to be unique), skew statistics, and, in severe cases, increase server overhead.

In this tip, we'll learn how to recognize duplicate data in MySQL, as well as how to delete them without removing precious valid data.

Duplicate Types

Most of the duplicate records that you'll encounter are one of two distinct types: Duplicate Meaning and Non-unique Keys. In this instalment we'll be dealing with Duplicate Meaning; we'll address Non-unique Keys in the next one.

When a Duplicate is not a Duplicate

Duplicate Meaning is the most common type of duplicate. It's a situation where two or more fields' contents are not the same, but their meaning is. You could think of it as a semantic duplicate.

Consider the following table excerpt:

In the media column, the entries "Television" and "TV" have the same connotation, but expressed differently. This issue is often caused by the use of free-text input where a limited dropdown would have been a better choice.

This type of duplication can be very challenging to deal with because you can't exclude duplicates using a SELECT DISTINCT.

There are two ways to deal with this problem:

• Select data using REPLACE() to swap out values that we don't want with those that we want to see instead:

SELECT DISTINCT	movie_name,
	REPLACE(media, "TV", "TELEVISION") as media,
FROM films;

• Update the actual table data. Here's a statement that updates all instances of TV with the preferred TELEVISION value:

UPDATE films

SET media = REPLACE(media, "TV", "TELEVISION")

WHERE media = "TV";

Here's a real-life example that I came across only a month ago!

Somehow, some unwanted curly apostrophes found their way into our data. Notice the O'BRIEN and O'BRIEN entries:

We can deal with this problem in the same way we did above:

• Select data using REPLACE() to swap out curly apostrophes with regular single quotes so that we're always dealing with the same character:

SELECT DISTINCT	first_name,
	REPLACE(last_name, "'", "'") as last_name,
FROM actors
WHERE REPLACE(last_name, "'", "'") like "O'BRIEN";

• Update the actual table data. This statement updates all apostrophes in the last_name column with regular single quotes:

UPDATE actors

SET last_name = REPLACE(last_name, "'", "'")

WHERE last_name like "%'%";

Conclusion

Duplicate records, doubles, redundant data, duplicate rows; whatever you want to call them, they are one of the biggest banes in a DBA's life. Nevertheless, it's crucial that you weed them out on a regular basis, lest you want to generate faulty statistics and confuse your users who interact with the database.

A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. There are many kinds of data models, but the most popular type is the relational model, which uses a table-based format.

Usually, the data warehousing staff of a business will design one or more types of data models in order to most effectively normalize the tables and pan how to most efficiently store and retrieve business data. Another advantage of doing this exercise upfront is that many professional tools like Navicat can utilize the models as plans and build the database according to their specifications.

That being said, it is an unfortunate fact that all-too-often, data models get misplaced or deleted over time. In that event, DBAs have no recourse but to either redraft the models from scratch or, if they're in the know, let their Database Management Tool create models for them based on the existing database.

In today's tip, we'll learn how to create a model from a variety of database objects in Navicat Premium.

Launching the Wizard

The process of extracting design information from a software product is known as reverse engineering. In Navicat, you can reverse engineer a database/schema, tables or views to a physical model.

To reverse engineer a database schema, right-click it in the Navigation Pane and choose Reverse Schema to Model from the popup menu:

Navicat will then generate a physical model from the selected schema and open it in a new Model window:

You can then work with the new model just as you would one that you created from scratch. For example, you can add relationships, move objects around, and save the model.

Reversing Tables to Model

Individual tables or views may be reverse engineered into physical models as well by right-clicking them in the Navigation Pane and selecting Reverse Tables to Model from the popup list:

That will open the selected table in a new Model window:

Selecting Multiple Tables/Views

It is also possible to select more than one table or view by selecting them in the Objects pane:

Right-clicking anywhere within the selection and choosing Reverse Tables to Model from the popup list will now open those tables/views in a new Model window:

Importing Databases, Schema, Tables or Views from the Model Window

Navicat also supports the importing of databases, schema, tables or views from the Model window. A step-by-step wizard is provided to guide you through the import process.

• Begin by opening a new Model window, either by:
• Clicking the Model button on the main toolbar followed by the New Model button on the Objects toolbar:


OR

• Selecting File > New > Model from the main menu:

• Enter the Database Vendor and Version number in the New Model dialog and click OK to open a new Model window for that product:

• Select File -> Import from Database from the Model window menu:

• On the Import from Database dialog, select a Connection.
• Choose the databases, schemas, tables or views you want to import:

• Click Start to create the model from the selected objects.

Conclusion

Should the need ever arise to reverse engineer database objects into a model, Navicat has you covered. Available in Navicat Premium and Enterprise Editions, the Reverse Engineering feature takes the challenge out of physical model creation from databases, schema, tables or views.

Whether your database of choice is app like MySQL, MariaDB, SQL Server, Oracle, and PostgreSQL, or a cloud-based service such as Amazon RDS, Amazon Aurora, Amazon Redshift, SQL Azure, Oracle Cloud and Google Cloud, you'll inevitably be looking for a piece of data whose location eludes you. For those occasions, you'll be happy that you use one of Navicat's award winning database administration products.

Available in all editions, with the except of Navicat Essentials, the Find In Database/Schema tool allows you to search tables, views and even object structures within a database and/or schema.

You'll find it under the Tools item in the main menu:

Searching for Data

Suppose that we were looking for a record associated with the word jungle. You could bushwhack your way through each and every table or simply enter the search term in the Find in Database screen. There are four Search Modes to choose from: Contains, Whole Word, Prefix and even using powerful Regular Expression pattern matching.

• Contains will match your search term against any part of a text value.
• Whole Word will only match if the text value is exactly the same as the search term.
• Prefix matches the start of a text value.
• Regular Expression apply pattern matching to text values.

Matching is performed on a case insensitive basis unless you uncheck the Case Insensitive box.

The results of your search are displayed in the Find results pane. The table/view Name is displayed, along with the Number of Matched Records. I got two matches for jungle:

To take a better look at the matched rows, just double-click the item in the Find results pane. That will open a new Query Editor with only the row that contains the match:

Searching for Object Structures

An Object Structure search looks for matches against database object names. These would include Tables, Views, Functions, Queries, Indexes, Triggers, Events and/or Materialized Views.

The Search Modes include the same four as in data searches and can either be case sensitive or insensitive.

For this search I set the Search Mode to Prefix so that the Find In Database/Schema tool would find object names that begin with my search term. Not surprisingly, in a movie rental store database, a Prefix of film_ hit a few times!

Below are the results. Notice that the object type and match are both included in the Find results pane. The search term text within Matched Content is highlighted in red as well:

This time, double-clicking an item in the Find results pane opens the appropriate editor for that database object. For example, clicking the last match in the list for the inventory table opens the Table Editor with the matching field selected and highlighted:

Conclusion

The Find In Database/Schema feature makes searching for text content within data and object structure names so much easier than the alternative that you'll ask yourself how you every got by without it. For more information on how to use the tool, there's a video about that very subject on YouTube.

The utility shows comparison between the data and objects from two databases to see differences. It acknowledges objects having separate definitions in both the databases and presents them in a separate style format of selection. Variations in the data are seen in standard form of CSV, VERTICAL, GRID or TAB.

Make use of notation db1: db2 and name both the databases to compare or just compare DB1 to compare two databases under similar name.

The comparison might just run against both databases of varied names on a singular server by suggesting only the server1 alternative. Users can also interlink to another serve by stipulating just the server 1 option. However, they can also link to another server by stipulating the server 2 alternative. Under such a condition, db1 is taken from server 1 while db2 is taken from server 2.

All the databases between both the servers can also be compared via the all option. Under such a situation, only the databases in common with similar name between the servers are efficiently compared. So, you do not have to be specified but server 1 and server 2 alternatives are needed. You can skip the comparison of some databases via the exclude option.

Remember the data shouldn't be changed during the comparison. You may see errors occur when data is changed during the comparative study.

The objects chosen in the database comprise of views, procedures, events, functions, triggers and tables. You can show the count for every object type via vv option.

The check is performed via tests. By default, when the first test fails, the utility stops, but with the help of run all tests options to run all the tests together irrespective of their final state.

The tests comprise of:

• Evaluate database definitions: ensure both the databases are present.
• Evaluate the existence of objects in the two databases: ensure that both the databases acknowledges the objects.
• Make a comparative study of object definitions: the objects are compared and differences shown.
• Evaluate table row counts: ensure that the two database tables have similar row numbers.
• Evaluate table data uniformity: it ensures both the changed rows and the missing rows from each table in the databases. The step is divided into two stages: firstly the complete table is compared between the table, but if it fails, then the program to search rows differences is implemented.

You might wish to use skip xxx function to just run few tests. It is helpful when you want to synchronize two databases, to ignore running all the tests again and again in the procedure. Every test shows following results:

• Pass- the test is successful.
• Fail- the test failed.
• Skip- the test skipped because of a missing point.
• Warn- the test witnessed an unusual error.
• - -the test isn't right for this object.

With the help of these results, you can find out whether the tests were successful or not and whether you need to conduct them again or not.

Being a MySQL compatible relational database engine, Amazon Aurora amalgamates safety, availability and speed of top notch commercial databases with the easiness and cost efficiency of open source database. The engine has been tagged at 1/10th of the price of the commercial engines.

After determining that Aurora is the database to be used for application development, the second stage is to choose on a migration methodology and to generate a database migration program.

Migration Factors: Source Database

There are two types of migration factors:

• Homogenous migrations - Percona, MariaDB and MySQL to Amazon Aurora
• Heterogeneous migrations - Oracle, PostgresSQL, Microsoft SQL Server to Amazon Aurora

Homogeneous Migration

For the source database you desire to migrate is amenable to MySQL 5.6 such as Percona or MariaDB, then you have the below stated migration methodologies:

RDS Snapshot migration: for those who have AWS RDS system accessing their MYSQL Data Base server, then they just have to migrate the database snapshot to the AWS Aurora Database. For downtime migrations, you may either have to terminate your application or may just terminate writing to the database whilst migration and snapshot is in evolvement.

Migration with the help of native Navicat Tools: one can make use of native Navicat tools to migrate the plan from your DB server to AWS Aurora DB. With the help of this approach, you get more control over the database migration procedure.

Migration by utilizing AWS DMS: it is equipment rendered by AWS just to migrate he database scheme to AWS Aurora DB. Before making use of AWS DMS to shift the data, one needs to copy the database plan from the resource to the place where you have targeted via using native Navicat tools.

Making use of AWS DMS is a reliable idea when you do not have the experience to use native Navicat tools. It is provided with the option of having downtime as well as not having downtime methodology.

Heterogeneous Migrations

When the source database you wish to migrate isn't MySQL compliant database such as PostgresSQL, Oracle to AWS Aurora DB, then you have a number of options accessible to fulfill the migration procedure.

Schema Migration: schema migration to Amazon Aurora from a non-MySQL compliant database can be accomplished by making use of AWS Schema Conversion Tool. It is a desktop app which assists you to convert your database plan from PostgreSQL, Microsoft SQL Server as well as Oracle database to an Amazon RDS MySQL DB or Amazon Aurora DB cluster.

Data Migration: whilst assisting homogeneous migrations with zero downtime, AWS DMS (AWS Database Migration Service) assists constant replication over heterogeneous databases and is a priority alternative to move your resource database to your destination database, for migrations with downtime as well as migrations with near-zero downtime.

With the help of these migrations, you can effectively migrate database to Amazon Aurora. They are the simplest and easiest way to migrate database. So, choose any according to your compliance and then go for it.

Now you have the availability of Navicat for Amazon Aurora Databases. You can now handle Amazon Aurora Databases via Navicat, the sturdiest powerful database manager, Graphical User Interface and admin tool.

Amazon Aurora is a MySQL compliant, interactive database engine which interconnects the availability and speed of top notch commercial databases with easiness and cost efficiency of open source databases.

Navicat Premium is one of the most prominent database management solutions for database development on all the leading platforms like Linux, Windows and MacOS. It permits you to connect to SQLite , SQL Server, Oracle, MySQL, PostgreSQL and MariaDB databases from just one app. It is basically installed on your computer and interconnects not just on-premises databases but also cloud database like Amazon Redshift, Amazon Aurora as well as Amazon RDS.

Amazon eradicates the requirement of set up, operation and measure a relational database, permitting the users to concentrate on the database design and efficient management. Along with Amazon instance, Navicat gives you a top notch end to end database development experience.

Manage AWS Aurora Database with Navicat on Windows as well as Mac.

• Go to Navicat Premium and Click File > New Connection > Amazon AWS and then finally Amazon Aurora.
• Type in a suitable name that best describes your connection in the text box of Connection Name.
• When you choose to Navicat Cloud feature, you can select to save the connection to My Connections or the assignments on Cloud from drop-down list of Add To. If you have chosen My Connections, then the settings are stored in the local device.
• Type in the endpoint details of the cluster in the Endpoint and Port columns.
• Type in your username and password.
• Check your connection.

Migration

Navicat renders you with an interactive and powerful GUI and provides a set of full-fledged specs for Amazon database development as well as maintenance. To enhance the efficacy and productivity for your database, it's Data Transfer feature assists you to transfer the data across several DBMS local to SQL file, local to cloud and local to local. It allows you to perform automation work at periodic intervals and send alerts mails to mentioned recipients on job completion to assure your migration is well submitted and 100% successful.

Amazon Web Services

Amazon Web Services renders a complete bunch of cloud based products for your developmental needs and business requirements. It provides easy access to and simple handling of the services via a communicative web based user interface. Big companies and organizations rely on Amazon AWS because of its reliability and service level registrations.

Some of the features offered by Navicat are mentioned below:

• Unified data migration
• Diversified manipulation equipment
• Quick and reliable SQL editing
• Smart Database designer
• Productivity increment
• Simple and easy collaborations
• Enhanced secure connection

With that being said, you can manage your AWS Aurora databases efficiently with navicat. It is reliable, fast, simple and convenient to handle. You will no longer need to go for anything else.

There are optimization techniques for database server, majorly managing system configuration rather than tweaking SQL statements. It is suitable for DBAs who desire to assure performance as well as scalability over the servers they handle, for developer initiating installation scripts which comprise of establishing the database and for those running MySQL themselves for development, testing and more to enhance their productivity.

System Factors

Some system level aspects also impact the performance in a great way:

If you have sufficient RAM, you can get rid of all swap devices. Often OS use a swap device in some regards regardless of having free memory.

Ignore exterior locking for MyISAM tables. The default is for exterior locking to be restricted. The exterior locking and skip exterior locking alternatives unambiguously enable and disable exterior locking. Disabling exterior locking doesn't impact MySQL functionality till the time you run just one server. Make sure you take down the server before running myisamchk. On few systems, it is important to disable exterior locking because it won't work.

You cannot disable the external locking when you run several MySQL servers on the similar data, or when you run myisamchk to evaluate a table without seeing the server to level and lock the tables primarily. Remember that making use of several MySQL servers to evaluate the same data synchronously isn't usually recommended, except when you're utilizing NDB cluster.

Optimizing Disk I/O

It shows pathways to organize storage devices when you can devote better and quicker storage hardware to the database server.

Disk seeks are a big performance blockage. The issue becomes more obvious when the data amount commences to grow so big that efficient caching gets impossible.

Enhance the number of present disk spindles by symliking files to other disks or disks striping.

A good idea is to differ the RAID level according to the critical kind of data.

Using NFS with MySQL

You need to be cautious when thinking of using NFS with MySQL. Possible problems which differ by OS and NFS version comprise of:

• Log files and MySQL data files sited on NFS volumes get locked and are unavailable for usage.
• Data inconsistencies produced because to messages received out of order or poor network traffic. To get rid of it, make use of TCP with hard and intr mount support.
• High file size restrictions.

Use symbolic links

You can shift database from database directory to any other place or replace it with symbolic links to a new place. You may wish to do this, for instance, to shift a database to a file system with higher free space or enhance your system's speed by spreading your table to varying disks.

The suggested idea to do it is to symlink complete database directories to a separate disk. Symlink MYISAM tables just as a last choice.

1.You can use symbolic links for databases on Unix
2.You can use symbolic links for MyISAM tables on Unix
3.You can use symbolic links for databases on Windows