If you’ve landed here, it’s probably because you’ve heard about the benefits of using MySQL as a database. Perhaps you’ve already decided that you want to run a blog, forum, or whatever, on your server – and have come to the inescapable conclusion that you’re going to require a database, such as MySQL, to make it happen.
There are a ton of installation guides on the web around MySQL, but I wanted to give you a greater understanding of “MySQL”, including a general insight into the platform itself, and also provide you with some invaluable links which will help you get started.
MySQL Topics Covered in this Post:
- MySQL is not a single product!
- Percona DB Server
- The Classic MySQL Setup
- The Basic MySQL Components
- After Setup, the Tricky Part
- Notes on Cluster, Connectors and Code
- Glossary of SQL and Related Phrases and Terminology
- Why MySQL? What are the Benefits of MySQL?
- Ralf’s MySQL Cheat Sheet
MySQL is not a single product!
First of all: “MySQL” is not a single product, even when referred to in that way. It is more like a software environment made up of various components, the exact combination of which varies for each of the various platforms out there. The original MySQL software packages were born on Linux in the 90s, evolved over the years and were finally bought out as a company by Oracle in 2009. Many of the open source developers were NOT happy with binding that independent software to a corporate giant like Oracle, which resulted in various clones and alternatives being available on numerous platforms since late 2009. The most famous of these variants is MariaDB, which was forked off the original source code by the author of the original MySQL code.
Percona DB Server
Also pretty successful is the cool PerconaDB server, which was optimized to solve specific performance issues that plagued the standard MySQL distributions.
If you are digging deeper into the topic of MySQL, the name Percona will pop up a lot. I know these folks personally and – when it comes to MySQL – they are REALLY good at what they do, and so into it that they even border on the fanatical at times
The Classic MySQL Setup
First let’s talk about the classic MySQL-Setup which made the database so successful in the first place:
- Part of a Linux Distribution (as an RPM) or an xAMPP package (for Windows)
- Automatically installed with SuSE, Red Hat, CentOS, etc.
- Required by famous applications like WordPress, phpBB, etc.
In these variations, the MySQL installation process is mostly handled automatically by an installer program, which makes every step easy for you! Instead of downloading a source package and compiling it, these standard setups work as follows:
a) Linux Distribution: you select the MySQL package (probably a RPM) and an installer does all the complicated installing and makes sure all the various components are properly set up. Oracle provides binaries for the major distributions.
b) Windows or Mac: you either get the single MySQL distribution for it (Oracle provides one with a Windows installer now) or, alternatively, you use WAMPP (Windows Apache MySQL Php Package) or MAMP (the complete Mac package). If you just want to learn about MySQL and prefer to not spend too much time in a steep learning curve: the LAMPP or WAMPP packages are the fastest way to a complete and clean installation. I’m using one on my laptop to develop, too!
The Basic MySQL Components
After installation, you will have these basic components on your machine, which we will focus on here:
- A server, which allows connections and receives your SQL statements
- A client, which connects to that server and allows you to interact with the database
- A configuration file, which defines the performance, amongst other configurable settings.
Depending on your level of database expertise, you’ll have a variety of additional tools with which to interact with the server, such as MySQL Workbench – which includes a configuration wizard, etc.
For some basic hints and tips, let’s assume you have already installed MySQL, either with WAMPP or via your Linux distribution, without running into any problems.
If you want to play around with a fresh MySQL installation, I strongly recommend the xAMPP packages. There are packages available for Linux, Mac OS X and Windows, which are quickly installed on a laptop and come bundled with invaluable tools (especially phpMyAdmin, which is the best web interface for it).
After setup comes the tricky part:
Namely, anticipating the usage of the database, its growth path, upgrading necessities and major tasks along your development.
If you want to run a blog on MySQL, there isn’t really much you need to do. The speed and stability of your server depends mostly on the configuration of WordPress and your webserver. But if you want to build up a large forum or a store with millions of database entries, there is no easy way or shortcut – you’ll have to learn as much as you can about the configuration!
The database saves its data in files. It selects the data you request along the SQL (standard query language) statements you send to the server. That process can – and should – be tweaked and optimized.
YouTube is a great source of MySQL learning resources. This YouTube tutorial is great for beginners.
The files can be clustered and the server can hold a lot of data in the memory, etc. These are topics to spend a few months getting involved in! Most of the stuff I know I picked up from the guys at Percona and their MySQL performance blog.
Some of the aspects I have learned, I have already summed up here.
Notes on cluster, connectors and code
With the ongoing “explosion” of data in our time, the need for structured management and storage of information goes far beyond what a standard installation of MySQL can help you with. If you want to manage your 2,000 items with a database, MySQL is probably already overkill. If it comes to clustering a database setup for a growth path, the standard install will not help you.
MySQL, MariaDB or PerconaDB have evolved into enterprise level solutions that telecom companies use to save millions of connections. There are API interfaces and connectors for ODBC, perl, PHP, Java, etc. and specific storage solutions for specific usage scenarios.
In an earlier section, I wrote about small cluster setups or using full-text search on sphinx. If you are digging into the wonderful world of RDBMS (relational database management systems), you will find that there is much here to learn.
When starting out, learning MySQL with the above mentioned packages is the perfect entry into that vast world. If you are looking for VERY large setups, you would be better off looking into the Hadoop solution and taking a different approach than just focusing on the database. Scalability, then, directly depends on your complete architecture – from code to hardware – rather than your database alone.
Glossary of SQL and Related Terminology
This section covers some useful phrases you may come across in the world of MySQL – and IT in general!
|Windows||Windows is Microsoft’s own operating system, currently on version 8, notable for supporting not only desktops and laptops but also tablets.|
|Mac OS X||OS X is the operating system designed by Apple specifically, and exclusively, for its own Apple Mac desktops and laptops.|
|Linux||Linux is an open source UNIX based operating system. It comes in many flavors (also known as distribution packages) and evolved from a single, solitary kernel written by Linus Torvalds in 1991.|
|directory||A directory is a file that consists purely of a chain of other files. A directory is often called a folder, most notably on the Windows operating system.|
|sudo||Sudo is a command on UNIX based operating systems that allows the logged in user to temporarily gain the security permissions of another user (such as the root user) in order to run a specific application. Sudo is used because it is considered bad systems admin practice to use the superuser or root user for general “everyday” tasks, due to the ease with which mistakes can result in data loss.|
|Google is an internet-based service provider that offers a search engine, mailbox, online word processing and many other services. Google Search is the most popular search engine in the world.|
|install||To install a software package, such as an application or driver, is to unpack its distribution or binaries archive and copy the component parts to the required locations on the target system. To ensure files are unpacked and copied to the correct destinations, installation is usually an automated process, handled by an installer. Microsoft offers its own installer authoring package called MSI, which automatically copies files to the appropriate directories as per the author’s instructions.|
|server||A server is a host computer hardware system dedicated to running one or more services, to serve the needs of the network users. There are many types of servers, depending on the purpose of the application – database servers, file servers, mail servers, web servers, gaming servers, etc.|
|source||The source is the original code that an application was written in. Acquiring the source code for an application means it can be modified to meet new requirements. Open source software is released to the community for this very reason.|
|configuration||Configuration is the process of arranging variable elements in order to achieve the best possible performance or harmony with regard to a specific application/purpose. In computing terms, configuration or configuring of computer systems (such as servers or client terminals and workstations) is usually handled via configuration files. These are small text files that contain a list of settings and values. Usually the first task of a system upon startup is to execute the configuration file.|
|php||PHP is an open source scripting language designed for web coding. It is executed server side and used to produce dynamic content web pages.|
|file||A file is a container of data, organized into a single component to facilitate transfer and transmission, and eventual download from the internet. Files come in a number of different file formats, depending on the intended use and host application.|
|database||A database is a structured, organized collection of data stores that is based on a data model. The purpose of a database is to facilitate the fast and easy access, writing and updating of millions of individual pieces of data. The most common type of database is the tabular, relationship database.|
|distribution||A distribution, in the context of computing, refers to the process of releasing upgrades to existing systems. Linux distributons (for example) are commonly separated out into individual packages, to make them easy to transmit for download over the internet.|
|upgrade||An upgrade refers to the process of replacing outdated or defunct software/hardware with a new version or model, which usually boasts improved performance and/or enhanced features. The opposite to upgrading is downgrading. Downgrading is the process of reverting, or rolling back, to an older software version (often carried out due to incompatibility or unforeseen issues).|
|usr||A USR configure file is a computer file that ends with the suffix USR and is a user configuration file for applications that utilize a database.|
|UNIX||Unix is a multitasking and multi-user computer operating system developed in the first instance in 1969 by a team of AT&T employees at Bell Labs. Since that time, many individuals have contributed to its development and continued success. UNIX is the base for many operating systems, including Linux and Apple’s OS X.|
|solaris||Solaris is a UNIX based operating system that was originally developed by Sun Microsystems, superceding their earlier 1993 SunOS. Today it is officially called Oracle Solaris. Opensolaris was an open source OS based on Solaris that was discontinued by Sun Microsystems in 2010.|
|ubuntu||Ubuntu is a free, open source OS based on the Debian Linux distribution. It comes bundled with its own GUI desktop environment and is named after the Southern African philosophy of ubuntu – “humanity towards others”.|
|terminal||In computing terms, a terminal is a hardware workstation through which data can be accessed, written or modified. A “terminal window” is a GUI-window shell containing a command line interface through which commands can be entered directly, thus bypassing the GUI layer of the OS.|
|local||Local refers to the immediate environment, such as the terminal currently being used, and its physical storage. A local file would be one stored on the computer currently being used, as opposed to on a connected network drive.|
|command||A command is an instruction. A user with the appropriate permissions uses a command to tell a computer to do something, for example run a program or a series of programs.|
|perl||Perl is a high-level, dynamic programming language that borrows elements from other programming languages such as C. It offers powerful text processing facilities without the limitations of many Unix tools. A complete overhaul of the language is in the works, which was originally announced in 2000.|
|msi||An MSI installer is a Windows based installation and configure system for unpacking files and copying them to the relevant directories, prior to running an application.|
|path||A path is the full resource indicator to a file. It is also known as an address and contains a list of all the directories that must be traversed in order to reach that file.|
|address||The address is the resource indicator to a file stored on a network or on the internet. It is sometimes referred to as a path. Addresses can be full or relative to the current location.|
|tables||Tables are the bread and butter of relationship databases. A table is a set of data elements, or values, organized using a model of vertical columns and horizontal rows. Cells are used to store individual data elements along where these columns and rows intersect with one another. The map of database tables and how they relate to one another is called a schema.|
|bug||A bug is a defect or fault in a software application. Developers spend much of their working day chasing down, and fixing, bugs.|
|regular expression||Also known as a regex, a regular expression is a method of matching or comparing strings of data. It is commonly used in search features in web applications, when returning possible page results based on a query entered by the user.|
|primary key||A primary key is a unique identifier for each row in a database table. It is the only data in a table that must be unique.|
Why MySQL? The Benefits of using MySQL
As web and network usage proliferates and becomes more indelible and ingrained in our every day lives, relationship database systems become even more indispensible. Here’s a few reasons why I think choosing MySQL is a good choice:
- Inexpensive – for individual users, MySQL is free, and even the commercial license is far more affordable than (for example) Oracle, PostgreSQL or SQL Server.
- Solid Data Recovery Support – corrupt data is always an issue for database administrators, and in the event of an unexpected shutdown, the risk of this is high. MySQL offers a comprehensive set of reassuring recovery tools that allow you manage your important database with confidence.
- Heavily Customizable – the open source GPL license of MySQL means you can, if you have the development know-how, fully modify MySQL to meet your specific requirements (or the specific requirements of your business).
- Multi-platform Support – MySQL is available on Windows, Linux, Mac OS X, UNIX and even FreeBSD (MySQL servers only function on Windows-based applications, however).
- It’s SIMPLE! – It’s relatively easy to get started with the SQL language and build and configure your own database. All you need is a little time and perserverance!
Ralf’s MySQL Cheat Sheet
Refer to this MySQL cheat sheet when your database is up and running!
Show all available databases in database server:
Lists all tables in a database:
Modifying the structure of tables is easy in SQL. Try these!
Add a new column into a table:
Delete an existing column in a table:
Add a primary key into a tables:
ADD PRIMARY KEY [COLUMN_NAME, PRIMARY_KEY_NAME]
Counting the number of rows is a useful function in SQL:
SELECT COUNT (*) FROM table_name
And so is searching for data based on a LIKE statement:
SELECT * FROM table_name WHERE column_name LIKE '%value%'
Or using a regular expression:
SELECT * FROM table_name WHERE column_name RLIKE 'regular_expression'