Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Mastering MariaDB

By : Razzoli

3.8 (10)

Mastering MariaDB

3.8 (10)

By: Razzoli

Overview of this book

This book is intended for intermediate users who want to learn how to administrate a MariaDB server or a set of servers. It is aimed at MariaDB users, and hence working knowledge of MariaDB is a prerequisite.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Understanding the Essentials of MariaDB

1. Understanding the Essentials of MariaDB

The MariaDB architecture

The command-line client

Storage engines

Logs

MariaDB caches

InnoDB data structures

Authentication and security

The information_schema database

The performance_schema database

Compatibility with MySQL and other DBMS

MariaDB resources

Summary

2. Debugging

2. Debugging

Understanding error conditions in MariaDB

The diagnostics area

The GET DIAGNOSTICS statement

The error log

System logs

The general query log

Maintenance of the server logs

The SQL_ERROR_LOG plugin

Tips on debugging stored programs

Debugging stored programs using the SQL_ERROR_LOG plugin

Summary

3. Optimizing Queries

3. Optimizing Queries

The slow query log

Explaining the pt-query-digest command from Percona Toolkit

Introducing indexes

Working with the EXPLAIN statement

Summary

4. Transactions and Locks

4. Transactions and Locks

The InnoDB locks

Reads consistency

Deadlocks

Transactions

Metadata locks

Summary

5. Users and Connections

5. Users and Connections

User accounts

Setting permissions using roles

Connecting MariaDB through Secure Socket Layer

Authentication plugins

Monitoring connections

Summary

6. Caches

6. Caches

InnoDB caches

MyISAM key cache

Aria page cache

The query cache explained

The table open cache

Per-session buffers

Summary

7. InnoDB Compressed Tables

7. InnoDB Compressed Tables

An overview of the InnoDB compression

InnoDB compression requirements

Explaining the file-per-table mode

Creating InnoDB compressed tables

Explaining the implementation of the InnoDB compression

Monitoring the InnoDB compression performance

Other compression solutions

Summary

8. Backup and Disaster Recovery

8. Backup and Disaster Recovery

Types of backups

Complete and incremental backups

Creating a dump file with mysqldump

Delimited text backups

Physical backups

Using the binary log for incremental backups

Percona XtraBackup

Securing backups

Repairing tables

Summary

9. Replication

9. Replication

An overview of replication

Choosing a binary log format

Configuring replication

Multisource replication

Replication logs

Checking the replication for errors

Troubleshooting

Summary

10. Table Partitioning

10. Table Partitioning

Support for partitioning

Partitioning types and expressions

Administering partitioned tables

Partitions' physical files

Query optimizations

Summary

11. Data Sharding

11. Data Sharding

Distributing files between multiple disks

The FEDERATEDX and CONNECT storage engines

The SPIDER storage engine

Summary

12. MariaDB Galera Cluster

12. MariaDB Galera Cluster

MariaDB Galera Cluster key concepts

Setting up a cluster

Starting the nodes

Configuring the cluster

Monitoring and troubleshooting

Load balancing

Listing the limitations of Galera Cluster

Galera Load Balancer

Summary

Index

Index

Query optimizations

If the partitioning expression and the partition type have been carefully selected, most queries will only involve one partition or a set of partitions.

In many cases, the optimizer will find out which partitions are not relevant for the current query. This optimization is called partition pruning.

Also, the user can use a SQL clause to specify the list of partitions that must be used. This is called partition selecting.

Note

However, in MariaDB, queries are never parallelized. Even if the optimizer knows that two partitions must be read, and those partitions are on different disks, the same thread will read them sequentially. In particular, one can expect full table scans and full index scans on partitioned tables to run much faster because of parallelization, but this is not the case.

Partition pruning

In MariaDB, partition pruning is possible with RANGE and LIST partitioning, but not with RANGE COLUMNS or LIST COLUMNS. When a statement references the columns used by the...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Mastering MariaDB

Search

Your notes and bookmarks