mysql的1067错误

你的名字 2022-05-14 09:04 185阅读 0赞

由于要升级MySQL到V5.6，所以拷贝my.ini和数据文件到新的系统上。

在启动服务时，又出现1067错误！

查看，主机名.err文件（xiaobin-PC.err）

1.  `2013-12-02 20:23:22 3684 [Note] Plugin 'FEDERATED' is disabled.`
2.  `2013-12-02 20:23:22 171c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.`
3.  `2013-12-02 20:23:22 3684 [Note] InnoDB: The InnoDB memory heap is disabled`
4.  `2013-12-02 20:23:22 3684 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions`
5.  `2013-12-02 20:23:22 3684 [Note] InnoDB: Compressed tables use zlib 1.2.3`
6.  `2013-12-02 20:23:22 3684 [Note] InnoDB: Not using CPU crc32 instructions`
7.  `2013-12-02 20:23:22 3684 [Note] InnoDB: Initializing buffer pool, size = 47.0M`
8.  `2013-12-02 20:23:22 3684 [Note] InnoDB: Completed initialization of buffer pool`
9.  `2013-12-02 20:23:22 3684 [Note] InnoDB: Highest supported file format is Barracuda.`
10. `2013-12-02 20:23:23 3684 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*1536 pages, LSN=1625977`
11. `2013-12-02 20:23:23 3684 [Warning] InnoDB: Starting to delete and rewrite log files.`
12. `2013-12-02 20:23:23 3684 [Note] InnoDB: Setting log file .\ib_logfile101 size to 24 MB`
13. `2013-12-02 20:23:23 3684 [Note] InnoDB: Setting log file .\ib_logfile1 size to 24 MB`
14. `2013-12-02 20:23:24 3684 [Note] InnoDB: Renaming log file .\ib_logfile101 to .\ib_logfile0`
15. `2013-12-02 20:23:24 3684 [Warning] InnoDB: New log files created, LSN=1625977`
16. `2013-12-02 20:23:24 3684 [Note] InnoDB: 128 rollback segment(s) are active.`
17. `2013-12-02 20:23:24 3684 [Note] InnoDB: Waiting for purge to start`
18. `2013-12-02 20:23:24 3684 [Note] InnoDB: 5.6.14 started; log sequence number 1625977`
19. `2013-12-02 20:23:24 3684 [ERROR] mysql56: unknown variable 'table_cache=256'`
20. `2013-12-02 20:23:24 3684 [ERROR] Aborting`

从err文件中可以看到错误主要是“未知变量‘table\_cache=256’”。

在[系统变量][Link 1]中我们找到三个以“table”开头的全局变量：“table\_definition\_cache”、“table\_open\_cache”和“table\_open\_cache\_instances”

第一个变量是系统缺省设置；而第三个是系统缺省实例数；

那么，只有第二个是我们要使用的。

对于，这种猜测使用，看看之前是否有文档做出过详细的说明。

在V5.1文档里找到了答案：table\_open\_cache替换table\_cache做为新的变量名称。

*   [`table_cache`][table_cache]
    
    <table> 
     <tbody> 
      <tr> 
       <td>Deprecated</td> 
       <td colspan="3">5.1.3, by&nbsp;<a href="http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_open_cache" rel="nofollow">table_open_cache</a></td> 
      </tr> 
      <tr> 
       <td>Removed</td> 
       <td colspan="3">5.1.3</td> 
      </tr> 
      <tr> 
       <td>Command-Line Format</td> 
       <td colspan="3"><code>--table_cache=#</code></td> 
      </tr> 
      <tr> 
       <td>Option-File Format</td> 
       <td colspan="3"><code>table_cache</code></td> 
      </tr> 
      <tr> 
       <td>System Variable Name</td> 
       <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_cache" rel="nofollow">table_cache</a></code></td> 
      </tr> 
      <tr> 
       <td>Variable Scope</td> 
       <td colspan="3">Global</td> 
      </tr> 
      <tr> 
       <td>Dynamic Variable</td> 
       <td colspan="3">Yes</td> 
      </tr> 
      <tr> 
       <td>&nbsp;</td> 
       <td colspan="3">Permitted Values</td> 
      </tr> 
      <tr> 
       <td>Type</td> 
       <td colspan="2"><code>numeric</code></td> 
      </tr> 
      <tr> 
       <td>Default</td> 
       <td colspan="2"><code>64</code></td> 
      </tr> 
      <tr> 
       <td>Range</td> 
       <td colspan="2"><code>1 .. 524288</code></td> 
      </tr> 
     </tbody> 
    </table>
    
    This is the old name of [`table_open_cache`][table_open_cache] before MySQL 5.1.3. From 5.1.3 on, use [`table_open_cache`][table_open_cache] instead.

重新启动mysql：net start mysql56

正常启动mysql了！

附：my.ini（V5.6）

1.  `# MySQL Server Instance Configuration File`
2.  `# ----------------------------------------------------------------------`
3.  `# Generated by the MySQL Server Instance Configuration Wizard`
4.  `#`
5.  `#`
6.  `# Installation Instructions`
7.  `# ----------------------------------------------------------------------`
8.  `#`
9.  `# On Linux you can copy this file to /etc/my.cnf to set global options,`
10. `# mysql-data-dir/my.cnf to set server-specific options`
11. `# (@localstatedir@ for this installation) or to`
12. `# ~/.my.cnf to set user-specific options.`
13. `#`
14. `# On Windows you should keep this file in the installation directory`
15. `# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To`
16. `# make sure the server reads the config file use the startup option`
17. `# "--defaults-file".`
18. `#`
19. `# To run run the server from the command line, execute this in a`
20. `# command line shell, e.g.`
21. `# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"`
22. `#`
23. `# To install the server as a Windows service manually, execute this in a`
24. `# command line shell, e.g.`
25. `# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"`
26. `#`
27. `# And then execute this in a command line shell to start the server, e.g.`
28. `# net start MySQLXY`
29. `#`
30. `#`
31. `# Guildlines for editing this file`
32. `# ----------------------------------------------------------------------`
33. `#`
34. `# In this file, you can use all long options that the program supports.`
35. `# If you want to know the options a program supports, start the program`
36. `# with the "--help" option.`
37. `#`
38. `# More detailed information about the individual options can also be`
39. `# found in the manual.`
40. `#`
41. `#`
42. `# CLIENT SECTION`
43. `# ----------------------------------------------------------------------`
44. `#`
45. `# The following options will be read by MySQL client applications.`
46. `# Note that only client applications shipped by MySQL are guaranteed`
47. `# to read this section. If you want your own MySQL client program to`
48. `# honor these values, you need to specify it as an option during the`
49. `# MySQL client library initialization.`
50. `#`
51. `[client]`
52.  
53. `port=3306`
54.  
55. `[mysql]`
56.  
57. `default-character-set=utf8`
58.  
59.  
60. `# SERVER SECTION`
61. `# ----------------------------------------------------------------------`
62. `#`
63. `# The following options will be read by the MySQL Server. Make sure that`
64. `# you have installed the server correctly (see above) so it reads this`
65. `# file.`
66. `#`
67. `[mysqld]`
68.  
69. `# The TCP/IP Port the MySQL Server will listen on`
70. `port=3306`
71.  
72.  
73. `#Path to installation directory. All paths are usually resolved relative to this.`
74. `#basedir="E:/Program Files/MySQL/MySQL Server 5.5/"`
75. `basedir="E:/Program Files/MySQL56/"`
76.  
77. `#Path to the database root`
78. `#datadir="E:/Program Files/MySQL/MySQL Server 5.5/Data/"`
79. `datadir="E:/Program Files/MySQL56/Data/"`
80.  
81. `# The default character set that will be used when a new schema or table is`
82. `# created and no character set is defined`
83. `character-set-server=utf8`
84.  
85. `# The default storage engine that will be used when create new tables when`
86. `default-storage-engine=INNODB`
87.  
88. `# Set the SQL mode to strict`
89. `sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"`
90.  
91. `# The maximum amount of concurrent sessions the MySQL server will`
92. `# allow. One of these connections will be reserved for a user with`
93. `# SUPER privileges to allow the administrator to login even if the`
94. `# connection limit has been reached.`
95. `max_connections=100`
96.  
97. `# Query cache is used to cache SELECT results and later return them`
98. `# without actual executing the same query once again. Having the query`
99. `# cache enabled may result in significant speed improvements, if your`
100. `# have a lot of identical queries and rarely changing tables. See the`
101. `# "Qcache_lowmem_prunes" status variable to check if the current value`
102. `# is high enough for your load.`
103. `# Note: In case your tables change very often or if your queries are`
104. `# textually different every time, the query cache may result in a`
105. `# slowdown instead of a performance improvement.`
106. `query_cache_size=0`
107.  
108. `# The number of open tables for all threads. Increasing this value`
109. `# increases the number of file descriptors that mysqld requires.`
110. `# Therefore you have to make sure to set the amount of open files`
111. `# allowed to at least 4096 in the variable "open-files-limit" in`
112. `# section [mysqld_safe]`
113. `#table_cache=256`
114. `table_open_cache=256`
115.  
116. `# Maximum size for internal (in-memory) temporary tables. If a table`
117. `# grows larger than this value, it is automatically converted to disk`
118. `# based table This limitation is for a single table. There can be many`
119. `# of them.`
120. `tmp_table_size=17M`
121.  
122.  
123. `# How many threads we should keep in a cache for reuse. When a client`
124. `# disconnects, the client's threads are put in the cache if there aren't`
125. `# more than thread_cache_size threads from before. This greatly reduces`
126. `# the amount of thread creations needed if you have a lot of new`
127. `# connections. (Normally this doesn't give a notable performance`
128. `# improvement if you have a good thread implementation.)`
129. `thread_cache_size=8`
130.  
131. `#*** MyISAM Specific options`
132.  
133. `# The maximum size of the temporary file MySQL is allowed to use while`
134. `# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.`
135. `# If the file-size would be bigger than this, the index will be created`
136. `# through the key cache (which is slower).`
137. `myisam_max_sort_file_size=100G`
138.  
139. `# If the temporary file used for fast index creation would be bigger`
140. `# than using the key cache by the amount specified here, then prefer the`
141. `# key cache method. This is mainly used to force long character keys in`
142. `# large tables to use the slower key cache method to create the index.`
143. `myisam_sort_buffer_size=34M`
144.  
145. `# Size of the Key Buffer, used to cache index blocks for MyISAM tables.`
146. `# Do not set it larger than 30% of your available memory, as some memory`
147. `# is also required by the OS to cache rows. Even if you're not using`
148. `# MyISAM tables, you should still set it to 8-64M as it will also be`
149. `# used for internal temporary disk tables.`
150. `key_buffer_size=25M`
151.  
152. `# Size of the buffer used for doing full table scans of MyISAM tables.`
153. `# Allocated per thread, if a full scan is needed.`
154. `read_buffer_size=64K`
155. `read_rnd_buffer_size=256K`
156.  
157. `# This buffer is allocated when MySQL needs to rebuild the index in`
158. `# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE`
159. `# into an empty table. It is allocated per thread so be careful with`
160. `# large settings.`
161. `sort_buffer_size=256K`
162.  
163.  
164. `#*** INNODB Specific options ***`
165.  
166.  
167. `# Use this option if you have a MySQL server with InnoDB support enabled`
168. `# but you do not plan to use it. This will save memory and disk space`
169. `# and speed up some things.`
170. `#skip-innodb`
171.  
172. `# Additional memory pool that is used by InnoDB to store metadata`
173. `# information. If InnoDB requires more memory for this purpose it will`
174. `# start to allocate it from the OS. As this is fast enough on most`
175. `# recent operating systems, you normally do not need to change this`
176. `# value. SHOW INNODB STATUS will display the current amount used.`
177. `innodb_additional_mem_pool_size=2M`
178.  
179. `# If set to 1, InnoDB will flush (fsync) the transaction logs to the`
180. `# disk at each commit, which offers full ACID behavior. If you are`
181. `# willing to compromise this safety, and you are running small`
182. `# transactions, you may set this to 0 or 2 to reduce disk I/O to the`
183. `# logs. Value 0 means that the log is only written to the log file and`
184. `# the log file flushed to disk approximately once per second. Value 2`
185. `# means the log is written to the log file at each commit, but the log`
186. `# file is only flushed to disk approximately once per second.`
187. `innodb_flush_log_at_trx_commit=1`
188.  
189. `# The size of the buffer InnoDB uses for buffering log data. As soon as`
190. `# it is full, InnoDB will have to flush it to disk. As it is flushed`
191. `# once per second anyway, it does not make sense to have it very large`
192. `# (even with long transactions).`
193. `innodb_log_buffer_size=1M`
194.  
195. `# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and`
196. `# row data. The bigger you set this the less disk I/O is needed to`
197. `# access data in tables. On a dedicated database server you may set this`
198. `# parameter up to 80% of the machine physical memory size. Do not set it`
199. `# too large, though, because competition of the physical memory may`
200. `# cause paging in the operating system. Note that on 32bit systems you`
201. `# might be limited to 2-3.5G of user level memory per process, so do not`
202. `# set it too high.`
203. `innodb_buffer_pool_size=47M`
204.  
205. `# Size of each log file in a log group. You should set the combined size`
206. `# of log files to about 25%-100% of your buffer pool size to avoid`
207. `# unneeded buffer pool flush activity on log file overwrite. However,`
208. `# note that a larger logfile size will increase the time needed for the`
209. `# recovery process.`
210. `innodb_log_file_size=24M`
211.  
212. `# Number of threads allowed inside the InnoDB kernel. The optimal value`
213. `# depends highly on the application, hardware as well as the OS`
214. `# scheduler properties. A too high value may lead to thread thrashing.`
215. `innodb_thread_concurrency=8`

> ## 欢迎关注公众号： ##
> 
> # ![70][] #

[Link 1]: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
[table_cache]: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_cache
[table_open_cache]: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_open_cache
[70]: /images/20220514/e2f06a1fe79947909e7f362302f4bfe9.png