mysql基础

1. mysql基础架构

 

2.写（insert， update）数据

redo 日志：

innodb_flush_log_at_trx_commit 1 每次执行事务都持久化

mysql> show variables like 'innodb%log%';

root@lang-McVb:/var/lib/mysql# pwd
/var/lib/mysql
root@lang-McVb:/var/lib/mysql# ls
auto.cnf         employees       ib_logfile0  ibdata1  mysql               sakila  test
debian-5.7.flag  ib_buffer_pool  ib_logfile1  ibtmp1   performance_schema  sys

 

binlog日志：

sync_binlog 1 持久化

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     4054 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                   |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000003 |    4 | Format_desc    |        10 |         123 | Server ver: 5.7.26-0ubuntu0.16.04.1-log, Binlog ver: 4 |
| mysql-bin.000003 |  123 | Previous_gtids |        10 |         154 |                                                        |
| mysql-bin.000003 |  154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| mysql-bin.000003 |  219 | Query          |        10 |         293 | BEGIN                                                  |
| mysql-bin.000003 |  293 | Table_map      |        10 |         350 | table_id: 116 (sakila.city_demo)                       |
| mysql-bin.000003 |  350 | Write_rows     |        10 |         399 | table_id: 116 flags: STMT_END_F                        |
| mysql-bin.000003 |  399 | Xid            |        10 |         430 | COMMIT /* xid=51 */                                    |

 

3.事物级别

MVCC多版本并发控制

未提交读

提交读

可重复度

序列化读

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

长事务

mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 136237
                 trx_state: RUNNING
               trx_started: 2019-07-18 12:10:15
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 7