MySQL data type

Common components of a relational database
Databases: Database
Tables: Table
Row: Row
Columns: Column
Indexes: Index
Trying to: view
Users: User
Permissions: Privilege

                    存储过程：procedure                    存储函数：function                    触发器：trigger                    事件调度器：event scheduler

Error log:
By default, the error log probably records the following areas of information:
1. Information during server startup and shutdown (not necessarily error messages, for example, how MySQL launches InnoDB tablespace files, how to initialize its own storage engine, etc.)
2. Error messages during server operation
3. Information generated when the event Scheduler runs an event
4. Information generated when starting the server process from the server

  Note: 1, you can set the value of different error logs according to their own needs 1 = only log at Errors level 2 = log Errors, warnings level logging 3= record errors, warnings, notes (defaults) level log 2, how to delete the old error log before mysql5.7: The database administrator can delete the error log for a long time to ensure that the MySQL server Hard disk space. MySQL database, you can use the Mysqladmin command to open a new error log: The command syntax is as follows: Mysqladmin-u root-p Flush_logs can also log in to the MySQL database using the Flush logs statement to Opening a new error log after 5.7: The server will turn off this feature. You can only use the Rename original error log file to manually flush the log to create a new method as follows: MV Mysqld.err Mysqld.err.old  

Binary log: Binary logs & binary log Index
binary logs, which we often call Binlog logs, is one of the most important logs in MySQL server, It is primarily used to record modified data or MySQL statements that may cause data changes, and to record:
1, statement occurrence
2, execution duration
3, operational Data
4, and so on
so the binary log allows you to query the MySQL database for those changes
General volume limit is 1G
when we open the function of the record with "Log-bin=file_name", MySQL logs all query that modifies the database data to the log file in binary form. Of course, not only is the query statement so simple in the log, it also includes the time that each query executes, the resources it consumes, and the related transaction information, so Binlog is transaction-safe. As with the error log, the Binlog logging feature also requires the display of the "bin-log=file_name" parameter to be enabled, and if file_name is not specified, it is recorded as Mysql-bin in the data directory. ( represents a number between 0~9 to represent the ordinal of the log)
Bin-log has some additional option parameters:

    Max_binlog_size: To set the maximum storage limit of binlog, usually set to 512M or 1G, generally not more than 1G, when the log reaches that limit, MySQL will re-create a journal to continue recording.    Occasionally, however, there are more binlog than this setting, generally because a larger transaction is generated when the upper limit is reached and MySQL does not log the same transaction to two binlog in order to ensure the security of the transaction.    Binlog-do-db=db_name: If the display of this parameter is specified, MySQL ignores the query executed against the other database and simply logs the query executed against the specified database. "Binlog_ignore_db=db_name" is the exact opposite of "Binlog-do-db=db_name", and he displays a binlog record that specifies that a database is ignored, and when this parameter is specified,        MySQL records the binlog of all databases outside the specified database. There is a common concept between "binlog-do-db=db_name" and "Binlog_ignore_db=db_name" two parameters: the db_name in the parameter is not the database of the database where the query statement was updated, but the current The database in which it is located.    Regardless of updating the data for that database, MySQL simply compares the database in which the current connection is located (the database that was switched on using the use db_name) with the same name as the database set by the parameter, without parsing the database in which the query statement was updated.    The function of the Mysql_bin.index file (binary log index) is to record the absolute path of all binary logs, ensuring that all MySQL threads are able to successfully find all the required binary log files. bin_cache_size=32768 (default) Bin_cache_size: A transaction, when not committed (uncommitted), the resulting log, recorded in the cache: Wait until the transaction commits (committed) need to commit, The log is persisted to disk. In general, if there are no large transactions in our database, writing is not particularly frequent, 2MB~4MB is a suitable choice. However, if our database large transaction, write volume is large, can be appropriately adjusted high binlog_cache_size. At the same time, we can analyze the settings by Binlog_cache_use and Binlog_cache_disk_use Binlog_cachE_size is sufficient, is there a large number of Binlog_cache that use temporary files to cache binlog_stmt_cache_size=32768 #当非事务语句使用二进制日志缓存 due to insufficient memory size, but beyond Binlog_stmt_    Cache_size, use a temporary file to hold these statements. binlog-format={row| Statement|        MIXED} #指定二进制日志的类型, default is MIXED.                1. Statement mode (SBR) Each SQL statement that modifies data is recorded in Binlog. The advantage is that there is no need to record data changes per row, reduce Binlog day quality, save Io, improve performance, and disadvantage is that in some cases the data in Master-slave will be inconsistent (such as the Sleep () function, last__insert_id (), and US Er-defind functions (UDF) and other problems) 2, Row mode (RBR) does not log the information of each SQL statement, only need to record that the data has been modified, what is the drawback is to generate a large number of logs, so that        The log soared. 3, Mixed Mode (MBR) above two modes of mixed use, general replication using statement mode save Binlog, for statement mode cannot copy operation using row mode save Binlog,mysql will select the log according to the SQL statement executed Storage mode.             That is, the use of alternating lines and statements, the MySQL server self-judgment.            The row-based definition of the data size is larger but can guarantee the accuracy of the data. Sync_binlog=10 #设定多久同步一次二进制日志至磁盘文件中, 0 means no synchronization, and any positive value indicates that there are not many write operations to the binary after synchronization.            When the value of autocommit is momentary, every statement execution causes binary log synchronization, otherwise the commit of each transaction causes synchronization of the binary log.            Max_binlog_cache_size #二进制日志缓存空间大小, 5.5.9 that later versions are only used for transaction caching, which is determined by the max_stmt_cache_size on-line. EXPIRE_LOG_DAYS={0,99} #设The number of days that the binary log log expires, the binary log that exceeds the number of days is automatically deleted, and the default is 0, which means that expired auto-deletion is not enabled.            If this feature is enabled, automatic removal usually occurs at MySQL startup or flush logs. Current binary file and location show binary logs; View binary information show master status; View all binary letters            Show Binlog event\g View the binary information for the specified log show Binlog events in ' mysql-bin.* ' \g * * on the command line view binary log mysqlbinlog "binlog_name" Delete binary log information purge {Binary|master} logs {to ' Lo G_name ' |            Before datetime_expr} example: Purge binary logs to ' mysql-bin.000006 ' removes all binary logs (with caution) reset master; It is not recommended to use this operation in a production environment

The

Transaction log: (or Redo log)
transaction log (InnoDB-specific log) can help increase the efficiency of the transaction. With transaction logs, the storage engine modifies the table's data only by modifying its memory copy, and then logging the modification behavior to the transaction log that is persisted on the hard disk, rather than persisting the modified data to the hard disk each time. The transaction log is appended, so the log operation is sequential I/O in a small area of the disk, rather than the need to move the heads in multiple places, like the random I/O, so the transaction log is relatively fast in the way it is used. After the transaction log is persisted, the in-memory modified data can slowly be brushed back to disk in the background. Most of the storage engines are now implemented in this way.

  If a transaction's modification has been logged and persisted to the transaction log, the single data itself has not been written to disk, and the system crashes, and the storage engine restarts automatically to recover this part of the modified data. The recovery method you have is dependent on the storage engine. Check out what MySQL has to offer search engine show engines view MySQL current default storage engine show variables like '%storage_engine% ' to see what engine show Create Teb     Le table name; change the table's storage engine create table library name. Show engine= "InnoDB" to view the definition of transaction log show global variables like ' innodb_flush_log_at% ' #在事务提交时innodb是否同步日志从缓冲区到文件中, when this value is 1 (the default), when each transaction commits, the log is written to the log file, the log file is refreshed with disk operations, and the performance is poor, resulting in a lot of disk I/O but the safest way to do this: if Set to 2, each commit transaction will be written to the log, but will not perform a brush operation.        Timed to log files per second is not guaranteed to be flushed to disk 100% per second, depending on the scheduling of the process.        Set to 0, the day to the buffer is written to the log file once per second, and the log file is refreshed with disk operations, but no action is taken on a thing commit. * * Brush Writing concept Brush is actually two operations, brush (flush) and write (write), it is important to distinguish between the two concepts. In most operating systems, the InnoDB log buffer (memory) is written to the log (called by the system to call write), simply to transfer the data to the operating system cache, the operating system cache also refers to memory.            There is no persisted data. So at 0 and 2, the last second is lost when a crash or power outage occurs because the data is only present in the operating system cache.    It is generally possible to lose more than one second of data, such as blocking when performing a flush operation. Summary: Set to 1 is certainly the safest, but performance is also the worst (compared to the other two parameters, but not unacceptable). If the data consistency and integrity requirements are not high, can be set to 2, if only demand performance, such as high concurrent write log server, set to zero for higher performance. 

Slow query log: Slow query log
As the name implies, the slow query log is a lengthy execution of query, which is what we often call slow query. The slow query log is a simple text format that allows you to view the content in a variety of text editors. It records the moment of execution, the time spent executing, executing the user, connecting the host and other related information.
Slow query log function:
The slow query log is used to record the execution time of the query, which is what we often say slow query.
through the slow query log can be found out those query statement execution efficiency is low, in order to optimize, generally recommended to open, he has little impact on server performance, but can record the MySQL server executed a long time query statement. can help us locate performance issues. MySQL also provides utility Mysqldumpslow, which is designed to analyze slow query logs, to help database managers resolve possible performance issues.

    View the definition of slow query log show global variables like '%slow_query_log% ' start and set slow query log method One: Open the Slow query log method by MY.CNF two: Login                 MySQL server direct settings (using set_query_log=1) are mostly versions that can be used by show variables like '%slow% ' or show variables like '%long% ' where Slow_query_log; off off state, on open state slow_query_log_file: Slow query log storage location Long_query_tim E: Set the time value, time in seconds, can be accurate to microseconds, if the time exceeded (the default is 10 seconds) This query statement will be recorded in the slow query log.                    When set to 0, indicates that all queries are logged. Note: 1, if you do not specify a path, the default is stored in the MySQL database data file, if you do not specify a file name, the default file name is Hostname-slow.log 2, the Unix time to turn  To a readable time, you can use the timestamp in the date [email protected] log 3, mysqldumpslow option parameter-S                            is to indicate in which way the c,t,l,r is sorted according to the number of records, time, query time, returned records, Ac,at,al,ar indicates the corresponding reverse order.                -T, which is the meaning of top N, that is, to return the number of data in front of-G, you can write a regular expression, case-insensitive file type: 1. frm file Table-related metadata (meta) information is stored in the ". frm" file, including the definition information for the table structure. No matter what storage engine is dedicated to storage engines, each table will have a tableThe named. frm file. The folder under which the owning database is stored 2. MyISAM database Table file:. myd file: Table data file. MYI: Table index file 3, InnoDB uses tablespace (tablespace) to manage data, store table data and indexes. ibd file: But a table tablespace file, each table uses a tablespace file (file per table), which stores            User database table data and Indexes InnoDB shared tablespace (that is, InnoDB file set, Ib-file set): Ibdata1, IBDATA2, etc., storing InnoDB system Information and user database table data and indexes, all tables shared 4. ibd files and Ibdata files both files are files that hold InnoDB data, and there are two types of files that hold InnoDB data (including indexes) because INNODB data storage can be configured to store data using shared tablespace, or                    is a unique table space for storing data. Exclusive tablespace:. IBD shared tablespace:. Ibdata ibdata files can be Innodb_data_fil                        E_path The total directory where the configuration data resides can be configured with multiple Idbata files at once, the file can be either specified in size or auto-extended, but only the last Ibdata file can be set to auto-expand. A restart is required to complete the Ibdata Add Summary: Shared tablespace and exclusive tablespace are shared tablespaces for the way data is stored: All tables in a database The index files are all placed in a single file exclusive tablespace: Each will be generated in a separate file mode for storage, each table has a. frm table description file, and one.        ibd file, where this file includes the advantages and disadvantages of the data content of a single table and the contents of the index: shared tablespace                Advantage: You can put the table space into multiple files to be stored on each disk. Data and files together easy to manage cons: All data and indexes are placed in one file, multiple tables and indexes are stored in a table space, so that a large number of deletions are done for a table, and the table space will                    There are a lot of voids, especially for statistical analysis, such applications such as day-value systems are most unsuitable for sharing table spaces. Stand-alone table space advantages: 1, each table has its own independent table Space 2, each table data and index will have its own table  Space in 3, you can achieve a single table in different databases move 4, space can be recycled a, Drop Table operation automatically reclaims the tablespace, if for statistical analysis or daily value table, delete large amounts of data, can be passed: altertable TableName engine = InnoDB; retract unused space B                        , for tables that use separate table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to handle it. Disadvantage: Single table increase is too large, such as more than 100 g in contrast, the use of exclusive table space efficiency and performance will be higher a bit on the stand alone                                Table space, off for shared tablespace replication (stored procedure) related files 1, master.info file The Master.info file exists in the data directory on the slave side, which contains information about the master side of the slave, including mastEr's host address, connection user, connection password, connection port, current log location, log location information that has been read. 2. Relay log and relay log index MYSQL-RELAY-BIN.XXXXXN files are used to store the binary lo that the I/O thread of the slave side reads from the Master side G information, which is then read from the relay log by the SQL thread on the slave side and parses the corresponding log information into the SQL statement executed by master, and then slave the end application Mysql-relay-bin The. index file functions like Mysql-bin.index, which is also the absolute path where the log is stored, except that she does not log the binary log and relay log 3, relay-log.in The Dex file is similar to Master.info, which holds information about the slave I/O thread writes to the local relay log, a Slave SQL thread and some administrative operations that are ready to get the current replicated                        Related information.                            Other files: 1, System config file mysql is usually my.cnf                            2, PID file PID files is a MySQL application in the unix/linux environment of a process document 3, socket file socket files only in the Unix/linux environment, you can directly only use the socket file to connect to MySQL, faster than TCP, but only for MySQL and application in a PC on the computer.

Summarize:
1. View system settings
Show [global|session] variables [like_or_where]
2. Operation Status
Show [global|session] status [Like_or_where]
3. Refresh the Log
Flush Log

MySQL data type