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
- 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
- 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
- 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
- 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
- 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