更换Mysql存储引擎为Innodb
更换Mysql存储引擎为Innodb
问题:由于环境中的Mysql数据库使用的是Mysql5.1.77版本 ,使用的是MYISAM引擎 ,会导致表级锁,并且不支持故障自动恢复(CSR)
表级锁:对表中任意一行数据修改操作时,整个表都会被锁定,对其他行的数据都不能操作
不支持故障自动恢复(CSR) :当断电时可能会出现数据损坏或丢失的问题
解决方案: 将数据库升级到5.6.44版本并更换引擎为Innodb
5.1.77里对Innodb的支持不够完善,更换版本为5.6.44
实现步骤(使用aaa演示)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | aaa | | mysql | | performance_schema | +--------------------+ 8 rows in set (0.00 sec) mysql> mysql> show tables; +---------------+ | Tables_in_aaa | +---------------+ | a1 | | b1 | | c1 | | d1 | +---------------+ 4 rows in set (0.00 sec)
查看表使用的引擎
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam'; +--------------------+---------------------------+--------+ | table_schema | table_name | engine | +--------------------+---------------------------+--------+ | aaa | a1 | MyISAM | | aaa | b1 | MyISAM | | aaa | c1 | MyISAM | | aaa | d1 | MyISAM | +--------------------+---------------------------+--------+ 35 rows in set (0.00 sec)
将aaa数据库备份出来
[ ~]# mysqldump -uroot -p -S /data/3307/data/mysql.sock -B aaa >/tmp/aa.sql Enter password:***
将数据库的备份文件scp到新的mysql环境中
[ ~]# scp /tmp/aa.sql :/a.sql 's password: aa.sql 100% 3450 2.7MB/s 00:00 [ ~]#
在新的Mysql环境上修改备份文件的引擎信息
[ ~]# sed -i 's#ENGINE=MyISAM#ENGINE=INNODB#g' /a.sql
将数据库备份文件导入到新数据库中并查看
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | aaa | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql>
查看表的存储引擎
mysql> use aaa Database changed mysql> show tables; +---------------+ | Tables_in_aaa | +---------------+ | a1 | | b1 | | c1 | | d1 | +---------------+ 4 rows in set (0.00 sec) mysql> show create table a1; +-------+-------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------+ | a1 | CREATE TABLE `a1` ( `id` int(11) DEFAULT NULL, `name` varchar(12) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> #ENGINE=InnoDB 已经被更改为Innodb
相关推荐
云中舞步 2020-09-11
emmm00 2020-11-17
王艺强 2020-11-17
aydh 2020-11-12
世樹 2020-11-11
zry 2020-11-11
URML 2020-11-11
spurity 2020-11-10
yifangs 2020-10-13
Andrea0 2020-09-18
Ida 2020-09-16
ltd00 2020-09-12
tufeiax 2020-09-03
xjd0 2020-09-10
greatboylc 2020-09-10
adsadadaddadasda 2020-09-08
疯狂老司机 2020-09-08
CoderToy 2020-11-16
ribavnu 2020-11-16