mysql(七)

MySQL存储引擎-innodb

img

查看存储引擎

mysql> show engines;

#查看整个数据库中，哪些表是innodb
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';

#查看整个数据库中，哪些表是myisam
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';

innodb和myisam的物理区别

#myisam
[root@db01 mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 11月 28 14:43 user.frm     #表结构
-rw-rw---- 1 mysql mysql  1408 12月  2 08:48 user.MYD     #表空间
-rw-rw---- 1 mysql mysql  2048 12月  2 08:48 user.MYI

#innodb
[root@db01 zls]# ll
-rw-rw---- 1 mysql mysql  8556 12月  4 11:02 zls1.frm    #表结构
-rw-rw---- 1 mysql mysql 98304 12月  4 11:02 zls1.ibd    #表空间

innodb

image.png

核心特性

• MVCC 多版本并发控制
• 事务
• 行级锁
• 热备
• CSR（自动故障恢复）

myisam

image.png

#查看当前使用的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

mysql> show create table zls.zls1;

#修改配置文件
default-storage-engine=<Storage Engine>

企业案例

项目背景：

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

小问题不断：

• 1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
• 2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

如何解决？？？？

1.准备新环境，使用mysql5.6.44

./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

2.备份导出数据

mysqldump -B 程序库> /tmp/full.sql

3.修改存储引擎

sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql

vim /tmp/full.sql
:%s#MyISAM#InnoDB#g 

4.将备份的数据导入新环境

mysql < /tmp/full.sql

5.修改代码，连接数据库的IP

6.先停库

7.截取全备，到停库之间的新数据

8.恢复到新数据库，开启业务

innodb-表空间

1.共享表空间（5.5版本以后出现共享表空间概念）

• 系统数据
• 临时表
• undo log（事务日志）

2.独立表空间

• 生产数据，用户数据

#共享表空间（默认大小12M）
[root@db01 data]# ll
-rw-rw---- 1 mysql mysql 79691776 12月  4 11:35 ibdata1

mysql> show variables like '%path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath            |                        |
| ssl_crlpath           |                        |
+-----------------------+------------------------+

[root@db01 data]# du -sh ibdata1 
76M ibdata1

#共享表空间切割
[root@db01 data]# vim /etc/my.cnf
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

#重启数据库
[root@db01 data]# /etc/init.d/mysqld restart

image.png

报错原因：设置共享表空间的大小，50M小于实际表空间大小76M

解决方法：修改配置文件中的50M，76M

innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

注意：ibdata1 已产生的共享表空间大小一定要与配置文件中的大小一致，不能多，也不能少

#开启独立表空间
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

企业案例

在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。

解决思路：

1.准备新环境

2.将旧数据导入到新环境

3.需要知道建表语句（表结构）
管开发要建表语句

 CREATE TABLE `city_new` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `idx_pop` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;


#删除新表的表空间
mysql> alter table world.city_new discard tablespace;

#拷贝旧表空间
[root@db02 world]# cp -a city.ibd city_new.ibd

#导入表空间
mysql> alter table world.city_new import tablespace;

mysql> select * from world.city_new;

#删除旧表
[root@db02 world]# rm -fr city.ibd  city.frm

#新表改名
mysql> alter table world.city_new rename world.city;