MySQL log, authorization, cache, transaction, engine

One, the MySQL engine

MySQL's two main engines: MyISAM and InnoDB

To view the MySQL engine:

1. View the supported engines: show engines;

2. View the engine of the table: Show table status from DATABASE [where name= ' |like '];

Show create table table name;

Show variables like '%storage_engine% ';

MyISAM Features:

1. MyISAM does not support transactions

2. There are three tables created by the MyISAM engine:

USER.FRM storage is the structure of the table

User. MYI stores the index of a table

User. MYD stores data for a table

3, MyISAM support table-level lock

4. Support Full-Text indexing

InnoDB Features:

1. Support short transaction, support high concurrency MVVC (multiple version concurrency control), four isolation levels

2. The created table has two

Structure of the USER.FRM table

Data and index of USER.IBD table

3, row-level lock

4. Using the clustered index

Second, MySQL's authorization

Syntax: GRANT priv_type[,...] On [{table|function|procedure}] db. {Table|routine} To ' USERNAME ' @ ' HOST ' [identified by ' password '][require SSL] [with With_option]

With_option:

GRANT OPTION

| Max_queries_per_hour Count

| Max_updates_per_hour Count

| Max_connections_per_hour Count

| Max_user_connections Count

Third, MySQL's business

Four elements of a transaction: A (atomicity atomicity) C (consistency conformance) I (Isolation Isolation) D (permanent durability)

Isolation level of the transaction:

1. Read not submitted

2. Read Submit

3, can be stressed

4, can be serialized

Problems that may exist:

Dirty Read

Non-REPEATABLE READ

Phantom reading

Locking Read

MySQL's isolation level defaults to repeatable_read, viewing commands for show VARIABLES like ' tx_isolation ';

Start MySQL transaction: Start tarnsaction;

End transaction: 1) COMMIT 2) ROLLBACK

Iv. MySQL Cache

Query cache related server variables; SHOW GLOBAL VARIABLES like '%query% ';

------------------------------+---------+

| variable_name | Value |

+------------------------------+---------+

| Query_alloc_block_size | 16384 |

| Query_cache_limit | 1048576 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 1048576 |

| query_cache_strip_comments | OFF |

| Query_cache_type | OFF |

| Query_cache_wlock_invalidate | OFF |

| Query_prealloc_size | 24576 |

+------------------------------+---------+

Query cache Status: SHOW GLOBAL status like '%qcache% ';

+-------------------------+---------+

| variable_name | Value |

+-------------------------+---------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 1031336 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| qcache_not_cached | 0 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

+-------------------------+---------+

To adjust the logic of a parameter:

V. MySQL logs

1. Query log

Open Query log: SHOW VARIABLES like '%general_log% '

+------------------+-----------+

| variable_name | Value |

+------------------+-----------+

| General_log | OFF |

| General_log_file | Node4.log |

+------------------+-----------+

Log_output table| File query log output files type

2. Slow query log

Turn on slow query log: SHOW GLOBAL VARIABLES like '%slow_query_log% ';

+---------------------+----------------+

| variable_name | Value |

+---------------------+----------------+

| Slow_query_log | OFF |

| Slow_query_log_file | Node4-slow.log |

+---------------------+----------------+

Set Slow_query_log=on or edit/etc/my.cnf

Long_query_time | 10.000000 query over how long to record the default is 10s

3. Binary Log

Open binary log: Edit/etc/my.cnf bin_log=/data/mydata/my_bin

View show VARIABLES like '%bin% ';

View binary logs: Mybinlog

Mysqlbinlong--start-position=# my_bin.000001

--stop-position=#

--start-datetime= ' Yyyy-mm-dd hh:mm:ss '

The status of the binary log currently being used by SHOW MASTER status

Show binary LOGS view used binary logs

4. Use when relaying log replication

How to turn on the trunk log: vim/etc/my.cnf Relaylog=relay_log

5. Error log

6. Transaction log

MySQL Forgot password:

1, >mysqld_safe--skip-grant-tables--skip-networking;

>use MySQL;

>update user SET Password=password (' Newpass ') where user= ' root ';

>flush privileges;

Terminate process safe process, restart MySQL

Yum Source installation mariadb:

1, Vim/etc/yum.repos.d/mariadb.repo

[Mariadb]name = Mariadbbaseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb//mariadb-10.2.10/yum/centos/6/x86_ 64gpgcheck=0

2. Installation and development environment and MARIADB Yum install

3, configure/ETC/MY.CNF.D/SERVER.CNF, add the following three lines under Mysqld:

Datadir=/data/mydata

Skip_name_resolve=on

Innodb_file_per_table=on #InnoDB每个表单独使用一个表空间存储数据及索引 advanced features can be used after you turn on this entry

4, change MyData of the genus Chown-r Mysql:mysql/data/mydata

5, run the installation program/usr/bin/mysql_install_db--user=mysql--datadir=/data/mydata

Do not install will not start the MySQL prompt blocked

How MySQL imports the database:

1, Mysql-u-P hellodb

2, Mysql-u-P Mysql>source hellodb.sql

MySQL log, authorization, cache, transaction, engine