MySQL Reset Password and optimization

First, resetting the MySql database root password

L Modifying a configuration file

vim /etc/my.conf   # database location generated at installation

# # # in [mysqld] This module Add code:skip-grant

/etc/init.d/mysqld Restart # changes need to restart service to take effect

   re-login root user – uroot no password required to log in root account

show databases; # See which databases are available

Use MySQL; # use mysql database , password stored in this database

Show tables; # See what tables are available

# Change the user table root password to XXX

Update user set password=passwoed ('XXX') where user=' Root

# exit the database

Quit

L Delete the skip-grant in the my.conf [mysqld] module and restart the service.

to Speed up network database access , add code to the [mysqld] module:

Skip-name-resolve

Skip-innodb # Data volume is large with InnoDB ( such as bank ) data volume small with MyISAM

Second, Mysql Optimization

MySQL tuning can be done in several ways:

1. Architecture Layer:

Do from the library, to achieve read and write separation;

2. System level:

Increased memory;

raid0 or RAID5 the disk to increase the read and write speed of the disk;

You can re-mount the disk and add the noatime parameter, which reduces the I/O to the disk;

3. MySQL itself tuning:

(1) if master-slave synchronization is not configured, the bin-log function can be turned off to reduce disk I/O

(2) add skip-name-resolve to my.cnf to avoid MySQL due to delay in parsing host name Slow Execution

(3) adjust several key buffer and cache. The basis of adjustment, mainly according to the state of the database debugging.

4. Application level:

View the slow query log, based on the SQL statements in the slow query log optimizer , such as increasing the index

5. adjust several key buffer and cache

1) key_buffer_sizeFirst you can set it according to the size of the system's memory, probably a reference value:1Gthe following memory settings128M;2g/256m; 4g/384m;8g/1024m;16g/2048m.This value can be checked by checking the status valuekey_read_requestsand theKey_reads,can knowkey_buffer_sizesetting is reasonable. Proportionskey_reads/key_read_requestsshould be as low as possible, at least1:100,1:1000Better(The above status values can be usedSHOW STATUS like'key_read%' Get). Note: This parameter value setting is too large to be the overall efficiency of the server is reduced!

2) Table_open_cacheWhen a table is opened, the data in the table is temporarily placed in this part of memory and is generally set to1024x768That's enough, it's size we can measure by this way: if you findOpen_tablesequalsTable_cache, andOpened_tablesGrowing , then you need to increaseTable_cache's value.(The above status values can be usedSHOW STATUS like'Open%tables' Get). Be careful not to blindlyTable_cacheset to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

3) Sort_buffer_size the size of the buffer that can be used when the query is sorted , and the allocated memory for that parameter is per-connection exclusive ! if there is - connection, the total sort buffer size that is actually allocated is - x 4 = 400MB . Therefore, the recommended setting for a server that has around 4GB is 4-8m.

4) Read_buffer_size The buffer size that can be used by the read query operation. As with sort_buffer_size , the allocated memory for this parameter is exclusive to each connection !

5) join_buffer_size The size of the buffer that can be used by the Federated query operation, as with sort_buffer_size , the allocated memory for this parameter is also per connection exclusive !

6) Myisam_sort_buffer_size This buffer is primarily used to repair the memory used by the sort index during the table process or the memory size used to sort the index when indexing, generally 4G memory to 64M can be.

7) Query_cache_size MySQLthe size of the query operation buffer is adjusted by the following practices:SHOW STATUS like'qcache%'; ifQcache_lowmem_prunesThis parameter records how many queries have been removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately. If the value is very large, it indicates that there is often insufficient buffering and that the cache size needs to be increased; Qcache_free_memory:query cache memory size, through this parameter can be very clear know the current system query memory is sufficient, is more, or not enough, we can make adjustments according to the actual situation. Under normal circumstances4GMemory Settings64MThat 's enough.

8) thread_cache_size indicates that the number of threads stored in the cache can be re-used, refer to the following values:1G -> 8 2G -> 16 3G -> >3g ->

In addition, there are a few more key parameters:

9) Thread_concurrency This value is set to 2 times The number of CPU cores

Wait_timeout indicates idle connection timeout, default is 28800s, this parameter is used with interactive_timeout , This means that in order for Wait_timeout to take effect, the interactive_timeout must be set at the same time and both are recommended to be set to 10

One) max_connect_errors is a Security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations. is not much related to performance. In order to avoid some errors, we generally set a larger, such as 10000

Max_connections Maximum number of connections, according to the amount of business requests to adjust, set enough

Max_user_connections refers to the maximum number of connections that the same account can connect to the MySQL service at the same time. Set to 0 to indicate no limit. Usually we set it to enough

This article is from the Linux blog, so be sure to keep this source http://lstulinux.blog.51cto.com/3938932/1701469

MySQL Reset Password and optimization