Linux环境下修改MySQL数据库存储引擎
16lz
2021-01-22
今天在执行Oracle数据库迁移至MySQL数据库时报出了一个错误信息:
Specified key was too long; max key length is 1000 bytes
百度发现,原来需要更改MySQL数据库的存储引擎为InnoDB,查询目前现有的存储引擎信息:
[root@test-121 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.71 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
mysql>
查询发现,默认的存储引擎为MyISAM,目前安装的MySQL数据库提供了对InnoDB引擎的支持。
更改方法如下:
修改“/etc/my.cnf”配置文件
[root@test-121 mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=InnoDB
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
在[mysqld]节点下面增加“default-storage-engine=InnoDB”选项。
重启MySQL服务:
service mysqld restart
再次查看数据存储引擎信息:
mysql> show engines;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 10
Current database: *** NONE ***
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.01 sec)
发现已经将数据库存储引擎切换至InnoDB。
再次迁移数据库没有发生错误信息。
关键信息总结:
1、登录MySQL Console控制台
mysql –u root –p
2、查看数据库存储引擎
show engines;
3、MySQL数据库配置文件路径
/etc/my.cnf
4、增加数据存储引擎配置项
default-storage-engine=InnoDB
5、重启MySQL
service mysqld restart
更多相关文章
- mysql数据库忘记ROOT密码时的解决办法
- 无法从SQLite数据库获取最后一行
- sql2008中如何收缩数据库日志文件
- 如何利用SQL语句查询数据库中所有表的名称?
- java eclipse连接并且操作mysql数据库详解
- SQL server 2008 (R2) 无日志文附加数据库
- SQLyog Enterprise强制Copy表结构给本身数据库,数据会丢吗,急
- [Python] - No.1 使用python3连接Mysql 数据库
- 装了sql server 2005附加数据库成功,程序却无法访问