show variables like '%innodb_log%';
innodb_log_file_size 5242880 -- 每个日志文件大小,/var/lib/mysql/ib_logfile
innodb_log_files_in_group 2  -- 日志组成员个数，建议调大
innodb_log_group_home_dir ./ -- 事务文件路径

$ mkdir -pv /data/mysqllogs/
$ chmod mysql.mysql /data/mysqllogs/ 
$ vim /etc/my.cnf
[mysqld]
innodb_log_files_in_group=5  #仅修改日志数会导致服务起不来
innodb_log_group_home_dir=/data/mysqllogs/
$ systemctl restart mariadb

show variables like 'general_log%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| general_log      | OFF           |
| general_log_file | localhost.log |
+------------------+---------------+
set global general_log=on;
show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |	 -- TABLE|FILE|NONE
+---------------+-------+
set global log_output=table;

use testdb
-- 脚本内容
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure pro_testlog()
begin
declare i int;
set i = 1;
while i < 100000
do insert into testlog(name,age) values(concat('wang',i),i) ;
set i = i +1;
end while;
end$$

delimiter ;

start transaction;
-- 生成大数据事务
call pro_testlog; 
call pro_testlog; 
call pro_testlog; 
-- 查询数据
select count(*) from testlog; 
\! ls -lh /var/lib/mysql/testdb/testlog.ibd
-- 数据直接写入数据文件，超过了事务日志的大小。
truncate testlog;
-- 删除数据库
delete from testlog;
-- 用delete删除大文件，查询数据文件没有减少,需要optimize进行清除。
\! ls /var/lib/mysql/testdb/testlog.ibd -h 
optimize table testlog;

show variables like 'slow%';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_launch_time    | 2                  |
| slow_query_log      | OFF                |
| slow_query_log_file | localhost-slow.log |
+---------------------+--------------------+
set global slow_query_log=ON;
-- ll /var/lib/mysql/hellodb/
select sleep(1),name from students;
-- 查询谁在占用
show processlist;

use hellodb
-- 查询时间不少于10s，或者不使用索引，系统自动默认OFF
-- log_queries_not_using_indexes=OFF
select * from students;
-- 系统自动优化sql语句，可能使用索引或不使用
explain select * from students where name like 'x%'\G;
explain select * from students where name like 's%'\G;

set profiling=ON;
show variables like 'prof%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.00041210 | select @@profiling     |
|        2 | 0.09205547 | select * from students |
+----------+------------+------------------------+
show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000016 |
| Waiting for query cache lock   | 0.000003 |
| init                           | 0.000002 |
| checking query cache for query | 0.000144 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.090296 |
| After opening tables           | 0.000014 |
| System lock                    | 0.000045 |
| Table lock                     | 0.000013 |
| Waiting for query cache lock   | 0.000168 |
| init                           | 0.000176 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000016 |
| preparing                      | 0.000019 |
| executing                      | 0.000003 |
| Sending data                   | 0.000599 |
| end                            | 0.000010 |
| query end                      | 0.000007 |
| closing tables                 | 0.000004 |
| Unlocking tables               | 0.000015 |
| freeing items                  | 0.000007 |
| updating status                | 0.000005 |
| Waiting for query cache lock   | 0.000003 |
| updating status                | 0.000419 |
| Waiting for query cache lock   | 0.000008 |
| updating status                | 0.000003 |
| storing result in query cache  | 0.000008 |
| cleaning up                    | 0.000034 |
+--------------------------------+----------+

sql_log_bin=ON|OFF：
-- 是否记录二进制日志，默认ON
log_bin=/PATH/BIN_LOG_FILE：
-- 指定文件位置；默认ON，表示不启用二进制日志功能，上述两项都为ON才可
max_binlog_size=1073741824：
-- 单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
sync_binlog=1|0：
-- 设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：
-- 二进制日志可以自动删除的天数。 默认为0，即不自动删除

show master logs;
show binary logs;
-- 查询二进制的日志文件
show master status;
update students set name='xyz' where stuid=10;
show binlog events in 'mysql-bin.000003' from 1577;
-- 重新生成二进制日志文件
flush logs;
show master status;

-- 清楚000002之前的二进制文件，
pruge binary logs to "mysql-bin.000002";
show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |     10042 |
| mysql-bin.000003 |      1812 |
| mysql-bin.000004 |       385 |
+------------------+-----------+
-- 删除至000001的二进制文件
reset master to 1