如何在MySQL中修改innodb_data_file_path参数事项？很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

在测试环境下没有设置过多的详细参数就初始化并启动了服务，后期优化的过程中发现innodb_data_file_path设置过小：

root@node114:59:[(none)]>showvariableslike'%innodb_data_file_path%';+-----------------------+------------------------+|Variable_name|Value|+-----------------------+------------------------+|innodb_data_file_path|ibdata1:12M:autoextend|+-----------------------+------------------------+1rowinset(0.00sec)root@node114: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-fibdata*ib_logfile*

也可以启动MySQL，但是mysql错误日志里会报如下错误：

2019-03-29T07:10:47.844560Z0[Warning]Couldnotincreasenumberofmax_open_filestomorethan5000(request:65535)2019-03-29T07:10:47.844686Z0[Warning]Changedlimits:table_open_cache:1983(requested2000)2019-03-29T07:10:48.028262Z0[Warning]'NO_AUTO_CREATE_USER'sqlmodewasnotset.2019-03-29T07:10:48.147653Z0[Warning]InnoDB:Cannotopentablemysql/pluginfromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.mysqld:Table'mysql.plugin'doesn'texist2019-03-29T07:10:48.147775Z0[ERROR]Can'topenthemysql.plugintable.Pleaserunmysql_upgradetocreateit.2019-03-29T07:10:48.163444Z0[Warning]InnoDB:Cannotopentablemysql/gtid_executedfromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.mysqld:Table'mysql.gtid_executed'doesn'texist2019-03-29T07:10:48.163502Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened.2019-03-29T07:10:48.163658Z0[Warning]InnoDB:Cannotopentablemysql/gtid_executedfromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.mysqld:Table'mysql.gtid_executed'doesn'texist2019-03-29T07:10:48.163711Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened.2019-03-29T07:10:48.164619Z0[Warning]FailedtosetupSSLbecauseofthefollowingSSLlibraryerror:SSLcontextisnotusablewithoutcertificateandprivatekey2019-03-29T07:10:48.166805Z0[Warning]InnoDB:Cannotopentablemysql/server_costfromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.166891Z0[Warning]Failedtoopenoptimizercostconstanttables2019-03-29T07:10:48.168072Z0[Warning]InnoDB:Cannotopentablemysql/time_zone_leap_secondfromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.168165Z0[Warning]Can'topenandlocktimezonetable:Table'mysql.time_zone_leap_second'doesn'texisttryingtolivewithoutthem2019-03-29T07:10:48.169454Z0[Warning]InnoDB:Cannotopentablemysql/serversfromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.169527Z0[ERROR]Can'topenandlockprivilegetables:Table'mysql.servers'doesn'texist2019-03-29T07:10:48.170042Z0[Warning]InnoDB:Cannotopentablemysql/slave_master_infofromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.170617Z0[Warning]InnoDB:Cannotopentablemysql/slave_relay_log_infofromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.170946Z0[Warning]InnoDB:Cannotopentablemysql/slave_master_infofromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.171046Z0[Warning]Infotableisnotreadytobeused.Table'mysql.slave_master_info'cannotbeopened.2019-03-29T07:10:48.171272Z0[Warning]InnoDB:Cannotopentablemysql/slave_worker_infofromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.171626Z0[Warning]InnoDB:Cannotopentablemysql/slave_relay_log_infofromtheinternaldatadictionaryofInnoDBthoughthe.frmfileforthetableexists.Pleaserefertohttp://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.htmlforhowtoresolvetheissue.2019-03-29T07:10:48.171688Z0[Warning]Infotableisnotreadytobeused.Table'mysql.slave_relay_log_info'cannotbeopened.

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。