Shulou(Shulou.com)06/01 Report--

MySQL Storage engine and Log description 1.1 introduction to Storage engine

1.1.1 File system Stora

File system: a mechanism for an operating system to organize and access data. File system is a kind of software.

Type: ext2 3 4, xfs data. No matter what file system is used, the data content will not change, except for the storage space, size, and speed.

1.1.2 mysql database storage

MySQL engine: it can be understood that MySQL's "file system" is just more powerful.

MySQL engine functions: in addition to providing basic access functions, there are more functions such as transaction functions, locking, backup and recovery, optimization and special functions.

1.1.3 types of MySQL storage engines

MySQL provides the following storage engines:

InnoDB, MyISAM (the two most commonly used) MEMORY, ARCHIVE, FEDERATED, EXAMPLE BLACKHOLE, MERGE, NDBCLUSTER, CSV

In addition, a third-party storage engine can be used.

1.1.4 comparison between innodb and myisam

InnoDb engine

Support ACID transactions, support four isolation levels of transactions; support row-level locks and foreign key constraints: so it can support write and concurrency; do not store the total number of rows An InnoDb engine is stored in one filespace (shared tablespace, table size is not controlled by the operating system, and a table may be distributed in multiple files), or multiple (set to independent table empty, table size is limited by operating system file size, generally 2G), limited by operating system file size The primary key index uses a clustered index (the data field of the index stores the data file itself), and the data field of the secondary index stores the value of the primary key; therefore, to find data from the secondary index, you need to find the primary key value through the secondary index, and then access the secondary index; it is best to use a self-increasing primary key to prevent large adjustments to the file in order to maintain the B+ tree structure when inserting data.

The main index structure of Innodb is as follows:

MyISAM engine

Do not support transactions, but each query is atomic; support table-level locking, that is, each operation is to lock the entire table; store the total number of rows of the table; a MYISAM table has three files: index file, table structure file, data file; using Philippine clustered index, the data field of the index file stores pointers to the data file. The secondary index is basically the same as the primary index, but the secondary index does not need to be unique.

The main index structure of MYISAM is as follows:

The two index data lookup processes are as follows:

1.2 innodb Storage engine

After the MySQL5.5 version, the default storage engine provides high reliability and high performance.

1.2.1 benefits of the Innodb engine

A) transaction security (ACID compliant) b) MVCC (Multi-Versioning Concurrency Control Multi-version concurrency control) c) InnoDB row-level locks d) support foreign key referential integrity constraints e) Fast automatic recovery after failure (crash safe recovery) f) buffer pools (buffer pool (data buffer page log buffer page), Undo buffer page) g) maximum performance on large data volumes h) Table queries will be mixed with different storage engines I) Oracle style consistent unlocked reads (shared locks) j) Table data collation to optimize primary key-based queries (clustered indexes)

1.2.2 Overview of Innodb features

Function

Support

Function

Support

Storage limit

64 TB

Index cache

Yes

MVCC

Yes

Data cache

Yes

B-tree index

Yes

Adaptive hash index

Yes

Cluster index

Yes

Copy

Yes

Compressed data

Yes

Update data dictionary

Yes

Encrypted data [b]

Yes

Geospatial data type

Yes

Query cache

Yes

Geospatial index

No

Business

Yes

Full-text search index

Yes

Locking granularity

OK

Cluster database

No

Foreign key

Yes

Backup and recovery

Yes

File format management

Yes

Fast index creation

Yes

Multiple buffer pools

Yes

PERFORMANCE_SCHEMA

Yes

Change buffer

Yes

Automatic fault recovery

Yes

1.2.3 method of querying storage engine

1. Use SELECT to confirm the session storage engine:

SELECT @ @ default_storage_engine; or show variables like'% engine%'

2. Use SHOW to confirm the storage engine of each table:

SHOW CREATE TABLE City\ G SHOW TABLE STATUS LIKE 'CountryLanguage'\ G

3. Use INFORMATION_SCHEMA to confirm the storage engine of each table:

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'City' AND TABLE_SCHEMA =' world_innodb'\ G

4. Migrate from version 5.1 to version 5.5 or above

Suppose that all production tables in version 5.1 database are myisam.

After using mysqldump backup, replace the engine (engine) field in the backed up file from myisam to innodb (you can use the sed command), otherwise the migration is meaningless.

When upgrading the database, we should pay attention to the compatibility of other supporting facilities and whether the code is compatible with the new features.

1.2.4 setting up the storage engine

1. Set the server storage engine in the startup configuration file:

[mysqld] default-storage-engine=

2. Use the SET command to set the current customer session:

SET @ @ storage_engine=

3. Specify: in the CREATE TABLE statement:

CREATE TABLE t (I INT) ENGINE =; 1.3Storage structure of the InnoDB storage engine 1.3.1 InnoDB system tablespace characteristics InnoDB metadata, undo logs, and buffers are stored in the system "tablespace" by default. This is a single logical storage area that can contain one or more files. Each file can be a regular file or an original partition. The last file can be extended automatically. 1.3.2 definition of tablespace

Tablespaces: how MySQL databases are stored

The tablespace contains data files

The relationship between MySQl table space and data file is 1:1

With the exception of shared tablespaces, it is possible to have 1RV N relationships.

1.3.3 Tablespace type

1. Shared tablespaces: ibdata1~ibdataN, usually 2-3

2. Independent tablespaces: stored in the specified library directory, such as city.ibd under the data/world/ directory

Tablespace location (datadir):

Under the data/ directory

1.3.4 Storage contents of system tablespaces

Shared tablespaces (physical storage structure)

Ibdata1~N is often called system tablespace and is generated by data initialization.

System metadata, base table data, data other than table content data.

Tmp tablespaces (generally with little attention)

Undo logs: data-rollback data (used by rollback logs)

Redo logs: ib_logfile0~N stores some redo logs of the system's innodb table.

Note: undo logs are real in ibdata by default, and can be defined separately after 5.6a.

After version 5.7, the tmp tablespace was moved out of ibdata1 and became ibtmp1.

Prior to version 5. 5, all application data was stored in ibdata by default.

Independent tablespace (the function of a storage engine)

After 5.6, a single table is stored separately to a separate tablespace file by default

In addition to system tablespaces, InnoDB creates additional tablespaces in the database directory for .ibd files for each InnoDB table.

Each new table created by InnoDB sets an .ibd file in the database directory to match the. frm file of the table.

You can control this setting using the innodb_file_per_table option, and changing this setting will only change the default values for new tables that have been created.

1.3.5 set up shared tablespaces

View current shared tablespace settings

Mysql > show variables like 'innodb_data_file_path' +-+ | Variable_name | Value | +-+ | Innodb_data_file_path | ibdata1:12M:autoextend | +-+ 1 row in set (0.00 sec)

Set up shared tablespaces:

Generally, the number is configured when the environment is initially built, and the default value is generally 1G; and the last one is automatically extended.

[root@db02 world] # vim / etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

Restart the service to view the current shared tablespace settings

Mysql > show variables like 'innodb_data_file_path' +-+ | Variable_name | Value | + -- + | innodb_data_file_path | ibdata1:76M Ibdata2:100M:autoextend | +-+-- + 1 row in set (0.00 sec)

1.3.6 set up independent tablespaces

Independent tablespaces are enabled by default in version 5.6.

Note for independent tablespaces: if independent tablespaces are not opened, shared tablespaces will take up a lot of space.

Mysql > show variables like'% per_table%' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_file_per_table | ON | +-- + 1 row in set (0.00 sec)

Independent tablespaces can be controlled in the parameter file / etc/my.cnf

Turn off independent tablespaces (0 is off, 1 is on)

[root@db02 clsn] # vim / etc/my.cnf [mysqld] innodb_file_per_table=0

View standalone tablespace configuration

Mysql > show variables like'% per_table%' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_file_per_table | OFF | +-- + 1 row in set (0.00 sec)

Summary:

Innodb_file_per_table=0 closes independent tablespaces innodb_file_per_table=1 to open independent tablespaces, and a single form stores transactions in 1.4 MySQL

A set of steps for performing data operations, which are treated as a unit of work

Used to group multiple statements and can be used when multiple clients access data in the same table concurrently.

All steps succeed or fail

If all steps are normal, execute, or cancel if the steps are incorrect or incomplete.

To put it simply, a transaction is to ensure that the statements in the unit of work succeed or fail at the same time.

Schematic diagram of transaction process

1.4.1 what is a transaction

Instead of defining a transaction, it is better to talk about the characteristics of the transaction. As we all know, transactions need to meet the four characteristics of ACID.

A (atomicity) atomicity.

The execution of a transaction is regarded as an indivisible minimum unit. Either all the operations in the transaction are executed successfully or all of them fail to roll back, and you cannot perform only some of them.

All statements are executed successfully or cancelled as a unit. Updata T1 set money=10000-17 where id=wxid1 updata T1 set money=10000+17 where id=wxid2

C (consistency) consistency.

The execution of a transaction should not break the integrity constraints of the database. If the system crashes after the second operation in the above example, make sure that the total money of An and B will not change.

If the database is in a consistent state at the beginning of the transaction, the consistent state is retained during the execution of the transaction. Updata T1 set money=10000-17 where id=wxid1 updata T1 set money=10000+17 where id=wxid2 check your account or 10000 during the above operation

I (isolation) isolation.

In general, the behavior of transactions should not affect each other. In practice, however, the degree to which transactions interact with each other is affected by the isolation level. It will be discussed in detail later in the article.

Transactions do not affect each other. When others do anything to the two accounts, under different isolation conditions, the consistency guarantee may be different.

Isolation level

The isolation level affects consistency. A level that read-uncommit X read-commit may use the default level of repeatable-read, the same strict default of SERIALIZABLE as oracle, which is generally not used

This rule is controlled not only by the isolation level, but also by the lock. Think of the implementation of NFS.

D (durability) persistence.

After the transaction is committed, the committed transaction needs to be persisted to disk. Even if the system crashes, the submitted data should not be lost.

The real security of the transaction is to ensure that the data is on the ground. 1.4.2 transaction control statement

Common transaction control statements:

START TRANSACTION (or BEGIN): explicitly start a new transaction COMMIT: permanently record changes made by the current transaction (transaction ends successfully) ROLLBACK: cancel changes made by the current transaction (transaction failed end)

Transaction control statements you need to know:

SAVEPOINT: allocate a location during a transaction for future reference ROLLBACK TO SAVEPOINT: cancel changes made after savepoint RELEASE SAVEPOINT: delete savepoint identifier SET AUTOCOMMIT: disable or enable the default autocommit mode 1.4.3 autocommit parameter for the current connection

At the beginning of MySQL5.5, begin or start transaction statements are no longer required to open a transaction. Also, Autocommit mode is turned on by default, implicitly committing each statement as a transaction.

In some busy enterprise scenarios, this configuration may have a significant impact on performance, but there is a significant improvement in security. In the future, we need to weigh our business needs to adjust whether to submit automatically.

Note: in production, according to the actual demand to choose whether to open or not, the general banking business will choose to close.

View the current autocommit status:

Mysql > show variables like'% autoc%'; +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in set (0.00 sec)

Modify the configuration file and restart

[root@db02 world] # vim / etc/my.cnf [mysqld] autocommit=0

Check the autocommit status again

Mysql > show variables like'% autoc%'; +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.00 sec) mysql > select @ @ autocommit +-+ | @ @ autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec)

Description: autocommit is set to enable comparison

Advantages: data security is good, and every modification will hit the ground.

Disadvantages: can not conduct banking transactions, generate a large number of small IO

1.4.4 non-transactional statements that caused the submission: DDL statements: (ALTER, CREATE and DROP) DCL statements: (GRANT, REVOKE and SET PASSWORD) locking statements: (LOCK TABLES and UNLOCK TABLES)

Example of a statement that causes an implicit submission:

TRUNCATE TABLE LOAD DATA INFILE SELECT FOR UPDATE

SQL statement for implicit submission:

START TRANSACTION SET AUTOCOMMIT = 11.5redo and undo

1.5.1 transaction Log undo

Undo principle:

The principle of Undo Log is simple: in order to satisfy the atomicity of the transaction, before manipulating any data, first backup the data to a place (the place where the backup of the data is called Undo Log). Then modify the data.

If an error occurs or the user executes the ROLLBACK statement, the system can use the backup in Undo Log to restore the data to the state it was before the transaction began.

In addition to ensuring the atomicity of transactions, Undo Log can also be used to assist in the persistence of transactions.

What is undo?

Undo, as its name implies, rollback log, is a kind of transaction log.

What is the function?

In the process of transactional ACID, the function of "A" atomicity is realized.

Using Undo Log to simplify the process of atomicity and persistence transactions

Suppose there are two data An and B, and the values are 1 and 2 respectively. a. The business begins. b. Record Agg1 to undo log. c. Modify Agg3. D. Record bread2 to undo log. e. Modify Bamboo 4. F. Write undo log to disk. g. Write the data to disk. h. Transaction commit

There is an implicit prerequisite: 'data is first read into memory, then modified, and finally written back to disk to ensure atomicity and persistence because of the following characteristics:

a. Record the Undo log before updating the data. b. To ensure persistence, the data must be written to disk before the transaction commits. As long as the transaction is successfully committed, the data must have been persisted. C. Undo log must be persisted to disk before the data is persisted. If the system crashes between GMAH, the undo log is complete and can be used to roll back transactions. d. If the system crashes between Amurf because the data is not persisted to disk. So the data on disk remains the same as it was before the transaction started.

Defect:

Data and Undo Log are written to disk before each transaction is committed, which results in a large amount of disk IO, so performance is very low. If you can cache data for a period of time, you can reduce IO and improve performance. But this loses the durability of the transaction.

Therefore, another mechanism is introduced to achieve persistence, namely Redo Log.

1.5.2 transaction Log redo

Redo principle:

In contrast to Undo Log, Redo Log records a backup of new data. Just persist the Redo Log before the transaction is committed, and there is no need to persist the data. When the system crashes, the data is not persisted, but the Redo Log is persisted.

The system can restore all data to the latest state according to the content of Redo Log.

What is Redo?

Redo, as the name implies, "redo log", is a kind of transaction log.

What is the function?

In the process of transactional ACID, the role of "D" persistence is realized.

Simplified process of Undo + Redo transaction

Suppose there are two data An and B, and the values are 1pm and 2.A respectively. The business begins. b. Record Agg1 to undo log. c. Modify Agg3. D. Record Aban 3 to redo log. e. Record bread2 to undo log. f. Modify Bamboo 4. G. Record bread4 to redo log. h. Write redo log to disk. i. Transaction commit

The characteristics of Undo + Redo transaction

a. To ensure persistence, the Redo Log must be persisted before the transaction commits. b. Data does not need to be written to disk before the transaction is committed, but is cached in memory. C. Redo Log guarantees the persistence of transactions. D. Undo Log guarantees the atomicity of the transaction. e. An implicit feature is that data must be written to persistent storage later than redo log.

Whether redo is persisted to disk parameters

Locks in innodb_flush_log_at_trx_commit=1/0/21.5.3 transactions

What is a lock?

As the name implies, "lock" means locking.

What is the function of "lock"?

In the process of transactional ACID, "lock" and "isolation level" work together to achieve "I" isolation.

Granularity of the lock:

1. MyIasm: low concurrency lock-table level lock

2. Innodb: high concurrency lock-row level lock

Four isolation levels:

READ UNCOMMITTED allows transactions to view uncommitted changes made by other transactions READ COMMITTED allows transactions to view committed changes made by other transactions REPEATABLE READ* ensures that the SELECT output of each transaction is consistent; the default level of InnoDB, SERIALIZABLE, completely isolates the results of one transaction from other transactions

Overhead, locking speed, deadlock, granularity, concurrency performance

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency. Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest. Page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

As can be seen from the above characteristics, it is difficult to say in general which kind of lock is better, only in terms of the characteristics of the specific application, which kind of lock is more appropriate!

Only from the point of view of locks: table-level locks are more suitable for applications that focus on queries, with only a small amount of data updated according to index conditions, such as Web applications, while row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

1.6 MySQL Log Management 1.6.1 introduction to MySQL Log types

Description of the type of log:

Log file

Option

File name

program

N/A

Table name

Error

-- log-error

Host_name.err

N/A

Routine

-- general_log

Host_name.log

Mysqldumpslow

Mysqlbinlog

General_log

Slow query

-- slow_query_log

-- long_query_time

Host_name-slow.log

N/A

program

Slow_log

Binary system

-- log-bin

-- expire-logs-days

Host_name-bin.000001

N/A

Audit

-- audit_log

-- audit_log_file

Audit.log

N/A

1.6.2 configuration method status error log: [mysqld] log-error=/data/mysql/mysql.log

View the configuration method:

Mysql > show variables like'% log%error%'

Function:

It is important for us to record the general status information and error information of mysql database.

According to the common log of routine error processing in the library.

Mysql > show variables like'% log%err%' +-+ | Variable_name | Value | +-+- -+ | binlog_error_action | IGNORE_ERROR | | log_error | / application/mysql/data/db02.err | +-+-- + 2 rows in set (0.00 sec)

1.6.3 General query log

Configuration method:

[mysqld] general_log=on general_log_file=/data/mysql/server2.log

View the configuration method:

Show variables like'% gen%'

Function:

Record the information of all successful SQL statements in mysql, which can be used for auditing, but we rarely open them.

Mysql > show variables like'% gen%' +-+-+ | Variable_name | Value | +-+ -+ | general_log | OFF | | general_log_file | / application/mysql/data/db02.log | +-+-- + 2 rows in set (0.00 sec)

1.7 binary log

Binary logs are not dependent on the storage engine.

Rely on the sql layer to record information related to sql statements

The role of binlog logs:

1. Provide backup function

2. Master-slave replication

3. Arbitrary recovery based on point in time

Record completed statements that have been executed at the SQL layer, and if it is a transaction, record completed transactions.

Function: point-in-time backup and point-in-time recovery, master-slave

The "master gate" of binary log

Function:

1. Whether to enable 2, binary log path / data/mysql/ 3, binary log file name prefix mysql-bin 4, file name with "prefix" .000001 ~ N log-bin=/data/mysql/mysql-bin

"separate off" of binary logs:

It makes sense only when the main gate is opened, and the default is open. We shut it down temporarily sometimes. Only the current session is affected. Format of sql_log_bin=1/01.7.1 binary log

Statement, statement mode:

The recording information is concise, recording the SQL statement itself. However, if there is a function operation in the statement, it is possible that the recorded data is not accurate. 5.6. the default mode is, but it is used cautiously in production environment. It is recommended to change to row.

Row, row mode

The process of changing the row data in the table. The recording data is detailed and requires high IO performance. The recording data is accurate in any case. This mode is generally used in production. 5.7 the default mode after.

Mixed, mixed mode

After judgment, a recording mode mixed with row+statement is selected. (generally not used) 1.7.2 Open binary log

Mysql > show variables like'% log_bin%' +-- +-+ | Variable_name | Value | +-- +-+ | log_bin | OFF | | log_bin_basename | log_bin_index | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +-- | -+-+ 6 rows in set (0.00 sec)

Modify the configuration file to open the binary log

[root@db02 tmp] # vim / etc/my.cnf [mysqld] log-bin=/application/mysql/data/mysql-bin

The method of command line modification

Mysql > SET GLOBAL binlog_format = 'STATEMENT' mysql > SET GLOBAL binlog_format =' ROW'; mysql > SET GLOBAL binlog_format = 'MIXED'

View the type of file binary log

[root@db02 data] # file mysql-bin.* mysql-bin.000001: MySQL replication log mysql-bin.index: ASCII text

View the configuration of MySQL:

Mysql > show variables like'% log_bin%' +-- +-+ | Variable_name | Value | +- -+ | log_bin | ON | | log_bin_basename | / application / mysql/data/mysql-bin | | log_bin_index | / application/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | | ON | +-- +-- + 6 rows in set (0.00 sec) |

1.7.3 define how to record

View the current format

Mysql > show variables like'% format%' +-- +-+ | Variable_name | Value | +-- +-+ | binlog_format | | STATEMENT | | date_format |% Y-%m-%d | | datetime_format |% Y-%m-%d% H:%i:%s | | default_week_format | 0 | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_ | Max | Antelope | | time_format |% H:%i:%s | +-- +-+ 8 rows in set (0.00 sec)

Modify format

[root@db02 data] # vim / etc/my.cnf [mysqld] binlog_format=row

Check it after you change it.

Mysql > show variables like'% format%' +-- +-+ | Variable_name | Value | +-- +-+ | binlog_format | | ROW | | date_format |% Y-%m-%d | | datetime_format |% Y-%m-%d% H:%i:%s | | default_week_format | 0 | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file | _ format_max | Antelope | | time_format |% H:%i:%s | +-- +-+ 8 rows in set (0.00 sec)

1.8 Operation of binary logs 1.8.1 View

View at the operating system level

[root@db02 data] # ll mysql-bin.*-rw-rw---- 1 mysql mysql 143 Dec 20 20:17 mysql-bin.000001-rw-rw---- 1 mysql mysql 120 Dec 20 20:17 mysql-bin.000002-rw-rw---- 1 mysql mysql 82 Dec 20 20:17 mysql-bin.index

Refresh the log

Mysql > flush logs

Refresh the log directory after completion

[root@db02 data] # ll mysql-bin.*-rw-rw---- 1 mysql mysql 143Dec 20 20:17 mysql-bin.000001-rw-rw---- 1 mysql mysql 167 Dec 20 20:24 mysql-bin.000002-rw-rw---- 1 mysql mysql 20 Dec 20 20:24 mysql-bin.000003-rw-rw---- 1 mysql mysql 123 Dec 20 20:24 mysql-bin.index [root@db02 data] #

View the binary log files currently in use

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000003 | 120 | +-- -+ 1 row in set (0.00 sec)

View all binary log files

Mysql > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 143 | mysql-bin.000002 | 167 | | mysql-bin.000003 | 120 | +- -+-+ 3 rows in set (0.00 sec)

1.8.2 View the contents of binary log

The noun describes:

1. Events event

How binary logs are defined: the minimum generating unit of a command

2 、 position

The location number that each event wants to correspond to in the entire binary is the position number

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000003 | 120 | +-- -+ 1 row in set (0.00 sec) [root@db02 data] # mysqlbinlog mysql-bin.000003 > / tmp/aa.ttt

Export all the information

[root@db02 data] # mysqlbinlog mysql-bin.000003 > / tmp/aa.ttt

How to view binlog:

1. View the original information of binlog

Mysqbin mysql-bin.000002

2. In row mode, translate into sentences

Mysqlbinlog-- base64-output='decode-rows'-v mysql-bin.000002

3. View binlog events

Show binary logs; all binlog information in use show binlog events in 'log file'

4. How to intercept binlog content and restore it according to demand (conventional way of thinking)

(1), show binary logs; show master status

(2) looking back and forward, show binlog events in finds the misoperated transaction, and judges the start position and the end position of the transaction

(3) get rid of the misoperation and leave the normal operation in 2 sql files

(4) first, the test database is restored, the misoperated data is exported, and then the production is restored.

Problems encountered using the above methods:

Recovery events are long

It has a certain impact on production data, and there may be redundant data.

A better solution.

1. Flashback flashback function

2. Delay the slave database through backup

1.8.3 the method of intercepting binary logs by mysqlbinlog

The common options for mysqlbinlog are as follows:

Parameters.

Parameter description

-- start-datetime

Reads from the binary log at a specified time equal to or later than the local computer

-- stop-datetime

The time value specified to be less than or equal to the local computer from the binary log is the same as above.

-- start-position

Reads the specified position event location from the binary log as a start.

-- stop-position

Read the specified position event location from the binary log as the event expires

Binary log file example: mysqlbinlog-- start-position=120-- stop-position= end number

1.8.4 Delete binary log

By default, old log files are not deleted.

Delete the log based on the time it exists:

SET GLOBAL expire_logs_days = 7; or PURGE BINARY LOGS BEFORE now ()-INTERVAL 3 day

Delete the log based on the file name:

PURGE BINARY LOGS TO 'mysql-bin.000010'

Reset the binary log count, count from 1, and delete the original binary log.

Reset master1.9 mysql's slow query log (slow log) 1.9.1 what is this?

Slow-log records slow sql statements within all conditions

A tool log for optimization. It can help us locate the problem.

1.9.2 slow query log

Is to record the relevant SQL statements in the mysql server that affect the performance of the database to a log file

Through the analysis of these special SQL statements, it is improved to improve the performance of the database. Slow log settings

Long_query_time: set the threshold for slow query. The SQL that exceeds the set value is recorded in the slow log. The default value is 10s slow_query_log: specify whether to enable the slow log slow_query_log_file: specify the location where slow log files are stored, which can be empty The system will give a default file host_name-slow.log min_examined_row_limit: the query checks whether the SQL that returns less than the specified line of this parameter is not recorded in the slow log log_queries_not_using_indexes: whether the slow log that does not use the index is recorded to the index

Slow query log configuration

[root@db02 htdocs] # vim / etc/my.cnf slow_query_log=ON slow_query_log_file=/tmp/slow.log long_query_time=0.5 # controls the threshold log_queries_not_using_indexes for slow logging

Restart the service after the configuration is complete.

Check whether the slow query log is enabled and its location.

Mysql > show variables like'% slow%'-> +-- +-+ | Variable_name | Value | +-- +-+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | / tmp/slow.log | +-- +-+ 5 rows in set (0.00 sec) |

1.9.3 mysqldumpslow command / path/mysqldumpslow-s c-t 10 / database/mysql/slow-log

This outputs the 10 SQL statements with the most records, of which:

Parameters.

Description

S

C, t, l, r are sorted according to the number of records, time, and query, respectively.

Sort by time and the number of records returned. Ac, at, al and ar indicate the corresponding flashbacks.

-t

It means top n, that is, how many pieces of data are returned before.

-g

A regular matching pattern can be written later, which is case-insensitive.

Example:

/ path/mysqldumpslow-s r-t 10 / database/mysql/slow-log

Get the 10 queries that return the most recordsets.

/ path/mysqldumpslow-s t-t 10-g "left

Join "/ database/mysql/slow-log

Get the query statements with left links in the first 10 items sorted by time.

1.9.4 how to ensure the consistency of committed transactions between binlog and redolog

When binlog is not enabled, when commit is executed, it is considered that the redo log is persisted to a disk file, and the commit command is successful.

Write binlog parameters:

Mysql > show variables like'% sync_binlog%'; +-+-+ | Variable_name | Value | +-+-+ | sync_binlog | 0 | # Control binlog commit phase +-+-+ 1 row in set (0.00 sec)

Sync_binlog ensures that every committed transaction is written to binlog.

The double one standard in 1.9.5 mysql:

Innodb_flush_log_at_trx_commit and sync_binlog are the key parameters that control MySQL disk writing policy and data security.

Description of parameter meaning:

Innodb_flush_log_at_trx_commit=1

If innodb_flush_log_at_trx_commit is set to 0 innodb_flush_log_at_trx_commit log buffer will be written to log file once a second, and the flush (brush to disk) operation of log file will occur at the same time. In this mode, writing to disk is not actively triggered when the transaction is committed.

If innodb_flush_log_at_trx_commit is set to 1, MySQL will write log buffer data to log file and flush (flush to disk) each time the transaction commits.

If innodb_flush_log_at_trx_commit is set to 2, MySQL writes log buffer data to log file. But the flush (flush to disk) operation does not happen at the same time. In this mode, MySQL performs a flush (flush to disk) operation once a second.

Note:

Due to the problem of process scheduling policy, this "flush (flush to disk) operation per second" does not guarantee 100% "per second".

Description of parameter meaning:

Sync_binlog = 1

The default value of sync_binlog is 0, and like the operating system's mechanism for brushing other files, MySQL does not synchronize to disk but relies on the operating system to refresh binary log.

When sync_binlog = N (N > 0), MySQL uses the fdatasync () function to synchronize its write binary log binary log to disk every time it writes N binary log binary log.

Note:

If autocommit is enabled, each statement statement will have one write operation; otherwise, each transaction will have a write operation.

Description of security

It is most secure when both innodb_flush_log_at_trx_commit and sync_binlog are 1. In the case of a mysqld service crash or a server host crash, binary log can only lose at most one statement or transaction. But you can't have both. Double 11 will lead to frequent io operations, so this mode is also the slowest way.

When innodb_flush_log_at_trx_commit is set to 0memmysqld process, the crash will result in the loss of all transaction data in the previous second.

When innodb_flush_log_at_trx_commit is set to 2, all transaction data can be lost in the last second only if the operating system crashes or the system is powered off.

Double 1 is suitable for very high data security requirements, and the disk IO write capacity is sufficient to support business, such as order, transaction, recharge, payment consumption system. In double-1 mode, when the disk IO cannot meet the business requirements, such as the pressure of 11.11 activities. The recommended practice is innodb_flush_log_at_trx_commit=2, sync_binlog=N (N = 500 or 1000) and use a cache cache with battery backup power to prevent abnormal system power outages.

System performance and data security are necessary factors for the high availability and stability of business systems. We need to find a balance point for the optimization of the system, and the appropriate one is the best. According to the needs of different business scenarios, the two parameters can be combined and adjusted to optimize the performance of the db system.

1.10 references

Implementing pt-query-digest with https://www.cnblogs.com/wangdake-qq/p/7358322.html http://www.jb51.net/article/87653.htm http://www.mysqlops.com/2012/04/06/innodb-log1.html https://www.cnblogs.com/Bozh/archive/2013/03/18/2966494.html https://www.cnblogs.com/andy6/p/6626848.html https://www.cnblogs.com/xuanzhi201111/p/4128894.html Anemometer Graphical http://www.coooz.com/archives/771 double-one standard

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.