Data Management

TechRepublic Tutorial: Use SQLyog to manage remote MySQL databases

Manipulate remote MySQL databases with SQLyog

The pricing and licensing model for MySQL, the open source database of choice, makes it attractive to ISPs that want to offer customers a database back end as part of a hosting package. If you're planning to develop dynamic e-commerce Web sites for your clients, MySQL skills should be in your arsenal.

The MySQL database itself is SQL-compliant and can be manipulated from a prompt, much like Oracle’s SQL*Plus or the old command line ISQL for SQL Server. The problem becomes how to get a prompt. If you're working on a MySQL database for a client, the database probably already resides on the ISP’s server. If, like me, you have MySQL running on a Linux box somewhere on your office network, sitting at the machine is not always an option. To help you find a prompt, you can use SQLyog, which is designed to allow remote manipulation of MySQL databases from a Windows GUI application.

Obtaining SQLyog
SQLyog is available via trial download. The most recent release (SQLyog 3.62) is completely MySQL 4.1 compatible as well as backward compatible. The trial version is good for 30 days, after which you can purchase it starting at $49 per license. A registration code unlocks the trial version for continued use. As an indication of my feelings about the product, I purchased it before the trial period was up.

Getting connected
The SQLyog installation takes up 982 KB on my machine. The only beef I have is that an .ini file contains non-encrypted connection information, including IDs and passwords. Otherwise, the installation went smoothly. The first window you'll have to deal with is the connection window shown in Figure A.

Figure A
You'll need to supply the address of the host on which the database resides, a user name and password, and a database name.

SQLyog will try to use port 3306 by default. If you're working on a machine on your own network, the login information can be relatively simple to come up with. If you're trying to connect to a client’s database on a server belonging to the client's ISP, you're going to have to spend some time on the phone with the ISP’s support staff.

Please note that if the MySQL database is on a UNIX or Linux machine (as is often the case), you must be able to log in to the machine as well as the database. For a remote machine, the ISP's support staff will have to grant you access. If you have your own test machine on a LAN, make sure the appropriate records are in the host and user tables in the main MySQL database. If this seems a little hazy, remember the assumption is that you know your way around MySQL already. You must understand MySQL security and the GRANT and REVOKE commands.

You can create as many connections as you want to databases in different locations, and select the one you want to use from a drop-down list, much as you would in a typical FTP application. Once you've established a successful connection to a MySQL database, you're ready to take a look around.

The main SQLyog window
When you get connected, the main window opens, as shown in Figure B. Things should look familiar to anyone who has used SQL Server’s Query Analyzer. SQL commands are entered in the SQL Editor and executed, with the results appearing below. As you type commands, six-color syntax highlighting is applied. The tabbed results area shows database messages (such as errors or counts of affected or returned rows) and stores a list of recently executed commands.

Figure B

The nicest feature of the main window is the Object Browser, which displays all the tables in the database in a tree view. By expanding each table’s tree, you can view all the columns in the table along with their data types and NULL/NOT NULL properties. All indexes, such as primary keys, are also listed. Double-clicking on the name of a table in the Object Browser displays all the information about the table on an Objects tab in the Results pane. Included are extra properties such as any autoincrement key fields and the CREATE TABLE command used to actually create the table. You'll never have to issue another DESCRIBE command. Command results can be displayed as text or in a grid similar to that in the datasheet view in Microsoft Access.

With a table selected in the Object Browser, you have access to menu commands that allow you to alter the table’s structure, manage its indexes and relationships, and import and export table data. Result sets can also be exported. There are toolbar icons to copy a database, manage users, and even create an HTML file of the database’s schema. The SQLyog main window manages to combine most of the features of SQL Server 7.0’s Query Analyzer and Enterprise Manager in one window.

Help and support
If you click on Help in the menu of the main window, an HTML file from Weblog opens in your browser. Pressing [F1] opens the same file, but the help is not context-sensitive. The site also contains a FAQ section and a users’ forum. As is the norm in the open source world, you had better be good at searching the Web for your answers. Yet the open source community seldom disappoints. A Google search for the word SQLyog returned 75,000 results. A more meaningful search such as import + SQLyog returned just over 1,500 results, some of which took the form of long, detailed instructions in downloadable .pdf files.

Basically, SQLyog support is appropriate for the price at which it is offered. However, don’t expect operators to be standing by to take your call.

Editor's Picks