3.Skill Check: Understanding MySQL Architecture

How do you set the number of InnoDB buffer pool instances to 12?

Execute SET GLOBAL innodb_buffer_pool_size=12000 1024 1024;

Execute SET GLOBAL innodb_buffer_pool_instances=12;

Add innodb_buffer_pool_instances=12 to /etc/my.cnf

Add innodb_buffer_pool_size=12G to /etc/my.cnf

Which are true about the MyISAM storage engine? (Choose three)

It supports table-level locking

It supports spatial data types and indexes

It supports storing row data and indexes in memory

It supports all data types except spatial data types

It supports FULLTEXT indexes

Which storage engine supports INSERT and SELECT but not DELETE, REPLACE, or UPDATE commands?

ARCHIVE

MyISAM

BLACKHOLE

MEMORY

You must create a table with these attributes: 1. Table name: sqltab1 2. Stored in the general tablespace Which command will do this?

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4));

CREATE TABLESPACE myts ADD DATAFILE ‘myts_data1.ibd’;

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4)) DATA DIRECTORY=’/datadir2′;

CREATE TABLE sqltab1(a INT PRIMARY KEY) TABLESPACE=myts;

You must create a table with these attributes: 1. Table name: sqltab1 2. A single integer column d 3. Stored in its own default tablespace in the /tablespaces directory Which command will do this?

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=general;

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=external;

mysql> CREATE TABLESPACE external ADD DATAFILE ‘/tablespaces/sqltab1.ibd’;

mysql> CREATE TABLE sqltab1 (d INT) DATA DIRECTORY=’/tablespaces’;

Which features are supported by InnoDB? (Choose two)

Hash indexes

Clustered indexes

B-tree indexes

Cluster database support

T-tree indexes

Which MySQL storage engines are transactional and support foreign keys? (Choose two)

BLACKHOLE

NDBCLUSTER

MERGE

InnoDB

MyISAM

====

3.Skill Check: Understanding MySQL Architecture

How do you set the number of InnoDB buffer pool instances to 12?

Execute SET GLOBAL innodb_buffer_pool_size=12000 1024 1024;

Execute SET GLOBAL innodb_buffer_pool_instances=12;

Add innodb_buffer_pool_instances=12 to /etc/my.cnf

Add innodb_buffer_pool_size=12G to /etc/my.cnf

答案:
Add innodb_buffer_pool_instances=12 to /etc/my.cnf

Which are true about the MyISAM storage engine? (Choose three)

It supports table-level locking

It supports spatial data types and indexes

It supports storing row data and indexes in memory

It supports all data types except spatial data types

It supports FULLTEXT indexes

答案:
It supports table-level locking
It supports spatial data types and indexes
It supports FULLTEXT indexes

说明:
MyISAM Storage Engine
• Is used in many legacy systems
– Was the default storage engine before MySQL 5.5
• Is fast and simple, but subject to table corruption if server crashes
– Use REPAIR TABLE to recover corrupted MyISAM tables.
• Supports FULLTEXT indexes
• Supports spatial data types and indexes
• Supports table-level locking
• Supports raw table-level backup and recovery because of the simple file format
• No transactional support
• No support for table partitioning in MySQL 8.0 as compared to MySQL 5.7.

说明:
MySQL RPM Installation Process
• The RPM installation performs the following tasks:
– Extracts RPM files to their default locations
– Registers SysV init or systemd startup scripts
– Sets up the mysql user and group in the operating system
— The MySQL server process runs as the mysql user.
• When you start the service for the first time using service mysqld
start or systemctl start mysqld, MySQL:
– Creates the data directory and the default my.cnf file
— These files are owned by the mysql user and group.
– Creates the default root@localhost account
– Sets up a random temporary password for the root account and
writes that password to the error log file (/var/log/mysqld.log)
— You must change the password before you can use MySQL.

Which storage engine supports INSERT and SELECT but not DELETE, REPLACE, or UPDATE commands?

ARCHIVE

MyISAM

BLACKHOLE

MEMORY

答案:
ARCHIVE

说明:
ARCHIVE Storage Engine
The ARCHIVE storage engine is used for storing large volumes of data in a compressed
format, allowing for a very small footprint. It has these primary characteristics:
• Does not support indexes
• Supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE
• Supports ORDER BY operations and BLOB columns
• Accepts all data types except spatial data types
• Uses row-level locking
• Supports AUTO_INCREMENT columns
• Disabled by default, need to be enabled to use

You must create a table with these attributes: 1. Table name: sqltab1 2. Stored in the general tablespace Which command will do this?

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4));

CREATE TABLESPACE myts ADD DATAFILE ‘myts_data1.ibd’;

CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4)) DATA DIRECTORY=’/datadir2′;

CREATE TABLE sqltab1(a INT PRIMARY KEY) TABLESPACE=myts;

答案:
CREATE TABLE sqltab1(a INT PRIMARY KEY, b CHAR(4)) TABLESPACE=myts;

You must create a table with these attributes: 1. Table name: sqltab1 2. A single integer column d 3. Stored in its own default tablespace in the /tablespaces directory Which command will do this?

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=general;

mysql> CREATE TABLE sqltab1 (d int) TABLESPACE=external;

mysql> CREATE TABLESPACE external ADD DATAFILE ‘/tablespaces/sqltab1.ibd’;

mysql> CREATE TABLE sqltab1 (d INT) DATA DIRECTORY=’/tablespaces’;
答案:
mysql> CREATE TABLE sqltab1 (d INT) DATA DIRECTORY=’/tablespaces’;

Which features are supported by InnoDB? (Choose two)

Hash indexes

Clustered indexes

B-tree indexes

Cluster database support

T-tree indexes

答案:

Clustered indexes
B-tree indexes

Which MySQL storage engines are transactional and support foreign keys? (Choose two)

BLACKHOLE

NDBCLUSTER

MERGE

InnoDB

MyISAM
答案:
InnoDB
NDBCLUSTER

说明:
The two MySQL storage engines that are transactional and support foreign keys are:
InnoDB
Transactional: Supports ACID-compliant transactions (COMMIT, ROLLBACK).
Foreign Keys: Enforces referential integrity with foreign key constraints.

NDBCLUSTER (MySQL Cluster)
Transactional: Provides distributed transactions across nodes.
Foreign Keys: Fully supports foreign keys in a clustered environment.

BLACKHOLE: Discards writes (non-transactional), no foreign keys.
MERGE: Combines MyISAM tables (non-transactional), no foreign keys.
MyISAM: Non-transactional, no foreign key support (only table-level locking).

The post MySQL 8.0 for Database Administrators OCP 课程习题3 first appeared on C1G军火库.