Apply my SQL functionality to administer databases Create a

Solution

1. Creating passwords

SET PASSWORD Syntax

SET PASSWORD syntax for MySQL 5.7.6 and higher:
SET PASSWORD [FOR user] = password_option
password_option: {
PASSWORD(\'auth_string\')
| \'auth_string\'
}

SET PASSWORD syntax before MySQL 5.7.6:
SET PASSWORD [FOR user] = password_option
password_option: {
PASSWORD(\'auth_string\')
| OLD_PASSWORD(\'auth_string\')
| \'hash_string\'
}

The SET PASSWORD statement assigns a password to a MySQL user account, specified as either a cleartext (unencrypted) or encrypted value:
\'auth_string\' represents a cleartext password.
\'hash_string\' represents an encrypted password.

2. Checking systems and status variables
SHOW VARIABLES Syntax
SHOW [GLOBAL | SESSION] VARIABLES
[LIKE \'pattern\' | WHERE expr]
SHOW VARIABLES shows the values of MySQL system variables (see Section 6.1.5, “Server System Variables”). This statement does not require any privilege. It requires only the ability to connect to the server.

3. Configuring innoDB
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# skip_innodb
innodb_data_file_path=idbdata1:200M:autoextend
set-variable = innodb_buffer_pool_size=80M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

4. Using query cache
For the query cache to actually be able to hold any query results, its size must be set larger:
mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE \'query_cache_size\';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| query_cache_size | 999424 |
+------------------+--------+
1 row in set (0.00 sec)
The query_cache_size value is aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you assign.

If the query cache size is greater than 0, the query_cache_type variable influences how it works. This variable can be set to the following values:

A value of 0 or OFF prevents caching or retrieval of cached results.

A value of 1 or ON enables caching except of those statements that begin with SELECT SQL_NO_CACHE.

A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

etting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this:

mysql> SET SESSION query_cache_type = OFF;

The have_query_cache server system variable indicates whether the query cache is available:
mysql> SHOW VARIABLES LIKE \'have_query_cache\';

When you set query_cache_size to a nonzero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures. (The exact size depends on system architecture.) If you set the value too small, you\'ll get a warning, as in this example:

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE \'query_cache_size\';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 41984 |
+------------------+-------+

5.Reading the error, slow query and relay logs

MySQL Server has several logs that can help you find out what activity is taking place.

Log Type   Information Written to Log
Error log   Problems encountered starting, running, or stopping mysqld
General query log   Established client connections and statements received from clients
Binary log   Statements that change data (also used for replication)
Relay log   Data changes received from a replication master server
Slow query log   Queries that took more than long_query_time seconds to execute
DDL log (metadata log)   Metadata operations performed by DDL statements

5.1 Error Log

The error log contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

“console” means stderr, the standard error output; this is your terminal or console window unless the standard error output has been redirected. (For example, if invoked with the --syslog option, mysqld_safe arranges for the server\'s stderr to be sent to syslog, as described later.)

On Windows, the --log-error, --pid-file, and --console options affect error logging:

Without --log-error, mysqld writes error messages to the default log file.

With --log-error[=file_name], mysqld writes error messages to an error log file. If no file is named, mysqld writes to the default log file. If a file is named, mysqld writes to it, creating it in the data directory unless an absolute path name is given to specify a different directory.

The default log file is host_name.err in the data directory, unless the --pid-file option is specified. In that case, the default name is the PID file base name with a suffix of .err in the data directory. This default is used if --log-error is not given, or is given without naming a log file.

With --console, mysqld writes error messages to the console, unless --log-error is also given. If both options are present, --console is ignored and has no effect. Their order does not matter: --log-error takes precedence and error messages go to a log file. (Before MySQL 5.5.3, if both options are given, the last one takes precedence.)

In addition, on Windows, the server writes events and error messages to the Windows Event Log within the Application log. Entries marked as Warning and Note are written to the Event Log, but not informational messages such as information statements from individual storage engines. These log entries have a source of MySQL. You cannot disable writing information to the Windows Event Log.

On Unix and Unix-like systems, mysqld writes error log messages as follows:

Without --log-error, mysqld writes error messages to the console.

With --log-error[=file_name], mysqld writes error messages to an error log file. If no file is named, mysqld writes to the default log file. If a file is named, mysqld writes to it, creating it in the data directory unless an absolute path name is given to specify a different directory.

The default log file is host_name.err in the data directory. This default is used if --log-error is given without naming a log file.

The log_error system variable indicates the error log file name if error output is written to a file.

If you specify --log-error in an option file in a [mysqld], [server], or [mysqld_safe] section, mysqld_safe finds and uses the option.

If you flush the logs using FLUSH LOGS or mysqladmin flush-logs and mysqld is writing the error log to a file (for example, if it was started with the --log-error option), the effect is version dependent:

As of MySQL 5.5.7, the server closes and reopens the log file. To rename the file, do so manually before flushing. Flushing the logs then reopens a new file with the original file name. For example, to rename the file and create a new one, use the following commands (assuming a log file name of host_name.err):

shell> mv host_name.err host_name.err-old
shell> mysqladmin flush-logs
shell> mv host_name.err-old backup-directory
On Windows, use rename rather than mv.

5.2 Slow query LOG

The Slow Query Log
The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.

By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using --log-slow-admin-statements and log_queries_not_using_indexes, as described later.

The time to acquire the initial locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.

By default, the slow query log is disabled. To specify the initial slow query log state explicitly, use --slow_query_log[={0|1}]. With no argument or an argument of 1, --slow_query_log enables the log. With an argument of 0, this option disables the log. To specify a log file name, use --slow_query_log_file=file_name. To specify the log destination, use --log-output (as described in Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”). The older option to enable the slow query log file, --log-slow-queries, is deprecated.

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set slow_query_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the slow query log is enabled, the server writes output to any destinations specified by the --log-output option or log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the slow query log is enabled. Setting the log file name has no effect on logging if the log destination value does not contain FILE.

The server writes less information to the slow query log if you use the --log-short-format option.

To include slow administrative statements in the statements written to the slow query log, use the --log-slow-admin-statements server option. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable. When such queries are logged, the slow query log may grow quickly.

The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:

The query must either not be an administrative statement, or --log-slow-admin-statements must have been specified.

The query must have taken at least long_query_time seconds, or log_queries_not_using_indexes must be enabled and the query used no indexes for row lookups.

The query must have examined at least min_examined_row_limit rows.

The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.

By default, a replication slave does not write replicated queries to the slow query log. To change this, use the --log-slow-slave-statements server option.

The slow query log should be protected because logged statements might contain passwords. See Section 6.1.2.3, “Passwords and Logging”.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.