共計 6076 個字符，預計需要花費 16 分鐘才能閱讀完成。
自動寫代碼機器人，免費開通
如何在 MySQL 中修改 innodb_data_file_path 參數事項？很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
在測試環境下沒有設置過多的詳細參數就初始化并啟動了服務，后期優化的過程中發現 innodb_data_file_path 設置過小：
root@node1 14:59: [(none)] show variables like %innodb_data_file_path%
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
root@node1 14:59: [(none)]當沒有配置 innodb_data_file_path 時，默認 innodb_data_file_path = ibdata1:12M:autoextend
[mysqld]
innodb_data_file_path = ibdata1:12M:autoextend當需要改為 1G 時，不能直接在配置文件把 ibdata1 改為 1G ,
[mysqld]
innodb_data_file_path = ibdata1:1G:autoextend否則啟動服務之后，從錯誤日志看到如下報錯：
2019-03-29T06:47:32.044316Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 65536 pages, max 0 (relevant if non-zero) pages!
大致意思就是 ibdata1 的大小不是 65536page*16KB/1024KB=1G , 而是 786page*16KB/1024KB=12M
（未使用壓縮頁）
方法一：推薦
而應該再添加一個 ibdata2:1G，如下：
[mysqld]
innodb_data_file_path = ibdata1:12M;ibdata2:1G:autoextend重啟數據庫！
方法二：不推薦
直接改為如下的話
[mysqld]
innodb_data_file_path = ibdata1:1G:autoextend可以刪除 $mysql_datadir 目錄下 ibdata1、ib_logfile0、ib_logfile1 文件：
rm -f ibdata* ib_logfile*也可以啟動 MySQL，但是 mysql 錯誤日志里會報如下錯誤：
2019-03-29T07:10:47.844560Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 65535)
2019-03-29T07:10:47.844686Z 0 [Warning] Changed limits: table_open_cache: 1983 (requested 2000)
2019-03-29T07:10:48.028262Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2019-03-29T07:10:48.147653Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table mysql.plugin doesn t exist
2019-03-29T07:10:48.147775Z 0 [ERROR] Can t open the mysql.plugin table. Please run mysql_upgrade to create it.
2019-03-29T07:10:48.163444Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table mysql.gtid_executed doesn t exist
2019-03-29T07:10:48.163502Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.
2019-03-29T07:10:48.163658Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table mysql.gtid_executed doesn t exist
2019-03-29T07:10:48.163711Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.
2019-03-29T07:10:48.164619Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-03-29T07:10:48.166805Z 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.166891Z 0 [Warning] Failed to open optimizer cost constant tables
2019-03-29T07:10:48.168072Z 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.168165Z 0 [Warning] Can t open and lock time zone table: Table mysql.time_zone_leap_second doesn t exist trying to live without them
2019-03-29T07:10:48.169454Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.169527Z 0 [ERROR] Can t open and lock privilege tables: Table mysql.servers doesn t exist
2019-03-29T07:10:48.170042Z 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.170617Z 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.170946Z 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.171046Z 0 [Warning] Info table is not ready to be used. Table mysql.slave_master_info cannot be opened.
2019-03-29T07:10:48.171272Z 0 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.171626Z 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-03-29T07:10:48.171688Z 0 [Warning] Info table is not ready to be used. Table mysql.slave_relay_log_info cannot be opened.看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。
向 AI 問一下細節
正文完
