Ch. 4- MySQL

Audit

This MySQL Enterprise component keeps track of all database changes. For each change, Audit tracks the time of change and who made the change. Audit supports government and business audit requirements for sensitive databases such as financial, medical, and defense.

query parser

This checks each query for syntax errors and converts valid queries to an internal representation.

Architecture

This describes the components of a computer system and the relationships between components.

MySQL Community MySQL Server

This is a free edition. It includes a complete set of database services and tools, and is suitable for non-commercial applications such as education.

MySQL Workbench

This is a graphical user interface installed with MySQL Server and allows developers to execute SQL commands using an editor. When it's started, the user can connect to MySQL Server running on the local machine or on the network.

MySQL

This is a leading relational database system sponsored by Oracle. It's relatively easy to install and use, yet has many advanced capabilities. It runs on all major operating systems, including Linux, Unix, Mac OS, and Windows

connection

This is a link between tools and the query processor. Each one specifies a database name, server address, logon name, and password.

MySQL Enterprise

This is a paid edition for managing commercial databases. It includes MySQL Server and additional administrative applications.

buffer

Data blocks are retained in this area of main memory

file system

This MySQL layer contains system and user data, such as log files, tables, and indexes.

4 layers

How many layers are MySQL components organized?

B. False A graphical interface has icons or clickable text that can be selected, often by a mouse, to execute commands. In contrast, the MySQL Command-Line Client requires typing all commands. In the animation above, the user types a USE database command, then a SELECT command.

The MySQL Command-Line Client provides a graphical interface for interacting with the database server. A. True B. False

B. False The database server runs as a service in the background and does not normally need to be started or stopped manually.

The database server must be manually started each time the user runs the MySQL Command-Line Client. A. True B. False

A. True Clicking the lightning bolt or typing Ctrl+Shift+Enter on Windows, Command+Shift+Enter on a Mac, executes all the SQL statements.

The SQL statements in the SQL query panel are not executed until the lightning bolt is clicked. A. True B. False

A. True The user is prompted to enter a root account password during the installation. The password must be supplied to interact with the MySQL server.

The root account password is set when installing MySQL. A. True B. False

Monitor

This MySQL Enterprise component collects and displays information on CPU, memory, and index utilization, as well as queries and results. Database administrators use Enterprise Monitor to manage and tune large databases with many users.

storage engine

This MySQL layer executes instructions, manages indexes, and interacts with the file system. Some storage engines support database transactions, described elsewhere in this material.

Tools

This MySQL layer interacts directly with database users and administrators, and send queries to the query processor.

query processor

This MySQL layer manages connections from multiple users and compiles queries into low-level instructions for the storage engine.

Least Recently Used (LRU)

This algorithm tracks the time each block was last used and, when space is needed, discards 'stale' blocks. If data in a block has been updated, discarded blocks are first saved on disk.

Tools query processor storage engine file system

What are the four layers of the MySQL architecture?

execution plan

a detailed, low-level sequence of steps that specify exactly how to process a query.

catalog or data dictionary

a directory of tables, columns, keys, indexes, and other objects in a relational database.

MySQL Command-Line Client

a text interface included in the MySQL Server download.

A. True MySQL Workbench can also automatically generate some SQL statements, to reduce the amount of typing required.

MySQL Workbench and MySQL Command-Line Client both allow the user to type SQL statements. A. True B. False

query optimizer

This reads the internal representation, generates alternative execution plans, estimates execution times, and selects the fastest plan. Estimates are based on heuristics and statistics about data, like the number of rows in each table and the number of values in each column. These statistics are maintained in the data dictionary, described below.

buffer manager

This retains data blocks from the file system for possible reuse.

cache manager

This stores reusable information in main memory. Ex: It retains execution plans for queries that are submitted multiple times. If data used in repeated queries does not change, it may also save query results.