MySQL修改innodb_data_file_path参数的一些注意事项
前言
innodb_data_file_path用来指定innodbtablespace文件，如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1作为innodbtablespace。
说明
在测试环境下没有设置过多的详细参数就初始化并启动了服务，后期优化的过程中发现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.044316Z0[ERROR]InnoDB:TheAuto-extendinginnodb_systemdatafile'./ibdata1'isofadifferentsize768pages(roundeddowntoMB)thanspecifiedinthe.cnffile:initial65536pages,max0(relevantifnon-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'texist 2019-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'texist 2019-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'texist 2019-03-29T07:10:48.163711Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened. 2019-03-29T07:10:48.164619Z0[Warning]FailedtosetupSSLbecauseofthefollowingSSLlibraryerror:SSLcontextisnotusablewithoutcertificateandprivatekey 2019-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]Failedtoopenoptimizercostconstanttables 2019-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'texisttryingtolivewithoutthem 2019-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'texist 2019-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.
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。