MYSQ ' L series 10.mysql optimization & Access Control

Website open slow How to troubleshoot

1. Open the Web page, F12with Google Chrome, and view the network: Which load time is long to optimize which

2. If it is a database problem

2.1 View General Conditions

# Top

# uptime//load average Load

Mysql> Show full processlist;

2.2 Viewing the slow query log:

Long_query_time = 1

Log-slow-queries =/data/3306/slow.log

Log Analysis tool:

Mysqldumpslow Mysqlsla Myprofi Mysql-explain-slow-log mysqllogfilter

2.3 Observing the execution of SQL statements:

Mysql> explain ....

2.4 Viewing criteria fields uniqueness of the value of field

Mysql> Select COUNT (distinct Ader) from TB1;

2.5 Where conditional statements end with an equal sign, which is the most efficient

2.6 View Report

./mysqlreport--user=root--password=123456--socket=/usr/local/mysql/tmp/mysql.sock

Search the pressure on the Web site database

1. The user login from the business to search, reduce the number of searches (if this logic is not possible, give up)

2. If there is a large number of frequent searches, usually crawler crawling the site, then analyze the Web log to seal off this IP

3. Configure multiple master-slave synchronization, to achieve read and write separation, let like statements to query from the library

4. Add memcached server to the front of the database

5. Use Sphinx to implement database search because like statements are difficult to optimize

6. For the search to build a separate cluster , the daily reading of the library to calculate the search index, save on the server to provide search, every 5 minutes to make an increment from the library (this method is generally done by large companies)

MySQL Optimized

Hardware optimization

Cpu

Mem

Disk

Card

SQL Statement Optimization

1. Index optimization

2. Large SQL statement split into multiple small: subquery join table Query

3. Do not place the calculation in the database

4. Search function do not use database

Architecture optimization

1. Business splitting: For example, search function with Sphinx, some business applications using NoSQL persistent storage

2. Database front-end plus cache:memcached Redis

3. Data static: Entire file static, page fragment static

4. DB cluster and read/write separation

5. Demolition of the list: Single table 20 million and other particularly large cases

Software optimization

Operating system: x86_64

Software: MySQL compilation installation

my.cnf parameter Optimization

The amplitude of the optimization is very small

process, system, security optimization

See below

Systems and processes

1. Project Development

Office Development Environment---Office test environment---IDC test environment---IDC formal environment

2. Database Updates

Developer submits requirements---Development Supervisor Audit---Department leader review---DBA audit---DBA Execution---IDC execution

3.DBA participate in Project database design and audit

Account access Control

1. The need to be ambiguous

2. Office and test environment can release permissions, IDC test and formal environment to strictly control database write permissions

3. Developer Official Environment database: Give separate read-only permissions to non-external services, cannot assign write permissions to the official library (main library)

4. Special personnel (such as leadership) need authority, to ask clearly what he does, E- Mail reply : Note The user name, password, scope of authority, more reminders of his operation precautions, if possible by the DBA personnel in lieu of their operation

5. Privileged accounts are controlled by the DBA

Web Account Rights Assignment

1. The default permissions for the Write library account are: Select, INSERT, UPDATE, delete, do not change tables or all permissions

2. The default limit for Read library account is select ( with read-only parameter)

3. Dedicated library accounts , especially small companies with a lot of broken libraries

4. If the lamp is integrated in a single server environment, DB permissions are primarily set to localhost

5.WWW and DB are separate, depending on the number of Web services per IP or network segment to authorize

Database Client Access Control

1. Change the default MySQL client port

2. The database Web client is deployed uniformly on 1-2 servers that do not serve, restrict IP and port, and can only be accessed from the Office intranet.

3. Do not do public domain name resolution, with host or internal IP to achieve access

The 4.phpadmin Site Directory is independent of the other site root directory and can only be accessed by the specified domain name or IP

5. Restrict the use of Web Connection accounts to manage databases and specify account access based on user role settings

6. Manage accounts according to the position role assignment by any development and related

7. Set the specified account access (Apache/nginx authentication + mysql user two login limit)

8. Unified all Database account Login entry address, prohibit all developers to upload phpadmin and other database management programs without permission

9. Open VPN, springboard, internal IP to manage database

;

MYSQ ' L series 10.mysql optimization & Access Control