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
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.
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
Page 2: 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
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
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
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
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
Page 4: 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.
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.
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
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
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
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).
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
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
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.
To flush Host/Logs/Privileges/Tables/Tables With Read Lock/Status/All, select the appropriate option from the Flush dialog
box in Tools menu.
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