18ArchRelDBServer

Architecture of Relational Database Server

Outline

Client-Server Architecture
Basic Operations on Database Server
Initialization Variables
Post Installation
Databases

The present slide shows a simplified architecture and data flow within a typical relational database server.

A client server architecture means, that a number of clients are connected to a single database server, over a local or wide-area network.

The connections are handled by the top most layer of software implementing a database server. A single connection is implemented as a single thread of processing within an operating system.

The interactions between the clients and a database server are handled in the following way. Assume, that a client submits a request expressed as an SQL statement, to access some data items in a read or write mode. A request is processed by a parser, that verifies the syntactical correctness of S Q L statement and by the access control handler, that verifies the access rights of a user, in a relation to the requested data items. Next, SQL statement is passed to a query optimizer, that transforms the statement into a sequence of internal operations on data items and optimizes data access paths. At the end of this stage, the optimal processing plan is created for the processing of S Q L statement.

The optimised query processing plan, is passed to a query processor, that performs interpretation of the plan. Query processor brings the required data blocks, from persistent storage into a data buffer cache, located in the transient memory.

A data buffer cache is a piece of fast transient memory, that is used by a database system, for keeping frequently accessed data blocks. A data buffer cache is used to reduce the total number of read and write operations on slower persistent storage and to replace such operations, with the operation of much faster transient memory. The data blocks, that are read or written many times, do not need to be read from or write from slower persistent memory each time a read or write operation is performed by a database system. Instead, to make the modifications of data safe, a database server records all modifications in a sequential and persistent log. Sequential write operations on persistent storage are faster, than random write operations on persistent storage.

When processing of S Q L statement is completed, data obtained from the processing is returned to a client.

Client-Server Architecture

A client-server architecture means, that a number of clients connects to a database server over a local or wide-area network

Architecture of Database Server

Client-Server Architecture

A database server is implemented over there layers:
- Services not unique to MySQL like: network based client/server tools for connection handling, authentication, security, etc
- DDL and DML processing like query processing, analysis, optimization, caching, and all built-in functions, data entry, data modification, creating database structures, etc
- Database (storage) engines responsible for storing and retrieving data, e.g. InnoDB, MyISAM, MEMORY, CSV, etc

Client-Server Architecture

Client connections are organized in the following way:
- As more than one client can be connected at a time, each client connection gets its own thread within a database server
- A thread resides on one core or on one CPU
- When a client connects to a server then a server authenticates a connection
- Authentication is based on a user name, originating host and password
- Once a client is connected, the server verifies whether it has the privileges to access the relational tables in a database

Architecture of Relational Database Server

The basic database administrative operations include: starting, stopping, and testing a status of database server.

The operations are processed through a command line interface to operating system, for example: the Terminal program.

Usually, a database server is automatically started at the boot up time of an operating system.

A database server can be stopped through a command line interface using an operating system command: service, in the following way: service my s q l stop.

Note, that authentication at an operating system level, is required to process a command: service.

We may need to stop a database server, to perform some database administrative operations or to modify the values of system initialisation variables, that cannot be modified at run time.

A database server can be started through a command line interface, using an operating system command: service, in the following way: service my s q l start.

A status of a database server can be found through a command line interface, using an operation system command: service, in the following way: service my s q l status.

Basic Operations on Database Server

Starting/stopping a database server
Usually a database server is automatically started at boot-up time of an operating system
A database server can be stopped through a command line interface to an operating system (shell) for a certain period of time
A database server can be started through a command line interface to an operating system (shell)
A status of database server can be found through a command line interface to an operating system (shell) in the following way

Architecture of Relational Database Server

Outline

Client-Server Architecture
Basic Operations on Database Server
Initialization Variables
Post Installation
Databases

At startup time, a database server reads the values of system initialisation variables. The system initialisation variables determine the present functionality of a database server. By changing the values of system initialisation variables, we can change the behaviour of a database server.

For example, a system initialisation variable: autocommit, determines whether the data manipulation operations are automatically committed immediately after their successful completion.

Some of the names and values of system initialisation variables used in MySQL, are listed in the present slide.

For example, a value of: autocommit system initialisation variable, is set to: ON. It means, that in the present functionality of the system, the outcomes of data manipulation operations, are automatically committed.

Another system initialisation variable: date format, determines a default format used when a date is transformed from a string format into an internal date format. A default format requires a date to start from a value of year, followed by a value of month and a value of day.

The values must be separated with a single character: dash.

Initialization Variables

At startup time a database server reads the system initialization variables
The system initialization variables determine the functionality of a database server

For example, see below some of the system initalization variables of MySQL database server

+-------------------------------+--------------------------+
| Variable_name                 | Value                    |
+-------------------------------+--------------------------+
| auto_increment_increment      | 1                        |
| auto_increment_offset         | 1                        |
| autocommit                    | ON                       |
| automatic_sp_privileges       | ON                       |
| avoid_temporal_upgrade                                   |
...          ...               ...          ...           ...
| datadir                       | /var/lib/mysql/          |
| date_format                   | %Y-%m-%d                 |
| lower_case_table_names        | 0                        |
| max_user_connections          | 0                        |
...          ...               ...          ...           ...
+-------------------------------+--------------------------+

Initialization Variables

The system initialization variables are included in the system configuration files
To find the locations of system configuration files we may process the following commands through command line interface to an operating system
Typically the configuration files are located at
In our case a file mysqld.cnf with the system initialization variables is located at

Initialization Variables

The sample contents of system configuration file


[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp

To display all names and values of the system initialisation variables, start a command line interface: mysql and connect yourself to a database server and process a statement: show variables.

To display the names and values of global system initialisation variables, use a statement: show global variables. These variables are used to initialize the corresponding session variables, for new connections only.

To display the specific local, also called: session system initialisation parameters, use a statement: show variables like. These are the parameters, used at the moment by the current connection.

For example, to find the names and values of all variables related to updates, we can use a statement: show variables like single quotation precentage update percentage single quotation, please see the statement at the bottom part of the present slide.

A percentage sign (%) means a sequence of characters, possibly empty.

A string: %update%, means a word: update, included within any, possibly empty, string of characters.

For example, to find a value of a system initialisation variable: lower case table names, we use a statement: show variables like single quotation lower case table precentage single quotation.

Please, see a line at the bottom of the present slide.

Initialization Variables

To display ALL (501) system initialization variables we use show variables statement
To display GLOBAL (487) system initialization variables we use show global variables statement (parameters for new connections)
To display LOCAL(SESSION) (487) system initialization parameters we use show variables statement (parameters for the current connection)
For example, to find all variables related to updates we use a statement
For example, to find a value of variable lower_case_table_names we use a statement

There are two types of system initialisation variables: dynamic and static.

The values of dynamic system initialisation variables can be changed at run time, while no shutdown and restart of the system is required. To change a value of dynamic system initialisation variables we use a statement: set. For example, to change a value of a system initialisation variable: s q l safe updates, to: zero, we use the following statement: set s q l safe updates equals zero, see top part of the present slide.

Some of the system initialisation variables are static, (not dynamic), and such variables cannot be changed with the set statement. For example, a variable: lower case table names, is static and it cannot be changed with the set statement.

An attempt to change a value of a system initialisation variable: lower case table name, at run time ends with an error message: variable lower case table names, is a read only variable.

The static system initialisation variables must be changed within a system configuration file.

To do so, we must stop a database server and next, use a text editor to change a value of a variable and finally restart a database server.

Initialization Variables

To change a value of dynamic system initialization variables we use set statement
For example to change a value of system initialization variable sql_safe_updates to 0 we use the following set statement
Some of the system initialization variables are not dynamic and it cannot be changed with set !

For example, a variable lower_case_table_names is not dynamic and it cannot be changed with set

set lower_case_table_names=1

 
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

The system initialization variables, that are not dynamic must be changed in a system configuration file (stop server, change variable, start server)

Architecture of Relational Database Server

Outline

Client-Server Architecture
Basic Operations on Database Server
Initialization Variables
Post Installation
Databases

Just after installation of the system, there is only one user: root, available for a connection.

The user root, does not have a password granted and everyone can connect as a user root. As it is an evident security risk, you must set a password for a user root.

To do so, we start a command line client: mySQL and we connect as a user: root, without a password in the following way: mySQL -u root, see top of the present slide.

Next, we process ALTER USER statement, to set a new password for a user: root, connecting at a local host, see ALTER USER statement in the middle of the present slide.

To find the names of the other users, who can connect to the system, we process SELECT statement, that retrieves such information from a data dictionary, please see SELECT statement at bottom of the present slide.

Post Installation

Just after installation there is only one user root available on the installed system
First we start mysql client and we connect as a user root without a password !
As it is an evident security risk we must set a password for root user

To find what other users can connect to the system execute a statement:


SELECT user, host, HEX(authentication_string) FROM mysql.user;


+-----------+-----------+------------------------------------------...------+
| user      | host      | HEX(authentication_string)                        |
+-----------+-----------+------------------------------------------...------+
| root      | localhost | 2A383146354532314533353430374438386426432...30394 |
| mysql.sys | localhost | 2A5448495349534E4F544156414C4944504155345...44484 |
| csit115   | localhost | 2A323045343946324432303337373739383133336...44433 |
+-----------+-----------+------------------------------------------...------+

After a password of a user: root, is set up, any future connection as a user: root, must be done in the following way: my s q l -u root -p -v

An option: -p, means a connection with a password and an option: -v, verbose, turns on the listing of processed SQL statements included in SQL scripts.

Finally, remember that mySQL user: root, is completely different from the operating system user: root !

To find what databases are available on the system, we use a statement: show databases.

A database called: information schema, is commonly known as a data dictionary.

It contains information about the users, relational tables, columns, consistency constraints and the others.

To list the names of relational tables included in a database: information schema, (data dictionary) we use a statement: use information schema, see a line at the bottom of the present slide.

Post Installation

After a password to a user root is changed, any future connection as a user root must be performed as follows
Finally, we must remember, that MySQL root user is completely different from an operating system user root !
To find what databases are available on the system we use a statement
A database information_schema is commonly called as a data dictionary and it contains information about the relational tables, columns, constraints, etc
To list the names included in a database information_schema we make the database a default one

Post Installation

And, then we simply "say"
Now, we know the names of the relational tables in information_schema database and it is possible to access the data dictionary tables
For example, we access a relational table user_privileges to find what privileges are granted to the users

Some of the privileges granted to the users: root and csit115, are listed in the present slide.

+-------------------------+---------------+----------------+--------------+
| GRANTEE                 | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+----------------+--------------+
| 'root'@'localhost'      | def           | SELECT         | YES          |
| 'root'@'localhost'      | def           | INSERT         | YES          |
| 'root'@'localhost'      | def           | UPDATE         | YES          |
| 'root'@'localhost'      | def           | DELETE         | YES          |
| 'root'@'localhost'      | def           | CREATE         | YES          |
| 'root'@'localhost'      | def           | DROP           | YES          |
...			...	       ...        ...	         ...
| 'csit115'@'localhost'   | def           | SELECT         | NO           |
| 'csit115'@'localhost'   | def           | INSERT         | NO           |
| 'csit115'@'localhost'   | def           | UPDATE         | NO           |
| 'csit115'@'localhost'   | def           | DELETE         | NO           |
...			...	       ...        ...	         ...
+-------------------------+---------------+----------------+--------------+

Post Installation

The privileges granted to the users root and csit115 are the following

	      
+-------------------------+---------------+----------------+--------------+
| GRANTEE                 | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+----------------+--------------+
| 'root'@'localhost'      | def           | SELECT         | YES          |
| 'root'@'localhost'      | def           | INSERT         | YES          |
| 'root'@'localhost'      | def           | UPDATE         | YES          |
| 'root'@'localhost'      | def           | DELETE         | YES          |
| 'root'@'localhost'      | def           | CREATE         | YES          |
| 'root'@'localhost'      | def           | DROP           | YES          |
...			...	       ...        ...	         ...
| 'csit115'@'localhost'   | def           | SELECT         | NO           |
| 'csit115'@'localhost'   | def           | INSERT         | NO           |
| 'csit115'@'localhost'   | def           | UPDATE         | NO           |
| 'csit115'@'localhost'   | def           | DELETE         | NO           |
...			...	       ...        ...	         ...
+-------------------------+---------------+----------------+--------------+

Architecture of Relational Database Server

Client-Server Architecture
Basic Operations on Database Server
Initialization Variables
Post Installation
Databases

In MySQL database management system, the relational tables are stored in the databases. It is why we have to create a database, before we can create any relational table. To create a new database, we connect as a user: root, and we process: CREATE DATABASE statement, see top of the present slide.

We must be connected as a user: root, because a user: csit115, does not have the privileges to process: CREATE DATABASE statement.

A database can be dropped with: DROP DATABASE statement.

Note, that DROP DATABASE statement, drops all tables stored in the database being dropped and the only way to restore the relational tables is to use a backup.

To be able to create a database, a user must have: CREATE DATABASE privilege, and to drop a database a user must have: DROP DATABASE privilege

A statement: show databases, lists all existing databases.

To make a given database a default database for SQL statements, we process a statement: use database name.

Databases

In order to create the relational tables we have to create a database first
While connected as a user root we process a statement
We can always drop it with
To be able to create a database a user must have CREATE DATABASE privilege and to drop a database a user must have DROP DATABASE privileges
To list all created databases we process a statement
To make a database a default one, we process a statement

For example, to make a database: csit115, a default database for SQL statements, we process a statement: use csit115.

If a relational table: DEPARTMENT, is located in a database: csit115, then we can access the table with a simple: SELECT star FROM DEPARTMENT.

There is no need to prefix a name of a relational table with a name of database the relational table is located at.

However, if a relational table: COURSE, is located at a database: university, then we can access it through: SELECT star FROM university dot COURSE.

A database: university, is not the present default database and because of that, we can access a relational table: COURSE, by prefixing a relational table name with a database name.

We can also make a database: university, a default database, for example, see two bottom lines in the present slide.

Databases

For example, to use a database csit115 we process a statement
Then, if a relational table DEPARTMENT is created in a database csit115 then we can access the table with a simple
If a relational table COURSE is created in a database university then we can access it either through
or through prefixing a table name with a database name

Databases

Only one database can be a default on at a time, after processing of use statement
A user can access many databases at a time through prefixing the names of relational tables located in the other databases with an appropriate database name
A database can be dropped with

References

Cabral S., Murphy K., MySQL Administrator's Bible, Wiley Publications, 2009 (Available online through UOW Library)
How to ... ? Cookbook, How to manage MySQL database server ? Recipes 8.1, 8.2, 8.3, and 8.4