MySQL Architecture

Required knowledge:
> Use MySQL client programs to interface with the MySQL Server interactively and in batch
> Describe SQL Modes and their impact on behavior of MySQL
> Identify characteristics which have session scope

> Use MySQL client programs to interface with MySQL Server interactively and in batch

The client programs that one can use to connect to MySQL include the following items:

The most common are ‘mysql,’ ‘mysqladmin’, and ‘mysqldump,’ and ‘mysqlimport’  These four have batch modes that will – in my mind – most likely be on the exam.

mysql

Short Form Connection
mysql [options] -h host.name.tld -u username -p
If using the password on the same line (without being prompted), the following is correct “-ppassword” but this is not correct “-p password

Long Form Connection
mysql [options] -host=host.name.tld –user username –password

It is best to know both the short and long forms of the options that have both available: mysql options list.

Batch Mode

The most common batch mode for using MySQL is to import data via the command line, which includes a SQL script, or a series of SQL statements, often from a mysqldump file.

$> mysql < batch-file or mysql -h localhost -u username -p < batch-file)

or

$> mysql -e “source batch-file”

Extra information is available in the mysql batch-mode documentation.

mysqldump

This command line program allows us to do a backup of data in a database, or transfer it from one server to another:

$> mysqldump [options] db_name [tbl_name …]

$> mysqldump [options] –databases db_name …

$> mysqldump [options] –all-databases

The important thing to remember is that if you want to backup the entire databse, then leave everything blank after db_name, and if you only want to backup or export a particular table, then you can specify

$> mysqldump [options] db_name tbl_name

If you want to dump the entirety of multiple databases then use the –databases option, and name the databases to dump all at once.

The last line dumps all databases in your current server.

There are multiple options for connection, and for adding DDL statements, and for debugging.  Again, too many to list here, since the documentation has a nicely formatted list to study.

mysqladmin

This lets us administer the database from the command line, without having to log in to the server.

$> mysqladmin [options] command [command-arg] [command [command-arg]] …

The complete list of options is here; pay particular attention to password changes, flushing privileges, and flushing logs.

mysqlimport

While it’s possible to import data via the mysql client from the command line, mysqlimport offers the ability to import CSV files, and other files that are not in a SQL script format.  It is the command line equivalent to the “LOAD DATA INFILE” command when logged in to the server.  As such, the CLI version includes all the expected options, such as “–columns” and “–fields-escaped-by” and “–fields-enclosed-by” among others.

$> mysqlimport [options] db_name textfile1 [textfile2 …]

Option Files

When connecting via the command line clients, one can use option files, so that it is not necessary to list them all when accessing those programs.  You separate the options for each program by using a group name in brackets. e.g. [mysql] or [mysqldump] or [mysqladmin].  The important things to note are that [client] is read by all programs (except mysqld), and that all options files use the long form of the options, but without the hyphens “–“.

On Linux and Unix like systems, the server looks for /etc/my.cnf, or a ~/.my.cnf in the home directory of each user.  If you use a personal .my.cnf file, you can add further options on a per user basis.  But if you specify an option that was already in /etc/my.cnf, the personal options will overwrite the options specified in the global file.

On Windows, mysql will look for 1) my.ini, and 2) my.cnf in c:\windows first, and then in c:\

> Describe SQL Modes and their impact on behavior of MySQL

SQL Modes are optional values that control some aspect of the query processing, such as how strict or forgiving a query should be.  You can clear all sql modes with

$> SET sql_mode = ”;

To set a particular sql mode:

$> SET sql_mode = ‘TRADITIONAL’;

Individual SQL Modes

The most common individual sql_modes are:

ANSI_QUOTES
Treats ” (double quotes) as an identifier determination (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings, because anything within the double quotes will be interpreted as an identifier.

ERROR_FOR_DIVISION_BY_ZERO
The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0). For data-change operations (INSERT, UPDATE), its effect also depends on whether strict SQL mode is enabled.

If this mode is not enabled, division by zero inserts NULL and produces no warning.

If this mode is enabled, division by zero inserts NULL and produces a warning.

If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.

For SELECT, division by zero returns NULL. Enabling ERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled.

IGNORE_SPACE
Permit spaces between a function name and the ( character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 10.2, “Schema Object Names”. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax

The table name should be quoted:

mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.00 sec)

The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always permissible to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

STRICT_ALL_TABLES
Enable strict SQL mode for all storage engines. Invalid data values are rejected.

STRICT_TRANS_TABLES
Enable strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines.

Composite SQL Modes

The most common composite sql_modes are:

TRADITIONAL
TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

ANSI
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

ANSI mode also causes the server to return an error for queries where a set function S with an outer reference S(outer_ref) cannot be aggregated in the outer query against which the outer reference has been resolved. This is such a query:

SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2 WHERE …);
Here, MAX(t1.b) cannot aggregated in the outer query because it appears in the WHERE clause of that query. Standard SQL requires an error in this situation. If ANSI mode is not enabled, the server treats S(outer_ref) in such queries the same way that it would interpret S(const).

Here is the full list of all SQL Modes.

> Identify characteristics which have session scope

The items that are tied to session are:

Also, note that none of these items are protected with the “auto-reconnect” option.

Leave a Reply

Your email address will not be published. Required fields are marked *