Learning MySQL and MariaDB (2015)
MySQL is the most popular open source database system available. It’s particularly useful for public websites that require a fast and stable database. Even if you’re not familiar with MySQL, you’ve used it many times. You use it when you use Google, Amazon, Facebook, Wikipedia, and many other popular websites. It’s the keeper of the data behind huge websites with thousands of pages of data, and small sites with only a few pages. It’s also used in many non-web-based applications. It’s fast, stable, and small when needed.
The software was started by Michael “Monty” Widenius and David Axmark in 1995 and is licensed under the GNU General Public License. In time, they founded the Swedish company MySQL Ab (the Ab stands for aktiebolag, or stock company), which years later became MySQL, Inc., incorporated in the United States. In January 2008, the company was acquired by Sun Microsystems, which seemed promising for the future of the software. But in April 2009 Oracle — a major competitor of MySQL that offers closed source database software — acquired Sun. Many worried at the time that this acquisition would eventually end MySQL software as a free, open source alternative on which much of the Web and many sites that have changed the world were built. Five years after the acquisition, this hasn’t proved to be the case. Many new features have been added to MySQL and the number of MySQL developers within and outside of Oracle has increased.
Displeased that Oracle took control of MySQL software, Monty started a new company (Monty Program Ab) that has developed a fork of the software called MariaDB. Because MySQL software is licensed with the GPL, it is possible to freely and legally use the MySQL software and add to it. At the same time, Ulf Sandberg, the former Senior Vice President of Services at MySQL, Inc., along with other former employees of MySQL, left Sun and Oracle and started SkySQL Ab, providing support, consulting, training, and other services related to MySQL and MariaDB software. As of October 2013, Monty Program has merged into SkySQL, which was renamed to MariaDB Ab in October 2014. The software license, though, is now held by the MariaDB Foundation so that it cannot be bought by Oracle or any other corporation.
As for the community related to the software, some have been migrating to MariaDB, preferring software not associated with a large proprietary software company. Many operating systems distributors, hardware makers, and software packagers are now shipping their products with MariaDB, either together with MySQL or without it. Many websites that used MySQL software have switched to MariaDB. It’s easy to do, and for most sites it requires no changes to applications that use MySQL — not a single line of code needs to be changed to switch to MariaDB. If you want to take advantage of new, advanced features of MariaDB, it is necessary to add or change code in an application that previously used MySQL, bu the rest is the same.
Although ownership, company names, and even the name of the software has changed, the vision that began almost 30 years ago and the spirit that has grown strong and vibrant in the community is the same and continues in MariaDB.
If you want to learn about MySQL and MariaDB software, you can do it. It’s not difficult to understand or to use. This book has been written to be a primer for newcomers to MySQL and MariaDB, to get you started and help you be productive quickly. It’s also useful for beginners who have learned only parts of MySQL and feel that there may be key aspects used commonly that they don’t know, that they somehow missed or skipped over when first learning it. At the beginner level, there is no difference between MySQL and MariaDB. So when you learn one, you learn the other. Because of this, the names MySQL and MariaDB are used interchangeably.
The chapters of this book are written and ordered based on the assumption that the reader will read them in order. This does not assume that some chapters won’t be skipped; it’s assumed that most will skip Part I. For instance, in addition to skipping Chapter 1, the introductory chapter, if MySQL is already installed on your computer, you would probably skip Chapter 2, which covers installing MySQL and MariaDB. If you’ve never used MySQL, you probably should read Chapter 3, The Basics and the mysql Client. After that, all readers should read sequentially the chapters contained in the Parts II, III, and IV. The remaining chapters, contained in Part V, relate to administration and not all of those may be of use to you early on.
Most of the chapters conclude with a set of exercises. The exercises are designed to help you think through what you’ve read in the chapter. Working through the exercises will help reinforce what you should have learned from the examples in the chapter. Incidentally, it’s useful to try entering the examples throughout the chapters for more practice. The exercises at the end of the chapters depend on a building of knowledge, if not from one chapter to the next, at least from previous chapters.
Text-Based Interface and Operating Systems
Many people feel that graphical user interfaces (GUIs) are faster when using a complex software program or system. This accounts for the popularity of Windows programs. However, while it is said that a picture is worth a thousand words, when you want to say only one word, you don’t need to draw a picture. You don’t need to use an elaborate GUI to make a minute change to a database.
In particular, I don’t like GUIs for controlling a server or MySQL. Interfaces tend to change between versions of the interface. Command-line utilities are very stable and their basic commands don’t usually change. If you know how to configure a server at the command line, it matters little what kind of server you’re entering commands on. Any examples in this book that are executed within MySQL are universal. Examples shown at the command line are for Unix-like operating systems (e.g., Linux). I leave it to readers to make the necessary adjustments for their particular operating systems (i.e., how to get to the command prompt).
Conventions Used in This Book
The following typographical conventions are used in this book:
Indicates new terms, URLs, email addresses, filenames, and file extensions.
Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.
Constant width bold
Shows commands or other text that should be typed literally by the user.
Constant width italic
Shows text that should be replaced with user-supplied values or by values determined by context.
This icon signifies a tip, suggestion, or general note.
This icon indicates a warning or caution.
 Incidentally, MySQL is named for Monty Widenius’ first daughter, My Widenius. MariaDB is named for his second daughter, Maria Widenius.