分类目录归档:Database

MySQL 5.6初始化设置

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

Linux安装启动MySQL 5.6

本篇是在《Linux卸载原生MySQL数据库》的基础之上进行安装,关于安装过程请参考http://www.cnblogs.com/zhuque/archive/2012/11/03/2763353.html

1.环境描述
MySQL版本5.6
OS版本RHEL 6.6

2.安装MySQL前准备
2.1.检查该环境中是否已安装MySQL
1)检查方法:netstat –ano命令查看MySQL默认端口3306是否存在
2)在命令行中执行mysql
3)使用rpm -qa|grep -i mysql查看
2.2.在Oracle Software Delivery Cloud下载MySQL 5.6安装包
安装包V75295-01.zip包含以下rpm包:
MySQL-client-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-devel-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-embedded-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-server-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-shared-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-shared-compat-advanced-5.6.24-1.el6.x86_64.rpm
MySQL-test-advanced-5.6.24-1.el6.x86_64.rpm

3.执行安装
通常情况下MySQL安装只需安装MySQL-server和MySQL-client这两个组件包:
[root@dbtest5 MySQL]# pwd
/root/MySQL
[root@dbtest5 MySQL]# yum install MySQL-server-advanced-5.6.24-1.el6.x86_64.rpm
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Examining MySQL-server-advanced-5.6.24-1.el6.x86_64.rpm: MySQL-server-advanced-5.6.24-1.el6.x86_64
Marking MySQL-server-advanced-5.6.24-1.el6.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package MySQL-server-advanced.x86_64 0:5.6.24-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================
 Package                                 Arch                     Version                        Repository                                                    Size
====================================================================================================================================================================
Installing:
 MySQL-server-advanced                   x86_64                   5.6.24-1.el6                   /MySQL-server-advanced-5.6.24-1.el6.x86_64                   258 M

Transaction Summary
====================================================================================================================================================================
Install       1 Package(s)

Total size: 258 M
Installed size: 258 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows:
2:postfix-2.6.6-6.el6_5.x86_64 has missing requires of libmysqlclient.so.16()(64bit)
2:postfix-2.6.6-6.el6_5.x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-6.el6_5.x86_64 has missing requires of mysql-libs
  Installing : MySQL-server-advanced-5.6.24-1.el6.x86_64                                                                                                        1/1 
2016-08-12 12:16:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-08-12 12:16:53 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-enterprise-commercial-advanced) starting as process 5400 ...
2016-08-12 12:16:53 5400 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-08-12 12:16:53 5400 [Note] InnoDB: The InnoDB memory heap is disabled
2016-08-12 12:16:53 5400 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-08-12 12:16:53 5400 [Note] InnoDB: Memory barrier is not used
2016-08-12 12:16:53 5400 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-08-12 12:16:53 5400 [Note] InnoDB: Using Linux native AIO
2016-08-12 12:16:53 5400 [Note] InnoDB: Using CPU crc32 instructions
2016-08-12 12:16:53 5400 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-08-12 12:16:53 5400 [Note] InnoDB: Completed initialization of buffer pool
2016-08-12 12:16:53 5400 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-08-12 12:16:53 5400 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-08-12 12:16:53 5400 [Note] InnoDB: Database physically writes the file full: wait...
2016-08-12 12:16:53 5400 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-08-12 12:16:54 5400 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-08-12 12:16:54 5400 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-08-12 12:16:54 5400 [Warning] InnoDB: New log files created, LSN=45781
2016-08-12 12:16:54 5400 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-08-12 12:16:54 5400 [Note] InnoDB: Doublewrite buffer created
2016-08-12 12:16:54 5400 [Note] InnoDB: 128 rollback segment(s) are active.
2016-08-12 12:16:54 5400 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-08-12 12:16:54 5400 [Note] InnoDB: Foreign key constraint system tables created
2016-08-12 12:16:54 5400 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-08-12 12:16:54 5400 [Note] InnoDB: Tablespace and datafile system tables created.
2016-08-12 12:16:54 5400 [Note] InnoDB: Waiting for purge to start
2016-08-12 12:16:54 5400 [Note] InnoDB: 5.6.24 started; log sequence number 0
2016-08-12 12:16:54 5400 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-08-12 12:16:54 5400 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2016-08-12 12:16:54 5400 [Note] Binlog end
2016-08-12 12:16:54 5400 [Note] InnoDB: FTS optimize thread exiting.
2016-08-12 12:16:54 5400 [Note] InnoDB: Starting shutdown...
2016-08-12 12:16:56 5400 [Note] InnoDB: Shutdown completed; log sequence number 1625977
A random root password has been set. You will find it in '/root/.mysql_secret'.


2016-08-12 12:16:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-08-12 12:16:56 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-enterprise-commercial-advanced) starting as process 5423 ...
2016-08-12 12:16:56 5423 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-08-12 12:16:56 5423 [Note] InnoDB: The InnoDB memory heap is disabled
2016-08-12 12:16:56 5423 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-08-12 12:16:56 5423 [Note] InnoDB: Memory barrier is not used
2016-08-12 12:16:56 5423 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-08-12 12:16:56 5423 [Note] InnoDB: Using Linux native AIO
2016-08-12 12:16:56 5423 [Note] InnoDB: Using CPU crc32 instructions
2016-08-12 12:16:56 5423 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-08-12 12:16:56 5423 [Note] InnoDB: Completed initialization of buffer pool
2016-08-12 12:16:56 5423 [Note] InnoDB: Highest supported file format is Barracuda.
2016-08-12 12:16:56 5423 [Note] InnoDB: 128 rollback segment(s) are active.
2016-08-12 12:16:56 5423 [Note] InnoDB: Waiting for purge to start
2016-08-12 12:16:56 5423 [Note] InnoDB: 5.6.24 started; log sequence number 1625977
2016-08-12 12:16:56 5423 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2016-08-12 12:16:56 5423 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2016-08-12 12:16:56 5423 [Note] Binlog end
2016-08-12 12:16:56 5423 [Note] InnoDB: FTS optimize thread exiting.
2016-08-12 12:16:56 5423 [Note] InnoDB: Starting shutdown...
2016-08-12 12:16:58 5423 [Note] InnoDB: Shutdown completed; log sequence number 1625987




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!

[root@dbtest5 MySQL]# yum install MySQL-client-advanced-5.6.24-1.el6.x86_64.rpm
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Examining MySQL-client-advanced-5.6.24-1.el6.x86_64.rpm: MySQL-client-advanced-5.6.24-1.el6.x86_64
Marking MySQL-client-advanced-5.6.24-1.el6.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package MySQL-client-advanced.x86_64 0:5.6.24-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================
 Package                                 Arch                     Version                        Repository                                                    Size
====================================================================================================================================================================
Installing:
 MySQL-client-advanced                   x86_64                   5.6.24-1.el6                   /MySQL-client-advanced-5.6.24-1.el6.x86_64                    72 M

Transaction Summary
====================================================================================================================================================================
Install       1 Package(s)

Total size: 72 M
Installed size: 72 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MySQL-client-advanced-5.6.24-1.el6.x86_64                                                                                                        1/1 
  Verifying  : MySQL-client-advanced-5.6.24-1.el6.x86_64                                                                                                        1/1 

Installed:
  MySQL-client-advanced.x86_64 0:5.6.24-1.el6                                                                                                                       

Complete!

4.检查一下MySQL安装文件及位置
[root@dbtest5 ~]# which mysql      
/usr/bin/mysql
[root@dbtest5 ~]# whereis mysql          
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
[root@dbtest5 ~]# find / -name mysql  
/usr/share/mysql
/usr/bin/mysql
/usr/lib64/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/etc/rc.d/init.d/mysql
/etc/logrotate.d/mysql
1)数据库目录 /var/lib/mysql/
2)配置文件 /usr/share/mysql(mysql.server命令及配置文件)
3)相关命令 /usr/bin (mysqladmin mysqldump mysql等命令)
4)启动脚本 /etc/rc.d/init.d/(启动脚本文件mysql的目录)

5.启动MySQL
[root@dbtest5 ~]# service mysql status
MySQL is not running                                       [FAILED]
[root@dbtest5 ~]# service mysql start
Starting MySQL..                                           [  OK  ]
[root@dbtest5 ~]# service mysql status
MySQL running (5966)                                       [  OK  ]
注意:启动mysql服务也可以使用脚本/etc/init.d/mysql或/usr/share/mysql/mysql.server配置文件来执行,并且MySQL在5.5版本之后服务名从原来的mysqld改为mysql
[root@dbtest5 ~]# /etc/init.d/mysql status
MySQL running (5966)                                       [  OK  ]
[root@dbtest5 ~]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@dbtest5 ~]# /etc/init.d/mysql start
Starting MySQL.                                            [  OK  ]
[root@dbtest5 ~]# /etc/init.d/mysql status
MySQL running (6393)                                       [  OK  ]
[root@dbtest5 ~]# /usr/share/mysql/mysql.server status
MySQL running (6393)                                       [  OK  ]
[root@dbtest5 ~]# /usr/share/mysql/mysql.server stop
Shutting down MySQL..                                      [  OK  ]
[root@dbtest5 ~]# /usr/share/mysql/mysql.server start
Starting MySQL.                                            [  OK  ]
[root@dbtest5 ~]# /usr/share/mysql/mysql.server status
MySQL running (6601)                                       [  OK  ]

6.配置MySQL自启动
[root@dbtest5 ~]# chkconfig --list|grep mysql
[root@dbtest5 ~]# chkconfig --add mysql
[root@dbtest5 ~]# chkconfig --list|grep mysql
mysql           0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@dbtest5 ~]# chkconfig --list|grep mysql
mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off

7.登陆MySQL
在MySQL 5.5之前版本,安装完成后初始化启动登陆MySQL数据库时,默认不需要使用密码即可登录:
[root@dbtest6 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
[root@dbtest6 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

而从MySQL版本从5.5开始,初始化启动登陆MySQL时必须使用密码,否则报下面的错误:
[root@dbtest5 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using  EditLine wrapper
[root@dbtest5 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这是从安全角度考虑,MySQL在5.5.版本之后安装启动初始化的时候设置了一个随机密码,首次登陆后需要修改,从这段文字提示也可以看出:
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.
因此在MySQL安装初始化启动后,首次登陆时需要使用/root/.mysql_secret里随机生成的密码,登陆成功后再进行修改:
[root@dbtest5 ~]# cat /root/.mysql_secret
# The random password set for the root user at Fri Aug 12 12:16:54 2016 (local time): 1mekXLAbvT20XsvI
[root@dbtest5 ~]# mysql -uroot -p1mekXLAbvT20XsvI
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 4
Server version: 5.6.24-enterprise-commercial-advanced

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;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
这里提示必须先修改密码:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('oracle'); 
Query OK, 0 rows affected (0.00 sec)
初始密码修改成功:
[root@dbtest5 ~]# mysql -uroot -poracle
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 6
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 |
| test               |
+--------------------+
4 rows in set (0.00 sec)

8.登陆MySQL报错解决办法
Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’
在登陆MySQL时如果报这个错误,首先检查下MySQL是否启动,其次/var/lib/mysql文件权限是否正确/var/lib/mysql
检查MySQL是否启动:service mysql status
修改MySQL文件权限;chown -R mysql:mysql /var/lib/mysql