MySQL 5.6安装完成之后，需要进行一些基本的初始化设置，以满足系统上线前基本的测试需要。
该篇在《Linux安装启动MySQL 5.6》的基础之上进行一些基本的初始化设置。
在安装完MySQL-server-advanced-5.6.24-1.el6.x86_64.rpm时，会有如下提示：
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.
You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.
Also, the account for the anonymous user has been removed.
In addition, you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test database.
This is strongly recommended for production servers.
See the manual for more instructions.
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
Verifying : MySQL-server-advanced-5.6.24-1.el6.x86_64 1/1
Installed:
MySQL-server-advanced.x86_64 0:5.6.24-1.el6
Complete!
按照上面提示，我们首先执行脚本/usr/bin/mysql_secure_installation进行基本安全设置：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
[root@bidb ~]# /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] n ---是否修改root密码
... skipping.
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y ---是否移除anonymous用户
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n ---是否只允许root以localhost方式登陆数据库
... skipping.
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y ---是否移除test数据库
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y ---是否重新加载权限表
... Success!
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Cleaning up...
[root@bidb ~]# mysql -uroot -predhat
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.24-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
以上根据MySQL安装完成之后的提示，进行了简单的安全设置。
下面来对MySQL进行基本的参数设置，当MySQL启动的时候，会读取其配置文件my.cnf，类似于Oracle数据库启动时需要加载参数文件一样。
该配置文件默认会按一定的顺序从下面目录读取：
[root@bidb ~]# mysql --help | grep 'Default options' -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
但如果在以上位置都没有找到my.cnf配置文件，这时MySQL会用内置的默认参数启动，或者使用find来精确查找my.cnf文件位置：
[root@bidb ~]# find / -name my.cnf
/usr/my.cnf
如果find也没有找到my.cnf配置文件，则在MySQL配置文件目录/usr/share/mysql下有一个my-default.cnf文件。
默认的my.cnf和my-default.cnf文件相同：
[root@bidb ~]# cat /usr/my.cnf|grep -v ^#|grep -v ^$
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[root@bidb ~]# cat /usr/share/mysql/my-default.cnf|grep -v ^#|grep -v ^$
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
将find找到的my.cnf或是/usr/share/mysql目录下的my-default.cnf文件拷贝到/etc下作为MySQL的配置文件：
[root@bidb ~]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf
MySQL的参数分为以下两种：
1）系统参数：配置MySQL服务器的运行环境，可以用show variables查看
2）状态参数：监控MySQL服务器的运行状态，可以用show status查看
系统参数按其作用域的不同可以分为以下两种：
1）分为全局（GLOBAL）级：对整个MySQL服务器有效
2）会话（SESSION或LOCAL）级：只影响当前会话
有些参数同时拥有以上两个级别，MySQL将在建立连接时用全局级参数初始化会话级参数，但一旦连接建立之后，全局级参数的改变不会影响到会话级参数。
可以通过show vairables语句查看系统参数的值：
mysql> show variables like 'log%';
+----------------------------------------+-------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/lib/mysql/bidb.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+-------------------------+
13 rows in set (0.00 sec)
mysql> show variables where Variable_name like 'log%' and value='ON';
Empty set (0.00 sec)
注意：show variables优先显示会话级参数的值，如果这个值不存在，则显示全局级参数的值，当然你也可以加上GLOBAL或SESSION关键字区别：
show global variables like 'xxx%';
show session/local variables like 'xxx%';
mysql> show global variables like '%max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> show session variables like '%slave_transaction_retries';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| slave_transaction_retries | 10 |
+---------------------------+-------+
1 row in set (0.01 sec)
在写一些存储过程时，可能需要引用系统参数的值，可以使用如下方法：
@@GLOBAL.var_name
@@SESSION.var_name或@@LOCAL.var_name
如果在参数名前没有级别限定符，将优先显示会话级的值。
最后一种查看参数值的方法是从INFORMATION_SCHEMA数据库里的GLOBAL_VARIABLES和SESSION_VARIABLES表获得。
设置和修改系统参数的值，在MySQL服务器启动时，有以下两种方法设置系统参数的值：
1）命令行参数，如：mysqld --max_connections=200
2）配置文件（my.cnf）
在MySQL服务器启动后，如果需要修改系统参数的值，可以通过SET语句：
SET GLOBAL var_name = value;
SET @@GLOBAL.var_name = value;
SET SESSION var_name = value;
SET @@SESSION.var_name = value;
如果在参数名前没有级别限定符，表示修改会话级参数。
注意：和启动时不一样的是，在运行时设置的参数不允许使用后缀字母'K'、‘M'等，但可以用表达式来达到相同的效果，如：
SET GLOBAL read_buffer_size = 2*1024*1024
状态参数可以使我们及时了解MySQL服务器的运行状况，可以使用show status语句查看。
状态参数和系统参数类似，也分为全局级和会话级，show status也支持like匹配查询：
mysql> show status like '%lock%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Com_lock_tables | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6694 |
| Key_blocks_used | 4 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Table_locks_immediate | 84 |
| Table_locks_waited | 0 |
+------------------------------------------+-------+
18 rows in set (0.00 sec)
比较大的不同是状态参数只能由MySQL服务器本身设置和修改，对于用户来说是只读的，不可以通过SET语句设置和修改它们。
下面对MySQL进行一些基本的参数调整和优化：
1）开启慢查询
mysql> show global variables like '%slow_query%';
+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/bidb-slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)
mysql> show global variables like '%long_query%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show global variables like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global long_query_time=0.5;
Query OK, 0 rows affected (0.00 sec)
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.02 sec)
mysql> set global slow_query_log_file='/var/lib/mysql/g7-slow.log';
Query OK, 0 rows affected (0.00 sec)
mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%slow_query%';
+---------------------+----------------------------+
| Variable_name | Value |
+---------------------+----------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/g7-slow.log |
+---------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> show global variables like '%long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> show global variables like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
2）连接数调整
mysql> show global variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> show global status like '%max_used_connections%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> set global max_connections=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.00 sec)
比较理想的设置是：max_used_connections / max_connections * 100% ≈ 85%
还有两个比较重要参数:
wait_timeout=10
max_connect_errors = 100
wait_timeout指的是MySQL终止所有空闲时间超过10秒的连接。
在LAMP应用程序中，连接数据库的时间通常就是Web服务器处理请求所花费的时间。
有时候，如果负载过重，连接会挂起，并且会占用连接表空间。
如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取。
max_connect_errors是出于安全考虑。如果一个主机在连接到服务器时有问题，并重试很多次后放弃。
那么这个主机就会被锁定，直到FLUSH HOSTS之后才能运行。
默认情况下，10次失败就足以导致锁定了。将这个值修改为150000会给服务器足够的时间来从问题中恢复。
如果重试150000次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)
mysql> set global max_connect_errors=150000;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_connect_errors';
+--------------------+--------+
| Variable_name | Value |
+--------------------+--------+
| max_connect_errors | 150000 |
+--------------------+--------+
1 row in set (0.00 sec)
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 10 |
+---------------+-------+
1 row in set (0.00 sec)
3）open_file和table_cache调整
mysql> show global variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 5000 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'innodb_open_files';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 2000 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'table_open_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2000 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 1400 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> set global table_open_cache=10000;
Query OK, 0 rows affected (0.00 sec)
mysql> set global table_definition_cache=10000;
Query OK, 0 rows affected (0.00 sec)
mysql> set global open_files_limit=10000;
ERROR 1238 (HY000): Variable 'open_files_limit' is a read only variable
mysql> set global innodb_open_files=10000;
ERROR 1238 (HY000): Variable 'innodb_open_files' is a read only variable
和Oracle类似，MySQL系统参数修改也分为可在线修改和只读参数两种，例如table_open_cache可以在线修改，而open_files_limit则为只读参数。
只读参数需要在my.cnf配置文件中修改后重启MySQL生效：
[root@bidb ~]# cat /etc/my.cnf|grep -v ^$|grep -v ^#|grep open_files_limit
open_files_limit=10000
[root@bidb ~]# cat /etc/my.cnf|grep -v ^$|grep -v ^#|grep innodb_open_files=10000
innodb_open_files=10000
[root@bidb ~]# service mysql restart
Shutting down MySQL..[ OK ]
Starting MySQL..[ OK ]
[root@bidb ~]# mysql -uroot -predhat
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 10000 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'innodb_open_files';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 10000 |
+-------------------+-------+
1 row in set (0.00 sec)
4）设置innodb_buffer_pool_size
mysql> show global variables like '%innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> set global innodb_buffer_pool_size=10*2014*1024*1024;
ERROR 1238 (HY000): Variable 'innodb_buffer_pool_size' is a read only variable
[root@bidb ~]# cat /etc/my.cnf|grep -v ^$|grep -v ^#|grep innodb_buffer_pool_size
innodb_buffer_pool_size = 10737418240
[root@bidb ~]# service mysql restart
Shutting down MySQL....[ OK ]
Starting MySQL....................[ OK ]
[root@bidb ~]# mysql -uroot -predhat
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like '%innodb_buffer_pool_size';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 10737418240 |
+-------------------------+-------------+
1 row in set (0.01 sec)
5）设置innodb_file_per_table使用独立表空间
mysql> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
6）设置innodb_log_file
mysql> show global variables like 'innodb_log_file%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
+---------------------------+----------+
2 rows in set (0.00 sec)
mysql> set global innodb_log_file_size=256*1024*1024;
ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable
[root@bidb mysql]# service mysql start
Starting MySQL....[ OK ]
[root@bidb mysql]# cat /etc/my.cnf|grep -v ^$|grep -v ^#|grep innodb_log_file_size
innodb_log_file_size=268435456
mysql> show global variables like 'innodb_log_file%';
+---------------------------+-----------+
| Variable_name | Value |
+---------------------------+-----------+
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 2 |
+---------------------------+-----------+
2 rows in set (0.00 sec)
7）临时表设置
临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中。
理想情况下，在内存中创建临时表，但是如果临时表变得太大，就需要写入磁盘中，
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+-------------------------+---------+
每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加，Created_tmp_files表示MySQL服务创建的临时文件文件数
比较理想的配置是：Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，应该相当好了
我们再看一下MySQL服务器对临时表的配置：
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+---------------------+-----------+
只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表
每次使用临时表都会增大Created_tmp_tables，基于磁盘的表也会增大Created_tmp_disk_tables
对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。
长时间观察Created_tmp_disk_tables会显示所创建的磁盘表的比率，您可以确定设置的效率。
tmp_table_size和max_heap_table_size都可以控制临时表的最大大小，因此请确保在my.cnf中对这两个值都进行了设置
8）Open Table情况
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
Open_tables表示打开表的数量，Opened_tables表示打开过的表数量。
如果Opened_tables数量过大，说明配置中table_cache值可能太小，我们查询一下服务器table_cache值：
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+
比较合适的值为：Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%
9）线程使用情况
与表的缓存类似，对于线程来说也有一个缓存。
MySQL在接收连接时会根据需要生成线程，在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。
mysql> show global status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。
Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值.
查询服务器 thread_cache_size配置:
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
10)查询缓存(query cache)
很多LAMP应用程序都严重依赖于数据库，但却会反复执行相同的查询。
每次执行查询时，数据库都必须要执行相同的工作——对查询进行分析，确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。
MySQL有一个特性称为查询缓存，它将（后面会用到的）查询结果保存在内存中。
在很多情况下，这会极大地提高性能，不过，问题是查询缓存在默认情况下是禁用的。
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+-------------------------+-----------+
MySQL查询缓存变量解释：
Qcache_free_blocks：缓存中相邻内存块的个数，数目大说明可能有碎片，FLUSH QUERY CACHE会对缓存中的碎片进行整理。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大。
Qcache_inserts：每次插入一个查询时就增大，命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数，这个数字最好长时间来看：如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。
我们再查询一下服务器关于query_cache的配置：
mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size |203423744|
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存。
query_cache_min_res_unit：缓存块的最小大小。
query_cache_size：查询缓存大小。
query_cache_type：缓存类型决定缓存什么样的查询。
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率=(query_cache_size - Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率=(Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
示例服务器：查询缓存碎片率=20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁，而且可能有些碎片。
作为一条规则，如果FLUSH QUERY CACHE占用了很长时间，那就说明缓存太大了
11）文件打开数(open_files)
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1410 |
+---------------+-------+
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+
比较合适的设置：Open_files / open_files_limit * 100% <= 75％
mysql> show global status like 'table_locks%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Table_locks_immediate | 490206328 |
| Table_locks_waited | 2084912 |
+-----------------------+-----------+
Table_locks_immediate 表示立即释放表锁数
Table_locks_waited表示需要等待的表锁数
如果Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。
示例中的服务 器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足够了
12）表扫描情况
mysql> show global status like 'handler_read%';
+-----------------------+-------------+
| Variable_name | Value |
+-----------------------+-------------+
| Handler_read_first | 5803750 |
| Handler_read_key | 6049319850 |
| Handler_read_next | 94440908210 |
| Handler_read_prev | 34822001724 |
| Handler_read_rnd | 405482605 |
| Handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+
mysql> show global status like 'com_select';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_select | 222693559 |
+---------------+-----------+
计算表扫描率：
表扫描率=Handler_read_rnd_next / Com_select
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB
13）table_definition_cache
表定义信息缓存是从MySQL 5.1.3 版本才开始引入的一个新的缓存区，用来存放表定义信息。
当我们的MySQL中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。
MySQL提供了table_definition_cache 参数给我们设置可以缓存的表的数量。
在MySQL 5.1.25 之前的版本中，默认值为128，从MySQL5.1.25 版本开始，则将默认值调整为256 了，最大设置值为524288，当前版本默认值为528。
注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。
mysql> show global variables like '%definition%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 528 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> show status like '%definition%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_table_definitions | 70 |
| Opened_table_definitions | 0 |
+--------------------------+-------+
2 rows in set (0.02 sec)
Innodb内存的组成
Innodb存储引擎可以分为三部分：内存、进程、数据文件
Innodb的内存的作用大致如下：
缓存磁盘上的数据，方便快速的读取；
对磁盘文件的数据进行修改之前在这里缓存；
应用所作的日志的缓存；
内存结构自身的管理结构
a）Innodb buffer pool
缓冲池是最大块的内存部分，主要用来各种数据的缓冲。
innodb将数据文件按页（16K）读取到缓冲池，然后按最少使用（LRU)算法来保留缓存数据。
数据文件修改时，先修改缓存池中的页（即脏页），然后按一定平率将脏页刷新到文件。
mysql> show variables like 'innodb_%_size';
+----------------------------------+-------------+
| Variable_name | Value |
+----------------------------------+-------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_size | 10737418240 |
| innodb_change_buffer_max_size | 25 |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 268435456 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 300 |
| innodb_sort_buffer_size | 1048576 |
| innodb_sync_array_size | 1 |
+----------------------------------+-------------+
14 rows in set (0.01 sec)
按照数据页的类型
1、索引页
2、数据页
3、undo页
4、插入缓冲
5、自适应哈希索引
6、InnoDB存储的锁信息数据字典信息等
通过show engine innodb status可以查看缓冲池的具体信息
mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2016-08-20 18:53:30 7f919e938700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 2169 srv_idle
srv_master_thread log flush and writes: 2169
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3
OS WAIT ARRAY INFO: signal count 3
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 4866
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1, OS thread handle 0x7f919e938700, query id 33 localhost root init
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
299 OS file reads, 522 OS file writes, 11 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 21249737, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1626134
Log flushed up to 1626134
Pages flushed up to 1626134
Last checkpoint at 1626134
0 pending log writes, 0 pending chkp writes
12 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10989076480; in additional pool allocated 0
Dictionary memory allocated 40512
Buffer pool size 655356
Free buffers 655207
Database pages 149
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 149, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 149, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 81920
Free buffers 81889
Database pages 31
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 31, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 31, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 81919
Free buffers 81919
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 81920
Free buffers 81920
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 81919
Free buffers 81856
Database pages 63
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 63, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 63, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 81920
Free buffers 81865
Database pages 55
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 55, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 55, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 81919
Free buffers 81919
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 81920
Free buffers 81920
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 81919
Free buffers 81919
Database pages 0
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 0, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 12046, id 140263317419776, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
这边的单位是buffer frame，每个buffer frame为16K，通过计算可以查看buffer pool的使用情况
1、Buffer pool size 81919×16×1024
2、Free buffers表示当前空闲的缓冲页
3、Database pages表示已经使用的缓冲页
4、Modified db pages 表示脏页的数量
5、Old database pages表示LRU列表中old sublist中的数据块数量
对上面的innodb buffer pool细看会发现，buffer pool的数据类型又可以分为：page cache、hash index、undo、insert buffer、explicit locks
b）Log Buffer
日志缓冲池（功能跟Oracle redo log buffer基本相似），将重做日志信息放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。
该值一般不需要设置很大，因为一般情况下每一秒钟就会将重做日志缓冲刷新到日志文件，只需要保证每秒产生的事物量在这个缓冲大小之内即可
c）additional buffer pool
在innodb存储引擎中，对内存的管理是通过一种称为内存堆的方式进行的。
在对一些数据结构本身分配内存时，需要从额外获得内存池中申请，当该区域的内存不够时，Innodb会从缓冲池中申请。
但是每个缓冲池中的frame buffer还有对应的缓冲控制对象，这些对象记录了诸如LRU、锁、等待等方面的信息，而这个对象的内存需要从额外内存中申请。
因此，当你申请了很大的 Innodb缓冲池时，这个值也应该相应增加。
简单理解为：额外缓冲池用于管理缓冲池的内容的，所以缓冲池越大额外换池也需要越大。
d）内存计算
used_Mem =
+ key_buffer_size
+ query_cache_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections *(
+ read_buffer_size
+ read_rnd_buffer_size
+ sort_buffer_size
+ join_buffer_size
+ binlog_cache_size
+ thread_stack
+ tmp_table_size
+ bulk_insert_buffer_size
)
以下为MySQL专家叶金荣自己的博客上给出的关于MySQL最重要的参数优化调整建议：
1）选择Percona或MariaDB版本的话，强烈建议启用thread pool特性，可使得在高并发的情况下，性能不会发生大幅下降。此外，还有extra_port功能，非常实用， 关键时刻能救命的。还有另外一个重要特色是 QUERY_RESPONSE_TIME 功能，也能使我们对整体的SQL响应时间分布有直观感受；
2）设置default-storage-engine=InnoDB，也就是默认采用InnoDB引擎，强烈建议不要再使用MyISAM引擎了，InnoDB引擎绝对可以满足99%以上的业务场景；
3）调整innodb_buffer_pool_size大小，如果是单实例且绝大多数是InnoDB引擎表的话，可考虑设置为物理内存的50% ~ 70%左右；
4）根据实际需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求数据不能丢失，那么两个都设为1。如果允许丢失一点数据，则可分别设为2和10。而如果完全不用care数据是否丢失的话（例如在slave上，反正大不了重做一次），则可都设为0。这三种设置值导致数据库的性能受到影响程度分别是：高、中、低，也就是第一个会另数据库最慢，最后一个则相反；
5）设置innodb_file_per_table = 1，使用独立表空间，我实在是想不出来用共享表空间有什么好处了；
6）设置innodb_data_file_path = ibdata1:1G:autoextend，千万不要用默认的10M，否则在有高并发事务时，会受到不小的影响；
7）设置innodb_log_file_size=256M，设置innodb_log_files_in_group=2，基本可满足90%以上的场景；
8）设置long_query_time = 1，而在5.5版本以上，已经可以设置为小于1了，建议设置为0.05（50毫秒），记录那些执行较慢的SQL，用于后续的分析排查；
9）根据业务实际需要，适当调整max_connection（最大连接数）、max_connection_error（最大错误数，建议设置为10万以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小；
10）常见的误区是把tmp_table_size和max_heap_table_size设置的比较大，曾经见过设置为1G的，这2个选项是每个连接会话都会分配的，因此不要设置过大，否则容易导致OOM发生；其他的一些连接会话级选项例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等，也需要注意不能设置过大；
11）由于已经建议不再使用MyISAM引擎了，因此可以把key_buffer_size设置为32M左右，并且强烈建议关闭query cache功能；
关于原文请参考：
比较全面的MySQL优化参考（上篇）http://imysql.com/2015/05/24/mysql-optimization-reference-1.shtml
比较全面的MySQL优化参考（下篇）http://imysql.com/2015/05/29/mysql-optimization-reference-2.shtml
网络找到的一篇关于MySQL参数优化不错的文章http://www.cnblogs.com/chenpingzhao/p/4850942.html
MySQL 5.6初始化设置
发表回复