代码拉取完成，页面将自动刷新
zhonghuadong@LAPTOP-PQIEGIQ8:/etc/mysql$ ll ----------------------------------------------- total 8 drwxr-xr-x 1 root root 4096 Jul 13 16:31 ./ drwxr-xr-x 1 root root 4096 Jul 14 16:40 ../ drwxr-xr-x 1 root root 4096 Jul 13 16:31 conf.d/ -rwxr-xr-x 1 root root 120 May 4 20:36 debian-start* -rw------- 1 root root 317 Jul 13 16:31 debian.cnf lrwxrwxrwx 1 root root 24 Jul 13 16:31 my.cnf -> /etc/alternatives/my.cnf //做了一个软链接 -rw-r--r-- 1 root root 839 Aug 3 2016 my.cnf.fallback -rw-r--r-- 1 root root 682 Mar 11 04:45 mysql.cnf drwxr-xr-x 1 root root 4096 Jul 13 16:31 mysql.conf.d/
zhonghuadong@LAPTOP-PQIEGIQ8:/etc/mysql/mysql.conf.d$ cat mysqld.cnf ----------------------------------------------------- [mysqld] # # * Basic Settings # user = mysql # pid-file = /var/run/mysqld/mysqld.pid # socket = /var/run/mysqld/mysqld.sock # port = 3306 # datadir = /var/lib/mysql
systemctl mysqld restart
通常我们会用如下命令使用 mysql 客户端
mysql -h 127.0.0.1 -P 3306 -uroot -p
如果出现如下错误
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) # 说明 /etc/my.cnf 中路径配置不对，建议重新初始化 mysql
则是因为对应目录下的 mysql.sock 找不到引起的 (这个目录下根本没有 mysql.sock这个文件)。
mysql -uroot -p -S /tmp/mysql.sock.bak // 备份文件
mysqldump 是将数据表导成 SQL 脚本文件
导出到文件中（select into outfile） SELECT fields INTO OUTFILE 'file_name' [{FIELDS | COLUMNS} 字段 [TERMINATED BY 'string'] 字段之间分隔符号 [[OPTIONALLY] ENCLOSED BY 'char'] 字段被包含在char中间 [ESCAPED BY 'char'] 忽略字段里出现的char ] [LINES [STARTING BY 'string'] 忽略开头是string的行 [TERMINATED BY 'string'] 行分隔符 ] FROM test_table; 导入文件中的数据到mysql表 LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] 遇到重复的时候处理方法，替换或者是忽略 INTO TABLE tbl_name 导入数据的目的表名 [PARTITION (partition_name,...)] 分区选择 [CHARACTER SET charset_name] 字符集 [{FIELDS | COLUMNS} 字段 [TERMINATED BY 'string'] 字段之间分隔符号 [[OPTIONALLY] ENCLOSED BY 'char'] 字段被包含在char中间 [ESCAPED BY 'char'] 忽略字段里出现的char ] [LINES [STARTING BY 'string'] 忽略开头是string的行 [TERMINATED BY 'string'] 行分隔符 ] [IGNORE number {LINES | ROWS}] 忽略行/列 [(col_name_or_user_var,...)] 目的表的表字段名或者用户变量名 [SET col_name = expr,...] 设置表字段值
备份数据命令
-- 备份一个表 mysqldump -u root -p [db_name] [table_name] > [导出目录] -- 例如 mysqldump -u root -p ydlclass ydl_user > ~/dump.txt ------------------------------------------------------- -- 备份一个数据库 mysqldump -u root -p [db_name] > [导出目录] -- 例如 mysqldump -u root -p ydlclass > ~/dump.txt --------------------------------------------------------- -- 备份所有数据库 mysqldump -u root -p --all-databases > [导出目录] -- 例如 mysqldump -u root -p --all-databases > dump.txt
mysql -u root -p [数据库名] < 备份文件 -- 例如 mysql -u root -p ydl < ~/dump.txt
MariaDB [(none)]> show collation like '%utf8%'; +------------------------------+---------+------+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +------------------------------+---------+------+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | | utf8_latvian_ci | utf8 | 194 | | Yes | 8 | | utf8_romanian_ci | utf8 | 195 | | Yes | 8 | ... | utf8mb4_nopad_bin | utf8mb4 | 1070 | | Yes | 1 | | utf8mb4_unicode_nopad_ci | utf8mb4 | 1248 | | Yes | 8 | | utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 | | Yes | 8 | ---------------------------------------------------------------------------------
utf8_bin : 以二进制形式进行比较
MariaDB [(none)]> show variables like 'character_set_server'; +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | character_set_server | latin1 | +----------------------+--------+ 1 row in set (0.001 sec)
mysql在启动时，会将 my.cnf 加载到内存中，mysql 的变量存储在内存中。也就是说，有两种方式修改 mysql 变量
MariaDB [(none)]> show global variables like '%wait_timeout%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | | lock_wait_timeout | 86400 | | wait_timeout | 28800 | +--------------------------+-------+ 3 rows in set (0.001 sec)
MariaDB [(none)]> select @@global.wait_timeout; +-----------------------+ | @@global.wait_timeout | +-----------------------+ | 28800 | +-----------------------+ 1 row in set (0.001 sec)
set global wait_timeout = 12000; set @@global.wait_timeout = 12000;
mysql 连接时优先读取 会话变量
set wait_timeout = 12000; // 记住这个就行 set session wait_timeout = 12000; set local wait_timeout = 12000; -------------------------------------- set @@wait_timeout = 12000; set @@session.wait_timeout = 12000; set @@local.wait_timeout = 12000;
MariaDB [(none)]> set global wait_timeout = 12000; // 设置全局变量并不会影响会话变量 Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show variables like '%wait_timeout%'; // 查看会话变量 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | | lock_wait_timeout | 86400 | | wait_timeout | 28800 | +--------------------------+-------+ 3 rows in set (0.001 sec) MariaDB [(none)]> show global variables like '%wait_timeout%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | | lock_wait_timeout | 86400 | | wait_timeout | 12000 | +--------------------------+-------+ 3 rows in set (0.001 sec)
扇区 <= 块/簇 <= page
本文档以 innodb 为主进行讲解
对于innodb而言，数据存储在表空间中
表空间对用的具体的文件
MariaDB [(none)]> show variables like "innodb_data_file_path"; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ 1 row in set (0.001 sec)
MariaDB [(none)]> show variables like'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.001 sec)
MariaDB [mysql]> show table status like 'user'\G; *************************** 1. row *************************** Name: user Engine: MyISAM Version: 10 Row_format: Dynamic // Compact格式的升级版本，默认行格式 Rows: 4 Avg_row_length: 118 Data_length: 472 Max_data_length: 281474976710655 Index_length: 4096 Data_free: 0 Auto_increment: NULL Create_time: 2022-07-15 02:53:02 Update_time: 2022-07-15 03:07:36 Check_time: 2022-07-18 04:49:41 Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges Max_index_length: 288230376151710720 Temporary: N 1 row in set (0.000 sec) ERROR: No query specified
MariaDB [mysql]> help show table status; Name: 'SHOW TABLE STATUS' Description: Syntax: SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr] SHOW TABLE STATUS works likes SHOW TABLES, but provides a lot of information about each non-TEMPORARY table. You can also get this list using the mysqlshow --status db_name command. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in https://mariadb.com/kb/en/extended-show/. URL: https://mariadb.com/kb/en/show-table-status/
-- 创建数据表时,显示指定行格式 CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称; -- 创建数据表时,修改行格式 ALTER TABLE 表名 ROW_FORMAT=行格式名称; -- 具体如下： CREATE TABLE `ydl_user` ( `user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID', `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号', .... PRIMARY KEY (`user_id`) USING BTREE ) ROW_FORMAT = DYNAMIC;
1. 变长字段的长度列表(非null)
2. NULL 标志位
3. 记录头信息 (Record Header)
4. 组成每个列的数据
create table row_test ( t1 varchar(10), t2 varchar(10), t3 char(10), t4 varchar(10) ) engine=innodb row_format=compact;
MariaDB [zhdtest]> insert into row_test values('a','bb','bb','ccc'); Query OK, 1 row affected (0.017 sec) MariaDB [zhdtest]> insert into row_test values('d','ee','ee','fff'); Query OK, 1 row affected (0.001 sec) MariaDB [zhdtest]> insert into row_test values('d',NULL,NULL,'fff'); Query OK, 1 row affected (0.014 sec) MariaDB [zhdtest]> select * from row_test; +----------+----------+----------+----------+ | t1 | t2 | t3 | t4 | +----------+----------+----------+----------+ | a | bb | bb | ccc | | d | ee | ee | fff | | d | NULL | NULL | fff | +----------+----------+----------+----------+ 3 rows in set (0.013 sec)
03 02 01 00 00 00 10 00 2c 00 00 00 00 2b 68 00 00 00 00 06 05 80 00 00 00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00 00 02 01 00 00 00 00 0f 62 c9 00 00 01 b2 01 10 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66 03 01 06 00 00 20 ff 98 00 00 00 00 02 02 00 00 00 00 0f 67 cc 00 00 01 b6 01 10 64 66 66 66
03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1 00 // NULL标志位，第一行没有NULL值 00 00 10 00 2c // 记录头信息，固定5字节长度 00 00 00 00 2b 68 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 06 05 // 事务ID，固定6个字节 80 00 00 00 32 01 10 // 回滚指针，固定7个字节 61 // t1数据'a'，'a'的ascii码十六进制就是61 62 62 // t2'bb' 62 62 20 20 20 20 20 20 20 20 // t3数据'bb' Ox20十进制是32对应ascii码是空字符 63 63 63 // t4数据'ccc'
03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1 00 // NULL标志位，第二行没有NULL值 00 00 18 00 2b // 记录头信息，固定5字节长度 00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 0f 62 // 事务ID，固定6个字节 c9 00 00 01 b2 01 10 // 回滚指针，固定7个字节 64 // t1数据'd' 65 65 // t2数据'ee' 65 65 20 20 20 20 20 20 20 20 // t3数据'ee' 66 66 66 // t4数据'fff'
03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1 06 // 00000110 NULL标志位，逆序排列，t2和t3列为空。bitmap数据格式 00 00 20 ff 98 // 记录头信息，固定5字节长度 00 00 00 00 02 02 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度 00 00 00 00 0f 67 // 事务ID，固定6个字节 cc 00 00 01 b6 01 10 // 回滚指针，固定7个字节 64 // t1数据'd' 66 66 66 // t4数据'fff'
背景
新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。mysql8.0默认此格式。
select * from user where id between 10 and 1000;
如果没有 buffer pool，则需要在全表扫描时频繁进行 I/O 操作
这显然不合理
有了 buffer pool 后，执行过程变成如下
Innodb引擎会在mysql启动的时候，向操作系统申请一块连续的空间当做 buffer pool
MariaDB [mysql]> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.001 sec) MariaDB [mysql]> select 134217728/1024/1024; +---------------------+ | 134217728/1024/1024 | +---------------------+ | 128.00000000 | +---------------------+ 1 row in set (0.001 sec)
MariaDB [mysql]> show variables like "%blocks_time%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_old_blocks_time | 1000 | // 1000 ms +------------------------+-------+ 1 row in set (0.001 sec)
MariaDB [mysql]> show engine innodb status; ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 170590208 Dictionary memory allocated 34272 Buffer pool size 8192 Free buffers 7866 Database pages 326 Old database pages 0 // 老数据页 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s // young数据区 Pages read 195, created 131, written 131 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 326, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] --------------
create temporary table temp_table( id int, name varchar(10) ) ENGINE = InnoDB; insert into temp_table values (1,'1'); select * from temp_table ; -- 删除临时表 DROP TEMPORARY TABLE table_name;
MariaDB [mysql]> select * from innodb_table_stats ; +---------------+----------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+----------------+---------------------+--------+----------------------+--------------------------+ | mysql | gtid_slave_pos | 2022-07-15 02:53:02 | 0 | 1 | 0 | | zhdtest | row_test | 2022-08-16 04:57:59 | 2 | 1 | 0 | +---------------+----------------+---------------------+--------+----------------------+--------------------------+ 2 rows in set (0.000 sec) MariaDB [mysql]> explain select table_name from innodb_table_stats group by table_name order by table_name; +------+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | innodb_table_stats | index | NULL | PRIMARY | 793 | NULL | 2 | Using index; Using temporary; Using filesort | +------+-------------+--------------------+-------+---------------+---------+---------+------+------+----------------------------------------------+ 1 row in set (0.014 sec)
select age,count(*) from student group by age order by age
使用以下几种关键字，会创建内部临时表
其实临时表还可以分为
前四章内容，只需要了解有宏观概念即可，事务这章需要完全理解和掌握。
mysql 的事务可以分为
MariaDB [mysql]> show variables like'%autocommit%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | autocommit | ON | | wsrep_retry_autocommit | 1 | +------------------------+-------+ 2 rows in set (0.001 sec)
准备数据
MariaDB [zhdtest]> create table user( -> id int primary key auto_increment, -> name VARCHAR(20), -> balance DECIMAL(10,2) UNSIGNED -> ); MariaDB [zhdtest]> insert into user VALUES (1,'zhd',200); Query OK, 1 row affected (0.001 sec) MariaDB [zhdtest]> insert into user VALUES (2,'zzz',5000); Query OK, 1 row affected (0.001 sec)
-- 开启事务; start transaction; UPDATE user set balance = balance - 200 where id = 1; UPDATE user set balance = balance + 200 where id = 2; -- 提交事务 commit;
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 0.00 | | 2 | zzz | 5400.00 | +----+------+---------+ 2 rows in set (0.000 sec)
-- 都失败 start transaction; UPDATE user set balance = balance - 200 where id = 1; UPDATE user set balance = balance + 200 where id = 2; -- 回滚事务 rollback;
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 0.00 | | 2 | zzz | 5400.00 | +----+------+---------+ 2 rows in set (0.000 sec)
开启只读事务
只读事务基本语法
start transaction read only select * from .... select * from .... commit;
MariaDB [zhdtest]> start transaction read only; -> delete from user where id = 1; Query OK, 0 rows affected (0.000 sec) ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction
读写事务就是正常的事务，采用 start transaction 或者 begin 进行开启
我们可以使用 savepoint 关键字在事务执行中新建【保存点】
MariaDB [zhdtest]> start transaction; -- 开启事务 Query OK, 0 rows affected (0.001 sec) MariaDB [zhdtest]> UPDATE user set balance = balance + 200 where id = 1; Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [zhdtest]> savepoint a; -- 设置保存点 Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> UPDATE user set balance = balance + 200 where id = 2; Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5800.00 | +----+------+---------+ 2 rows in set (0.000 sec) MariaDB [zhdtest]> rollback to a; -- 回滚保存点 Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5600.00 | +----+------+---------+ 2 rows in set (0.000 sec) MariaDB [zhdtest]> commit; -- 关闭事务 Query OK, 0 rows affected (0.001 sec) MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 解决方案 |
|---|---|---|---|---|
| Read uncommitted（读未提交） | √ | √ | √ | |
| Read committed（读已提交） | × | √ | √ | undo log |
| Repeatable read（可重复读） | × | × | √ | MVCC版本控制+间隙锁（mysql的rr不存在幻读） |
| Serializable（串行化） | × | × | × |
MariaDB [mysql]> show variables like '%isolation%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | // 由于是 MariaDB 因此 +---------------+-----------------+ 1 row in set (0.001 sec)
--5.7 tx_isolation --8.0 transaction_isolation -- 设置下一个事务的隔离级别，只对下一个事务有效，下下个事务的隔离级别还是依赖于 session 的隔离级别 SET transaction isolation level read uncommitted; SET transaction isolation level read committed; set transaction isolation level repeatable read; SET transaction isolation level serializable; -- 设置当前会话的隔离级别，优先于全局变量生效 SET session transaction isolation level read uncommitted; SET session transaction isolation level read committed; set session transaction isolation level repeatable read; SET session transaction isolation level serializable; -- 设置全局事务的隔离级别 SET GLOBAL transaction isolation level read uncommitted; SET GLOBAL transaction isolation level read committed; set GLOBAL transaction isolation level repeatable read; SET GLOBAL transaction isolation level serializable;
MariaDB [zhdtest]> set tx_isolation = 'read-uncommitted'; Query OK, 0 rows affected (0.000 sec)
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = balance - 2000 where id = 2; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [zhdtest]> update user set balance = balance + 2000 where id = 1; Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 2200.00 | | 2 | zzz | 3600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
MariaDB [zhdtest]> rollback; Query OK, 0 rows affected (0.014 sec) MariaDB [zhdtest]> commit; Query OK, 0 rows affected (0.000 sec)
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
MariaDB [zhdtest]> set tx_isolation = 'read-committed'; Query OK, 0 rows affected (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = balance -5000 where id = 2; Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 5600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
MariaDB [zhdtest]> commit; Query OK, 0 rows affected (0.001 sec)
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 600.00 | +----+------+---------+ 2 rows in set (0.000 sec)
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 600.00 | +----+------+---------+ 2 rows in set (0.004 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> insert user values (3,'zhd',1000); Query OK, 1 row affected (0.019 sec) MariaDB [zhdtest]> commit; Query OK, 0 rows affected (0.014 sec)
MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 600.00 | +----+------+---------+ 2 rows in set (0.013 sec)
MariaDB [zhdtest]> commit; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 600.00 | | 3 | zhd | 1000.00 | +----+------+---------+ 3 rows in set (0.000 sec)
我们改一下上述案例，就会出现幻读的情况。
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 600.00 | | 3 | zhd | 1000.00 | +----+------+---------+ 3 rows in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> insert user values (4,'zhd',4000); Query OK, 1 row affected (0.013 sec) MariaDB [zhdtest]> commit; Query OK, 0 rows affected (0.001 sec)
MariaDB [zhdtest]> select * from user; -- 一开始没有发生幻读 +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 200.00 | | 2 | zzz | 600.00 | | 3 | zhd | 1000.00 | +----+------+---------+ 3 rows in set (0.000 sec) MariaDB [zhdtest]> update user set balance = balance + 200; -- 使用 update 更新数据后，出现了幻读 Query OK, 4 rows affected (0.000 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [zhdtest]> select * from user; -- 出现幻读 +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 400.00 | | 2 | zzz | 800.00 | | 3 | zhd | 1200.00 | | 4 | zhd | 4200.00 | +----+------+---------+ 4 rows in set (0.000 sec)
关于 Mysql 的索引，在介绍有关 Hive 文件格式的时候有详细说明，详见 这里
MariaDB [zhdtest]> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 0558a506-2a9c-11ed-8402-000c29935689 | +--------------------------------------+ 1 row in set (0.001 sec)
CREATE TABLE `dept`( `id` bigint(20) NOT NULL AUTO_INCREMENT, `code` varchar(32) NOT NULL , `name` varchar(30) NOT NULL, `address` varchar(50) DEFAULT NU;LL, PRIMARY KEY(`id`), KEY `idx_code` (`code`) USING BTREE -- 创建索引 )ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT '部门表'
create index idx_user_name on user(user_name);
alter table user add index idx_email (email);
drop index idx_user_name on ydl_user;
在创建索引时，有些列数据特别长，例如 email，其前几位就能唯一标识一行数据，我们可以截取前几位作为索引
create index idx_email on user(email(5));
CREATE TABLE items( id INT NOT NULL AUTO_INCREMENT, brand VARCHAR(20), -- 品牌 price INT, -- 价格 sales INT, --销量 PRIMARY KEY (id), KEY `idx_brand` (`brand`), KEY `idx_price` (`price`), KEY `idx_sales` (`sales`) )
综上所述，索引失效的条件为：where 条件中对不加索引的列使用 or 条件关联
alert table test add idx_a1_a2_a3 table (a1,a2,a3); create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7));
转化为下边的表格,复合索引是按如下进行排序的
| 品牌 | 价格 | 销量 | id |
|---|---|---|---|
| Armani | 16800 | 35 | |
| Armani | 26800 | 35 | 12,14,16 |
| Armani | 26800 | 100 | 34,56,17 |
| Armani | 68888 | 15 | 1,4,5,6,7 |
| GUCCI | 8999 | 135 | 78,92 |
| LV | 9999 | 326 | 55,63 |
| LV | 12888 | 99 | 57,99 |
| LV | 42888 | 69 | 11,22 |
| PRADA | 9588 | 125 | 111,202 |
在 innodb 中不要尽可能不要建立 text、blob 等大字段，否则会极大影响查询性能。
explain 关键字可以模拟 Mysql 优化器
MariaDB [zhdtest]> explain select * from user where id = 4; +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.007 sec)
| 列号 | 列 | 说明 |
|---|---|---|
| 1 | id | select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 |
| 2 | select_type | 查询类型 |
| 3 | table | 正在访问哪个表 |
| 4 | partitions | 匹配的分区 |
| 5 | type | 访问的类型 |
| 6 | possible_keys | 显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到 |
| 7 | key | 实际使用到的索引，如果为NULL，则没有使用索引 |
| 8 | key_len | 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度 |
| 9 | ref | 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值 |
| 10 | rows | 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数 filtered //查询的表行占表的百分比 |
| 11 | filtered | 查询的表行占表的百分比 |
| 12 | Extra | 包含不适合在其它列中显示但十分重要的额外信息 |
MariaDB [zhdtest]> explain select * from user a,user b where a.id=b.id ; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | | | 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.005 sec)
MariaDB [zhdtest]> explain select * from user where balance> (select balance from user where id = 1); +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | PRIMARY | user | ALL | NULL | NULL | NULL | NULL | 3 | Using where | | 2 | SUBQUERY | user | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set (0.015 sec)
MariaDB [zhdtest]> explain select * from user a,user b where a.id=b.id union select * from user a,user b where a.id=b.id ; +------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | | | 1 | PRIMARY | b | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) | | 2 | UNION | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | | | 2 | UNION | b | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 字段值 | 解释说明 |
|---|---|
| SIMPLE | 简单查询，不包含子查询或Union查询的sql语句。 |
| PRIMARY | 查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。 |
| SUBQUERY | 在select或where中包含子查询。 |
| UNION | 若第二个select出现在uion之后，则被标记为UNION。 |
| UNION RESULT | 从UNION表获取结果的合并操作。(临时表) |
type 为 NULL 表示，MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
MariaDB [zhdtest]> explain select min(id) from user; +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ 1 row in set (0.001 sec)
表只有一行记录（等于系统表），平时不太会出现，可以忽略
MariaDB [zhdtest]> explain select * from user where id=1; +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.000 sec)
MariaDB [zhdtest]> explain select * from user a left join user b on a.id=b.id; +------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | zhdtest.a.id | 1 | | +------+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+ 2 rows in set (0.002 sec)
MariaDB [zhdtest]> show create table items\G; *************************** 1. row *************************** Table: items Create Table: CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `brand` varchar(20) DEFAULT NULL, `price` int(11) DEFAULT NULL, `sales` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_brand` (`brand`), KEY `idx_price` (`price`), KEY `idx_sales` (`sales`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.000 sec) MariaDB [zhdtest]> explain select * from items where brand = 'a'; +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | items | ref | idx_brand | idx_brand | 23 | const | 1 | Using index condition | +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ 1 row in set (0.001 sec)
类似于 ref，但可以搜索值为 NULL 的行
explain select * from student s where name = '白杰' or name is null
explain select * from student where id = 1 or name ='李兴';
全表扫描
显示可能应用在这张表中的索引，一个或多个查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用
实际使用到的索引，如果为NULL，则没有使用索引查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表
它指返回结果的行占需要读到的行(rows列的值)的百分比
在学习 临时表 时分析过 group by 执行的流程 , 而在创建索引后，执行过程如下
直接使用索引信息，统计每个组的人数，直接返回。
MariaDB [zhdtest]> show create table items\G; *************************** 1. row *************************** Table: items Create Table: CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `brand` varchar(20) DEFAULT NULL, `price` int(11) DEFAULT NULL, `sales` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_brand` (`brand`), KEY `idx_price` (`price`), KEY `idx_sales` (`sales`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.000 sec) ERROR: No query specified MariaDB [zhdtest]> explain select * from items where brand is null; # 对于 null 值得判断，可以走索引 +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | items | ref | idx_brand | idx_brand | 23 | const | 1 | Using index condition | +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ 1 row in set (0.018 sec)
MariaDB [zhdtest]> explain select * from items where concat(brand,'-') = 'a-'; +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | items | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.019 sec) MariaDB [zhdtest]> explain select * from items where brand = 'a-'; +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | items | ref | idx_brand | idx_brand | 23 | const | 1 | Using index condition | +------+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ 1 row in set (0.014 sec)
select.... lock in share mode
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.001 sec) MariaDB [zhdtest]> select * from user where id = 1 lock in share mode; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 400.00 | +----+------+---------+ 1 row in set (0.013 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user where id = 1 ; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 400.00 | +----+------+---------+ 1 row in set (0.001 sec)
MariaDB [zhdtest]> update user set balance = 600 where id = 1; // 一直处于阻塞状态，直到超时 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [zhdtest]> update user set balance = 600 where id = 1; Query OK, 1 row affected (0.005 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> select * from user where id = 1; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | zhd | 600.00 | +----+------+---------+ 1 row in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = 1000 where id = 1; Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = 200 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MariaDB [zhdtest]> select * from user; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | zhd | 2000.00 | | 2 | zzz | 800.00 | | 3 | lisi | 1200.00 | | 4 | wangwu | 4200.00 | +----+--------+---------+ 4 rows in set (0.000 sec) MariaDB [zhdtest]> show create table user\G; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, `balance` decimal(10,2) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 1 row in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = 3000 where name = 'wangwu'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = 2000 where name = 'lisi'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction # 提交超时
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.002 sec) MariaDB [zhdtest]> update user set balance = 0 where id =1; Query OK, 1 row affected (0.017 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> begin ; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> update user set balance = 100 where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MariaDB [zhdtest]> update user set balance = 100 where id = 2; // 事务 B 可以更新索引不同的行 Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> show create table user\G; *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, `balance` decimal(10,2) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 1 row in set (0.000 sec) MariaDB [zhdtest]> select * from user; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | zhd | 2000.00 | | 2 | zzz | 800.00 | | 3 | lisi | 1200.00 | | 4 | wangwu | 3000.00 | | 7 | zd | 200.00 | | 10 | zs | 300.00 | | 11 | sd | 1000.00 | +----+--------+---------+ 7 rows in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user where id<=10 and id>=4 lock in share mode; # 锁定 id 在 4 到 10 之间的范围 +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 4 | wangwu | 3000.00 | | 7 | zd | 200.00 | | 10 | zs | 300.00 | +----+--------+---------+ 3 rows in set (0.005 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> insert into user values (5,'wd',1000); # 在记录 id 为 4 到 10 之间的数据无法进行插入，该范围被锁定 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MariaDB [zhdtest]> insert into user values (12,'wd',1000); # 锁定范围以外的记录可以进行插入 Query OK, 1 row affected (0.000 sec)
begin; -- 或根据非唯一索引列 锁住某条记录 SELECT * FROM table WHERE age = 24 FOR UPDATE;
当多个事务互相持有对方所需要的锁，且想要夺取对方所持有的锁，产生循环等待，无法正常结束，即会产生死锁问题。详细可以参考 javaSE 中的死锁
-- lock tables 会自动开启一个事务 lock tables teacher write,student read; -- 给 teacher 表加写锁，给 student 表加读锁 select * from teacher; commit; unlock tables;
MariaDB [zhdtest]> show create table user_version\G; *************************** 1. row *************************** Table: user_version Create Table: CREATE TABLE `user_version` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `balance` float(10,2) DEFAULT NULL, `version` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.000 sec) MariaDB [zhdtest]> select * from user_version; +----+--------+---------+---------+ | id | name | balance | version | +----+--------+---------+---------+ | 1 | 张三 | 500.00 | 1 | | 2 | 李四 | 1500.00 | 1 | | 3 | 王五 | 2500.00 | 1 | +----+--------+---------+---------+ 3 rows in set (0.000 sec)
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user_version where id = 1; +----+--------+---------+---------+ | id | name | balance | version | +----+--------+---------+---------+ | 1 | 张三 | 500.00 | 1 | +----+--------+---------+---------+ 1 row in set (0.001 sec) MariaDB [zhdtest]> update user_version set balance = balance+200, version=version+1 where id =1 and version =1 ; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [zhdtest]> begin; Query OK, 0 rows affected (0.000 sec) MariaDB [zhdtest]> select * from user_version; +----+--------+---------+---------+ | id | name | balance | version | +----+--------+---------+---------+ | 1 | 张三 | 500.00 | 1 | | 2 | 李四 | 1500.00 | 1 | | 3 | 王五 | 2500.00 | 1 | +----+--------+---------+---------+ 3 rows in set (0.000 sec)
MariaDB [zhdtest]> commit; Query OK, 0 rows affected (0.001 sec)
MariaDB [zhdtest]> update user_version set balance = balance+200, version=version+1 where id =1 and version =1; Query OK, 0 rows affected (14.252 sec) Rows matched: 0 Changed: 0 Warnings: 0 # 匹配不到数据，因为事务 A 提交后，id = 1 的 version 已经变成 2 了，表中已经匹配不到对应得数据了
| 日志类型 | 写入日志的信息 |
|---|---|
| binlog 日志 | 记录了对MySQL数据库执行更改的所有操作 |
| 慢查询日志 | 记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询 |
| 错误日志 | 记录在启动，运行或停止mysqld时遇到的问题 |
| 通用查询日志 | 记录建立的客户端连接和执行的语句 |
| 中继日志(relay log) | 从复制主服务器接收的数据更改 |
MariaDB [zhdtest]> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | # 没有开启，需要修改 mariadb 配置文件进行开启 | log_bin_basename | | | log_bin_compress | OFF | | log_bin_compress_min_len | 256 | | log_bin_index | | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 7 rows in set (0.003 sec)
[zhd@localhost ~]$ cd /etc/my.cnf.d [zhd@localhost my.cnf.d]$ chmod 777 mariadb-server.cnf [zhd@localhost my.cnf.d]$ vim mariadb-server.cnf # 如果是 mysql 则伟为 vim mysql-server.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid # 新增 binlog 配置内容如下 log_bin=binlog # 为日志路径和文件格式，可以修改为 log_bin = /var/lib/mysql/mysql-bin.log , 则数据会存储在/var/lib/mysql/目录下，文件会以mysql-bin.log开头 binlog_format=ROW # 默认是 MIXED 格式，但是推荐是 ROW 格式（row格式问题在于数据量太大） expire_logs_days=7 # 设置 7 天自动过期
[zhd@localhost my.cnf.d]$ chmod 644 mariadb-server.cnf
有关数字表示linux文件权限的相关内容，详见 linux基础笔记
重新启动，查看 log_bin 变量
MariaDB [(none)]> show variables like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | # 发现已经开启 binlog | log_bin_basename | /var/lib/mysql/binlog | | log_bin_compress | OFF | | log_bin_compress_min_len | 256 | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------+ 7 rows in set (0.001 sec)
MariaDB [(none)]> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_annotate_row_events | ON | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_commit_wait_count | 0 | | binlog_commit_wait_usec | 100000 | | binlog_direct_non_transactional_updates | OFF | | binlog_file_cache_size | 16384 | | binlog_format | ROW | | binlog_optimize_thread_scheduling | ON | | binlog_row_image | FULL | | binlog_stmt_cache_size | 32768 | | encrypt_binlog | OFF | | gtid_binlog_pos | | | gtid_binlog_state | | | innodb_locks_unsafe_for_binlog | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | read_binlog_speed_limit | 0 | | sync_binlog | 0 | | wsrep_forced_binlog_format | NONE | +-----------------------------------------+----------------------+
flush logs;
[zhd@localhost ~]$ cd /var/lib/mysql [zhd@localhost mysql]$ ls | grep log aria_log.00000001 aria_log_control binlog.000001 binlog.000002 # 产生 binlog 日志文件 binlog.index ib_logfile0 ib_logfile1
MariaDB [zhdtest]> show master logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 369 | | binlog.000002 | 365 | +---------------+-----------+ 2 rows in set (0.000 sec)
DROP TABLE IF EXISTS student; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `score` int(255) DEFAULT NULL, `grade` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a'); INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a'); INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c'); INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd'); INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c'); INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd'); INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd');
truncate table student;
[zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 [sudo] password for zhd: /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; #220915 22:37:24 server id 1 end_log_pos 903 CRC32 0x6e2c5cc9 GTID 0-1-3 ddl /*!100001 SET @@session.gtid_seq_no=3*//*!*/; # at 903 #220915 22:37:24 server id 1 end_log_pos 1029 CRC32 0xf3a82cc7 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1663295844/*!*/; DROP TABLE IF EXISTS `student` /* generated by server */ /*!*/; # at 1029 #220915 22:37:24 server id 1 end_log_pos 1071 CRC32 0x24050c2d GTID 0-1-4 ddl /*!100001 SET @@session.gtid_seq_no=4*//*!*/; # at 1071 #220915 22:37:24 server id 1 end_log_pos 1357 CRC32 0xce99a09e Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1663295844/*!*/; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `score` int(255) DEFAULT NULL, `grade` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 /*!*/; # at 1357 #220915 22:37:24 server id 1 end_log_pos 1399 CRC32 0x2076d049 GTID 0-1-5 trans /*!100001 SET @@session.gtid_seq_no=5*//*!*/; START TRANSACTION /*!*/; # at 1399 # at 1503 #220915 22:37:24 server id 1 end_log_pos 1503 CRC32 0xcf5154a8 Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a') #220915 22:37:24 server id 1 end_log_pos 1563 CRC32 0x56a9f7ee Table_map: `zhdtest`.`student` mapped to number 33 # at 1563 #220915 22:37:24 server id 1 end_log_pos 1614 CRC32 0x7df5615a Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZOEjYxMBAAAAPAAAABsGAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw7u 96lW ZOEjYxcBAAAAMwAAAE4GAAAAACEAAAAAAAEABP/wAQAAAAQAbHVjeVAAAAABAGFaYfV9 '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=1 ### @2='lucy' ### @3=80 ### @4='a' # Number of rows: 1 # at 1614 #220915 22:37:24 server id 1 end_log_pos 1645 CRC32 0xeae43f0d Xid = 27 COMMIT/*!*/; # at 1645 #220915 22:37:24 server id 1 end_log_pos 1687 CRC32 0x7d171b5e GTID 0-1-6 trans /*!100001 SET @@session.gtid_seq_no=6*//*!*/; START TRANSACTION /*!*/; # at 1687 # at 1791 #220915 22:37:24 server id 1 end_log_pos 1791 CRC32 0xdabd3dc9 Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a') #220915 22:37:24 server id 1 end_log_pos 1851 CRC32 0x78d3812d Table_map: `zhdtest`.`student` mapped to number 33 # at 1851 #220915 22:37:24 server id 1 end_log_pos 1902 CRC32 0x15aa6892 Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZOEjYxMBAAAAPAAAADsHAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw4t gdN4 ZOEjYxcBAAAAMwAAAG4HAAAAACEAAAAAAAEABP/wAgAAAAQAbGlseVoAAAABAGGSaKoV '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=2 ### @2='lily' ### @3=90 ### @4='a' # Number of rows: 1 # at 1902 #220915 22:37:24 server id 1 end_log_pos 1933 CRC32 0x32198f5a Xid = 28 COMMIT/*!*/; # at 1933 #220915 22:37:24 server id 1 end_log_pos 1975 CRC32 0x9bfeb0fd GTID 0-1-7 trans /*!100001 SET @@session.gtid_seq_no=7*//*!*/; START TRANSACTION /*!*/; # at 1975 # at 2079 #220915 22:37:24 server id 1 end_log_pos 2079 CRC32 0x051361bb Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c') #220915 22:37:24 server id 1 end_log_pos 2139 CRC32 0xeab337a4 Table_map: `zhdtest`.`student` mapped to number 33 # at 2139 #220915 22:37:24 server id 1 end_log_pos 2190 CRC32 0x496c2581 Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZOEjYxMBAAAAPAAAAFsIAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw6k N7Pq ZOEjYxcBAAAAMwAAAI4IAAAAACEAAAAAAAEABP/wAwAAAAQAamFjazwAAAABAGOBJWxJ '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=3 ### @2='jack' ### @3=60 ### @4='c' # Number of rows: 1 # at 2190 #220915 22:37:24 server id 1 end_log_pos 2221 CRC32 0x14a72229 Xid = 29 COMMIT/*!*/; # at 2221 #220915 22:37:24 server id 1 end_log_pos 2263 CRC32 0x3077f8df GTID 0-1-8 trans /*!100001 SET @@session.gtid_seq_no=8*//*!*/; START TRANSACTION /*!*/; # at 2263 # at 2369 #220915 22:37:24 server id 1 end_log_pos 2369 CRC32 0xbbd3ba13 Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd') #220915 22:37:24 server id 1 end_log_pos 2429 CRC32 0x8dd0713c Table_map: `zhdtest`.`student` mapped to number 33 # at 2429 #220915 22:37:24 server id 1 end_log_pos 2482 CRC32 0x2908e3ab Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZOEjYxMBAAAAPAAAAH0JAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw48 cdCN ZOEjYxcBAAAANQAAALIJAAAAACEAAAAAAAEABP/wBAAAAAYAaGVsbGVuKAAAAAEAZKvjCCk= '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=4 ### @2='hellen' ### @3=40 ### @4='d' # Number of rows: 1 # at 2482 #220915 22:37:24 server id 1 end_log_pos 2513 CRC32 0x8724de27 Xid = 30 COMMIT/*!*/; # at 2513 #220915 22:37:24 server id 1 end_log_pos 2555 CRC32 0x01288a30 GTID 0-1-9 trans /*!100001 SET @@session.gtid_seq_no=9*//*!*/; START TRANSACTION /*!*/; # at 2555 # at 2658 #220915 22:37:24 server id 1 end_log_pos 2658 CRC32 0xf9740bc3 Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c') #220915 22:37:24 server id 1 end_log_pos 2718 CRC32 0x66f63303 Table_map: `zhdtest`.`student` mapped to number 33 # at 2718 #220915 22:37:24 server id 1 end_log_pos 2768 CRC32 0xfa7cc749 Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZOEjYxMBAAAAPAAAAJ4KAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw4D M/Zm ZOEjYxcBAAAAMgAAANAKAAAAACEAAAAAAAEABP/wBQAAAAMAdG9tPAAAAAEAY0nHfPo= '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=5 ### @2='tom' ### @3=60 ### @4='c' # Number of rows: 1 # at 2768 #220915 22:37:24 server id 1 end_log_pos 2799 CRC32 0x9609a343 Xid = 31 COMMIT/*!*/; # at 2799 #220915 22:37:24 server id 1 end_log_pos 2841 CRC32 0x4d0a025d GTID 0-1-10 trans /*!100001 SET @@session.gtid_seq_no=10*//*!*/; START TRANSACTION /*!*/; # at 2841 # at 2946 #220915 22:37:24 server id 1 end_log_pos 2946 CRC32 0xd0c46d77 Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd') #220915 22:37:24 server id 1 end_log_pos 3006 CRC32 0x488c45c0 Table_map: `zhdtest`.`student` mapped to number 33 # at 3006 #220915 22:37:24 server id 1 end_log_pos 3058 CRC32 0x18aa3012 Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZOEjYxMBAAAAPAAAAL4LAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw7A RYxI ZOEjYxcBAAAANAAAAPILAAAAACEAAAAAAAEABP/wBgAAAAUAamVycnkKAAAAAQBkEjCqGA== '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=6 ### @2='jerry' ### @3=10 ### @4='d' # Number of rows: 1 # at 3058 #220915 22:37:24 server id 1 end_log_pos 3089 CRC32 0xc8863468 Xid = 32 COMMIT/*!*/; # at 3089 #220915 22:37:25 server id 1 end_log_pos 3131 CRC32 0x06302011 GTID 0-1-11 trans /*!100001 SET @@session.gtid_seq_no=11*//*!*/; START TRANSACTION /*!*/; # at 3131 # at 3235 #220915 22:37:25 server id 1 end_log_pos 3235 CRC32 0x9bcc74c2 Annotate_rows: #Q> INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd') #220915 22:37:25 server id 1 end_log_pos 3295 CRC32 0x89f122d2 Table_map: `zhdtest`.`student` mapped to number 33 # at 3295 #220915 22:37:25 server id 1 end_log_pos 3346 CRC32 0x454562a2 Write_rows: table id 33 flags: STMT_END_F BINLOG ' ZeEjYxMBAAAAPAAAAN8MAAAAACEAAAAAAAEAB3poZHRlc3QAB3N0dWRlbnQABAMPAw8E/AP8Aw7S IvGJ ZeEjYxcBAAAAMwAAABINAAAAACEAAAAAAAEABP/wBwAAAAQAc2lseRQAAAABAGSiYkVF '/*!*/; ### INSERT INTO `zhdtest`.`student` ### SET ### @1=7 ### @2='sily' ### @3=20 ### @4='d' # Number of rows: 1 # at 3346 #220915 22:37:25 server id 1 end_log_pos 3377 CRC32 0x7a528eea Xid = 33 COMMIT/*!*/; ------------------------------------------------------------------------------------------------------- # at 3377 #220915 22:41:44 server id 1 end_log_pos 3419 CRC32 0x6095c83c GTID 0-1-12 ddl /*!100001 SET @@session.gtid_seq_no=12*//*!*/; # at 3419 #220915 22:41:44 server id 1 end_log_pos 3511 CRC32 0xe5b478ae Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1663296104/*!*/; truncate table student /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 --start-position=0 --stop-position=3419 -v | mysql -uroot -p Enter password: Warning: option 'start-position': unsigned value 0 adjusted to 4
MariaDB [zhdtest]> select * from student; +----+--------+-------+-------+ | id | name | score | grade | +----+--------+-------+-------+ | 1 | lucy | 80 | a | | 2 | lily | 90 | a | | 3 | jack | 60 | c | | 4 | hellen | 40 | d | | 5 | tom | 60 | c | | 6 | jerry | 10 | d | | 7 | sily | 20 | d | +----+--------+-------+-------+ 7 rows in set (0.000 sec)
MariaDB [zhdtest]> show binlog events in 'binlog.000002'; +---------------+------+-------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+-------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | binlog.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 | | binlog.000002 | 256 | Gtid_list | 1 | 285 | [] | | binlog.000002 | 285 | Binlog_checkpoint | 1 | 325 | binlog.000001 | | binlog.000002 | 325 | Binlog_checkpoint | 1 | 365 | binlog.000002 | | binlog.000002 | 365 | Gtid | 1 | 407 | GTID 0-1-1 | | binlog.000002 | 407 | Query | 1 | 533 | use `zhdtest`; DROP TABLE IF EXISTS `student` /* generated by server */ | | binlog.000002 | 533 | Gtid | 1 | 575 | GTID 0-1-2 | | binlog.000002 | 575 | Query | 1 | 861 | use `zhdtest`; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `score` int(255) DEFAULT NULL, `grade` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | | binlog.000002 | 861 | Gtid | 1 | 903 | GTID 0-1-3 | | binlog.000002 | 903 | Query | 1 | 1029 | use `zhdtest`; DROP TABLE IF EXISTS `student` /* generated by server */ | | binlog.000002 | 1029 | Gtid | 1 | 1071 | GTID 0-1-4 | | binlog.000002 | 1071 | Query | 1 | 1357 | use `zhdtest`; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `score` int(255) DEFAULT NULL, `grade` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | | binlog.000002 | 1357 | Gtid | 1 | 1399 | BEGIN GTID 0-1-5 | | binlog.000002 | 1399 | Annotate_rows | 1 | 1503 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a') | | binlog.000002 | 1503 | Table_map | 1 | 1563 | table_id: 33 (zhdtest.student) | | binlog.000002 | 1563 | Write_rows_v1 | 1 | 1614 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 1614 | Xid | 1 | 1645 | COMMIT /* xid=27 */ | | binlog.000002 | 1645 | Gtid | 1 | 1687 | BEGIN GTID 0-1-6 | | binlog.000002 | 1687 | Annotate_rows | 1 | 1791 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a') | | binlog.000002 | 1791 | Table_map | 1 | 1851 | table_id: 33 (zhdtest.student) | | binlog.000002 | 1851 | Write_rows_v1 | 1 | 1902 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 1902 | Xid | 1 | 1933 | COMMIT /* xid=28 */ | | binlog.000002 | 1933 | Gtid | 1 | 1975 | BEGIN GTID 0-1-7 | | binlog.000002 | 1975 | Annotate_rows | 1 | 2079 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c') | | binlog.000002 | 2079 | Table_map | 1 | 2139 | table_id: 33 (zhdtest.student) | | binlog.000002 | 2139 | Write_rows_v1 | 1 | 2190 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 2190 | Xid | 1 | 2221 | COMMIT /* xid=29 */ | | binlog.000002 | 2221 | Gtid | 1 | 2263 | BEGIN GTID 0-1-8 | | binlog.000002 | 2263 | Annotate_rows | 1 | 2369 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd') | | binlog.000002 | 2369 | Table_map | 1 | 2429 | table_id: 33 (zhdtest.student) | | binlog.000002 | 2429 | Write_rows_v1 | 1 | 2482 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 2482 | Xid | 1 | 2513 | COMMIT /* xid=30 */ | | binlog.000002 | 2513 | Gtid | 1 | 2555 | BEGIN GTID 0-1-9 | | binlog.000002 | 2555 | Annotate_rows | 1 | 2658 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c') | | binlog.000002 | 2658 | Table_map | 1 | 2718 | table_id: 33 (zhdtest.student) | | binlog.000002 | 2718 | Write_rows_v1 | 1 | 2768 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 2768 | Xid | 1 | 2799 | COMMIT /* xid=31 */ | | binlog.000002 | 2799 | Gtid | 1 | 2841 | BEGIN GTID 0-1-10 | | binlog.000002 | 2841 | Annotate_rows | 1 | 2946 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd') | | binlog.000002 | 2946 | Table_map | 1 | 3006 | table_id: 33 (zhdtest.student) | | binlog.000002 | 3006 | Write_rows_v1 | 1 | 3058 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 3058 | Xid | 1 | 3089 | COMMIT /* xid=32 */ | | binlog.000002 | 3089 | Gtid | 1 | 3131 | BEGIN GTID 0-1-11 | | binlog.000002 | 3131 | Annotate_rows | 1 | 3235 | INSERT INTO `student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd') | | binlog.000002 | 3235 | Table_map | 1 | 3295 | table_id: 33 (zhdtest.student) | | binlog.000002 | 3295 | Write_rows_v1 | 1 | 3346 | table_id: 33 flags: STMT_END_F | | binlog.000002 | 3346 | Xid | 1 | 3377 | COMMIT /* xid=33 */ | | binlog.000002 | 3377 | Gtid | 1 | 3419 | GTID 0-1-12 | | binlog.000002 | 3419 | Query | 1 | 3511 | use `zhdtest`; truncate table student | +---------------+------+-------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 49 rows in set (0.000 sec)
# 指定时间范围 /usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00" -v | mysql -uroot -p
# 指定位置范围 [zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 --start-position=0 --stop-position=3419 -v | mysql -uroot -p
# 指定时间范围 [zhd@localhost mysql]$ sudo /usr/bin/mysqlbinlog -v binlog.000002 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00" -v | mysql -uroot -p
MariaDB [zhdtest]> show binlog events in 'binlog.000002'; -- 指定日志
Statement (Statement-Based Replication, SBR)
Row(Row-Based Replication,RBR)
Mixed(Mixed-Based Replication,MBR)
Statement 与 Row
MariaDB [zhdtest]> show binlog events in 'binlog.000004'; +---------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | binlog.000004 | 4 | Format_desc | 1 | 256 | Server ver: 10.3.28-MariaDB-log, Binlog ver: 4 | | binlog.000004 | 256 | Gtid_list | 1 | 299 | [0-1-11] | | binlog.000004 | 299 | Binlog_checkpoint | 1 | 339 | binlog.000004 | | binlog.000004 | 339 | Rotate | 1 | 383 | binlog.000005;pos=4 | +---------------+-----+-------------------+-----------+-------------+------------------------------------------------+ 4 rows in set (0.000 sec)
[zhd@localhost mysql]$ sudo cat binlog.index [sudo] password for zhd: ./binlog.000001 ./binlog.000002 ./binlog.000003 ./binlog.000004 ./binlog.000005
[hadoop@node02 mysql]$ mysql --version mysql Ver 8.0.29 for Linux on x86_64 (MySQL Community Server - GPL)
[mysqld] port=3306 server-id=1 user=hadoop socket=/zhd/data/mysqlDatas/socket/mysql.sock # 设置mysql的安装目录 basedir=/zhd/install/mysql-8.0.29 #你自己的安装路径 # 设置mysql数据库的数据的存放目录 datadir=/zhd/data/mysqlDatas/mysql/ #你自己创建的数据库文件存放路径 log-bin=/zhd/data/mysqlDatas/mysql/master-bin # mater-bin 文件
[mysqld] server-id=2 relay-log=mysql-relay-bin # 以下设置的意思是，在复制 binlog 日志时，忽略那些表的 binlog # 系统自带的数据库不需要进行同步 replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=sys.% replicate-wild-ignore-table=information_schema.% replicate-wild-ignore-table=performance_schema.%
CREATE USER 'hadoop'@'node02.zhd.org' IDENTIFIED BY 'Zh******2';
grant replication slave on *.* to 'hadoop'@'node02.zhd.org'; -- 这里的 *.* 表示任意的数据库中的任意表，都允许复制 FLUSH PRIVILEGES;
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 1071 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
CHANGE MASTER TO MASTER_HOST = 'node01.zhd.org', MASTER_USER = 'hadoop', MASTER_PASSWORD = 'Zh******2', MASTER_PORT = 3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=1071, MASTER_HEARTBEAT_PERIOD = 10000; -- MASTER_LOG_FILE与主库File 保持一致, 即上面查询的结果 -- MASTER_LOG_POS=120 , #与主库Position 保持一致
mysql> start slave; Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to source Master_Host: node01.zhd.org Master_User: hadoop Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1999 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1999 Relay_Log_Space: 157 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2061 Last_IO_Error: error connecting to master 'hadoop@node01.zhd.org:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 220918 10:35:39 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
# 在主库进行修改 mysql> ALTER USER 'hadoop'@'node02.zhd.org' IDENTIFIED WITH mysql_native_password BY 'Zhonghuadong2'; Query OK, 0 rows affected (0.00 sec)
[hadoop@node02 etc]$ cd /zhd/data/mysqlDatas [hadoop@node02 mysqlDatas]$ cd mysql [hadoop@node02 mysql]$ cat auto.cnf [auto] server-uuid=f2140bea-0458-11ed-83e3-000c293f8c8e
[hadoop@node01 mysql]$ cat auto.cnf [auto] server-uuid=f2140bea-0458-11ed-83e3-000c293f8c8e
mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 6815e635-3762-11ed-984c-000c293f8c8e | +--------------------------------------+ 1 row in set (0.00 sec)
vim auto.cnf
[hadoop@node02 mysql]$ mysql.server restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
mysql> start slave; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: node01.zhd.org Master_User: hadoop Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 2301 Relay_Log_File: mysql-relay-bin.000004 Relay_Log_Pos: 629 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%,performance_schema.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2301 Relay_Log_Space: 1199 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: f2140bea-0458-11ed-83e3-000c293f8c8e Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
mysql> drop database if exists master_test; Query OK, 0 rows affected (0.00 sec) mysql> create database if not exists master_test; Query OK, 1 row affected (0.01 sec)
mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | master_test | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
MariaDB [zhdtest]> show variables like '%general_log%'; +------------------+---------------+ | Variable_name | Value | +------------------+---------------+ | general_log | OFF | | general_log_file | localhost.log | # 通用查询日志文件名称为 localhost.log +------------------+---------------+ 2 rows in set (0.015 sec)
-- 在全局模式下，开启通用查询日志，1表示开启，0表示关闭 SET global general_log=1;
[zhd@localhost mysql]$ ls aria_log.00000001 binlog.000003 binlog.index ib_logfile1 multi-master.info performance_schema aria_log_control binlog.000004 ib_buffer_pool ibtmp1 mysql zhdtest binlog.000001 binlog.000005 ibdata1 localhost.localdomain.err mysql.sock binlog.000002 binlog.000006 ib_logfile0 localhost.pid mysql_upgrade_info [zhd@localhost mysql]$ ls aria_log.00000001 binlog.000003 binlog.index ib_logfile1 localhost.pid mysql_upgrade_info aria_log_control binlog.000004 ib_buffer_pool ibtmp1 multi-master.info performance_schema binlog.000001 binlog.000005 ibdata1 localhost.localdomain.err mysql zhdtest binlog.000002 binlog.000006 ib_logfile0 #localhost.log# mysql.sock
[zhd@localhost mysql]$ sudo cat localhost.log /usr/libexec/mysqld, Version: 10.3.28-MariaDB-log (MariaDB Server). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 220916 4:33:47 9 Query select * from student 220916 4:33:50 9 Query select * from stdent 220916 4:33:59 9 Query show status # 其记录了所有的 DDL 和 DML 操作
MariaDB [zhdtest]> show variables like '%slow_query_log%'; +---------------------+--------------------+ | Variable_name | Value | +---------------------+--------------------+ | slow_query_log | OFF | | slow_query_log_file | localhost-slow.log | +---------------------+--------------------+ 2 rows in set (0.001 sec)
MariaDB [zhdtest]> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.001 sec)
MariaDB [zhdtest]> show variables like '%log_error%'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | log_error | /var/log/mariadb/mariadb.log | # 位置和配置文件中的参数log-error有关系 +---------------+------------------------------+ 1 row in set (0.001 sec)
[zhd@localhost ~]$ sudo cat /var/log/mariadb/mariadb.log 220919 02:59:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2022-09-19 2:59:11 0 [Note] /usr/libexec/mysqld (mysqld 10.3.28-MariaDB-log) starting as process 2681 ... ... ... 2022-09-19 2:59:11 0 [Note] InnoDB: Buffer pool(s) load completed at 220919 2:59:11 2022-09-19 2:59:12 0 [Note] Plugin 'FEEDBACK' is disabled. 2022-09-19 2:59:12 0 [Warning] mysqld: GSSAPI plugin : default principal 'mariadb/localhost@' not found in keytab 2022-09-19 2:59:12 0 [ERROR] mysqld: Server GSSAPI error (major 851968, minor 2529639093) : gss_acquire_cred failed -Unspecified GSS failure. Minor code may provide more information. Keytab FILE:/etc/krb5.keytab is nonexistent or empty. 2022-09-19 2:59:12 0 [ERROR] Plugin 'gssapi' init function returned error. 2022-09-19 2:59:12 0 [Note] Recovering after a crash using binlog 2022-09-19 2:59:12 0 [Note] Starting crash recovery... ... 2022-09-19 2:59:12 0 [Note] Added new Master_info '' to hash table 2022-09-19 2:59:12 0 [Note] /usr/libexec/mysqld: ready for connections. Version: '10.3.28-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
前面讲述的 binlog日志、通用日志、错误日志、慢查询日志，都是 mysql server 级别的，接下来的 redo log 和 undo log 都是 InnoDB 级别的日志
在innodb执行任务时，有很多操作，必须具有原子性，我们把这一类操作称之为 Mini Transaction
[zhd@localhost mysql]$ pwd /var/lib/mysql [zhd@localhost mysql]$ ll |grep ib -rw-rw----. 1 mysql mysql 1306 Sep 16 02:54 ib_buffer_pool -rw-rw----. 1 mysql mysql 12582912 Sep 16 03:08 ibdata1 # undo log 存放在这里面 -rw-rw----. 1 mysql mysql 50331648 Oct 2 22:38 ib_logfile0 -rw-rw----. 1 mysql mysql 50331648 Jul 15 02:53 ib_logfile1 -rw-rw----. 1 mysql mysql 12582912 Oct 2 22:38 ibtmp1
当我们对数据库的数据进行一个操作时必须记录之前的信息，将来才能【悔棋】
在回滚时进行反向操作即可
undo log 分为两类
关于段的补充
当一个事务读取数据时，会根据当前数据形成一个 readView，读取时会按照以下逻辑进行读取
总结
使用如下案例来查看这个过程
通过间隙锁实现，一旦锁定某一个范围的数据，就会对这个范围的数据进行加锁，间隙锁保证我们 不能在这个范围内插入新的数据
/* CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW 业务逻辑 */ -- 当b_user表中插入数据后，b_log表中也插入一条数据 CREATE TRIGGER trigger_insert AFTER INSERT ON b_user FOR EACH ROW INSERT INTO b_log(字段) VALUES('插入数据')
/* DELIMITER $ CREATE TRIGGER 触发器名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW BIGIN INSERT...; UPDATE...; END;$ */ -- 在b_user表中插入数据前，b_log表中插入2条数据 DELIMITER $ CREATE TRIGGER trigger_ insert_before BEFORE INSERT ON b_user FOR EACH ROW BEGIN INSERT INTO b_log(comments,name) values('insert1' ，NEW.name); INSERT INTO b_log(comments,name) values('insert2' , NEW.name) ; END;$
DROP TRIGGER 触发器名称
系统变量
查看系统变量
SHOW GLOBAL variables; -- 查看全局变量 SHOW SESSION variables; -- 查看会话变量 SHOW variables; -- 查看会话变量 SHOW GLOBAL variables like '%dir%'; -- 模糊查询环境变量 SELECT @@datadir; -- 查看全局系统变量 SELECT @@session_track_transaction_info;
SHOW GLOBAL variables like 'autocommit'; -- 全局系统变量中为自动提交事务 SET GLOBAL autocommit=0; -- 将全局的自动提交的事务改为手动提交 SHOW SESSION variables like 'autocommit'; -- 查看会话变量中自动提交事务 SET SESSION autocommit=0; -- 将会话变量中自动提交的事务改为手动提交 SET @@session.autocommit=1; SET @@global.autocommit=1;
#设置方式1，先去声明并初始化用户变量，赋值操作既可以使用=进行赋值，也可以使用:=进行赋值 SET @变量名=值; SET @变量名:=值; // 对于未事先声明的变量，需要用 @ 前缀，对于已经进行 DECLARE 变量，不需要用 @ SELECT @变量名:=值; #设置方式2 SELECT 字段 into @变量名 FROM 表名;
#声明方式，必须在begin后面从第一行开始 DECLARE 变量名 类型; DECLARE 变量名 类型 DEFAULT 值; #局部变量的赋值 SET 变量名:=值; SELECT @变量名:=值; SELECT 字段 into 变量名 FROM 表名;
/* DELIMITER $ CREATE PROCEDURE 存储过程的名称(参数列表) BEGIN 局部变量的定义 多条sql语句 流程控制语句 END;$ */
| 参数模式 | 形参名称 | 参数类型 |
|---|---|---|
| IN | username | mysql数据库中的数据类型（数值型，字符型，日期型） |
| OUT | pwd | mysql数据库中的数据类型（数值型，字符型，日期型） |
| INOUT | xxx | mysql数据库中的数据类型（数值型，字符型，日期型） |
IN : 声明该参数是一个输入姓参数（类似于java中的形参）
OUT : 声明该参数为一个输出型参数（类似于java中的返回值），在一个存储过程中可以定义多个out类型的参数
INOUT : 声明该参数可以为输入型参数，也可以为输出型参数
存储过程的调用
CALL 存储过程的名称(实参列表) -- 实参列表中包含由输出类型的参数
-- 用于向b_user表中插入2条数据 DELIMITER $ CREATE PROCEDURE pro_insert() BEGIN INSERT INTO b_user(name,sex) VALUES('1','1'); INSERT INTO b_user(name,sex) VALUES('2','2'); END; $ CALL pro_insert();
-- 用于向b_user插入2条数据，性别由客户输入 DELIMITER $ CREATE PROCEDURE pro_insert2(IN sex CHAR(1)) BEGIN INSERT INTO b_user(name,sex) VALUES('1',sex); INSERT INTO b_user(name,sex) VALUES('2',sex); END;$ -- 调用存储过程 CALl pro_insert2('男');
-- 用于向b_user插入2条数据，用户名和密码由客户输入 DELIMITER $ CREATE PROCEDURE pro_insert3(IN name VARCHAR(10),IN sex VARCHAR(20)) BEGIN INSERT INTO b_user(name,sex) VALUES(name,sex); INSERT INTO b_user(name,sex) VALUES(name,sex); END; $ CALL pro_insert2('uname','男');
-- 判断用户登录，如果用户名和密码输入正确登录成功，否则登录失败 -- 根据输入的用户名和密码作为条件去b_user表中查询，如果查询总行数==1，则认为登录成功，让result返回登录成功，否则登录失败 DELIMITER $ CREATE PROCEDURE pro_login(IN name VARCHAR(20),IN pwd VARCHAR(20),OUT result VARCHAR(20)) BEGIN DECLARE total INT DEFAULT 0;-- 用于存放查询总行数，创建局部变量 select count(*) from b_user u where u.name=name and u.pwd=pwd;-- 将查询结果赋值给total局部变量 SET result:=IF(total=1,'登录成功','登录失败'); END; $ -- 存储过程如何执行 -- 解决判断，使用自定义变量 SET @result:=''; CAll pro_login('李四','123',@result); select @result;
DROP PROCEDURE 存储过程名称
SHOW CREATE PROCEDURE 存储过程名称;
DROP CREATE
/* IF 逻辑表达式 THEN 语句1; ELSEIF 逻辑表达式2 THEN 语句2; ... ELSE 语句n; END IF; */
CASE WHEN 逻辑表达式 THEN 语句1 ... ELSE 语句n END
CASE 字段|变量|表达式 WHEN 值 THEN 值|语句 WHEN 值 THEN 值 ... ELSE 值 END
/* WHILE 逻辑表达式 DO 循环体 END WHILE; */ -- 需求：创建存储过程，输入一个值，返回1到该值的和 -- 分析：一个输入参数，一个返回值，在结构体中，从1循环到输入的值，求和 DELIMITER // -- 将分隔符换成 // CREATE PROCEDURE pro_sum(IN input INT,OUT total INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE sum_ INT DEFAULT 0; WHILE i<=input do SET sum_=sum_+i; SET i=i+1; END WHILE; SET totle:=sum_; END;// SET @result=0; CALL por_sun(10,@result); SELECT @result;
#Loopname是定义的循环名称，为了跳出循环时指定跳出的循环 loopname:LOOP; IF 逻辑表达式 THEN LEAVE loopname; -- 跳出当前指定的循环，类似于java中的break END IF; END LOOP;
DElIMITER // CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE sum_ INT DEFAULT 0; a:LOOP; -- a为loopname SET sum_:=sum_+i; SET i:=i+1; IF i>input THEN LEAVE a; END IF; END LOOP; SET total:=sum_; END;// -- 调用存储过程 SET @result=0; CALL por_sum_loop(10,@result); SELECT @result;
REPEAT 循环体 UNTIL 逻辑表达式 -- 当满足逻辑表达式，跳出循环 END REPEAT;
DELIMITER // CREATE PROCEDURE pro_sum_loop(IN input INT,OUT total INT) BEGIN DECLARE i INT DEFAULT 1; -- 声明局部变量 DECLARE sum_ INT DEFAULT 0; REPEAT SET sum_:=sum_+i; SET i:=i+1 UNTIL i>input END REPEAT; SET total:=sum_; END; // SET @result=0; CALL por_sum_loop(10,@result); SELECT @result;
函数也是一组预先编译好的sql的集合，基本和存储过程相似
函数 VS 存储过程
创建函数基本语法
CREATE FUNCTION 函数名称(参数列表) RETURNS 返回类型 BINLOG参数 BEGIN 函数体 END
-- 写一个函数，用于求两数之和 DELIMITER // CREATE FUNCTION sum_(input1 INT,input2 INT) RETURNS INT NO SQL BEGIN return input1+input2; END;//
select 函数名(参数列表);
show create function 函数名 ;
DROP FUNCTION 函数名
MariaDB [zhdtest]> show variables like '%event_sche%' ; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.001 sec)
set global event_scheduler=1;
create event run_event on schedule every 1 minute on completion preserve disable do call test_procedure (); -- 可以调用存储过程或函数
MariaDB [zhdtest]> SELECT event_name,event_definition,interval_value,interval_field,status -> FROM information_schema.EVENTS; Empty set (0.003 sec)
alter event run_event on completion preserve enable; -- 开启定时任务 alter event run_event on completion preserve disable;-- 关闭定时任务
on schedule every 1 week --每周执行1次
on schedule at current_timestamp()+interval 5 day -- 5天后执行 on schedule at '2019-01-01 00:00:00' -- 在2019年1月1日，0点整执行
on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month -- 5天后开始每天都执行执行到下个月这天 on schedule every 1 day ends current_timestamp()+interval 5 day -- 从现在起每天执行，执行5天
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
