Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Manage Your MySQL Databases

DZone's Guide to

How to Manage Your MySQL Databases

Database administration tools provide a GUI/web interface to automate pretty much any database task. Learn how to use them to manage your MySQL databases.

· Database Zone
Free Resource

Read why times series is the fastest growing database category.

SQLyog is a professional-grade GUI tool for the MySQL RDBMS. SQLyog is available for the Windows operating system starting from Windows XP/Windows 2003 to Windows 8/Server 2008 R2. It will also work under Linux, Unix, and Mac OS X using the Wine environment. Moreover, a subset of SQLyog Enterprise/Ultimate functionalities is available with the free SQLyog Job Agent (SJA) for Linux as a native Linux utility. This makes it possible to specify and test "scheduled jobs" on a Windows environment and port execution parameters seamlessly to a Linux environment.

Why Use a Database Administration Tool

Database administration tools provide a GUI/web interface to automate database tasks like querying tables, find and replace operations, and any other task that you might want to run on a database. With respect to MySQL, it offers the free MySQL Workbench. Although it’s far more visually appealing than the command line interface, performance is less than stellar when under a high workload. As such, many users have reported that MySQL Workbench hangs or fails to respond. Frustration has led DBAs to far superior paid solutions that not only deliver more robust operations but also come with various useful features to help you get the most out of your DBA tasks.

Throughout the remainder of this blog, we’ll explore how SQLyog helps boost your productivity via some of its unique and value-adding features.

Session Restore

Have you ever experienced the frustration of your system crashing, or of accidentally closing your database administration application and losing all your unsaved tabs and queries? 

With Session Restore, you can restore your previous session along with all the query tabs, query builder tabs, schema designer tabs, and the history tab just the way they were prior to the crash.

SQLyog will always restore your previous session on startup by default. However, you can turn off this behavior from the Others tab of the main Preferences dialog. It’s accessible by selecting Tools > Preferences from the main menu.

The Restore session on startup option:

Session Savepoints

SQLyog also has the ability to save Session Savepoints and open connections from a save point to the same state as when the save point was generated.

As in the automatic restoring of the previous session on startup, the term "session" encompasses all open connections as well as all Query, Query Builder, and Schema Designer tabs opened for every connection, as well as the History tab.

The Session Savepoint commands are located on the File menu:

To save the current session, select either Save Session… or Save Session As… from the File menu. Save Session… will save to the current active session (thereby overwriting it) whereas Save Session As… will open the Save As dialog for you to choose your own file name.

Session information is written to a file with a .ysav extension. SQLyog employs a SQLite database to keep track of the last saved file’s location.

Automatic and manually saved Session Savepoints can be employed simultaneously. If automatic session restoration is enabled, the last active session will be loaded on startup. Then, any saved Session Savepoints can be accessed via the Open Session Savepoint… command.

Once a saved Session Savepoint has been opened, it may be manually closed by choosing the End Session command from the File menu. That causes SQLyog to stop tracking activity for that session. It will, however, continue to track the session for automatic session restoration if that option is activated.

If, for instance, you are a consultant working with many clients, this is a highly useful feature. You can have many savepoints for each client and get access to all the connections and scripts with a single click. And you won’t confuse what belongs to Client A with what belongs to Client B.

Autocomplete

Autocomplete, or word completion, is a software feature where the application predicts the rest of the word a user is typing. It’s one of the most coveted features in both development and administration software for a couple of very good reasons:

  1. It speeds up human-computer interactions by providing one or more words that the user might intend to enter after only a few characters have been typed into a text input field.

  2. It reduces typos by inserting the exact text of the object that the user wishes to access.

With respect to database administration software, the purpose of the autocomplete feature is to facilitate the insertion of database tables and columns names as well as MySQL keywords and functions into queries and DDL statements.

Available in SQLyog Professional, Enterprise, and Ultimate editions, the Autocomplete feature is made possible by the use of a small database engine that is built right into SQLyog. It contains the names of MySQL keywords and functions and for every connection that it uses. It also maintains a list of the object names for each connection for fast retrieval.

In addition to the two general benefits of Autocompletion stated above, there are very real and tangible benefits when dealing with long and/or awkward object names that are often found in RDBMSes. By reducing the possibility of misspellings, there is far less risk of accidentally executing incorrect queries and reaching erroneous conclusions.

Autocomplete basically works in four independent and differing ways.

1. Auto-Popup or Tooltip

Whenever you type an object name followed by a period (.), a list of available child objects will pop up in a small window. You can then select an item from the list by using the up and down arrow keys to move the selection highlighting and then pressing the tab or enter key to select the item.

For instance, try typing a database name like "sakila." to bring up the list of tables within the Sakila database:

Moreover, once you’ve selected a table, entering another period (.) will bring up the popup list once again with a list of columns within that table:

The Autocomplete feature also works with functions. For example, typing the letters "sub" followed by the ctrl + space key combination will bring up the popup list with functions that match your typed letters:

Typing the name of a function followed by an opening parenthesis “(“ will display the parameter list of that function. Continuing with our previous example, typing “substr(“ will bring up the parameters for the substr() function:

In this case, there are actually four signatures for the substr() function, each with a different parameter list. You can iterate through each of these using the up and down arrows. As you do so, the parameters will change.

The current parameter is highlighted in red.

While writing a routine call the parameter list will highlight the current parameter in red.

The Ctrl + Shift + Space Shortcut

If, for whatever reason, the parameter list goes away, such as when invoking a different autocomplete feature, the ctrl + shift + space keyboard shortcut will always display the parameter list of a routine when the cursor is positioned inside it.

2. Tab Key Functionality

Highlighting a database in the Object Browser and typing in one or more characters in the editor pane will activate the tab key to act as an object iterator. For every press of the tab key, the autocomplete will iterate over all possible inputs that start with that character combination. Inputs are a combination of functions, keywords, and database objects from the selected database.

For example, say that the film table of the Sakila database is selected in the Object Browser. After typing in a value of “fil” in the editor, pressing the tab key once will select the first matching word — in this case, “film”. Pressing the tab key a second time will change the word to the next matching word — in this instance, film_actor. Once the desired word is present on the screen, simply continue typing from there.

The tab key autocomplete functionality in action:

3. Ctrl + Enter Functionality

Similar to the tab key functionality, pressing the ctrl + enter key combination after typing one or more characters in the editor pane will bring up a small window containing a list of keywords and functions starting with that character combination. You can then use the arrow keys to navigate to the one you want. Pressing enter inserts the selected entry.

4. Ctrl + Space Functionality

Whenever the cursor is placed in the editor pane, pressing ctrl + enter always opens a small window with a list of all possible keywords and functions. As above, use the up and down arrow keys to select an item and then press enter to insert the selected item.

Using Autocomplete With Aliases

Since version 6.5, Autocomplete supports table aliases and column aliases:

In the above image, you will see that all Autocomplete functionalities identify "f" as an alias of the film table.

Autocomplete supports both table and column aliases in SELECT, UPDATE, and DELETE statements except in the following cases, which are not supported at this time:

  • SELECT statements used to specify a value for INSERT such as:

    INSERT INTO mytable (col1) values ((SELECT...))
  • SELECT statements used as an argument to a function such as:
    "SELECT IF((SELECT....),'true','false')"

Column-Width Persistence

The Result tab displays the data from SELECT queries in a Grid view by default. It formats the results in Excel-like rows and columns. (Form and Text views are also available.) The Grid view offers a few benefits, such as sorting (clicking on the header sorts the results by that column). This sorting is done on the client side without sending any additional queries to the MySQL server.

The Grid view offers another advantage.

With most other GUI tools using a data grid, you will need to spend time to adjust the width of column headers every time you open the program. In SQLyog, column widths are persistent across sessions and even across master/slave replicas of the same database.

The Result grid:

Conclusion

In this blog, we explored the many advantages offered by database administration tools — in particular, those provided by SQLyog. Its unique features include:

  1. Session Restore, which recalls your previous session along with all the Query tabs, Query Builder tabs, Schema Designer tabs, and the History tab just the way they were before a sudden outage or application shut down.

  2. Session Savepoints allow for the manual saving and restoring of sessions to any number of save point states.

  3. Available in SQLyog Professional, Enterprise, and Ultimate editions, the Autocomplete feature displays suggestions for table, view, column, and routine names, along with their parameters.
    It reduces the possibility of misspellings, so there is far less risk of accidentally executing incorrect queries and reaching erroneous conclusions.

Autocomplete works in four ways:

  1. Whenever you type an object name followed by a period (.), a list of available child objects will pop up in a small window.

  2. Highlighting a database in the Object Browser and typing in one or more characters in the editor pane will activate the tab key to act as an object iterator. For every press of the tab key, the autocomplete to iterate over all possible inputs that start with that character combination. Inputs are a combination of functions, keywords, and database objects from the selected database.

  3. Similar to the tab key functionality, pressing the ctrl + enter key combination after typing one or more characters in the editor pane will bring up a small window containing a list of keywords and functions starting with that character combination.

  4. Whenever the cursor is placed in the editor pane, pressing ctrl + space always opens a small window with a list of all possible keywords and functions.

Since version 6.5 Autocomplete supports table aliases and column aliases.

  • With most other GUI tools using a data grid, you will need to spend time to adjust the width of column headers every time you open the program. In SQLyog, column widths are persistent across session and even across master/slave replicas of the same database.

Learn how to get 20x more performance than Elastic by moving to a Time Series database.

Topics:
database ,dba ,gui ,mysql ,database management ,tutorial ,rdbms

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}