MySQL series 9.mysql Log & Storage Engine

Mysqlbinlog

is what ?

The following files are in the data directory: mysql-bin.xxxxxx

function ?

Record database internal additions and deletions to check MySQL database for updated content records

three different modes ?

statement level mode: The disadvantage is not enough detail

row level mode:

The log records the form in which each row of data is modified

(changing multiple lines in a row is actually a statement, but it will be recorded as multiple lines)

Pros: Record the details of each line to record function/trigger/stored procedures

mixed level mode: Mixed mode, automatically determine whether to use statement or row level

Three modes of configuration ?

log-bin=mysql-"MIXED"

Online modification takes effect immediately

Mysql> set global binlog_format="MIXED";

MySQL Log

error Log :

[Mysqld_safe]

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

Binary Log :

Log-bin=mysql-bin

Query log:

Normal log:

Gerneral_log = OFF #一般不开启, no effect, and the file is particularly large

Gerneral_log_file =/usr/local/mysql/data/mysql.log

Slow query log :

Long_query_time = 1

log-slow-queries =/usr/local/mysql/data/slow.log

log_queries_not_using_indexes

Storage Engine

Architecture diagram

four characteristics of a transaction (ACID)

Atomic Nature

Consistency

Isolation of

Durability

Start transaction;

Commit

Rollback

MyISAM Engine Features

Transaction not supported

Table-Level locking

Read and write blocking each other

Only the index is cached

Reads faster and consumes less resources

FOREIGN KEY constraints are not supported, but full-text indexing is supported

MyISAM engine-suitable production environment

No transaction support required

Generally for more reading data or write more than the occasion

Read and write concurrent access to relatively low business (lock mechanism issues)

Less data modification of the business (blocking issues)

Read-oriented business

Machines with poor hardware resources

Summary: For a purely read or write-only business

MyISAM Tuning

Set the appropriate index (caching mechanism)

Adjust read-Write priority

Enable deferred insertion (as many data writes at a time)

Keep the insert data written to the tail and reduce blocking as much as possible in sequential operations

Decomposition of large operations to reduce blocking time for individual operations

Reduce the number of concurrent (use queue mechanism)

For relatively static data, make full use of query_cache or memcached services for caching

Query_cache_size=2m

query_cache_limit=1m

query_cache_min_res_unit=2k

The count of MyISAM is particularly efficient when full-table scans are available (i.e., you cannot take a where condition)

The master-slave synchronization can be used InnoDB, using MyISAM from the library (copying data back and forth can be cumbersome, not recommended!!)

InnoDB engine

Support Transactions

Row-level locking (full table scan is still a table lock)

Read-write blocking is related to transaction isolation level

Very efficient caching features: ability to cache indexes and data

The entire table and primary key are stored in cluster form, forming a balanced tree

All Secondary index will hold the primary key information

Supports partitioning, table space

Support for FOREIGN KEY constraints, full-text indexing is supported after version 5.5

Compared with MyISAM, INNODB has higher hardware requirements

InnoDB engine-suitable production environment

Business that requires transaction support

High concurrency scenarios (make sure the index is already established)

Scenarios where data is read and written more frequently

Business with high data consistency requirements

Good hardware, can take advantage of InnoDB cache capacity

InnoDB Tuning

The primary key is as small as possible to avoid excessive space burden on secondary index

Avoid full table scans because table locks are used

Cache all indexes and data as much as possible

In large batches of small inserts, try to control their own affairs

Set the Innodb_flush_log_at_trx_commit parameter reasonably (refresh log to disk every few seconds), do not set to 0, that is, do not over-pursue security

Avoid primary key updates, as this can result in a lot of data movement

InnoDB Important Parameters

innodb_buffer_pool_size = 2048M #缓存池, set to 50%--80% of total machine memory

Bulk change Storage Engine

Act 1: alter

ALERT TABLE tb1 Engine=innodb;

Method 2: Replace the backup content with sed

Nohup sed-e '/smyisam/innodb/g ' tb1.sql > Tb1_1.sql

Method 3: mysql_convert_table_format command

Mysql_convert_table_format--user=root--password=123456--socket=/data/3306/mysql.sock--engine=MyISAM db1 tb1

;

MySQL series 9.mysql Log & Storage Engine