Day5-mysql Log Management

Log file

Error log--log-error Error-log=/data/mysql/mysql3306/error.log
General Log--general_log=/data/mysql/mysql3306/general_log
Slow query log--slow_query-log=/data/mysql/mysql3306/slow_log analysis tool: Mysqldumpslow/pt-query-digest
Binary Log--log-bin=/data/mysql/mysql3306/mysql-bin (keep 7-day Log expire-logs-days = 7) analysis tool: Mysqlbinlog/binlog2sql
Audit log (official fee plugin)--audit_log--audit_log_file audit.log

Binary log

33DDL DML DCL DQL

When to switch:
1, flush logs; (If the card is not moving, there may be a big transaction is not over, a transaction can only write to a binlog inside)
2, more than log settings max_binlog_size
3. Database restart

Format of binary logging:
Binlog_format=row
Statement: Record execution of SQL statements (past, master and slave versions, migrations, over-state of upgrades)
INSERT into T1 (ID,C1) VALUES (1,uuid ()); The Lord never agrees.
Row: Record data before and after the change (for production)
Mixed: (Do not use)

View Binlog:
Show binary logs;
Show master status; 5.7 Gtid:select @ @server_uuid; Find your server or get the execution from somewhere else.
Show Binlog events in ' mysql-bin.000001 ';
MySQL binary is stored in ' event ' as a unit in the log, a insert,update ... There are multiple events composed.
Event:begin,table_map,x_event,commit;
Position: Byte offset (location) Show Master status shows the position is the size of the log.

To view binary logs:
Unable to view with text
Logs are stored in a compact binary format, with event combinations
Use Mysqlbinlog to view
Mysqlbinlog-v--base64-output=decode-rows mysql-bin.000001 > T.log

To delete a binary log:
Time-based deletion
Set global expire_logs_days=7;
Purge binary logs before now ()-interval 3 day3;
Delete based on file name
Purge binary logs to ' mysql-bin.000010 ';

Audit log:
Audit--log_policy option settings
Default All

Log files generate a server activity audit record:
The contents include the error records that occur in the system,
The time the client connects and disconnects,
Actions performed during the connection,
Access to the database and tables.

MySQL Common commands

Help show; Help information
Pager more; Pagination View
Pager less; View up and down
Pager cat; Return

INFORMATION_SCHEMA:
Central repository, disk cannot be found, only select.

View the storage engine for the table below the schema:
Select Table_name,engine from information_schema.tables where tables_schema= ' test '
To view the character set of a table:
Select Character_set_name.collation_name from information_schema.colltions where is_default= ' Yes ';
See how many long tables are under each library:
Select Table_schema, COUNT (*) from Information_schema.tables GROUP by Table_schema;

Library renaming:
1, create database NewData;
Rename Olddata.t1 to Newdata.t1;
2. Select concat ("Rename table OldData.", TABLE_NAME, "to new.", TABLE_NAME, ";" from information_schema.tables where table _schema= ' OldData ' into outfile '/tmp/rename.sql ';
Source/tmp/rename.sql;
3, Terminal mysql-e "" > Rename.sql

Statistics Library Size:
Unit m:select sum (data_length+index_length)/1024/1024 from information_schema.tables where table_schema= ' test ';
Count the size of each table:
Unit M:select table_name,sum (data_length+index_length)/1024/1024 from information_schema.tables where table_schema= ' Test ' GROUP BY TABLE_NAME;

Show Core statement
Show open tables; View open Tables of course
Show table status like '; High concurrent use is not recommended
Show columns from TB;
Show full columns from TB;
Show databases like "t%";
Show columns from DB where ' Default ' is null;
Show character set;
Show collation;

Show global variables; Global Globals
Show variables; Current session

Show @@ XXX; Global

Show @ xxx; Session

Practice Script:
1, error if there is a change, print out the changes:
Backing up Error,diff backup files and online files

Day5-mysql Log Management