Lesson two-Introduction to parsing mysqldump commands and Mysqlbinlog commands +innodb and MyISAM storage engines

Environment description

MySQL version: percona-server-5.6.30
ip:10.7.15.167
Port: 3306
Installation directory:/httx/run/mysql
Data Catalog:/httx/run/mysql/data/

Common parameters of Mysqldump

Mysqldump Test--– Study on the difference of –single_transaction parameters open General_log log, track MySQL operation log

(General_log logs make it easy to keep track of all MySQL operations, but production environments are not recommended to open, consume resources, consume memory)

mysql> show variables like ‘%general%‘;+------------------+--------------------------------------+| Variable_name    | Value                                |+------------------+--------------------------------------+| general_log      | OFF                                  || general_log_file | /httx/run/mysql/data/web-test-46.log |+------------------+--------------------------------------+2 rows in set (0.00 sec)mysql> set global general_log=on;     Query OK, 0 rows affected (0.00 sec)

The database test condition table is as follows:

mysql> use test Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| cv             || cv2            || dept           |+----------------+

mysqldump command to export test library data without –single_transaction parameter

 [[email protected] run]#mysqldump --socket=/httx/run/mysql/data/mysql.sock  test >/tmp/test-nosingle.sql

不带--single-transaction参数的mysqldump备份过程：备份前准备：连接到数据库后，设置sql_mode、time_zone等； 开始备份：先查看数据库有哪些表（show tables），根据表挨个备份：备份前先锁表（LOCK TABLES）,且一个表备份完不会释放锁，而    是继续备份下一个表，直到全部备份好，才会UNLOCK tables. 注意：不带--single-transaction参数的mysqldump，备份过程全程锁表且没有创建数据一致性快照，这样会导致备份过程会阻塞数据库和数据不一致。

mysqldump command Export data with –single_transaction parameter

[[email protected] run]#mysqldump --socket=/httx/run/mysql/data/mysql.sock  

带上--single-transaction参数的mysqldump备份过程：备份前准备：连接到数据库后，设置sql_mode、time_zone，设置事务隔离级别为RR；开启一个事务，生成一致性快照，保存数据库此刻的    状态。开始备份：创建保存点（savepoint sp）,先查看数据库有哪些表（show tables），根据表挨个备份：备份过程先备份表结构（show create t able）再备份数据（select * from）,查询时不使用缓存以免备份脏数据（SQL_NO_CACHE）。备份完之后需要回滚到保存点（ROLL BACK         TO SAVEPOINT sp）,然后接着备份下一个表。注意：备份一个表前都要回滚到保存点，这样是为了保证数据的一致性，即使数据没有更新也要回滚。

======================================================================================================

mysqldump Command 1, mysqlbinlog common parameters

推荐查看row模式二进制日志的方式：mysqlbinlog  --base64-output=decode-rowsmysqlbinlog     -v或者mysqlbinlog     -vv

============================================================================================================

Other common commands

=============================================================================================================== =====

Common storage engine 1, InnoDB storage engine

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared with the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.
InnoDB uses MVCC to obtain high concurrency, and implements 4 isolation levels for the SQL standard, while using a strategy called Next-key locking (Gap Lock) to avoid Phantom reads (Phantom). In addition to this, the InnoDB storage engine provides high-performance technologies such as insert buffer, two write (double write), adaptive hash (Adaptive Hash Index), pre-read (read ahead).

(1) Mvcc:multi-version Concurrency control multiple version concurrency controls. Most MySQL transactional storage engines, such as InnoDB and Falcon, do not use a simple row-locking mechanism, but are used with MVCC. MVCC not only in MySQL, but also in Oracle and PostgreSQL, the use of MVCC.MVCC saves a snapshot of the data at a point in time, which means that transactions can be viewed as a consistent view of the data, no matter how long they need to run. This also means that the data for the same table that different transactions see at the same point in time may be different. Each storage engine has different implementations for MVCC, some of which include optimistic and pessimistic concurrency controls. We will show one side of the MVCC work through a simplified version of the InnoDB behavior. InnoDB: Implement MVCC by adding two additional hidden values for each row of records, one to record when this row of data was created, and another to record when this row of data expires (or is deleted). However, InnoDB does not store the actual time at which these events occur, but instead only stores the system version number when these events occur. This is a growing number of transactions as they are created. Each transaction will record its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as the version number of the transaction. Let's take a look at how this policy is applied to specific operations when the isolation level is REPEATABLE READ: (1.1) Select:innodb must ensure that each row of data meets the following 2 conditions: A, InnoDB must find a line version, It must be at least as old as the version of the transaction (that is, its version number is not greater than the version number of the transaction). This ensures that the data is present either before the transaction starts, or when the transaction is created, or when the row data is modified. B. The deleted version of this row of data must be undefined or larger than the transaction version. This ensures that this row of data is not deleted before the transaction begins. Here is not the real delete data, but the sign out of the delete. The true meaning of the deletion is at the time of commit. Rows that meet both conditions may be returned as query results. (1.2) Insert:innodb records the current system version number for this new line. (1.3) Delete:innodb sets the current system version number to the deletion ID for this row. (1.4) Update:innodb will write a new copy of this line of data, the version of which is the current system version number. It also writes this version number to the deleted version of the old row. The result of this extra record is that there is no need to acquire a lock for most queries. They simply read the data as quickly as they can, making sure that only the qualifyingYes. The downside of this scenario is that the storage engine must store more data for each row, do more checking, and handle more of the cleanup. MVCC only works under repeatable Read and read commited isolation levels.    Read uncommited is not MVCC compatible because the query cannot find the row version that is appropriate for their transactional version; they can only read the latest version at a time. Seriablable is also not compatible with MVCC because read operations lock each row of data that they return [1]. (2) Next-key locaking strategy: The gap lock of MySQL InnoDB (Next-key locking) is to prevent Phantom reads (Phantom Read) when MySQL's isolation level is set to repeatable A gap lock is triggered when read. Next-key the specific mode of work is: A, select a non-existent row, then lock all insert behavior, B, with the range Select, such as SELECT * from Test where id>100, will lock all the id>100 insert behavior. In row-level locking, InnoDB uses an algorithm called Next-key locking. InnoDB performs row-level locking in such a way that when it searches or scans the index of a table, it sets a shared or exclusive lock on the index records it encounters. Therefore, row-level locking is actually an index record lock. InnoDB the lock on the index record is also the "gap" before the image index record. If a user has a shared or exclusive lock on record R on an index, another user cannot insert a new index record in the order of the index immediately before R. The locking of this gap is performed to prevent the so-called "phantom Problem". Suppose you want to read and lock all child records from a child table that has an identifier value greater than 100, and then you want to update some columns in the selected row: SELECT * from children where ID > for update; Suppose there is an index in the ID column. The query starts the scan from the first record with an ID greater than 100. If the lock set on the index record does not exclude inserts generated in the gap, a new row may be inserted into the table at the same time. If you perform the same select within the same transaction, you may see a new row in the result package returned by the query. This is contrary to the principle of separation of transactions: a transaction should be able to run so that the data it has read does not change during the transaction. When InnoDB scans an index, it also locks the gap after the last record in the record. Just in the previous example: InnoDB set lock prevents any insertions to the ID that may be larger than100 of the table. You can use Next-key lock to implement a uniqueness check on your application: If you read the data in shared mode and don't see the duplicates of the rows you're about to insert, you can safely insert your row, And know that the Next-key lock set for the inheritors of your line during the reading process prevents anyone from inserting a duplicate of your line at the same time. Therefore, the Next-key lock allows you to lock something that does not exist in your table. InnoDB Features:--support foreign key--Support row lock--support transaction--Non-lock read (read operation does not generate lock)--clustered index structure to store data

2. MyISAM Storage Engine

MyISAM storage Engine, no support for transactions or foreign keys, fast access. There is no requirement for transactional integrity, or a SELECT, insert-based application can use this storage engine to create tables.
Each myisam is stored on disk as 3 files, where the file name and table names are the same, but the extension is:. frm. MYI. MYD

From for notes (Wiz)

Lesson two-Introduction to parsing mysqldump commands and Mysqlbinlog commands +innodb and MyISAM storage engines