Learning MySQL and MariaDB (2015)
Part I. The Software
At the heart of what is collectively known as MySQL and MariaDB is the server. The term server in this context refers to software, not a primary computer on which it may be running. The server maintains, controls, and protects your data, storing it in files on the computer where the server is running in various formats. The server listens for requests from other software that is running (called clients in this context). The term client refers to software, not a computer. A client and server software may be running on the same computer, which can be a personal laptop computer.
We’ll start by using a command-line client where you type in requests manually. Then we’ll graduate to issuing the requests from programs that can back up web servers and other uses for the data. It’s not necessary for you to know all of the files and programs that make up MySQL. There are, though, a few key ones of which you should be aware.
One key program is the server itself, mysqld (the d stands for daemon and is a common term for a server). The name is the same in both MySQL and MariaDB. This daemon must be running in order for users to be able to access data and make changes. As an administrator, you have the ability to configure and set mysqld to suit your database system needs. The daemon is mentioned where relevant in various chapters throughout this book.
Another key program, used extensively through this book, is the basic MySQL client, called simply, mysql. With it, you can interact with the mysqld daemon, and thereby the databases. It’s a textual user interface. There’s nothing fancy about it — a mouse is not needed to use it. You simply type in the SQL statements that you will learn about in this book. The results of queries are displayed in ASCII text. It’s very clean looking, but no graphics are involved. It’s also very fast, as there’s nothing but text (i.e., there are no binaries or image files). We’ll cover this in Chapter 3. There are GUI clients available, but because most MySQL developers and administrators prefer the mysql client, and what you type in mysql is the same as what is passed to the server by a GUI client, I cover it exclusively.
Chapter 1. Introduction
MySQL is an open source, multithreaded, relational database management system created by Michael “Monty” Widenius in 1995. In 2000, MySQL was released under a dual-license model that permitted the public to use it for free under the GNU General Public License (GPL). All of this, in addition to its many features and stability, caused its popularity to soar.
It has been estimated that there are more than six million installations of MySQL worldwide, and reports of over 50,000 downloads a day of MySQL installation software. The success of MySQL as a leading database is due not only to its price — after all, other cost-free and open source databases are available — but also its reliability, performance, and features. MariaDB is rapidly becoming the replacement to MySQL, and is seen by many as the heir apparent to the spirit of the MySQL community.
If you’re embarking on a career in computer programming, web development, or computer technology more generally, learning MySQL and MariaDB will prove useful. Many businesses develop and maintain custom software with MySQL. Additionally, many of the most popular websites and software use MySQL for their database — or they use another SQL database system that you can learn once you understand MySQL. It’s highly likely that you will be required to know or will benefit from knowing MySQL during the course of working as a database or website developer. Therefore, learning MySQL and MariaDB is a good foundation for your career in computer technology.
The Value of MySQL and MariaDB
Many features contribute to MySQL’s standing as a superb database system. Its speed is one of its most prominent features (refer to its benchmarks page for its performance over time). MySQL and MariaDB are remarkably scalable, and are able to handle tens of thousands of tables and billions of rows of data. They can also manage small amounts of data quickly and smoothly, making them convenient for small businesses or amateur projects.
The critical software in any database management system is its storage engine, which manages queries and interfaces between a user’s SQL statements and the database’s back-end storage. MySQL and MariaDB offer several storage engines with different advantages. Some are transaction-safe storage engines that allow for rollback of data (i.e., the often needed undo feature so familiar in desktop software). Additionally, MySQL has a tremendous number of built-in functions, which are detailed in several chapters of this book. MariaDB offers the same functions and a few more. MySQL and MariaDB are also very well known for rapid and stable improvements. Each new release comes with speed and stability improvements, as well as new features.
Mailing Lists and Forums
When learning MySQL and MariaDB, and especially when first using MySQL for your work, it’s valuable to know where to find help when you have problems with the software and your databases. For problems that you may have with your databases, you can receive assistance from the MySQL community at no charge through several Oracle-hosted forums. You should start by registering on the forums so that you may ask questions, as well as help others. You can learn much when helping others, as it forces you to refine what you know about MySQL. You can find similar resources related to MariaDB on MariaDB Ab’s website.
When you have a problem with MySQL, you can search the forums for messages from others who may have described the same problem that you are trying to resolve. It’s a good idea to search the forums and the documentation before starting a new topic in the forums. If you can’t find a solution after searching, post a question. Be sure to post your question in the forum related to your particular topic.
Other Books and Other Publications
MariaDB provides online documentation of their software that generally applies to MySQL software. Oracle provides extensive online documentation for the MySQL server and all of the other software it distributes. The documentation is organized by version of MySQL. You can read the material online or download it in a few different formats (e.g., HTML, PDF, EPUB). In PDF and EPUB, you can download a copy to an ereader. I maintain a website that contains some documentation and examples derived from my book, MySQL in a Nutshell (2008). Other people have also contributed examples and other materials to the site.
In addition to the book that you’re now reading, O’Reilly publishes a few other MySQL books worth adding to your library. O’Reilly’s mainline reference book on MySQL is written by me, MySQL in a Nutshell. For solving common practical problems, there’s MySQL Cookbook (2006) by Paul DuBois. For advice on optimizing MySQL and performing administrative tasks, such as backing up databases, O’Reilly has published High Performance MySQL (2012) by Baron Schwartz, Peter Zaitsev, and Vadim Tkachenko. At MySQL, Inc., I worked with the writers of both MySQL Cookbook and High Performance MySQL, and they are authorities on the topic and well respected in the MySQL community.
O’Reilly also publishes several books about the MySQL application programming interfaces (APIs). For PHP development with MySQL, there’s Learning PHP, MySQL, JavaScript, CSS, and HTML5 (2014) by Robin Nixon. For interfacing with Perl to MySQL and other database systems, there’s Programming the Perl DBI (published in 2000 and still very useful) by Alligator Descartes and Tim Bunce. To interface to MySQL with Java, you can use the JDBC and JConnector drivers; George Reese’s book, Database Programming with JDBC & Java (2000) is a useful resource on this topic.
In addition to published books on MySQL, a few websites offer brief tutorials on using MySQL. Incidentally, I’ve contributed a few articles to O’Reilly blogs and several other publications on MySQL and related topics. MySQL’s site also provides some in-depth articles on MySQL. Many of these articles deal with new products and features, making them ideal if you want to learn about using the latest releases available even while they’re still in the testing stages. All of these online publications are available for no cost, except the time invested in reading them. If you are a MySQL support customer, though, you can get information about MySQL from their private Knowledge Base, of which I was the editor for many years.
Once you’ve mastered the material in this book, if you require more advanced training on MySQL, MariaDB, or related topics, MariaDB Ab offers training courses. Some are for one or two days, others are week-long courses offered in locations around the world. You can find a list of courses and when they’re offered on MariaDB Ab’s training page. I’m currently the Curriculum Manager for MariaDB Ab.