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

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:    安装好mysql rpm 包之后,将数据文件和日志文件的路径由/var/lib/mysql 迁移到 /opt/mysql/data /opt/mysql/log ,并调整了innodb_data_file_path 和innodb_log_file_siz...
   安装好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的基础知识掌握不牢固。还是要努力学习~
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
MySQL 日志之 binlog 格式 → 关于 MySQL 默认隔离级别的探讨
背景问题 再讲 binlog 之前,我们先来回顾下主流关系型数据库的默认隔离级别,是默认隔离级别,不是事务有哪几种隔离级别,别会错题意了 1、Oracle、SQL Server 的默认隔离级别是什么,MySQL 的呢 ? 2、为什么 MySQL 的默认隔离级别是 RR ?
|
Java 关系型数据库 MySQL
HikariPool-1 - Exception during pool initialization. Springboot 默认选择的mysql
HikariPool-1 - Exception during pool initialization. Springboot 默认选择的mysql
1184 1
|
SQL 缓存 关系型数据库
mysql 的预编译功能默认是关闭的| 学习笔记
快速学习 mysql 的预编译功能默认是关闭的。
286 0
mysql 的预编译功能默认是关闭的| 学习笔记
|
存储 Ubuntu 关系型数据库
Ubuntu 18.04 修改Mysql默认数据存储路径
Ubuntu 18.04 修改Mysql默认数据存储路径
445 0
Ubuntu 18.04 修改Mysql默认数据存储路径
|
关系型数据库 MySQL 数据库
mysql中如何将默认用户名root改成其他?
mysql中如何将默认用户名root改成其他?
466 0
mysql中如何将默认用户名root改成其他?
|
关系型数据库 MySQL 数据库连接
使用MySqL替代Freeswitch默认的SQLite数据库 以提高性能
使用MySqL替代Freeswitch默认的SQLite数据库 以提高性能
|
关系型数据库 MySQL 数据库
MySQL约束-默认约束(default)、零填充约束(zerofill)
MySQL约束-默认约束(default)、零填充约束(zerofill)
263 0
MySQL约束-默认约束(default)、零填充约束(zerofill)
|
Ubuntu 安全 关系型数据库
Ubuntu安装MySQL 8.0.21更改默认用户密码
Ubuntu安装MySQL 8.0.21更改默认用户密码
1286 0
Ubuntu安装MySQL 8.0.21更改默认用户密码
|
关系型数据库 MySQL 数据库
MySQL - GROUP BY 默认查询第几条数据?
MySQL - GROUP BY 默认查询第几条数据?
637 0
|
SQL 关系型数据库 MySQL
Mysql默认隔离级别下的sql语句执行
Mysql的默认隔离级别为可重复读,在这个隔离级别下,可以防止幻读和不可重复读,但读到的数据有可能不是最新。

热门文章

最新文章