If you're working a lot with MySQL you're most certainly aware of MySQL Workbench, the official administration & management framework for MySQL DBAs and operators: that suite comes shipped with a lot of useful features, such as:
- DB and DB Schema Design, using a visual and intuitive interface.
- Database Connection Panel and SQL Editor, to manage connection against local & remote DB and to design/execute queries using the GUI or manually.
- Backup and Migration tools, useful to import/export DB and tables between other database servers/engines.
- Performance Dashboard, to monitor in real-time the performance of your database server.
We're talking of a bunch of great features that will cover most - if not all - the requirements of any decent MySQL developer and/or administrator: if you want to get a solid grip of your MySQL servers, installing the MySQL Workbench on your development machine is almost always a good choice.
However, there's a still rather unknown alternative that we can use instead of the aforementioned Workbench - or, even better, together with it: we're talking about SQLyog, a small yet great piece of software developed by Webyog and available in multiple editions: Enterprise, which is available through Trialware and shipped with many useful administrative modules for IT experts and business companies, and also Community, mostly aimed to software developers and free for personal use.
The GUI interface is what makes SQLyog great: simple, clear and finely tailored around the developer's needs. It also features a practical Address Book, very similar to the one available in client softwares such as FileZilla or TeamSpeak: we can use it to create entries that, once added, will allow us to connect to the destination server with a single mouse click. Once connected we'll be able to write and send our own queries, just like the Connection Panel shipped with the MySQL Workbench but - at least in my opinion - with a better editor and a superior syntax highlighting.
The query editor isn't the only thing that SQLyog can do: here's a brief list of the features I love the most, mostly because they saved me a lot of precious time in a number of occasions:
- Copy Database to different Host/Database. A powerful command that allows us to copy any source database to any destination database, even if they're on different MySQL servers, as long as we're connected to both of them. We can choose to copy only the DB structures (empty tables) or structure + data: if we choose to copy the data, we can also manually select which tables, views, stored procedures, functions, triggers and/or events to copy. The copy performances are also great, to the point that this command can be also used to do regular backups.
- Backup/Export > Backup Database as SQL Dump. As the name suggest, this command allows us to create .sql scripts from any database: again, we're given the chance to choose what to export (tables, views, stored procedures, functions, triggers and/or events) and a number of other useful customization options, such as: lock tables during dump, create bulk INSERT statements and more. Needless to say, this is a great command to backup a database to disk.
- Import > Execute SQL Script. This command can be used to execute a series of SQL statements within a given database, including - yet not limiting to - those created with the previous export command: it can be used to "restore" from a previous backup, to execute batch-generated queries, and so on.
- Tools -> Table Diagnostics. This is a collection of shortcuts that we can use to launch/perform the most common management and utility commands to the selected database: optimize, check, analyze, repair and so on: every one of them can be configured using a modal window that allows us to select the most common switches for each command.
- User Manager. A great user management interface allowing us to add/remove users, change their credentials, assign global or db-specific permissions and (almost) everything else we usually need to do regarding DB users.
- Show > Processlist. This is an high-level control panel that we can use to monitor the active processes with their corrisponding ID, Utente, Host and DB, command executed, execution time (in seconds so far), status info and other process details. It can be also used to issue a kill for each process (right-click > Kill Process).
I could easily add more points to this list, as the software is full of useful features: on top of that, all of the above ones comes unlocked (and fully usable) with the Community edition: if you're willing to pay for the Enterprise Edition you will also have access to a bunch of additional tools, such as: Database Synchronization Wizard, Visual Data Comparison Wizard, Scheduled Backups/Scheduled Jobs and Query Profiler, just to mention a few of them.
let alone the features, what I like the most in SQLyog is the great care given to the end-user approach: this is a software that I like to use not only because it has everything I'm looking for, yet also because the options are precisely where I need them to be: this is something extremely valuable, which makes SQLyog query editor - in my humble opinion - even better than MySQL Workbench. If you appreciate the GUI layouts of softwares such as Thunderbird, Photoshop and Visual Studio you will most likely like SQLyog too.
USEFUL LINKS
- SQLyog Community Edition and Download Page (both on Github)
- SQLyog Trialware Edition (official site)
