MySQL ARCHITECTURE
Storage Engines
Storage engines are MySQL components that handle the SQL operations for different table types. The default engine is InnoDB which is the
most general-purpose storage engine and is recommended for tables except for specialized use cases. MySQL Server uses a pluggable storage
engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.
The SHOW ENGINES statement displays which storage engines a server supports. The value in the Support column indicates whether an engine
can be used. A value of YES, NO, or DEFAULT indicates that an engine is available or not or available and currently set as the default.
SHOW ENGINES;
SHOW GLOBAL VARIABLES LIKE '%engine%';
default_storage_engine - default engine
default_tmp_storage_engine - default engine for temp tables
Storage engine types
InnoDB - supports transactions, row-level locking, and foreign keys
MyISAM - no transaction support, table-level locking, more speed
CSV - stores data in comma-separated text files
MRG_MYISAM - collection of identical MyISAM tables
BLACKHOLE - /dev/null storage engine (anything written to it disappears)
MEMORY - hash based, stored in memory, useful for temporary tables
FEDERATED - accesses data in tables of remote databases rather than in local tables
ARCHIVE - used for storing large amounts of data without indexes in a very small footprint
PERFORMANCE_SCHEMA - performance schema
The InnoDB storage engine
InnoDB is a general purpose storage engine that is the default unless configured otherwise. InnoDB balances reliability and performance and
supports ACID model for transactions, row-level locking and Oracle-style consistent reads, data compression and encryption.
InnoDB page size
The innodb_page_size variable specifies the page size for innoDB tablespaces. This value is set when the instance is created and
remains constant afterward. Valid values are 64kB, 32kB, 16kB (default) 8kB and 4kB.
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; - default 16kB
The default 16KB page size or larger is appropriate for a wide range of workloads, particularly for queries involving table scans and bulk
updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when
single pages contain many rows. Smaller pages might also be efficient with SSD storage devices which typically use small block sizes.
Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.
InnoDB multi-versioning
InnoDB is a multi-versioned storage engine - it keeps information about old versions of changed rows, to support transactional features
such as concurrency and rollback. This information is stored in the tablespace in a data structure called a rollback segment. InnoDB uses
the information in the rollback segment to perform the undo operations needed in a rollback and to build earlier versions of a row for a
consistent read.
Multiversion concurrency control (MVCC) lets InnoDB transactions with certain isolation levels perform consistent read operations; i.e
to query rows being updated by other transactions, and see the values from before those updates occurred. This is a powerful
technique to increase concurrency, by allowing queries to proceed without waiting due to locks.
Internally, InnoDB adds three fields to each row stored in the database.
A 6-byte DB_TRX_ID field indicates the last transaction that inserted or updated the row.
A 7-byte DB_ROLL_PTR field called the roll pointer, pointing to an undo log record written to the rollback segment. If the row was
updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.
A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered
index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.
MVCC treats secondary indexes differently than clustered indexes. Records in a clustered index are updated in-place, and their hidden
system columns point undo log entries from which earlier versions of records can be reconstructed. Unlike clustered index records, secondary
index records do not contain hidden system columns nor are they updated in-place.
The InnoDB buffer pool
InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Ideally, the size of the buffer pool can
be set to as large a value as practical (75% of available RAM is a standard), leaving enough memory for other processes on the server to
run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and
saving it in memory for subsequent reads. Changes made to data pages that are cached in the buffer pool are written to the data files
sometime later, a process known as flushing.
Using the innodb_buffer_pool_instances option, the buffer pool can be divided into a user-specified number of separate regions, each with
its own LRU (Least recently used) list and related data structures to reduce contention during concurrent memory read and write operations.
This option only takes effect when innodb_buffer_pool_size is set to 1GB or more. The total size is divided among all the buffer pools.
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%';
innodb_buffer_pool_size - the size of the buffer pool, making the pool larger can reduce the amount of disk I/O
innodb_buffer_pool_instances - divides the buffer pool into separate regions to improve concurrency
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages%';
Innodb_buffer_pool_pages_data - number of pages in the buffer pool containing data (includes both dirty and clean pages). When
using compressed tables, the value may be larger than pages_total
Innodb_buffer_pool_pages_dirty - current number of dirty pages (data changed in memory but not saved to data files)
Innodb_buffer_pool_pages_free - number of free pages
Innodb_buffer_pool_pages_misc - number of pages that are allocated for administrative overhead, such as row locks or the
adaptive hash index
Innodb_buffer_pool_pages_total - total size of the buffer pool in pages
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
Innodb_buffer_pool_read_requests - number of logical read requests
Innodb_buffer_pool_reads - number of logical reads that InnoDB couldn't satisfy from the pool, and had to read from disk
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free'; - number of times InnoDB had to wait/flush dirty pages for a clean page to be
available. Should be 0 or low if buffer pool size is adequate
InnoDB manages the buffer pool as a list, using a variation of the LRU algorithm. When room is needed to add a new page to the pool, InnoDB
evicts the least recently used page and adds the new page to the middle of the list. This 'midpoint insertion strategy' treats the list as
two sublists - at the head, a sublist of new (or young) pages that were accessed recently and at the tail, a sublist of old pages that were
accessed less recently.
The LRU algorithm operates as follows by default
3/8 of the buffer pool is devoted to the old sublist
The midpoint of the list is the boundary where the tail of the new sublist meets the head of the old sublist
When InnoDB reads a page (for a query or a read-ahead operation) into the buffer pool, it initially inserts it at the midpoint (the head
of the old sublist).
Accessing a page in the old sublist makes it young, moving it to the head of the buffer pool (the head of the new sublist). If the page
was read in because it was required, the first access occurs immediately and the page is made young. If the page was read in due to
read-ahead, the first access does not occur immediately (and might not occur at all before the page is evicted).
As the database operates, pages in the buffer pool that are not accessed age by moving toward the tail of the list. Pages in both the
new and old sublists age as other pages are made new. Pages in the old sublist also age as pages are inserted at the midpoint. Eventually,
a page that remains unused reaches the tail of the old sublist and is evicted
The insertion point in the LRU list can be controlled using the innodb_old_blocks_pct parameter that controls the percentage of the buffer
pool used for the old block sublist. The innodb_old_blocks_time parameter specifies the time in milliseconds a block inserted into the old
sublist must stay there after its first access before it can be moved to the new sublist. Values greater than 0 prevent the new sublist
from being flooded by table scans
SHOW GLOBAL VARIABLES LIKE 'innodb_old_blocks%';
innodb_old_blocks_pct - range 5 to 95, default 37 (3/8 of the pool)
innodb_old_blocks_time - default 1000
Buffer pool flushing
InnoDB uses a flush list to track dirty pages in the buffer pool that need to be flushed. This data structure is updated frequently by
InnoDB's internal mini-transactions and is protected by its own mutex to allow concurrent access to the buffer pool.
Buffer pool flushing is performed by page cleaner threads, whose number is controlled by the innodb_page_cleaners variable, which has a
default value of 4. If innodb_page_cleaners exceeds the number of buffer pool instances, it is automatically set to the same value as
innodb_buffer_pool_instances.
SHOW GLOBAL VARIABLES LIKE 'innodb_page_cleaners';
Buffer pool flushing is initiated when the percentage of dirty pages reaches the low water mark value defined by the
innodb_max_dirty_pages_pct_lwm variable. The default is 10% of buffer pool pages and a value of 0 disables this early flushing behaviour.
The purpose of the innodb_max_dirty_pages_pct_lwm threshold is to control the percentage of dirty pages in the buffer pool and to prevent
dirty pages from reaching the threshold defined by the innodb_max_dirty_pages_pct variable, which has a default value of 90. InnoDB
aggressively flushes buffer pool pages if the innodb_max_dirty_pages_pct threshold is reached.
SHOW GLOBAL VARIABLES LIKE 'innodb_max_dirty_pages_pct%';
innodb_max_dirty_pages_pct - percentage of dirty pages
innodb_max_dirty_pages_pct_lwm - low water mark
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_flushed'; - the number of requests to flush pages from the buffer pool
Adaptive flushing
InnoDB uses an adaptive flushing algorithm that tracks the number of dirty pages in the buffer pool and the rate at which redo log records
are being generated and dynamically adjusts the number of dirty pages to flush each second, ensuring that flushing activity keeps pace with
the current workload. Automatically adjusting the flushing rate helps avoid sudden dips in throughput that can occur when bursts of I/O
activity due to flushing affects the I/O capacity available for ordinary read and write activity.
The innodb_adaptive_flushing_lwm variable defines a low water mark representing percentage of redo log capacity. When that threshold is
crossed, adaptive flushing is enabled, even if the innodb_adaptive_flushing variable is disabled.
SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_flushing%';
innodb_adaptive_flushing - ON by default
innodb_adaptive_flushing_lwm - defaults to 10
Back
Tablespaces
The system tablespace
The system tablespace contains the InnoDB data dictionary (metadata for InnoDB-related objects) and is the storage area for the
doublewrite buffer, the change buffer and undo logs.
The system tablespace is represented by one or more data files. By default, a single system tablespace data file, named ibdata1, is created
in the data directory. The innodb_data_file_path variable defines the name, size and attributes of system tablespace data file(s). The
default value is ibdata1:12M:autoextend.
When the autoextend attribute is specified, the data file automatically increases in size by increments as space is required. The
innodb_autoextend_increment variable controls the increment size.
SHOW GLOBAL VARIABLES LIKE 'innodb_data_%';
innodb_data_file_path - system tablespace data file(s)
innodb_data_home_dir - directory path for the file(s)
SHOW GLOBAL VARIABLES LIKE 'innodb_autoextend_increment'; - the increment size (MB) for extending the file(default = 64)
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_system'; - system tablespace metadata
Once having grown, the ibdata files cannot be shrunk. To reduce the size, the databases need to be dumped and dropped and restored back
after deleting the ibdata and ib_log files and restarting the server.
The InnoDB data dictionary is comprised of internal system tables that contain metadata of objects such as tables, indexes and columns.
The metadata is physically located in the InnoDB system tablespace.
File-per-table tablespaces
If the innodb_file_per_table option is enabled (by default), each table and its indexes are stored in a separate .ibd data file. Each such
.ibd data file represents an individual tablespace. The shared tablespace file ibdata1 is then used to store other internal data - data
dictionary (metadata) of InnoDB tables, change buffer, doublewrite buffer, undo logs. Else all tables are all stored together in a system
tablespace (ibdata files).
Tables created in file-per-table tablespaces use the Barracuda file format which enables features such as compressed and dynamic row
formats. Each table also has a .FRM file which stores the structure of the table. File-per-table tablespace files are auto-extending
regardless of the innodb_autoextend_increment setting. The initial extensions are by small amounts, after which extensions occur in
increments of 4MB.
SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table'; - 0 or 1
RENAME TABLE db1.tblname TO db2.tblname; - moves an .ibd file and associated table to another db
Restoring tables from files
A table can be restored from its .ibd and .frm files by extracting the structure from the .frm file, creating the table, discarding the
tablespace to delete the .ibd file and then importing the required tablespace file.
ALTER TABLE tblname DISCARD TABLESPACE; - delete current .ibd file for a table
ALTER TABLE tblname IMPORT TABLESPACE; - use a new .idb file
MyISAM tables exist in the form of three files - .FRM (table structure), .MYD (data) and .MYI (indexes). The files can be copied into the
/var/lib/mysql/dbname folder to make the table available. REPAIR TABLE repairs a possibly corrupted table. The USE_FRM option is
available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information
in the .MYI file header and to re-create it using information from the .frm file.
General tablespaces
A general tablespace is a shared InnoDB tablespace that is created using the CREATE TABLESPACE statement and can be created in the data directory or outside of it.
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
CREATE TABLESPACE ts1 ADD DATAFILE '/home/usr/ts1.ibd' Engine=InnoDB; - creating outside the data directory
ALTER TABLESPACE ts1 RENAME TO ts2;
DROP TABLESPACE ts1;
If FILE_BLOCK_SIZE is not specified when creating a general tablespace, FILE_BLOCK_SIZE defaults to innodb_page_size and the tablespace may only contain uncompressed tables.
CREATE TABLESPACE ts1 ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.
Temporary tablespace
All non-compressed user-created temporary tables and on-disk internal temporary tables are created in a shared temporary tablespace.
Compressed temporary tables reside in file-per-table tablespace files created in the temporary file directory, defined by tmpdir.
The innodb_temp_data_file_path variable defines the relative path, name, size and attributes of temporary tablespace data files. If no
value is specified, by default a single auto-extending data file named ibtmp1 is created in the innodb_data_home_dir directory.
SHOW GLOBAL VARIABLES LIKE 'innodb_temp_data_file_path'; - temporary tablespace
The INFORMATION_SCHEMA.FILES provides metadata about the temporary tablespace.
SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'; - temp tablespace metadata
The INNODB_TEMP_TABLE_INFO table provides info about user-created InnoDB temporary tables that are active (not internal temporary tables).
The table is created when first queried, exists only in memory and is not persisted to disk.
SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO; - active user-created temp tables
The disk space occupied by the temporary tablespace data file can be reclaimed by restarting the MySQL server, which removes and recreates
the file.
Redo Log and buffer
The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. During normal
operations, the redo log encodes requests to change InnoDB table data that result from SQL statements or low-level API calls. Modifications
that did not finish updating the data files before an unexpected shutdown are replayed automatically during initialization and before the
connections are accepted. By default, the redo log is physically represented on disk as a set of files, named ib_logfile0 and ib_logfile1.
The redo log buffer is the memory area that holds data to be written to the redo log. The redo log buffer is periodically flushed to the
log file on disk. A large redo log buffer enables large transactions to run without the need to write redo log to disk before the
transactions commit. Thus, for transactions that update, insert or delete many rows, making the log buffer larger saves disk I/O.
The redo log files should be sufficiently large, optimally to hold at least an hour's worth of bytes written to the log. When the log file
is full, InnoDB must write the modified contents of the buffer pool to disk in a checkpoint and smaller redo log files cause many
unnecessary disk writes. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed 512GB.
The Log Sequence Number (LSN) is an 8-byte unsigned integer, an arbitrary, ever-increasing value that represents a point in time
corresponding to operations recorded in the redo log. This point in time is regardless of transaction boundaries and can fall in the
middle of transactions. It is used internally by InnoDB during crash recovery and for managing the buffer pool.
InnoDB uses fuzzy checkpointing, a technique that flushes small batches of dirty pages from the buffer pool, rather than flushing all
dirty pages at once which would disrupt database processing. The checkpoint label written to the log file is a record of the latest
changes (represented by an LSN value) that have been successfully written to the data files. During crash recovery all modifications in
the log after the checkpoint are applied.
SHOW GLOBAL VARIABLES LIKE 'innodb_log%';
innodb_log_file_size - size in bytes of each log file in a log group
innodb_log_files_in_group - number of log files in the log group
innodb_log_buffer_size - size in bytes of the buffer that InnoDB uses to write to the log files on disk (default 16MB)
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
innodb_flush_log_at_trx_commit - controls how the contents of the log buffer are written to the log file
innodb_flush_log_at_timeout - controls redo log flushing frequency (default once per sec)
SHOW GLOBAL STATUS LIKE 'innodb_log_writes'; - number of physical writes to the log files
SHOW GLOBAL STATUS LIKE 'innodb_os_log_written';- number of bytes written to the log files
SHOW GLOBAL STATUS LIKE 'innodb_log_waits'; - number of times the log buffer was too small and had to be flushed before continuing
Back
Undo logs
An undo log record contains information about how to undo the change by a transaction to a clustered index record. If another transaction
needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from the undo log records.
An undo log is a collection of undo log records associated with a single transaction. Undo logs exist within undo log segments, contained
within rollback segments which reside in the system tablespace, global temporary tablespace and undo tablespaces.
Each undo tablespace and the temporary tablepace individually support a maximum of 128 rollback segments defined by the
innodb_rollback_segments variable. The number of data-modifying transactions that a rollback segment supports depends on the number of undo
slots in the segment and the number of undo logs required by each transaction. The number of undo slots in a rollback segment (InnoDB page
size/16) for the default 16kB page size is 1024.
SHOW GLOBAL VARIABLES LIKE 'innodb_rollback_segments'; - maximum 128(default)
Undo logs that reside in the global temporary tablespace are used for transactions that modify data in user-defined temporary tables. These
undo logs are not redo-logged, as they are not required for crash recovery and are used only for rollback while the server is running. This
type of undo log benefits performance by avoiding redo logging I/O.
Purging of undo logs no longer needed for MVCC or rollback is done by one or more background threads (controlled by innodb_purge_threads)
running on a periodic schedule. Purge parses and processes undo log pages from the history list.
SHOW GLOBAL VARIABLES LIKE 'innodb_purge_threads';
Doublewrite buffer
The doublewrite buffer is a storage area located in the system tablespace where InnoDB writes pages that are flushed from the InnoDB
buffer pool, before writing to the data file. Only after flushing and writing pages to the doublewrite buffer, does InnoDB write pages to
the data file. If there is an OS, storage or mysqld process crash in the middle of a page write, InnoDB can later find a good copy of
the page from the doublewrite buffer during crash recovery.
SHOW GLOBAL VARIABLES LIKE 'innodb_doublewrite'; - enabled by default
Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations.
Data is written to the doublewrite buffer as a large sequential chunk, with a single fsync() call to the OS.
Doublewrite buffering is enabled by default and can be turned off when top performance is needed rather than concern for data integrity
or possible failures.
Change buffer
The change buffer is a special data structure that caches changes to secondary index pages when the affected pages are not in the buffer
pool. The buffered changes resulting from DML are merged later when the pages are loaded into the buffer pool by other read operations.
Unlike clustered indexes, secondary indexes are usually non-unique and inserts happen in a relatively random order and deletes and updates
may affect pages that are not adjacently located in an index tree. Merging cached changes at a later time, when affected pages are read into
the buffer pool by other operations, avoids substantial random access I/O. Periodically, the purge operation that runs when the system is
mostly idle writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently
than if each value were written to disk immediately.
Change buffer merging may take several hours when there are many affected rows and secondary indexes to update and during this time, disk
I/O is increased and can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a
transaction is committed and even after a server shutdown and restart.
The change buffer occupies part of the buffer pool in memory and on disk, the change buffer is part of the system tablespace where index
changes are buffered when the database server is shut down.
The extent to which InnoDB performs change buffering can be controlled using the innodb_change_buffering configuration parameter.
SHOW GLOBAL VARIABLES LIKE 'innodb_change_buffering';
all - buffer inserts, delete-marking operations and purges (default)
none - do not buffer any operations
inserts - buffer insert operations
deletes - buffer delete-marking operations
changes - buffer both inserts and delete-marking operations
purges - buffer the physical deletion operations that happen in the background
The innodb_change_buffer_max_size variable permits configuring the maximum size of the change buffer as a percentage of the buffer pool
size. The default is set to 25 and the maximum is 50.
SHOW GLOBAL VARIABLES LIKE 'innodb_change_buffer_max_size';
select name, comment from INFORMATION_SCHEMA.INNODB_METRICS where name like '%ibuf%'\G - change buffer metrics
Adaptive hash index
The adaptive hash index (AHI) lets InnoDB perform more like an in-memory database without sacrificing any transactional features or
reliability. This feature is enabled by the innodb_adaptive_hash_index option.
SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index'; - ON by default
Based on the observed pattern of searches, MySQL builds a hash index using a prefix of the index key. The prefix of the key can be any
length and only some of the values in the B-tree may appear in the hash index. Hash indexes are built on demand for those pages of the
index that are often accessed.
If a table fits almost entirely in main memory, a hash index can speed up queries by enabling direct lookup of any element, turning the
index value into a pointer. InnoDB monitors index searches and if it notices that queries could benefit from building a hash index, it
does so automatically.
The adaptive hash index feature is partitioned and partitioning is controlled by the innodb_adaptive_hash_index_parts variable. Each index
is bound to a specific partition and each partition is protected by a separate latch.
SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index_parts'; - default 8, maximum 512
Access to the adaptive hash index can sometimes become a source of contention under heavy workloads. If there are many threads waiting on
RW-latches created in btr0sea.cc, it might be useful to increase the number of partitions or disable adaptive hash indexing.
Thread concurrency
InnoDB uses OS threads to process requests from user transactions. In heavy workloads, the number of concurrent threads can be limited
by setting the configuration parameter innodb_thread_concurrency. Once the number of executing threads reaches this limit, additional
threads sleep for a number of microseconds, set by innodb_thread_sleep_delay, before being placed into the queue.
The default value of innodb_thread_concurrency is 0 and there is no limit on the number of concurrently executing threads. All threads
contend equally to be scheduled and the value of innodb_thread_sleep_delay is ignored.
SHOW GLOBAL VARIABLES LIKE 'innodb_thread_concurrency'; - maximum OS threads that can run concurrently inside InnoDB
SHOW GLOBAL VARIABLES LIKE 'innodb_thread_sleep_delay';
The option innodb_adaptive_max_sleep_delay can be set to the highest value to be allowed for innodb_thread_sleep_delay and InnoDB
automatically adjusts innodb_thread_sleep_delay up or down depending on the current thread-scheduling activity. This dynamic adjustment
helps the thread scheduling mechanism to work smoothly across varying loads.
SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_max_sleep_delay';
Background threads
InnoDB uses background threads to service various types of I/O requests. The number of background threads that service read and write I/O
on data pages can be configured using the innodb_read_io_threads and innodb_write_io_threads parameters. The default value for these parameters is 4 and permissible values range from 1-64.
SHOW GLOBAL VARIABLES LIKE 'innodb_%threads';
innodb_read_io_threads - threads servicing read I/O requests
innodb_write_io_threads - threads servicing write I/O requests
These configuration options make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is read-ahead requests. InnoDB tries to balance the load of incoming requests in such way that most background threads share work equally and also attempts to allocate read requests from the same extent to the same thread to increase the chances of coalescing the requests. On high end I/O subsystems, if more than (64 x innodb_read_io_threads) pending read requests are seen in SHOW ENGINE INNODB STATUS output, performance may be improved by increasing the value of innodb_read_io_threads.
The InnoDB main thread (thread/sql/main) performs various operations in the background including
checking free log space - ensure that there is enough reusable space in the redo log files
doing background drop tables
enforcing dict cache limit - make room in the table cache by evicting an unused table
flushing log - sync redo log to disk
doing insert buffer merge
making checkpoint - flush dirty pages from buffer pool to disk and record the checkpoint LSN
Flushing data from memory
The flushing method used to flush data to data and log files can affect I/O throughput based on hardware configuration and workload. If
innodb_flush_method is set to NULL on a Unix system, the fsync option is used by default and on Windows, the async_unbuffered option is
used by default.
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_method'; - flushing method
The innodb_flush_method options for Unix systems include:
fsync - InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting
O_DSYNC - uses O_SYNC to open and flush the log files, and fsync() to flush the data files
O_DIRECT - uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files
O_DIRECT_NO_FSYNC - InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call afterward
The innodb_flush_method options for Windows systems include:
async_unbuffered - InnoDB uses Windows asynchronous I/O and non-buffered I/O, default on Windows systems
normal: InnoDB uses simulated asynchronous I/O and buffered I/O
unbuffered: InnoDB uses simulated asynchronous I/O and non-buffered I/O
InnoDB I/O capacity
The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as
flushing pages from the buffer pool and merging data from the change buffer. If flushing activity falls behind, InnoDB can flush more
aggressively at a higher rate of IOPS than defined by innodb_io_capacity up to a maximum defined by the innodb_io_capacity_max variable.
Although very high values can be specified for innodb_io_capacity, they have little benefit in practice and a value higher than 20000
is generally not recommended. Systems with large write workloads are likely to benefit from a higher setting. Available I/O capacity is
distributed equally among buffer pool instances for flushing activities.
SHOW GLOBAL VARIABLES LIKE 'innodb_io%';
innodb_io_capacity - default 200
innodb_io_capacity_max - defaults to twice the value of innodb_io_capacity, with a minimum value of 2000
Row formats
The row format of a table determines how its rows are physically stored. InnoDB supports four row formats
REDUNDANT - provides compatibility with older versions of MySQL
COMPACT - reduces row storage space by about 20% compared to the REDUNDANT row format. It is likely to be faster for workloads limited
by cache hit rates and disk speed and slower for those limited by CPU speed
DYNAMIC - offers the same storage characteristics as COMPACT but adds enhanced storage capabilities for long variable-length columns
and supports large index key prefixes
COMPRESSED - offers the same storage characteristics and capabilities as DYNAMIC but adds support for table and index data compression
The innodb_default_row_format option defines the default row format for InnoDB tables and user-created temporary tables, when a ROW_FORMAT
option is not specified explicitly or when ROW_FORMAT=DEFAULT is used.
SHOW GLOBAL variables like 'innodb_default_row_format'; - default DYNAMIC
The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default and can only be
specified explicitly.
When a ROW_FORMAT option is not specified explicitly or when ROW_FORMAT=DEFAULT is used, any operation that rebuilds a table also silently
changes the row format of the table to that defined by innodb_default_row_format. Table-rebuilding operations include ALTER TABLE operations
that use ALGORITHM=COPY or ALGORITHM=INPLACE where table rebuilding is required and the OPTIMIZE TABLE statement.
Internal InnoDB temporary tables created by the server to process queries use the DYNAMIC row format, regardless of the
innodb_default_row_format setting.
Back
The Federated storage engine
For Federated tables, the table definition resides on the local server but the physical storage of data is handled on a remote server.
A Federated table consists of two elements
a remote server with a database table, which consists of the table definition (stored in the MySQL data dictionary) and the associated
table whose type may be any type supported by the remote mysqld server, including InnoDB.
a local server with a database table, where the table definition matches that of the corresponding table on the remote server and is
stored in the data dictionary. There is no data file on the local server and the table definition includes a connection string that points
to the remote table.
Federated tables can be created
using CONNECTION
CREATE TABLE federated_table (
id int(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY (id),
INDEX name (name)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='mysql://fed_user@remote_host:3306/federated/table1';
using CREATE SERVER
CREATE SERVER fedlink FOREIGN DATA WRAPPER mysql OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 3306, DATABASE 'federated');
CREATE TABLE federated_table (
id int(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY (id),
INDEX name (name)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 CONNECTION='fedlink/table1';
Back
MySQL programs
MySQL programs can be invoked from the command line followed by any options or other arguments. Options can also be specified using option
(configuration) files or environment variables.
mysqld
mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to
the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such
as log files and status files. When MySQL server starts, it listens for network connections from client programs and manages access to
databases on behalf of those clients.
shell> mysqld --verbose --help list mysqld startup options
The default configuration file \etc\mysql\my.cnf can be used to override mysqld default options.
mysql_upgrade
mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server and also upgrades the
system tables. If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and attempts a table repair.
mysql_upgrade should be executed after each upgrade of MySQL.
shell> mysql_upgrade --protocol=tcp -P 3306 [other options]
For local host connections on Unix, the --protocol=tcp option forces a connection using TCP/IP rather than the Unix socket file.
MySQL client programs
mysql
mysql is the MySQL command-line client. It is a simple SQL shell with input line editing capabilities and supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively, the result is presented in a tab-separated format. The output format can be changed using command options.
shell> mysql -uusername -p -hhostname - connect to a host, mysql interprets the first nonoption argument as a database name
Options:
--binary-as-hex - display binary values in hex notation (use --skip-binary-as-hex to disable)
--execute "stmt", -e - execute statement and quit
--force, -f - continue even if an SQL error occurs
--help, -? - display information and exit
--host hostname, -hhost - connect to mysql server on the given host
--local-infile - enable LOCAL capability for LOAD DATA INFILE
--password=pwd -ppwd - password to use, mysql will prompt if omitted
--port=num, -P num - port number to use for the connection
--quick, -q - don't cache query results, print each row as it is received
--show-warnings
--user username, -uuser - user name to connect to the server
--xml - get output in xml format
shell> mysql db1 -uuser -p -hhost -e "select * from t1;" | sed 's/\t/|/g' > t1.txt - get output in pipe-delimited format
mysql client commands
mysql sends each SQL statement issued to the server to be executed. There is also a set of commands that mysql itself interprets. Typing help or \h at the mysql> prompt lists these commands.
clear (\c) - clear the current input statement
exit (\q) - exit mysql (same as quit)
help (\h) - help
quit (\q) - quit mysql
source (\.) - execute an sql script file, takes file name as argument
use (\u) - use another database, takes DB name as argument
Executing SQL statements from a text file
shell> mysql db1 < file1.sql > out.txt
mysql> source file1.sql
mysql> \. file1.sql
mysqlcheck
The mysqlcheck client performs table maintenance - checks, repairs, optimizes or analyzes tables by using the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE. Each table is locked and therefore unavailable to other sessions while it is being processed. mysqlcheck must be used when the mysqld server is running. Table maintenance operations can be time-consuming, particularly for large tables.
mysqlcheck -u user -p db_name tbl_name - default option --check
mysqlcheck --repair -u user -p db_name tbl_name - repair table
Option (configuration) files
Most MySQL programs can read startup options from option (configuration) files. Option files provide a convenient way to specify commonly
used options so that they need not be entered on the command line each time when running a program.
To determine whether a program reads option files, it can be invoked with the --help option (for mysqld, --verbose and --help). If the
program reads option files, the help message indicates which files it looks for and which option groups it recognizes.
mysql --help
mysqld --verbose
Option files read on Unix and Unix-like systems
File Name Purpose
--------- -------
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
Option files read on Windows systems
%WINDIR%\my.ini, %WINDIR%\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
BASEDIR\my.ini, BASEDIR\my.cnf Global options BASEDIR = MySQL base installation directory
The syntax for specifying options in an option file is similar to command-line syntax, except that in an option file, the leading two dashes
are omitted from the option name and only one option per line is specified. Hyphens and underscores are both usable in option names. Comment
lines start with # or ; and a # comment can start in the middle of a line.
The [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program respectively and the [client] group specifies
options that apply to all clients.
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
innodb_buffer_pool_size=16G
max_allowed_packet=128M
[mysqldump]
quick
The data directory
Information managed by the MySQL server is stored under a directory known as the data directory. The --datadir option enables the location
of the data directory to be changed. Each subdirectory of the data directory is a database directory and corresponds to a database
managed by the server.
SHOW GLOBAL VARIABLES LIKE 'datadir'; - data directory
All MySQL installations have certain system databases -
mysql - contains information required by the MySQL server as it runs
information_schema - is a standard database, but its implementation uses no corresponding database directory
performance_schema - provides information used to inspect the internal execution of the server at runtime
sys - the sys schema provides a set of objects to help interpret Performance Schema information more easily
ndbinfo - corresponds to the ndbinfo database that stores information specific to MySQL Cluster
Other subdirectories may include - Log files written by the server, InnoDB tablespace and log files, Default/autogenerated SSL and RSA
certificate and key files and the server process ID file (while the server is running).
Copying an existing data directory
An existing data directory can be copied over to create a new MySQL instance in the same server or a new server.
1. Stop the existing MySQL instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes
to disk
2. Copy the data directory to a new location in the same server or a different server with MySQL installed
3. Copy the existing my.cnf file and make relevant changes to point to the new data directory
4. Start the new MySQL instance using the modified configuration file
MySQL data dictionary
MySQL server incorporates a transactional data dictionary that stores information about database objects. In earlier MySQL releases,
dictionary data was stored in metadata files, nontransactional tables and storage engine-specific data dictionaries.
Data dictionary tables are protected and may only be accessed in debug builds of MySQL, but data stored in data dictionary tables can be
accessed through INFORMATION_SCHEMA tables and SHOW statements.
The data dictionary schema stores dictionary data in transactional (InnoDB) tables that are located in the mysql database together with
non-data dictionary system tables. Data dictionary tables are created in a single InnoDB tablespace named mysql.ibd, which resides in the
data directory. Dictionary data is protected by the same commit, rollback and crash-recovery capabilities that protect user data stored in
InnoDB tables.
Dictionary Object Cache
The dictionary object cache is a shared global cache that stores previously accessed data dictionary objects in memory to enable object
reuse and minimize disk I/O. Similar to other MySQL cache mechanisms, the dictionary object cache uses an LRU-based strategy to evict
objects from memory.
The dictionary object cache comprises cache partitions that store different object types. Some cache partition size limits are configurable,
whereas others are hardcoded.
tablespace definition cache partition - stores tablespace definition objects. The tablespace_definition_cache option sets a limit for
the number of tablespace definition objects that can be stored in the dictionary object cache
schema definition cache partition - stores schema definition objects
table definition cache partition - stores table definition objects. The object limit is set to the value of max_connections, which has
a default value of 151. The table definition cache partition exists in parallel with the table definition cache that is configured using
the table_definition_cache option. Both caches store table definitions but serve different parts of the MySQL server and objects in one
cache have no dependence on the existence of objects in the other
stored program definition cache partition - stores stored program definition objects. The stored program definition cache partition
exists in parallel with the stored procedure and stored function caches that are configured using the stored_program_cache option.
The stored_program_cache option sets a soft upper limit for the number of cached stored procedures or functions per connection and the
limit is checked each time a connection executes a stored procedure or function. The stored program definition cache partition, on the
other hand, is a shared cache that stores stored program definition objects for other purposes.
character set definition cache partition - stores character set definition objects and has a hardcoded object limit of 256
collation definition cache partition - stores collation definition objects and has a hardcoded object limit of 256
show global variables like 'tablespace_definition_cache'; - default 256
show global variables like 'schema_definition_cache'; - default 256
show global variables like 'table_definition_cache'; - default -1 (autosizing)
show global variables like 'stored_program_definition_cache'; - default 256
show global variables like 'stored_program_cache'; - default 256
Internal temporary tables
MySQL creates internal temporary tables while processing some statements like those using UNION, GROUP BY or derived tables. The Explain
output for these statements shows Using temporary in the Extra column. An internal temporary table can be held in memory and processed by
the MEMORY storage engine or stored on disk by the InnoDB storage engine.
The maximum size of in-memory temporary tables is determined from whichever of the values of tmp_table_size and max_heap_table_size is
smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table that is created
in the ibtmp1 temporary tablespace file in the data directory.
For MEMORY tables explicitly created with CREATE TABLE, only the max_heap_table_size variable determines how large a table can grow and
there is no conversion to on-disk format.
SHOW GLOBAL VARIABLES like 'tmp_table_size'; - maximum size of internal in-memory temporary tables
SHOW GLOBAL VARIABLES like 'max_heap_table_size'; - maximum size of MEMORY tables
SHOW GLOBAL VARIABLES like 'big_tables'; - if set to 1, all temp tables are created on disk (default=OFF)
SHOW GLOBAL STATUS like 'Created%';
Created_tmp_tables - number of internal temporary tables created while executing statements
Created_tmp_disk_tables; - number of internal on-disk temp tables
Temporary files
On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If
TMPDIR is not set, MySQL uses the system default, usually /tmp, /var/tmp or /usr/tmp. On Windows, MySQL checks in order the values of the
TMPDIR, TEMP and TMP environment variables. If none of them are set, MySQL uses the system default, which is usually C:\windows\temp\.
If the file system containing the temporary file directory is too small, the tmpdir option can be used to specify a directory in a file
system where there is enough space.
The tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon
characters (:) on Unix and semicolon characters (;) on Windows.
SHOW GLOBAL VARIABLES like 'tmpdir'; - path for temp files
SHOW GLOBAL STATUS like 'Created_tmp_files'; - number of temporary files created
Back
MySQL server logs
Binary Log
The binary log contains events that describe database changes via DCL and DML. It also contains events for statements that potentially
could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains
information about how long each statement took that updated data. The binary log has two important purposes:
For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The
master server sends the events contained in its binary log to its slaves, which execute those events to sync with the master.
Certain data recovery operations require use of the binary log. After a backup is restored, the events in the binary log that were
recorded after the backup was taken are re-executed to bring databases up to date from the point of the backup.
The binary log consists of a set of files named using the base name appended with a sequence number, plus an index file that contains the
file names to keep track of which files have been used. The server creates a new file in the series each time it starts, flushes the logs
or when the maximum file size is reached.
SHOW BINARY LOGS; - lists the binary log files
SHOW GLOBAL VARIABLES LIKE '%binlog%';
binlog_cache_size - the size of the cache to hold changes to the binary log during a transaction
max_binlog_size - log file size, if exceeded the server opens a new log file
binlog_format - the binary logging format, can be STATEMENT, ROW or MIXED
sync_binlog - number of binary log commit groups to collect before synchronizing to disk
SHOW GLOBAL VARIABLES LIKE 'log_bin%';
log_bin - ON by default, can be used to specify a base name and location for the binary log
log_bin_basename - the name and complete path to the binary log file
log_bin_index - the index file for binary log file names
The PURGE BINARY LOGS statement (needs SUPER or BINLOG_ADMIN privilege) deletes all the binary log files listed in the log index file prior
to the specified log file name or date.
SHOW GLOBAL VARIABLES LIKE '%expire%';
expire_logs_days - the number of days for automatic binary log file removal (0 - no auto removal)
PURGE BINARY LOGS TO 'mysql-bin.012';
PURGE BINARY LOGS BEFORE '2015-06-02 22:46:26';
There are three logging formats:
Statement - Replication in MySQL originally was based on propagation of SQL statements from master to slave, called statement-based logging
Row - In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected
Mixed - With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain
cases
The SHOW BINLOG EVENTS statement shows the events in the binary log. If the log name is not specified, the first binary log is displayed. It requires the REPLICATION SLAVE privilege.
SHOW BINLOG EVENTS LIMIT 20; - shows events in the first binary log
SHOW BINLOG EVENTS IN 'mysql-bin.000220' FROM 594 LIMIT 10; - shows events from pos 594
SHOW BINLOG EVENTS displays the following fields for each event
log_name - log that is being listed
pos - position of the event
event_type - describes the event type
server_id - server id of the server where the event originated
end_log_pos - the position at which the next event begins, equal to pos plus the size of the event
info - information about the event
When sync_binlog=0, the binary log is never synchronized to disk, and when sync_binlog is set to a value > 0 this number of binary
log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before
they are committed and in the event of an unexpected restart, any transactions that are missing from the binary log are only in prepared state and will be rolled back by the server's automatic recovery routine. This guarantees that no transaction is lost from the binary log, and is the safest option. However this impacts performance because of an increased number of disk writes. Using a higher value improves performance with the increased risk of data loss.
The sql_log_bin session variable controls whether logging to the binary log is enabled for the current session. Setting the variable to OFF
prevents transactions in the current session from being written to the binary log or GTIDs being assigned to them.
SHOW VARIABLES LIKE 'sql_log_bin'; - default ON
The general query log
It is a general record of mysqld activity. The server writes information to this log when clients connect or disconnect and it logs
each SQL statement received from clients. The general query log can be very useful during troubleshooting to know exactly what a client sent to mysqld.
mysqld writes statements to the query log in the order that it receives them which might differ from the order in which they are executed. If no name is specified for the general query log file, the default name is host_name.log created in the data directory.
SHOW GLOBAL VARIABLES LIKE 'general_log%';
general_log - whether general log is enabled (ON/OFF)
general_log_file - file location
SHOW GLOBAL VARIABLES LIKE 'log_output';
log_output - can contain one or more of options FILE,TABLE,NONE (Default=FILE)
The error log
The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the
server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
SHOW GLOBAL VARIABLES like 'log_error'; - error log file
SHOW GLOBAL VARIABLES like 'log_error_verbosity; - the verbosity of the server in writing messages to the error log
1 - errors
2 - errors & warnings
3 = errors, warnings & informational messages (Default)
The slow query log
The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least
min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10 respectively. The value can be
specified to a resolution of microseconds.
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'min_examined_row_limit'; - default 0
SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes'; - default OFF
SET GLOBAL long_query_time=1.500;
SET GLOBAL log_output='table'; - divert output to mysql.slow_log
SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log=ON;
SHOW GLOBAL STATUS like 'Slow_queries'; - number of queries that have taken more than long_query_time seconds
The general and slow query log files can be cleared at runtime without a server restart by disabling the log, renaming the log file and
enabling the log again.
The DDL log or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE.
MySQL uses this log to recover from crashes occurring in the middle of a metadata operation.
Back
DATABASE OBJECTS
Databases
SHOW DATABASES; - list databases
SHOW DATABASES like '%test%';
SELECT table_schema 'db', round(SUM(data_length + index_length)/1024/1024/1024,2) 'Size(GB)', round(SUM(data_length +
index_length)/1024/1024,2) 'Size(MB)' FROM information_schema.tables GROUP BY table_schema; - size of databases
SELECT table_schema, table_name, round(data_length/1024/1024,2) 'Data Size(MB)', round(index_length/1024/1024,2) 'Index Size(MB)'
FROM information_schema.tables where table_schema = 'db1' ORDER BY data_length + index_length DESC; - large tables
SELECT CONCAT('rename table db1.', table_name , ' to db2.' , table_name,';') FROM information_schema.TABLES
WHERE table_schema = 'db1'; - move tables
The READ ONLY option controls modification of the database and objects within it. The permitted values are DEFAULT or 0 (not read only) and 1 (read only). This option is useful for database migration as a READ ONLY database can be migrated to another MySQL instance without concern that the database might be changed during the operation.
ALTER DATABASE db1 READ ONLY = 1;
Information_schema
Information_schema is a database within each MySQL instance that stores metadata information about all other databases. It contains several
read-only tables that are actually views, not base tables, so there are no files associated with them and there is no database directory
with that name.
select * from information_schema.schemata; - information about databases
SELECT * FROM information_schema.TABLES where table_name = 't1' and table_schema = 'testdb'; - table details
SELECT table_name FROM information_schema.COLUMNS where table_schema = 'db1' AND column_name LIKE '%c1%'; - tables with column c1
All MySQL users have the right to access most information_schema tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. Tables with names beginning with INNODB_ require the PROCESS privilege.
Tables
A table can have a maximum of 1017 columns and 64 secondary indexes. The maximum table size is the maximum tablespace size which depends
on the innodb_page_size (64TB for the default 16KB page size). The maximum row size for the default page size of 16KB is about 8000 bytes.
CREATE TABLE t1 (c1 int(10) unsigned auto_incement, c2 varchar(255), primary key (c1), key ix_1 (c2)) default charset=utf8;
CREATE TABLE t1 LIKE t2; - create t2 modeled after t1
SHOW TABLES like '%pat%'; - lists tables and views
SHOW TABLE STATUS like '%pat%'; - lists table details
DESC(RIBE) table1; - show table structure
SHOW CREATE TABLE table1; - get create table syntax
SHOW COLUMNS/FIELDS from t1; - display column information for tables/views
Altering tables
RENAME TABLE t1 TO t2; - rename table
ALTER TABLE t1 ADD PRIMARY KEY(col1,col2); - add primary key
ALTER TABLE t2 DROP PRIMARY KEY; - drop primary key
ALTER TABLE t1 ADD COLUMN col1 int unsigned AFTER col2; - add a column
ALTER TABLE t1 ADD COLUMN col1 int FIRST; - add a column at the top
ALTER TABLE t1 ADD COLUMN col1 int PRIMARY KEY AUTO_INCREMENT FIRST; - add an auto-increment column
ALTER TABLE t1 CHANGE OldCol NewCol DATATYPE; - rename column
ALTER TABLE t1 DROP COLUMN col1; - drop a column
ALTER TABLE t1 MODIFY COLUMN col1 DATATYPE; - change column definition
ALTER TABLE t1 ALTER col1 SET DEFAULT 'abc'; - change default value
ALTER TABLE t1 MODIFY COLUMN col1 VARCHAR(50) AFTER col2; - move a column
ALTER TABLE t1 MODIFY `id` int unsigned, DROP PRIMARY KEY, ADD PRIMARY KEY (`uid`); - drop auto-incr primary key
ALTER TABLE t1 MODIFY col1 int COMMENT 'comment'; - add a comment for column
ALTER TABLE t1 ENGINE = InnoDB; - change storage engine
ALTER TABLE t1 AUTO_INCREMENT = 5; - set next value of auto_increment column
Constraints
Creation of a foreign key constraint requires the REFERENCES privilege for the parent table. Corresponding columns in the foreign key and
the referenced key must have similar data types. The length of string types need not be the same. For nonbinary (character) string columns,
the character set and collation must be the same.
When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on
the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. MySQL supports five options
CASCADE - automatically delete or update the matching rows in the child table. Cascaded foreign key actions do not activate triggers
SET NULL - set the foreign key column or columns in the child table to NULL. The columns in the child table should not be declared as
NOT NULL
RESTRICT - rejects the delete or update operation for the parent table, this is the default action
NO ACTION - a keyword from standard SQL, equivalent to RESTRICT
SET DEFAULT - this action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE
SET DEFAULT or ON UPDATE SET DEFAULT clauses
ALTER TABLE t1 ADD CONSTRAINT cons_name FOREIGN KEY (col1) REFERENCES t2(col2); - add a foreign key
ALTER TABLE tl DROP FOREIGN KEY fk1; - drop foreign key
SELECT * FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY'; - list all foreign keys
SELECT * FROM information_schema.key_column_usage WHERE referenced_table_name = 'tbl'
AND referenced_column_name = 'col1' AND table_schema = 'db1'; - foreign keys pointing to a column
SET foreign_key_checks = 0; - ignore foreign key constraints for current session
ALTER TABLE t1 ADD CONSTRAINT cons_name UNIQUE (col1,col2); - add a unique constraint
ALTER TABLE t1 DROP INDEX uq_ix; - drop a unique constraint
Temporary tables
Temporary tables are created by using the TEMPORARY keyword - CREATE TEMPORARY TABLE. A temp table is visible only to the current
connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same
temporary table name without conflicting with each other.
Compressing tables
An InnoDB table created with ROW_FORMAT=COMPRESSED has compression enabled and can use a smaller page size on disk than the configured innodb_page_size value. Smaller pages require less I/O to read from and write to disk.
CREATE TABLE t1 (c1 int(10) unsigned PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
If ROW_FORMAT=COMPRESSED is used, KEY_BLOCK_SIZE can be omitted and the default compressed page size of 8KB is used. If KEY_BLOCK_SIZE is
used, ROW_FORMAT=COMPRESSED can be omitted and compression is enabled automatically.
In the buffer pool, the compressed data is held in small pages, with a page size based on the KEY_BLOCK_SIZE value. For extracting or updating the column values, MySQL also creates an uncompressed page in the buffer pool with the uncompressed data and any updates to the uncompressed page are also written to the equivalent compressed page.
Partitioning
Partitioning makes it possible to store more data in one table than can be held on a single disk or file system partition. Redundant data can be easily removed by dropping the partition containing only that data. Some queries can be greatly optimized if data satisfying a given WHERE clause is stored only on one or more partitions.
SELECT * FROM t PARTITION (p0,p1) WHERE c < 5; - selects only those rows in partitions p0 and p1
Partitioning types
RANGE - assigns rows to partitions based on column values falling within a given range. If the column value is NULL, the row is assigned to the lowest partition
LIST - similar to RANGE, except that the partition is selected based on columns matching one of a set of discrete values. Rows with NULL column values are admitted only if one of partitions is defined with NULL in its value list
HASH - a partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be
inserted into the table. The function may consist of any expression valid in MySQL that yields a nonnegative integer value
A partitioning key must be either an integer column or an expression that resolves to an integer.
CREATE TABLE t1 (
emp_id int not null,
store_id int not null )
partition by range (store_id) (
partition p0 values less than (25),
partition p1 values less than (50),
partition p2 values less than (100));
ALTER TABLE t1 ADD PARTITION (partition p3 values less than (200));
ALTER TABLE t1 DROP PARTITION p0;
Every unique key on a partitioned table (including the primary key) must use every column in the table's partitioning expression. Also, partitioned tables using the InnoDB storage engine do not support foreign keys.
Partition management
Rebuilding partitions has the same effect as dropping all records stored in the partition and then reinserting them and is useful for defragmentation.
ALTER TABLE t1 REBUILD PARTITION p0, p1;
ALTER TABLE t1 TRUNCATE PARTITION p0;
ALTER TABLE t1 TRUNCATE PARTITION ALL; - truncate all partitions
Defragmenting a table
Random insertions/deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering
of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in
the 64-page blocks that were allocated to the index.
ALTER TABLE tbl_name FORCE; - performs a null alter operation that rebuilds the table
Online DDL
The InnoDB online DDL feature permits in-place table alterations or concurrent DML by using the LOCK and ALGORITHM clauses of the ALTER TABLE statement.
ALGORITHM=INSTANT - only modifies metadata in the data dictionary. An exclusive metadata lock on the table may be taken briefly during the execution phase of the operation
ALGORITHM=INPLACE - alters table without copying. Permits concurrent DML
ALGORITHM=COPY - copies the table
LOCK=EXCLUSIVE - blocks access to the table entirely
LOCK=SHARED - allows queries but not DML
LOCK=NONE - allows full query and DML access to the table
LOCK=DEFAULT or omitting the LOCK clause - permits as much concurrency as possible depending on the type of DDL operation
ALGORITHM=INSTANT is used by default or supported for adding, dropping and renaming columns, setting or dropping column default values and renaming tables. ALGORITHM=INPLACE is supported for adding, dropping or renaming secondary indexes, dropping a primary key and adding another, for adding foreign key constraints (with foreign_key_checks disabled) and dropping foreign keys.
The innodb_online_alter_log_max_size option specifies an upper limit in bytes on the size of the temporary log files used during online
DDL operations. There is one log file for each index being created or table being altered that stores data inserted, updated or deleted in
the table during the DDL operation. The temporary log file is extended when needed by the value of innodb_sort_buffer_size up and if it
exceeds the upper size limit, the ALTER TABLE operation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large
value for this option allows more DML to happen during an online DDL operation, but also extends the period of time at the end of the DDL
operation when the table is locked to apply the data from the log.
SHOW GLOBAL VARIABLES LIKE 'innodb_online_alter_log_max_size'; - default 128MB
Indexes
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use
R-trees and that MEMORY tables also support hash indexes. Spatial indexes are only available on MyISAM tables.
When a primary key is defined on a table, InnoDB uses it as the clustered index. If not, MySQL locates the first unique index where all the key columns are NOT NULL and InnoDB uses it as the clustered index. If the table has no primary key or suitable unique index, InnoDB
internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the
secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.
An index can consist of upto 16 columns. In a multi-column index, any leftmost prefix of the index can be used by the optimizer to find
rows.
The cardinality of an index is the estimated number of unique values in the index. The higher the cardinality, the greater the chance that
MySQL uses the index.
CREATE INDEX ix1 ON t1 (col1, col2, col3); - create index
CREATE UNIQUE INDEX ix1 ON t1 (col1, col2, col3); - create a unique index (unique columns can have nulls)
ALTER TABLE t1 ADD INDEX ix1(c1), ADD INDEX ix2(c2), ALGORITHM=INPLACE, LOCK=NONE; - add an index without locking table
ALTER TABLE tl DROP INDEX ix1; - drop index
SHOW INDEX FROM db1.tbl1; - returns table index information
SELECT distinct table_name, index_name from information_schema.statistics where table_schema = 'db1'; - list all indexes
SELECT * FROM information_schema.tables t LEFT JOIN information_schema.statistics s ON t.table_name = s.table_name
WHERE s.table_name is null AND t.table_schema = 'db1'; - tables with no indexes
SELECT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.statistics s
ON t.table_schema=s.table_schema AND t.table_name=s.table_name AND s.non_unique=0
WHERE s.table_name IS NULL AND t.table_schema = 'db1'; - tables with no primary key
SELECT object_schema, object_type, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'db1' AND index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_type, object_name;
- unused indexes
SELECT p.object_schema, p.object_type, p.object_name, p.index_name, (i.stat_value * @@innodb_page_size) /1024/1024 'index_size'
FROM performance_schema.table_io_waits_summary_by_index_usage p, mysql.innodb_index_stats i
WHERE i.stat_name='size' AND p.object_schema = i.database_name AND p.object_name = i.table_name AND p.index_name = i.index_name
AND object_schema = 'db1' AND p.index_name IS NOT NULL AND p.index_name <> 'PRIMARY' AND p.count_star = 0
ORDER BY index_size DESC; - unused indexes by size
SELECT object_schema, object_type, object_name, count_read FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'db1' AND index_name IS NULL AND count_read > 0 ORDER BY count_read desc limit 20;
- full table scans with rows read
SELECT object_schema, object_type, object_name, index_name, count_read FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'db1' AND object_name = 't1' ORDER BY count_read desc; - rows read by index
SELECT * from t1 FORCE INDEX (ix_t1_c1), t2 where t1.c1 = t2.c1; - force index usage
Full-text indexes
Full-text indexes are of FULLTEXT type and can be created only for text-based columns (CHAR, VARCHAR or TEXT) to help speed up queries on
data in those columns, omitting any words that are defined as stopwords.
For large data sets, it is much faster to load the data into a table that has no FULLTEXT index and then create the index than to load data
into a table that has an existing FULLTEXT index.
CREATE FULLTEXT INDEX ix_ft_col1 ON tbl1(col1);
Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be
searched and AGAINST takes a string to search for and an optional modifier that indicates the type of search to perform.
SELECT id, name, MATCH(name) AGAINST ('search_string') AS relevancy FROM table1; - perform a fulltext index search
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); - perform a natural language search
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD; - default stopword list
Boolean full-text searches can be performed using the IN BOOLEAN MODE modifier. The + and - operators indicate that a word must be present
or absent, respectively, for a match to occur.
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
- retrieves all the rows that contain the word 'MySQL' and do not contain the word 'YourSQL'
Back
Stored objects
Stored objects include these object types
Views
Stored routines - stored procedures and functions
Triggers
Events
Stored routines, triggers and events are together referred to as stored programs.
Views
MySQL supports views, including updatable views. Views are stored queries that when invoked produce a result set. A view acts as a virtual
table.
The optional ALGORITHM clause for CREATE/ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view.
ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.
For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view
definition replace corresponding parts of the statement.
For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE because MERGE is usually more efficient and
a TEMPTABLE view cannot be updatable
In TEMPTABLE views, locks can be released on underlying tables after the temporary table has been created and before it is used to finish
processing the statement. This might result in quicker lock release than the MERGE algorithm.
For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table.
A view is not updatable if it contains aggregate functions or window functions, DISTINCT, GROUP BY, HAVING, UNION/UNION ALL and certain
subqueries and joins. MySQL sets a view updatability flag at CREATE VIEW time if update operations are legal for the view. The IS_UPDATABLE
column in the information_schema.VIEWS table displays the status of this flag.
select * from information_schema.views; - metadata about views
Stored routines
A stored routine is either a procedure or a function. A stored procedure is invoked using the CALL statement and does not have a return
value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client
program.
A stored function is used much like a built-in function, invoked in an expression and returns a value
SHOW PROCEDURE STATUS WHERE db = 'db1'; - list procedures
SHOW FUNCTION STATUS; - list functions
SHOW CREATE PROCEDURE proc1; - display procedure
SHOW CREATE FUNCTION func1;
To use SHOW CREATE PROCEDURE/FUNCTION, the global SELECT privilege is required. The information_schema.ROUTINES table provides information
about stored routines.
SELECT * FROM information_schema.routines where routine_type = 'FUNCTION' and routine_name = 'func1';
SELECT * FROM information_schema.routines where routine_type = 'PROCEDURE' and routine_name = 'proc1';
Creating stored routines
Stored routines are created with the CREATE PROCEDURE and CREATE FUNCTION statements, which require the CREATE ROUTINE privilege. If the DEFINER clause is present, the privileges required depend on the user value. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges variable.
CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE [DEFINER = user] FUNCTION fn_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
characteristic:
COMMENT 'string'
LANGUAGE SQL
[NOT] DETERMINISTIC
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
SQL SECURITY { DEFINER | INVOKER }
The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be
used. For procedures, parameters are specified as IN, OUT or INOUT before the parameter name. An IN parameter passes a value into a
procedure and an OUT parameter passes a value from the procedure back to the caller. Each parameter is an IN parameter by default. For a
function, parameters are always regarded as IN parameters.
The RETURNS clause is mandatory for functions and indicates the return type of the function and the function body must contain a RETURN
value statement.
A routine is considered deterministic if it always produces the same result for the same input parameters and not deterministic otherwise.
The default is NOT DETERMINISTIC. If binary logging is enabled, a CREATE FUNCTION statement must specify at least one of DETERMINISTIC, NO SQL or READS SQL DATA explicitly to declare that either the function is deterministic or it does not modify data. Also stored function creators/alterers must have the SUPER privilege in addition to the CREATE ROUTINE/ALTER ROUTINE privilege to safeguard against unsafe statements.
To drop a stored routine, the ALTER ROUTINE privilege is required for it.
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
The ALTER PROCEDURE/FUNCTION statement can be used to change the characteristics of a stored routine. However, the parameters or body of a stored routine cannot be changed using this statement and to make such changes, the routine must be dropped and re-created. The ALTER ROUTINE privilege for the routine is required for ALTER PROCEDURE/FUNCTION.
The CALL statement invokes a stored procedure. The EXECUTE privilege enables use of statements that execute stored routines.
CALL sp_name([parameter[,...]]);
CALL sp_name[()];
Sample routines
DELIMITER $$
USE `SAMPLEDB`$$
DROP PROCEDURE IF EXISTS usp_getCity$$
CREATE DEFINER=`root`@`localhost` PROCEDURE usp_getCity(IN cityid INT, OUT cityname VARCHAR(50))
BEGIN
IF cityid = 0 THEN
SET cityname = NULL
ELSE
SELECT city into cityname FROM city where city_id = cityid;
END IF;
END$$
DELIMITER ;
call usp_getCity (1, @city);
select @city;
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
Triggers
A trigger is a named database object associated with a table and activated when an event (like insert or update) occurs for the table.
The TRIGGER privilege is needed to display/create/drop/execute triggers.
SHOW TRIGGERS; - list triggers in current database
SHOW TRIGGERS like 'tbl%'; - show triggers for specific tables
SELECT * FROM information_schema.triggers;
SHOW CREATE TRIGGER trig1;
The below sample trigger is invoked when the stock table is updated.
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `trig1`$$
CREATE /*!50017 DEFINER = 'root'@'%' */ TRIGGER `trig1` AFTER UPDATE ON `stock`
FOR EACH ROW BEGIN
DECLARE stockqty DOUBLE;
DECLARE productid TINYINT;
DECLARE storeid INT;
SELECT quantity INTO stockqty FROM stock s WHERE s.store_id = NEW.store_id AND s.product_id = NEW.product_id;
IF stockqty < 500 THEN
INSERT INTO low_stock (store_id, product_id) VALUES (NEW.store_id, NEW.product_id);
END IF;
END;
$$
DELIMITER ;
With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers do not
execute on the slave and the row changes on the master resulting from trigger execution are replicated and applied.
Events
An event is a task that the server runs according to a schedule. The MySQL event scheduler manages the scheduling and execution of events,
which are named database objects containing one or more SQL statements. The event scheduler is implemented as a special thread. When
running, the event scheduler thread and its current state can be seen in the output of SHOW PROCESSLIST with the PROCESS privilege.
SHOW GLOBAL VARIABLES LIKE 'event_scheduler'; - whether event scheduler is enabled (ON/OFF/DISABLED)
If event_scheduler has not been set to DISABLED, it can be toggled between ON and OFF.
The EVENT privilege is needed to create, drop or show events. The event scheduler writes information about event execution that terminates
with an error or warning to the MySQL error log.
Metadata about events can be obtained in the following ways
SHOW EVENTS; - list events in current database
SHOW EVENTS FROM db1;
SHOW CREATE EVENT event1;
select * from information_schema.events;
last_executed - time the event last executed
Creating an event
An event is associated with a schema and is created in the default schema or the schema qualifying the event name.
DROP EVENT IF EXISTS db1.event1;
delimiter |
CREATE EVENT db1.event1
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + interval 1 hour
COMMENT 'Saves and clears table each day'
DO BEGIN
INSERT INTO t1_backup SELECT * FROM t1;
TRUNCATE TABLE t1;
END |
delimiter ;
When an event is created on a replication master, the status of the event is set to SLAVESIDE_DISABLED on the slave. When promoting such
slaves to be a master, those events need to be enabled.
SELECT event_schema, event_name FROM information_schema.events WHERE status = 'SLAVESIDE_DISABLED';
ALTER EVENT event1 ENABLE/DISABLE/DISABLE ON SLAVE; - enable/disable event
Access control for stored programs and views
Stored programs and views, when referenced, execute within a security context whose privileges are controlled by the DEFINER attribute
and the SQL SECURITY characteristic.
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER/INVOKER
BEGIN ... END
All stored programs (procedures, functions, triggers and events) and views can have a DEFINER attribute that names a MySQL account. If the DEFINER attribute is omitted, the default account is the user who creates the object. The SET_USER_ID or SUPER privilege is required to specify any account as the DEFINER attribute. Additionally, to set the DEFINER attribute to an account that has the SYSTEM_USER privilege, SYSTEM_USER privilege is needed. Otherwise, the only permitted definer is the current user.
In addition, stored routines (procedures and functions) and views can have an SQL SECURITY characteristic with a value of DEFINER or
INVOKER to specify the context that the object executes in. If omitted, the default is definer context.
Triggers and events have no SQL SECURITY characteristic and always execute in definer context, as there is no invoking user.
Object names
Object names are known as identifiers. An identifier may be quoted or unquoted. If an identifier contains special characters or is a
reserved word it must be quoted whenever it is referred, with one exception. A reserved word that follows a period in a qualified name
must be an identifier, so it need not be quoted. The identifier quote character is the backtick (`).
Internally, identifiers are converted to and are stored as Unicode (UTF-8).
Permitted characters in unquoted identifiers
ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
Extended: U+0080 .. U+FFFF
Permitted characters in quoted identifiers
ASCII: U+0001 .. U+007F
Extended: U+0080 .. U+FFFF
ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers
Identifiers may begin with a digit but unless quoted may not consist solely of digits
Database, table and column names cannot end with spaces
Since databases, tables and triggers have a folder or file named after them, consequently, the case sensitivity of the underlying OS plays
a part in the case sensitivity of database, table and trigger names. The names are not case-sensitive in Windows but are case-sensitive in
most varieties of Unix.
The lower_case_table_names system variable affects how table and database names are stored on disk and used in MySQL. It can only be
configured when initializing the server and changing it after the server is initialized is prohibited.
show global variables like 'lower_case_table_names'; - default 0 on Unix and 1 on Windows
0 - table names are stored as specified and comparisons are case-sensitive
1 - table names are stored in lowercase on disk and comparisons are not case sensitive
2 - table names are stored as given but compared in lowercase
Back
MySQL LANGUAGE
Data types
Numeric
Fixed-point - The DECIMAL and NUMERIC types store exact numeric data values and are used when it is important to preserve exact precision, for example with monetary data.
DECIMAL(precision,scale)/NUMERIC(precision,scale) e.g DECIMAL(5,2) - 5 digits total, 2 after decimal point, can store values from -999.99 to 999.99
Values for DECIMAL/NUMERIC columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage
for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes and the
leftover digits require a fraction of four bytes.
Floating-point - The FLOAT and DOUBLE types represent approximate numeric data values and are used for scientific and mathematical calculations where fractional precision is required.
FLOAT 4-bytes single-precision
DOUBLE, REAL 8-bytes double-precision
MySQL also supports the optional precision specification, but the precision value in FLOAT(p) is used only to determine storage size. A precision from 0 to 23 results in a 4-byte FLOAT column and a precision from 24 to 53 results in an 8-byte DOUBLE column.
FLOAT(p)
Integer - MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT and as an extension, also supports the types TINYINT, MEDIUMINT and BIGINT, with the below required storage and range.
TINYINT 1 byte 0-255 (unsigned), -128 to 127 (signed)
SMALLINT 2 bytes 0-65535, -32768 to 32767
MEDIUMINT 3 bytes 0-16777215, -8388608 to 8388607
INT/INTEGER 4 bytes 0-4294967298, -2147483648 to 2147483647
BIGINT 8 bytes 0 to 264-1,-263-1 to 263-1
Bit-value - The BIT data type is used to store bit values.
BIT(n) n = 1 to 64 - needs approximately (n+7)/8 bytes
String
The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM and SET.
CHAR fixed-length (0-255)
VARCHAR variable length (0-65535), uses a 1 or 2 byte length prefix before the data
BINARY/VARBINARY binary strings
TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT character strings of length 2^8-1 (255B), 2^16-1 (32KB), 2^24-1 (16MB), 2^32-1 (4GB)
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB binary large object (binary strings)
ENUM a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table
creation time
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET a string object whose values are chosen from a list of permitted values specified when the table is created
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)
Date/Time
The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP and YEAR.
DATE 3 bytes YYYY-MM-DD
DATETIME 5 bytes YYYY-MM-DD HH:MM:SS
TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS (UTC)
TIME 3 bytes HH:MM:SS or HHH:MM:SS(for time intervals)
YEAR 1 byte YYYY
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
If time zone is changed, the retrieved timestamp value is different from the value that was stored.
MySQL permits storing a zero value of '0000-00-00' as a 'dummy date' as in some cases, it is more convenient than using NULL values and uses less data and index space. To disallow this, strict sql mode can be enabled.
MySQL has fractional seconds support for TIME, DATETIME and TIMESTAMP values, with up to microseconds (6 digits) precision. The syntax type_name(fsp) is used to define a column that includes a fractional seconds part, where type_name is TIME, DATETIME or TIMESTAMP and fsp is the precision - e.g. DATETIME(3). The fsp value must be in the range 0 to 6, 0 signifying no fractional part. Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding.
Literals
Bit-value
b'01' (or B'01')
0b01
Boolean
TRUE,true - evaluates to 1
FALSE,false - 0
Hexadecimal
x'01AF' (or X'01AF')
0x01AF
String
'string'
"string"
Special Character Escape Sequences
\0 An ASCII NUL (X'00') character
\' single quote
\" double quote
\b backspace
\n newline (linefeed)
\r carriage return
\t tab
\\ backslash
A character string/hexadecimal/bit-value literal may have an optional character set introducer and COLLATE clause to designate it as a
string that uses a particular character set and collation.
[_charset_name] literal [COLLATE collation_name]
Operators
Arithmetic
%, MOD modulo operator
* multiplication
+ addition
- minus
/ division
DIV integer division, discards any fractional part
- change the sign of the argument
Assignment
:= assign a value
= assign a value (in SET or UPDATE statements)
Bitwise
& bitwise AND
>> right shift
<< left shift
^ bitwise XOR
| bitwise OR
~ bitwise inversion
Comparison
> greater than
>= greater than or equal
< less than
<= less than or equal
= equal
<=> NULL-safe equal to operator
<>, != not equal operator
IN() whether a value is within a set of values
IS, IS NOT test a value against a boolean
IS NULL, IS NOT NULL NULL value test
LIKE simple pattern matching
BETWEEN..AND.. whether a value is within a range of values
REGEXP, RLIKE whether string matches regular expression
SOUNDS LIKE compare sounds
Logical
AND, && logical AND
OR, || logical OR
XOR logical XOR
NOT, ! negates value
Statements
Administrative statements
FLUSH - used to flush internal caches, tables and logs. It needs the RELOAD privilege
FLUSH HOSTS - empties the host cache and the Performance Schema host_cache table that exposes the cache contents and unblocks any blocked
hosts. The host cache needs to be flushed when a legitimate host is blocked from connecting (based on max_connect_errors) or when the host
IP has been changed
FLUSH HOSTS;
FLUSH LOGS - closes and reopens any log file to which the server is writing. A new binary log or relay log file is created with the
sequence number incremented by one. The general log and slow log files are just closed and reopened
FLUSH LOGS;
FLUSH TABLES - closes all open tables, forces all tables in use to be closed and flushes the prepared statement cache
FLUSH TABLES;
FLUSH TABLES t1, t2; - flushes tables t1 and t2
FLUSH TABLES WITH READ LOCK; - closes all open tables and locks all tables in all databases with a global read lock
KILL - kills a connection thread or the statement being executed by the connection. CONNECTION_ADMIN or SUPER privilege is required to kill
threads/statements owned by others
KILL processlist_id; - terminates the connection associated with the given processlist_id
KILL CONNECTION processlist_id; - same as KILL
KILL QUERY processlist_id; - terminates the statement that the connection is executing, leaving the connection intact
processlist_id can be determined from the ID column of the INFORMATION_SCHEMA.PROCESSLIST table, the Id column of SHOW PROCESSLIST output and the PROCESSLIST_ID column of the performance_schema.threads table
OPTIMIZE TABLE - reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O
efficiency when accessing the table
OPTIMIZE TABLE t1;
SET - assigns values to variables
SET @var1 = expr1; - user-defined variable assignment
SET @var1 = (SELECT SUM(col1) FROM t1);
SET var1 = expr2; - stored program variable assignment
SET GLOBAL sysvar = value; - system variable assignment
SET @@GLOBAL.sysvar = value;
SHOW - provides information about database objects and server status
SHOW ERRORS; - similar to SHOW WARNINGS but only displays errors
SHOW WARNINGS; - displays the errors, warnings and notes resulting from execution of the most recent non-diagnostic
statement in the current session
Control flow statements
IF
IF n > m THEN SET s = '>';
ELSEIF n = m THEN SET s = '=';
ELSE SET s = '<';
END IF;
LOOP - implements a simple loop construct, enabling repeated execution of the statement list until the loop is terminated by a LEAVE
statement. Within a stored function, RETURN can also be used, which exits the function entirely. Neglecting to include a loop-termination
statement results in an infinite loop.
label1: LOOP
statements;
ITERATE label1;
LEAVE label1;
END LOOP label1;
REPEAT - the statement list within a REPEAT statement is repeated until the search_condition expression is true. Thus, a REPEAT always
enters the loop at least once.
label1: REPEAT
statements;
UNTIL search_condition;
END REPEAT label1;
WHILE
WHILE condition DO
statements;
END WHILE;
DDL statements
ALTER TABLE - To use ALTER TABLE, ALTER, CREATE and INSERT privileges for the table are needed
CREATE TABLE - requires the CREATE privilege
RENAME TABLE - Renaming a table requires ALTER and DROP on the old table and ALTER, CREATE and INSERT on the new table
TRUNCATE TABLE - empties a table completely. To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data and logically is similar to a sequence of DROP TABLE and CREATE TABLE statements. It does not cause ON DELETE triggers to fire, it cannot be performed for tables with foreign keys pointing to them and it cannot be rolled back like a DML operation. It requires the DROP privilege.
TRUNCATE TABLE t1;
DML statements
DELETE
DELETE t1 FROM tbl1 t1, tbl2 t2 where t1.c1 = t2.c2;
DELETE t1 FROM tbl1 t1 LEFT JOIN (SELECT * FROM tbl1 LIMIT 10) t2 ON t1.id = t2.id
WHERE t1.id IS NULL; - delete all but top 10 rows
INSERT
INSERT INTO t1 (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); - insert multiple rows
INSERT IGNORE INTO t1 SELECT * FROM t2; - ignore errors
REPLACE - works exactly like INSERT except that if a value exists for a PRIMARY KEY or a UNIQUE index, the old row is deleted and the
new row is inserted. To use REPLACE, both the INSERT and DELETE privileges are needed for the table.
SELECT
SELECT * FROM t1 LIMIT 1,10; - limited rows (offset, rowcount)
SELECT * FROM t1 LIMIT 10 OFFSET 1; - limited rows
SELECT col1 INTO @var1 FROM t1 WHERE col2 = 'x'; - store column value in a variable
SELECT SUM(c1='a') a_cnt, SUM(c1='b') b_cnt FROM t1; - counts
SELECT * from t1 order by CAST(percentage AS DECIMAL(5,2)) DESC; - order by with cast
SELECT * from t1 order by (c1 = 'Yes' AND c2 = 1) DESC; - order by specific column values
Subqueries
SELECT IF (EXISTS (SELECT * FROM t1 WHERE c1 = 1),1, 0); - if exists
SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 AND t2.c2 = 0
AND NOT EXISTS (SELECT * FROM t1 WHERE c1 = t2.c1 AND c2 = 1); - not exists
SELECT t1.* from (SELECT t2.col1, t3.col2 FROM t2, t3 where t2.c1 = t3.c2) t1; - derived table
SELECT t1.c1 from t1 where t1.c2 = (SELECT max(c2) FROM t2 where c1 = t1.c1); - dependent subquery
SELECT * from t1, (SELECT max(c1) FROM t1 group by c2) t2 where t1.c1 = t2.c1; - find max value of c1 for each c2 value
UPDATE
update t1, t2 set t1.c1 = t2.c1 where t1.c2 = t2.c2;
Prepared statements (Dynamic SQL)
MySQL supports server-side prepared statements using the efficient client/server binary protocol through client programming interfaces. An alternative SQL interface is also available based on the below statements.
PREPARE - prepares a statement for execution
EXECUTE - executes a prepared statement
DEALLOCATE PREPARE - releases a prepared statement
SET @colname = CONCAT('day',DAY(CURRENT_DATE));
SET @query = CONCAT('SELECT count(*) FROM t1 where ', @colname,' = 0');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
A prepared statement is specific to the session where it was created and if the session is terminated without deallocating it, the server deallocates it automatically.
The variable max_prepared_stmt_count can be used to guard against too many prepared statements being created simultaneously. A value of 0 prevents the use of prepared statements.
show global variables like 'max_prepared_stmt_count'; - default 16382
Functions
Cast
BINARY - converts an expression to a binary string
SELECT BINARY 'a' = 'A'; - returns 0
CAST(expr as type)/CONVERT(expr,type) - cast an expression to the specified type
CONVERT('129291',UNSIGNED INTEGER) - convert str to int
CONVERT(129291,CHAR(10)) - convert int to str
CONVERT(expr USING transcoding_name) - converts data between different character sets
SELECT CONVERT('abc' USING utf8);
Comparison
COALESCE(list) - returns the first non-NULL value in the list, or NULL if there are no non-NULL values
GREATEST (val1, val2..) - returns the largest argument
ISNULL(expr) - returns 1 or 0
LEAST (val1, val2...) - returns the smallest argument
Control flow
IF(expr1,expr2,expr3) - returns expr2 if expr1 is true, else expr3
CASE - has two syntaxes
CASE var1 WHEN val1 THEN out1 WHEN val2 THEN out2 ELSE out3 END
CASE WHEN cond1 THEN out1 WHEN cond2 THEN out2 ELSE out3 END
SELECT * from t1 LEFT JOIN t2 ON CASE WHEN t1.c1=1 THEN t2.id= t3.id
WHEN t1.c1=2 THEN t2.id= t4.id END - join on case
IFNULL (expr1,expr2) - returns expr1 if it is not null, else expr2
NULLIF (expr1,expr2) - returns null if expr1 = expr2 is true, else returns expr1
SELECT NULLIF (1,1); - returns 1
Date
CURDATE() - returns the current date
CURRENT_TIMESTAMP() - returns the current date and time
DATE(datetime) - extracts the date part of a datetime expression
DATE_ADD/DATE_SUB - perform date arithmetic
DATE_ADD(NOW(),INTERVAL 2 DAY)
DATE_FORMAT(date,format) - formats a date value as per the format string
DATE_FORMAT(NOW(),'%Y-%m-01') - gets first day of current month
DATE_FORMAT(NOW(),'%Y-%m-31') - INTERVAL 1 MONTH - get last day of prior month
The specifiers that can be used in the format string are
%d day of the month
%h/%H hour(0-12)/hour(0-24)
%i minutes
%m month
%M month name
%p AM or PM
%s seconds
%y/%Y 2-digit/4-digit year
LAST_DAY(date) - returns the last day of the month or NULL if the argument is invalid
SELECT count(*) from t1 WHERE date1 BETWEEN DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00')
AND DATE_FORMAT(LAST_DAY(CURDATE()),'%Y-%m-%d 23:59:59');
MONTH(date) - returns the month
MONTHNAME(date) - returns the name of the month
NOW() - returns the current date and time
SEC_TO_TIME(secs) - converts seconds to HH:MM:SS time format
SEC_TO_TIME(2378) -- '00:39:38'
STR_TO_DATE(str,format) - takes in a string and returns a DATETIME, DATE or TIME value as per the format string
STR_TO_DATE(9, '%m') - '0000-09-00'
STR_TO_DATE ('15.04.2011','%d.%m.%Y') - '2011-04-15'
SYSDATE() - returns the time at which it executes
SELECT SYSDATE(6); - returns time with a fractional seconds precision of 6
TIME() - extracts the time part of a datetime expression
TIME_TO_SEC(time) - converts time to seconds
TIMESTAMPDIFF() - returns difference between two dates
SELECT TIMESTAMPDIFF(SECOND,'2016-01-01','2016-05-01 12:05:55');
SELECT CONCAT(TRUNCATE(time_ms/(1000*60*60*24),0), 'd ', CAST((time_ms/(1000*60*60)%24) AS UNSIGNED INTEGER), 'h ',
CAST((time_ms/(1000*60)%60) AS UNSIGNED INTEGER), 'm '); - convert millisecs to day/hour/mins
WEEKDAY(date) - returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday)
YEAR(date) - returns the year
Information
CHARSET(expr) - returns the character set of the argument
COLLATION(str) - returns the collation of the string argument
LAST_INSERT_ID() - value of the AUTOINCREMENT column for the last INSERT
SELECT CONNECTION_ID(); - connection ID (thread ID) for the connection
SELECT CURRENT_USER(); - authenticated user name and host name
SELECT DATABASE(); - current database name
SELECT VERSION(); - MySQL server version
SELECT USER(); - user name and host name provided by the client
Mathematical
ABS(x) - returns the absolute (positive) value of x
CEIL(x) - returns the smallest integer not less than x
FLOOR(x) - returns the largest integer not greater than x
RAND() - returns a random floating-point value between 0 and 1.0
SELECT FLOOR(i + (RAND() * j-i)); - returns a random number between i and j
ROUND(x,d) - rounds x to d decimal places
TRUNCATE(x,d) - truncates to d decimal places. If d is 0, the result has no decimal point. d = negative causes d digits left of the
decimal point of x to become zero
String
CHAR_LENGTH(str)/CHARACTER_LENGTH(str) - returns the length of a string, measured in code points. A multi-byte character counts as a single code point. For a string containing two 3-byte characters, CHAR_LENGTH() returns 2 whereas LENGTH() returns 6
CONCAT - concatenate strings
SELECT CONCAT('drop table ', table_name,';') FROM information_schema.tables where table_name like 'tmp%' AND table_schema = 'db1';
CONCAT_WS(separator,str1,str2..) - concatenate strings with a separator added between
ENCRYPT(str) - encrypts using the Unix crypt() system call and returns a binary string
FIELD(str,s1,s2,s3,...) - returns the index (position) of str in the s1, s2, s3 list, 0 if str is not found
FORMAT(x,d [,locale]) - Formats the number x to a format like 'n,nnn,nnn.nn', rounded to d decimal places, and returns the result as a string. If d is 0, the result has no fractional portion. Locale by default is en_US.
SELECT FORMAT(1712332.123456, 4); -> '1,712,332.1235'
GROUP_CONCAT - returns a string result with the concatenated non-NULL values from a group. The result is truncated to the length given
by the group_concat_max_len system variable (default = 1024)
SELECT player, GROUP_CONCAT(DISTINCT score ORDER BY score DESC SEPARATOR ' ') FROM scores GROUP BY player;
SHOW GLOBAL VARIABLES LIKE 'group_concat_max_len';
SET SESSION group_concat_max_len = 50000;
HEX(str) - returns a hexadecimal string representation of str
INSTR(str,substr) - returns the position of the first occurrence of substring substr in string str, same as the two-argument form of LOCATE(), except that the order of the arguments is reversed
LENGTH(str) - returns no. of bytes in string
LOAD_FILE(file) - reads the file and returns contents as a string
UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1;
LOCATE(substr, str [,pos]) - returns the position of the first occurrence of substring substr in string str starting at position pos. Returns 0 if substr is not in str
LPAD/RPAD(str,len,padstr) - returns the string str, left/right-padded with the string padstr to a length of len characters
LPAD('3',2,'00') -- returns '03'
SOUNDEX(str) - returns a soundex string
SELECT * from emp where soundex(first_name) = soundex('Jon');
SPACE(n) - returns a string of n spaces
SUBSTR(str,pos,len) - returns a portion of a string. A -ve value for pos implies pos characters from the end of the string
SUBSTRING_INDEX(str,delim,count) - returns the substring from string str before count occurrences of the delimiter delim.
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql'
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com'
TO_BASE64(str) - converts a string to base-64 encoded form
UPPER(str)/LOWER(str) - changes all characters to upper/lower case according to the current character set mapping (default utf8mb4)
Regular Expressions
SELECT c1 from t1 where c1 REGEXP '[0-9]'; - find numbers
SELECT c1 FROM t1 WHERE c1 REGEXP '[^0-9]'; - without numbers
SELECT c1 from t1 where c1 REGEXP '^[0-9]'; - find numbers in beginning
SELECT c1 from t1 where c1 REGEXP '[0-9]$'; - find numbers at end
SELECT c1 FROM t1 WHERE c1 REGEXP '[A-Za-z]'; - find characters
SELECT c1 FROM t1 WHERE c1 REGEXP '[A-Za-z]{2,}'; - find characters in sequence
SELECT c1 from t1 where c1 NOT REGEXP '[0-9!%#@*&^?$~,.`[.hyphen.][.right-square-bracket.]
[.slash.][.semicolon.][.less-than-sign.][.greater-than-sign.][.equals-sign.][.left-square-bracket.]]'; - validate name
Others
SLEEP(s) - pauses for s seconds
SELECT SLEEP(5); - returns 0
DO SLEEP(5); - no output
UUID() - returns a UUID
Comments
MySQL server supports three comment styles:
From a # character to the end of the line
From a -- sequence to the end of the line
From a /* sequence to the following */ sequence, as in the C language
MySQL supports some variants of C-style comments. These enable writing code that includes MySQL extensions, but is still
portable.
/*! MySQL-specific code */
If a version number is added after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or
equal to the specified version number
CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */; - executed only by version 5.1.10 or higher
User-defined variables
A value can be stored in a user-defined variable in one statement and referred to in another statement, enabling passing of values from
one statement to another. User-defined variables are session specific and can only be seen and used by the client defining them, but a user with access to the performance_schema.user_variables_by_thread table can see all user variables for all sessions. All variables for a client session are automatically freed when the client exits.
User variables are written as @var_name, where var_name consists of alphanumeric characters, ., _ and $. A user variable name can
contain other characters if quoted as a string or identifier (e.g @'my-var', @"my-var", or @`my-var`). User variable names are not case-sensitive and have a maximum length of 64 characters.
A user-defined variable can be set by issuing a SET statement. Either = or := can be used as the assignment operator.
SET @a = 1; SET @b := 2; SELECT @c := 3; - assigning values
SET @curRank = 0; SELECT name, age, @curRank := @curRank + 1 AS rank FROM t1 ORDER BY age; - ranking rows
SELECT id, group_id, if(@grp <> group_id, @rank := @rank + 1, @rank) as rank, @grp := group_id
FROM t1, (SELECT @rank := 0, @grp := 0) r order by group_id; - rank by group
User variables can be employed to remember results without having to store them in temporary variables in the client. For e.g, to find the items with the highest and lowest price
SELECT @min_price:=MIN(price), @max_price:=MAX(price) FROM items;
SELECT * FROM items WHERE price=@min_price OR price=@max_price;
Back
SERVER ADMINISTRATION
Installing/upgrading MySQL
MySQL editions
MySQL Community Edition is the freely downloadable version. Commercial editions available are: Standard, Enterprise and Cluster Carrier
Grade(CGE).
The Enterprise edition includes features like Enterprise backup, Transparent Data Encryption(TDE), Enterprise monitor and Oracle MySQL
cloud service. MySQL Cluster is the distributed database combining linear scalability and high availability providing in-memory real-time
access with transactional consistency across partitioned and distributed datasets.
Installing MySQL
MySQL can be installed using a set of binary distributions in the form of .tar.gz files or from the source code. Building MySQL from the
source code enables customizing of build parameters, compiler optimizations and installation location.
After downloading a MySQL package, it must be ensured that its MD5 checksum matches the one provided on the MySQL download pages. Each package has an individual checksum that can be verified against the package that was downloaded. Each OS offers its own version of tools for checking the MD5 checksum.
-- Linux
$> md5sum mysql-standard-8.0.28-linux-i686.tar.gz
aaab65abbec64d5e907dcd41b8699945 mysql-standard-8.0.28-linux-i686.tar.gz
-- Windows
$> md5.exe mysql-installer-community-8.0.28.msi
aaab65abbec64d5e907dcd41b8699945 mysql-installer-community-8.0.28.msi
Upgrading MySQL
In-place upgrade - involves shutting down the server, replacing the old MySQL binaries or packages with the new ones and restarting MySQL
on the existing data directory and running mysql_upgrade to update data structures.
Before the upgrade, MySQL should be configured to perform a slow shutdown
SET GLOBAL innodb_fast_shutdown=0;
With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that data files are fully
prepared in case of file format differences between releases.
Logical upgrade - involves exporting existing data from the old version using mysqldump, installing the new MySQL version and loading the
dump file into the new version and running mysql_upgrade.
MySQL source files
btr0cur.cc - The index tree cursor. All changes that row operations make to a B-tree go through this module. Undo log records are written
for every modify or insert of a clustered index record
btr0sea.cc - The index tree adaptive search
buf0buf.cc - The database buffer pool
buf0flu.cc - The buffer pool dirty page write and flush algorithm
buf0lru.cc - The buffer pool replacement algorithm
dict0dict.cc - Data dictionary system
fil0fil.cc - The tablespace memory cache
fsp0fsp.cc - File space management
log0log.cc - Redo log
mtr0log.cc - Mini-transaction log routines
mtr0mtr.cc - Mini-transaction buffer
row0ins.cc - Insert a row in a table
srv0srv.cc - The database server main program
MySQL startup/shutdown
When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients.
-- Linux
sudo /etc/init.d/mysqld start
sudo /etc/init.d/mysqld stop
sudo /etc/init.d/mysqld restart
-- Windows
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" - start server
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlshow" -u root -p mysql - shows tables in mysql db
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" -u root -p version status proc - shows server info
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" -u root -p shutdown - stop
If innodb_fast_shutdown is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. The slow shutdown can take minutes or even hours when substantial amounts of data are still buffered. The slow shutdown technique should be used before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
If the value is 1 (the default), InnoDB skips those operations at shutdown, a process known as a fast shutdown.
If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed. No committed transactions are lost but the crash recovery operation makes the next startup take longer. It should be used in emergency or troubleshooting situations for the fastest shutdown if data is at risk of corruption.
SHOW GLOBAL VARIABLES like 'innodb_fast_shutdown'; - the InnoDB shutdown mode
The MySQL server shutdown process takes place as follows
The shutdown process is initiated by a SHUTDOWN command or a signal from the OS
The server creates a shutdown thread, if shutdown was requested by a client. If shutdown is the result of receiving a SIGTERM signal,
the signal thread might handle shutdown itself or it might create a separate thread to do so
The server stops accepting new connections by closing the handlers for the network interfaces to which it listens for connections -
the TCP/IP port, the Unix socket file, the Windows named pipe and shared memory on Windows
The server terminates current activity. For each thread associated with a client connection, the server breaks the connection and
marks the thread as killed. For threads that have an open transaction, the transaction is rolled back
If the server is a replication master, it treats threads associated with currently connected slaves like other client threads and each one
is marked as killed and exits when it next checks its state. If the server is a slave, it stops the I/O and SQL threads, if they are
active, before marking client threads as killed. The SQL thread is permitted to finish its current statement (to avoid causing replication
problems) and then stopped. If the SQL thread is in the middle of a transaction, the server waits until the current replication event
group has finished executing, or until the user issues a KILL QUERY or KILL CONNECTION statement
The server shuts down or closes storage engines, flushes the table cache and closes all open tables. InnoDB flushes its buffer pool to
disk (unless innodb_fast_shutdown is 2), writes the current LSN to the tablespace and terminates its own internal threads
The server exits returning one of the following exit codes
0 = successful termination (no restart done)
1 = unsuccessful termination (no restart done)
2 = unsuccessful termination (restart done)
Running multiple instances on a machine
The primary resource managed by a MySQL instance is the data directory. So each instance should use a different data directory specified
using the --datadir option.
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data - inst1
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data-slave - inst2
Each instance should have a distinct port number for TCP/IP connections. Alternatively, if the host has multiple network addresses,
--bind-address can be used to cause each server to listen to a different address.
port=3306 - inst1
port=3307 - inst2
The instances can be started using their own option files and shut down separately.
-- inst1
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"
"C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqladmin" -u root -p --port=3306 shutdown
-- inst2
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my-slave.ini"
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" -u root -p --port=3307 shutdown
Server configuration
Server variables
The MySQL server maintains system variables that configure its operation. The default values of the variables can be changed at
server startup using command-line options or configuration files or dynamically changed at runtime by the SET statement globally (effective till the next restart of the server) or for the current session. Setting the global value of a system variable requires the SYSTEM_VARIABLES_ADMIN/SUPER privilege. Setting the session system variable requires no special privileges and can be done by any user.
SHOW GLOBAL VARIABLES; - list all global system variables
SHOW GLOBAL VARIABLES LIKE '%version%'; - mysql version info
SHOW GLOBAL VARIABLES LIKE 'server%';
server_id - used in replication to uniquely identify master and slave servers
server_uuid - true uuid of server
SHOW GLOBAL VARIABLES like '%time_zone';
system_time_zone - server system time zone
time_zone - used to initialize the time zone for each client that connects, default=SYSTEM (system_time_zone)
Certain global system variables can also be persisted to the mysqld-auto.cnf file in the data directory so that they apply across server restarts.
SET PERSIST max_connections=1000;
SET PERSIST_ONLY table_open_cache=8192; - persist a variable without setting the runtime value
RESET PERSIST max_connections; - remove a persisted variable from mysqld-auto.cnf
RESET PERSIST; - remove all persisted variables
The performance_schema persisted_variables table provides an SQL interface to the mysqld-auto.cnf file.
SELECT * FROM performance_schema.persisted_variables;
The MySQL server also maintains status variables that provide information about its operation. The global variables aggregate the values for all connections and the session variables show the values for the current connection.
SHOW GLOBAL STATUS; - list all global status variables
SHOW GLOBAL STATUS like 'uptime'; - number of seconds that the server has been up
Server SQL modes
The MySQL server can operate in different SQL modes and can apply these modes differently for different clients, depending on the value
of the sql_mode system variable.
SHOW GLOBAL VARIABLES like 'sql_mode';
ONLY_FULL_GROUP_BY - reject queries that refer to non-aggregated columns that are not in the GROUP BY clause
NO_ENGINE_SUBSTITUTION - control automatic substitution of the default storage engine when CREATE/ALTER TABLE statement specifies an
unavailable storage engine
NO_ZERO_DATE - does not permit '0000-00-00' as a valid date
STRICT_ALL_TABLES - enable strict SQL mode for all storage engines
STRICT_TRANS_TABLES - enable strict SQL mode for transactional storage engines and when possible for non-transactional engines
Strict SQL mode
Strict mode controls how MySQL handles invalid or missing values in statements such as INSERT or UPDATE. Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings. Strict SQL mode causes an error on division by zero and does not permit '0000-00-00' as a valid date.
Character set and collation
Every server, database, table and character column has a character set and a collation. If they are not specified at table create time, the
default character set and collation are used.
SHOW CHARACTER SET; - available character sets and their default collation
SHOW COLLATION WHERE charset = 'latin1';
The server character set and collation are used as default values if the database character set and collation are not specified in CREATE
DATABASE statements and have no other purpose. The CREATE/ALTER DATABASE statements have optional clauses for specifying the database
character set and collation.
CREATE DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
The character_set_database and collation_database system variables show character set and collation for the default database. The server
sets these variables whenever the default database changes. If there is no default database, the variables have the same value as
character_set_server and collation_server.
show global variables like 'character_set%';
character_set_server - default server character set
character_set_database - character set used by the default database
character_sets_dir - directory where character sets are installed
show global variables like 'collation_%';
collation_server - default server collation
collation_database - collation used by the default database
For CREATE TABLE statements, the database character set and collation are used as default values for table definitions if the table
character set and collation are not specified.
CREATE TABLE t1(col_list) CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; - change table character set and collation
ALTER TABLE t1 MODIFY COLUMN c1 varchar(255) CHARSET utf8 COLLATE utf8_unicode_ci; - change column character set and collation
SELECT t1.* from t1, t2 WHERE t1.col1 COLLATE utf8_unicode_ci = t2.col2; - override collation in query
Opening and closing tables
MySQL is multi-threaded, so many clients issue queries for a given table simultaneously. To minimize the problem with multiple client
sessions having different states on the same table, the table is opened independently by each concurrent session, which uses additional
memory but normally increases performance.
The cache of open tables is kept at a level of table_open_cache entries. The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If one or both of these values are increased, it may run up against a limit imposed by the OS on the per-process number of open file descriptors. Many OSs permit the open-files limit to be increased.
table_open_cache is related to max_connections. For e.g, for 200 concurrent running connections, a table cache size of at least 200 * N
can be specified, where N is the maximum number of tables per join in any of the queries executed. Some extra file descriptors must also
be reserved for temporary tables and files. The effective value of table_open_cache is the greater of (the effective value of open_files_limit - 10 - the effective value of max_connections / 2) and 400.
It must be ensured that the OS can handle the number of open file descriptors implied by the table_open_cache setting. If table_open_cache
is set too high, MySQL may run out of file descriptors and exhibit symptoms such as refusing connections or failing to perform queries. If the opened_tables status variable is high or if queries are seen waiting in 'opening tables' state, then it may be prudent to increase table_open_cache.
The table_definition_cache variable specifies the number of table definitions that can be stored in the definition cache. If a large number of tables are used, a large table definition cache can be created to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum value is 400. The default value is based on the formula MIN(400 + table_open_cache / 2, 2000), capped to a limit of 2000.
SHOW GLOBAL VARIABLES LIKE '%table%';
table_open_cache - open tables for all threads
table_open_cache_instances - the number of open tables cache instances
table_definition_cache - table definitions that can be stored in the definition cache (default -1, signifies autosizing)
SHOW GLOBAL STATUS like '%table%';
open_tables - tables that are open
opened_tables - tables that have been opened since server startup
open_table_definitions - open table definitions
opened_table_definitions - table definitions opened since server startup
The open_files_limit variable specifies the number of file descriptors available to mysqld from the OS (LimitNOFILE in Linux). The runtime
value of this variable is the real value permitted by the system and might differ from the value specified at startup. The effective
open_files_limit value is based on the value specified at startup and the values of max_connections and table_open_cache.
SHOW GLOBAL VARIABLES like 'open_files_limit'; - the number of files that the OS permits mysqld to open
If the file descriptors allocated to MySQL are not enough, 'Too many open files' or 'File table overflow' errors may result.
The innodb_open_files variable specifies the maximum number of .ibd files that MySQL can keep open at one time. The default value is the
higher of 300 and table_open_cache. The file descriptors used for .ibd files (innodb tables) are independent of those specified by
open_files_limit and do not affect the operation of the table cache.
SHOW GLOBAL VARIABLES like 'innodb_open_files';
Connection management
The MySQL server listens on one or more network sockets for TCP/IP connections. Each socket is bound to one address, but it is possible for an address to map onto multiple network interfaces. The bind_address variable needs to be set at server startup to specify how the server should listen for TCP/IP connections.
SHOW GLOBAL VARIABLES like 'bind_address';
If bind_address is specified, it accepts a list of one or more address values, each of which may specify a single non-wildcard IP address or host name. Each address may include a network namespace specifier. If only one address is specified, it may make use of one of the wildcard address formats that permit listening on multiple network interfaces (*, 0.0.0.0 or ::). When multiple addresses separated by commas are listed, each value must specify a single non-wildcard IP address (either IPv4 or IPv6) or a host name, and wildcard address formats (*, 0.0.0.0 or ::) are not allowed.
IP addresses can be specified as IPv4 or IPv6 addresses. For any value that is a host name, the server resolves the name to an IP address and binds to that address. If a host name resolves to multiple IP addresses, the server uses the first IPv4 address if there are any, or the first IPv6 address otherwise.
The server treats different types of addresses as follows:
If the address is *, the server accepts TCP/IP connections on all server host IPv4 interfaces, and if the server host supports IPv6, on all IPv6 interfaces. It is the default and permits both IPv4 and IPv6 connections on all server interfaces
If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces
If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces
If the address is an IPv4-mapped address, the server accepts TCP/IP connections for that address, in either IPv4 or IPv6 format. For e.g if the server is bound to ::ffff:127.0.0.1, clients can connect using --host=127.0.0.1 or --host=::ffff:127.0.0.1
If the address is a regular IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address
Connection manager threads
Connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request
processing for that connection. Manager threads consult the thread cache to see whether it contains a thread that can be used for the
connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.
SHOW GLOBAL VARIABLES LIKE 'thread_cache_size'; - how many threads the server should cache for reuse
The default value of thread_cache_size is calculated by (max_connections / 100) + 8, capped to a maximum of 100.
If the server sees hundreds of connections per second, thread_cache_size should be set high enough so that most new connections use cached
threads. The efficiency of the thread cache can be seen by examining the difference between the connections and threads_created status
variables.
SHOW GLOBAL STATUS LIKE 'threads_%';
threads_cached - threads in cache
threads_created - threads created apart from cache, if high thread_cache_size can be increased
threads_connected - current connections
threads_running - threads not sleeping
Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are
dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.
SHOW GLOBAL VARIABLES LIKE 'net_buffer_length'; - default 16KB, maximum 1 MB
SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'; - maximum size of the communication buffer between server and client
The max_connections system variable controls the maximum number of clients the server permits to connect simultaneously. If the server refuses a connection because the limit is reached, it increments the Connection_errors_max_connections status variable.
SHOW GLOBAL VARIABLES like '%connections%';
max_connections - maximum simultaneous client connections
max_user_connections - maximum simultaneous connections permitted to a user account (default = 0, no limit)
SHOW GLOBAL STATUS like '%connection%';
connections - number of connection attempts (successful or not)
max_used_connections - maximum connections that have been in use simultaneously since the server started
connection_errors_max_connections - number of connections refused because max_connections was reached
mysqld actually permits max_connections + 1 client connections, the extra connection being reserved for use by accounts that have the
CONNECTION_ADMIN or SUPER privilege to connect and diagnose problems.
Increasing the max_connections value increases the number of file descriptors that mysqld requires and if the required descriptors are not
available, the server reduces the value of max_connections. Increasing the open_files_limit system variable may be necessary, which may
also require raising the OS limit on how many file descriptors can be used by MySQL.
SHOW GLOBAL VARIABLES like '%timeout%';
connect_timeout - number of seconds that mysqld waits for a connect packet before responding with Bad handshake (default 10)
net_read_timeout - number of seconds to wait for more data from a connection before aborting the read (default 30)
net_write_timeout - number of seconds to wait for a block to be written to a connection before aborting the write (default 60)
Host cache
The MySQL server maintains an in-memory host cache that contains information about clients' IP address, host name and error information. The performance_schema.host_cache table exposes the contents of the host cache. The host cache is enabled by default and its size is controlled by the host_cache_size variable.
The server uses the host cache only for non-localhost TCP connections. It is not used for TCP connections established using a loopback interface address (e.g 127.0.0.1 or ::1), or for connections established using a Unix socket file, named pipe or shared memory.
By caching the results of IP-to-host name lookups, the server avoids doing a DNS lookup for each client connection and only needs to perform a lookup only for the first connection from that host. The cache also contains information about errors that occur during the client connection process and if too many of these occur successively from a given host without a successful connection, the server blocks further connections from that host. The max_connect_errors variable determines the permitted number of successive errors before blocking occurs. Flushing the host cache unblocks any blocked hosts.
SHOW GLOBAL VARIABLES like 'host_cache_size';
SHOW GLOBAL VARIABLES like 'max_connect_errors';
Closing idle connections
On thread startup, the session wait_timeout value is initialized from the global wait_timeout or interactive_timeout value, depending on the type of client as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect().
SHOW GLOBAL VARIABLES like '%timeout%';
interactive_timeout - number of seconds the server waits for activity on an interactive connection before closing it (default 28800)
wait_timeout - number of seconds waited for activity on a non-interactive connection before closing it (default 28800)
Connection character sets and collations
Every client has session-specific connection-related character set and collation system variables which are initialized at connect time,
but can be changed within the session.
show global variables like 'char%';
character_set_client - character set for statements sent from the client
character_set_connection - character set used for literals specified without an introducer and for number-to-string conversion
character_set_results - character set in which the server sends query results to the client
The server converts statements (without introducers) sent by the client from character_set_client to character_set_connection.
When a client connects to the server, it indicates which character set it wants to use for communication with the server. Actually, the
client indicates the default collation for that character set from which the server can determine the character set and set the
character_set_client, character_set_results, character_set_connection and collation_connection variables for the session. In effect, the
server performs the equivalent of a SET NAMES operation.
If the server does not support the requested character set or collation, it falls back to using the server character set and collation
to configure the connection.
Each client supports a --default-character-set option, which enables users to specify the character set explicitly to override whatever
default the client otherwise determines.
[mysql]
default-character-set=utf8
collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection
does not matter because columns have their own collation, which has a higher precedence.
show global variables like 'collation%';
collation_connection - collation of the connection character set
MySQL server plugins
MySQL supports a plugin API that enables creation of server components. Plugins can be loaded at server startup or loaded and unloaded at
runtime without restarting the server. The components supported by this interface include storage engines, INFORMATION_SCHEMA tables,
full-text parser plugins, server extensions stc.
Installing plugins
Before a server plugin can be used, it must be installed using one of the following methods
Built-in plugins - a built-in plugin is known by the server automatically and the server enables the plugin at startup
Plugins registered in the mysql.plugin system table - The mysql.plugin table serves as a registry of plugins (other than built-in
plugins, which need not be registered). At startup, the server loads each plugin listed in the table.
select * from mysql.plugin;
Plugins named with command-line options - a plugin located in a plugin library file can be loaded at server startup with the
--plugin-load, --plugin-load-add or --early-plugin-load option
Plugins installed with the INSTALL PLUGIN statement - a plugin located in a plugin library file can be loaded at runtime with the
INSTALL PLUGIN statement. The statement also registers the plugin in the mysql.plugin table to cause the server to load it on subsequent
restarts. It requires the INSERT privilege for the mysql.plugin system table.
INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
Shared_library_name is the name of the shared library that contains the plugin code and it must be located in the plugin directory (the
directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory.
Loading plugins
Server plugins must be loaded into the server before they can be used. MySQL supports plugin loading at server startup and runtime,
controlling the activation state of loaded plugins and unloading them at runtime.
When a plugin is loaded, information about it is available from the INFORMATION_SCHEMA.PLUGINS table and the SHOW PLUGINS statement.
To be usable by the server, the plugin library file must be located in the MySQL plugin directory. The --early-plugin-load option
is used to load the plugin by naming the library file that contains it.
SHOW PLUGINS; - shows installed plugins
SELECT * from information_schema.plugins;
[mysqld]
early-plugin-load=keyring_file.so
Uninstalling plugins
The UNINSTALL PLUGIN statement disables and uninstalls a plugin known to the server at runtime. It unloads the plugin and removes it from
the mysql.plugin table, if it is registered there, and the server does not load the plugin automatically for subsequent restarts. The
UNINSTALL PLUGIN statement requires the DELETE privilege for the mysql.plugin table.
Monitoring the server
Information_schema tables
InnoDB INFORMATION_SCHEMA tables provide metadata, status information and statistics about the InnoDB storage engine and can be used to
monitor ongoing InnoDB activity.
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G - provides buffer pool status info
SELECT * FROM information_schema.INNODB_METRICS \G - stores innoDB performance and resource-related counters
The PROCESSLIST table provides information about which threads are running.
select * from information_schema.processlist; - shows running threads
SHOW FULL PROCESSLIST; - equivalent to above
Performance Schema
The performance schema is a feature for monitoring MySQL Server execution at a low level during runtime and is implemented using the
PERFORMANCE_SCHEMA storage engine and the performance_schema database.
The performance schema monitors server events and an event is anything the server does that takes time and has been instrumented so timing
information can be collected. The PERFORMANCE_SCHEMA storage engine collects event data using instrumentation points in server source code
and stores them in in-memory tables in the performance_schema database. The tables are discarded at server shutdown and repopulated at startup.
SHOW GLOBAL variables LIKE 'performance_schema'; - ON(default)
The internal operation and memory requirements of the Performance Schema can be inspected by
SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
Name values consist of two parts, an internal buffer and a buffer attribute.
an internal buffer that is not exposed as a table is named within parentheses. Examples: (pfs_cond_class).row_size, (pfs_mutex_class).memory
an internal buffer that is exposed as a table in the performance_schema database is named after the table, without parentheses. Examples: events_waits_history.row_size, mutex_instances.row_count
a value that applies to the Performance Schema as a whole begins with performance_schema. Example: performance_schema.memory
Buffer attributes have these meanings
row_size is the size of the internal record used by the implementation, such as the size of a row in a table. row_size values cannot be changed
row_count is the number of internal records, such as the number of rows in a table. row_count values can be changed using Performance Schema configuration options
for a table, tbl_name.memory is the product of row_size and row_count. For the Performance Schema as a whole, performance_schema.memory is the sum of all the memory used (the sum of all other memory values)
Performance schema tables
data_locks - shows data locks held and requested
SELECT * FROM performance_schema.data_locks \G
data_lock_waits - shows locks requested that are being blocked by locks held
SELECT * FROM performance_schema.data_lock_waits \G
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id \G
events_statements_current - one row per thread showing the current status of the thread's most recent monitored statement event
Columns:
event_name - the name of the instrument (from the setup_instruments table) from which the event was collected
source - name of the source file containing the instrumented code that produced the event and the line number
timer_start, timer_end, timer_wait - timing of the event in picoseconds (10-12)
sql_text - text of the sql statement
select * from performance_schema.events_statements_current where timer_wait > 1000000000; - timed more than 1 ms
mutex_instances - lists all the mutexes seen by the performance_schema while the server executes
Columns:
name - the instrument name associated with the mutex
object_instance_begin - the address in memory of the instrumented mutex
locked_by_thread_id - when a thread currently has a mutex locked, the thread_id of the locking thread, else NULL
select * from performance_schema.mutex_instances;
metadata_locks - metadata lock information
Columns:
object_type - the type of lock
object_name - the name of the object
owner_thread_id - thread requesting the lock
select * from performance_schema.metadata_locks;
threads - The threads table has a row with information for each existing server thread and the rows are removed when threads end. For a thread associated with a client session, removal occurs when the session ends.
The columns with names having a prefix of processlist_ provide information similar to the information_schema.processlist table or the SHOW PROCESSLIST statement. Access to threads does not require a mutex and has minimal impact on server performance while information_schema.processlist and SHOW PROCESSLIST require a mutex. The threads table includes background threads and also provides additional information for each thread.
Columns:
thread_id - unique thread identifier
name - the name associated with the thread instrumentation code in the server
thread/sql/one_connection - user connection
thread/sql/main - main() function of the server
type - FOREGROUND (user connection threads) or BACKGROUND (internal system threads)
processlist_id - id displayed in the processlist table
processlist_command - For foreground threads, the type of command the thread is executing on behalf of the client or Sleep
(idle session)
Thread command values:
Binlog Dump - thread on a master for sending binary logs to slave
Daemon - internal server thread, not servicing a client connection
Processlist - the thread is producing info about server threads
Query - thread is executing a statement
Sleep - waiting for the client to send a statement
processlist_state - thread state
General thread states:
cleaning up - The thread has processed one command and is preparing to free memory and reset certain state variables
closing tables - The thread is flushing the changed table data to disk and closing the used tables. This should be a fast
operation unless the disk is full or heavily used
converting HEAP to ondisk - The thread is converting an internal temporary table from a MEMORY table to an on-disk table
copy to tmp table - The thread is processing an ALTER TABLE statement
Copying to tmp table - The server is copying to a temporary table in memory
Copying to tmp table on disk - The temporary result set has become too large and the thread is changing the temporary table
from in-memory to disk-based format to save memory
Creating sort index - The thread is processing a SELECT that is resolved using an internal temporary table
freeing items - command executed, usually followed by 'cleaning up'
init - occurs before the initialization of ALTER TABLE/SELECT/INSERT/UPDATE/DELETE statements. Actions taken by the server include
flushing the binary log and the InnoDB log
Killed - A Kill statement has been issued to the thread and it should abort next time it checks the kill flag
Opening tables - The thread is trying to open a table. It should be a very fast operation unless something is preventing it
optimizing - performing initial optimizations for a query
preparing - occurs during query optimization
query end - occurs after processing a query but before the freeing items state
removing tmp table - The thread is removing an internal temporary table after processing a SELECT statement
Rolling back - rolling back a transaction
Searching rows for update - The thread is doing a first phase to find all matching rows before updating them. This has to be done if
the UPDATE is changing the index that is used to find the involved rows
Sending data - The thread is reading and processing rows for a SELECT statement, and sending data to the client
statistics - the server is calculating statistics to develop a query execution plan
System Lock - the thread has called mysql_lock_tables(). It is a general state that can occur for many reasons. For e.g, the thread is
going to request or is waiting for an internal or external system lock for the table. This can occur when InnoDB waits for a
table-level lock during execution of LOCK TABLES
updating reference tables - executing the second part of a multiple-table update and updating the matched rows from the other tables
waiting for handler commit - The thread is waiting for a transaction to commit versus other parts of query processing
Waiting for table flush - The thread is executing FLUSH TABLES and is waiting for all threads to close their tables
Waiting for table level lock - Waiting for a table lock or a database dump is being performed
Waiting for table metadata lock - waiting to obtain a metadata lock
processlist_time - time the thread has been in its current state
processlist_info - statement the thread is executing
parent_thread_id - if the thread is a subthread, the thread_id of the spawning thread
connection_type - protocol used ( TCP/IP - TCP/IP without encryption, SSL/TLS - TCP/IP with encryption, Socket - Unix socket file, Named
Pipe - Windows named pipe, Shared Memory - Windows shared memory)
thread_os_id - the thread or task identifier as defined by the OS
select count(*) from performance_schema.threads; - current threads
select name, count(*) from performance_schema.threads group by name; - threads by name
select processlist_user, count(*) from performance_schema.threads group by processlist_user; - threads by user
select processlist_host, count(*) from performance_schema.threads group by processlist_host; - threads by host
select processlist_command, count(*) from performance_schema.threads group by processlist_command; - threads by command
select * from performance_schema.threads where processlist_command <> 'Sleep' and processlist_time > 1; - long running queries
select concat('kill query ', processlist_id,';') from performance_schema.threads
where processlist_command = 'Query' and processlist_time > 600 and processlist_user = 'reports';
SELECT t1.object_name, t2.table_rows FROM performance_schema.table_io_waits_summary_by_table t1, information_schema.tables t2
WHERE t1.object_schema = t2.table_schema and t1.object_name = t2.table_name and t1.object_type = 'TABLE'
and t1.count_star = 0 AND object_schema = 'db1'; - unused tables
Checking ALTER TABLE statement progress
UPDATE performance_schema.setup_instruments SET ENABLED='YES' WHERE NAME='stage/sql/copy to tmp table';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_stages_%';
SELECT * from performance_schema.events_stages_current;
Sys schema
The sys schema includes a set of objects that helps interpret data collected by the performance schema. sys schema objects can be used for typical tuning and diagnosis use cases. The objects include views, procedures and functions that summarize performance schema data, perform performance schema configuration and generate diagnostic reports. Many of the views have a version with a x$ prefix that provide raw data suitable for use with other tools.
The host_summary_by_file_io view summarizes file I/O and latencies grouped by host.
SELECT * FROM sys.host_summary_by_file_io;
SELECT * FROM sys.x$host_summary_by_file_io;
The innodb_lock_waits view summarizes the locks that transactions are waiting for, sorted by descending lock age.
select * from sys.innodb_lock_waits;
The version view provides the sys schema and mysql versions.
SELECT * FROM sys.version;
InnoDB monitors
InnoDB monitors provide information about the InnoDB internal state, useful for performance tuning. When turned on, InnoDB monitors print data about
every 15 seconds to the error log.
SET GLOBAL innodb_status_output=ON; - enable standard monitor
SET GLOBAL innodb_status_output_locks=ON; - enable locks monitor
SHOW ENGINE INNODB STATUS\G - obtain standard monitor output on demand
The standard InnoDB Monitor displays the following info:
Background thread
Work done by the main background thread
Semaphores
Reservation count - indicates how often InnoDB has allocated slots for semaphores in the OS wait array
Signal count - measures how often threads have been signaled via the array
InnoDB first tries to spin-wait for the lock and if it doesn't succeed after the number of spin rounds specified by innodb_sync_spin_loops,
falls back to the more expensive OS wait array.
Reports threads waiting for a semaphore and how many times threads have needed a spin or a OS wait on a mutex or a rw-lock semaphore. A
large number of threads waiting for semaphores may be a result of disk I/O or contention due to heavy parallelism of queries or problems
in OS thread scheduling. Setting innodb_thread_concurrency to smaller than the default value may help. The Spin rounds per wait line shows
the number of spinlock rounds per OS wait for a mutex.
Latest foreign key error
Latest detected deadlock
Transactions
Lock waits for transactions
Table and record locks held by active transactions
File I/O
Information about threads that InnoDB uses to perform various types of I/O. The first few are dedicated to general InnoDB processing.
The contents also display information for pending I/O operations and statistics for I/O performance. The number of these threads are
controlled by the innodb_read_io_threads and innodb_write_io_threads parameters.
Insert buffer and adaptive hash index
Shows change buffer and adaptive hash index statistics
size - The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page
seg size - The size of the change buffer, in pages
Log
Displays current LSN, how far the log has been flushed to disk and the last checkpoint LSN
Buffer pool and memory - metrics pertaining to the operation of the InnoDB buffer pool
Dictionary memory allocated - total memory allocated to the InnoDB data dictionary (bytes). Innodb allocates approximately 15 KB per table
once a table is accessed and keeps it until the server is shut down
Buffer pool size - total pages allocated to the buffer pool
Free buffers - pages in the buffer pool free list
Database pages - pages in the buffer pool LRU list
Old database pages - pages in the old LRU sublist
Modified db pages - modified pages
Pending reads - number of pages waiting to be read into the buffer pool
Pending writes LRU - number of old dirty pages within the buffer pool to be written from the bottom of the LRU list
Pending writes flush list - number of pages to be flushed during checkpointing
reads/s,creates/s,writes/s - average number of buffer pool pages read/created/written per second
LRU len - pages in the buffer pool LRU list
unzip_LRU len - pages in the unzip_LRU list. Uncompressed tables only use the LRU. If a table is compressed then compressed pages are on
the LRU and the unzip_LRU is used for pages that have an uncompressed and compressed version in the buffer pool
Row operations
The queries inside InnoDB and queries in queue counters are 0, if there is no limit on concurrent threads (innodb_thread_concurrency = 0).
If the SHOW ENGINE INNODB STATUS output is empty, the /tmp directory may have been filled up or be inaccessible, since to print the
SHOW INNODB STATUS, InnoDB writes to a tmpfile that it has opened at the mysqld startup.
The InnoDB Lock Monitor prints additional lock information along with the standard output.
Monitoring memory usage
Most Performance Schema memory instrumentation is disabled by default and instruments can be enabled by updating the ENABLED column of the performance_schema.setup_instruments table. Memory instruments have names in the form of memory/code_area/instrument_name, where code_area is a value such as sql or innodb and instrument_name is the instrument detail.
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%'; - all memory instruments
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%'; - innodb memory instruments
update performance_schema.setup_instruments set enabled='YES' where name like 'memory%';
The below rule can be added to the config file to enable memory instruments.
performance-schema-instrument='memory/%=COUNTED'
Querying memory usage instruments
SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb%';
SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql%';
Aggregating currently allocated memory (current_alloc) by code area
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
Back
TRANSACTION/QUERY MANAGEMENT
Transaction handling
MySQL operates in AUTOCOMMIT mode by default. i.e unless a transaction is explicitly begun, it automatically executes each query in
a separate transaction. SET autocommit disables or enables the default autocommit mode for the current session. If set to 0,
COMMIT/ROLLBACK must be issued explicitly.
SET GLOBAL autocommit = 1; (or 0/ON/OFF)
START TRANSACTION or BEGIN starts a new transaction. COMMIT/ROLLBACK commit or cancel the current transaction's changes. Some statements
cannot be rolled back like data definition language (DDL) statements, such as those that create or drop databases, create, drop or alter
tables or stored routines. Most of these statements also perform an implicit commit and should not be included within transactions.
With START TRANSACTION, autocommit remains disabled until the transaction is ended with COMMIT or ROLLBACK.
The innodb_flush_log_at_trx_commit variable controls the balance between strict ACID compliance for commits and higher performance that
is possible when commits are rearranged and done in batches. Better performance can be achieved by changing the default value, but up to
a second of transactions can be lost in a crash.
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
1 - default and is required for full ACID compliance. The contents of the InnoDB log buffer are written out to the log file at each
transaction commit and the log file is flushed to disk
0 - the contents of the log buffer are written to the log file approximately once per second and the log file is flushed to disk. No
writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not guaranteed 100% every
second due to process scheduling issues. With any mysqld process crash up to a second of transactions can be lost
2 - the contents of the log buffer are written to the log file after each commit and the log file is flushed to disk approximately once
per second. Once-per-second flushing is not 100% guaranteed every second and since the flushing to disk is done once per second, with any
crash up to a second of transactions can be lost
The SAVEPOINT statement sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with
the same name, the old savepoint is deleted and a new one is set. The ROLLBACK TO SAVEPOINT statement rolls back a transaction to
the named savepoint without terminating the transaction. The RELEASE SAVEPOINT statement removes the named savepoint from the set
of savepoints of the current transaction. No commit or rollback occurs.
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
Isolation levels
SET TRANSACTION ISOLATION LEVEL level is used to set the isolation level of the transaction. level can be REPEATABLE READ, READ COMMITTED,
READ UNCOMMITTED or SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.
SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; - transaction isolation-level. Defaults to REPEATABLE-READ.
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; - change isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; insert into t1 SELECT * FROM t2 ; COMMIT ; - select with no lock
InnoDB locks
InnoDB implements standard row-level locking with two types of locks - shared and exclusive. A shared (S) lock permits the transaction that holds the lock to read a row. An exclusive (X) lock permits the transaction to update or delete a row.
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; - seconds that a transaction waits for a row lock before giving up
Intention locks
InnoDB supports multiple granularity locking which permits coexistence of row-level locks and locks on entire tables using intention
locks. Intention locks are table-level locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require
later for a row in that table.
There are two types of intention locks - Intention shared (IS) and Intention exclusive (IX). Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table and before acquiring an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
Intention locks do not block anything except full table requests (for e.g LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row or going to lock a row in the table. Table-level lock type compatibility is shown in the below matrix.
X IX S IS
- -- - --
X x x x x
IX x ok x ok
S x x ok ok
IS x ok ok ok
Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output.
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX
Record locks
A record lock is a lock on an index record and prevents any other transaction from inserting, updating or deleting matching rows. For e.g,
SELECT col1 FROM t1 WHERE col1 = 10 FOR UPDATE prevents inserting, updating or deleting rows where the value of col1 = 10.
Record locks always lock index records and if a table is defined with no indexes, InnoDB creates a hidden clustered index and uses this
index for record locking.
Transaction data for a record lock appears similar to the following in the InnoDB monitor output
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
Gap locks
A gap lock is a lock on a gap between index records or a lock on the gap before the first or after the last index record and prevents
other transactions from inserting a value within a range because the gaps between all existing values in the range are locked. For e.g,
SELECT col1 FROM t1 WHERE col1 BETWEEN 10 and 20 FOR UPDATE prevents inserting a value of 15 into column col1.
A gap might span a single index value, multiple values or even be empty. Gap locks are part of the trade-off between performance and concurrency and are used in some transaction isolation levels and not others. Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.
Gap locks in InnoDB are purely inhibitive, they only stop other transactions from inserting to the gap and do not prevent them from
taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.
Next-Key Locks
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
By default, InnoDB operates in REPEATABLE READ transaction isolation level and uses next-key locks for searches and index scans, which prevents phantom rows.
Transaction data for a next-key lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output.
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
Insert Intention Locks
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, each lock the gap with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other as the rows are non-conflicting.
Transaction data for an insert intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output.
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...
Locking reads
Regular SELECT statements do not take any locks under any isolation level. If data is queried and then related data is updated/inserted
within the same transaction, the regular SELECT statement does not give enough protection as other transactions can update or delete the
same rows queried. InnoDB supports two types of locking reads that offer extra safety.
SELECT...LOCK IN SHARE MODE sets a shared mode lock (IS) on any rows that are read. Other sessions can read the rows, but cannot modify
them until the transaction commits.
SELECT...FOR UPDATE locks the rows (IX) and any associated index entries, the same as issuing an UPDATE statement for those rows. Other
transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain
transaction isolation levels till a COMMIT is issued. Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is
disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching
the specification are not locked.
Deadlocks
When innodb_deadlock_detect is enabled (the default), the lock wait timeout value does not apply to deadlocks and InnoDB detects deadlocks
immediately and rolls back one of the transactions. When innodb_deadlock_detect is disabled, InnoDB relies on innodb_lock_wait_timeout for
rollback when a deadlock occurs.
SHOW GLOBAL VARIABLES LIKE 'innodb_deadlock_detect';
If frequent deadlock warnings cause concern, debugging information can be collected in the error log by enabling the
innodb_print_all_deadlocks variable and turning it off later.
SHOW GLOBAL VARIABLES LIKE 'innodb_print_all_deadlocks';
On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock and it may be efficient
to disable deadlock detection and rely on innodb_lock_wait_timeout for transaction rollback when a deadlock occurs.
Table locks
MySQL enables client sessions to acquire table locks explicitly for cooperating with other sessions for access to tables, or to prevent
other sessions from modifying tables during periods when a session requires exclusive access to them.
LOCK TABLES and SELECT privilege is needed for each object to be locked.
LOCK TABLE tb1 READ; - lock a table in read mode for the current session
LOCK TABLE tb1 WRITE;
UNLOCK TABLES; - releases any table locks held by the current session
The session that holds the READ lock can read the table (but not write it). Multiple sessions can acquire a READ lock for the table at
the same time. Other sessions can read the table without explicitly acquiring a READ lock.
The session that holds the WRITE lock can read and write the table. No other session can access the table until the lock is released.
Lock requests for the table by other sessions are blocked while the WRITE lock is held.
Auto-increment locking
InnoDB provides a configurable locking mechanism that can improve scalability and performance of SQL statements that add rows to tables
with AUTO_INCREMENT columns. The lock mode used for generating auto-increment values is set by the innodb_autoinc_lock_mode parameter.
Permissible values are 0, 1 or 2 for traditional, consecutive or interleaved respectively.
show global variables like 'innodb_autoinc_lock_mode';
Transaction and locking information
The innodb_trx table has info about every transaction (excluding read-only transactions) currently executing inside InnoDB.
select * from information_schema.innodb_trx;
The table has these columns
trx_id - unique ID number, not created for for read-only, non-locking transactions
trx_lock_structs - number of locks reserved
trx_mysql_thread_id - thread_id (the id column of processlist table)
trx_operation_state - the transaction's current operation (inserting, fetching rows, flushing log, starting index read, updating or deleting)
trx_started - start time
trx_state - running, lock wait, rolling back, committing
trx_tables_locked - number of tables the statement has row locks on
trx_rows_locked - the approximate number or rows locked by this transaction
trx_rows_modified - the number of modified and inserted rows in this transaction
The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock
that a transaction holds that is blocking another transaction. (LOCK_TRX_ID - id of the transaction holding the lock)
The INNODB_LOCK_WAITS table contains one or more rows for each blocked InnoDB transaction, indicating the lock it has requested and
any locks that are blocking that request. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.
SHOW OPEN TABLES WHERE database = 'db1' and in_use > 0; - show locked tables, in_use = number of locks & lock requests
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; - requested and blocking locks
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; - blocked transactions and blocking locks
SELECT r.trx_id waiting_trx, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w, information_schema.innodb_trx b, information_schema.innodb_trx r
WHERE b.trx_id = w.blocking_trx_id and r.trx_id = w.requesting_trx_id;
Semaphores and mutexes
A semaphore is a lock structure consisting of a state and a counter for acquired locks used internally by mysqld and the OS to control
access to database resources that may be shared among running threads.
A mutex (mutually exclusive) is a specialized binary semaphore and is used in the code to enforce that only one thread at a given time
can have access to a common resource. When threads executing in the server need to access the same resource, the first thread to obtain
a lock on the mutex causes the other threads to wait until the lock is released.
show engine innodb mutex; - displays InnoDB mutex and rw-lock statistics
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%'; - available InnoDB mutex wait instruments
Mutexes:
buf_pool - all the control data structures of the buffer pool
flush_list - the buffer pool flush list
lock_sys_page - page lock queue shards
log_sys - log data structures and LSN
log_buf - writes to buffer pool pages due to mini-transactions
Query optimization
Buffering
join_buffer_size
The minimum size of the buffer that is used for plain index scans, range index scans and joins doing full table scans. Unless Batched Key
Access (BKA) is used (in the optimizer_switch variable), there is no gain from setting the buffer larger than required to hold each
matching row, and all joins allocate at least the minimum size, so the global setting should be small and changed to a larger setting only
in sessions that are doing large joins. Memory allocation time can cause performance drops if the global size is larger than needed by
most queries that use it.
When BKA is used, join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer,
the more sequential access will be to the right hand table of a join operation, which can improve performance.
SHOW GLOBAL VARIABLES LIKE 'join_buffer_size'; - default 256kB
sort_buffer_size
Each session that performs a sort allocates a buffer of this size. If the sort_merge_passes status variable is large, sort_buffer_size can
be increased to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or indexing. It is better to
increase it as a session setting as increasing it globally will slow down most queries that sort for memory allocation.
SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size'; - default 256kB
SHOW GLOBAL STATUS like 'sort_merge_passes'; - number of merge passes the sort algorithm has had to do
Using Explain
Explain provides information about how MySQL executes a statement.
Join types:
const - const is used when all parts of a PRIMARY KEY or UNIQUE index are compared to constant values. The table has at most one matching
row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as
constants by the rest of the optimizer. const tables are very fast because they are read only once
system - The table has only one row (= system table). This is a special case of the const join type
eq_ref - One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this
is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT
NULL index
ref - All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if
the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot
select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type
range - Only rows in a given range are retrieved, using an index to select the rows. The key column indicates which index is used. The
key_len contains the longest key part that was used. The ref column is NULL for this type. range can be used when a key column is compared
to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN() operators
index - The index join type is the same as ALL, except that the index tree is scanned. MySQL can use this join type when the query uses
only columns that are part of a single index. This occurs two ways:
a. If the index is a covering index for the queries and can be used to satisify all data required from the table, only the index tree is
scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index
usually is smaller than the table data
b. A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra
column
index_merge - retrieves rows with multiple range scans and merges their results into one. This access method merges index scans from a
single table only and can produce unions, intersections or unions-of-intersections of its underlying scans
ALL - A full table scan is done for each combination of rows from the previous tables. This is normally bad if the table is the first
table not marked const and worse in all other cases.
Extra column info:
Range checked for each record (index map: N) - MySQL found no good index to use, but some indexes might be used after column values from
preceding tables are known. For each row combination in the preceding tables, MySQL checks whether a range or index_merge access method
can be used to retrieve rows. Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map
value N is a bitmask value that indicates which indexes are candidates. For e.g a value of 0x19 (binary 11001) means that indexes 1, 4
and 5 will be considered.
Select tables optimized away - The optimizer determined that at most one row should be returned and the rows to be read can be read
during the optimization phase (by reading index rows) and no reads are needed during query execution
Using filesort - MySQL must do an extra pass to retrieve the rows in sorted order by going through all rows that match the WHERE clause
according to the join type and storing the sort key and pointer. The keys then are sorted and the rows retrieved in sorted order
Using index - The column information is retrieved from the table using only the index tree without having to read the actual row. This
strategy is used when the query uses only columns that are part of a single index
Using index condition - Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In
this way, index information is used to defer ('push down') reading full table rows unless it is necessary
Using intersect(...) - algorithm of index_merge
Using sort_union(...) - algorithm of index_merge
Using temporary - To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query
contains GROUP BY and ORDER BY clauses
Using union(...) - algorithm of index_merge
Using where - A WHERE clause is used to restrict which rows to match against the next table or send to the client
Query profiling
The performance schema statement events and stage events can be used to display profiling information that indicates resource usage for
statements executed during the course of the current session.
1. Update the default row in the setup_actors table to disable historical event collection and monitoring for all foreground threads and
insert a new row that enables for just the user that will run the query
UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%';
INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('localhost','test_user','%','YES','YES');
2. Ensure that statement and stage instrumentation is enabled by updating the setup_instruments table
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
3. Ensure that events_statements_* and events_stages_* consumers are enabled
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
4. Run the statement to be profiled under the user account being monitored
5. Identify the EVENT_ID of the statement by querying the events_statements_history_long table
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';
6. Query the events_stages_history_long table to retrieve the statement's stage events. Stages are linked to statements using event
nesting. Each stage event record has a NESTING_EVENT_ID column that contains the EVENT_ID of the parent statement.
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000080 |
| stage/sql/checking permissions | 0.000005 |
| stage/sql/Opening tables | 0.027759 |
| stage/sql/init | 0.000052 |
| stage/sql/System lock | 0.000009 |
| stage/sql/optimizing | 0.000006 |
| stage/sql/statistics | 0.000082 |
| stage/sql/preparing | 0.000008 |
| stage/sql/executing | 0.000000 |
| stage/sql/Sending data | 0.000017 |
| stage/sql/end | 0.000001 |
| stage/sql/query end | 0.000004 |
| stage/sql/closing tables | 0.000006 |
| stage/sql/freeing items | 0.000272 |
| stage/sql/cleaning up | 0.000001 |
+--------------------------------+----------+
Back
IMPORTING/EXPORTING DATA
Loading text files
Load Data statement
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. It is usually 20 times faster than using
INSERT statements.
LOAD DATA LOCAL INFILE '\\\\servername\\dir\\subdir\\data.txt' [IGNORE/REPLACE] INTO TABLE t1
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' - field and line delimiters and text qualifier
IGNORE 1 LINES - ignore first line
(col1, col2, col3); - columns to load
LOAD DATA LOCAL INFILE 'C:\\Users\\vincent\\Documents\\data.txt' INTO TABLE t1 - load a UTF-8 file
CHARACTER SET UTF8
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
If LOCAL is specified with LOAD DATA, the file is read by the client program on the client host and sent to the server and if not, the file
must be located on the server host and is read directly by the server (needs FILE privilege). When using LOCAL, a copy of the file is
created in the server OS's temporary directory, and is not configurable in MySQL. (/tmp on Linux systems and C:\WINDOWS\TEMP on Windows).
Lack of sufficient space can cause the load to fail. Both the server (local-infile=1) and client (mysql --local-infile -uroot -p) need to be configured to permit the load.
Using LOCAL is a bit slower than letting the server access the files directly as the file contents must be sent over the connection by the
client to the server. On the other hand, the FILE privilege is not needed to load local files.
If LOCAL is not specified, the server reads a text file located on the server host and the FILE privilege is required. Non-LOCAL load and export operations are also subject to the secure_file_priv setting. If this variable is a nonempty directory name, the file to be loaded/exported must be located in that directory.
show global variables like 'secure_file_priv';
Field and line handling
The FIELDS and LINES clauses are optional but FIELDS must precede LINES if both are specified. If FIELDS is specified, at least one of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, ESCAPED BY) must be specified. Arguments to these clauses can only contain ASCII characters.
IF FIELDS and LINES clauses are not specified, the defaults are FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY ''. \ is the MySQL escape character within strings in SQL statements and to specify a literal backslash, two backslashes must be specified to be interpreted as a single backslash. The escape sequences '\t' and '\n' specify tab and newline characters.
If IGNORE or REPLACE are not specified without LOCAL, an error occurs when a duplicate key value is found and the rest of the file is
ignored. With LOCAL, the default behavior is the same as if IGNORE is specified as the server has no way to stop transmission of the file
in the middle.
- using SET to insert derived values
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (col1, @var1) SET col2 = @var1/100;
- using SET to supply values not in file
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (col1, col2) SET col3 = CURRENT_TIMESTAMP;
- discard an input field by assigning it to an unused user variable
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (col1, @dummy, col2);
Loading xml files
The LOAD XML INFILE statement reads data from an XML file into a table. By default, the <row> element is considered to be the
equivalent of a table row; this can be changed using the ROWS IDENTIFIED BY clause.
The field names in the XML file should match the column names else those columns are set to null.
LOAD XML LOCAL INFILE 'person.xml' INTO TABLE person
ROWS IDENTIFIED BY '<person>'
SET colx = NOW(), coly = 'processing';
Exporting table data
The SELECT INTO OUTFILE statement is the complement of LOAD DATA and exports table data to text files on the server machine (needs FILE privilege). The file should not exist already (cannot be overwritten to safeguard system files) and mysql user may need write permissions on the folder.
If INTO DUMPFILE is used instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful for selecting a BLOB value and storing it in a file.
- produce a CSV output file
SELECT * INTO OUTFILE '/tmp/table1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table1;
FIELDS ESCAPED BY controls how to write special characters and is used when necessary to avoid ambiguity as a prefix that precedes following characters on output
the FIELDS ESCAPED BY character
the FIELDS [OPTIONALLY] ENCLOSED BY character
the first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values
ASCII NUL
The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY and LINES TERMINATED BY characters must be escaped so that the file can be read back in reliably.
SELECT * INTO OUTFILE '/tmp/t1.txt'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
LINES TERMINATED BY '\n'
FROM t1;
The below example executes a query in a loop and writes out multiple files.
DELIMITER $$
DROP PROCEDURE IF EXISTS `usp_create_txn_files`$$
CREATE PROCEDURE `usp_create_txn_files`()
BEGIN
DECLARE DECLARE did INT; DECLARE dcode VARCHAR(255); DECLARE bmonth TINYINT UNSIGNED; DECLARE filename VARCHAR(255);
DECLARE stdate DATETIME; DECLARE enddate DATETIME;
SET did = 0;
DROP TEMPORARY TABLE IF EXISTS result;
CREATE TEMPORARY TABLE result (d_code VARCHAR(255), txn_id VARCHAR(255), txn_date VARCHAR(20), quantity VARCHAR(255));
SELECT MIN(id) INTO did FROM t1 WHERE STATUS = 1 AND id > did;
WHILE (did IS NOT NULL) DO
SELECT code INTO dcode FROM t1 WHERE id = did;
SET bmonth = 3;
WHILE (bmonth < 6) DO
SET stdate = CONCAT('2018-',LPAD(bmonth,2,'00'),'-01 00:00:00');
SET enddate = CONCAT(LAST_DAY(stdate), ' 23:59:59');
TRUNCATE TABLE result;
INSERT INTO result VALUES ('d_code','txn_id','txn_date','quantity');
INSERT INTO result SELECT * FROM bill b WHERE d_code = dcode AND txn_date BETWEEN stdate AND enddate;
SET filename = CONCAT('/txn_files/txns_',dcode,'_2018', LPAD(MONTH(stdate),2,'00'),'.txt');
SET @sqlout = CONCAT("select * into outfile '", filename, "' FIELDS TERMINATED BY '|' ESCAPED BY '' LINES TERMINATED
BY '\\n'", " from result;");
PREPARE stmt FROM @sqlout;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET bmonth = bmonth + 1;
END WHILE;
SELECT MIN(id) INTO did FROM t1 WHERE STATUS = 1 AND id > did;
END WHILE;
DROP TEMPORARY TABLE result;
END$$
DELIMITER ;
Back
BACKUP/RECOVERY
Backups and Restores
The MySQL Enterprise Backup product does a hot backup of all InnoDB tables. MyISAM and other non-InnoDB tables are backed up last, using
the warm backup technique: the database continues to run, but the system is in a read-only state during that phase of the backup.
MySQL Enterprise Backup performs physical backups. Logical backup tools include the mysqldump program and the SELECT ... INTO OUTFILE
statement. These work for any storage engine, even MEMORY.
Full and Point-in-Time (incremental) recovery
A full recovery restores all data from a full backup. Point-in-time recovery is based on the binary log and typically follows a
full recovery from the backup files that restores the server to its state when the backup was made. Then the data changes written
in the binary log files are applied as incremental recovery to redo data modifications and bring the server up to the
desired point in time.
mysqldump
The mysqldump client is a backup program that takes logical backups and can be used to dump MySQL databases for backup or transfer to another SQL server. The dump typically contains SQL statements to create/populate tables but can also be used to generate files in CSV, other delimited text or XML format.
Options
--all-databases dump all tables in all databases
--events, -E include event scheduler events (disabled by default)
--flush-logs flush the log files before starting the dump. Requires the RELOAD privilege. Closes current binary log file and
creates a new one to set up a recovery point
--force, -f ignore all errors during a table dump
--insert-ignore write INSERT IGNORE rather than INSERT statements
--no-autocommit enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-info, -t data only, no CREATE TABLE statements
--no-data, -d structure only
--quick, -q useful for dumping large tables, dumps one row at a time instead of retrieving the entire row set and buffering
it in memory
--routines, -R include stored routines in the dump (disabled by default)
--set-gtid-purged=AUTO/ON/OFF controls the inclusion of a SET @@GLOBAL.gtid_purged statement and a SET @@SESSION.sql_log_bin=0
statement (which disables binary logging and prevents new GTIDs from being generated when reloading) in the output
AUTO - the default value. If GTIDs are enabled on the server and gtid_executed is not empty, SET @@GLOBAL.gtid_purged is added to
the output, containing the GTID set from gtid_executed. If GTIDs are enabled, SET @@SESSION.sql_log_bin=0 is added to the output
OFF - SET @@GLOBAL.gtid_purged and SET @@SESSION.sql_log_bin=0 are not added to the output
ON - if GTIDs are enabled, SET @@GLOBAL.gtid_purged (unless gtid_executed is empty) and SET @@SESSION.sql_log_bin=0 are added to
the output. An error occurs if this option is set but GTIDs are not enabled
--single-transaction sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the
server before dumping data. Useful with transactional tables, it dumps the consistent state of the database at the
time when START TRANSACTION was issued without blocking any applications
--skip-add-drop-table no DROP TABLE statements
--skip-add-locks no LOCK TABLES and UNLOCK TABLES statements surrounding each table dump
--skip-lock-tables do not lock tables before dumping them
--triggers include triggers (enabled by default)
--xml, -X write dump output as well-formed XML
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers and LOCK TABLES if the --single-transaction option is not used.
mysqldump db1 > /home/dir/db1.sql - dump a database
mysqldump --all-databases > all_dbs.sql - dump all databases
mysqldump --databases db1 db2 > some_dbs.sql - dump specific databases
mysqldump db1 t1 t2 t3 > t123.sql - dump tables t1, t2, t3 only
mysqldump db1 --ignore-table=t1 --ignore-table=t2 > db1.sql - dump all tables except t1, t2
mysqldump db1 tbl1 >> multitbl.sql - append to existing dump
mysqldump -u user -p pwd --databases db1 --tables t1 --where="id < 1000" > dump.sql - using where
mysqldump -u user -p pwd --databases db1 --tables t1 --where="1 limit 100" > dump.sql - using limit
mysqldump -u user -p pwd --databases db1 --tables t1 --where="id in (select id from t2)" > dump.sql - using subquery
mysql dbname < /dir1/db_full.sql - restore from dump
mysql -hHostname dbname < /dir1/db_full.sql - restore in a different host
To dump a database with stored procedures, SELECT privilege is needed on the mysql.proc table.
GRANT SELECT ON mysql.proc to 'mysqldump-user';
Troubleshooting/Recovery
MySQL startup problems
If Errcode 13 (Permission denied) is got when starting mysqld, the privileges of the data directory or its contents do not permit server access and the permissions need to be changed for the involved folders and files so that the server has the right to use them. The server can also be started as root, but this raises security issues and should be avoided.
Can't start server: Bind on TCP/IP port: Address already in use/Bind on unix socket... - TCP/IP port or Unix socket file is being used by another mysqld or program
mysql.service: Start request repeated too quickly - Invalid entry in the config file
On Windows, if the MySQL service doesn't startup with the Error 1067: The process terminated unexpectedly, it could be due to mysqld.exe already running (but not as a service) and occupying port 3306. When the service tries to start and sees that port 3306 is already taken, it fails. If mysqld.exe is seen running in the Windows task manager it can be killed and service can be started again.
Connection errors
A MySQL client on Unix can connect to the mysqld server in two ways - by using a Unix socket file to connect through a file in the file
system (default /tmp/mysql.sock) or by using TCP/IP which connects through a port number. A Unix socket file connection is faster than
TCP/IP but can be used only when connecting to a server on the same computer. A Unix socket file is used if a host name is not specified
or localhost is specified.
show global variables like 'socket'; - the name of the socket file(Unix)/named pipe(Windows) used for local client connections
On a MySQL server running on Windows, TCP/IP can be used to connect. If the server is started with the --enable-named-pipe option, named
pipes can also be used to connect if the client is running on the same host as the server. The name of the named pipe is MySQL by default.
If a host name is not specified when connecting to mysqld, a MySQL client first tries to connect to the named pipe. If that does not work,
it connects to the TCP/IP port. The use of named pipes on Windows can be forced by using . as the host name.
The error '2002 Can't connect to local MySQL server through socket' normally means that there is no MySQL server running on the system or
that an incorrect Unix socket file name or TCP/IP port number are being used or the TCP/IP port has been blocked by a firewall or port
blocking service.
The error '2003 Can't connect to MySQL server on server (10061)' indicates that the network connection has been refused. The server
may not be running, may not have network connections enabled or the port specified is not correct.
ps xa | grep mysqld - check if mysqld process is running
The error '2013 Lost connection to MySQL server during query' has these likely causes
network connectivity trouble - the condition of the network needs to be checked
too many rows are being sent as part of one or more queries - net_read_timeout can be increased from its default of 30 seconds to 60
seconds or longer, sufficient for the data transfer to complete
BLOB values that are larger than max_allowed_packet, which may need to be increased
Rarely, the 'Lost connection to MySQL' error can happen when the client is attempting the initial connection to the server. In this case,
the problem may be resolved by increasing the connect_timeout value, particularly for a very long distance or slow connection. The cause
can be confirmed by
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
which increases by one for each initial connection attempt that the server aborts. If 'reading authorization packet' is seen as part of
the error message, this is the solution needed.
If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable and logs an Aborted connection message to the error log. The cause can be any of the following
the client program did not call mysql_close() before exiting
the client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server
the client program ended abruptly in the middle of a data transfer
SHOW GLOBAL STATUS LIKE 'Aborted_clients'; - number of connections aborted because the client died without closing the connection properly
Forcing innodb recovery
MySQL tablespaces or data/index files may get corrupted by mysqld being killed or crashing in the middle of an update, low-quality
hardware, external program manipulating data or index files at the same time as mysqld without locking the table properly, tampering of
files in the data folder or bugs in the software.
The usual way to recover a corrupted database is to dump the tables and restore them back. Serious corruption might cause InnoDB
background operations or roll-forward recovery to crash and mysqld may not start up. In such cases, the innodb_force_recovery option can
be used to force the InnoDB storage engine to start up while preventing background operations from running, so that the tables can be
dumped.
The innodb_force_recovery variable can be set to a value greater than 0 (starting incrementally from 1) so that InnoDB can be started
and tables can be dumped. As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater
than 0. Values up to 3 are relatively safer and values above 4 can corrupt the data files. A setting of 4 or above places InnoDB
in read-only mode.
[mysqld]
innodb_force_recovery = 1 - possible values 0 (default) to 6
Data dictionary errors
If a file-per-table tablespace file (.ibd file) is missing, the below messages appear at startup. The DROP TABLE statement can be issued
to remove data about the missing table from the data dictionary.
[ERROR] InnoDB: Cannot open datafile for read-only: './db1/t1.ibd' OS error: 71
[Warning] InnoDB: Ignoring tablespace `db1/t1` because it could not be opened
The below message appears if there is an orphan .frm file without the .ibd file or the tablespace id of the .ibd file doesn't match the
one in the data dictionary.
[Warning] InnoDB: Cannot open table db1/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists
Server error codes
1022 Can't write; duplicate key in table 't1' - foreign key names must be unique in a database
1030 Got error xx from storage engine - check OS error code xx
1032 Can't find record in 'table' - missing data in replication slave
1034 Incorrect key file table 't1', try to repair it - possible tmpdir out of space while altering table
1040 Too many connections - max_connections limit has been reached, connect as SUPER user to analyze
1050 Table already exists - when moving table to another DB by renaming, check for duplicate foreign keys
1062 Duplicate entry 'x' for key 'keyname'
1114 Table 'x' is full - shortage of disk/mount space, temp folder space or ibdata1 file space
1129 Host is blocked due to many connection errors - max_connect_errors has been exceeded, use FLUSH HOSTS or mysqladmin flush-hosts
1130 Host is not allowed to connect to this server - user with hostname (or %) doesn't exist in mysql.user
1133 Can't find any matching row in the user table - trying to grant to a non-existent user
1135 Can't create a new thread - reduce max_connections variable and if using 32-bit mysql upgrade to 64-bit for gaining access to more memory
1142 Command denied to user for table - lack of privilege or incorrect table or database name
1143 Select command denied for user for column in table - if user is empty, user who created view is not in mysql.user
1146 Table 'db1.t1' doesn't exist when using LOCK TABLES (mysqldump) - corrupted table, discard tablespace
1153 Got a packet bigger than 'max_allowed_packet' bytes - client or mysqld server received a packet larger than max_allowed_packet
1158 Got an error reading communication packets - client connection terminated abnormally or exceeded the max_allowed_packet for queries
1171 All parts of primary key must be NOT NULL - declare primary key column(s) as not nullable
1186 Binlog closed, cannot Reset Master - enable binary logging and restart
1194 Table 't' is marked as crashed and should be repaired - truncate table or restore from backup
1205 Lock wait timeout exceeded; try restarting transaction
1206 The number of locks exceeds the lock table size - buffer pool size is inadequate
1213 Deadlock found when trying to get lock; try restarting transaction - debug using innodb_print_all_deadlocks
1215 Cannot add foreign key constraint - possible datatype mismatch (or use SHOW ENGINE INNODB STATUS)
1231 Variable x can't be set to the value of NULL (mysqldump) - max_allowed_packet can be increased to avoid loss of connection
1236 Error reading binary log from master; log event exceeded max_allowed_packet - increase max_allowed_packet of master and use same value for slave
1236 The master has purged binary logs containing GTIDs that the slave requires - provision a new slave
1236 Slave has more GTIDs than the master has, using the master's server UUID - GTIDs need to be synchronized
1265 Data truncated for column - oversized data or formatted number being inserted into a numeric field
1329 No data - zero rows fetched, selected or processed - handler for no rows found condition is needed for cursor
1396 Create user failed for 'user'@'%' - user with same name exists already
1418 This function has none of DETERMINISTIC, NO SQL or READS SQL DATA in its declaration and binary logging is enabled - functions must be declared deterministic for binary logging
1419 You do not have the SUPER privilege and binary logging is enabled - creating/altering functions or triggers needs SUPER privilege for binary logging
1593 The slave I/O thread stops because master and slave have equal MySQL server UUIDs - delete slave auto.cnf file and restart
1594 Relay log read failure - corrupted relay log, use RESET SLAVE to recreate the relay logs
1595 Slave I/O Relay log write failure could not queue event from master - disk full or corrupted binlog
1682 Native table db.x has the wrong structure - run mysql_upgrade and restart mysqld
1690 Value is out of range - cast unsigned columns as signed during subtraction
1701 Cannot truncate a table referenced in a foreign key constraint
1743 Slave I/O Replication event checksum verification failed while reading from network - may indicate a corrupted binlog
1789 Master has purged required GTIDs - replicate the missing transactions from elsewhere or provision a new slave from backup
1799 Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log - increase variable value or retry when concurrent DML is less
1812 Tablespace is missing for table - possible corruption of table or .ibd file
1813 Tablespace exists - .ibd file needs to be removed
1840 @@global.gtid_purged can only be set when @@global.gtid_executed is empty - use RESET MASTER to clear gtid_executed
1872 Slave failed to initialize relay log info structure from the repository - Reset slave
1878 Temporary file write failure - tmpdir folder is filled up
3009 Column count of table db.x is wrong. Created with mysql ver1 running with ver2 - run mysql_upgrade and restart mysqld
3019 Undo log error, no space in system tablespace for undo log pages - disk out of space
3077 To have multiple channels, repository cannot be of type FILE - change master_info_repository and relay_log_info_repository variables
Client error codes
2002 Can't connect to local MySQL server through socket '/var/run/mysqld.sock' - mysqld.exe isn't running or socket file is missing
2003 Can't connect to MySQL server on 'host' - use 'telnet <server> 3306' to verify server is running
2006 MySQL server has gone away - server timed out/encountered a large query/a running thread was killed and closed the connection (increase max_allowed_packet/wait_timeout)
2013 Lost connection to server during query - max_allowed_packet may need to be increased along with net_read_timeout and net_write_timeout
2059 Authentication plugin cannot be loaded - install plugin or alter user to use an available plugin
2061 Authentication plugin 'caching_sha2_password' requires secure connection - use --get-server-public-key option to request the RSA public key from the server
Global (server or client) errors
These errors can be produced by the server or by client programs and have error codes in the range from 1 to 999.
Can't create/write to file '/home/temp/MYzplJok' (errcode 13) - lack of permissions
Can't create/write to file '/temp/MYzplJok' (errcode 28) - out of space on device
Error dropping database (Can't rmdir './db1', errno 17) - database directory has non-table files that need to be removed
'file_name' not found (errno 23) - not enough file descriptors have been allocated to MySQL
Can't open file: file_name (errno 24) - set open_files_limit to increase the number of file descriptors available to mysqld
Can't create/write to file 'DB/tmpdir/MY0zlbyt' (errno 24 - Too many open files) - increase open_files_limit
Back
USER MANAGEMENT/SECURITY
User accounts
MySQL stores accounts in the system table mysql.user. An account is defined in terms of a user name and the client host or hosts from which
the user can connect to the server.
To authenticate client connections for accounts that use MySQL native authentication (implemented by the mysql_native_password
authentication plugin), the server uses passwords stored in the user table. If the server authenticates a client using some other plugin,
the authentication method that the plugin implements may or may not use a password stored in the user table.
CREATE USER 'jeff'@'localhost' IDENTIFIED BY 'pwd'; - user jeff can connect only from the local host
CREATE USER 'joe'@'%' IDENTIFIED BY 'pwd'; - can connect from any host
SET PASSWORD FOR 'abe'@'host_name' = 'eagle'; - set/change password
DROP USER 'jeffrey'@'localhost'; - takes effect when the user's existing session is closed
The default_password_lifetime system variable is used to establish automatic password-expiration policy globally. The default value is 0 which disables expiration. A positive value N indicates that the password must be changed every N days.
show global variables like 'default_password_lifetime';
Anonymous user
An account with a blank user name is an anonymous user.
CREATE USER ''@'localhost' identified by 'pwd';
When a local anonymous user exists, any user who connects from the local host with the correct password for the anonymous user will be
permitted access, with the privileges associated with the anonymous-user account.
Setting Account Resource Limits
CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank' - use ALTER or GRANT for existing users
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;
Authentication plugins
MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords
sha256_password - implements basic SHA-256 authentication
caching_sha2_password - implements SHA-256 authentication, but uses caching on the server side for better performance and has additional features
Privileges
MySQL provides privileges that apply in different contexts and at different levels of operation:
Administrative privileges - enable users to manage operation of the MySQL server. These privileges are global and not database-specific.
They can be assigned by ON *.*
FILE - allows read/write of files on the server host
PROCESS - allows viewing of information about threads executing within the server
RELOAD - allows use of the FLUSH statement
REPLICATION CLIENT - to be able to execute replication-related statements
REPLICATION SLAVE - needed by accounts used by slave servers to connect to this master
SHOW DATABASES - to be able to see all database names
SUPER - enables use of administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL
USAGE - no privileges, allow connect only
Database privileges - apply to a database and to all objects within it. These privileges can be granted for specific databases (db.*), or
globally to all databases (*.*)
CREATE - enable creation of databases and tables
CREATE TEMPORARY TABLES - enables the creation of temporary tables
CREATE ROUTINE - create of stored routines
CREATE VIEW - enables use of the CREATE VIEW statement
DROP - drop existing databases, tables and views. Also needed for TRUNCATE TABLE
EVENT - required to create, alter, drop, or see events for the Event Scheduler
LOCK TABLES - enables use of explicit LOCK TABLES statements to lock tables (with SELECT privilege)
Privileges for database objects such as tables, indexes, views and stored routines can be granted for specific objects within a database, for all objects of a given type (e.g all tables) within a database or globally for all objects of a given type in all databases.
ALTER - enables the use of the ALTER TABLE statement and renaming tables
ALTER ROUTINE - alter or drop stored routines
DELETE - delete rows from a table
EXECUTE - execute stored routines
INDEX - create or drop indexes on existing tables
REFERENCES - create foreign key constraints on a table
SELECT - select rows from tables
SHOW VIEW - enables use of the SHOW CREATE VIEW statement
TRIGGER - create, drop, execute or display triggers for a table
UPDATE - update rows in tables
SHOW PRIVILEGES; - list of privileges the server version supports
GRANT SELECT, INSERT ON db1.tbl1 TO 'jeff'@'localhost';
REVOKE INSERT ON *.* FROM 'jeff'@'localhost';
GRANT SELECT ON *.* TO 'user'@'%' WITH GRANT OPTION; - allow user to grant privileges
SHOW GRANTS FOR 'joe'@'home.example.com'; - show privileges for a user
SHOW GRANTS; - for CURRENT_USER();
The ALL and ALL PRIVILEGES privilege specifiers are used to grant all privileges available at a given privilege level (except GRANT
OPTION).
GRANT ALL ON db1.* TO 'joe'@'%';
The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level. Hence
if a database name contains a _ character it needs to be specified as \_ in the GRANT statement to prevent the user from being able to
access additional databases matching the wildcard pattern.
GRANT SELECT ON `db%`.* to user1; - grant access to all databases starting with 'db'
Grant tables
The information about user accounts and the privileges held by them is contained in several grant tables in the mysql database. The server
reads the contents of the grant tables into memory when it starts and the tables can be reloaded by issuing a FLUSH PRIVILEGES statement.
Normally, the contents of these tables are are not modified directly but manipulated indirectly by using statements such as CREATE USER,
GRANT and REVOKE to set up accounts and control the privileges available to each account.
user - contains user accounts, global privileges, and other non-privilege columns
SELECT * from mysql.user;
db - contains database-level privileges
SELECT * FROM mysql.db WHERE Db = 'db1'; - show users with access on db
tables_priv - contains table-level privileges
columns_priv - contains column-level privileges
procs_priv - contains stored procedure and function privileges
proxies_priv - contains proxy-user privileges
If the server is started with the --skip-grant-tables option, it does not read the grant tables or implement any access control and any
user can connect and perform any operation till access checking is enabled by flushing the privileges.
A grant table reload affects privileges for existing client sessions as follows
table and column privilege changes take effect with the client's next request
database privilege changes take effect the next time the client executes a USE dbname statement
global privileges and passwords are unaffected for a connected client and take effect only in sessions for subsequent connections
Roles
Roles are named collections of privileges and they can have privileges granted to and revoked from them like user accounts. A user account
can be granted roles which grants to the account the privileges associated with each role. Creating roles requires the CREATE ROLE or
CREATE USER privilege.
Role names, like user account names are in the 'user'@'host' format. The host part defaults to '%', if omitted. The user and host parts
can be unquoted unless they contain special characters such as - or %.
CREATE ROLE 'app_developer', 'app_read', 'app_write';
DROP ROLE 'app_read', 'app_write';
Dropping a role revokes it from every account to which it was granted. Roles named in the mandatory_roles variable cannot be dropped.
GRANT 'app_developer' TO 'user1'@'localhost';
REVOKE role1 FROM user1;
SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
Roles can be specified as mandatory by naming them in the mandatory_roles system variable. The server treats a mandatory role as granted
to all users so that it need not be granted explicitly to any account. Setting mandatory_roles at runtime requires the ROLE_ADMIN
privilege.
SHOW GLOBAL VARIABLES LIKE 'mandatory_roles';
Roles granted to a user account can be active or inactive within account sessions. If a granted role is not active within a session, its
privileges do not apply. The CURRENT_ROLE() function displays active roles within the session.
SELECT CURRENT_ROLE();
To cause all explicitly granted and mandatory roles to be automatically activated when users connect to the server, the
activate_all_roles_on_login variable has to be enabled. Automatic role activation is disabled by default.
SHOW GLOBAL VARIABLES LIKE 'activate_all_roles_on_login';
Authenticating connections
For all connection attempts, the server checks credentials first and then account locking state. If both succeed, the server accepts the
connection and then waits for requests.
Credential checking is performed using the three user table scope columns (Host, User and authentication_string). Locking state is
recorded in the user table account_locked column. The server accepts the connection only if the Host and User columns in some user table
row match the client host name and user name, the client supplies the password specified in that row and the account_locked value is 'N'.
For each request that issued through the connection, the server checks whether the use has sufficient privileges using the privilege
columns in the grant tables.
Pluggable authentication
When a client connects to the MySQL server, the server uses the user name provided by the client and the client host to select the
appropriate row from the mysql.user system table, determines the authentication plugin to use and then authenticates the client.
If the server cannot find the plugin, an error occurs and the connection attempt is rejected. Else the server invokes that plugin to
authenticate and return a status indicating whether the user provided the correct password and is permitted to connect.
Pluggable authentication enables these important capabilities
Choice of authentication methods - makes it easy to choose and change the authentication method used for individual MySQL accounts
External authentication - makes it possible for clients to connect to the MySQL server with credentials appropriate for authentication
methods that store credentials elsewhere than in the mysql.user table. For e.g plugins can be created to use external authentication
methods such as PAM, Windows login IDs, LDAP or Kerberos
Proxy users - if a user is permitted to connect, an authentication plugin can return to the server a user name different from the name
of the connecting user, to indicate that the connecting user is a proxy for another user (the proxied user). While the connection lasts,
the proxy user is treated as having the privileges of the proxied user, effectively impersonating one another
MySQL provides several authentication plugins like native, SHA-256, LDAP, Fido, Kerberos etc. Native authentication is based on the password hashing method in use from before the introduction of pluggable authentication.
The CREATE USER and ALTER USER statements can specify how an account authenticates.
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password' AND IDENTIFIED WITH authentication_ldap_simple;
The authentication_policy variable is used to administer multifactor authentication (MFA) capabilities, where factor corresponds to an authentication method or plugin associated with an account.
SHOW GLOBAL VARIABLES LIKE 'authentication_policy'; - default *,,
The value of authentication_policy is a list of 1, 2 or 3 comma-separated elements. Each element can be an authentication plugin name, an asterisk or empty. The first element is mandatory.
If element N is an authentication plugin name, an authentication method for factor N is required and must use the named plugin and in addition, the plugin becomes the default plugin for factor N authentication methods that do not name a plugin explicitly.
Authentication plugins that use internal credentials storage can be specified for the first element only and cannot repeat. For e.g authentication_policy = 'caching_sha2_password, sha256_password' cannot be used
If element N is an asterisk (*), an authentication method for factor N is required and it may use any authentication plugin that is valid for element N
If element N is empty, an authentication method for factor N is optional. If given, it may use any authentication plugin that is valid for element N
If element N is missing from the list, an authentication method for factor N is forbidden. For e.g, a value of '*' permits only a single factor and thus enforces single-factor authentication (1FA) for accounts. In this case, CREATE and ALTER USER statements cannot specify authentication for factors 2 or 3.
When an authentication_policy element names an authentication plugin, the permitted plugin names for the element are subject to these conditions
Element 1 must name a plugin that does not require a registration step. For e.g, authentication_fido cannot be named.
Elements 2 and 3 must name a plugin that does not use internal credentials storage.
When authentication_policy element N is *, the permitted plugin names for factor N in account definitions are subject to these conditions
For factor 1, account definitions can use any plugin. Default authentication plugin rules apply for authentication specifications that do not name a plugin.
For factors 2 and 3, account definitions cannot name a plugin that uses internal credentials storage. For example, with '*,*', '*,*,*', '*,', '*,,' authentication_policy settings, plugins that use internal credentials storage are only permitted for the first factor and cannot repeat.
When authentication_policy element N is empty, the permitted plugin names for factor N in account definitions are subject to these conditions
For factor 1, this does not apply because element 1 cannot be empty.
For factors 2 and 3, account definitions cannot name a plugin that uses internal credentials storage.
Empty elements must occur at the end of the list, following a nonempty element i.e the first element cannot be empty, and either no element is empty or the last element is empty or the last two elements are empty. For e.g, a value of ',,' is not permitted because it would signify that all factors are optional - accounts must have at least one authentication factor.
The default value of authentication_policy is '*,,'. This means that factor 1 is required in account definitions and can use any authentication plugin, and that factors 2 and 3 are optional and each can use any authentication plugin that does not use internal credentials storage.
Encrypted connections
MySQL supports encrypted connections between clients and the server using the TLS (Transport Layer Security) protocol. MySQL does not use the SSL protocol because it provides weak encryption. TLS incorporates algorithms that provide identity verification using the X509 standard.
SHOW VARIABLES LIKE 'have_ssl'; - whether mysqld supports secure connections
SHOW GLOBAL VARIABLES LIKE 'tls_version'; - protocols permitted by the server for encrypted connections
SHOW SESSION STATUS LIKE 'ssl_version'; - encryption protocol used for an encrypted connection
Server-side configuration
The --ssl option specifies that the server permits but does not require encrypted connections and is enabled by default. To make clients connect mandatorily using encrypted connections, the require_secure_transport variable must be enabled.
SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';
These system variables specify the certificate and key files the server uses when permitting clients to establish encrypted connections.
[mysqld]
ssl-ca=ca.pem - the Certificate Authority (CA) certificate
ssl-cert=server-cert.pem - the server public key certificate. Can be sent to the client and authenticated against its CA certificate
ssl-key=server-key.pem - the server private key
Client-side configuration
By default, MySQL client programs attempt to establish an encrypted connection if the server supports encrypted connections, with further control available through the --ssl-mode option. In the absence of an --ssl-mode option or --ssl-mode=PREFERRED, clients attempt to connect using encryption and if it cannot be established, fall back to an unencrypted connection.
--ssl-mode=PREFERRED - default
--ssl-mode=REQUIRED - clients require an encrypted connection and fail if one cannot be established.
--ssl-mode=DISABLED - clients use an unencrypted connection.
--ssl-mode=VERIFY_CA - require an encrypted connection and perform verification against the server CA certificate
--ssl-mode=VERIFY_IDENTITY - also perform verification against the server host name in its certificate
The following options on the client side identify the certificate and key files clients use when establishing encrypted connections to the server.
shell> mysql --ssl-ca=ca.pem \ - the CA certificate. If used, must specify the same certificate used by the server
--ssl-cert=client-cert.pem \ - the client public key certificate
--ssl-key=client-key.pem - the client private key
A client can determine whether the current connection with the server uses encryption from the ssl_cipher status variable.
SHOW STATUS LIKE 'ssl_cipher';
It is also possible to connect securely from within an SSH connection to the MySQL server host.
Data encryption
Encryption functions
Many encryption and compression functions return strings containing arbitrary byte values and should be stored in a VARBINARY or BLOB
column. This will avoid potential problems with trailing space removal or character set conversion that would change data values, if
a nonbinary column (CHAR, VARCHAR, TEXT) is used.
Some encryption functions return strings of ASCII characters - MD5(), SHA(), SHA1(), SHA2(), STATEMENT_DIGEST(), STATEMENT_DIGEST_TEXT().
Their return value has a character set and collation determined by the character_set_connection and collation_connection variables. This
is a nonbinary string unless the character set is binary.
AES_ENCRYPT() and AES_DECRYPT() implement encryption and decryption of data using the official AES (Advanced Encryption Standard)
algorithm. The AES standard permits various key lengths. By default these functions implement AES with a 128-bit key length and key
lengths of 196 or 256 bits can also be used. The key length is a trade off between performance and security.
AES_ENCRYPT(str, key_str)
AES_DECRYPT(crypt_str, key_str)
AES_ENCRYPT() encrypts a string using the key string key_str and returns a binary string containing the encrypted output. AES_DECRYPT()
decrypts the encrypted string using the key string key_str and returns the original plaintext string. If either function argument is NULL,
the function returns NULL.
The str and crypt_str arguments can be of any length and padding is automatically added to str so it is a multiple of a block as required
by block-based algorithms such as AES. This padding is automatically removed by the AES_DECRYPT() function. The length of crypt_str can be
calculated using this formula
16 * (trunc(string_length / 16) + 1)
For a key length of 128 bits, the most secure way to pass a key to the key_str argument is to create a truly random 128-bit value and pass
it as a binary value. For e.g
INSERT INTO t VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));
A passphrase can be used to generate an AES key by hashing the passphrase. For e.g
INSERT INTO t VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));
The COMPRESS() functiom compresses a string and returns the result as a binary string and requires MySQL to have been compiled with a
compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS().
SELECT LENGTH(COMPRESS(REPEAT('a',1000))); - returns 21
The MD5() function calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hexadecimal digits or NULL
if the argument was NULL. The return value can be used as a hash key.
SELECT MD5('testing'); - 'ae2b1fca515949e5d54fb22b8ed95575'
Tablespace encryption
Tablespace encryption uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys. When a
tablespace is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user
wants to access encrypted tablespace data, InnoDB uses a master key to decrypt the tablespace key. The decrypted version of a tablespace
key never changes, but the master key can be changed as required (master key rotation).
Tablespace encryption relies on a keyring plugin for master encryption key management. The keyring_file plugin is available in all
mysql editions and stores keyring data in a file local to the server host. Additional keyring plugins are offered in the Enterprise
edition.
Enabling tablespace encryption
A keyring plugin must be installed and configured. Keyring plugin installation is performed at startup using the early-plugin-load
option and it ensures that the plugin is available prior to initialization of the InnoDB storage engine.
The keyring_file plugin library file base name is keyring_file. The file name suffix differs per platform (.so for Unix-like systems,
.dll for Windows).
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/usr/local/mysql/mysql-keyring/keyring
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%'; - check plugin status
To enable/disable encryption for a file-per-table tablespace
CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
ALTER TABLE t1 ENCRYPTION='Y';
ALTER TABLE t1 ENCRYPTION='N';
The master encryption key should be rotated periodically. Master key rotation is an atomic, instance-level operation. Each time the
master encryption key is rotated, all tablespace keys in the MySQL instance are re-encrypted and saved back to their respective tablespace
headers. If master key rotation is interrupted by a server failure, InnoDB rolls the operation forward on server restart.
ALTER INSTANCE ROTATE INNODB MASTER KEY; - rotate the master encryption key
Master key rotation supports concurrent DML but it cannot be run concurrently with tablespace encryption operations and locks are taken
to prevent conflicts that could arise from concurrent execution.
The keyring
The InnoDB storage engine uses the keyring to store its key for tablespace encryption.
SHOW GLOBAL VARIABLES LIKE 'keyring_file_data';
At plugin startup, if keyring_file_data specifies a file that does not exist, the keyring_file plugin attempts to create it and writes a
message to the log if the file cannot be accessed or created.
Once the keyring_file plugin has created its data file and started to use it, the file should not be removed. InnoDB uses the file to
store the master key used to decrypt the data in tables that use InnoDB tablespace encryption. Loss of the file will cause data in such
tables to become inaccessible. It is permissible to rename or move the file, as long as the value of keyring_file_data is changed to
match.
Keyring operations are transactional. The plugin uses a backup file during write operations to ensure that it can roll back to the original
file if an operation fails. The backup file has the same name as the value of the keyring_file_data variable with a suffix of .backup.
MySQL Enterprise Transparent Data Encryption (TDE)
MySQL Enterprise TDE(Transparent Data Encryption) enables data-at-rest encryption by encrypting the physical files of the database. Data
is encrypted automatically in real time prior to writing to storage and decrypted when read from storage.
Back
HIGH-AVAILABILITY/SCALABILITY
Availability refers to the ability to cope with and recover from failures on the host, including failures of MySQL, the OS, or the
hardware and maintenance activity that may otherwise cause downtime. Scalability refers to the ability to spread both the database and
the load of application queries across multiple MySQL servers.
Replication
Replication enables data from one MySQL server (master) to be replicated to one or more servers (slaves). MySQL replication is asynchronous
by default and slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, all
databases, selected databases or even selected tables can be replicated.
Replication is based on the master server keeping track of all changes to its databases in its binary log. Each slave that connects to
the master requests a copy of the binary log i.e pulls the data from the master (rather than the master pushing the data to the slave)
and executes the events from the binary log that it receives. Each slave is able to read and update the copy of the database at its
own pace and can start and stop the replication process at will without affecting the ability to update to the latest database
status on either the master or slave side.
MySQL replication capabilities are implemented using three threads, one on the master server and two on the slave:
Binlog dump thread - The master creates a thread to send the binary log contents to a slave when the slave connects, which can be identified in the output of SHOW PROCESSLIST as the Binlog Dump thread. If there are no Binlog Dump threads on a master server, replication is not running i.e no slaves are currently connected.
Binlog dump thread states:
Sending binlog event to slave - Binary logs consist of events which is usually an update plus some other information. The thread has read
an event from the binary log and is now sending it to the slave
Finished reading one binlog; switching to next binlog - The thread has finished reading a binary log file and is opening the next one to
send to the slave
Master has sent all binlog to slave; waiting for more updates - The thread is idle, waiting for new events
Waiting to finalize termination - A brief state that occurs as the thread is stopping
Slave I/O thread - When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the
master and asks to send the updates recorded in its binary logs, which are written to a relay log in the slave.
Slave I/O thread states:
Waiting for master update - The initial state before connecting to the master
Requesting binlog dump - A brief state that occurs after connecting to the master. The thread sends to the master a request for the
contents of its binary logs, starting from the requested file name and position
Waiting for master to send event - The thread has connected to the master and is waiting for binary log events to arrive. If the wait
lasts for slave_net_timeout seconds, a timeout occurs and the thread makes an attempt to reconnect
Queueing master event to the relay log - The thread has read an event and is copying it to the relay log so that the SQL thread can process it
Waiting for slave mutex on exit - A state that occurs briefly as the thread is stopping
The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.
Slave SQL thread - The slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute
the events contained therein
Slave SQL thread states:
Reading event from the relay log - The thread has read an event from the relay log for processing
Slave has read all relay log; waiting for more updates - The thread has processed all events and is now waiting for the I/O thread to write
new events
Killing slave - The thread is processing a STOP SLAVE statement
Slave relay log
The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index
file that contains the names of all used relay log files. Relay log files have the same format as binary log files and can be read using
mysqlbinlog.
By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory. The slave uses an index file ( default
name - host_name-relay-bin.index in the data directory) to track the relay log files currently in use. The default relay log and
index file names can be overridden with the --relay-log and --relay-log-index server options.
The SQL thread automatically deletes each relay log file after it has executed all events in the file and no longer needs it.
SHOW GLOBAL variables LIKE '%relay%';
relay_log - base name for the relay log
relay_log_index - relay log index file
Configuring replication
The CHANGE MASTER TO/CHANGE REPLICATION SOURCE TO statement configures/changes the parameters that a slave/replica uses for connecting to the master, for reading the master binary log and reading the slave relay log. It also updates the contents of the master info and relay log info repositories. It needs the REPLICATION_SLAVE_ADMIN or SUPER privilege.
The optional FOR CHANNEL channel clause specifies the replication channel the statement applies to and is used to add a new channel or modify an existing channel.
CHANGE MASTER TO MASTER_HOST = host, MASTER_PORT = TCP/IP port, MASTER_USER = user, MASTER_PASSWORD = pwd, MASTER_AUTO_POSITION = 1 [FOR CHANNEL channel1];
MASTER_AUTO_POSITION = 1 - connect using GTID-based protocol (default = 0)
MASTER_LOG_FILE/MASTER_LOG_POS - coordinates the slave I/O thread should begin reading from the master for binary log file position based replication
CHANGE MASTER TO deletes all relay log files and starts a new one unless either the slave SQL thread or I/O thread is running.
START SLAVE starts the slave threads and requires the REPLICATION_SLAVE_ADMIN or SUPER privilege.
START SLAVE; - start both threads
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;
The log_slave_updates option makes a slave write updates received from a master to its own binary log. Binary logging must also be enabled
on the slave. This enables replication servers to be chained i.e a slave can be a master of another slave.
SHOW GLOBAL VARIABLES LIKE 'log_slave_updates';
GTID-based replication
A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of
origin (master). The GTID (serveruuid:txnid) is unique across all servers in a given replication setup. A GTID set comprises one or more
single GTIDs or ranges of GTIDs.
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49
When using GTIDs, each transaction can be identified and tracked and it is not necessary to refer to log files and positions when starting
a new slave or failing over to a new master, which greatly simplifies these tasks. Because GTID-based replication is completely
transaction-based, it is simple to determine whether masters and slaves are consistent.
The auto-skip function for GTIDs ensures that a transaction committed on the master can be applied only once on the slave guaranteeing
consistency. Once a transaction with a given GTID has been committed on a given server, any attempt to execute a subsequent transaction
with the same GTID is ignored by that server and no error is raised.
SHOW GLOBAL variables LIKE 'gtid_%';
gtid_mode - whether gtid-based logging is enabled
gtid_executed - all transactions executed on the server
gtid_owned - for internal use, list of GTIDs currently in use with the ids of threads that own them
gtid_purged - set of all transactions purged from the binary log, subset of gtid_executed
SHOW variables LIKE 'gtid_%';
gtid_next - how the next gtid value is obtained (Default - Automatic)
The variable gtid_executed contains the set of all transactions executed on the server and GTIDs that have been set by a SET gtid_purged
statement and is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS. When the
server starts gtid_executed is initialized by reading the binary logs. GTIDs are then added to the set as transactions are executed or if
any SET gtid_purged statement is executed.
The following categories of GTIDs are in gtid_purged
GTIDs of replicated transactions that were committed with binary logging disabled on the slave
GTIDs of transactions that were written to a binary log file that has now been purged
GTIDs that were added explicitly to the set by the statement SET @@GLOBAL.gtid_purged
The set of transactions that can be found in the binary logs at any given time is equal to
select GTID_SUBTRACT(@@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged);
The system table mysql.gtid_executed is used to preserve the assigned GTIDs of all the transactions applied on a server, except those that
are stored in a currently active binary log file. A row in this table contains the UUID of the server and the starting and ending
transaction IDs of the GTID set. These two values are the same for a row referencing only a single GTID.
select * from mysql.gtid_executed;
Compression of the mysql.gtid_executed table is performed by a dedicated foreground thread named thread/sql/compress_gtid_table, which
combines multiple rows for the same UUID into a single row.
SHOW GLOBAL variables like 'gtid_executed_compression_period'; - default 1000 (transactions)
GTID Auto-positioning
When GTIDs are in use, all the information that the slave needs for synchronizing with the master is obtained directly from the replication
data stream by enabling the MASTER_AUTO_POSITION option.
When a slave has GTIDs enabled and the MASTER_AUTO_POSITION option enabled, auto-positioning is activated for connection to the master. The
master must have GTID_MODE=ON for the connection to succeed. In the initial handshake, the slave sends a GTID set containing the
transactions that it has already received, committed or both. This GTID set is equal to the union of the set of GTIDs in the gtid_executed
variable (@@GLOBAL.gtid_executed) and the set of GTIDs recorded in the Performance Schema replication_connection_status table as received
transactions (the result of SELECT received_transaction_set FROM performance_schema.replication_connection_status).
The master responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the slave.
This exchange ensures that the master only sends the transactions with a GTID that the slave has not already received or committed. If the
slave receives transactions from more than one master, as in the case of a diamond topology, the auto-skip function ensures that the
transactions are not applied twice.
If any of the transactions to be sent by the master have been purged from the master's binary log or added to the set of GTIDs in the
gtid_purged system variable by another method, the master sends an error the slave and replication does not start. The GTIDs of the missing
purged transactions are identified and listed in the master's error log in a warning message. To recover from this error, the missing
transactions need to be replicated from another source or the slave needs to be replaced by a new slave created from a more recent backup.
Configuring GTID-based replication:
1. Stop both servers
2. Restart both servers with GTIDs enabled
[mysqld]
gtid_mode=ON
log-bin
enforce-gtid-consistency
server-id=1 (2 for slave)
log-slave-updates (for slave)
3. Direct the slave to use the master and start replicating
CHANGE MASTER TO MASTER_HOST='localhost', MASTER_PORT=3306, MASTER_USER='repluser', MASTER_PASSWORD='pwd', MASTER_AUTO_POSITION=1;
START SLAVE;
Multi-threaded replication
In a single-threaded slave, a single thread applies replication events serially. Transactions utilize separate CPU cores on the master,
while the slave uses just one CPU core to execute the replication events. A multi-threaded slave (MTS) provides parallel execution by using multiple worker threads and a coordinator thread to manage them. The way which transactions are distributed among applier threads is configured by slave_parallel_type. Setting slave_parallel_workers to a number greater than 0 creates an MTS with this number of applier threads. When set to 0 (the default) parallel execution is disabled and the slave uses a single applier thread.
Setting slave_parallel_workers has no immediate effect and the state of the variable applies on all subsequent START SLAVE statements. The
optimum number of applier threads depends on the workload and too many threads will increase the synchronization effort between threads
and may bring reduced benefit.
SHOW GLOBAL VARIABLES LIKE 'slave%';
slave_parallel_type - policy used to decide which transactions are allowed to execute in parallel on the slave
DATABASE - transactions that update different databases are applied in parallel
LOGICAL_CLOCK - transactions that are part of the same binary log group commit on a master are applied in parallel
slave_parallel_workers - number of slave applier threads for executing replication transactions in parallel (default = 0)
slave_preserve_commit_order - ensures that transactions are committed in the same order as they appear in the relay log
Once a multi-threaded slave has been started, transactions can begin to execute in parallel. With slave_preserve_commit_order enabled, the
executing thread waits until all previous transactions are committed before committing. While the slave thread is waiting for other workers to commit their transactions it reports its status as 'Waiting for preceding transaction to commit'. Enabling this mode on an MTS ensures that it never enters a state that the master was not in.
select * from performance_schema.replication_applier_status_by_coordinator; - status of the coordinator thread
select * from performance_schema.replication_applier_status_by_worker; - status of worker threads
Replication channels
In MySQL multi-source replication, a replica opens multiple replication channels, one for each source. The replication channels represent the path of transactions flowing from a source to the replica. Each replication channel has its own receiver (I/O) thread, one or more applier (SQL) threads, and relay log. When transactions from a source are received by a channel's receiver thread, they are added to the channel's relay log file and passed through to the channel's applier threads, enabling each channel to function independently.
The maximum number of channels that can be created on one replica in a multi-source replication topology is 256. A multi-source replica can also be set up as a multi-threaded replica by setting slave_parallel_workers to a value greater than 0. Each channel on the replica has the specified number of applier threads, plus a coordinator thread to manage them. The number of applier threads cannot be configured for individual channels.
To enable replication operations to act on individual replication channels, the FOR CHANNEL channel clause can be used with the following replication statements
CHANGE REPLICATION SOURCE TO/CHANGE MASTER TO
START/STOP/RESET REPLICA/SLAVE
SHOW RELAYLOG EVENTS
FLUSH RELAY LOGS
SHOW REPLICA STATUS/SHOW SLAVE STATUS
Filtered replication
CHANGE REPLICATION FILTER sets one or more replication filtering rules on the replica in the same way as starting the replica mysqld with replication filtering options such as --replicate-do-db or --replicate-wild-ignore-table. CHANGE REPLICATION FILTER requires the SUPER privilege.
Options for CHANGE REPLICATION FILTER
REPLICATE_DO_DB/REPLICATE_IGNORE_DB - include/exclude updates based on database name (equivalent to --replicate-do-db/--replicate-ignore-db)
REPLICATE_DO_TABLE/REPLICATE_IGNORE_TABLE - include/exclude updates based on table name (--replicate-do-table/--replicate-ignore-table)
REPLICATE_WILD_DO_TABLE/REPLICATE_WILD_IGNORE_TABLE - include/exclude updates based on wildcard pattern matching table name (--replicate-wild-do-table/--replicate-wild-ignore-table)
REPLICATE_REWRITE_DB - perform updates on replica after substituting database name (--replicate-rewrite-db)
Names of tables and database not containing any special characters need not be quoted. Values used with REPLICATION_WILD_TABLE and REPLICATION_WILD_IGNORE_TABLE are string expressions, possibly containing wildcard characters and must be quoted.
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1,db2), REPLICATE_IGNORE_DB = (db3);
CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db1.t1) for channel 'channel_1'; - channel specific replication filter
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.old%');
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((db1, db2)); - rewrites statements on db1 on the source to db2 on the replica
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((db1, db2), (db3, db4));
The CHANGE REPLICATION FILTER statement replaces replication filtering rules only for the filter types and replication channels affected by the statement, and leaves other rules and channels unchanged. For unsetting all filters of a given type, the filter's value can be set to an explicitly empty list.
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (), REPLICATE_IGNORE_DB = (); - removes all existing REPLICATE_DO_DB and REPLICATE_IGNORE_DB rules
Monitoring replication
On the master
SHOW SLAVE HOSTS; - list of slaves registered with the master
SHOW MASTER STATUS; - status info about the binary log files of the master
On the slave
SHOW GLOBAL STATUS LIKE 'Slave_running'; - ON if the machine is a slave and both the I/O and SQL threads are running
SHOW SLAVE STATUS \G
Master_Log_File/Read_Master_Log_Pos - master binary log file and position up to which the I/O thread has read
Relay_Master_Log_File/Exec_Master_Log_Pos - master binary log file and position up to which the SQL thread has executed events
Seconds_Behind_Master - time difference between SQL and IO threads
Retrieved_Gtid_Set - set of GTIDs corresponding to all transactions received by the slave. When all relay logs are purged by
RESET SLAVE or CHANGE MASTER TO, the set is cleared. When relay_log_purge = 1, the newest relay log is always kept
and the set is not cleared
Executed_Gtid_Set - set of GTIDs written in the binary log, same as the Executed_Gtid_Set value in SHOW MASTER STATUS
Handling errors
The below statement skips the next N events from the master and is useful for recovering from replication stops caused by a statement.
SET GLOBAL sql_slave_skip_counter = N;
The statement is valid only when the slave threads are not running, else it produces an error. When the skipped event is in the middle of a
transaction event group, the slave continues to skip events until it reaches the end of the group and starts execution from the next
event group.
The sql_slave_skip_counter is not usable when gtid-based replication is in vogue. To skip statements when gtid_mode=ON, empty transactions
need to be injected.
STOP SLAVE;
SET gtid_next='eacc5096-be12-11e6-99cf-e839355808e3:1406'; - transaction to skip
BEGIN; COMMIT;
SET gtid_next='AUTOMATIC';
START SLAVE;
After the START SLAVE the slave checks that the transaction is already in its own binary log and considers it as having been executed.
Stopping/resetting replication
A slave can be removed from the replication setup by issuing a STOP SLAVE and then a RESET SLAVE ALL command.
The RESET SLAVE statement makes the slave forget its replication position in the master's binary log and is meant to be used for a clean
start. It clears the master info and relay log info repositories, deletes all the relay log files and starts a new relay log file. RESET
SLAVE does not change the values of gtid_executed or gtid_purged.
The RESET SLAVE statement requires the RELOAD privilege and the running replication threads must be stopped using STOP SLAVE before
issuing RESET SLAVE.
STOP SLAVE; - stops the slave threads
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
RESET SLAVE;
RESET SLAVE ALL;
RESET SLAVE does not change any replication connection parameters such as master host, port, user or password. When
master_info_repository=TABLE is set on the server, the connection parameters are preserved in the crash-safe table mysql.slave_master_info
as part of the RESET SLAVE operation. They are also retained in memory. In the event of a server crash or deliberate restart after issuing
RESET SLAVE but before issuing START SLAVE, the parameters are retrieved from the table and reused.
When master_info_repository=FILE is set on the server, replication connection parameters are only retained in memory and if the slave
mysqld is restarted after issuing RESET SLAVE deliberately or due to a crash, they are lost. A CHANGE MASTER TO statement must be issued
after the server start to respecify the parameters before issuing START SLAVE.
Connection parameters are reset by RESET SLAVE ALL.
RESET MASTER deletes all binary log files and the binary log index file returning the master to its state before binary logging was
started. RESET MASTER also resets the GTID execution history by clearing and setting the values of the gtid_purged and gtid_executed
system variables to an empty string and also clears the mysql.gtid_executed table.
RESET MASTER;
The sql_log_bin variable can be set to OFF for a session to temporarily disable binary logging while making changes to the master that
should not be replicated to the slave.
SET SESSION sql_log_bin=OFF;
Master/Slave shutdowns during replication
It is safe to shut down a master server and restart it later. When a slave loses its connection to the master, it tries to reconnect
immediately and retries periodically. The default is every 60 seconds and can be changed with the CHANGE MASTER TO statement.
An unclean shutdown/crash on the master can result in the master binary log having a final position less than the most recent position
read by the slave, due to the master binary log file not being flushed. This can cause the slave not to be able to replicate when the
master comes back up. Setting sync_binlog=1 helps to minimize this problem.
Shutting down a slave cleanly is safe because it keeps track of where it left off. Unclean shutdowns might produce problems, especially
if the disk cache was not flushed to disk before the problem occurred.
Switching masters during failover
Slaves can be run with binary logging enabled (the default) and if GTIDs are not used for replication, then also with --skip-log-slave-updates. In this way, the slave is ready to become a master without restarting mysqld. A restart will be required to enable binary logging.
Back
Group replication
MySQL group replication is a MySQL Server plugin that enables creating elastic, highly-available, fault-tolerant replication topologies.
Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively,
groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently, with applications
having to workaround the limitations of such deployments.
In multi-primary mode, all read-write (RW) transactions commit only after they have been approved by the group. When a transaction is ready
to commit at the originating server, the server atomically broadcasts the write values (rows changed) and the correspondent write set
(unique identifiers of the rows that were updated). Then a global total order is established for that transaction. This means that all
servers receive the same set of transactions in the same order. As a consequence, all servers apply the same set of changes in the same
order and remain consistent.
Conflicts are detected by inspecting the write sets of two different and concurrent transactions, in a process called certification. The
resolution procedure states that the transaction that was ordered first commits on all servers, whereas the transaction ordered second
aborts, and is rolled back on the originating server and dropped by the other servers in the group.
MySQL NDB Cluster
MySQL Cluster is a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. MySQL Cluster
integrates the standard MySQL server with an in-memory clustered storage engine called NDB (Network DataBase, also called NDBCLUSTER)
to enable running several computers with MySQL servers and other software in a cluster.
A MySQL Cluster consists of a set of computers, known as hosts, each running one or more processes. These processes, known as nodes, may
include MySQL servers (for access to NDB data), data nodes (for storage of the data), one or more management servers, and possibly other
specialized data access programs.
NDB Cluster process types
Management node - performs administrative tasks such as stopping individual data nodes, querying node and cluster status, and making
backups. Executable: ndb_mgmd
Single-threaded data node - Stores data and executes queries. Executable: ndbd
Multi threaded data node - Stores data and executes queries with multiple worker threads executing in parallel. Executable: ndbmtd
SQL node - MySQL server for executing SQL queries against NDB. Executable: mysqld
API node - A client accessing data in NDB by means of the NDB API or other low-level client API, rather than by using SQL
Management client - Not needed to run the cluster but provides commands for checking cluster status, starting backups and other
functions. Executable: ndb_mgm
Each NDB Cluster host computer must have the correct executable programs installed.
Auto-Sharding
MySQL Cluster uses automatic sharding (partitioning) to scale out read and write operations and can be accessed via SQL and Non-SQL
(NoSQL) APIs.
Unlike other sharded databases, users do not lose the ability to perform JOIN operations, sacrifice ACID-guarantees or referential
integrity (Foreign Keys) when performing queries and transactions across shards.
MySQL Cluster also replicates across data centers for disaster recovery and global scalability. Using its conflict handling
mechanisms, each cluster can be active, accepting updates while maintaining consistency across locations.
Data nodes
A data node (ndbd or ndbmtd process) stores one or more replicas i.e copies of the partitions assigned to the node group of which the node
is a member. Each data node should be located on a separate computer.
Node group
A node group consists of one or more nodes, and stores partitions, or sets of replicas. The number of node groups in an NDB Cluster is not
directly configurable; it is a function of the number of data nodes and of the number of replicas (NoOfReplicas config parameter).
[# of node groups] = [# of data nodes]/ NoOfReplicas
Partition
This is a portion of the data stored by the cluster. Each node is responsible for keeping at least one copy (replica) of any partitions
assigned to it available to the cluster.
Replica
This is a copy of a cluster partition. Each node in a node group stores a replica. The number of replicas is equal to the number of
nodes per node group.
NDB Cluster installation
Initial configuration of NDB Cluster
Each data node or SQL node requires a my.cnf file that provides two pieces of information: a connection string to find the management node and a line telling the MySQL server on this host to enable the NDBCLUSTER storage engine. The configuration file should be located in the /etc directory
$> vi /etc/my.cnf
[mysqld]
ndbcluster # run NDB storage engine
[mysql_cluster]
ndb-connectstring=198.51.100.10 # location of management server
The management node needs a config.ini file telling it how many fragment replicas to maintain, how much memory to allocate for data and indexes on each data node, where to find the data nodes, where to save data to disk on each data node and where to find any SQL nodes.
$> mkdir /var/lib/mysql-cluster
$> cd /var/lib/mysql-cluster
$> vi config.ini
[ndbd default]
# Options affecting ndbd processes on all data nodes
NoOfReplicas=2 # Number of fragment replicas
DataMemory=98M # How much memory to allocate for data storage
[ndb_mgmd]
# Management process options
HostName=198.51.100.10 # management node
DataDir=/var/lib/mysql-cluster # directory for management node log files
[ndbd]
# (one [ndbd] section per data node)
HostName=198.51.100.40 # Hostname or IP address
NodeId=2 # Node ID for this data node
DataDir=/usr/local/mysql/data # directory for this data node's data files
[ndbd]
HostName=198.51.100.50
NodeId=3
DataDir=/usr/local/mysql/data
[mysqld]
# SQL node options
HostName=198.51.100.20 # Hostname or IP address
NodeId=4 # Node ID for this SQL node
[mysqld]
HostName=198.51.100.30
NodeId=5
Initial startup of NDB Cluster
Each cluster node process must be started separately on the host where it resides. The management node should be started first, followed by the data nodes and then finally by any SQL nodes.
On the management host, following command must be issued from the system shell to start the management node process
$> ndb_mgmd --initial -f /var/lib/mysql-cluster/config.ini
The first time that it is started, ndb_mgmd must be told where to find its configuration file, using the -f or --config-file option. This option requires that --initial or --reload to be specified.
On each of the data node hosts, this command must be run to start the ndbd process
$> ndbd
The startup script must be used to start the MySQL server process on the SQL nodes.
The cluster should now be operational and can be tested by invoking the ndb_mgm management node client. The output looks as below
$> ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @198.51.100.40 (Version: 8.0.34-ndb-8.0.34, Nodegroup: 0, *)
id=3 @198.51.100.50 (Version: 8.0.34-ndb-8.0.34, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @198.51.100.10 (Version: 8.0.34-ndb-8.0.34)
[mysqld(API)] 1 node(s)
id=4 @198.51.100.20 (Version: 8.0.34-ndb-8.0.34)
id=4 @198.51.100.30 (Version: 8.0.34-ndb-8.0.34)
The SQL node is referenced here as [mysqld(API)], which reflects the fact that the mysqld process is acting as an NDB Cluster API node. The NDB cluster should now be ready to handle tables and data.
Caching of configuration data
The management server caches the configuration the first time it is started and thereafter the global configuration file is read only when one of the following conditions is true
The --initial option is used to start the management server. The global configuration file is re-read, any existing cache files are deleted and a new cache is created
The --reload option is used while starting which causes the management server to compare its cache with the global configuration file and if they differ, a new cache is created. Any existing configuration cache is preserved, but not used. If the cache and the file contain the same configuration data, then the existing cache is used and no new cache is created
The management server is started using --config-cache=FALSE, which disables --config-cache (enabled by default) and can be used to bypass configuration caching altogether and configuration data is always read from the config.ini file
No configuration cache is found. In this case, the management server reads the global configuration file and creates a cache containing the same data as found in the file
The management server by default creates configuration cache files in a directory named mysql-cluster in the MySQL installation directory. This can be overridden at runtime by starting the management server with the --configdir option. Configuration cache files are binary files named according to the pattern ndb_node_id_config.bin.seq_id, where node_id is the management server's node ID in the cluster, and seq_id is a cache identifier. Cache files are numbered sequentially using seq_id, in the order in which they are created. The management server uses the latest cache file as determined by the seq_id. It is possible to roll back to a previous configuration by deleting later configuration cache files or by renaming an earlier cache file so that it has a higher seq_id.
NDB Cluster management client
In addition to the central configuration file, a cluster may also be controlled through a command-line interface available through the management client ndb_mgm which is the primary administrative interface to a running cluster.
The client has the following commands
CONNECT connection-string
Connects to the management server indicated by the connection string. If the client is already connected to this server, the client reconnects
CREATE NODEGROUP nodeid[, nodeid, ...]
Creates a new NDB Cluster node group and causes data nodes to join it. This command is used after adding new data nodes online to a cluster and causes them to join a new node group and begin participating fully in the cluster. The command takes as its sole parameter a comma-separated list of node IDs of the nodes just added and started that are to join the new node group. The list must contain no duplicate IDs and the number of nodes in the list must be the same as the number of nodes in each node group that is already part of the cluster (each NDB Cluster node group must have the same number of nodes) i.e. if the NDB Cluster consists of 2 node groups having 2 data nodes each, then the new node group must also have 2 data nodes
The node group ID of the new node group created by this command is determined automatically and is always the next highest unused node group ID in the cluster and it is not possible to set it manually.
DROP NODEGROUP nodegroup_id
Drops the NDB Cluster node group with the given nodegroup_id. DROP NODEGROUP acts only to remove the data nodes in the effected node group from that node group. It does not stop data nodes, assign them to a different node group or remove them from the cluster's configuration. A data node that does not belong to a node group is indicated in the output of the SHOW command with no nodegroup in place of the node group ID as below
id=3 @10.100.2.67 (8.0.34-ndb-8.0.34, no nodegroup)
The command works only when all data nodes in the node group to be dropped are completely empty of any table data and table definitions.
HELP
Displays information on all available commands.
QUIT, EXIT
Terminates the management client.
SHOW
Displays basic information about the cluster and cluster nodes. For all nodes, the output includes the node's ID, type, and NDB software version. If the node is connected, its IP address is also shown; otherwise the output shows not connected, accepting connect from ip_address, with any host used for nodes that are permitted to connect from any address.
In addition, for data nodes, the output includes starting if the node has not yet started and shows the node group of which the node is a member. If the data node is acting as the master node, this is indicated with an asterisk (*).
SHUTDOWN
Shuts down all cluster data nodes and management nodes. It does not shut down any SQL nodes or API nodes that are connected to the cluster.
node_id STATUS
Displays status information for the data node identified by node_id (or for all data nodes). Possible node status values include UNKNOWN, NO_CONTACT, NOT_STARTED, STARTING, STARTED, SHUTTING_DOWN and RESTARTING. The output also indicates when the cluster is in single user mode.
Back
InnoDB Cluster
An InnoDB Cluster consists of at least three MySQL Server instances and it provides high-availability and scaling features. InnoDB Cluster uses the following MySQL technologies
MySQL Shell, which is an advanced client and code editor for MySQL. MySQL Shell includes AdminAPI which enables configuring and administering an InnoDB Cluster and removes the need to work directly with Group Replication in InnoDB clusters
Group Replication, which enables a set of MySQL instances to replicate data and provide high-availability. InnoDB Cluster provides an alternative, easy to use programmatic way to work with Group Replication
MySQL Router, a lightweight middleware that provides transparent routing between the application and InnoDB Cluster
Client app MySQL shell(cluster admin) <------------
| | |
| | |
V V | MySQL Admin API
MySQL Router |
| _______________________________|
| |
| |
-----------------------+-----------------------------------------------
| | | MySQL Servers |
| V V |
| -------------------------------- |
| | Primary instance (R/W) | |
| -------------------------------- |
| | | |
| | | |
| | | Group |
| | | replication |
| V V |
| Secondary Secondary |
| instance1(R/O) instance2(R/O) |
| | High-availability cluster
-----------------------------------------------------------------------
The AdminAPI can be accessed through the dba global variable and its associated methods. The dba variable's methods enable deploying,
configuring and administering InnoDB clusters. For e.g, the dba.createCluster() method creates an InnoDB cluster.
MySQL Shell enables connecting to servers over a socket connection, but AdminAPI does not support it and requires TCP connections to a
server instance.
MySQL Router
MySQL Router is part of InnoDB cluster and is a lightweight middleware that provides transparent routing between applications and
back-end MySQL Servers. It can be used for scalability by effectively routing database traffic to appropriate servers. The pluggable
architecture also enables developers to extend MySQL Router for custom use cases.
InnoDB Cluster is tightly integrated with MySQL Router and AdminAPI can be used to work with them together. MySQL Router can automatically configure itself based on an InnoDB Cluster, in a process called bootstrapping, which removes the need to configure routing manually. MySQL Router then transparently connects client applications to the InnoDB Cluster, providing routing and load-balancing for client connections.
The steps for deploying MySQL Router with an InnoDB Cluster after configuring the cluster are:
Install MySQL Router
Bootstrap InnoDB Cluster and test - Bootstrapping automatically configures MySQL Router for an existing InnoDB Cluster by using --bootstrap and other command-line options. During bootstrap, Router connects to the cluster, fetches its metadata and configures itself for use. Bootstrapping is optional
Set up MySQL Router for automatic startup - system needs to be configured to automatically start MySQL Router when the host is rebooted, a process similar to how the MySQL server is configured to start automatically
Back
TOOLS/UTILITIES
MySQL workbench
MySQL workbench provides a graphical integrated tools environment for working with MySQL Servers and databases, including
Database and SQL development - using a visual editor
Database design & modeling - The workbench provides capabilities for creating and manipulating database models including:
Create and manipulate a model graphically
Reverse engineer a live database to a model
Forward engineer a model to a script or live database
Create and edit tables and insert data
Server configuration and administration - Performance statistics can be viewed in a graphical dashboard. Statistics shown include
network status (Incoming and Outgoing network traffic and Client connections), MySQL status (Table open cache efficiency, SQL Statements
Executed and counts per second for SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER and DROP statements) and InnoDB status (Usage, Reads and
Writes).
Database migration - Enables the migration of ODBC-compliant databases to MySQL, using the Migration Wizard. The workbench converts
tables and copies data but will not convert stored procedures, views or triggers.
Data export and import - The Table Data Export and Import Wizard supports import and export operations using CSV and JSON files. SQL Data Export and Import Wizard can be used to either export or import SQL generated from MySQL Workbench or with the mysqldump command. A result set in the visual SQL editor can be exported to file formats including CSV, JSON, HTML and XML.
MySQL Enterprise monitor
MySQL Enterprise Monitor is a web-based application designed to monitor one or more MySQL servers on a network or on a cloud service.
MySQL Enterprise Monitor has the following components
MySQL Enterprise Monitor Agent - communicates with the MySQL instances and hosts that it monitors and collects variables, status and health information according to a defined schedule. The collection data is sent to MySQL Enterprise Service Manager for analysis and presentation. MySQL Enterprise Monitor Agent is typically installed on the same host as the monitored server
MySQL Enterprise Service Manager - analyzes, stores and presents the data collected by the agent. It also contains a built-in agent which is used to monitor the repository
MySQL utilities
MySQL utilities are command-line utilities that are used for maintaining and administering MySQL servers, that can be installed
with the MySQL workbench or as a standalone package. They can be invoked from the command-line or from the workbench using the Tools ->
Start shell for MySQL utilities option.
Admin utilities
mysqlbinlog - displays the events in the binary log files (also relay log files) in text format. The output from mysqlbinlog can be re-executed by using it as input to mysql to redo the statements in the log for recovery operations after an unexpected server exit. To execute the internal-use BINLOG statements used by mysqlbinlog, the user requires the BINLOG_ADMIN privilege or the REPLICATION_APPLIER privilege plus the appropriate privileges to execute each log event.
mysqlbinlog can be used to read binary log files directly and apply them to the local MySQL server and can also read binary logs from a remote server by using the --read-from-remote-server option.
mysqlbinlog binlog.0000003 - display contents
mysqlbinlog --result-file out.txt binlog.0000003 - write output to file
mysqlbinlog -uuser -p -hhost --read-from-remote-server --result-file out.txt binlog.000003 - read log from remote server
Options
| --raw | write events in raw(binary) format to output files |
| --start-position=N | start reading the binary log at the first event having a position equal to or
greater than N. This option applies to the first log file named on the command line. Useful for point-in-time recovery |
| --stop-never |
used with --read-from-remote-server to remain connected with the server. Otherwise mysqlbinlog exits when the last log file has been
transferred from the server. --stop-never implies --to-last-log, so only the first log file to transfer needs to be named on the command
line. --stop-never is commonly used with --raw to make a live binary log backup, but also can be used without --raw to maintain a
continuous text display of log events as the server generates them |
| --stop-position=N | stop reading the binary log at the first event having a position equal to or
greater than N. This option applies to the last log file named on the command line. Useful for point-in-time recovery |
| --to-last-log, -t | do not stop at the end of the requested binary log but continue printing until the end of the
last binary log. This option requires --read-from-remote-server |
| --verbose | reconstruct row events as SQL statements |
The events in the output are preceded by header comments that provide additional information.
# at 141
#100309 9:28:36 server id 123 end_log_pos 245
Query thread_id=3350 exec_time=11 error_code=0
In the first line, the number following at indicates the file offset, or starting position, of the event in the binary log file.
The second line starts with a date and time indicating when the statement started on the server where the event originated. end_log_pos indicates where the next event starts (i.e the end position of the current event + 1).
The output of mysqlbinlog can be piped into the mysql client to execute the events contained in the binary log.
shell> mysqlbinlog binlog.000001 | mysql -u root -p
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p - process multiple files
Another approach to process multiple files is to write all the logs to a single file and then process it
shell> mysqlbinlog binlog.000001 > /tmp/stmts.sql
shell> mysqlbinlog binlog.000002 >> /tmp/stmts.sql
shell> mysql -u root -p -e "source /tmp/stmts.sql"
mysqldbcompare - Compares the objects and data from two databases to find differences
mysqldbcompare --server1=root:passwd@localhost:3306 --server2=root:passwd@localhost:3306 db1:db2 --run-all-tests - compare databases db1 and db2 on the local server
mysqldbcopy - Copies a database on a source server to a database on a destination server
mysqldbcopy --source=root:pass@localhost:3310 --destination=root:pass@localhost:3310 db1:db1_copy - copy database db1 to db1_copy on the same server
mysqldbexport - Exports metadata (object definitions) or data or both from one or more databases. mysqldbexport differs from mysqldump in that it can produce output in a variety of formats to make data extraction/transport much easier. It can export data in the format most suitable to an external tool or another MySQL server without the need to reformat the data
mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data
General utilities
mysqlfrm - reads .frm files and produces equivalent create statements from the table definition found in the file
mysqlfrm --diagnostic "C:\MySQL\DATA\tbl.frm"
perror - displays the error message for MySQL or OS error codes
shell> perror errorcode
MySQL Shell
MySQL Shell is an advanced client and code editor for MySQL Server. In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. MySQL Shell is a component that can be installed separately.
MySQL Shell includes the following APIs implemented in JavaScript and Python which can be used to develop code that interacts with MySQL.
The X DevAPI enables developers to work with both relational and document data when MySQL Shell is connected to a MySQL server using the X Protocol. This enables MySQL to be used as a Document Store, sometimes referred to as 'using NoSQL'.
The AdminAPI enables database administrators to work with InnoDB Cluster, which provides an integrated solution for high availability and scalability using InnoDB based MySQL databases. The AdminAPI also includes support for InnoDB ReplicaSet, which enables administering a set of MySQL instances running asynchronous GTID-based replication in a similar way to InnoDB Cluster. Additionally, the AdminAPI makes administration of MySQL Router easier, including integration with both InnoDB Cluster and InnoDB ReplicaSet.
MySQL Shell is available in two editions, the Community Edition and the Commercial Edition.
Percona Toolkit
Percona toolkit is a collection of advanced open source command-line tools that can be used for MySql administrative tasks.
pt-archiver - archives table rows into another table or a file
pt-archiver --source L=ON,h=host,D=db1,t=t1,i=ix_t1_date1,u=user,p=pwd --dest h=host,D=db2,u=user,p=pwd
--where "date1 < DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 3 MONTH" --charset utf8 --limit 2000 --commit-each
--progress 10000 --bulk-insert --bulk-delete --statistics
pt-diskstats - prints disk I/O statistics for GNU/Linux. It is similar to iostat, but it is interactive and more detailed
pt-duplicate-key-checker - finds duplicate indexes and foreign keys
pt-mysql-summary - summarizes the status and configuration of MySQL servers
pt-slave-restart - watch and restart MySQL replication after errors
Back
Maintained by: VINCENT KANDASAMY, Database Architect/Administrator (kandasf@hotmail.com)
Last updated: Aug 17, 17:31
