Shulou(Shulou.com)06/01 Report--

1. Composition of Mysql physical files

(1) Log files

1. Error log: Error Log

The error log records all serious warnings and error messages during the operation of MyQL Server, as well as the details of each startup and shutdown of MySQLServer. By default, the function of system logging error logs is turned off, and error messages are output to standard error output (stderr). If you want to turn on the function of system logging error logs, you need to turn on the-log-error option at startup. The default location of the error log is in the data directory, named after hostname.err. But you can use the command:-- log-error [= file_name] to change its storage directory and file name.

To facilitate maintenance, sometimes you want to back up the contents of the error log and start recording again, so you can use MySQL's FLUSH LOGS command to tell MySQL to back up the old log file and generate a new one. The backup file name ends with ".old".

2. Binary log: Binary Log & Binary Log Index

Binary log, which is often called binlog, is also one of the most important logs in MySQL Server. When we turn on the recording function through "--log-bin [= file_name]", MySQL records all the query that modifies the database data to the log file in binary form. Of course, the log is not limited to query statements, but also includes the execution time of each query, the resources consumed, and related transaction information, so binlog is transaction-safe.

Like the error log, the binlog logging function also requires the explicit specification of the "- log-bin [= file_name]" parameter to enable. If file_name is not specified, it will be recorded as mysql-bin.* in the data directory (* represents a number between 0x9 to indicate the sequence number of the log).

Binlog has some other additional option parameters:

"--max_binlog_size" sets the maximum storage limit for binlog. When the log reaches this upper limit, MySQL will recreate a log and start recording. However, occasionally binlog beyond this setting is generated, usually because a larger transaction is generated when the upper limit is about to be reached. In order to ensure transaction security, MySQL will not record the same transaction into two binlog separately.

The "--binlog-do-db=db_name" parameter explicitly tells MySQL that the binlog needs to be recorded against a (db_name) database, and if the "--binlog-do-db=db_name" parameter is explicitly specified, MySQL will ignore the query executed against other databases and only record the query executed against the specified database.

"--binlog-ignore-db=db_name", as opposed to "--binlog-do-db=db_name", explicitly specifies that the binlog record of a (db_name) database is ignored, and when this parameter is specified, MySQL records the binlog of all databases other than the specified database.

The two parameters "--binlog-ignore-db=db_name" and "--binlog-do-db=db_name" have a common concept that we need to understand clearly. The db_name in the parameters does not refer to the database in which the data updated by the query statement is located, but the current database in which the query is executed. No matter which database is updated, MySQL only compares the database in which the current connection is located (the database after switching through use db_name) with the database name set by the parameter, and does not analyze the database in which the data updated by the query statement is located.

The function of mysql-bin.index file (binary log index) is to record the absolute path of all Binary Log to ensure that various threads of MySQL can successfully find all the needed Binary Log files according to it.

3. Update log: update log

The update log is used by MySQL in older versions, and its function is basically similar to binlog, except that the content is recorded not in binary format but in simple text format. Since MySQL added the binlog feature, update logs have been rarely used. Since version 5. 0, MySQL no longer supports updating logs.

4. Query log: query log

Query all the query in the log record MySQL, and turn on this function through "--log [= fina_name]". Due to the record of all query, including all select, the size is relatively large, after opening it also has a greater impact on performance, so please be cautious about using this feature. This feature is only briefly turned on when it is used to track certain special sql performance issues. The default query log file name is hostname.log.

5. Slow log: slow query log

As the name implies, the slow query log records the query that has been executed for a long time, that is, we often say slowquery. Set-log-slow-queries [= file_name] to open this function and set the record location and file name. The default file name is hostname-slow.log, and the default directory is also the data directory.

The slow log is in a simple text format and can be viewed through a variety of text editors. It records the time of the execution of the statement, the time consumed by the execution, the execution user, connecting to the host and other related information. MySQL also provides a tool program mysqlslowdump that is specially used to analyze full query logs to help database administrators solve possible performance problems.

6. Innodb's online redo log: innodb redo log

Innodb is a transaction-secure storage engine, and its transaction security is mainly guaranteed by online redo logs and undo information recorded in the table space. All physical changes and transaction information made by Innodb are recorded in the redo log, and through the redo log and undo information, Innodb ensures transaction security in any case. Innodb's redo logs are also stored in the data directory by default. You can change the location of setting logs through innodb_log_group_home_dir, and set the number of logs through innodb_log_files_in_group.

(2) data files

In MySQL, every database has a folder named after the database under the defined (or default) data directory, which is used to store various table data files in the database. Different MySQL storage engines have different data files and different storage locations. The data files of most storage engines are stored in the same directory as the MyISAM data files, but each data file has a different extension. For example, MyISAM uses ".MYD" as the extension, Innodb uses ".ibd", Archive uses ".arc", CSV uses ".csv", and so on.

1. ".frm" file

The meta information related to the table is stored in the ".frm" file, including the definition information of the table structure, and so on. Regardless of the storage engine, each table has a ".frm" file named after the table. All ".frm" files are stored in the folder of the database to which they belong.

2. ".MYD" file

The ".MYD" file is dedicated to the MyISAM storage engine and stores data from MyISAM tables. Each MyISAM table has a ".MYD" file corresponding to it, which is also stored in the folder of the database to which it belongs, along with the ".frm" file.

3. ".MYI" file

The ".MYI" file is also dedicated to the MyISAM storage engine and mainly stores the index-related information of the MyISAM table. For MyISAM storage, the content that can be cache is mainly from the ".myi" file. Each MyISAM table corresponds to a ".myi" file, which is stored in the same location as ".frm" and ".MYD".

4. ".ibd" file and ibdata file

These two kinds of files are files for storing Innodb data. The reason why there are two kinds of files to store Innodb data (including indexes) is that the data storage method of Innodb can be configured to decide whether to use shared table space to store data or exclusive table space to store data. Exclusive tablespace storage uses ".ibd" files to store data, and each table has an ".ibd" file, which is stored in the same location as MyISAM data. If you choose a shared storage tablespace to hold the data, the ibdata file is used for storage, and all tables share a single (or multiple, self-configurable) ibdata file. Ibdata files can be configured by the two parameters innodb_data_home_dir and innodb_data_file_path, innodb_data_home_dir configuration data storage directory, and innodb_data_file_path configuration of the name of each file. Of course, you can also use the absolute path to complete the configuration of the innodb_data_file_path parameter without configuring innodb_data_home_dir. You can configure multiple ibdata files at a time in innodb_data_file_path. Files can be of a specified size or auto-extended, but Innodb limits that only the last ibdata file can be configured as an auto-extension type. When we need to add a new ibdata file, it can only be added at the end of the innodb_data_file_path configuration, and we must restart MySQL to complete the ibdata addition. However, if we use exclusive tablespace storage, there will be no such problem, but if we want to use bare devices, one bare device for each table may result in a very large number of bare devices, and it is not easy to control the size. It is difficult to implement, while shared tablespaces do not have this problem, and it is easy to control the number of bare devices. Personally, I prefer to use exclusive tablespace storage. Of course, the two ways have their own advantages and disadvantages, depending on where the focus of their respective application environment is.

The above only introduces the data files of the two most commonly used storage engines. In addition, various other storage engines have their own data files. Readers can create a table of a storage engine to do a simple test and learn more.

(3) Replication-related documents:

1. Master.info file:

The master.info file exists in the data directory on the Slave side, which stores the relevant information of the Master side of the Slave, including the host address of the Master, the connection user, the connection password, the connection port, the current log location, the location of the log that has been read, and other information.

2. Relay log and relay log index

The mysql-relay-bin.xxxxxn file is used to store the Binary Log information read by the Master thread on the Slave side, and then the SQL thread on the Slave side reads and parses the corresponding log information from the relay log, converts it into SQL statements executed by Master, and then applies it on the Slave side.

The function of the mysql-relay-bin.index file is similar to mysql-bin.index, which is also the absolute path to the location where the log is stored, except that what he records is not Binary Log, but Relay Log.

3. Relay-log.info file:

Similar to master.info, it stores information that is written to the local relay log through the Slave's I _ swap O thread. SQL threads on the Slave side and some administrative operations can obtain information about the current replication at any time.

2.1.4 other documents:

1 、 system config file

The system configuration files of MySQL are generally "my.cnf", which are stored in "/ etc" directory by default under Unix/Linux, and "c:/windows" directory in Windows environment. " The "my.cnf" file contains a variety of parameter option groups (group), each of which is given a fixed group name through square brackets, for example, the "[mysqld]" group includes the initialization parameters at the startup of the mysqld service, the "[client]" group contains parameters that can be read by the client tool program, and there are other specific parameter groups for each client software, such as "[mysql]" used by the mysql program. "[mysqlchk]" used by mysqlchk, and so on. If you write a client program by yourself, you can also set a parameter group name, configure the relevant parameters in it, and then call the parameter reading api in the mysql client api program to read the relevant parameters.

2 、 pid file

Pid file is a process file of mysqld application in Unix/Linux environment. Like many other Unix/Linux server programs, it stores its own process id.

3 、 socket file

Socket files are also available in the Unix/Linux environment. In the Unix/Linux environment, users can connect to MySQL directly using Unix Socket without going through the TCP/IP network.

II. Concepts of User, schema and database

In MySQL:

Server instance = = not identified with catalog, just a set of databases

Database = schema = = catalog = = a namespace within the server.

User = = named account, who is can connect to server and use (but can not own-no concept of ownership) objects in one or more databases

To identify any object you need (database name + object name)

In Oracle:

Server instance = = database = = catalog = = all data managed by same execution engine

Schema = = namespace within database, identical to user account

User = = schema owner = = named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas

To identify any object you need (schema name + object name)

3. MySQL default database mysql, information_schema, etc.

L mysql

Database mysql: this is the core database of mysql, which is mainly responsible for storing database users, permissions settings, keywords and other control and management information that mysql needs to use. Can not be deleted, if you do not know much about mysql, do not easily modify the table information in this database.

L information_schema

The information_schema database is native to MySQL and provides a way to access database metadata. What is metadata? Metadata is data about data, such as database or table names, data types of columns, or access permissions. Sometimes other terms used to describe this information include "data dictionary" and "system catalog".

In MySQL, think of information_schema as a database, or rather an information database. It holds information about all other databases maintained by the MySQL server. Such as the name of the database, the table of the database, the data type and access of the table column, etc. In INFORMATION_SCHEMA, there are several read-only tables. They are actually views, not basic tables, so you won't be able to see any files associated with them.

L performance_schema

MySQL 5.5 adds a storage engine named PERFORMANCE_SCHEMA, which is mainly used to collect database server performance parameters. MySQL users cannot create tables whose storage engine is PERFORMANCE_SCHEMA

Performance_schema provides the following features:

1. Provide details of the process waiting, including locks, mutexes, and file information

two。 Save historical event summary information to make a detailed judgment for providing MySQL server performance

3. It is very easy to add and delete monitoring event points, and you can change the monitoring cycle of mysql server at will, such as (CYCLE, MICROSECOND)

From the above information, DBA is able to understand in more detail what bottlenecks the performance degradation may be due to.

The opening of Performance is simple. Add performanc_schema to my.cnf [mysqld] to check whether the performance database is started:

SHOW VARIABLES LIKE 'performance_schema'

If the returned value is ON, the performance database is normally open.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.