Mysql-5.7
Mysql Innodb 錯誤號 24 表示“打開的文件太多”？
我正在使用 mysqldump 恢復我的數據庫，但它出錯了
ERROR 1030 (HY000) at line 637885: Got error 168 from storage engine當我檢查日誌時，我看到了這個：-
2021-06-20T02:43:28.598142Z 113 [ERROR] InnoDB: Operating system error number 24 in a file operation. 2021-06-20T02:43:28.604918Z 113 [ERROR] InnoDB: Error number 24 means 'Too many open files' 2021-06-20T02:43:28.604935Z 113 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html 2021-06-20T02:43:28.604950Z 113 [ERROR] InnoDB: File ./minerva@002dv085/FTS_0000000000029783_0000000000042996_INDEX_1.ibd: 'create' returned OS error 124. 2021-06-20T02:43:28.604956Z 113 [ERROR] InnoDB: Operating system error number 24 in a file operation. 2021-06-20T02:43:28.604960Z 113 [ERROR] InnoDB: Error number 24 means 'Too many open files' 2021-06-20T02:43:28.604964Z 113 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html這是我的配置文件目前的樣子：-
[mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql bind-address = 0.0.0.0 # # * Fine Tuning # key_buffer_size = 32M max_allowed_packet = 64M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP max_connections = 2000 innodb_open_files = 100000 sql_mode= 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' table_open_cache = 4000 #thread_concurrency = 10 explicit_defaults_for_timestamp = ON optimizer_trace_max_mem_size = 1M back_log = 5000 max_error_count = 1024 event_scheduler = ON # # * Performance Schema # performance-schema-instrument='wait/lock/metadata/sql/mdl=ON' performance-schema-instrument='memory/%=COUNTED' performance-schema-consumer-events-transactions-current = ON performance-schema-consumer-events-transactions-history = ON performance-schema-instrument='transaction%=ON' query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log - should be very few entries. # log_error = /var/log/mysql/error.log # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 100M master-info-repository = TABLE relay-log-info-repository = TABLE # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # innodb_buffer_pool_size = 8G innodb-log-file-size = 512M innodb_flush_neighbors = 1 innodb_max_dirty_pages_pct_lwm = 10 innodb_max_dirty_pages_pct = 90 slave_skip_errors=1062,1032,1050,1236,1813,1146,1305,1396,1217有人可以指出我應該如何解決上述錯誤嗎？謝謝你。
從日誌的外觀來看，您似乎正在使用某種形式的 Linux。如果是 Ubuntu（或其中一種衍生版本），您可能需要確認作業系統已設置為處理您在
innodb_open_files. 很少將系統設置為開箱即用處理 100,000 個，因為這需要大量 RAM。您可以使用以下命令（通過終端或 SSH 連接）檢查伺服器的功能：
ulimit -n這個數字可能在 5,000~10,000 左右。如果你需要增加這個值，你可以設置一個新的限制：
ulimit -n 50,000請注意，將此數字設置得太高可能會產生一些不幸的後果，並導致系統記憶體在幾秒鐘內耗盡。增量增加2500左右。如果伺服器的 RAM 測量為 100 GB，您可能能夠測試更大的增加。