Introducing SQLyog, a MySQL Front End

Wednesday Dec 18th 2002 by DatabaseJournal.com Staff
Share:

With the combination of PHP and MySQL becoming one of the defacto standards for Web development, the need for a good front end for MySQL is increasing. This article takes a look at the basics of connecting to and using one of the most popular MySQL graphical interfaces, SQLyog.

by Insanely Great


Introduction

With the combination of PHP and MySQL becoming one of the defacto standards for Web development, the need for a good front end for MySQL is increasing. For many, the task of creating and manipulating MySQL databases is a daunting task. It is often difficult to master the complex commands required by the MySQL console and MySQL Admin. Luckily there is hope. Due to the ever increasing popularity of MySQL and PHP, many third party tools are being developed specifically for these platforms.

In this article, we will take a look at the basics of connecting to and using one of the most popular of these MySQL graphical interfaces, SQLyog. You can download the latest version at http://www.webyog.com/sqlyog/download.html.


Getting Started

The first thing you get after starting SQLyog is the connection detail window. Just give the details and press Connect.



After connecting to the MySQL server you will see the SQLyog main window.

The first logical step to do with SQLyog is to create a database. This you can do by selecting DB -> Create Database (Ctrl+D). Give the database name and press Create!


Working With SQLyog

The main window of SQLyog is divided into three panes -- Object Browser, SQL Window, Result Window.



The Object Browser gives you details of all the Database/Tables/Columns/Indexes available in the MySQL server. You can Show/Hide Object Browser and Result Window by pressing Ctrl+1 and Ctrl+2 respectively.


Page 2: Executing Queries



Executing Queries

You execute all your SQL commands in the SQL window. To help you out with SQL syntax, SQLyog provides templates for all the SQL supported by MySQL. You can get to the template window by selecting Edit -> Insert Templates (Ctrl+T).

There are three methods of executing queries in SQLyog -- Execute Current Query (F5), Execute All Query(s) (Shift+F5) and Execute Selected Query (Ctrl+F5). If you have written multiple queries and want to execute a particular query, then place your cursor in the respective query and press F5.

When you execute a query that returns a resultset, a new Result tab is inserted in the Result window. If you have executed multiple queries that produce multiple resultset(s), then you will have multiple Result tabs in the window, with the resultset of the respective query attached to it. If you double click on a BLOB column, SQLyog would try to parse the BLOB data to find out whether it is a valid text/image file and then display the contents in a modeless dialog. All popular file formats like (GIF/JPEG/PNG) are supported.



Toggle between GRID/TEXT modes to view results by selecting Edit -> Show Results In Text (Ctrl+L). While in grid mode you can click on the header to sort the results on that column. This sorting is done on the client side without sending any query to the MySQL Server. You can also apply simple filters on the result set by selecting the Filter option in the right-click context menu.



Viewing result sets in text mode is very helpful if you want to cut and paste plain text into another application. While viewing a result set in text mode, you can also search for a particular string in the result set by selecting Edit -> Search from the menu.


Page 3: Exporting Resultset in XML, HTML, CSV Formats



Exporting Resultset in XML, HTML, CSV Formats

Options to export Resultset are one of the most important features you can get from a front end. Using SQLyog, export your resultset in XML, HTML or CSV format. To work with this option just select a valid Result tab in the Result Window and click on Tools -> Export Resultset.



SQLyog lets you overcome the limitation of MySQL's SELECT INTO OUTFILE... that allows you to dump data from a resultset to a file on the server. Using SQLyog you can save the file in your client machine. All options available in the SELECT INTO OUTFILE are presented with a neat GUI interface.

You can even copy your Resultset in Clipboard by right clicking on the Result window and selecting Copy Data To Clipboard/Copy Selected Data To Clipboard.


Insert/Update Data In A Table

Manipulating data is also simplified through the use of SQLyog. To work with the MySQL data, simply click on the table in the Object Browser and select Insert/Update Data from the context menu. In this area, you will see the data displayed in a familiar spreadsheet-like format. To edit an existing record, simply click on the desired field and key in the new information.





Adding a new record is accomplished by keying in new information in the blank row at the bottom. If any of the columns in the table are of BLOB type, SQLyog will show a button that opens up the BLOB editor. Through this you can Insert binary/text data into the table. It allows you to edit existing data, and you can even save the BLOB data as a file into your client machine.


Getting Information About Database/Tables

The Objects tab in the Result Window gives you information about a particular database/table. View the information by double-clicking on the particular object in the Object Browser and information will show up in the Result Window.




Page 4: SQL History



SQL History

As you continue to work with SQLyog, you will notice that SQL statements, along with the time they have taken to execute, are getting recorded in the History tab of Result Window. Apart from logging the SQL that you have typed in the SQL window, SQLyog also logs all SQL that it sends to MySQL to provide you with a GUI interface. This helps a lot in a couple of things like:

  • Profiling - You now know how much time a SQL statement took to execute. This information would be of great help in optimizing your queries, deciding when to create indexes, etc.

  • SQL Tutorial - Looking at this log is probably the best way to learn MySQL! You can find out all the commands SQLyog sends to MySQL for displaying a GUI interface to you.



Creating/Altering Table

You can create a new Table in a database and alter the structure of an existing table in an easy to use Excel like GRID environment. To create a table in a database select the database in the Object Browser and select DB -> Create Table In The Database... or press (Insert).



Just fill in the details and Press Create Table. If you want to provide some advanced properties like Comment/Table Type/Checksum etc., you can do this by pressing Other Properties. It provides you with a dialog box that allows you to provide all table properties.

Similarly, alter the structure of an existing table by selecting the particular table in the Object Browser and select Alter Table Structure... or press (F6). Just change the details in the Grid and press Alter Table. To cancel the changes at any point of time while altering a table, press Cancel.


Managing Indexes

Indexes help the database store data in a way that makes for quicker searches. You can create/edit indexes in a table by using SQLyog's Index Manager. To view the existing Indexes in a table, select the table in Object Browser and select Manage Indexes from the popup menu.



To create a new Index press the New button. Check the column(s) on which you want to create the index. SQLyog is very flexible in index management. You can change the order of the columns by using the Up/Down button and even index on the first "n" characters of a column or make an Index Unique/Fulltext.

To edit an existing Index, select the Index and press the Edit button and apply your changes.


Page 5: Backing Up/Restoring Data



Backing Up/Restoring Data

Backing up and restoring data on your server makes it possible to safeguard the information in your application in case problems occur. When you create a backup, you get a copy of the current state of the database and Web files at the time of backup. Then, you can use that copy to restore the application to that state if any part of the application is lost or corrupted. SQLyog's various backup features allow you to easily backup/restore your critical data.

In the following section we will learn how to backup data in many common formats.

Data As SQL Batch Scripts - SQLyog allows you to take a backup of a database in SQL batch scripts. Click on the database in the Object Browser and select DB -> Export Database As Batch Scripts... or by pressing (Ctrl+Alt+E).



Fill in all the required details and press Export to backup your database. You can restore the backup later by using SQLyog's "Execute batch file..." option discussed later.

Data As CSV - One of the most preferred ways of exporting data of a table chosen by developers is in the CSV format. To export data from table into CSV format select the table in the Object Browser and select Table -> Export Table Data As CSV... or press (Ctrl+Alt+C).



Select the columns which you want to export data from, specify the escaping characters (pressing the Change button will popup the escaping character dialog box), give a file name and press Export. The data exported is compatible with the LOAD LOCAL INFILE option provided by MySQL.

Using MySQL's Backup Option -- Using MySQL's Backup command through SQLyog you can make a SQL level backup of a table. Select a database and select DB -> Backup Database. Select the table(s) and give the directory where you want to export data.

Similarly there are various ways to restore/import data.

Importing From a SQL Batch File -- If you have your data backed up as SQL batch scripts then you can restore it using SQLyog's Select Tools -> Execute Batch File. Give the batch file name and press Execute. SQLyog can execute a batch file of any size (limited only by your OS).



Importing From CSV - You can Import a CSV data into a table by using Table -> Import Data From CSV. Select the table, column(s) and give the escaping characters. SQLyog uses the MySQL LOAD DATA LOCAL INFILE command to import data from CSV. Therefore this option will work only with MySQL 3.23 or greater.

Using MySQL's Restore Option -- The Restore command restores the table(s) from the backup that was made with BACKUP TABLE. To restore a database select the database and select DB -> Restore Database. Next select the table(s) and specify the folder where your table(s) is backed up.


Page 6: Security Management



Security Management


Adding Users

To add a new user select Tools -> User Manager -> Add Users. Provide the username, hostname and password and select the privileges that you want to give to the user. These privileges are Global. You will learn how to give Database/Table/Column-level privileges later in the tutorial. Remember, if you want to give a user permissions at the db/table/column level, don't give him global permissions in this dialog (use the Manage Permissions dialog instead).




Modifying Users

To edit an existing user's Global privileges, select Tool -> User Manager -> Edit User. Modify the required details and press Apply.




Managing Database/Table/Column Level Privileges

SQLyog's Manage Permission tool allows you to give database, table & column level privileges to any user. The Manage Permissions option can be found at Tools -> User Manager -> Manage Permissions (Ctrl+Shift+W).




Documenting your Database Using a Single Click

Many a times during development, developers need to create schemas of databases in standard format. You can do this through SQLyog with just a click of mouse. Select the Database -> Create Schema For The Database in HTML from the context menu (Ctrl+Shift+S) to create a schema in HTML. Moreover, the schema created is completely printer friendly with page breaks given between two tables.




Page 7: Saving Your Frequently Used SQL Scripts In Personal Folder



Saving Your Frequently Used SQL Scripts In Personal Folder

As a PHP developer you likely have some fixed SQL queries that you frequently use. To save you from the hassles of opening and executing them every time, SQLyog provides an option to save them in a Personal Folder. To save a SQL query, select Edit -> Add Current SQL To Personal Folder (Ctrl+Shift+F) and provide a name. When you want it back, press (Ctrl+Shift+P) and select the query name from the popup menu.




Diagnosing Your Tables

To run a quick check on your database and table(s), use SQLyog's Table Diagnostics option which can be found at Tools -> Table Diagnostics.



In the dialog box select the database and the table that you want to diagnose and select the type of diagnosis you want to perform on the table.


Flush Manager

To flush Host/Logs/Privileges/Tables/Tables With Read Lock/Status/All, select the appropriate option from the Flush dialog box in Tools menu.




Conclusion

As you can easily see, MySQL can be made much more user friendly with the use of a graphical client such as SQLyog. One thing that I like about SQLyog is its blazing speed and simplicity. Though it lacks some less frequently used features like Report Designer, it appears to be an indispensable tool for MySQL developers/administrators. Moreover, it is FREE. This tool gives the commercial MySQL front-ends a run for their money.


Back to Page 1


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved