linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL修改 innodb

时间:2019-04-05  来源:未知  作者:admin666

在测试环境下没有设置过多的详细参数就初始化并启动了服务,后期优化的过程中发现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.

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

友情链接
  • 阿里巴巴证实全资收购协作软件平台Teambition
  • 甲骨文通知用户需付费取得Java 8更新
  • MIPS指令集开源了
  • Oracle 索引扫描的几种类型
  • Oracle 利用执行计划来避免排序操作
  • 火爆的996.ICU项目正在酝酿996.ICU开源许可证禁止996公司使用
  • Oracle日常性能问题查看
  • Google宣布新的两步验证界面
  • 微软已正式废止 WoSign / StartCom 品牌根证书
  • Oracle里的查询转换
  • PostgreSQL 最终支持 “REINDEX CONCURRENTLY”
  • Trinity R14.0.6 桌面环境发布,基于KDE 3.5
  • Oracle 序列 视图 索引 同义词
  • Radeon的AMDVLK Vulkan驱动程序获得优化,支持战锤2
  • WordPress 5.2 Beta 1 发布
  • 关于 Oracle 分组查询
  • Libinput 1.13 发布,改进Touch Arbitration
  • 麦当劳收购一家价值3亿美元的机器学习初创公司Dynamic Yield
  • Oracle 11g R2 Backup Data Pump(数据泵)之expdp/impdp工具
  • 基于Chromium的Microsoft Edge可以使用两种不同的引擎
  • HTML5 Broadway后端在GTK 4.0发布之前再次受到关注
  • 28365
  • 中国福彩3d走势图
  • 老虎机88老虎
  • 香港彩票
  • 体彩大乐透开奖