MySQL optimization in the production environment and mysql optimization in the environment

MySQL optimization in the production environment and mysql optimization in the environment

A: hardware optimization:

1. Use a 64-bit cpu with at least 4 CPUs. The larger the L2 cache, the better.
2. Large Memory, 1-2 instances running 32-64 GB, and 3-4 instances running 96-128G
3. sas disks are used for mechanical disks. The speed is above 15000. If possible, ssd disks are used.
4. The RAID card uses raid10 for the master database and raid0 for the slave database.
5. Multiple NICs, more than 1 GB
6. Do not use virtualization for databases. The slave hardware is better than the master

B: Operating System Optimization

1. Select x86_64-bit for the operating system and try to use the xfs file system.
2. Optimized disk storage Parameters
3. Optimize Kernel Parameters
4. Network Optimization

C: mysql Architecture Optimization

1. Configure the server to run multiple instances based on the memory size
2. Master/Slave replication adopts the mixed mode. Do not synchronize data between different data centers as much as possible. If you want to synchronize data between data centers, use remote writing and local reading as much as possible.
3. Regularly check and fix Master-slave replication data differences
4. Business Splitting: the search function does not use MySQL databases for execution. Some high-concurrency and secure businesses use nosql, such as memcache and redis.
5. Database Front-end cache, such as memcache, used for user login and product Query
6. static dynamic data, static entire file, and static page fragments
7. Database Cluster read/write splitting, one master, multiple slaves, and cluster read/write splitting through dbproxy
8. If a single table exceeded 8 million, split the database and table, for example, manually split the table (login, product, order)
9. Select slave database backup and perform database/table sharding backup

D: MySQL database Optimization

1. Optimize the parameters of my. cnf
2. Optimized database table design, including character set, String Length, short index creation, and multi-purpose composite index;
3. Optimize SQL statements to reduce the number of slow statements;

E: database management process and system optimization

1. Human process: Development-> core O & M/DBA
2. Test Procedure: Perform online test on the Intranet IDC
3. Client Management, PHPMYADMIN

F: MySQL database security Optimization

1. Disable Internet settings for Databases
2. Database File Permission Optimization;
3. permission restrictions for authorized users.
4. restrict development operation permissions on the production database
5. Prevent SQL statement Injection

Address: http://lidao.blog.51cto.com/3388056/1945391