【MySQL】一次修改mysql 默认路径的经历

  1. 云栖社区>
  2. 博客列表>
  3. 正文

【MySQL】一次修改mysql 默认路径的经历

北在南方 2016-04-13 17:38:01 浏览1420 评论0

摘要:  安装好mysql rpm 包之后,将数据文件和日志文件的路径由/var/lib/mysql 迁移到 /opt/mysql/data /opt/mysql/log ,并调整了innodb_data_file_path 和innodb_log_file_size 的值,启动数据库的时候报错: 关于数...

 安装好mysql rpm 包之后,将数据文件和日志文件的路径由/var/lib/mysql 迁移到 /opt/mysql/data /opt/mysql/log ,并调整了innodb_data_file_path 和innodb_log_file_size 的值,启动数据库的时候报错:
关于数据库文件的报错!
120709 19:12:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
120709 19:12:32 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: auto-extending data file /opt/mysql/data/ibdata1 is of a different size
InnoDB: 640 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 128000 pages, max 0 (relevant if non-zero) pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
120709 19:12:33 [ERROR] Plugin 'InnoDB' init function returned error.
120709 19:12:33 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
ibdata1 is of a different size 是由于 innodb_data_file_path 的值(文件大小)设置错误引起~
如错误提示中所说: ibdata1  的值640个pages 配置文件中的值128000个pages 不同。所以问题的原因找到了,修改my.cnf 中innodb_data_file_path 中ibdata1 的值即可!
方法如下:
640/64=10M 
innodb_data_file_path = ibdata1:10M:autoextend

root@AY120621100302d64e92e # du -sm ibdata1 
11      ibdata1 使用系统命令查看实际值比配置文件中的值大1M~!
重新启动数据库 又报关于日志的错误
120709 20:47:22 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
120709 20:47:22 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: log file /opt/mysql/data/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
120709 20:47:22 [ERROR] Plugin 'InnoDB' init function returned error.
120709 20:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
显然是日志文件的实际大小和my.cnf 的值不同!注意 error 提示当前的数据库是不支持innodb 存储引擎的!
登录数据库中查看:
创建innodb的表失败!
root@localhost : test 20:57:38> create table t2 engine=innodb as select 1,now();
Query OK, 1 row affected, 2 warnings (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
root@localhost : test 20:58:00> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1286 | Unknown table engine 'innodb'              |
| Warning | 1266 | Using storage engine MyISAM for table 't2' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)
数据库中并没有显示支持innodb!
root@localhost : (none) 20:59:30> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

正常关闭server的情况下,修改my.cnf 参数之后没有删除ib_logfile文件,ib_logfile文件中记录些innodb引擎非常有用的信息比如说默认的innodb默认的配置信息,又是在未正常关闭server情况下操作的,所以导致重启后的server不支持innodb引擎。
(关于innodb参数设置不当也导致 系统不支持innodb存储引擎 ) 
解决步骤:
1 关闭mysql数据库 ,观察 错误日记的信息,确保正常关闭!
2 修改innodb_log_file_size = 512M (按照自己的实际情况)
3 使用mv 命令将ib_logfile0 ib_logfileN 做备份!
4 重新启动数据库,并观察 错误日记的信息!
5 如果启动成功,则删除之前备份的旧日志文件

root@AY120621100302d64e92e # service mysql stop
Shutting down MySQL.120709 21:02:18 mysqld_safe mysqld from pid file /opt/mysql/data/AY120621100302d64e92e.pid ended
[  OK  ]
[1]+  Done                    /usr/bin/mysqld_safe
root@AY120621100302d64e92e # pwd
/opt/mysql/data
root@AY120621100302d64e92e # ls
ibdata1  ib_logfile0  ib_logfile1  mysql  test
root@AY120621100302d64e92e # mv ib_logfile0 ib_logfile0.bak
root@AY120621100302d64e92e # mv ib_logfile1 ib_logfile1.bak
root@AY120621100302d64e92e # /usr/bin/mysqld_safe &
[1] 14317
You have new mail in /var/spool/mail/root
root@AY120621100302d64e92e # 120709 21:03:06 mysqld_safe Logging to '/opt/mysql/log/mysql-error.log'.
120709 21:03:06 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
进行测试 创建innodb的表成功!
root@AY120621100302d64e92e # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost : (none) 21:04:38> use test;
Database changed
root@localhost : test 21:04:42> 
root@localhost : test 21:04:42> create table t3 engine=innodb as select 1,now();
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0
root@localhost : test 21:04:50> 
root@localhost : test 21:04:52> show create table t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `1` int(1) NOT NULL DEFAULT '0',
  `now()` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost : test 21:04:59> exit
Bye

附上最后修改后 错误日记的信息
120709 21:03:06 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
120709 21:03:06 [Note] Plugin 'FEDERATED' is disabled.
启动的过程中,mysqld会发现日志文件不存在,它会自己创建日志文件!
120709 21:03:06  InnoDB: Log file /opt/mysql/data/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /opt/mysql/data/ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
120709 21:03:29  InnoDB: Log file /opt/mysql/data/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /opt/mysql/data/ib_logfile1 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120709 21:03:54  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120709 21:03:54  InnoDB: Started; log sequence number 0 44556
120709 21:03:54 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
120709 21:03:54 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
120709 21:03:54 [Note] Event Scheduler: Loaded 0 events
120709 21:03:54 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.40-community-log'  socket: '/opt/mysql/data/mysql.sock'  port: 3306  MySQL Community Server (GPL)

整个过程总结下来,还是对mysql的基础知识掌握不牢固。还是要努力学习~
【云栖快讯】你想见的Java技术专家都在这了,向大佬提问,有问题必答  详情请点击

网友评论

北在南方
文章661篇 | 关注25
关注
MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供... 查看详情
支持 PB 级数据存储的海量分布式关系型数据库。它支持 MySQL 数据库接口,采用可扩展的... 查看详情
阿里云数加提供了大量的大数据产品,包括大数据基础服务、数据分析及展现、数据应用、人工智能等产... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航