MySQL Architecture composition

What is MySQL

MySQL has mysqld AB Independent research and development, is one of the most popular development of source code database management system, it is also a support multi-threaded high concurrency multiuser relational database management system

MySQL database with its simple and efficient and reliable characteristics.

Simple comparison of MySQL with other databases

Feature comparison: field type support, another well-known open source database PostgreSQL supports the most complete type, while Oracle and some other business databases, such as Db2,sybase, are relatively less than MySQL. In terms of transactional support, although MySQL's own storage engine is not available, it has implemented all of the four transaction isolation levels defined by the SQL 92 standard through a third-party plug-in storage engine, InnoDB. There is still a gap between MySQL and other databases in terms of programmable support, although the latest version of MySQL has begun to offer some simple programmable support. such as starting to support Procedure,function,trigger and so on. However, the supported features are still relatively limited, compared with several other major commercial database management systems, still insufficient.

Ease-of-use comparisons: In terms of system usability, every user who has ever used MySQL can clearly feel the advantages of MySQL in this respect with other common database management systems. In particular, for some large business database management systems such as ORACLE,DB2 and Sybase, for ordinary users, the ease of operation is obviously not at a level, MySQL has always adhered to the principle of easy to use. From the installation side, the MySQL installation package is only about 100MB, and a few large commercial databases at a level, the ease of installation also forced oraccle and other commercial databases are much simpler, whether through the compiled binary distribution package or source code compiled installation. Data creation comparison, MySQL only need a simple create datebase command, can be completed in a moment to build the library, and Oracle to create a database is a very large project

Performance Comparison: Only Oracle database can be compared with one of the higher

Reliability: The most popular web site in the world is the MySQL database, which shows that MySQL is stable and reliable, and most of the 10-bit global Web sites are running in a MySQL database environment. MySQL database in the development process has been its own three principles: simple, efficient and reliable.

MySQL's main fit scenario

Web Site System

MySQL is the largest customer base, because the MySQL database installation configuration is very simple, the use of process maintenance is not as complex as a large business database management system, and excellent performance. One important reason is that MySQL is open source and can be used completely free of charge.

Logging System

MySQL database insert and query performance are very efficient, if the design is better, when using the Mylsam storage engine, the two can be unlocked, to achieve high concurrency performance. So for systems that require a lot of logging for insertions and queries. such as processing user log, operation log, etc. is very suitable for the application scenario

Data Warehouse System

With the rapid growth of data warehouse data in the 21st century, we need more and more storage space, increasing data volume, so that statistical analysis of data becomes more and more inefficient and more and more difficult. There are still a few ideas to solve: one is to use expensive high-performance host to improve computing performance, with high-end storage equipment to provide I/O performance, but the cost is very high: second, by copying the data to several high-capacity hard disk on the cheap PC server, to improve overall computing performance and I/O capabilities, the effect is fair, Storage space is limited, low cost: Third, by splitting the data horizontally, using multiple inexpensive PC server and local disk to hold the data, each machine has only a portion of all the data, solve the problem of data volume, all PC server parallel computing, but also solve the problem of computing power, Through the intermediate agent to allocate the operation of each machine task, that can solve the computational performance problems and can solve the I/O performance problems, the cost is very low. The second and third implementations, MySQL has a large advantage, through the simple replication of MySQL, can be very good to copy data from one host to another, the Internet can also be replicated. Of course, other databases can also be done, not only MySQL has such a feature, but MySQL is the meter, and the majority of other databases are based on the number of hosts or CPU charges, basically all database systems can be achieved, but its high cost not every company can bear.

MySQL Architecture composition

Log files: error log, query log, slow query log, transaction log, binary log

Log is an important part of the MySQL database log files recorded in the MySQL database during the operation of the changes, that is used to record the MySQL database client connection status, SQL statement execution and error information. When the database is accidentally damaged, you can find the cause of the error by looking at the log file, and you can also recover data from the log file.

Error log: errorlog

In the MYSLQ database, the error log function is turned on by default, and the error log is stored by default in the data directory of the MySQL database, and the error log file is usually named Hostname.err. Where Hostnmae represents the server host name.

The error log information can be configured on its own, and the information logged by the error log can be defined by Log-error and log-warnings, where log-warnings defines whether the warning information is also defined in the error log. By default, the error log probably records information from the server startup and shutdown process, the server allows error messages in the process, the event Scheduler allows an event to generate information, and the information that is generated when the server process is started on the server.

MySQL has a lot of system variables can be set, system variable settings, will cause the system to allow different state.

To view system settings:

Show [Global | session] variables [Like_or_where]

Show Variables:shows the values of the MySQL system variables

To view the allowed status:

Show [Global | session] status [Linke_or_where]

Show Status:provides Server status information

How to modify the system configuration

Configuration File Settings my.cnf

binlog_cache_size=1m

Set Global binlog_cache_size=1048576

In general, the definition of a log level does not have a session variable that is defined only at the global level

View the status of the error log: Show global variables like '%log_error% ';

Log_error the file path defined as the error log

Log_error_verbosity

Change the error log location to use Log-error to set the form

Vim/etc/my.cnf

Log-error=/usr/local/mysql/data/mysqld.err

Tail/usr/local/mysql/data/mysqld.err

In order to facilitate maintenance needs, sometimes want to make the error log content in the north and restart recording, this time, you can use the Ysql Flush logs command to tell MySQL to back up the old log file and generate a new log file, the Northern file name ends in.

Delete error log

mysql5.5.7 before: The database administrator can delete the hard disk space on the MySQL server for a long time before the error log to bun. MySQL database, you can use the Mysqladmin command to open a new error log. mysqladmin command syntax: mysqladmin-u root-p flush-logs can also log in to the MySQL database using the Flush logs statement to open a new error log.

After mysql5.5.7: The server will turn off this feature, only use the Rename original error log file, manually flush the log to create a new

Binary logs: Binary log & Binary log index

Binary log, which is what we often call Binlog, is also one of the most important logs in MySQL server, which is used to record the MySQL statements that modify data or may cause data changes, and to record the time of the statement, the length of execution, the data of the operation, and so on. So the binary log allows you to query what changes have been made in the MySQL database, with a general size limit of 1G.

When we open the function of the record through "Log-bin=file_name", MySQL logs all queries that modify the database data into a log file in binary form, not just the query statement, but also the timing of a query execution. The resources consumed, and the related transaction information, so the Binlog is transaction-safe

As with the error log, the Binlog logging function also requires the explicit designation of the "Log-bin=file_name" parameter to turn on, and if no file_name is specified, it is recorded in the data directory as mysql-bin.******

View binary Open Status

Show global variables like '%log_bin% ';

Variable_name value

Log_bin on

Log_bin_basename/usr/local/mysql/data/mysql-bin

Log_bin_index/usr/local/mysql/data/mysql-bin.index

Log_bin_trust_function_creators OFF

Log_bin_use_v1_row_events OFF

Binlog There are some additional option parameters:

"Max_binlog_size" set the maximum storage limit of binlog, generally set to 512 or 1G, generally not more than 1G log up to the limit is, MySQL will re-create a journal to continue to record, but occasionally there are more than the set of Binlog generated, It is generally because the upper limit is reached, resulting in a larger transaction, in order to ensure transaction security, MySQL will not record the same transaction separately into two Binlog

The "binlog-do-db_name" parameter explicitly tells MySQL that a database record needs to be binlog, and if the display of the "binlog-do-db=db_name" parameter is specified, MySQL ignores the query performed on the other database. Instead, simply log the query against the database execution.

This article is from the "11853028" blog, please be sure to keep this source http://tanhong.blog.51cto.com/11853028/1902787

MySQL Architecture composition