It is said that the most advanced technologies are invisible to the user. This is certainly true in the case of database management systems. Databases are one of the most important invisible technologies that make things happen. They are everywhere around us and we use them several times a day; though we can't see them. For example, we use them when we make a phone call, or reserve a hotel room, or visit a website, or use some electronic device. Sometimes, these databases are big and complex. And, in many cases, they are managed by MySQL or one of its forks such as MariaDB.
MySQL is mainly known for being one of the four components of the LAMP stack, that is, Linux, Apache, MySQL, and PHP—the most common technologies that make websites work. And that's the reason why many people, who aren't even associated with Information Technology, have heard about MySQL. In fact, MySQL development started in the 80s but the software became famous only with the birth of dynamic websites between 2000 and 2001. It is open source, free, and very simple to learn and administrate. It was exactly what the new websites needed.
Despite its substantial simplicity, MySQL's features have grown fast. It was criticized by PostgreSQL supporters because it did not support many key features of DBMSs, such as transactions and foreign keys. In spite of this being true at that time, MySQL had several unique features, which were extremely useful for a large amount of users. For example, it supported replication 10 years before PostgreSQL! It was also reliable and fast. And as time went by, MySQL became a complete, feature-rich relational DBMS.
Why then did Monty Widenius, the creator of MySQL, leave the project to start a fork called MariaDB? That was because, in 2005, Oracle bought the InnoDB storage engine. In 2008, Sun Microsystems bought MySQL, which in turn was bought by Oracle in 2009. Since that acquisition, MySQL belongs to its biggest competitor: a big corporation whose business is mainly proprietary, high-cost software.
To be fair, we must make it clear that Oracle is investing substantial resources on the MySQL technology, and did a great job in some areas, especially InnoDB. However, some uncertainty remains; is Oracle selecting the new features to avoid the users of their main product switching to MySQL? Will Oracle always invest in MySQL and keep it open source? Only Oracle managers know the answers to these questions.
What we know for sure is that MySQL is now less open than it used to be in the past. The public bug database is not used by Oracle employees and contains obsolete information. No information or test cases are released about security holes. The repositories are updated less often. And some community-oriented sites, such as a public wiki edited by the community, that contain vast information about MySQL do not exist anymore.
On the contrary, the strength of MariaDB is its continuous collaboration with the community. Very important new features such as multisource replication and roles have been initially developed by community members. MariaDB's bug tracking and project management software allows us to know which bugs or new features are being processed, what the new versions will look like, and when they will be released. Some developers actively communicate with users via the mailing lists and IRC channel. And while MySQL documentation has always been proprietary, MariaDB documentation has free licenses and can be improved by the community using a public wiki.
Most importantly, MariaDB Foundation exists. It is similar to other foundations related to free software projects such as the Apache Foundation. Monty Widenius himself is a member of the board of directors. The purpose of the foundation is to safeguard the MariaDB source code, and guarantee that it will always remain free. It also promotes MariaDB and its ecosystem, maintaining the MariaDB Knowledge Base. The website of the MariaDB Foundation is https://mariadb.org/.
The MariaDB and MySQL teams follow diverging roads. The starting point is the same: the state of MySQL in 2009. However, while the MySQL road is going towards a less open zone, MariaDB is a perfectly open project. The results of this openness are positive from a technical point of view, too. MariaDB developers and MariaDB users are both interested in constantly improving the server, and they collaborate to achieve this goal.
This book provides the knowledge needed to administrate the MariaDB server and clusters of servers. It will help you master database development on the MariaDB server. It shows you how to maintain a MariaDB server, taking advantage of its most recent features as well as the battle-tested functionalities inherited from MySQL. The book starts with an overview of the basic features and mechanisms that an advanced user should know. This includes diagnosing and solving most of the real-life problems, such as MariaDB errors, logs, and locks. You will learn how to improve the performance of a server by identifying slow queries. The book then covers how to choose and set up a proper backup plan and recover data when disaster occurs. Sharing your data through several servers using replication, MariaDB Galera Cluster, and the SPIDER storage engine will be dealt in detail. By the end of this book, the reader will be able to configure MariaDB servers, diagnose, as well as troubleshoot the standard transactional problems, and execute database maintenance. Both of these features imported from MySQL, as well as MariaDB's unique features, are covered in the book. Plugins and tools developed by the community are also explained.
MariaDB is ready to make things work, even in situations where high performance and high availability are critical. Get ready to make MariaDB work!
Chapter 1, Understanding the Essentials of MariaDB, discusses some of the key concepts and components of MariaDB, such as storage engines and logging. The most important resources for MariaDB professionals are also listed in this chapter.
Chapter 2, Debugging, explains how to debug SQL statements in MariaDB. It discusses how MariaDB generates errors and logs that can be used to find bugs.
Chapter 3, Optimizing Queries, is an overview on query optimization. First, it shows how to find slow queries that need be optimized. Then, it discusses the most important algorithms used by the MariaDB optimizer to execute a query, such as the index merge and the subquery optimization algorithms.
Chapter 4, Transactions and Locks, deals with concurrency. It explains how MariaDB uses locks to guarantee a proper isolation level for each transaction, and how these locks affect performance.
Chapter 5, Users and Connections, discusses how to manage user accounts and their activities in MariaDB. It covers permissions, the allocation of resources on a user basis, authentication methods, SSL connections, and the pool of threads.
Chapter 6, Caches, explains the caches used by the general purpose storage engines: InnoDB buffer pool, MyISAM key cache, and Aria page cache. Then, it explains the query and subquery caches and discusses alternative methods to cache the results of queries.
Chapter 7, InnoDB Compressed Tables, discusses InnoDB compressed tables. It shows how to create compressed tables and how to monitor their performance. Finally, it compares the different compression solutions available in MariaDB.
Chapter 8, Backup and Disaster Recovery, explains the backup methods provided by MariaDB and some third-party tools. The chapter discusses how to choose a backup plan, how to perform the different backup types, and how to restore backups when needed.
Chapter 9, Replication, illustrates how to set up and maintain a replication environment. The latest features of replication from MariaDB 10.0 are included, namely parallel replication and multisource replication.
Chapter 10, Table Partitioning, shows how to split big tables into multiple partitions, perhaps located on different storage devices. The characteristics of different partitioning types are explained, as well as the optimizations allowed by the different partition types.
Chapter 11, Data Sharding, discusses the main methods to distribute data across multiple disks or servers. The storage engines that allow reading and writing data onto remote servers are illustrated here: SPIDER, FederatedX, and CONNECT.
Chapter 12, MariaDB Galera Cluster, covers the MariaDB distribution of the Galera Cluster technology. It explains how to set up a cluster, add new nodes, monitor performance, and identify the most common problems.