Mastering MariaDB

Book Image

Mastering MariaDB

By : Federico Razzoli

Book Image

Mastering MariaDB

By: Federico Razzoli

Overview of this book

Mastering MariaDB

Mastering MariaDB

Credits

About the Author

About the Author

About the Reviewers

About the Reviewers

www.PacktPub.com

www.PacktPub.com

Preface

Free Chapter

Understanding the Essentials of MariaDB

Understanding the Essentials of MariaDB

The MariaDB architecture

The command-line client

Storage engines

InnoDB data structures

Authentication and security

The information_schema database

The performance_schema database

Compatibility with MySQL and other DBMS

MariaDB resources

Debugging

Understanding error conditions in MariaDB

The diagnostics area

The GET DIAGNOSTICS statement

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

Optimizing Queries

Optimizing Queries

The slow query log

Explaining the pt-query-digest command from Percona Toolkit

Introducing indexes

Working with the EXPLAIN statement

Transactions and Locks

Transactions and Locks

The InnoDB locks

Reads consistency

Users and Connections

Users and Connections

Setting permissions using roles

Connecting MariaDB through Secure Socket Layer

Authentication plugins

Monitoring connections

Caches

MyISAM key cache

Aria page cache

The query cache explained

The table open cache

Per-session buffers

InnoDB Compressed Tables

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

Backup and Disaster Recovery

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

Replication

An overview of replication

Choosing a binary log format

Configuring replication

Multisource replication

Replication logs

Checking the replication for errors

Troubleshooting

Table Partitioning

Table Partitioning

Support for partitioning

Partitioning types and expressions

Administering partitioned tables

Partitions' physical files

Query optimizations

Data Sharding

Distributing files between multiple disks

The FEDERATEDX and CONNECT storage engines

The SPIDER storage engine

MariaDB Galera Cluster

MariaDB Galera Cluster

MariaDB Galera Cluster key concepts

Setting up a cluster

Starting the nodes

Configuring the cluster

Monitoring and troubleshooting

Listing the limitations of Galera Cluster

Galera Load Balancer

Index

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

The diagnostics area

The diagnostics area consists of two subareas: the statement information and the condition information.

The statement information contains two values:

NUMBER: This is the number of conditions stored in the condition area.
ROW_COUNT: This is the number of rows modified by the statement it refers to. The same value is returned by the ROW_COUNT() SQL function and by the mysql_affected_rows() API function.

The diagnostics area is populated and emptied by following the exact rules. Knowing these rules is very important to debug single statements without falling for some common pitfalls, and it is more important to debug the stored programs.

Whenever a statement generates at least one condition (notes, warnings, or errors), the diagnostics area is populated with such conditions. Any condition present previously in the diagnostics area is deleted. However, there is an exception. If the new statement is RESIGNAL or GET DIAGNOSTICS, the old conditions are not deleted. This is to...