Mysql-配置

作者：code0 爱可生 DMP 团队一位不出名的 coder，充斥神秘的气味... 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 一、起因非 root 用户运行 MySQL，当 MySQL 配置比拟高时，MySQL 运行中失效的参数值与配置的值不一样，所以具体分析一下 MySQL 是怎么调整这些参数值的。 所以这篇文章的目标是为了阐明在系统资源不够的状况下，MySQL 是怎么调整者三个参数的。 二、阐明此文波及到 3 个参数: open_files_limitmax_connectionstable_open_cache这 3 个参数与零碎相干的资源是最多能同时关上的文件（ ulimit -n 查看）理论即文件描述符（fd）。 零碎参数与文件描述符的关系 - max_connection & fd : 每一个MySQL connection都须要一个文件描述符 - table_open_cache & fd 关上一张表至多须要一个文件描述符，如关上MyISAM须要两个fd 三、MySQL 调整参数的形式依据配置（配置的 3 个参数值或默认值）计算 request_open_files（须要的文件描述符）获取无效的零碎的限度值 effective_open_files依据 effective_open_files 调整 request_open_files依据调整后的 request_open_files，计算理论失效的参数值（ show variables 查看到的 3 个参数值）1、计算 request_open_filesrequest_open_files 有三个计算公式： # 最大连接数+同时关上的表的最大数量+其余（各种日志等等）limit_1= max_connections + table_cache_size * 2 + 10;# 假如均匀每个连贯关上的表的数量（2-4）# 源码中是这么写的：# We are trying to allocate no less than # max_connections*5 file handles limit_2= max_connections * 5;# MySQL 默认的最低值是 5000limit_3= open_files_limit ? open_files_limit : 5000;# 所以 open_files_limit 期待的最低 request_open_files= max(limit_1, limit_2,limit_3);2、计算 effective_open_filesMySQL 的思路：在无限值的的范畴内 MySQL 尽量将 effective_open_files 的值设大 ...

一 装置前筹备 查看是否已装置[root@localhost /]# rpm -qa | grep mysql如果已装置，执行删除命令 [root@localhost /]# rpm -e --nodeps mysql-libs-5.1.73-5.el6_6.x86_64查问所有mysql文件夹并删除[root@localhost /]# whereis mysqlmysql: /usr/bin/mysql /usr/include/mysql[root@localhost lib]# find / -name mysql/data/mysql/data/mysql/mysql[root@localhost /]# rm -rf /usr/bin/mysql /usr/include/mysql /data/mysql /data/mysql/mysql [root@localhost /]# whereis mysqlmysql:[root@localhost /]# find / -name mysql[root@localhost /]# mysql用户组和用户是否存在，如果没有，则创立[root@localhost /]# cat /etc/group | grep mysql[root@localhost /]# cat /etc/passwd |grep mysql[root@localhost /]# groupadd mysql[root@localhost /]# useradd -r -g mysql mysql[root@localhost /]# 官网下载mysql安装包[root@localhost /]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz二 装置mysql ...

MySQL 突然出现写不了的情况, 登录查看磁盘空间, 发现空间不足. 查看 MySQL 数据所在目录 发现占用空间最大的是一个临时文件ibtmp1. 上网搜索得到的解决办法如下:

在使用Percona XtraBackup的部分备份功能时，发现官方文档的第一句话就是：xtrabackup supports taking partial backups when the innodb_file_per_table option is enabled.于是查询了一下MySQL的 innodb_file_per_tableinnodb_file_per_table 顾名思义，首先是innoDb引擎的配置，其次表示每表一个文件。其实也就是独立表空间，与之对应的就是共享表空间。打开MySQL的datadir，进入一个innoDb数据库的目录，会看到每张表都对应有两个文件：table_name.frm 和 table_name.ibd，这就是开启了独立表空间的效果。查看是否开启mysql> show variables like ‘%per_table%’;开启方法：在my.cnf文件[mysqld]下设置innodb_file_per_table=1MySQL自5.6.6版本，就默认开启了这个参数，所以就没意识到它的存在。那么MySQL为什么要默认开启独立表空间呢，肯定有其原因。独立表空间的优点：每个表都有自己独立的表空间。每个表的数据和索引都在自己的表空间中。可以实现单表在不同的数据库中移动。空间可以回收性能优势(除insert共享表空间略有优势外，其他方面独立表空间都表现更好)

慢日志查询作用慢日志查询的主要功能就是，记录sql语句中超过设定的时间阈值的查询语句。例如，一条查询sql语句，我们设置的阈值为1s，当这条查询语句的执行时间超过了1s，则将被写入到慢查询配置的日志中.慢查询主要是为了我们做sql语句的优化功能.慢查询配置项说明登录mysql服务，使用如下命令mysql> show variables like ‘%query%’;+——————————+—————————————–+| Variable_name | Value |+——————————+—————————————–+| binlog_rows_query_log_events | OFF || ft_query_expansion_limit | 20 || have_query_cache | YES || long_query_time | 10.000000 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 33554432 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || slow_query_log | OFF || slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |+——————————+—————————————–+13 rows in set (0.01 sec)这里，我们只需要关注三个配置项即可。1.slow_query_log该配置项是决定是否开启慢日志查询功能，配置的值有ON或者OFF.2.slow_query_log_file该配置项是慢日志查询的记录文件,需要手动创建.3.long_query_time该配置项是设置慢日志查询的时间阈值，当超过这个阈值时，慢日志才会被记录.配置的值有0(任何的sql语句都记录下来)，或者>0(具体的阈值).该配置项是以秒为单位的，并且可以设置为小数.4.log-queries-not-using-indexes该配置项是为了记录未使用到索引的sql语句.如何配置慢查询配置慢查询功能的方式有两种，一种是使用mysql的配置文件配置,另外一种是使用mysql命令配置.这里建议使用配置文件配置，因为在命令配置的过程中发现有时候配置项在set命令的时候是成功了，但是查询还是没设置。1.配置文件配置// 找到[mysqld],在其下面添加如下代码即可.slow_query_log=ONslow_query_log_file=/usr/local/mysql/var/localhost-slow.loglong_query_time=0log-queries-not-using-indexes = 1// 配置好后，重启mysql服务2.使用命令配置// 这里就简单些一个配置项就行了，其他的配置项均按照此方法配置mysql> set slow_query_log=ON;配置好之后，查看mysql慢查询日志是否配置成功.mysql> show variables like ‘%query%’;+——————————+—————————————–+| Variable_name | Value |+——————————+—————————————–+| binlog_rows_query_log_events | OFF || ft_query_expansion_limit | 20 || have_query_cache | YES || long_query_time | 0.000000 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 33554432 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || slow_query_log | ON || slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |+——————————+—————————————–+13 rows in set (0.01 sec)如何查看慢日志记录在配置慢查询之前，我们已经导入了示例的数据文件,这里就不做单独的演示了。mysql官方数据库示例 。接下来，我们就开始做查询操作.mysql> select * from city where city=‘Salala’;+———+——–+————+———————+| city_id | city | country_id | last_update |+———+——–+————+———————+| 444 | Salala | 71 | 2006-02-15 04:45:25 |+———+——–+————+———————+1 row in set (0.01 sec)此时，我们根据配置的慢查询日志记录文件/usr/local/mysql/var/localhost-slow.log,发现该文件记录了上面的命令操作.# Time: 2019-01-17T08:12:27.184998Z# User@Host: root[root] @ localhost [] Id: 4# Query_time: 0.002773 Lock_time: 0.001208 Rows_sent: 1 Rows_examined: 600SET timestamp=1547712747;select * from city where city=‘Salala’;上诉文件配置内容说明1.Time该日志记录的时间2.User@HostMySQL登录的用户和登录的主机地址3.Query_time一行第一个时间是查询的时间、第二个是锁表的时间、第三个是返回的行数、第四个是扫描的行数4.SET timestamp这一个是MySQL查询的时间5.sql语句这一行就很明显了，表示的是我们执行的sql语句总结由于我们配置long_query_time=0,因此所有的sql语句都将被记录下来，这里我们假设，仅仅是假设。我们设置的long_query_time=5，然而上面的第三项中Query_time大于5，如果是实际项目中不属于正常范围，则需要对其进行优化，当然优化的方式有很多种，下面我们使用简单的索引方式进行优化一下。优化sql语句1.先查看原本的sql语句执行结构mysql> explain select * from city where city=‘Salala’\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: city partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 600 filtered: 10.00 Extra: Using where1 row in set, 1 warning (0.00 sec)mysql> create index idx_cityName on city(city);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0看得出，该sql语句是进行了全盘扫描。我们则用索引对其简单的优化一下。2.创建索引mysql> create index idx_cityName on city(city);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 03.在用explain分析一次mysql> explain select * from city where city=‘Salala’\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: city partitions: NULL type: refpossible_keys: idx_cityName key: idx_cityName key_len: 152 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)通过创建索引，我们发现此时只扫描了一行，并且是使用的索引扫描，因此大大提高了一个mysql查询的效率。MySQL慢查询使用总结在平常的开发中，慢查询作为MySQL优化的一个途径，是非常有用的。它会记录下我们一些查询时间长的sql语句，对其语句我们进行分析，从而达到sql查询语句的最优化。但是慢日志查询开启之后，针对sql查询会通过磁盘I/O将相关的记录写入到磁盘文件中，增加了一个磁盘的I/O读写。因此，我们该功能用在开发、测试环境上，而不用在生产环境中去。慢日志查询工具由于慢日志查询文件越到后期，内容越多。我们对其分析的压力越大，因此我们需要借助某些工具实现快速分析。这些工具还没完全使用熟悉，后期单独写一篇文章介绍该类型的工具，这里只是罗列一下工具名称。1.mysqldumpslow2.pt-query-digest3.mysqltop（天兔Lepus）原文转自浪子编程走四方 ...