MySQL Database Server on the Linux Desktop

MySQL Server is a very powerful yet light and quick database engine. It is a widely spread standard among developers, capable of running fairly big databases in professional environments, but also very convenient for medium to small projects. In fact, it is about the latter I want to talk about in this article.

Before I started using MySQL, being still fairly new to Linux and still keeping a "Windows user" mindset, I was under the impression that setting it up would be terribly difficult, or perhaps too heavy or resource consuming for a standard desktop/laptop. I couldn't have been more wrong, for the installation and set up were as easy as it gets. If you are into development or simply want to taste what MySQL can offer, you should definitely give it a try. Let's see how.

THE BASICS

MySQL is tightly integrated in pretty much any Linux distro (at least in all I have tried so far), and it works very well with many in the most popular ones. While SQL knowledge is required to work with MySQL, the GUI tools available are great and definitely ease up tasks when starting up. There are two I find particularly interesting: MySQL Administrator and Query Browser.

MySQL Administrator

This fabulous aplication is a great manager for a huge variety of things MySQL server. From creating a database (catalog), a user or a table, to granting access, modifying privileges, setting up passwords, etc.

MySQL Query Browser

Similar to other popular SQL clients, MySQL Query Browser provides a graphical SQL interface which includes documentation, like a pretty thorough SQL syntax reference, query history, table trees and many more features. This tool is obviously perfect to work with the MySQL server, allowing a great deal of functionality with little SQL knowledge required. For example, you can simply drag and drop on of the table diagrams to get generic SELECT, INSERT, UPDATE or DELETE statements.

INSTALLATION

Installing MySQL doesn't require any specific knowledge or skill, it is as simple as installing any other application. I have installed it in Mandriva, Ubuntu, Fedora and PCLinuxOS at different stages and it was always a piece of cake.

GUI installation

When installing from your favorite package manager, be it the Ubuntu Software Center, Synaptic Manager or any other, you simply need to search and find the right packages, like this:

1.- Search for "mysql" and select and install the MySQL server (usually labeled "mysql-server") package.

2.- Search for "mysql admin" and select and install the MySQL Administrator (usually labeled "mysql-admin") package.

3.- Search for "mysql query" and select and install the MySQL Query Browser (usually labeled "mysql-query-browser") package.

Note that dependencies will be highlighted and selected for you, simply install them as you go.

CLI installation

From a terminal, run the following commands as root:

apt-get install mysql-server mysql-admin mysql-query-browser

You should then get dependencies automatically fetched and installed for you as well.

INSTALLED OK... NOW WHAT?

MySQL Server, Administrator and Query Browser are very powerful tools, potentially complicated, but I'd like to show a bit of what can be done with them, perhaps how to get started.

MySQL server console

First off, let me say that MySQL server includes a command line interface which provides all of the functionality offered by the Administrator and Query Browser. To start it up, open a terminal and type the following command:

mysql -u root -p

The command right above basically logs you in to the MySQL server console as root (MySQL root, not to be confused with the system user account). When you click enter, you will be prompted for the root password, which you should have set during the installation. If that was not the case, root probably was setup without a password, so you should set one up yourself.


Logging in to the MySQL console

This article is a high level introduction to MySQL, so I won't get into details about this console, just wanted to let you know it is there.

First steps with MySQL Administrator

To start the MySQL Administrator, find the development category in your applications or main menu and launch it from there. You will get a login screen as shown below:


The access to MySQL Administrator defaults to localhost and root as expected.

Once again, enter the password you set up during the installation process or leave it blank if you did not. The MySQL Administrator provides lots of interesting features, such as the nice visual monitoring interface shown below:


The MySQL Administrator monitor.

To create a new database, click on the lowest item on your left menu ("Catalogs"). An applet will appear on the lower left part of the application interface (labeled "schemata"), showing the schemas available. The "mysql" schema should be the only one showing after installation.


The MySQL Administrator Catalogs.

To create a new schema simply right click on the schemata applet and select "Create Schema". You will be prompted for the schema name.


Create a new DB easily from this screen.

Once created, select the schema and you will see how the main applet is enabled, allowing you to create new tables.


Creating tables is equally simple.

As you can imagine, creating your whole DB structure this way is quick and simple. Once done, it is a good idea to create a user who will have access to the new DB and tables, as opposed to using root for all tasks, which is not recommended.


Create new users, set passwords and/or privileges from this screen.

First contact with MySQL Query Browser

Once again, from the Development subsection under the main menu, open MySQL Query Browser and log in as root or as any other user you may have created.


MySQL Query Browser Login screen.

The main interface depends on which schema was chosen when logging in, for that will condition which tables are shown on the right panel. If you logged into a schema whose tables already contain data, you can very simply query them by dragging and dropping that table on the main applet, as shown below:


Running SQL predefined commands are a breeze in MySQL Query Browser.

Note that I ran a SELECT command there, but you can choose from a number of predefined commands, which are enabled before you drop the table.

Loading data into your tables is very simple, with both manual input and flat files as potential sources. I will not go into details about the whole process, but it is very clearly explained on the official MySQL Manual, which you can download from HERE.

TRY IT OUT!

So there you have it, easy enough and very powerful. Is it for you? I guess it depends on whether you work with databases, but my aim was to show that installing and configuring MySQL Server is extremely simple and convenient under Linux. With the right GUI tools, it can also be easy to use.

Give it a go and have fun!