Mysql安装、配置、优化

Mysql安装、配置、优化

mysql安装**

1. 编译安装cmake
   tar zxvf /usr/local/src/cmake-3.4.0.tar.gz
   cd /usr/local/src/cmake-3.4.0
   ./configure && make && make install
2. 编译安装boost（编译时不加boost，可以不用安装此软件）
   tar zxvf /usr/local/src/boost_1_59_0.tar.gz -C /usr/local/boost/
   cd /usr/local/boost/
   ./bootstrap.sh
   ./b2
3. 新增用户用户组
   groupadd mysqluser
   add mysql -g mysql -M -s /sbin/nologin
4. 编译安装MySQL
   tar zxvf /usr/local/src/mysql-5.7.9.tar.gz
   cd /usr/local/src/mysql-5.7.9
   /cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql - DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 - DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
   make
   make install
   mysql配置
   1.连接数(connection)配置max_connections 可以设置最大并发连接数。当MySql的并发连接达到这个设定值时，新的连接将会被拒绝（“Can not connect to MySQL server. Too many connections”-mysql 1040错误，）。当发现MySql有能力处理更多的并发的时候， 建议调大这个值，相应给服务器带来更高的负载(CPU/IO/内存)。查看设置的最大连接是多少：
   mysql> show variables like ‘max_connections’;
   ±----------------±------+
   | Variable_name | Value |
   ±----------------±------+
   | max_connections | 151 | 允许的最大连接数
   ±----------------±------+
   看当前连接数：show status like ‘threads_connected’;
   最大连接数: show status like “max_used_connections”;　　
   如果max_used_connections已经接近 max_connections了，就说明max_connections太小。不合适了。
   还有一些跟连接数相关的配置：
   back_log=50
   MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。如果链接数超过max_connections+back_log ，才会出错。max_connect_errors=10
   当客户端连接服务端超时(超过connect_timeout), 服务端就会给这个客户端记录一次error，当出错的次数达到max_connect_errors的时候，这个客户端就会被锁定。除非执行FLUSH HOSTS命令。connect_timeout=5
   连接超时的秒数
   2.查询缓存(query_cache)配置
   查询缓存就是内存中的一块存储区域，其存储了用户的SQL文本以及相关的查询结果。通常情况下，用户下次查询时，如果所使用的SQL文本是相同的，并且自从上次查询后，相关的纪录没有被更新过，此时数据库就直接采用缓存中的内容。从内存中读取要比从硬盘上速度要快好几百倍。MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接 返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化.要使用缓存，有几个条件。
   一是所采用的SQL语句是相同的。每次查询的语句不一样，肯定不能用到缓存。比如语句里带当前秒数 where ctime > xxx
   二是表数据没有改过。没有改过结构，没有update，insert
   三：客户端与服务器的默认字符集得一样所以可以看出，要利用好缓存，有大量的相同的查询，而很少改变表里的数据，否则没有必要使用此功能.
   查看查询缓存的设置：12345678910111213
   SHOW VARIABLES LIKE ‘%query_cache%’;
   ±-----------------------------±---------+
   | Variable_name | Value |
   ±-----------------------------±---------+
   | have_query_cache | YES || query_cache_limit | 1048576 | 如果单个查询结果大于这个值，则不Cache
   | query_cache_min_res_unit | 4096 | 每次给QC结果分配内存的大小| query_cache_size | 33554432 || query_cache_type | ON || query_cache_wlock_invalidate | OFF |
   ±-----------------------------±---------+
   query_cache_type=1　　
   如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
   如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。query_cache_size 默认是32M，太小了，可调到128M或者256M。 可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。query_cache_size的工作原理：一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。
   那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？
   首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建系把该功能禁掉。Qcache_lowmem_prunes可以检查是否设置的太小。
   query_cache_limit 默认是1M，根据你的常用查询的数据结果大小来定。如果返回的数据小，可以设置小一点。
   设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费查看缓存使用效果如何：1234567891011121314151617181920
   show status like ‘%Qcache%’;
   ±------------------------±---------+
   | Variable_name | Value |
   ±------------------------±---------+|
   Qcache_free_blocks | 160 | 目前还处于空闲状态的 Query Cache中内存 Block 数目，数目大说明可能有碎片。
   FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
   | Qcache_free_memory | 23147296 | 缓存中的空闲内存总量。
   | Qcache_hits | 52349 | 缓存命中次数。
   | Qcache_inserts | 8827 | 缓存失效次数。
   | Qcache_lowmem_prunes | 0 | 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。
   这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。
   | Qcache_not_cached | 2446 | 没有被cache和不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句以及由于query_cache_type设置的不会被Cache的查询。
   show,use,desc
   | Qcache_queries_in_cache | 5234 | 当前被cache的SQL数量。
   | Qcache_total_blocks | 10796 | 缓存中块的数量。
   ±------------------------±---------+
   show global status like ‘Com_select’;
   ±--------------±------+
   | Variable_name | Value |
   ±--------------±------+
   | Com_select | 12592 | com_select 变量记录的是无缓存的查询次数+错误查询+权限检查查询。
   ±--------------±------+
   Mysql的查询缓存命中率没有官方算法，只有前人的经验总结
   命中率 ≈ qcache_hits / (qcache_hits + com_select)缓存碎片率 = 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可能有点小，要不就是碎片太多。如何提高命中率：
   1：字符集相同，
   2：SQL语句尽量固定(SQL语句避免随机数，秒数等)
   3 加大缓存空间
   4：适当分表,动静分离，

3.临时表缓存(tmp_table_size)配置mysql进行复杂查询或者 做高级GROUP BY操作的时候，系统为了优化查询，生成一些临时表。通过设置tmp_table_size选项来设置临时表占用空间的大小。我们使用explain分析SQL，如果在Extra列看到Using temporary就意味着使用了临时表。MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎；一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表
临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。12345678910111213141516
mysql> SHOW VARIABLES LIKE ‘%tmp_table_size%’;
±---------------±---------+
| Variable_name | Value |
±---------------±---------+
| tmp_table_size | 33554432 |
±---------------±---------+
1 row in set (0.00 sec)
mysql> show global status like ‘created_tmp%’；
±------------------------±---------+
| Variable_name | Value |
±------------------------±---------+
| Created_tmp_disk_tables | 690421 | 服务器执行语句时在硬盘上自动创建的临时表的数量
| Created_tmp_files | 755473 | mysqld已经创建的临时文件的数量
| Created_tmp_tables | 14372959 | 服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大，你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘
±------------------------±---------+
每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，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%，应该相当好了默认大小是 32M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞跟临时表相关的另一配置是max_heap_table_size ，
用户可以创建的独立的内存表所允许的最大容量.这个变量不适用与用户创建的内存表(memory table).
SHOW VARIABLES LIKE ‘%max_heap_table_size%’; 咱们希望临时表是放到内存的。所以这个值设置的临时表缓存的空间一样就行。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果临时表大于这两个的任何一个，都会存硬盘缓存：自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下 4.索引缓冲区（key_buffer_size）配置key_buffer_size是对MyISAM表性能影响最大的一个参数.key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。 12345678910111213141516171819202122
mysql> show variables like ‘key_buffer_size’;
±----------------±---------+|
Variable_name | Value |
±----------------±---------+|
key_buffer_size | 67108864 | 索引缓冲区的大小
±----------------±---------+
show variables like ‘key_cache_block_size’;
mysql> show global status like ‘key%’;
±-----------------------±-----------+|
Variable_name | Value|
±-----------------------±-----------+|
Key_blocks_not_flushed | 0 |索引缓存内已经更改，但还没有清空到硬盘上的索引的数据块数量。
| Key_blocks_unused | 0 | 索引缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存
| Key_blocks_used | 53585 | 索引缓存内使用的块数量。该值为高水平线标记，说明已经同时最多使用了多少块。
| Key_read_requests | 4952122733 | 一共有XXX个索引读取请求，
| Key_reads | 11879 | 索引读取请求在内存中没有找到，直接从硬盘读取索引| Key_write_requests | 10508455 | 将索引的数据块写入缓存的请求数。
| Key_writes | 6042774 | 将索引向硬盘写入数据块的物理写操作的次数。
±-----------------------±-----------+　　
比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好
如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率
Key_writes/Key_write_requests：比例接近1较好别人的经验是内存在4GB左右的服务器该参数可设置为384M或512M。可以自己算一下自己数据库的索引文件大小。注意：该参数值设置的过大反而会是服务器整体效率降低!Cache命中比率：11 - Key_reads / Key_read_requests　　Key buffer的使用率1100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size ) mysql优化
1、删除test库
C:\Users\Administrator>mysql -uroot -p123456
mysql> show databases;
mysql> drop database test;
2、删除不使用的用户 在 Linux 系统上 yum 安装 mysql 时，直接使用 mysql 命令即可连接到 mysql，这是因为 mysql 的内置用户（用户名为空而且也没有密码）导致的。甚至 root 用户也没有密码，即使root用户不能进行远程连接，这也是不安全的。
1 mysql> select user, host, password from mysql.user;
2 ±-----±----------------------±---------+
3 | user | host | password |
4 ±-----±----------------------±---------+
5 | root | localhost | |
6 | root | localhost.localdomain | |
7 | root | 127.0.0.1 | |
8 | | localhost | |
9 | | localhost.localdomain | |
10 ±-----±----------------------±---------+
11 5 rows in set (0.00 sec)
首先，把用户名为空的用户删除 mysql> delete from mysql.user where user = ‘’; 然后，把root@localhost.localdomain用户删除 mysql> delete from mysql.user where host = ‘localhost.localdomain’;
（参考自他人博客，原文链接https://www.cnblogs.com/hunhunrensheng/p/3782596.html）