-- Installing MySQL on Unix/Linux Using Generic Binaries
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
-- Mysql Install(Linux)
-- Load file into Mysql
To load a log file in that format into MySQL, you can use a statement something like this:
LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
-- MySQL Server and Server-Startup Programs
The main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.
mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log file. A description of error logging is given later in this section
MySQL distributions on Unix include a script named mysql.server, which starts the server using mysqld_safe. It can be used on systems such as Linux and Solaris that use System V-style run directories to start and stop system services.
mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status.
mysqld_multi searches for groups named [mysqldN] in my.cnf (or in the file named by the --defaults-file option)
-- MySQL Installation-Related Programs
--MySQL Client Programs
-- MySQL Administrative and Utility Programs
The mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf. The file location is the %APPDATA%\MySQL directory on Windows and the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.
The unencrypted format of the .mylogin.cnf login path file consists of option groups, similar to other option files. Each option group in .mylogin.cnf is called a “login path,” which is a group that permits only certain options: host, user, password, port and socket. Think of a login path option group as a set of options that specify which MySQL server to connect to and which account to authenticate as. Here is an unencrypted example:
[client]
user = mydefaultname
password = mydefaultpass
host = 127.0.0.1
[mypath]
user = myothername
password = myotherpass
host = localhost
-- MySQL Program Development Utilities
-- The Most Important SQL Modes
The most important sql_mode values are probably these:
- ANSI
This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section. - STRICT_TRANS_TABLES
If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More details are given later in this section. - TRADITIONAL
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section.
- Note
The INSERT or UPDATE aborts as soon as the error is noticed. This may not be what you want if you are using a nontransactional storage engine, because data changes made prior to the error may not be rolled back, resulting in a “partially done” update.
When this manual refers to “strict mode,” it means a mode with either or both STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled.
-- Server Command Options
- Options that affect security: See Section 6.1.4, “Security-Related mysqld Options and Variables”.
- SSL-related options: See Section 6.4.5, “Command Options for Secure Connections”.
- Binary log control options: See Section 5.4.4, “The Binary Log”.
- Replication-related options: See Section 17.1.4, “Replication and Binary Logging Options and Variables”.
- Options for loading plugins such as pluggable storage engines: See Section 5.5.2, “Installing and Uninstalling Plugins”.
- Options specific to particular storage engines: See Section 14.14, “InnoDB Startup Options and System Variables” and Section 15.2.1, “MyISAM Startup Options”.
-- Installing and Uninstalling Plugins
Server plugins must be loaded into the server before they can be used. MySQL supports plugin loading at server startup and runtime. It is also possible to control the activation state of loaded plugins at startup, and to unload them at runtime.
-- Server System Variables
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
mysql> SHOW VARIABLES;
mysql> SHOW STATUS;
shell>When tuning a MySQL server, the two most important variables to configure aremysqladmin variablesshell>mysqladmin extended-status
key_buffer_size andtable_open_cache. You should first feel confident that you have these set appropriately before trying to change any other variables.mysqld --query_cache_size=16M --max_allowed_packet=1G
SET sort_buffer_size=10000; SET @@local.sort_buffer_size=10000; SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
The mysql System Database
Grant System Tables
These system tables contain grant information about user accounts and the privileges held by them:
- user: User accounts, global privileges, and other non-privilege columns.
- db: Database-level privileges.
- host: Obsolete. MySQL install operations do not create this table as of MySQL 5.6.7.
- tables_priv: Table-level privileges.
- columns_priv: Column-level privileges.
- procs_priv: Stored procedure and function privileges.
- proxies_priv: Proxy-user privilege
Object Information System Tables
These system tables contain information about stored programs, user-defined functions, and server-side plugins:
- event: Information about Event Scheduler events. See Section 20.4, “Using the Event Scheduler”. The server loads events listed in this table during its startup sequence, unless started with the --skip-grant-tables option.
- func: Information about user-defined functions (UDFs). See Section 24.4, “Adding New Functions to MySQL”. The server loads UDFs listed in this table during its startup sequence, unless started with the --skip-grant-tables option.
- plugin: Information about server-side plugins. See Section 5.5.2, “Installing and Uninstalling Plugins”, and Section 24.2, “The MySQL Plugin API”. The server loads plugins listed in this table during its startup sequence, unless started with the --skip-grant-tables option.
- proc: Information about stored procedures and functions. See Section 20.2, “Using Stored Routines (Procedures and Functions)”.
Log System Tables
The server uses these system tables for logging:
- general_log: The general query log table.
- slow_log: The slow query log table.
Log tables use the CSV storage engine.
For more information, see Section 5.4, “MySQL Server Logs”.
Server-Side Help System Tables
These system tables contain server-side help information:
- help_category: Information about help categories.
- help_keyword: Keywords associated with help topics.
- help_relation: Mappings between help keywords and topics.
- help_topic: Help topic contents.
Time Zone System Tables
These system tables contain time zone information:
- time_zone: Time zone IDs and whether they use leap seconds.
- time_zone_leap_second: When leap seconds occur.
- time_zone_name: Mappings between time zone IDs and names.
- time_zone_transition, time_zone_transition_type: Time zone descriptions.
Replication System Tables
The server uses these system tables to support replication:
- ndb_binlog_index: Binary log information for MySQL Cluster replication. See Section 18.6.4, “MySQL Cluster Replication Schema and Tables”.
- slave_master_info, slave_relay_log_info, slave_worker_info: Used to store replication information on slave servers. See Section 17.2.2, “Replication Relay and Status Logs”.
These tables use the InnoDB storage engine as of MySQL 5.6.6, MyISAM before that.
Optimizer System Tables
The innodb_index_stats and innodb_table_stats system tables are used for InnoDB persistent optimizer statistics. See Section 14.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.
These tables use the InnoDB storage engine.
MySQL Server has several logs
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
|
Log control at server startup. The --log-output option specifies the destination for log output. This option does not in itself enable the logs. Its syntax is --log-output[=value,...]:
TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files). NONE
The 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.
On some operating systems, the error log contains a stack trace if mysqld exits abnormally.
- Without --log-error, mysqld writes error messages to the console.
- With --log-error[=file_name], mysqld writes error messages to an error log file. The server uses the named file if present, creating it in the data directory unless an absolute path name is given to specify a different directory. If no file is named, the default name is host_name.err in the data directory
The General Query Log
The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
When using statement-based binary logging on a replication master server, statements received by its slaves are written to the query log of each slave. Statements are written to the query log of the master server if a client reads events with the mysqlbinlog utility and passes them to the server.
However, when using row-based binary logging, updates are sent as row changes rather than SQL statements, and thus these statements are never written to the query log when binlog_format is ROW.
A given update also might not be written to the query log when this variable is set to MIXED, depending on the statement used. See Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”, for more information.
By default, the general query log is disabled. To specify the initial general query log state explicitly, use --general_log[={0|1}]. With no argument or an argument of 1, --general_log enables the log
The Binary Log
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.
The binary log has two important purposes:
- For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
- Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin.
The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size
mysqld also creates a binary log index file that contains the names of all used binary log files. By default, this has the same base name as the binary log file, with the extension '.index'. You can change the name of the binary log index file with the --log-bin-index[=file_name] option.
A client that has the SUPER privilege can disable binary logging of its own statements by using a SET sql_log_bin=0
The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the --replicate-do-db and --replicate-ignore-db options.
To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-bin option
You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS
it is preferable to use PURGE BINARY LOGS, which also safely updates the binary log index file for you (and which can take a date argument).
mysqlbinlog also can be used to display replication slave relay log file contents because they are written using the same format as binary log files.
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
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.
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.
The DDL Log
The DDL log, or metadata log, records metadata operations generated by data definition statements such as DROP TABLE and ALTER TABLE. MySQL uses this log to recover from crashes occurring in the middle of a metadata operation.
Server Log Maintenance
- you can use the mysql-log-rotate script for this
- cron (or its equivalent) for handling log files.
- For the binary log, you can set the expire_logs_days system variable to expire binary log files automatically
- Log flushing occurs when you issue a FLUSH LOGS statement or execute a mysqladmin flush-logs, mysqladmin refresh, mysqldump --flush-logs, or mysqldump --master-data command
Server Plugins Available
MySQL distributions include several plugins that implement server extensions:
- Plugins for authenticating attempts by clients to connect to MySQL Server. Plugins are available for several authentication protocols. See Section 6.3.7, “Pluggable Authentication”.
- A password-validation plugin implements password strength policies and assesses the strength of potential passwords. See Section 6.5.2, “The Password Validation Plugin”.
- Semisynchronous replication plugins implement an interface to replication capabilities that permit the master to proceed as long as at least one slave has responded to each transaction. See Section 17.3.9, “Semisynchronous Replication”.
- MySQL Enterprise Edition includes a thread pool plugin that manages connection threads to increase server performance by efficiently managing statement execution threads for large numbers of client connections. See Section 5.5.4, “MySQL Enterprise Thread Pool”.
- MySQL Enterprise Edition includes an audit plugin for monitoring and logging of connection and query activity. See Section 6.5.3, “MySQL Enterprise Audit”.
- MySQL Enterprise Edition includes a firewall plugin that implements an application-level firewall to enable database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. See Section 6.5.4, “MySQL Enterprise Firewall”.
Installing Plugins
Before a server plugin can be used, it must be installed using one of the following methods. In the descriptions, plugin_name stands for a plugin name such as innodb, csv, or validate_password.
- Built-in plugins:
A built-in plugin is known by the server automatically. Normally, the server enables the plugin at startup. Some built-in plugins permit this to be changed with the --plugin_name[=activation_state] option.
- Plugins registered in the mysql.plugin system table:
- The mysql.plugin table serves as a registry of plugins (other than built-in plugins, which need not be registered). At startup, the server loads each plugin listed in the table. Normally, for a plugin loaded from the mysql.plugin table, the server also enables the plugin. This can be changed with the --plugin_name[=activation_state] option.
If the server is started with the --skip-grant-tables option, it does not consult the mysql.plugin table and does not load the plugins listed there.
- Plugins named with command-line options:
A plugin located in a plugin library file can be loaded at server startup with the --plugin-load or --plugin-load-add option. Normally, for a plugin loaded at startup, the server also enables the plugin. This can be changed with the --plugin_name[=activation_state] option.
--plugin-load=x --plugin-load-add=y
INSTALL PLUGIN myplugin SONAME 'somepluglib.so';
[mysqld]
csv=ON
blackhole=FORCE
archive=OFF
Obtaining Server Plugin Information
- The INFORMATION_SCHEMA.PLUGINS table contains a row for each loaded plugin. Any that have a PLUGIN_LIBRARY value of NULL are built in and cannot be unloaded.
SELECT * FROM information_schema.PLUGINS\G
- The SHOW PLUGINS statement displays a row for each loaded plugin. Any that have a Library value of NULL are built in and cannot be unloaded.
mysql> SHOW PLUGINS\G
MySQL Enterprise Thread Pool
As of MySQL 5.6.10, MySQL Enterprise Edition includes MySQL Enterprise Thread Pool, implemented using a server plugin. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.
Several system variables are related to the thread pool. The thread_handling system variable has a value of loaded-dynamically when the server successfully loads the thread pool plugin.
Thread Pool Tuning
thread_pool_size is the most important parameter controlling thread pool performance. It can be set only at server startup
, thread_pool_stall_limit, is important for handling of blocked and long-running statements.
SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED)
FROM information_schema.TP_THREAD_GROUP_STATS;
- thread_pool_prio_kickup_timer is set to 10000 (10 seconds).
- thread_pool_stall_limit is set to 100 (1 second).
Running Multiple MySQL Instances on One Machine
- --port=port_num
--port controls the port number for TCP/IP connections. - --socket={file_name|pipe_name}
--socket controls the Unix socket file path on Unix or the named pipe name on Windows - --pid-file=file_name
- --general_log_file=file_name
- --log-bin[=file_name]
- --slow_query_log_file=file_name
- --log-error[=file_name]
댓글 없음:
댓글 쓰기