MySQL Base statement

View current MySQL-supported storage engines
Show engines;

View permissions for a user
Show grants for UserName;

How to know the current binary log file and position value
Show master status;

What command to switch binary Log
Flush logs;

What command to repair the MyISAM table
Repaire table tabname;

What commands are used to defragment the table data file
Optimize table tabname;

How to get the Taba table statement
Show CREATE TABLE TabA;

Create Tabb table, complete copy of Taba table structure and index, no data
CREATE table TabB like TabA;

Added field cola for Taba table, decimal type with no deviation, 10-bit integer, 2 decimal, indexed
ALTER TABLE TabA add ColA decimal (12,2), add key (ColA);

How to empty data from a Tabb table
TRUNCATE TABLE TabB;

How to do not enter the MySQL client, execute a SQL command, account user, password passwd, library name dbname,sql "SELECT Sysdate ();"
mysql-uuser-ppasswd-d dbname-e "Select Sysdate ();"

How to parse all the SELECT statements from slow log, output the first 20 entries in reverse order, and log the name Slow_log.file
Mysqldumpslow-g ' SELECT '-S c-r slow_log.file

How to parse the SQL statement for the Anjuke library with time from "2017-10-27 13:00:00" to "2017-10-27 18:00:00" from Binlog, with the log name Bin_log.file
mysqlbinlog-d Anjuke--start-datetime= ' 2017-10-27 13:00:00 '--stop-datetime= ' 2017-10-27 18:00:00 ' bin_log.file

Create user, username iamdba, password is mypasswd, access source 10.11.8.X, that is, 8 IP is allowed, the Anjuke library all tables have "delete and change" permission
Grant Insert,delete,update,select on anjuke.* to [e-mail protected] ' 10.10.8.% ' identified by ' mypasswd ';

How to execute a SQL file, account user, password passwd, library name dbname,sql file named Sql.file
Mysql-uuser-ppasswd-d DBName

Export all TabA data to the/tmp/taba.txt file in select mode
SELECT * from TabA into outfile '/tmp/taba.txt ';

Import TabA.txt data into a Tabb table, such as a primary key or unique key violation, overwriting data in a Tabb table
Load data [local] infile '/tmp/taba.txt ' replace into table TabB;

Use the mysqldump command to export the Tabb table in the Anjuke library to tabb.sql files that meet the "ColA > 100", do not export the build statement, account user, password passwd
MYSQLDUMP-UUSER-PPASSWD--no-create-info Anjuke tabb-w "ColA > >tabb.sql"
PS:--no-create-info can also be written as-t

Switch sync to master 10.11.8.11, Port 3307, username repl, password passwd, start Binlog to db-master.000001, position 123456
Change Master to master_host= ' 10.11.8.11 ', master_port=3307, master_user= ' repl ', master_password= ' passwd ', Master_log _file= ' db-master.000001 ', master_log_pos=123456;

Start the SQL process for slave and stop on the db-master.000003 file 123,456-bit sync to master
Start slave sql_thread util master_log_file= ' db-master.000003 ', master_log_pos=123456;

Build an optimal index that meets the following five criteria
where a=? and b=? and c=?
where a=? and B>? and c=?
where a=? and b in (?) and c=?
where a=? and c=? ORDER BY B
where a=? ORDER BY C,b
Create a composite index in order (A,C,B)

There are two composite indexes (A, B) and (c,d), how do the following statements use the index? What kind of optimization can be done?
Select from Tab where (a= and b=?) or (c=? and d=?)
Based on the MySQL mechanism, only a composite index with good filtering effect can be used to optimize the following
Select from Tab where a=? and b=?
Union
SELECT * from Tab where c=? and d=?

How to Count file a.txt How many non-blank lines are there?
Grep-c ' ^. *$ ' A.txt
Or
Grep-v ' ^$ ' a.txt | Wc-l

File B.txt, each line with ":" Character into 5 columns, such as "1:apple:3:2017-10-25:very good", how to get the sum of the third column of all rows
awk ' BEGIN {fs= ': "; S=0} {s+=$3} END {print S} ' B.txt

Capture the 60th to No. 480 line of the file c.txt, ignoring the case, and counting the record with the most repetitions, and the number of repetitions
Sed-n ' 60,480 ' P c.txt | Sort | Uniq-i-C | Sort-rn | Head-n 1
Second, simple answer 5x5

What are the characteristics of MyISAM and InnoDB? What are the scenarios for each?
MyISAM, table lock, does not support transactions, table corruption rate is high, divided into MyD data files and myi index two files, read and write concurrency is not as InnoDB, suitable for more insert scenes, and support the direct copying of files to back up data
InnoDB, row lock, support transaction, crash with recove mechanism, only IBD file, divided into data area and index area, have good read and write concurrency ability, but do count () operation quite consumes CPU

What are the backup methods that MySQL native supports and what are the pros and cons?
(1) Direct copy of the data file, must be a MyISAM table, and the use of flush tables with read lock, the advantage is simple and convenient, the disadvantage is that need to lock write, and can only be restored on the same version of MySQL use
(2) Mysqldump, the export is the SQL statement, so you can recover across the version, but need to import data and rebuild the index, the recovery time will be longer, if the MyISAM table, also need to lock the table, if it is InnoDB table, You can use the--single-transaction parameter to avoid this problem

What are some of the rules to be aware of when creating and using indexes?
Avoid too many indexes that can affect write performance
Index the field with low filter effect, almost invalid, such as gender, status flag, etc.
When each query executes, only one index is used, and a composite index should be created if necessary
The compound index is used in accordance with the "left to right" principle, the left percent sign is strictly forbidden
Do not have operations on indexed fields and use functions, you will not be able to use indexes

How do I analyze the execution performance of an SQL statement and what information is being followed?
Regardless of performance, do not have subqueries and nested SQL, try not to have a join query
Using the explain command, observe the type column, you can know whether it is a full table scan, and the use of the index, watch key can know which index to use, observe Key_len can know whether the index is used to complete, observe rows can know whether the number of rows scanned is too much, Observe extra to see if temporary tables are used and additional sorting operations are performed

What mechanism does MySQL use to control access rights?
MySQL library, from user table to host table and DB table, to Tables_priv table and Colums_priv table

MySQL Base statement