mysql文件不想用mysql打开

Linux会对用户所占用的系统资源进行限制，MySQL运行在Linux系统下也会受此限制。

查看当前系统的所有限制值

shell>ulimit -a

设置可以同时打开的最大文件数，默认为1024，如不修改打开文件数过多会出现too many open files错误。

shell>ulimit -n

设置最大可用进程数

shell>ulimit -u

一般可通过修改/etc/security/limits.conf文件进行配置。

shell>more /etc/security/limits.conf

* soft nproc 20480

* hard nproc 20480

* soft nofile 65535

* hard nofile 65535

MySQL打开文件限制

mysql>show variables like '%open\_%'

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| innodb_open_files | 4096 |

| open_files_limit | 65535 |

| table_open_cache | 2048 |

| table_open_cache_instances | 1 |

+----------------------------+-------+

4 rows in set (0.00 sec)

open_files_limit : *** 作系统允许mysqld打开的文件数量，服务器运行时该变量为系统实际允许打开的值，和启动服务器指定的参数可能不一致。如果该值为0，表示不允许MySQL修改打开的文件数量。

有效的open_files_limit值是基于系统启动指定的open_files_limit，max_connections和table_open_cache计算得到，服务器将会获取三个指标中最大的值，如果三者指标都没有指定，服务器将获得os允许的最大值。

1) 10 + max_connections + (table_open_cache * 2)

2) max_connections * 5

3) 启动时设定的open_files_limit，如果没有指定默认为5000

innodb_open_files：指定MySQL可同时打开.ibd文件的最大个数，最小为10，默认300。此选项只针对InnoDB表打开的.ibd文件描述符，独立于open_files_limit。

table_open_cache：所有线程打开表的数目。它的作用就是缓存表文件描述符，降低打开关闭表的频率， 如果这个参数设置得过小，就不得不关闭一些已打开的表以便为缓存新表，从而出现频繁的打开关闭MyISAM表文件的情况，而INNODB表的打开不受这个参数控制，而是放到其数据字典当中，即在ibd文件中。当Opened_tables状态值较大，且不经常使用FLUSH TABLES 关闭并重新打开表，就需要增加该值。

table_open_cache_instances：表缓存实例数，为通过减小会话间争用提高扩展性，表缓存会分区为table_open_cache/table_open_cache_instances大小的较小的缓存实例。DML语句会话只需要锁定所在缓存实例，这样多个会话访问表缓存时就可提升性能(DDL语句仍会锁定整个缓存)。默认该值为1，当16核以上可设置为8或16。

table_definition_cache:缓存表定义(.frm)文件的数量。如果表较多，可以增大该值加快打开表。与一般表缓存不同，表定义缓存不占用文件描述符，占用空间也小。最小为400，上线为2000，默认为：

400 + (table_open_cache / 2)。如果打开表数量高于table_definition_cache，则会通过LRU机制搜索表空间LRU文件列表并刷新列表。对于InnoDB，打开文件的限制为max(table_definition_cache, innodb_open_files)。

MySQL文件打开状态

mysql>show global status like '%open%'

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Com_ha_open | 0 |

| Com_show_open_tables | 1 |

| Innodb_num_open_files | 19 |

| Open_files | 3 |

| Open_streams | 0 |

| Open_table_definitions | 8 |

| Open_tables | 8 |

| Opened_files | 509 |

| Opened_table_definitions | 116 |

| Opened_tables | 90 |

| Slave_open_temp_tables | 0 |

| Table_open_cache_hits | 3254 |

| Table_open_cache_misses | 90 |

| Table_open_cache_overflows | 0 |

+----------------------------+-------+

14 rows in set (0.00 sec)

Open_table_definitions：代表当前缓存了多少.frm文件。

Opened_table_definitions：代表自MySQL启动后，缓存了.frm文件的数量。 需要注意的是.frm文件是MySQL用于存放表结构的文件，

对应myisam和innodb存储引擎都必须有的，可以通过show open tables 查看 这2个变量的值。

Open_tables：代表当前打开的表个数

Opened_tables：代表自MySQL启动后，打开过的表个数，如该值过大，可能是table_open_cache设置太小。

Open_files：打开文件的个数。服务器层打开的一般文件，不包含sockets 或 pipes类型文件，也不包含内部函数打开的文件。

Opened_files：通过使用my_open()系统函数打开的文件数。

Table_open_cache_hits：打开表缓存查找的命中数。

Table_open_cache_misses：打开表缓存查找的未命中数。

Table_open_cache_overflows：打开表缓存溢出数。

MySQL如何打开关闭表

由于MySQL是多线程的，因此可能存在多个会话同时根据指定表进行查询。为解决同一个表在不同会话状态不一致，该表会由每个会话独立的打开，这样MySQL会消耗内存但会提高性能。

table_open_cache同时会跟max_connections相关。如200个并发连接线程，指定的表缓存至少为200*N，N为每个连接关联的最大表数量。

以下几种情况MySQL会关闭未使用的表并将其从表缓存中删除：

表缓存已满，线程将要打开不在缓存中的表。

缓存中的表多于table_open_cache且缓存中的表不被任何线程使用。

当发生表刷新 *** 作(flush tables)

当表缓存满后，服务器将执行以下 *** 作：

当前不使用的表将被释放，先释放最近最少使用的表

当新表需要被打开，但是缓存已满且无表可以被释放，服务器将会根据需要临时扩展缓存，当临时扩展缓存中的表从使用变为未使用状态，表将被关闭，扩充的临时缓存将被释放。

文件打开常见问题

数据库报错：

[ERROR] /opt/mysql/bin/mysqld: Can't open file: './tpcc/sbtest98.frm' (errno: 24 - Too many open files)

查看os最大允许打开数

shell>ulimit -n

65535

查看数据库打开设定最大打开文件数

mysql>show global variables like 'open_files_limit'

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| open_files_limit | 500 |

+------------------+-------+

1 row in set (0.00 sec)

查看当前数据库已经打开的文件描述符

shell>ll /proc/24012/fd | wc

501 5507 41673

调整open_files_limit设定并重启生效。

yum -y install libaio

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

cd /usr/local/soft/mysql/src

mkdir ../5.7.24_3306 &&tar -xzvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C ../5.7.24_3306 --strip-components 1

mkdir -p /usr/local/soft/mysql/5.7.24_3306/{run,data,logs,bin_log,sock,cert}

groupadd mysql &&useradd -r -g mysql -s /bin/false mysql

cd /usr/local/soft/mysql/5.7.24_3306/bin &&./mysqld --initialize

--explicit_defaults_for_timestamp

--user=mysql

--basedir=/usr/local/soft/mysql/5.7.24_3306

--datadir=/usr/local/soft/mysql/5.7.24_3306/data

--lc_messages_dir=/usr/local/soft/mysql/5.7.24_3306/share

--lc_messages=en_US

2018-12-01T09:56:45.934627Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-12-01T09:56:46.056837Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-12-01T09:56:46.127141Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 69d028f9-f54f-11e8-9c4a-00163e08e793.

2018-12-01T09:56:46.130249Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-12-01T09:56:46.131377Z 1 [Note] A temporary password is generated for root@localhost: sDdPx3>.,oBi

cd /usr/local/soft/mysql/5.7.24_3306/bin &&./mysql_ssl_rsa_setup --datadir=/usr/local/soft/mysql/5.7.24_3306/cert

修改证书权限为644

cd /usr/local/soft/mysql/5.7.24_3306/bin &&./mysql -uroot -S /usr/local/soft/mysql/5.7.24_3306/sock/mysql.sock

use mysql

update user set host = '%', authentication_string = password('123456') where user = 'root'

select host,user,authentication_string from user

flush privileges

grant all privileges on . to 'root'@'%'

flush privileges

cat >/etc/systemd/system/3306.service <<EOF

[Unit]

Description=MySQL 3306

Documentation= http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target remote-fs.target nss-lookup.target

[Service]

Type=forking

PIDFile=/usr/local/soft/mysql/5.7.24_3306/run/mysql.pid

ExecStart=/usr/local/soft/mysql/5.7.24_3306/support-files/mysql.server start

ExecReload=/usr/local/soft/mysql/5.7.24_3306/support-files/mysql.server restart

ExecStop=/usr/local/soft/mysql/5.7.24_3306/support-files/mysql.server stop

PrivateTmp=true

[Install]

WantedBy=multi-user.target

EOF

systemctl enable 3306.service

1、

cd /usr/local/soft/mysql/5.7.24.3306/bin &&./mysqld_safe --skip-grant-tables &

cd /usr/local/soft/mysql/5.7.24.3306/bin &&./mysqld_safe --skip-grant-tables --skip-networking &

2、

./mysql -uroot -p -S /usr/local/soft/mysql/5.7.24.3306/sock/mysql.sock

3、

use mysql

update mysql.user set authentication_string=password('123456') where user='root'

flush privileges

mkdir -p /usr/local/soft/mysql/back/5.7.23_3306 &&cp -Rf /usr/local/soft/mysql/5.7.23_3306/* /usr/local/soft/mysql/back/5.7.23_3306

mkdir -p /usr/local/soft/mysql/back/5.7.24_3306 &&cp -Rf /usr/local/soft/mysql/5.7.24_3306/* /usr/local/soft/mysql/back/5.7.24_3306

CREATE USER 'sdyc'@'%' IDENTIFIED BY '123456'

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON sd_data.* TO 'sdyc'@'%'

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON card_data.* TO 'sdyc'@'%'

SHOW GRANTS FOR sdyc

CREATE USER 'back'@'localhost' IDENTIFIED BY '123456'

GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT ON . TO 'back'@'localhost'

SHOW GRANTS FOR back

REVOKE SELECT, INSERT, UPDATE, DELETE, EXECUTE ON sd_data.* FROM 'sdyc'@'%'