MySQL architecture and various file type learning

1,mysql Architecture

Consists of a database and a database instance, which is a single entry multithreaded architecture.

Database: A collection of physical operating system files or other files, in MySQL, the database file can be frm, myd, Myi, IBD and other end of the file, when using the NDB storage engine, is not an OS file, is stored in memory files.

DB instance: consists of a database background process/thread and a shared memory area that can be shared by a running background process/thread.

2,mysql File types

There are several main file types for MySQL:

Parameter file: Where to find the database file when the MySQL instance starts, and specify some initialization parameters that define settings such as the size of a memory structure, and also describe the type of the parameter and the scope of the definition.

Log files: Files that are written when MySQL responds to a condition.

Socket file: Files required when logging in with Linux MySQL command line window

PID file: Process file for MySQL instance

MySQL table structure file: Storing MySQL table structure definition file

Storage Engine Files: A file that records storage engine information.

3, Parameter file my.cnf

? When the MySQL instance starts, the configuration parameter file is read first my.cnf

? Find MY.CNF Location

(1): Default: Mysql--help|grep my.cnf

(2): Background process to find: Ps–eaf|grep MySQL

(3): Global search: Find/-name my.cnf

? can use VI Direct maintenance to modify the parameter values inside

(1) Dynamic: can be modified in real time by set

(2) Static, can only be modified inside the MY.CNF, need restart effective

Parameters in the MySQL parameter file can be divided into 2 types: Dynamic parameters and Static parameters (Staitic)

The dynamic parameter means that it can be modified in the MySQL instance running, set global sort_buffer_size=32999999, and after the modification, the other connection will be able to take effect again.

The scope of entry into force is: global and session.

Static instructions are not modified during the entire MySQL instance run, just like a read only

4, log file

Log files record the various types of activity that affect the MySQL database, common log files have error logs, binary logs, slow query logs, full query logs, redo logs, undo logs

5. Error log

The error log logs the start, run, and shutdown processes of MySQL and MySQL dba should check the error log file for the first time when encountering a problem, which not only records the error message, but also records some warning messages and correct information. This error log file is similar to the alert file for Oracle, except that it ends with an error by default. Can be through show variables like ' log_error ';

You can see the file name of the error file as the host name of the server. Of course, you can also set the path of the error log file in MY.CNF:

Vim my.cnf

Log-error=/usr/local/mysql/mysqld.log

We can see in the error log file some database boot information, as well as the alarm message and error message

6, slow query logs slow log

The slow query log is a slow-running SQL statement information, to optimize the SQL statement to provide a good help, you can set a threshold, the running time exceeds the threshold of the SQL statement running information is recorded in the slow log log. The threshold value can be set by long_query_time or set to milliseconds microseconds:

However, it is important to note that for runtime equals this threshold, it will not be recorded inside.

Another parameter is log_queries_not_using_indexes, if the running SQL does not use the index, as long as the threshold is exceeded will also be recorded in the slow query log.

long_query_time=0 (logging all SQL can be audited), the DBA can use this audit to drive the business development, can know which business carries on the good those business to carry on the bad, through the slow SQL can analyze which application performance is poor needs the optimization improvement, The DBA's greatest function and contribution is to drive business development and progress through the maintenance of the database. From data to business, this is the direction we need to keep trying.

The slow query log can also be recorded in the table,

Slow_log table, you can also put the slow query log into a table

Show variables like ' log_output '; see if file is stored in slow log, if table is inside the Slow_log table.

7. Full Query Log

Logs all request information for the MySQL database, regardless of whether the request information was correctly executed, the default file name is hostname. log, and you can see the request for access denied.

Database audit + troubleshooting tracking (loss of 3%-5% performance)

8, binary Log

The operations that make changes to the database are recorded, but do not include the select operation and the show operation, because such operations have no modifications to the database itself, and if you want to record select and show, you need to look at the previous full query log. In addition, Binlog includes information such as the time and execution time of the database change operation.

The main functions of the binary are as follows 2:

(1): Restore recovery. Some data recovery requires a binary log, after the full library file recovery, can be based on the binary log for point-to-time recovery.

(2): Copy (Replication). Similar in principle to recovery, a remote MySQL database (slave) is synchronized in real time with a MySQL database (master) by copying and executing binary logs.

By setting Log-bin =/home/data/mysql/binlog/mysql-bin.log in My.cnf, the default is under Data directory DataDir

Binlog Log Parameters:

Max_binlog_size: Specifies the maximum value for a single binary, and if this value is exceeded, a new log file is generated, with the suffix name +1, and recorded in the. index file. The default value is 1G, but from years of DBA career summary, 64M is the universal size setting.

Binlog_cache_size:

When using the InnoDB storage engine, all uncommitted uncommitted binary logs are recorded in a cache, and when the transaction commits, committed writes the binary log in the buffer directly into the binary log file, and the buffer size is determined by the binlog_cache_ Size to determine that this buffer is based on the session, that is, every thread needs a transaction, MySQL will allocate a binlog_cache_size cache, so the change settings need to be very careful, not set too large, lest the memory overflow.

Sync_binlog:

Sync_binlog=n, the parameter optimization introduced, probably means that each write buffer n times to sync to the disk file, if n is set to 1, each time will be written to the Binlog disk file, which is the safest safest, if n>1, in the event of an accident, It is indicated that there will be N-1 DML is not written in Binlog, and there is a possibility that the active data inconsistency will occur.

Binlog-do-db, Binlog-ingore-db:

Represents a log that requires writing or ignoring which libraries are written, by default NULL, indicating that all library logs can be written to a binary file.

Log-slave-update:

Enable the Slave log feature on the slave server so that this computer can be used to form a mirror chain (A->B->C), which allows you to generate a binary log file from above the library, and then mount a slave library from the library.

Binlog-format: Log Format

With statement, row, mixed format

Statement: Every SQL that modifies data is recorded in Binlog.

Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Cons: because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and User-definedfunctions (UDF) can cause problems ).

2.Row: does not log the SQL statement context-sensitive information, only save which record is modified.

Advantage: Binlog can not record the context-sensitive information of executed SQL statements, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations

disadvantage: All executed statements when logged in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, then binlog each change will have a record, This causes the Binlog log volume to be very large, especially when executing statements such as altertable, because the table structure changes, each record changes, then the table each record will be recorded in the log.

3.Mixedlevel: is the above two levels of mixed use, the general statement modification using statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format to save Binlog, MySQL distinguishes the log form of the treated record according to each specific SQL statement executed. That is, choose between statement and row. The new version of the MySQL Squadron Rowlevel mode has also been optimized, not all changes will be recorded in Rowlevel, as in the case of table structure changes will be recorded in the statement mode. For statements that modify data such as update or delete, the changes are recorded for all rows.

Statements that use the following functions cannot be copied:

*load_file ()

*uuid ()

*user ()

*found_rows ()

*sysdate () (unless the--sysdate-is-now option is enabled at startup)

At the same time in the insert ... SELECT produces more row-level locks than RBR

Row, mixed

9, Socket sockets file

Linux system local connection MySQL can use the Linux domain socket socket, need a socket socket file, can have parameters socket control, the general default in the/tmp directory, can also be viewed in the following 2 ways:

1, ps-eaf|grep MySQL |grep socket

[Email protected] binlog]# Ps-eaf|grep mysql|grep socket

MySQL 3152 1979 0 Feb28? 00:00:02/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/home/data/mysql/data--plugin-dir=/usr/ Local/mysql/lib/plugin--user=mysql--log-error=/usr/local/mysql/mysqld.log--open-files-limit=8192--pid-file=/ Usr/local/mysql/mysqld.pid--socket=/usr/local/mysql/mysql.sock--port=3306

[Email protected] binlog]#

2,

3,my.cnf

Socket =/usr/local/mysql/mysql.sock

10,pid file

When the MySQL instance starts, it writes its process ID to a file, which is the PID file, which is controlled by the parameter pid_file, the default path is located in the database directory and can be viewed in the following three ways:

1), show variableslike ' Pid_file ';

2), Ps-eaf|grepmysql |grep pid

3), my.cnf (pid-file =/usr/local/mysql/mysqld.pid)

11, table structure file

*.frm

*.ibd

12,innodb Storing files

The InnoDB storage engine mimics Oracle on the storage design, which is the default tablespace file that can be set by parameter Innodb_data_file_path in the following format:

Innodb_data_file_path= Ibdata1:128m;ibdata2:128m:autoextend

You can make a table space with multiple files, and also make the properties of the file,

IBdata1 and IBdata2 are located on different disk groups, which can provide some level of performance improvement. The properties behind the file represent the file size, and Autoextend indicates that it can also be extended.

However, if Innodb_file_per_table is set to true, then the table data file will be in a separate. ibd file, not inside the Ibdata file.

13,redo file

All the databases are log first, write the log, then write the data file, so there will be redo log rules.

By default, there are 2 file names Ib_logfile0 and Ib_logfile1, and these 2 files can be seen in the MySQL database directory, which is important for INNODB storage engines because they record the transaction log for the InnoDB storage engine.

The main purpose of redo log files is: In case the instance or media fails media failure, redo log can come in handy, if the database due to the host power down caused the instance to fail, the InnoDB storage engine will use redo log to restore to the time before the power loss, in order to ensure the integrity of the data.

Each InnoDB storage engine has at least one redo log group, each group has at least 2 redo log files, such as the default Ib_logfile0 and Ib_logfile1, for greater reliability, you can set up multiple groups, or you can put each group on a different disk to improve performance

LSN logsequence Number:

Increment produces, can only mark a redo log, for our database recovery is very important, can only locate the database running state, as to how to locate the details, we can go to see the redo, undo the source code: in the "storage/innobase/ Include/log0log.h "

View parameter settings: Show variables like ' innodb%log% ';

14,undo Log

exist in the shared tablespace ibdata1 inside, there is a rollback segment address, which contains the header information, configuration header information, section of the header information, which stores the opposite of Redo data update operation, if rollback, the undo paragraph inside the data back to write to the data file inside.

If a stand-alone tablespace is used, it is stored directly in the table's own space without being stored in the shared tablespace. In the InnoDB storage engine, undo log is used to complete the rollback of transactions and the functionality of MVCC

Redo and undo they are not independent of each other, they are related, alternating cooperation to ensure data consistency and security,

Transferred from: http://www.2cto.com/database/201503/380114.html

MySQL architecture and various file type learning