Mastering MariaDB

Book description

Debug, secure, and back up your data for optimum server performance with MariaDB

In Detail

MariaDB is a community-driven fork of MySQL, and is a relational DBMS that can be used to build reliable, high-performance database servers. MariaDB combines MySQL characteristics such as replication, backup and disaster recovery, transactions and locks, and also includes important improvements to the performance, stability, security, and monitoring capabilities.

Mastering MariaDBstarts with an overview of the basic features and mechanisms, which includes diagnosing and solving real-life problems such as data corruption, poorly performing queries, and deadlocks. You will learn how to improve the performance of a server by identifying slow queries, and how to choose and set up a proper backup plan and recover data when disasters occur. You will learn how to share your data through several servers using replication. By the end of this book, you will be able to configure MariaDB servers, diagnose as well as troubleshoot common transactional problems, and execute database maintenance.

What You Will Learn

  • Identify inefficient queries using logs and log analysis tools
  • Design your indexes and optimize your queries to produce efficient query plans
  • Tune MariaDB and InnoDB configuration to achieve a stabilized degree of performance and reliability
  • Create and manage users, roles, and permissions
  • Perform regular backups and restore data
  • Share your data through several partitions, disks, or servers using techniques such as replication to make operations faster
  • Set up, maintain, and troubleshoot a replication environment as well as a database cluster

Table of contents

  1. Mastering MariaDB
    1. Table of Contents
    2. Mastering MariaDB
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Understanding the Essentials of MariaDB
      1. The MariaDB architecture
      2. The command-line client
      3. Storage engines
        1. XtraDB and InnoDB
        2. TokuDB
        3. MyISAM and Aria
        4. Other engines
      4. Logs
      5. MariaDB caches
      6. InnoDB data structures
      7. Authentication and security
      8. The information_schema database
      9. The performance_schema database
      10. Compatibility with MySQL and other DBMS
      11. MariaDB resources
      12. Summary
    9. 2. Debugging
      1. Understanding error conditions in MariaDB
        1. The SQLSTATE value
        2. The error number
        3. The error message
        4. The custom errors
        5. The SHOW WARNINGS and SHOW ERRORS statements
      2. The diagnostics area
      3. The GET DIAGNOSTICS statement
      4. The error log
        1. The error log format
        2. A troubleshooting example with the error log
      5. System logs
      6. The general query log
        1. The file format of the general query log
        2. The general_log table
        3. Debugging examples with the general query log
      7. Maintenance of the server logs
        1. Flushing logs
        2. Rotating the file-based logs
        3. Rotating the table-based logs
      8. The SQL_ERROR_LOG plugin
      9. Tips on debugging stored programs
      10. Debugging stored programs using the SQL_ERROR_LOG plugin
      11. Summary
    10. 3. Optimizing Queries
      1. The slow query log
        1. The file format of the slow query log
        2. The slow_log table
      2. Explaining the pt-query-digest command from Percona Toolkit
      3. Introducing indexes
        1. Table statistics
        2. Storage engines and indexes
      4. Working with the EXPLAIN statement
        1. Understanding the output of EXPLAIN
          1. Simple SELECT statements
          2. Internal temporary tables or files
          3. The UNION queries
          4. Simple index access methods
          5. Index optimizations of the JOIN clause
          6. Optimization of subqueries
      5. Summary
    11. 4. Transactions and Locks
      1. The InnoDB locks
        1. The lock modes
        2. Lock types
        3. Diagnosing locks
        4. Locks used by various SQL statements
      2. Reads consistency
        1. The non-repeatable reads
        2. Phantom rows
        3. Consistent reads
        4. Locking reads
      3. Deadlocks
      4. Transactions
        1. The transactions life cycle
        2. Transactions isolation levels
          1. The READ UNCOMMITTED isolation level
          2. The READ COMMITTED isolation level
          3. The REPEATABLE READ isolation level
          4. The SERIALIZABLE isolation level
        3. Transactions access modes
      5. Metadata locks
      6. Summary
    12. 5. Users and Connections
      1. User accounts
      2. Setting permissions using roles
      3. Connecting MariaDB through Secure Socket Layer
      4. Authentication plugins
        1. Activating the pool of threads
        2. Monitoring the pool of threads
        3. Configuring the threadpool implementation
          1. Configuring the pool of threads on Unix
          2. Configuring the pool of threads on Windows
        4. Tuning the configuration variables
        5. Unblocking a blocked pool of threads
      5. Monitoring connections
        1. States of the process
        2. Aborting connections
      6. Summary
    13. 6. Caches
      1. InnoDB caches
        1. InnoDB pages
        2. The InnoDB buffer pool
          1. Old and new pages
          2. Buffer pool instances
          3. Dirty pages
          4. The read ahead optimization
          5. Diagnosing the buffer pool performance
          6. Dumping and loading the buffer pool
          7. The InnoDB change buffer
          8. Explaining the doublewrite buffer
      2. MyISAM key cache
        1. LRU and the midpoint insertion strategy
        2. Key cache instances
        3. Segmented key cache
        4. Preloading indexes into the cache
      3. Aria page cache
      4. The query cache explained
        1. Configuring the query cache
        2. Information on the status of the query cache
        3. Explaining the subquery cache
        4. Alternative query caching methods
      5. The table open cache
      6. Per-session buffers
      7. Summary
    14. 7. InnoDB Compressed Tables
      1. An overview of the InnoDB compression
      2. InnoDB compression requirements
      3. Explaining the file-per-table mode
        1. A brief on InnoDB file formats
      4. Creating InnoDB compressed tables
      5. Explaining the implementation of the InnoDB compression
      6. Monitoring the InnoDB compression performance
        1. The INNODB_CMPMEM table
        2. The INNODB_CMP_PER_INDEX table
        3. The INNODB_CMP table
      7. Other compression solutions
      8. Summary
    15. 8. Backup and Disaster Recovery
      1. Types of backups
        1. Logical and physical backups
        2. Hot and cold backups
      2. Complete and incremental backups
        1. Backups and replication
        2. Steps to be followed before performing backups
      3. Creating a dump file with mysqldump
      4. Delimited text backups
        1. The --tab option of the mysqldump command
        2. Loading a dump file with the mysqlimport command
        3. Creating a text-delimited file with the SELECT … INTO OUTFILE command
        4. Dumping a table definition with the SHOW CREATE TABLE command
        5. Loading a dump file with the LOAD DATA INFILE statement
        6. Separator options and clauses
        7. An example to create and restore dump files
        8. Performing a backup using a CONNECT or CSV engine
      5. Physical backups
        1. Which files should be copied?
          1. Table files
          2. Logfiles
          3. Configuration files
        2. Hot physical backups
        3. Filesystem snapshots
        4. Incremental physical backups with the rsync command
        5. Copying files when the server is running
      6. Using the binary log for incremental backups
      7. Percona XtraBackup
        1. Performing backups
          1. Complete backups
          2. Partial backups
        2. Preparing backups
        3. Preparing complete backups
          1. Preparing partial backups
        4. Restoring backups
          1. Restoring complete backups
          2. Restoring partial backups
      8. Securing backups
      9. Repairing tables
        1. Recovering InnoDB tables
          1. Checking tables
          2. Transaction logs
          3. Forcing data recovery
        2. Repairing non-InnoDB tables
          1. The CHECK TABLE statement
          2. The REPAIR TABLE statement
          3. Repairing CSV tables
          4. Repairing tables with the myisamchk and aria_chk tools
          5. MyISAM and Aria autorecovery
      10. Summary
    16. 9. Replication
      1. An overview of replication
        1. How replication works
        2. Replication threads
          1. Parallel replication
        3. Slave logs
      2. Choosing a binary log format
        1. Statement-based binary logging
        2. Row-based binary logging
        3. The MIXED binary logging format
        4. The binary logging of stored programs
      3. Configuring replication
        1. Configuring a new replication master
        2. Configuring a new replication slave
        3. Starting a slave
        4. Checking whether a slave is running
        5. Reconfiguring an existing slave
        6. Importing the data into a master
        7. Importing the data into a slave from a master
          1. Dumping data from a master
          2. Dumping data from a slave
        8. Filtering binary log events
          1. The SET SQL_LOG_BIN statement
          2. The @@skip_replication variable
        9. Filtering the replication of events on the slaves
        10. Checksums of the binary log events
        11. Configuring parallel replication
        12. Delaying a slave
      4. Multisource replication
      5. Replication logs
        1. Rotating the binary log
        2. Rotating the relay log
        3. The slave status logs
      6. Checking the replication for errors
        1. The CHECKSUM TABLE statement
        2. The pt-table-checksum tool
        3. Files checksum
        4. Query checksum
      7. Troubleshooting
        1. A slave does not start
        2. A slave lags behind
      8. Summary
    17. 10. Table Partitioning
      1. Support for partitioning
      2. Partitioning types and expressions
        1. Partitioning expressions
        2. Indexes and primary keys
        3. Partition names
        4. Partitioning types
          1. The RANGE type
          2. The LIST type
          3. The COLUMNS keyword
          4. The HASH and KEY types
          5. The LINEAR keyword
          6. Splitting into subpartitions
      3. Administering partitioned tables
        1. Obtaining information about partitions
        2. Changing partitions' definitions
          1. Modifying RANGE and LIST partitions
          2. Modifying HASH and KEY partitions
          3. Copying data between a partition and a table
        3. Maintenance operations statements
      4. Partitions' physical files
      5. Query optimizations
        1. Partition pruning
        2. Partition selection
      6. Summary
    18. 11. Data Sharding
      1. Distributing files between multiple disks
        1. Determining the path of table files
        2. InnoDB logfiles
        3. Configuring the undo log
        4. Configuring the redo log
      2. The FEDERATEDX and CONNECT storage engines
        1. Creating a FEDERATEDX table
        2. Defining a link to a remote server
        3. Creating a MYSQL CONNECT table
          1. Sending SQL statements to a remote server
          2. Merging multiple CONNECT MYSQL tables
      3. The SPIDER storage engine
        1. Explaining the working of the SPIDER storage engine
        2. Installing the SPIDER storage engine
        3. Creating a SPIDER table
        4. Logging of queries and errors
        5. Executing arbitrary statements on remote servers
          1. Explaining the spider_direct_sql() function
          2. Explaining the spider_bg_direct_sql() function
      4. Summary
    19. 12. MariaDB Galera Cluster
      1. MariaDB Galera Cluster key concepts
        1. An overview of Galera Cluster
        2. Synchronous replication
      2. Setting up a cluster
        1. Requirements
        2. Installation
      3. Starting the nodes
        1. Determining a node URL
        2. Node provisioning
          1. State Snapshot Transfer
          2. Incremental State Transfer
        3. The split brain problem
          1. The Galera arbitrator
      4. Configuring the cluster
        1. Explaining the important Galera system variables
          1. Generic cluster settings
          2. Performance and reliability
          3. Settings affecting the behavior of State Snapshot Transfer
          4. Dealing with Galera limitations
        2. Setting the wsrep parameters
      5. Monitoring and troubleshooting
        1. Notification scripts
        2. Checking the status variables
          1. The health of a cluster
          2. Individual node health
          3. The health of a replication
          4. Network performance
      6. Load balancing
      7. Listing the limitations of Galera Cluster
      8. Galera Load Balancer
      9. Summary
    20. Index

Product information

  • Title: Mastering MariaDB
  • Author(s): Federico Razzoli
  • Release date: September 2014
  • Publisher(s): Packt Publishing
  • ISBN: 9781783981540