-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # default collation
al: average lock time # average lock time
ar: average rows sent # average return record time
at: average query time # average query time
c: count # records
l: lock time # lock time
r: rows sent # returns the number of records
t: query time # query time
-r reverse the sort order (largest last instead of first) # reverse order
-t NUM just show the top n queries #top n
-a don't abstract all numbers to N and strings to'S' # do not use n to replace numbers, S to replace strings
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string # matching query, the same as grep
-h HOSTNAME hostname of db server for -slow.log filename (can be wildcard),default is '', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
use mysqldumpslow A quick look: things that obviously omit a lot of comments   Relevant parameters:
#And slow query related parameter settings
log_throttle_queries_not_use_indexes=10, which records the number of times allowed to write to the slow log per minute. The default value is 0, which means there is no limit
long_query_time=10 # specifies the timeout length of the slow query (10 seconds by default). Those exceeding this length belong to the slow query
log_output={TABLE|FILE|NONE} # defines the output format of general query log and slow query log. The default is file
log_slow_queries={yes|no} # whether to enable slow query log. It is not enabled by default
slow_query_log={1|ON|0|OFF} # is also whether to enable slow query log. This variable is the same as log_slow_queries modify one and change the other at the same time
slow_ query_ log_ file=/mydata/data/hostname-slow. The log # default path is the hostname under the library file directory plus - slow log
log_queries_not_using_indexes=OFF # query whether the index is recorded in the slow query log when it is not used
**1.5,Binary log file**
Record pair mysql The database has performed all operations of the change, excluding select and show If such an operation is performed update and delete Such an operation, however, does not cause any changes in the database data, and may also be written to the binary log file.
Binary logs are recorded in the form of events, not transaction logs (but they may also be based on transaction log binary logs). For transaction table operations, binary logs are only written once when transactions are committed(Transaction based innodb Binary log)，Each binary log record before submission cache，Write on commit. For non transaction table operations, they are written directly every time the statement is executed.
mysql A binary log index file is also created. When the binary log file is scrolled, the corresponding information will be written to the file. Therefore, this file contains the file names of all binary log files used. By default, the file name is the same as that of the binary log file, with the extension of'.index'. You can use parameters--log-bin-index[=file_name]specify a filename
**1.5.1,Role of binary log files**
1,Restore: the user can restore through binary files point-in-time Restore
2,Copy: recover data on a remote machine by executing binary files
3,Audit: you can audit binary log files to determine whether there is an injection attack on the database
**1.5.2,View binary**
1.use mysqlbinlog Tools.
2.use show Display the corresponding information.
SHOW {BINARY | MASTER} LOGS # See which log files are used
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] # View what has been done in the log
SHOW MASTER STATUS # Displays binary log information from the primary server
mysqlbinlog Use format:
mysqlbinlog [option] log-file1 log-file2...
Common options:
-d. – database=name: view only the log operations of the specified database
-o. – offset = #: ignore the first n operation commands in the log
-r. – result file = name: outputs the output log information to the specified file. Redirection can also be used.
-s. – short form: displays the log in a simple format, only records some ordinary statements, and omits some additional information, such as location information, time information and line based log. It can be used for debugging, and the production environment must not be used
–set-charset=char_name: when outputting log information to the file, add set names char to the first line of the file_ name
– start datetime, – stop datetime: specify all log information within the output start time and end time
– start position = #, – stop position = #: specify all log information within the start and end positions of the output
-v. - vv: displays more detailed information. The log based on row will not be displayed by default. At this time, use - V or - vv to view it
use mysqlbinlog Command pair mysql-bin.000001 View the log as follows:
[root@lgh3 mysql]# mysqlbinlog /data/binlog/mysql-bin.000001
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 4
#190908 21:18:32 server id 1000 end_log_pos 123 CRC32 0x052f8ffd Start: binlog v 4, server v 5.7.24-log created 190908 21:18:32 at startup
ROLLBACK/!/;
BINLOG '
aKh1XQ/oAwAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABoqHVdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Af2PLwU=
'/!/;
at 123
#190908 21:18:32 server id 1000 end_log_pos 154 CRC32 0x927edf95 Previous-GTIDs
[empty]
at 154
#190908 21:19:15 server id 1000 end_log_pos 219 CRC32 0xb05f419c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
at 219
#190908 21:19:15 server id 1000 end_log_pos 313 CRC32 0xaafce462 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1567991955/!/;
SET @@session.pseudo_thread_id=2/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1075838976/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8mb4 //!/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
create database test
/!/;
at 313
#190908 21:19:53 server id 1000 end_log_pos 378 CRC32 0xdccfd8ec Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
at 378
#190908 21:19:53 server id 1000 end_log_pos 497 CRC32 0x57a45727 Query thread_id=2 exec_time=0 error_code=0
use test/!/;
SET TIMESTAMP=1567991993/!/;
create table gxt1(id int , name varchar(128))
/!/;
at 497
#190908 21:20:17 server id 1000 end_log_pos 562 CRC32 0x316b41e9 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED//!/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/;
at 562
#190908 21:20:17 server id 1000 end_log_pos 634 CRC32 0x693ee549 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1567992017/!/;
BEGIN
/!/;
at 634
#190908 21:20:17 server id 1000 end_log_pos 684 CRC32 0xde906285 Table_map: test.gxt1 mapped to number 108
at 684
#190908 21:20:17 server id 1000 end_log_pos 734 CRC32 0xa0fc1d34 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
0ah1XRPoAwAAMgAAAKwCAAAAAGwAAAAAAAEABHRlc3QABGd4dDEAAgMPAgACA4VikN4=
0ah1XR7oAwAAMgAAAN4CAAAAAGwAAAAAAAEAAgAC//wBAAAACAB6aGFuZ3NhbjQd/KA=
'/!/;
at 734
#190908 21:20:17 server id 1000 end_log_pos 765 CRC32 0xc9208948 Xid = 10
COMMIT/!/;
at 765
#190908 21:30:18 server id 1000 end_log_pos 812 CRC32 0xda645def Rotate to mysql-bin.000002 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog / /!*/;
DELIMITER ;
End of log file
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
The blue font above indicates the position and red indicates the time.
SHOW BINARY LOGS ;
SHOW MASTER LOGS ;
show binlog events in 'mysql-bin.000001';
SHOW MASTER STATUS;
Indicates that the binary file has been recorded to the fourth, position 418.
**1.5.3,Binary file deletion**
1.reset master All logs will be deleted and the log file will start from 00000 1 again.
mysql> reset master;
2.PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
purge master logs to "binlog_name.00000X" Will empty 00000 X All previous log files. For example, delete log files before 00000 6.
mysql> purge master logs to "mysql-bin.000006";
mysql> purge binary logs to "mysql-bin.000006";
master and binary Is a synonym
purge master logs before 'yyyy-mm-dd hh:mi:ss' All logs before the specified date will be deleted. However, if the specified time is in the log file in use, it cannot be processed purge.
3.use--expire_logs_days=N Option specifies the number of days after which the log will automatically expire and be emptied
**1.5.4,Related parameters**
log-bin=on|off|file_name # or specify the path log bin = / data / binlog / mysql bin to enable and set the path, msyql5 7. Server must be assigned to enable this parameter_ ID, otherwise mysql cannot be started
server_id=1000 # specify server_id
max_binlog_size: # (default 1G) specifies the maximum value of a binary file. If the value is exceeded, a new binary log file will be generated (considering that the transaction cannot span two binlog files, sometimes the actual size of the binlog file will not be equal to the set value)
binlog_cache_size: # (32k by default) stores all uncommitted binary log records for caching. When the transaction is committed, it will be written to the binary log file. If the parameter is too small, it may be) stores all uncommitted binary log records for caching. When the transaction is committed, it will be written to the binary log file. If the parameter is too small, it may be
#It will be written to the disk and affect the performance. You can view the parameter binlog_cache_disk_use indicates the number of times to write to the disk
sync_ Binlog: # by default, the binary log is not written to the binary log file every time it is written, (it can be understood as buffering) if the database goes down abnormally, there will be a lack of records in the binary log file. It can be configured through the parameter sync_binlog=[N]. When N=1, it means that the binary file is written synchronously to the disk. This does not use the buffer of the operating system to write the log.
#N=0 (default). Although setting sync_binlog=1 theoretically ensures synchronization, when innodb storage engine is used, there will be binary recorded operations but transactions rolled back by,
#You can use the parameter innodb_support_xa=1 to solve this problem, innodb_support_xa=1 ensures the synchronization of binary files and storage engine files
binlog_ Do DB: # record the database
binlog_ Ingnore DB: # ignore the database
log_ Slave update: # if the slave role in the current database is copied, the binary log obtained from the master will not be written to its own binary file. If necessary, it needs to be enabled
#This parameter must be enabled if the schema replication of master = > slave = > slave is built
binlog_format: # can be set to the following values
#statement: binary log files are recorded logical sql statements
#Row: records the changes of table rows. If this parameter is set, the transaction isolation level of innodb storage engine can be basically set to READ COMMITTED to obtain better concurrency. It is generally set to row. Although it costs more disk and network overhead, the format is binary format. Use the mysqlbinlog tool to view
#Mixed: mixed mode. statement configuration is used by default
expire_logs_days : #Specifies the time when binary logs are automatically deleted, that is, the log expiration time log_bin_index : #Specify MySQL bin The path to the index file max_binlog_cache_size : #Specify the maximum size of binary log cache, hard limit. The default is 4G, which is large enough. It is recommended not to change it binlog_cache_use: Number of binary log writes using cache(This is a statistical value that changes in real time) binlog_cache_disk_use:The number of times to write binary logs using temporary files. When the log exceeds binlog_cache_size Temporary files will be used to write logs. If the value of this variable is not 0, consider increasing it binlog_cache_size Value of **1.5.5,Log restore** Method 1: direct restore mysqlbinlog --stop-datetime="2014-7-2 15:27:48" /tmp/mysql-bin.000008 | mysql -u user -p password Method 2: first import to a file and then restore mysqlbinlog mysql-bin.000001 > /tmp/a.sql mysql -u root -p password -e "source /tm # 2, Transaction log innodb The transaction log includes redo log and undo log. redo log Is a redo log that provides roll forward operations, undo log It is a rollback log and provides rollback operations. undo log no redo log In fact, they are all logs used for recovery: 1.redo log It is usually a physical log, which records the physical modification of the data page, rather than the modification of a row or rows. It is used to recover the submitted physical data page(Recover the data page and only to the last submitted location). 2.undo Used to roll back row records to a version. undo log Generally, it is a logical log, which is recorded according to each line of records **2.1,redo log** redo log It is mainly buffered by redo logs( redo log buffer)And redo log files( redo log file)Composition. The buffered data is stored in memory. When the machine or service is abnormal, the data in the buffer will be lost. The data of the log file is stored on disk and is a persistent file. Redo logs are written to the buffer first and then refreshed to the disk. How can we ensure that data will not be lost? We can use parameters innodb_flush_log_at_trx_commit This parameter has three values, namely 0,1,2，Before understanding this parameter in detail, let's first understand how the next redo log buffer and redo log file are synchronized: ensure that each log can be written to the transaction log file log buffer The operating system will be called once during the process of writing logs in the log file fsync operation(Namely fsync()system call). because MySQL It works in user space, MySQL of log buffer In user space memory. To write to disk log file in(redo:ib_logfile0 and ib_logfile1 file,undo:share tablespace or.ibd file)，It also passes through the kernel space of the operating system os buffer，call fsync()The role of is to OS buffer The log in is brushed to the log on disk log file Yes. As shown in the figure below:  Next, keep looking innodb_flush_log_at_trx_commit Meaning of the value of: When set to 1, the transaction will be committed each time log buffer Log writes in os buffer And call fsync()Brush to log file on disk Yes. In this way, even if the system crashes, no data will be lost, but because each commit is written to disk, IO Poor performance. (recommended) When set to 0, the transaction will not be committed log buffer Log write to os buffer，Instead, it writes every second os buffer And call fsync()Write to log file on disk Yes. That is, when it is set to 0(about)Refresh the data written to the disk every second. When the system crashes, 1 second of data will be lost. When set to 2, each commit is only written to the os buffer，Then calls per second fsync()take os buffer Logs in are written to log file on disk.  redo log It's for data crash recovery Yes, this is one of the important functions of the database to ensure data security innodb The modification records in the table are also called log files innodb In the storage engine, there are two log files by default. After a new database is created, it will be named ib_logfile0 and ib_logfile1 Two files. If two files do not exist when starting the database, the innodb It is created based on the parameters.  # Summary: experience Now that I have chosen this industry and become a programmer, I understand that only by constantly learning and accumulating practical experience can I be qualified to go up, get a high salary, and have certain economic security for myself, my parents and my family in the future. Learning time is squeezed out by yourself. It may be difficult to see the effect in a short time. Once you stick to it, it will change. It's better to think about why you want to enter this industry and give yourself an answer. When interviewing a large factory, the most basic thing is to consolidate the foundation, otherwise you will be cold if the interviewer asks you casually; Secondly, I will ask some technical principles. It will also depend on the breadth of your knowledge. The most important thing is your ideas, which is more valued by the interviewer. Finally, the above real interview questions of large factories are very good learning materials. Through these real interview questions, you can see the general situation of your mastery of technical knowledge, so as to set a learning direction for yourself. Including the learning guide shared above, you can straighten out the learning route from the learning guide to avoid inefficient learning. **[To get the above information, just click here to download it for free](https://gitee.com/vip204888/java-p7)** **Big factory Java Architecture core notes (suitable for middle and senior programmers):** Foundation, otherwise the interviewer will be cold if you ask casually; Secondly, I will ask some technical principles. It will also depend on the breadth of your knowledge. The most important thing is your ideas, which is more valued by the interviewer. Finally, the above real interview questions of large factories are very good learning materials. Through these real interview questions, you can see the general situation of your mastery of technical knowledge, so as to set a learning direction for yourself. Including the learning guide shared above, you can straighten out the learning route from the learning guide to avoid inefficient learning. **[To get the above information, just click here to download it for free](https://gitee.com/vip204888/java-p7)** **Big factory Java Architecture core notes (suitable for middle and senior programmers):** 