PlumRain 2020-02-20
问题:由于环境中的Mysql数据库使用的是Mysql5.1.77版本 ,使用的是MYISAM引擎 ,会导致表级锁,并且不支持故障自动恢复(CSR)
表级锁:对表中任意一行数据修改操作时,整个表都会被锁定,对其他行的数据都不能操作
不支持故障自动恢复(CSR) :当断电时可能会出现数据损坏或丢失的问题
解决方案: 将数据库升级到5.6.44版本并更换引擎为Innodb
5.1.77里对Innodb的支持不够完善,更换版本为5.6.44
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)
[ ~]# mysqldump -uroot -p -S /data/3307/data/mysql.sock -B aaa >/tmp/aa.sql Enter password:***
[ ~]# scp /tmp/aa.sql :/a.sql 's password: aa.sql 100% 3450 2.7MB/s 00:00 [ ~]#
[ ~]# 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