Detailed explanation of MariaDB logs and transactions and basic operation statements under CentOS6.5

MySQL Log category:
General query logs: log, general_log, log_output
Slow query log: the query execution time exceeds the specified query time, that is, the slow query;
Error Log: generally refers to the error log information, usually the log information about server shutdown and startup, the error information during server running, and the warning information.
Binary log: it is only an operation related to modification. It can be understood as a redo log for copying basic creden;
Relay log: it is actually related to replication, almost the same as binary logs;

Transaction Log: Random I/O is converted to sequential I/O, which is usually stored in two files. If one file is full, it is stored in another file.

Query logs:

Log = {ON | OFF}: whether to record the log information of all statements in the general query log FILE (general_log) log_output :={ TABLE | FILE | NONE} TABLE and FILE can appear at the same time, use commas to separate them. general_log: whether to enable the query log general_log_file: defines the file saved in the general query log mysql> set golbal general_log = {OFF | ON }; enable or disable the mysql> set glogal log_output = 'table' option to control the log storage method. TABLE indicates that the log is saved as a table.

Slow query log:

Long_query_time: 10.000000 seconds. If this value is exceeded, it is called the slow query log slow_query_log: OFF. It sets whether to enable the slow query log, its output location also depends on log_output = {TABLE | FILE | NONE}; slow_query_log_file: www-slow.log: defines the log FILE path and name mysql> set global log_output = 'file, table '; the output is in the form of a table or a file.

Error Log:
1. Information during server startup and shutdown;
2. error messages during server running;
3. Information generated when an event is scheduled to run;
4. Information generated when the slave server thread is started in the replication architecture;

Log_error =/path/to/error_log_file: Specifies to save the file to a specific directory.
Log_warnings = {0 | 1}: Indicates whether warning information is recorded in the error log.

Binary log:

Records the information related to the modification, which affects the potential content of the data. The select log is not recorded in binary logs. The binary log is also called a copy log, which is stored in the data directory by default, the command used to view logs is mysqlbinlog.
Functions of binary logs:
1. Perform restoration at the time point. We can manually tune the recovery at which point, so this is an important tool for Backup recovery.
2. Perform replication;
Because our MySQL Data is a single-process multi-thread mechanism, it can initiate many statements that we modify at the same time, but our server only uses one log, if the requests of multiple threads simultaneously write data to the log file at the same time, the log file becomes a resource hotspot, also called a resource requisition point, which will be messy, to solve this problem, when our thread needs to write data to the binary log file, it does not directly write data to the log file, or write data to the log buffer in a unified manner, the log buffer is used to write log files one by one. However, for a very busy server, our log files produce a large amount of data every day, if all the binary log data is written in a log file, it is inconvenient to manage and unreasonable. If the log file is lost, then all the log data information is lost. We should not do this. Then we have a log rolling mechanism. We can define binary logs by ourselves, there are two methods to define log rollback, one is defined by size, for example, we define the size of a log file to 1 GB, the storage will scroll around 1 GB and use the next day. Logs are used to store binary log data. The other one can be defined by time, for example, rolling once a week or once a month or once a day; or the log will be rolled every time you restart the service or execute the flush logs command.

MariaDB [(none)]> show master status; you can view the binary log files being used by the current server and the Position based on when the next event starts. If you are currently using 00004, so 00001, 2, 3 will not be used again, because it has been rolled over. MariaDB [hellodb]> show binary logs; view all the BINARY log files on the current system, in fact, is the information in the mysql-bin.index file under the Data Directory, this file is the entry that saves the log file that has been rolled. MariaDB [hellodb]> flush logs; command for clearing log files: PURGEMariaDB [hellodb]> show binlog eventsin 'Log _ file '; # mysqlbinlog -- start-time -- stop-time -- start-# mysqlbinlog -- start-protion = 1139 mysql-bin.000001: in the command line, view the data recorded after a certain location of a log file. You can use output redirection to save it to a file for future execution. Server-id: id of the server, MariaDB [hellodb]> select version (); view the current database VERSION MariaDB [(none)]> show binlog events in 'mysql-bin.000001 'FROM 1139; view the event information of a binary log file. MariaDB [(none)]> show binlog events in 'mysql-bin.000006 '\ G to view information related to all time points and end points IN a binary file.

MySQL records binary logs in three formats:
Statement-based: statement allows you to save exactly the same data as statements.
Row-based: row, for example, the current insertion time. Is the current insertion time the current time? Are you sure you want to insert the current time in a few days? The function of the current time is CURRENT_DATE (), the information based on the row record is more accurate, but sometimes the data processing is too large. For example, if you want to update 10000 pieces of data to write only one statement, you need to record 1000 rows based on the row record.
Mixed Mode: mixed. MySQL determines the log recording method.

The content formats of binary log files mainly contain the following record information:

# At 1451 #140409 17:40:01 server id 1 end_log_pos 1563 Query thread_id = 10 exec_time = 0 error_code = 0 set timestamp = 1397036401 /*! */;/*! 40000 alter table 'classe' disable keys *//*! */; Record the date and time when the event occurred (140409 17:40:01) record the server ID serverid1 record the event type Query record the end position of the event, this is where the event ends (end_log_pos 1563). It records the ID number (thread_id = 10) generated by the thread on the original server. MariaDB [hellodb]> show processlist; view the information and ID of a thread, and record the statement Timestamp and the time difference between writing binary log files. The unit is seconds exec_time = 0, if the value is less than 1 second, it is recorded as 0. The error code: 0 indicates no error (error_code = 0) indicates the start position of the record. It is also the end position at 1451 /*! */: This is a comment.

Server parameters related to binary log files:

MariaDB [hellodb]> show global variables like '% log %'; view the server log parameter log_bin = {NO | OFF}, which can also be the file path, specify the location where the binary file path is stored. SQL _log_bin = {NO | OFF} log_bin_trust_function_creatorsbinlog_format = {statement | row | mixed} is in three binary formats: statement, row, and mixed mode. Max_binlog_cache_size =: size of the binary log buffer space. Only logs of related classes of the OSS are buffered. Max_binlog_stmt_cache_size =: the size of the shared buffer with non-transactional and transactional classes. The Unit is byte max_binlog_size: the upper limit of the binary log file. If the unit is byte, the file will scroll when the upper limit is exceeded.

Note: Do not place binary logs and data files on the same device. This improves performance and ensures that data cannot be recovered due to damage to data files. By default, data files are stored together. Therefore, many default settings of MySQL are not suitable for use in the production environment and need to be adjusted.

Relay log: it is actually related to replication. It is almost the same as binary log, but it is not used to record events, but is used as the source for reading data and executed locally, of course, the relay log is on the slave server.

MariaDB [hellodb]> show global variables like '% relay %'; relay_log = {null }: whether to enable relay_log_purge = {NO | OFF} for relay logs: Indicates whether to automatically clear relay logs that are NO longer needed. By default, relay_log_space_limit is enabled. This indicates whether the relay log space is limited. If it is 0, relay_log_recovery is not limited. It is related to automatic recovery of relay logs.

MySQL multi-Table query and subquery:
Join query: join two or more tables in advance, query based on the join results, and cross join
MariaDB [hellodb]> SELECT * FROM students, classes; multiple tables are combined into one table for cross join query.
Natural join: Also known as inner join or equivalent join
Equivalent join: equivalent join is performed on the corresponding fields of two tables, also called inner join.
Conditional join: Multi-table join with the WHERE condition after multi-table join
Outer Join: Outer Join is divided into left Outer Join and right outer join.

LEFT Outer JOIN: only the tuples In the relation that appear before the LEFT Outer JOIN Operation (that is, the LEFT side) are retained. The tuples are the entry left_tb left join right_tb ON connection condition MariaDB [hellodb]> SELECT s. name, c. class FROM students AS s left join classes AS c ON s. classID = c. classID;: left join left Outer JOIN. ON is based ON the condition. The LEFT Outer JOIN is based ON the students table ON the LEFT, as long as all the tables ON the LEFT are displayed, if the right table does not have one, it is empty. RIGHT outer JOIN: only the tuples in the link that appears after the RIGHT Outer JOIN Operation (that is, the RIGHT side) are retained; left_tb right join right_tb on right Outer JOIN, MariaDB [hellodb]> SELECT s. name, c. class FROM students AS s right join classes AS c ON s. classID = c. classID;: right join: The left Outer JOIN. The ON condition is what the RIGHT outer JOIN is based ON the classes table ON the RIGHT, as long as all the tables ON the RIGHT are displayed, if the left table does not have one, it is empty. Full outer join: both of them are left empty, and mysql does not support full outer join.

Alias:
The table alias can be added with the AS Alias after the table, and the field alias can be added with the AS Alias after the field.

MariaDB [hellodb]> SELECT s. name, c. class FROM students AS s, classes AS c WHERE s. classID = c. classID; call MariaDB [hellodb]> SELECT s. name, c. class FROM students AS s right join classes AS c ON s. classID = c. classID order by Name LIMIT 10;: order by Name LIMIT 10 sorts BY alias and then LIMIT 10 retrieves the data of the first 10 rows.

Nested queries in a query are called subqueries.
1. When used to compare subqueries in expressions, only one of the return values can be returned. this parameter is used for subqueries in WHERE.
2. Used for subqueries in EXISTS to determine whether or not the subquery EXISTS.
3. Used for subqueries IN to determine whether the subqueries exist IN the specified list.
Used for subqueries in FROM:
SELECT alias. col ,...... FROM (SELECT clause) AS alias WHERE condition
MYSQL is not good at subqueries.

MySQL joint query: combines the results of two or more query statements into one result and outputs the UNION
SELECT clauase union select clause UNION .........

View: it is actually a virtual table with the stored SELECT statement:

MariaDB [mysql]> grant all on hellodb. students TO 'openstack' @ '2017. 16. %. % 'identified BY 'linux ';: grant permissions to openstack users. You can only view the students table MariaDB [hellodb]> revoke all on hellodb. students FROM 'openstack' @ '172. 16. %. % '; Revoke authorization if only a few fields are allowed to be viewed by the user during authorization, the view is used to create a virtual image for the specified authorized user, simply put, a SELECT statement is used as a table, and the result of this SELECT query is saved. When the table is used, it is called a view. MariaDB [hellodb]> create view stu as select StuID, Name, Age, Gender FROM students;: create view; MariaDB [hellodb]> grant all on hellodb. stu TO 'openstack' @ '172. 16. %. % ';: openstack can only query data information in this view.

The view has additional side effects. If the user is authorized to insert data, the user can insert data into the view. In this way, if the dependent data is incomplete, the view cannot store data, and the data should be stored in the base table, that is, the table on which the view depends. Therefore, the view must be accurately planned. Otherwise, some situations may occur.

MySQL lock type: the lock mode applied during the operation
Read lock: it is also called a shared lock. Multiple read operations can be applied at the same time. It is non-blocking.
Write lock: exclusive lock, also called exclusive lock. It is used independently and is blocking.

Lock granularity: the lock granularity can be divided into the following types:
Table lock: table lock. The entire table is locked.
Row lock: row lock. the row to be locked is not necessarily a row or multiple rows.
The smaller the granularity, the higher the overhead, but the better the concurrency. For example, the row required by the lock is the higher overhead.
The larger the granularity, the lower the overhead, but the lower the concurrency. For example, locking the entire table is less overhead.
Therefore, whether in a mysql database or in other relational databases, You need to select an appropriate lock policy for the most appropriate approach, try to find a balance between the lock granularity and its parallelism. Therefore, it is not necessarily better for a table lock than a row lock. It is vital to select a good lock policy.

Based on the mysql implementation level, the lock implementation position is divided into the following categories:
MySQL lock: the locks at this level can be used manually or explicitly.
Storage engine lock: The storage engine automatically uses an implicit lock,
Explicit implementation (Table-Level Lock ):
Mysql> lock tables lock table
Mysql> unlock tables unlock table
Syntax: LOCK TABLES
Tbl_name [[AS] alias] lock_type
[, Tbl_name [[AS] alias] lock_type]...

Lock_type: There are two types of locks: Read locks and write locks.
READ [LOCAL] | [LOW_PRIORITY] WRITE
Mysql> lock tables classes READ; READ LOCK
Mysql> lock tables classes WRITE; WRITE LOCK

The InnoDB Storage engine also supports another explicit lock (locking selected rows) that uses row-level locks:

SELECT... lock in share mode; when this query statement is displayed, SELECT… is performed with an explicit share lock .... For update; MariaDB [hellodb]> select * from classes where ClassID <= 3 lock in share mode; this is only used when the table storage engine is InnoDB and the first three rows are locked, because MyISAM does not support row-level locks, MariaDB [hello]> alter table classes ENGINE 'innodb'; modifies the TABLE storage ENGINE to InnoDB.

Transaction: a Transaction is a group of atomic query statements that have nothing to do with database object creation. It mainly refers to SELECT queries. Multiple queries are considered as an independent unit of work, things are string-shaped. They are executed at the same time only when two things do not involve the same table at all, as long as they involve two things in the same dataset.
ACID test: If the ACID test is met, it indicates that it supports things or is compatible with things.
A: Atomicity. The so-called Atomicity is that A thing must be an inseparable unit, either executed or not executed.
C: Consistency. The so-called Consistency is the Consistency between one state and the other. For example, when the bank transfers funds, the total amount of the two accounts after the transfer is equal to the total amount of the two accounts after the transfer is consistent.
I: Isolation. operations performed by a transaction on all people before they are submitted are invisible. Note: isolation will greatly reduce the concurrency.
D: Durability persistence. Once a transaction is committed, its modifications will be permanently stored in the data, which is permanently valid and will not cause data loss due to other operations.
In general, the higher the data security, the lower the concurrency, and the isolation will greatly reduce the concurrency. Therefore, the concept of isolation level is introduced:

Isolation level. Four isolation levels:
Read uncommitted (read uncommitted) is to READ data that has not been committed by others. Its data security is the worst, but its concurrency is the best. Reading data that has not been committed by others is called dirty READ; there can be non-repeated reads, that is, the data read twice is not the same as that of non-repeated reads; the data displayed is different from the data on the back;
Read committed (READ commit) can be viewed only when someone else submits a transaction. The so-called READ commit is a transaction that can only see modifications to the submitted transaction at the beginning, no other unsubmitted changes can be found and cannot be re-viewed;
Repeatable read (repeable) is the default mysql level. REPEATABLE solves the dirty READ problem, because every READ result is the same, it is called REPEATABLE, this ensures that the data read multiple times is the same data.
SERIALIZABLE (SERIALIZABLE) strictly isolates things from things. The isolation level is very high, and the performance is extremely low. It forces the serial execution of things to avoid phantom reading. Data can be read only when data consistency is ensured.

In one situation: my own things have started, others' things have started, others have changed a data, and what I read is the same, after someone else completes the change, they still read the original form. This is phantom reading, and the data they see is different from what they see.

Mysql> start transaction: start transaction MariaDB [(test)]> COMMIT: submit TRANSACTION MariaDB [(test)]> ROLLBACK: ROLLBACK. You can roll back a TRANSACTION before it is submitted, once the rollback is submitted, the MariaDB [(test)]> SAVEPOINT storage point is not valid. The MariaDB [(test)]> select database (); it shows that if the currently used database does not explicitly start a transaction, every statement will be treated as a proprietary transaction, MariaDB [(test)]> SELECT @ lobal. autocommit; MariaDB [(test)]> show global variables like '% commit %'; MariaDB [(test)]> show global variables like 'tx _ isolation '; MariaDB [ Hellodb]> show global variables like 'tx _ iso % '; view the transaction isolation level of mysql. MariaDB [(test)]> set global autocommit = 0; Disable self-commit. Do not forget to submit manually after starting the transaction; otherwise, data may be lost. Suggestion: Read and submit can be used when there are not strict requirements on things. You must use the function to permanently change the configuration file.

MVCC: Multi-version Concurrency Control
When everything starts, InnoDB provides a snapshot of the current time for every started thing,
To implement this function, InnoDB provides hidden fields for each table, one for saving the creation time of rows, and one for saving the invalidation time, the system version number is stored)
Multi-version concurrency control is only valid at two isolation levels: read committed and REPEATABLE READ

Insert into: replace
First: insert into tb_name [(clo1, col2…)] {VALUES | VALUE} (val1, val2)
Type 2: insert into tb_name SET col1 = val1, col2 = val2 ,......
Third: insert into tb_name SELECT clause

The working mechanism of REPLACE is the same as INSERT. Except for the newly inserted data and the data defined by the primary key or unique index in the table, the data of the old row will be replaced.

UPDATE statement:
UPDATE tb_name SET col_name1 = val1, col_name2 = val2 ,......
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}]...
[WHERE where_condition]
[Order by...]: sort the modified number of rows first.
[LIMIT row_count]: limits the modified row data.
In general, you must use the WHERE clause or use LIMIT to LIMIT the number of rows to be modified.
-- Safe-updates

DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[Order by...]
[LIMIT row_count]
For us, only a single table makes sense. It is the same as UPDATE. If the WHERE condition is not added to limit which rows to delete, all rows will be deleted, you do not need to specify fields for the data to be deleted from a table. Because the data is row data, fields cannot be deleted, and fields can only be cleared or modified, therefore, a row is deleted.
TRUNCATE tb_name: If a field is automatically increased in our table, if you delete a data entry, the newly inserted data is not automatically reset to 1, in this way, we can use this truncate to reset the Automatically increasing value.

MySQL query cache:
It is used to save the complete results returned by the MySQL query statement. When hit, MySQL immediately returns the results. How to check the cache: Use the SELECT statement itself for hash calculation and the calculation result as the key, the query result is used as the value. What types of statements will not be cached? When some uncertain data in the query statement is not cached, such as: NOW (), CURRENT_TIME (), and generally, if a query contains a user-defined function, a storage function, a user variable, a temporary table, a system table in the mysql database, or any table containing permissions, it is generally not cached.

This article from the "warm boiled frog" blog, please be sure to keep this source http://tanxw.blog.51cto.com/4309543/1395568