创建 my.cnf 会导致 innodb 出错

创建 my.cnf 会导致 innodb 出错

我访问了现有的 mysqld 服务器(Debian Wheezy 的 5.5.35),其中包含生产中使用的现有数据。我在 /etc/my.cnf 或 /etc/mysql/my.cnf 中找不到现有配置文件。因此,我将文件从 复制/etc/mysql/my.cnf.dpkg-dist/etc/mysql/my.cnf并启动了服务器。出现此错误:

150506 13:31:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150506 13:31:09 [Note] Plugin 'FEDERATED' is disabled.
150506 13:31:09 InnoDB: The InnoDB memory heap is disabled
150506 13:31:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150506 13:31:09 InnoDB: Compressed tables use zlib 1.2.7
150506 13:31:09 InnoDB: Using Linux native AIO
150506 13:31:09 InnoDB: Initializing buffer pool, size = 1.0G
150506 13:31:10 InnoDB: Completed initialization of buffer pool
150506 13:31:10 InnoDB: highest supported file format is Barracuda.
150506 13:31:10  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
150506 13:31:10  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './mysql/innodb_index_stats.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
150506 13:31:10  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
150506 13:31:10  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './mysql/innodb_table_stats.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
150506 13:31:10  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
150506 13:31:10  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './mysql/slave_master_info.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
150506 13:31:10  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
150506 13:31:10  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './mysql/slave_relay_log_info.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
150506 13:31:10  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
150506 13:31:10  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './mysql/slave_worker_info.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
150506 13:31:10  InnoDB: Waiting for the background threads to start
150506 13:31:11 InnoDB: 5.5.35 started; log sequence number 2461972895878
150506 13:31:11 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'
150506 13:31:11 [ERROR] Aborting

如果我删除此配置,服务器将正常启动并且不会出现错误。

问题是我在 /var/lib/mysql 目录中找不到任何包含“innodb_index_stats”或类似内容的文件。

这里是“最小化”的 my.cnf:

[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
skip-external-locking
explicit_defaults_for_timestamp

bind-address            = 127.0.0.1

max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover-options  = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10
max_binlog_size         = 100M

log-error               = /var/log/mysql/error.log

innodb_data_file_path=ibdata1:10M:autoextend

join_buffer_size        = 12M
table_open_cache        = 800
max_heap_table_size     = 128M
tmp_table_size          = 128M
thread_cache_size       = 8

以下是“无需配置”实例的一些变量:

mysql> SHOW VARIABLES LIKE 'basedir%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| something_development  |
| something_production   |
| something_staging      |
| mysql                  |
| performance_schema     |
| test                   |
+------------------------+
7 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 134217728              |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_print_all_deadlocks      | OFF                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 5.5.35                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+
60 rows in set (0.00 sec)

相关内容