2. Debugging | Mastering MariaDB

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

The GET DIAGNOSTICS statement

The GET DIAGNOSTICS statement is a good way to show the structure of the diagnostics area because it can copy each value into a variable.

It has a verbose and an error-prone syntax, but it is the only way to analyze the diagnostics area within a stored program. In fact, SHOW WARNINGS returns a result set to the client but does not allow the SQL code to access it. The HANDLER block is executed when an exact error or a class of errors occurs, but it does not provide information about the problem. The reader is expected to be familiar with the stored programs. However, some examples of the use of GET DIAGNOSTICS are useful to demonstrate the contents of the diagnostics area.

First, let's populate the diagnostics area with two errors. For this, we will use the same statements of the previous example:

MariaDB [(none)]> SET sql_mode = 'x';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'x'
MariaDB [(none)]> RESIGNAL;
ERROR 1645 (0K000): RESIGNAL...

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