Index

A

• aggregate functions
  • reference link / See also
• alter operations
  • in-place / Altering tables
  • copy / Altering tables
• APT bundle
  • using / Using the APT bundle
• APT repositories
  • used, for installing MySQL / Installing MySQL using YUM/APT
  • using /
• Atomicity / Introduction
• Atomicity, Consistency, Isolation, and Durability (ACID) properties / Introduction

B

• backups
  • setting up / Introduction
  • obtaining, mysqldump used / Taking backups using mysqldump
  • of databases / Full backup of all databases, Specific databases and tables
  • point-in-time recovery / Point-in-time recovery
  • master binary coordinates, dumping / Dumping master binary coordinates
  • of tables / Specific databases and tables
  • tables, ignoring / Ignore tables
  • of rows / Specific rows
  • from remote server / Backup from a remote server
  • server, rebuilding with different schema / Backup to rebuild another server with a different schema
  • no data / Only schema and no data
  • schema / Only schema and no data
  • data / Only data and no schema
  • no schema / Only data and no schema
  • data, merging with server / Only data and no schema
  • obtaining, mysqlpump used / Taking backups using mysqlpump
  • parallel processing / Parallel processing
  • database objects, excluding regex used / Exclude/include database objects using regex
  • database objects, including regex used / Exclude/include database objects using regex
  • users, backup / Backup users
  • compressed backups / Compressed backups, Compressed backups
  • faster reload / Faster reload
  • obtaining, mydumper used / Taking backups using mydumper, How to do it...
  • installation / Installation
  • full backup / Full backup
  • consistent backup / Consistent backup
  • single table / Backup of a single table
  • of databases, regex used / Backup of specific databases using regex
  • of big table, mydumper used / Taking backup of a big table using mydumper
  • non-blocking backup / Non-blocking backup
  • data, backing up / Backing up only data
  • obtaining, flat files used / Taking backups using flat files, How to do it...
  • obtaining, XtraBackup used / Taking backups using XtraBackup, How to do it...
  • instances, locking / Locking instances for backup
• basic parameters
  • configuring / Configuring the parameters
  • data directory / data directory
  • innodb_buffer_pool_size / innodb_buffer_pool_size
  • innodb_buffer_pool_instances / innodb_buffer_pool_instances
  • innodb_log_file_size / innodb_log_file_size
• binary logging
  • about / Introduction
  • replication / Introduction
  • point in time recovery / Introduction
  • using / Using binary logging
• binary logs
  • enabling / Enabling binary logs
  • disabling, for session / Disabling binary logs for a session
  • flush log / Move to the next log
  • expiring / Expire binary logs
  • format / Binary log format, How to do it...
  • statements, extracting / Extracting statements from a binary log, How to do it...
  • observations / Observations
  • based on time, extracting / Extracting based on time and position
  • position, extracting / Extracting based on time and position
  • based on database, extracting / Extracting based on the database
  • row event display, extracting / Extracting a row event display
  • database name, rewriting / Rewriting a database name
  • disabling, for recovery / Disabling a binary log for recovery
  • events, displaying / Displaying events in a binary log file
  • databases, selecting / Ignoring databases to write to a binary log
  • example / Example 1, Example 2
  • relocating / Relocating binary logs
  • backup / Binary log backup, How to do it...
  • managing / Managing the binary logs, How to do it...

C

• chain replication
  • slave, switching from master-slave / Switching slave from master-slave to chain replication, How to do it...
• common table expression (CTE)
  • about / Common table expressions (CTE), Non-recursive CTE
  • non-recursive CTE / Non-recursive CTE
  • recursive CTE / Recursive CTE
• composite partitioning / Subpartitioning
• config file
  • using / Using config file
• Consistency / Introduction
• consistent backup / Introduction

D

• data
  • selecting / Selecting data
  • columns, selecting / Selecting columns
  • count / Count
  • filter based, on condition / Filter based on condition
  • operators / Operators
  • simple pattern matching / Simple pattern matching
  • regular expressions / Regular expressions
  • results, limiting / Limiting results
  • table alias, used / Using the table alias
  • selecting, into file / Selecting data into a file and table
  • selecting, into table / Selecting data into a file and table
  • loading, into table / Loading data into a table
  • replacing / REPLACE with new data
  • ignoring / IGNORE data
• databases
  • creating / Creating databases, How to do it..., See also
  • information, obtaining / Getting information about databases and tables
• Data Definition Language (DDL) / Creating databases
• data directory
  • changing / Changing the data directory, How to do it...
• Data Manipulation Language (DML) / Inserting, updating, and deleting rows
• datatypes
  • optimizing / Optimizing datatypes, How to do it...
• DEB files
  • used, for installing MySQL 8.0 / Installing MySQL 8.0 using RPM or DEB files
• delayed replication
  • setting up / Setting up delayed replication, How to do it...
• delete statement / Deleting
• descending indexes / Descending index, How to do it...
• dirty read / Read uncommitted
• duplicate index
  • about / Removing duplicate and redundant indexes
  • removing / Removing duplicate and redundant indexes, How to do it...
  • pt-duplicate-key-checker / pt-duplicate-key-checker
  • mysqlindexcheck / mysqlindexcheck
• Durability / Introduction
• dynamic table metadata / Getting information about databases and tables

E

• encrypted connections
  • setting up, X509 used / Setting up encrypted connections using X509, How to do it...
• error log
  • managing / Managing the error log
  • configuring / Configuring the error log
  • rotating / Rotating the error log
  • system log, used for logging / Using the system log for logging
  • in JSON format / Error logging in JSON format
  • reference link / Error logging in JSON format
• events
  • about / Events, How to do it...
  • access control / Access control
• EXPLAIN
  • using / Using EXPLAIN
  • used, for connection / Using EXPLAIN for connection
• EXPLAIN JSON
  • using / Using EXPLAIN JSON
• explain plan
  • about / The explain plan
  • reference link / Using EXPLAIN for connection
• external locking / Locking

F

• file
  • data, selecting / Selecting data into a file and table
  • saving / Saving as a file
• file-per-table tablespaces
  • creating, outside data directory / Creating file-per-table tablespaces outside the data directory, How to do it...
  • copying, to another instance / Copying file-per-table tablespaces to another instance, How to do it...
  • table, copying / Copy full table
  • partition of table, copying / Copying individual partitions of a table
• flat file backup
  • recovering / Recovering from flat file backup
• flat files
  • used, for obtaining backups / Taking backups using flat files, How to do it...
• function and operator reference
  • reference link / See also
• functions
  • about / Functions
  • inbuilt functions / Inbuilt functions
• function syntax
  • reference link / See also

G

• general availability (GA) / Using YUM repositories
• general query log
  • managing / Managing the general query log and slow query log
  • about / General query log
  • query log output destinations, selecting / Selecting  query log output destinations
• general tablespace
  • managing / Managing general tablespace
  • creating / Create a general tablespace
  • tables, adding / Adding tables to a general tablespace
  • non-partitioned tables, moving between tablespaces / Moving non-partitioned tables between tablespaces
  • partitioned tables, managing / Managing partitioned tables in a general tablespace
  • dropping / Dropping general tablespace
• generated columns
  • about / Generated columns
  • virtual / Generated columns
  • stored / Generated columns
• generic binaries
  • used, for installing MySQL on Linux / Installing MySQL on Linux using Generic Binaries
• global transaction identifier (GTID) / Setting up GTID replication
• global variables
  • using / Using global and session variables
• GTID replication
  • setting up / Setting up GTID replication, How to do it...

I

• in-place downgrades, MySQL 8.0
  • YUM repositories, used / Using YUM repositories
  • APT repositories, used /
  • RPM bundle, used / Using the RPM or APT bundle
  • APT bundle, used / Using the RPM or APT bundle
  • generic binaries, used / Using Generic Binaries
• in-place upgrades, MySQL 8.0
  • YUM-based systems / YUM-based systems
  • APT-based systems / APT-based systems
  • RPM bundle, used / Using RPM or APT bundles
  • APT bundle, used / Using RPM or APT bundles
  • generic binaries, used / Using Generic Binaries
• index-organized table / Primary key (clustered index) and secondary indexes
• indexes
  • adding / Adding indexes, Add index
  • primary key / Primary key (clustered index) and secondary indexes
  • secondary index / Primary key (clustered index) and secondary indexes
  • UNIQUE index / UNIQUE index
  • Prefix index / Prefix index
  • Drop index / Drop index
  • on generated columns / Index on generated columns
• index hints
  • using / Using index hints
• index usage
  • checking / Checking index usage
• init-file
  • using / Using init-file
• InnoDB locks
  • reference link / Locking
• InnoDB redo log files
  • number, changing / Changing the number or size of InnoDB redo log files, How to do it...
  • size, changing / Changing the number or size of InnoDB redo log files, How to do it...
• InnoDB system tablespace
  • resizing / Resizing the InnoDB system tablespace, How to do it...
  • increasing / Increasing the InnoDB system tablespace
  • shrinking / Shrinking the InnoDB system tablespace
• InnoDB tables
  • compressing / Compressing InnoDB tables, How to do it...
  • compression, enabling for file_per_table Tables / Enabling Compression for file_per_table Tables
  • compression, disabling for file_per_table Tables / Disabling Compression for file_per_table Tables
  • compression, enabling for general tablespace / Enabling Compression for General Tablespace
• insert
  • on duplicate key update / REPLACE, INSERT, ON DUPLICATE KEY UPDATE
• insert statement / Inserting
• internal locking
  • about / Locking
  • row-level locks / Locking
  • table-level locks / Locking
  • reference link / Locking
• invisible index / Invisible index
• IO thread / Introduction
• Isolation / Introduction
• isolation levels
  • about / Isolation levels
  • read uncommitted / Read uncommitted
  • read committed / Read committed
  • repeatable read / Repeatable read
  • serializable / Serializable

J

• JavaScript Object Notation (JSON)
  • using / Using JSON
  • about / Using JSON
  • inserting / How to do it...
  • retrieving / Retrieve JSON
  • functions / JSON functions
  • pretty view / Pretty view
  • searching / Searching
  • modifying / Modifying
  • removing / Removing
  • other functions / Other functions
  • indexing, generated columns used / Indexing for JSON using generated columns, How to do it...
• JSON format
  • reference link / Using EXPLAIN for connection
• JSON function
  • reference link / See also

L

• locking
  • about / Locking
  • internal locking / Locking
  • external locking / Locking
  • queue / Locking queue
• logical backup / Full backup of all databases
• Logical backup utilities / Introduction
• logical downgrades, MySQL 8.0
  • YUM repositories, used / Using YUM Repositories
  • APT repositories, used / Using APT Repositories
  • RPM bundle, used / Using RPM or APT bundles
  • APT bundle, used / Using RPM or APT bundles
  • generic binaries, used / Using Generic Binaries
• log sequence number (LSN) / Taking backups using XtraBackup

M

• master-master replication
  • setting up / Setting up master-master replication, How to do it...
• multi-master replication
  • setting up / Setting up multi-source replication, How to do it...
• Multi-Version Concurrency Control (MVCC) / Repeatable read
• mydumper
  • used, for obtaining backups / Taking backups using mydumper, How to do it...
  • recovering, using myloader / Recovering from mydumper using myloader
  • full database, recovering / Recovering full database
  • single database, recovering / Recover a single database
  • single table, recovering / Recovering a single table
• MySQL
  • installing, YUM repositories / Installing MySQL using YUM/APT
  • installing, APT repositories / Installing MySQL using YUM/APT
  • installing, on Linux using generic binaries / Installing MySQL on Linux using Generic Binaries, There's more...
  • uninstalling, on binaries / Uninstalling Binaries
  • connecting, command-line client used / Connecting to MySQL using the command-line client, How to do it..., See also
  • about / Introduction
  • static / Introduction
  • dynamic / Introduction
• MySQL 8
  • stopping / Starting or Stopping MySQL 8
  • uninstalling / Uninstalling MySQL 8
  • uninstalling, on YUM-based systems / On YUM-based systems
  • uninstalling, on APT-based systems / On APT-based systems
• MySQL 8.0
  • downgrading / Introduction, Downgrading from MySQL 8.0
  • upgrading / Introduction, Upgrading to MySQL 8.0, How to do it...
  • installing / Introduction
  • installing, DEB files used / Installing MySQL 8.0 using RPM or DEB files
  • installing, RPM files used / Installing MySQL 8.0 using RPM or DEB files
  • starting / Starting or Stopping MySQL 8
  • server, starting / Starting the MySQL 8.0 server
  • server, stopping / Stopping the MySQL 8.0 server
  • server status, checking / Checking the status of the MySQL 8.0 server
  • in-place downgrades / In-place Downgrades
  • logical downgrades / Logical Downgrades
  • in-place upgrades / In-place upgrades
  • logical upgrades / Logical Upgrades
• MySQL APT repository
  • reference link /
• mysqldump
  • used, for obtaining backups / Taking backups using mysqldump
  • recovering / Recovering from mysqldump and mysqlpump, How to do it...
• mysqlpump
  • used, for backups obtaining / Taking backups using mysqlpump
  • recovering / Recovering from mysqldump and mysqlpump, How to do it...
• MySQL server
  • managing, with systemd / Managing the MySQL Server with systemd
• MySQL utilities
  • installing / Installing MySQL utilities
  • installing, on YUM-based systems / On YUM-based systems
  • installing, on APT-based systems / On APT-based systems
• MySQL YUM Repository
  • reference link / Using YUM repositories
• mysql_config_editor
  • used, for password-less authentication / Password-less authentication using mysql_config_editor, How to do it...
• mysql_secure_installation
  • securing / Securing installation, How to do it...
  • FILE privilege / The FILE privilege

N

• networks
  • restricting / Restricting networks and users, How to do it...
• non-recursive CTE / Non-recursive CTE
• non-repeatable read / Read committed

O

• operators
  • reference link / Operators

P

• parameters
  • used, with startup script / Using parameters with startup script
• partition information
  • about / Partition information
  • SHOW CREATE TABLE, used / Using SHOW CREATE TABLE
  • SHOW TABLE STATUS, used / Using SHOW TABLE STATUS
  • EXPLAIN, used / Using EXPLAIN
  • INFORMATION_SCHEMA.PARTITIONS table, querying / Querying the INFORMATION_SCHEMA.PARTITIONS table
• partition management
  • about / Partition management
  • ADD partitions / ADD partitions
  • reorganizing partitions / Reorganizing partitions
  • DROP partitions / DROP partitions
  • TRUNCATE partitions / TRUNCATE partitions
  • HASH partitions, managing / Managing HASH and KEY partitions
  • KEY partitions / Managing HASH and KEY partitions
  • other operations / Other operations
• partition pruning / Partition pruning and selection, Partition pruning
• partition selection / Partition pruning and selection, Partition selection
• password-less authentication
  • mysql_config_editor, used / Password-less authentication using mysql_config_editor, How to do it...
• Percona Toolkit
  • installing / Installing Percona Toolkit
  • installing, on Debian/Ubuntu / On Debian/Ubuntu
  • installing, on CentOS/Red Hat/Fedora / On CentOS/Red Hat/Fedora
• performance_schema
  • using / Using performance_schema
  • disabling / How to do it...
  • enabling / Enable/disable performance_schema
  • instruments, enabling / Enable/disable consumers and instruments
  • consumers, enabling / Enable/disable consumers and instruments
  • consumers, disabling / Enable/disable consumers and instruments
  • instruments, disabling / Enable/disable consumers and instruments
  • tables / performance_schema tables
• Physical backup utilities / Introduction
• point-in-time recovery
  • performing / Performing point-in-time recovery
  • mysqldump / mysqldump or mysqlpump
  • mysqlpump / mysqldump or mysqlpump
  • mydumper / mydumper
• procedures
  • reference link / See also
• pt-archiver
  • reference link / See also
• pt-query-digest
  • used, for analyzing slow queries / Analyzing slow queries using pt-query-digest
  • slow query log / Slow query log
  • general query log / General query log
  • process list / Process list
  • binary log / Binary log
  • TCP dump / TCP dump

Q

• queries
  • benchmarking / Benchmarking queries and the server, How to do it...
  • time, spending in evaluating plans / How to know that the query is spending time in evaluating plans?
• query optimizer
  • controlling / Controlling the query optimizer, How to do it...
  • optimizer_search_depth / optimizer_search_depth
  • optimizer_switch / optimizer_switch
  • optimizer hints / Optimizer hints
  • optimizer cost model, adjusting / Adjusting the optimizer cost model

R

• RANGE partitioning
  • reference link / RANGE partitioning
• redundant indexes
  • removing / Removing duplicate and redundant indexes, How to do it...
• regular expressions
  • reference link / Regular expressions
• relational database management system (RDBMS) / Creating databases
• release candidate (RC) /
• replace
  • on duplicate key update / REPLACE, INSERT, ON DUPLICATE KEY UPDATE
• replication
  • about / Introduction
  • reference link / Introduction
  • scale-out solutions / Introduction
  • data security / Introduction
  • analytics / Introduction
  • long-distance data distribution / Introduction
  • setting up / Setting up replication, How to do it...
  • traditional master-slave replication / Setting up replication
  • chain replication / Setting up replication
  • master-master replication / Setting up replication
  • multi-master replication / Setting up replication
• replication filters
  • setting up / Setting up replication filters, How to do it...
  • database, replicating / Replicate a database only
  • tables, replicating / Replicate specific tables
  • database, ignoring / Ignore a database
  • tables, ignoring / Ignore specific tables
• resource group
  • using / Using resource groups, How to do it...
  • drop resource group / Alter and drop resource group
  • alter resource group / Alter and drop resource group
• results
  • sorting / Sorting results
  • grouping / Grouping results (aggregate functions)
  • count / COUNT
  • sum / SUM
  • average / AVERAGE
  • distinct / DISTINCT
  • filtering, having clause used / Filtering using HAVING
• root password
  • resetting / Resetting the root password
  • init-file, used / Using init-file
  • init-file / Using init-file
  • skip-grant-tables, used / Using --skip-grant-tables
• row-based replication (RBR) / Getting ready
• rows
  • inserting / Inserting, updating, and deleting rows
  • updating / Inserting, updating, and deleting rows
  • deleting / Inserting, updating, and deleting rows
• RPM bundle
  • using / Using the RPM bundle
• RPM files
  • used, for installing MySQL 8.0 / Installing MySQL 8.0 using RPM or DEB files

S

• sample data
  • loading / Loading sample data
• savepoints
  • using / Using savepoints
• secondary indexes
  • reference link / How to do it...
• Secure Sockets Layer (SSL) / Setting up encrypted connections using X509
• SELECT syntax
  • reference link / Selecting data
• semi-synchronous replication
  • setting up / Setting up semi-synchronous replication, How to do it...
• server
  • benchmarking / Benchmarking queries and the server, How to do it...
• session variables
  • using / Using global and session variables
• shared lock / Locking
• skip-grant-tables
  • using / Using --skip-grant-tables
• slave
  • switching, from master-slave replication to chain replication / Switching slave from master-slave to chain replication, How to do it...
  • switching, from chain replication to master-slave replication / Switching the slave from chain replication to master-slave, How to do it...
• slaves / Introduction
• slow queries
  • analyzing, pt-query-digest used / Analyzing slow queries using pt-query-digest
• slow query log
  • managing / Managing the general query log and slow query log
  • about / Slow query log
  • query log output destinations, selecting / Selecting  query log output destinations
• soft delete rows / Efficiently managing time to live and soft delete rows
• source code
  • URL, for installing / There's more...
• SQL thread / Introduction
• SSL replication
  • setting up / Setting up SSL replication, How to do it...
• statement-based replication (SBR) / Getting ready
• statements
  • extracting, from binary logs / Extracting statements from a binary log, How to do it...
• static table metadata / Getting information about databases and tables
• stored procedures / Stored procedures, How to do it...
• subpartitioning / Subpartitioning
• sys schema
  • using / Using the sys schema, How to do it...
  • table statistics, with buffer / How to do it...
  • statement by type, from host / How to do it...
  • statement by type, from user / How to do it...
  • table statistics / How to do it...
  • redundant indexes / How to do it...
  • unused indexes / How to do it...
  • statements, executed from host / How to do it...
  • statement analysis / Statement analysis
• systemd
  • MySQL server, managing / Managing the MySQL Server with systemd

T

• table
  • creating / Creating tables, How to do it...
  • truncating / Truncating tables
  • data, selecting / Selecting data into a file and table
  • saving / Saving as a table
  • data, loading / Loading data into a table
  • joining / Joining tables, How to do it...
  • duplicates, identifying SELF JOIN / Identifying Duplicates using SELF JOIN
  • SUB queries, used / Using SUB queries
  • mismatched rows, finding / Finding mismatched rows between tables
  • information, obtaining / Getting information about databases and tables
  • TABLES / TABLES
  • COLUMNS / COLUMNS
  • FILES / FILES
  • INNODB_SYS_TABLESPACES / INNODB_SYS_TABLESPACES
  • INNODB_SYS_TABLESTATS / INNODB_TABLESTATS
  • PROCESSLIST / PROCESSLIST
  • altering / Altering tables, How to do it...
  • moving, across databases / Moving tables across databases
  • altering, online schema change tool used / Altering tables using an online schema change tool, How it works...
  • archiving / Archiving tables
  • data, purging / Purging data
  • data, archiving / Archiving data
  • data, copying / Copying data
  • cloning / Cloning tables
  • partitioning / Partitioning tables, How to do it...
  • RANGE partitioning / RANGE partitioning
  • REMOVE partitioning / Removing partitioning
  • RANGE COLUMNS partitioning / RANGE COLUMNS partitioning
  • LIST partitioning / LIST and LIST COLUMNS partitioning
  • LIST COLUMNS partitioning / LIST and LIST COLUMNS partitioning
  • LINEAR HASH partitioning / HASH and LINEAR HASH partitioning
  • HASH partitioning / HASH and LINEAR HASH partitioning
  • LINEAR KEY partitioning / KEY and LINEAR KEY partitioning
  • KEY partitioning / KEY and LINEAR KEY partitioning
  • subpartitioning / Subpartitioning
• table structure
  • cloning / Cloning table structure
• time to live
  • managing / Efficiently managing time to live and soft delete rows
• transactions
  • performing / Performing transactions
  • autocommit / Autocommit
• Transport Layer Security (TLS) / Setting up encrypted connections using X509
• triggers / Triggers, How to do it...
• trigger syntax
  • reference link / See also

U

• UNDO tablespace
  • managing / Managing UNDO tablespace, How to do it...
• update statement / Updating
• users
  • creating / Creating users, How to do it...
  • access, granting / Granting and revoking access to users
  • access, revoking / Granting and revoking access to users
  • privileges, granting / Granting privileges
  • grants, checking / Checking grants
  • grants, revoking / Revoking grants
  • mysql.user table, modifying / Modifying the mysql.user table
  • password expiry, setting / Setting password expiry for users
  • locking / Locking users
  • roles, creating / Creating roles for users
  • restricting / Restricting networks and users, How to do it...
• user syntax, creating
  • reference link / See also

V

• variables
  • config file / Introduction
  • startup script / Introduction
  • SET command, using / Introduction
• views / Views, How to do it...

W

• window functions
  • about / Window functions
  • row number / Row number
  • partition results / Partition results
  • named windows / Named windows
  • nth values / First, last, and nth values
  • first values / First, last, and nth values
  • last values / First, last, and nth values

X

• X509
  • used, for setting up encrypted connections / Setting up encrypted connections using X509, How to do it...
• XtraBackup
  • used, for obtaining backups / Taking backups using XtraBackup, How to do it...
  • installing / On CentOS/Red Hat/Fedora
  • installing, on CentOS/Red Hat/Fedora / On CentOS/Red Hat/Fedora
  • installing, on Debian/Ubuntu / On Debian/Ubuntu

Y

• YUM repositories
  • used, for installing MySQL / Installing MySQL using YUM/APT
  • using / Using YUM repositories