Linux Operations (III)

For several days did not review, the skull has responded but came, was asked to own research, absolute SB, naught! Hope to receive a phone call tomorrow, enter the desire of e-commerce company!

1, a SQL statement query very slow how to do?

A: (1) Log in to Mysql,show global status, and Show Processlist View database system Status

(2) Check the slow query log, show variables like '%slow% ', analysis is that those SQL statements are slowing down the system

(3) VI/ETC/MY.CNF configuration file, modify parameters, such as the maximum number of connections and table cache

(4) reasonably add index

2. Which fields are suitable as indexes and which fields are not suitable for indexing? What are the differences between internal connections and external connections?

A: Suitable for: Foreign key fields, primary key fields, fields in the WHERE clause.

Not appropriate: Data that is frequently updated is not suitable for indexing, full table scan is faster than indexing, does not need to be indexed, like queries are preceded by%, and an or statement is not suitable for indexing

Inner join: Matches the same record row for a column value in the left and right tables

Outer joins: also divided into left outer join, right outer join, full outer JOIN, return one table match row, and in another table does not match the behavior null value

The left outer join is based on left table, to match the right table, if there is a match then return, if not match, the corresponding column null value. How many data are there in the left table, and how many data are the results?

3. Transaction ISOLATION level?

A: (1) Non-committed read, can read UNCOMMITTED transaction data, also known as dirty Read

(2) Read-committed, read-only data that has been committed, also known as non-repeatable read

(3) Repeatable read, when the transaction reads a row, another transaction modifies and commits the row, and the previous transaction sees the new row, also known as the Phantom Read

(4) serialization read, forcing the order of transactions, making it impossible to conflict, so as to solve the phantom reading problem

4, MySQL database CPU soared to 500%, how he handled it?

A: (1) Multi-instance server, first top view is that a process, which end share population with more CPU;

(2) Show processlist see if the load problem is caused by a large number of concurrent locks;

(3) Otherwise, look at the slow query, find out the execution time of the Sql;explain analysis SQL whether to go index, SQL optimization;

(4) To see if cache invalidation is caused, you need to see the buffer hit ratio

5, bash for loop print the following words in this sentence is not more than 6 letters (Kunlun Worldwide interview)

I am oldboy teacher Welcome to Oldboy training Class.#!/bin/bashfor word in I am Oldboy teacher Welcome to Oldboy Training class do if [' Echo ${word}|wc-l '-le 6] #-L,--max-line-length shows the length of the longest line then echo $word fi don E

6. Regular expressions match rows with IP addresses

Answer: Grep-e "([0-9]{1,3}\.) {3} [0-9] {1,3} "Test.txt # #事实上, this is not the correct answer, but also need to subdivide 255, etc., but this good remember, generally applicable, but also must be extended

7, [1,15,4,6,29,22] to sort him out, and remove the brackets and commas (this if the pen test encountered, kneeling)

A: (1) sed-i ' s/,//g; s/\[//g; s/\]//g ' Test.txt # #把逗号变为空格, and delete the brackets,-i changes the original file, but-I does not output the file

(2) sed-i ' s//\n/g ' test.txt |sort-nr # #把空格变为换行, and reverse sort

(3) sed-i ' s/\n//g ' test.txt # #已经排完序, but need to change to the horizontal output

8, set the NIC MTU to 1K command is

A: Temporary effect: ifconfig eth0 MTU 1024

Permanent effect: echo "mtu=1024" >>/etc/sysconfig/network-scripts/ifcfg-eth0

9: Change the system environment variable of the current Linux terminal to GB2312;

Answer: Export LANG=ZH_CN. GB2312

10, set up a user group (group) Group1, the group can only read the directory/opt/dir1;

Answer: Groupadd group1;setfacl-m g:group1:r--/opt/dir1

11, if every day 3:00, need to automatically forcibly terminate the process MyProc (using kill stop), and then restart (execute the myproc command), you should execute the script command is:

0 3 * * */bin/pkill-18 MyProc

12. Use awk to achieve the following results? (It's hard to make it if you don't know the paste command)

Cat 1.txt

1 2 3

1 2 3

Cat 2.txt

A b C

A b C

Implementation: 2 A C

2 A C

Answer: Paste 1.txt 2.txt |awk ' {print $2,$4,$6} '

13. When will the lock be triggered by the type of MySQL lock?

Answer: page level: Engine BDB.

Table level: Engine MyISAM, understood to lock the entire table, can read at the same time, write No

Row level: Engine INNODB, single row of records plus lock

When modifying with an indexed field as a condition, whether the table lock depends on whether the index field determines the record is unique, when the index value corresponding record is not unique, the lock table is performed, and the row lock is instead.

The read lock should be the first SQL to be freed, and the write lock to be released at the end of the entire transaction

14. The difference between session and Cookie

A: (1) The cookie data is stored on the client's browser and the session data is placed on the server.

(2) Cookies are not very secure, others can analyze cookies stored locally and make cookie spoofing, considering that security should use the session because it does not arbitrarily read the information stored by the customer.

(3) A single cookie cannot hold more than 4K of data, and many browsers limit a maximum of 20 cookies per site.

15. How does the MySQL index be implemented? Index type?

A. Way: B + Tree, hash index, bitmap index type: Normal index, primary key index, unique index, clustered index

Reference Document: Http://www.cnblogs.com/barrywxx/p/4351901.html

16, the session principle and sharing method.

A: (1) Definition: Session is a storage space maintained by the server, when the user connects to the server, a unique session_id is generated by the service side, and the session_id is called an identifier to access the session storage space on the service side.

(2) Principle: SessionID This data is stored in a cookie, and when the user submits the page, the SessionID is submitted to the server for access to the session data. However, it is not entirely dependent on cookies and can be passed SessionID through the server-side automatic URL rewriting, and the entire process is transparent to the programmer.

(3) Mode: cookie-based sharing, database-based sharing, memcached-based sharing

Reference Document: http://blog.csdn.net/zhuanshenweiliu/article/details/38844741

17. Take out the number of shells appearing in/etc/passwd? (awk array resolution)

Answer: Awk-f ":" ' {arr[$NF]++}end{for (shell in arr) {print Shell,arr[shell]}} '/etc/passwd

18, Merge file problem (this problem is more difficult than the above, mainly the problem of sorting and size transformation)

A.txt

100 $5,000

200

300 $3,000

400 $1,250

B.txt

Json Smith

John Doe

Sanjay Gupta

Ashok Sharma

Request Results

Ashok Sharma $1,250

John Doe

JSON Smith $5,000

Sanjay Gupta $3,000

Answer: Paste b A|awk ' {print $1,$2,$3,$5} ' |TR [: Upper:] [: Lower:]|sort-k 2 >c.txt

# #tr命令将大写转为小写, sort-k sorted by keyword, 2 for second column

19, print the local Exchange partition size, output as follows: (This problem is difficult, if not on the machine, it is difficult to make)

swap:2016m

Answer: Free-m|grep-i Swap|awk ' {print $1,$2 ' m '} ' or the following second method

Top-n 1|grep swap|sed ' s/k.*//g ' |awk ' {print $1,$2/1024 ' M '} '

# #top command shows system resource usage,-N 1 means only 1 calls, swap:16779884k Total replaces K and subsequent strings with NULL, so this leaves swap:16779884, divided by 1024.

20. Test output the time it took to create 20,000 catalogs? I think it's a difficult problem if I don't see it in my day! ）

Time for I in ' seq 1 20000 '; Do mkdir "$i" &>/dev/null; Done

Output

Real0m0.065s

User0m0.015s

SYS0m0.024s

# #值得注意的是do后面没有分号, the other/dev/null just put the results of the screen into a black hole, but still produce these directories

21, to seek the root of the user login how much time.

A: Use the current time minus the login time, one step can not be used script

#!/bin/bashlogin= ' Who am I | awk ' {print $4} ' logintime= ' date-d $login +%s ' ##%s is the meaning of taking seconds, because only hours above: minutes now= ' date +%s ' ##-d displays the time described by the specified string, not the current time let last= Now-logintime # #用let命令进行计算操作时不需要用 $ sign Let Lasttime=last/3600echo "logged on for $lasttime hours"

22. Nagios monitoring the communication process of Linux server?

A: When Nagios needs to monitor the service or resource conditions of a remote Linux host:

1) Nagios runs the Check_nrpe plugin, and we want to tell it what to check in the Nagios configuration file (/usr/local/nagios/etc/objects/commands.cfg)

2) The Check_nrpe plug-in will connect to the remote Nrpe via SSL daemon

3) NRPE Daemon will run the appropriate Nagios plug-in to perform checks on local resources or services

4) NRPE Daemon Returns the results of the check to the Check_nrpe plugin, which the plugin submits to the Nagios program for processing

5) CGI graphic display to the user

Note: NRPE daemon requires Nagios plug-ins to be installed on remotely monitored Linux hosts, otherwise, daemon cannot do any monitoring

23, has the commodity table, has the primary key goods_id, the column cat_id, the price prices, has already added the index on the price column, but by the price inquiry still is very slow, may be what reason.

A: In the actual situation, an e-commerce website of a lot of goods, directly in all goods, according to the price of goods, is very few, the general customers have come to the classification, and then check.

Correction: Remove the index of the individual price column, plus (cat_id,price) compound index and query

24. Explain the meaning of 30, int (20) in the varchar (30) in 20? Why is it so designed? What is the upper and lower limit of the numbers stored in int (20)?

A: 20 means that the maximum display width is 20, but still occupies 4 bytes of storage, storage range is unchanged; varchar (30) 30 has a maximum storage of 30 characters, which is a variable length.

Design reason: The actual business has this demand. 4 bytes is 32 bits, possibly the first bit is 0, then the positive number is 2^32, the first bit is 1, the negative number is-(2^32-1)

25. mysql master-slave copy binlog log format

A: row: only need to record which record has been modified, what kind of change

Statment: Every SQL statement that modifies data is recorded in the Bin-log of master

Mixed: The combination of the first two patterns

26, how to forward the local 80 port request to 9000 port, the current host IP is 192.168.2.1 (this problem is often confused)

Answer: iptables-a prerouting-d 192.168.2.1-p tcp-m tcp--dport 80-j DNAT--to-destination 10.0.0.18:9000

27, handle the following oldboy.log, remove the domain name and count the order

Http://www.etiantian.org/index.html

Http://www.etiantian.org/1.html

Http://post.etiantian.org/index.html

Http://mp3.etiantian.org/index.html

Http://www.etiantian.org/3.html

Http://post.etiantian.org/2.html

Answer: awk-f "/" ' {print $} ' Oldboy.log |sort|uniq-c

28, how to improve the security of MySQL?

A: (1) If the MySQL client and server connections need to cross and pass through an untrusted network, then SSH tunneling is required to encrypt the connection's traffic.

(2) using the Set Password statement to modify the user's password, first "Mysql-u root" login to the database system, and then "mysql> Update Mysql.user set Password=password (' Newpwd ')", Finally execute "flush privileges" on it.

MySQL needs to beware of attacks that have, anti-eavesdropping, tampering, replay, denial of service, etc., not involving availability and fault tolerance. All connections, queries, and other operations are done using security measures based on ACLs, which are access control lists. There are also some support for SSL connections.

(3) Any user other than the root user is not allowed to access the user table in the MySQL master database; If the encrypted user password is stored in the user table after the encryption, the other person can freely use the corresponding database of the username/password;

(4) The use of grant and REVOKE statements for user access control work;

Using a firewall can remove 50% of the external danger, the database system to hide behind the firewall to work, or placed in the DMZ area;

(5) In order to prevent malicious incoming illegal parameters, such as where id=234, others but input where id=234 or 1=1 cause the full display, so in the Web Form using "or" to use the string, in the dynamic URL to add the%22 for double quotes,% 23 for the pound,% 27 represents single quotation marks; passing unchecked values to the MySQL database is very dangerous;

(6) Make sure that only users who start the database service in the MySQL directory can have read and write permissions to the file;

29, thread communication, and thread synchronization mechanism?

A: communication: As with the process, Message Queuing, shared memory, sockets, signals, semaphores, pipeline communication

Synchronization: (1), critical section: Through the serialization of multithreading to access public resources or a piece of code, fast, suitable for controlling data access. Only one thread is allowed to access the shared resource at any time, and if more than one thread attempts to access the public resource, the other threads that attempt to access the public resource will be suspended after one thread enters, and wait until the thread that enters the critical section leaves and the critical section is freed before other threads can preempt it.

(2), mutex: adopt mutually exclusive object mechanism. Only the thread that owns the mutex has access to the public resource, because there is only one mutex object, so that the public resources are not accessed by multiple threads at the same time. Mutual exclusion can not only realize the common resources security sharing of the same application, but also realize the security sharing of common resources of different applications.

(3), Semaphore: It allows multiple threads to access the same resource at the same time, but needs to limit the maximum number of threads that access this resource at the same time

(4), event: the way to maintain thread synchronization by notification operation, but also to facilitate the implementation of priority comparison of multiple threads operations

30, MySQL optimization?

A: (1) The design of the table is reasonable (in accordance with 3NF) (2) Add appropriate index (3) Sub-table technology (4) Read and write "Update/insert/delete" separation (5) Stored procedure "Modular Programming" (6) MySQL profile optimization "Configure maximum concurrent count, cache size" (7) Hardware upgrade (solid state drive or RAID array) (8) Timed removal of unwanted data, timed defragmentation

Linux Operations (III)