Cert5

Question 1

What is a thread

Answer 1

A unit of processing that is typically more lightweight than a process, allowing for greater concurrency.

The server is multi-threaded, and a thread is like a small process running inside the server. For each client that connects, the server allocates a thread to it to handle the connection. For performance reasons, the server maintains a small cache of thread handlers. If the cache is not full when a client disconnects, the thread is placed in the cache for later reuse. If the cache is not empty when a client connects, a thread from the cache is reused to handle the connection. Thread handler reuse avoids the overhead of repeated handler setup and teardown.

Question 2

What are the ibdata files? idbdata1, ibdata2, etc

Answer 2

A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace. For information about the structures and data that reside in the system tablespace ibdata files, see Section 14.6.3.1, “The System Tablespace”.

Growth of the ibdata files is influenced by the innodb_autoextend_increment configuration option.

Question 3

What is MySQL Administrator

Answer 3

Legacy GUI based client app for administoring MySQL. Replaced by MySQL Workbench

Question 4

What is the system table space:

Answer 4

The system tablespace is the storage area for the InnoDB data dictionary, the doublewrite buffer, the change buffer, and undo logs. It may also contain table and index data if tables are created in the system tablespace rather than file-per-table tablespaces.

The system tablespace can have one or more data files. By default, a single system tablespace data file, named ibdata1, is created in the data directory. The size and number of system tablespace data files is defined by the innodb_data_file_path startup option. For configuration information, see System Tablespace Data File Configuration.

Question 5

SHOW status;

Answer 5

SHOW STATUS displays server status variables. The status indicators enable you to check the runtime state of caches, which can be useful for assessing the effectiveness with which they are being used and for determining whether you would be better off using larger (or in some cases smaller) buffers.

Question 6

what is a tablespace

Answer 6

A data file that can hold data for one or more InnoDB tables and associated indexes.

The system tablespace contains the InnoDB data dictionary, and prior to MySQL 5.6 holds all other InnoDB tables by default.
The innodb_file_per_table option, enabled by default in MySQL 5.6 and higher, allows tables to be created in their own tablespaces. File-per-table tablespaces support features such as efficient storage of off-page columns, table compression, and transportable tablespaces. See Section 14.6.3.2, “File-Per-Table Tablespaces” for details.
InnoDB introduced general tablespaces in MySQL 5.7.6. General tablespaces are shared tablespaces created using CREATE TABLESPACE syntax. They can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.
MySQL NDB Cluster also groups its tables into tablespaces. See Section 18.5.10.1, “NDB Cluster Disk Data Objects” for details.

Question 7

what is the upper tier for processing SQL calls?

Answer 7

The upper tier includes the SQL parser and optimizer. The server parses each statement to see what kind of request it is, then uses its optimizer to determine how most efficiently to execute the statement. However, this tier does not interact directly with tables named by the statement.

Question 8

What is ib_logfile? ib_logfile0, ib_logfile1, etc

Answer 8

A set of files, typically named ib_logfile0 and ib_logfile1, that form the redo log. Also sometimes referred to as the log group. These files record statements that attempt to change data in InnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.

This data cannot be used for manual recovery; for that type of operation, use the binary log.

See Also binary log, log group, redo log.

Question 9

what files have .idb extension?

Answer 9

The data file for file-per-table tablespaces and general tablespaces. File-per-table tablespace .ibd files contain a single table and associated index data. General tablespace .ibd files may contain table and index data for multiple tables. General tablespaces were introduced in MySQL 5.7.6.

The .ibd file extension does not apply to the system tablespace, which consists of one or more ibdata files.

If a file-per-table tablespace or general tablespace is created with the DATA DIRECTORY = clause, the .ibd file is located at the specified path, outside the normal data directory, and is pointed to by a .isl file.

When a .ibd file is included in a compressed backup by the MySQL Enterprise Backup product, the compressed equivalent is a .ibz file.

See Also database, file-per-table, general tablespace, ibdata file, .ibz file, innodb_file_per_table, .isl file, MySQL Enterprise Backup, system tablespace.

Question 10

Where is the data directory?

Answer 10

/var/lib/mysql

Question 11

What files have .frm extension?

Answer 11

**Removed in 8.0. Info is now stored in the dictionary file**

A file containing the metadata, such as the table definition, of a MySQL table.

For backups, you must always keep the full set of .frm files along with the backup data to be able to restore tables that are altered or dropped after the backup.

Although each InnoDB table has a .frm file, InnoDB maintains its own table metadata in the system tablespace.

.frm files are backed up by the MySQL Enterprise Backup product. These files must not be modified by an ALTER TABLE operation while the backup is taking place, which is why backups that include non-InnoDB tables perform a FLUSH TABLES WITH READ LOCK operation to freeze such activity while backing up .frm files. Restoring a backup can result in .frm files being created, changed, or removed to match the state of the database at the time of the backup.

See Also data dictionary, MySQL Enterprise Backup, system tablespace.

Question 12

show variables

Answer 12

SHOW VARIABLES displays server system variables so that you can see how the server is configured.

Question 13

What are supported database API?

Answer 13

What are supported database API?

Question 14

What are non-client utility programs?

Answer 14

These are programs that don’t connect to the server but act directly on data files

Question 15

What is the lower tier for processing SQL calls?

Answer 15

The lower tier comprises a set of storage engines. The server uses a modular architecture: Each storage engine is a software module to be used for managing tables of a particular type. The storage engine associated with a table directly accesses it to store or retrieve data. MyISAM, MEMORY, and InnoDB are some of the available engines. The use of this modular approach allows storage engines to be easily selected for inclusion in the server at configuration time. New engines also can be added relatively easily.

Question 16

What are the 3 categories of a MySQL intallation?

Answer 16

MySQL Server (mysqld), client programs, non-client utility programs

Question 17

what are the two tiers for processing SQL statements?

Answer 17

The upper tier includes the SQL parser and optimizer.
The lower tier comprises a set of storage engines

Question 18

What is General Tablespace?

Answer 18

A shared InnoDB tablespace created using CREATE TABLESPACE syntax. General tablespaces can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats. General tablespaces were introduced in MySQL 5.7.6.

Tables are added to a general tablespace using CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name syntax.

Contrast with system tablespace and file-per-table tablespace.

For more information, see General Tablespaces.

See Also file-per-table, system tablespace, table, tablespace.

Question 19

What are MySQL communication protocols?

Answer 19

–protocol Value Transport Protocol Used Applicable Platforms
TCP TCP/IP All
SOCKET Unix socket file Unix and Unix-like systems
PIPE Named pipe Windows
MEMORY Shared memory Windows

Question 20

These are programs that don’t connect to the server but act directly on data files

Answer 20

29. 1 MySQL Connector/C++
30. 2 MySQL Connector/J
31. 3 MySQL Connector/NET
32. 4 MySQL Connector/ODBC
33. 5 MySQL Connector/Python
34. 6 MySQL Connector/Node.js

Question 21

what architecture does MySQL use?

Answer 21

client-server

Question 22

How does MySQL use memory?

Answer 22

1. thread handles
2. data buffers
3. grant table buffer
4. key buffer for myisam tables - data is cached by OS
5. table cache holds descriptors for open tables
6. Query cache
7. Innodb buffer
8. memory engine
9. temp tables
10. client connections

Question 23

What is MySQL Query Browser

Answer 23

Legacy GUI based client app for analysing data from MySQL. Replaced by MySQL Workbench

Question 24

why does the server create/use threads?

Answer 24

1. each connection is allocated a thread
2. other types of storage engines may issue threads
3. threads for replication

Question 25

Where is data and indices stored?

Answer 25

Data and indices are stored in files with extension .idb that are located in the database director in the data directory.

Question 27

What are the ib_logfile files?

Answer 27

A set of files, typically named ib_logfile0 and ib_logfile1, that form the redo log. Also sometimes referred to as the log group.

These files record statements that attempt to change data in InnoDB tables.

These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.

Question 28

What is the redo log?

Answer 28

A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.

The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.3.

The disk layout of the redo log is influenced by the configuration options innodb_log_file_size, innodb_log_group_home_dir, and (rarely) innodb_log_files_in_group. The performance of redo log operations is also affected by the log buffer, which is controlled by the innodb_log_buffer_size configuration option.

Question 29

What is the undo log?

Answer 29

A storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area.

In MySQL 5.6 and MySQL 5.7, you can use the innodb_undo_tablespaces variable have undo logs reside in undo tablespaces, which can be placed on another storage device such as an SSD. In MySQL 8.0, undo logs reside in two default undo tablespaces that are created when MySQL is initialized, and additional undo tablespaces can be created using CREATE UNDO TABLESPACE syntax.

The undo log is split into separate portions, the insert undo buffer and the update undo buffer.

Question 30

What is a rollback segment?

Answer 30

The storage area containing the undo logs. Rollback segments have traditionally resided in the system tablespace. As of MySQL 5.6, rollback segments can reside in undo tablespaces. As of MySQL 5.7, rollback segments are also allocated to the global temporary tablespace.

Question 31

What is the undo tablespace?

Answer 31

An undo tablespace contains undo logs. Undo logs exist within undo log segments, which are contained within rollback segments. Rollback segments have traditionally resided in the system tablespace. As of MySQL 5.6, rollback segments can reside in undo tablespaces. In MySQL 5.6 and MySQL 5.7, the number of undo tablespaces is controlled by the innodb_undo_tablespaces configuration option. In MySQL 8.0, two default undo tablespaces are created when the MySQL instance is initialized, and additional undo tablespaces can be created using CREATE UNDO TABLESPACE syntax.

Question 32

What is a undo log segment

Answer 32

A collection of undo logs. Undo log segments exists within rollback segments. An undo log segment might contain undo logs from multiple transactions. An undo log segment can only be used by one transaction at a time but can be reused after it is released at transaction commit or rollback. May also be referred to as an “undo segment”.

Question 34

what are the server logs?

Answer 34

Error log

General Query log

Binary log

slow query log

Question 35

What is the error log?

Answer 35

A type of log showing information about MySQL startup and critical runtime errors and crash information.

Question 36

What is the general query log?

Answer 36

A type of log used for diagnosis and troubleshooting of SQL statements processed by the MySQL server. Can be stored in a file or in a database table. You must enable this feature through the general_log configuration option to use it. You can disable it for a specific connection through the sql_log_off configuration option.

Records a broader range of queries than the slow query log. Unlike the binary log, which is used for replication, the general query log contains SELECT statements and does not maintain strict ordering. For more information, see Section 5.4.3, “The General Query Log”.

Question 37

What is the binary log?

Answer 37

A file containing a record of all statements or row changes that attempt to change table data. The contents of the binary log can be replayed to bring replicas up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although Oracle recommends always enabling it if you use replication or perform backups.

You can examine the contents of the binary log, or replay it during replication or recovery, by using the mysqlbinlog command. For full information about the binary log, see Section 5.4.4, “The Binary Log”. For MySQL configuration options related to the binary log, see Section 17.1.6.4, “Binary Logging Options and Variables”.

For the MySQL Enterprise Backup product, the file name of the binary log and the current position within the file are important details. To record this information for the source when taking a backup in a replication context, you can specify the –slave-info option.

Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.

Question 38

what is slow query log?

Answer 38

A type of log used for performance tuning of SQL statements processed by the MySQL server. The log information is stored in a file. You must enable this feature to use it. You control which categories of “slow” SQL statements are logged. For more information, see Section 5.4.5, “The Slow Query Log”.