MySQL Architecture

Question 1

Which of the following is true, regarding The buffer pool:

a) The buffer pool is an area in main memory where InnoDB caches table data as it is accessed
b) The buffer pool is an area in main memory where InnoDB caches index data as it is accessed
c) The buffer pool permits frequently used data to be accessed directly from memory
d) All of the above
e) none of the above

Answer 1

Answer: D

all of the above.

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

Question 2

Buffers specific to InnoDB are configured using the following parameters

a) key_buffer_size
b) innodb_log_buffer_size
c) innodb_buffer_pool_size
d) both a and b
e) both b and c
f) all three a, b and c

Answer 2

Answer: e

key_buffer_size is for MyISAM , not InnoDB.

Buffers specific to InnoDB are configured using the following parameters:

innodb_buffer_pool_size defines size of the buffer pool, which is the memory area that holds cached data for InnoDB tables, indexes, and other auxiliary buffers. The size of the buffer pool is important for system performance,
and it is typically recommended that innodb_buffer_pool_size is configured to 50 to 75 percent of system memory. The default buffer pool size is 128MB.

On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.
The number of buffer pool instances is controlled by the by innodb_buffer_pool_instances option. By default, InnoDB creates one buffer pool instance. The number of buffer pool instances can be configured at startup.

innodb_log_buffer_size defines the size of the buffer that InnoDB uses to write to the log files on disk. The default size is 16MB. A large log buffer enables large transactions to run without writing the log to disk before the transactions commit. If you have transactions that update, insert, or delete many rows, you might consider increasing the size of the log buffer to save disk I/O. innodb_log_buffer_size can be configured at startup.

Question 3

Memory area that holds data to be written to the log files on disk.

a) change buffer
b) log buffer
c) innodb_buffer_pool_size
d) buffer_pool

Answer 3

Answer: b

The log buffer is the memory area that holds data to be written to the log files on disk. Log buffer size is defined by the innodb_log_buffer_size variable. The default size is 16MB. The contents of the log buffer are periodically flushed to disk. A large log buffer enables large transactions to run without the need to write redo log data to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.

Question 4

A special data structure used for caching changes in secondary index pages that are not in the buffer pool.

a) change buffer
b) clustered indexes
c) log buffer
d) buffer pool

Answer 4

Answer: a

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

Question 5

Which of the following is false, regarding the change buffer

a) Change buffering is supported for a secondary index if the index contains a descending index column
b) the change buffer occupies part of the buffer pool
c) On disk, the change buffer is part of the system tablespace,
d) Change buffering is not supported if the primary key includes a descending index column

Answer 5

Answer : a

Change buffering is not supported for a secondary index if the index contains a descending index column or if the primary key includes a descending index column.

In memory, the change buffer occupies part of the buffer pool. On disk, the change buffer is part of the system tablespace, where index changes are buffered when the database server is shut down.

Question 6

Each database has a data directory located under database directory.

a) True
b) False

Answer 6

Answer: a
Explanation: In MySQL, each database has a database directory that is located under the data directory. The tables, views and triggers within a database correspond to files in the database directory.

Question 7

In Mysql 8.0.x

In the database directory (datadir) of the database each view and trigger object creates how many files?

a) 0
b) 1
c) 2
d) 3

Answer 7

Answer: a

in older versions of Mysql there was a .frm file. in MySQL8 we do not have FRM files, they were dropped

Oracle has moved the FRM information—and more—to what is called Serialized Dictionary Information (SDI), the SDI is written INSIDE the ibd file, and represents the redundant copy of the information contained in the data dictionary.

Question 8

What is the maximum number of characters allowed for a database name to have?

a) 16
b) 32
c) 64
d) 128

Answer 8

Answer: c
Explanation: MySQL allows the database names and the table names to be a maximum of 64 characters long. The length of these names is also bound by the length allowed by the operating system on the machine.

Question 9

What is the maximum number of characters allowed for a User-Defined Variable to have?

a) 16
b) 32
c) 64
d) 128

Answer 9

Answer: c
Explanation: MySQL allows theUser-Defined Variable to be a maximum of 64 characters long. The length of these names is also bound by the length allowed by the operating system on the machine.

Question 10

In MySQL, identifier names can start with a digit.

a) True
b) False

Answer 10

Answer: a
Explanation: Identifiers are allowed to start with any legal character. So it may begin with a digit. The special characters in the identifier characters consist of the ‘_’ and ‘$’. The other characters are the alphanumeric characters.

Question 11

To verify plugin installation use the following command in the mysql shell?

a) SHOW PLUGINS
b) SHOW VARIABLES ‘PLUGINS’;
c) SHOW GLOBAL VARIABLES ‘PLUGINS’;
d) SELECT plugins();

Answer 11

Answer: a

mysql> show plugins;
+———————————+———-+——————–+———+————-+
| Name | Status | Type | Library | License |
+———————————+———-+——————–+———+————-+
| binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | PROPRIETARY |
| daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | PROPRIETARY |
| CSV | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY |

Question 12

During the installation of MySQL there are some user accounts created.

a) True
b) False

Answer 12

Answer: a

One part of the MySQL installation process is data directory initialization . During data directory initialization, MySQL creates user accounts that should be considered reserved:

‘root’@’localhost: Used for administrative purposes. This account has all privileges, is a system account, and can perform any operation.

Strictly speaking, this account name is not reserved, in the sense that some installations rename the root account to something else to avoid exposing a highly privileged account with a well-known name.

‘mysql.sys’@’localhost’: Used as the DEFINER for sys schema objects. Use of the mysql.sys account avoids problems that occur if a DBA renames or removes the root account. This account is locked so that it cannot be used for client connections.

‘mysql.session’@’localhost’: Used internally by plugins to access the server. This account is locked so that it cannot be used for client connections. The account is a system account.

‘mysql.infoschema’@’localhost’: Used as the DEFINER for INFORMATION_SCHEMA views. Use of the mysql.infoschema account avoids problems that occur if a DBA renames or removes the root account. This account is locked so that it cannot be used for client connections.

Question 13

Which user account is used internally by plugins to access the server. This account is locked so that it cannot be used for client connections. The account is a system account.

a) ‘mysql.infoschema’@’localhost
b) ‘mysql.session’@’localhost’
c) ‘mysql.sys’@’localhost’
d) ‘mysql.plugins’

Answer 13

Answer: b

‘mysql.session’@’localhost’: Used internally by plugins to access the server. This account is locked so that it cannot be used for client connections. The account is a system account.

Question 14

Which of the following is NOT a Reserved Account created during the MySQL installation and initialization?

a) ‘mysql.infoschema’@’localhost
b) ‘root’@’localhost
c) ‘mysql.session’@’localhost’
d) ‘mysql.sys’@’localhost’

Answer 14

One part of the MySQL installation process is data directory initialization. During data directory initialization, MySQL creates user accounts that should be considered reserved:

‘root’@’localhost: Used for administrative purposes. This account has all privileges, is a system account, and can perform any operation.

Strictly speaking, this account name is not reserved, in the sense that some installations rename the root account to something else to avoid exposing a highly privileged account with a well-known name.

Question 15

In MySQL 8.0, the default authentication plugin is:

a) mysql_native_password
b) mysql_native_strict
c) mysql_sha2_password
d caching_sha2_password

Answer 15

Answer : d

In MySQL 8.0, the default authentication plugin has changed from mysql_native_password to caching_sha2_password, and the ‘root’@’localhost’ administrative account uses caching_sha2_password by default.

Question 16

What does the following command do:

mysqld –initialize –user=mysql

a) creates a user name mysql and grants ALL privledges
b) starts up the database as user mysql
c) logs into the database as user mysql
d) sets installed data directory files with owner mysql and ensures server has read and write access

Answer 16

Answer: D
after install use this command to initialize the data directory, including the mysql schema containing the initial MySQL grant tables that determine how users are permitted to connect to the server.

On Unix and Unix-like systems, it is important for the database directories and files to be owned by the mysql login account so that the server has read and write access to them when you run it later. To ensure this, start mysqld from the system root account and include the –user option as shown here:

bin/mysqld –initialize –user=mysql

Question 17

You issue the function to check for strong password and the following occurs:
mysql> SELECT VALIDATE_PASSWORD_STRENGTH(‘N0Tweak$@123!’);
+———————————————-+
| VALIDATE_PASSWORD_STRENGTH(‘N0Tweak$@123!’) |
+———————————————-+
| 0 |
+———————————————-+
1 row in set (0.00 sec)

What does this mean?

a) validate_password is not installed
b) the function needs to be run as root user
c) the password policy set is 0
d) The password checked is very strong

Answer 17

Answer: a

If validate_password is not installed, the validate_password.xxx system variables are not available, passwords in statements are not checked, and the VALIDATE_PASSWORD_STRENGTH() function always returns 0. For example, without the plugin installed, accounts can be assigned passwords shorter than 8 characters, or no password at all.

Assuming that validate_password is installed, it implements three levels of password checking: LOW, MEDIUM, and STRONG. The default is MEDIUM;

Question 18

To load the authentication plugin at server startup, which server my.cnf file option is used to name the library file.

a) –plugin-load-add
b) –plugin-dir
c) –basedir
d) –datadir

Answer 18

Answer: a

[mysqld]
plugin-load-add=authentication_pam.so

Question 19

Which is the default MySQL 8.0 plug-in directory after installation on Linux?

a) /etc/mysql/plugin
b) /etc/plugin
c) /var/lib/mysql/plugin
d) /usr/lib/mysql/plugin/

Answer 19

Answer: d

/usr/lib/mysql/plugin/

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

mysql> show variables like ‘%plugin%’;
+———————————————–+————————+
| Variable_name | Value |
+———————————————–+————————+
| default_authentication_plugin | caching_sha2_password |
| plugin_dir | /usr/lib/mysql/plugin/ |

Question 20

Pick all of the following that will store values in the plugin and authentication_string columns of the mysql.user system table?

a) mysql_no_login
b) caching_sha2_password
c) sha256_password
d) mysql_native_password
e) none of these

Answer 20

Answer: b,c,d

mysql_no_login authentication method allows users to
be set up with no password, which basically means there in no authentication on the user.

Therefore, the mysql.user table has no value for the column authentication_string.

Question 21

The following commands are entered into the mysql by the root user:
CREATE DATABASE nologindb;
CREATE USER 'nologin'@'localhost'
  IDENTIFIED WITH mysql_no_login;
GRANT ALL ON nologindb.*
  TO 'nologin'@'localhost';
GRANT SELECT ON mysql.user
  TO 'nologin'@'localhost';
CREATE DEFINER = 'nologin'@'localhost'
  SQL SECURITY DEFINER
  VIEW nologindb.myview
  AS SELECT User, Host FROM mysql.user;

What occurs when os user root then tries to run the following from the os command line from the localhost:

root>mysqladmin -unologin ping

a) mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘nologin’@’localhost’ (using password: NO)’
b) mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
c) Enter password:
d) mysqld is alive

Answer 21

Answer: a

admin1@ubuntuv2:~$ mysqladmin -unologin -h localhost ping

mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘nologin’@’localhost’ (using password: NO)’

An account that authenticates using mysql_no_login may be used as the DEFINER for stored program and view objects. If such an object definition also includes SQL SECURITY DEFINER, it executes with that account’s privileges.

This enables clients to access MySQL through the proxy account (nologin) but not to bypass the proxy mechanism by connecting directly as the proxied user (nologin).