This is the first in a series of posts on MySQL® for those new to the database, or those migrating from another DBMS.
So you've made the decision to try MySQL. Now you just have to get it installed. Luckily for most purposes MySQL is quite often already available. If you have a Linux installation then chances are that both the server and client are installed. If you are planning on using MySQL for your website, chances are the hosting provider gives you several MySQL databases for your use.
Before diving in too deep though, let's get some background and terminology out of the way.
What is MySQL?
This depends. MySQL was a company, is a trademark, is an ecosystem and is the name of arguably the most popular relational database management system (RDBMS) on the planet. Originally developed by MySQL AB, MySQL (both the database and the trademark) are now owned by Oracle Corporation. But the story doesn't stop there. Because the MySQL code is released under the GNU Public License (GPL) v2.0 many other companies and individuals have contributed to the code, either releasing plugins, patches, tools or even code forks (new versions of the code that share a common inheritence, but are not controlled by the original company).
Why do I need it?
Virtually all programs are a combination of code and data. The data part can be quite simple, or quite complex. For simple applications you can easily manage the data in your code, but for more complex applications the code required just to manage the data can become a large percentage of your entire codebase. This is where a DBMS (Database Management System) comes into play. It is both a storage and retrieval system and code hooks to allow application programmers to worry about their application and not about their data. MySQL, like many DBMS uses SQL (Structured Query Language) to provide the interface between the application and its data. But more on SQL in a later post.
Most websites also have data requirements. This blog site for instance uses code that allows me to edit my posts and categorise them, and share them with the world. All of the words you see are stored in a MySQL database. MySQL is FTW! (For the Web, as well as For the Win).
Client? Server? What are these?
MySQL generally refers to the server - the piece of software that actually stores the data on disk and retrieves it for you. It can get confusing though as the CLI (Command Line Interface) utility is also called 'mysql'. Where it matters I'll refer to mysql (note the lower case) when referring to the CLI, and mysqld (note the d) when referring to the server process. The mysql CLI is not the only client though. Your application can also be a client, and talks to the server using an API (Application Programming Interface). For instance you may see reference to libmysql or libmysqlclient, which are the C programming libraries that implement the MySQL API. Languages like PHP, Java, .Net, PERL and Python will provide methods to talk to the MySQL server, usually in a module called a Connector. On the mysql.com website you will see references to Connector/Net, Connector/J, etc.
The MySQL server process, mysqld, can run on the same computer as client programs or it can be remote. Should your server be remote you may only need to install the client libraries or connectors for your application or language.
Community vs Enterprise vs GA
One of the stumbling blocks you will find when looking on the web for MySQL is the use of the terms GA, Community and Enterprise. GA is an indication of the build quality of the code. It is an abbreviation of 'Generally Available'. Many other products use the terms 'Production Ready', 'stable' or some other indication of the code's readiness for production code. You don't want to run a pre-release version which is marked as 'alpha', 'beta' or 'rc' (release candidate). GA is the "stamp of approval" that gives you that confidence.
The 'Community' vs 'Enterprise' is a slightly different proposition. 'Enterprise' is in fact a product that includes both the software and support and update services for MySQL. 'Community' refers to just the software. You can download 'Community' versions freely, and up until recently there was no difference between the code downloaded as 'Community' and that provided with 'Enterprise' - the only difference was that you got services with 'Enterprise' that you didn't get with 'Community', which is why there is a price for 'Enterprise' and not for 'Community'.
OK, back to business. You've got your website or your application all mapped out and now you need to get MySQL installed. Where do you start? As mentioned it may be simpler than you think.
On most Linux distributions MySQL will be available via the package management system in use. There are two main package types in general use: RPM and DEB. RPM was originally developed by RedHat and is used by many distributions such as RedHat, Fedora, SuSE and CentOS. DEB is used by Debian-based distributions such as Debian and Ubuntu. Generally the packages are named similarly, 'mysql' for the client and 'mysql-server' for the server. There may be other packages such as 'mysql-common' for the common libraries. Fortunately you don't need to worry about that much as the package managers deal with dependencies for you.
So, on a Debian based system like Ubuntu system you would use something like:
sudo apt-get install mysql mysql-server
This installs both the client (CLI) and server components, and any dependencies required for them to work.
On an RPM-based system like Fedora you would use:
sudo yum install mysql mysql-server
Simple! You now have the server and a command line utility installed.
You may be wondering why there are downloads on various sites, including mysql.com of packages for Linux distributions if you can just install it from the packages supplied by your distribution. The main reason is that the official packages tend to be more recent and have bug fixes that may not yet be available in your distribution release. There are problems with some of these though. For instance it has been a nagging issue in the RedHat releases that the official packages have different dependencies to the RedHat supplied packages. This means that you cannot cleanly upgrade an already installed RedHat system with the official packages. This may be resolved by the time of writing, however it has been an issue for a number of years. So if you need the latest version, make sure you don't have the distribution release versions installed first, or use the .tar.gz download instead.
Windows is fast becoming a major platform for MySQL. In fact the vast majority of downloads of MySQL are for Windows. However Windows doesn't have a packaging system like Linux distributions that collect together packages from a variety of vendors using a single tool. Instead you need to find and install a package from a trusted source. For Windows there are two types of install, one is the MSI (Windows Installer) format, and the other is often referred to as the 'No Install' package. This is a ZIP file containing the components but without a Windows Installer. You can run MySQL directly with this package, but it takes a bit of understanding. For most users, download the .msi (Windows Installer) package and execute it. It will take you through the installation steps required.
One thing you must remember is that when you go through the Configuration Wizard you will be asked for a password. Make sure you remember this as this is required for administrative access to the database, and to the command line utility. If there is enough interest I'll do a step-by-step through the Installer and Configuration Wizard in a future post.
The number of web hosting packages out there is mind blowing, and it is impossible to cover all possible variations. In general if you have a control panel for your hosting package there will be a way to set up a database and database user and there will be instructions on how to configure your application to talk to it. If in doubt contact your web hosting company for support. If they don't provide MySQL services then you may need to change hosting companies.
Where do you get it?
You can get MySQL and its derivatives from a number of places:
If you want a pre-configured installation for a variety of environments, you might like to try out the SkySQL Reference Architecture
The next issue will be talking about how connections are made to MySQL and configuring connections and logins.
I am employed by SkySQL Ab, which provides support and services for MySQL, MariaDB and Drizzle and is staffed by many ex-MySQL AB employees. While I try to be unbiased in all of my public opinions, and my opinions are all my own and not those of my employer, I work for SkySQL because I believe in what they are doing and therefore cannot be truly unbiased.
MySQL and 'MySQL Enterprise' are registered trademarks of Oracle Corporation, MariaDB is a trademark of Monty Program Ab.