Mastering MariaDB

Name: Mastering MariaDB
Brand: Packt Publishing
Availability: OnlineOnly

Federico Razzoli(Autor*in)

Packt Publishing

1. Auflage

Erschienen am 24. September 2014

384 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-78398-155-7 (ISBN)

ab 27,99 €

Als Download verfügbar

Merkliste: siehe Preise

Beschreibung

Alle Preise

Weitere Details

Weitere Ausgaben

Inhalt

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

Systemvoraussetzungen

Dateiformat: ePUB
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat ePUB ist sehr gut für Romane und Sachbücher geeignet – also für „fließenden” Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Dateiformat: PDF
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat PDF zeigt auf jeder Hardware eine Buchseite stets identisch an. Daher ist eine PDF auch für ein komplexes Layout geeignet, wie es bei Lehr- und Fachbüchern verwendet wird (Bilder, Tabellen, Spalten, Fußnoten). Bei kleinen Displays von E-Readern oder Smartphones sind PDF leider eher nervig, weil zu viel Scrollen notwendig ist.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Als PDF speichern Als Link merken