我访问了现有的 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)