05 : mysql 存储引擎 和 事务

 MySQL的存储引擎

  

01）InnoDB

02）MyISAM

03）MEMORY

04）ARCHIVE

05）FEDERATED

06）EXAMPLE

07）BLACKHOLE

08）MERGE

09）NDBCLUSTER

10）CSV

 

mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='myisam';

mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where engine='innodb';

innodb 和 myisam的区别

1.物理区别

[root@db01 test]# ll
total 10792
-rw-rw---- 1 mysql mysql 8730 Nov 25 10:18 student.frm
-rw-rw---- 1 mysql mysql 540672 Nov 25 10:27 student.ibd
[root@db01 test]# ll
-rw-rw---- 1 mysql mysql 10684 Nov 23 09:11 user.frm
-rw-rw---- 1 mysql mysql 760 Nov 25 10:18 user.MYD
-rw-rw---- 1 mysql mysql 2048 Nov 25 10:18 user.MYI

innodb核心特性

重点: MVCC 事务 行级锁 热备份 Crash Safe Recovery（自动故障恢复）

 

 

mysql> SELECT @@default_storage_engine;

+--------------------------+

| @@default_storage_engine |

+--------------------------+

| InnoDB |

+--------------------------+

vim /etc/my.cnf

[mysqld]

default-storage-engine=<Storage Engine>

企业案例

项目背景：

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

小问题不断：

1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。

2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

如何解决：

更换数据库的引擎

1.准备新环境，安装MySQL5.6 5.7

 

[root@db02 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql -- datadir=/application/mysql/data

2.导出程序连接的数据库

[root@db01 ~]# mysqldump -B zls > /tmp/zls.sql

3.修改存储引擎

[root@db01 ~]# vim /tmp/zls.sql

:%s#ENGINE=MyISAM#ENGINE=InnoDB#g

[root@db01 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/zls.sql

4.导入新环境

#方法一：

[root@db01 ~]# scp /tmp/zls.sql 172.16.1.52:/tmp/ [root@db02 scripts]# mysql < /tmp/zls.sql

#方法二：

mysql> grant all on *.* to root@'%' identified by '123'; [root@db01 ~]# mysql -uroot -p123 -h172.16.1.52 < /tmp/zls.sql

5.测试，功能

6.恢复数据提供服务

恢复到生产环境

把zls.sql导入生产库

截取新增的数据补全到生产库

应用割接

修改程序连接数据库的IP

截取新增的数据补全到新环境

表空间

 

# 共享表空间
mysql> show variables like '%path%';
+----------------------- + ------------------------ +
| Variable_name | Value |
+----------------------- + ------------------------ +
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath | |
| ssl_crlpath | |
+----------------------- + ------------------------ +

[root@db02 data]# ll

-rw-rw---- 1 mysql mysql 12582912 Dec 28 18:28 ibdata1

存储数据：

1.系统数据

2.临时表

3.undo（事务日志）

#修改配置文件 vim /etc/my.cnf [mysqld]

innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend [root@db01 data]# du -sh ibdata1
76Mibdata1

 

#独立表空间

mysql> show variables like '%per_table%';

+----------------------- + ------- +
| Variable_name | Value |
+----------------------- + ------- +
| innodb_file_per_table | ON |
+----------------------- + ------- +

 

企业案例

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

1.准备新环境

 

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

2.管开发或者DBA要建表语句

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`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

3.删除新建的表的独立表空间

mysql> alter table city_new discard tablespace;

4.拷贝旧表的独立表空间给新表

[root@db03 world]# cp -a city.ibd city_new.ibd

 

mysql> select * from city_new;

ERROR 1814 (HY000): Tablespace has been discarded for table 'city_new'

5.导入新表的独立表空间

 

mysql> alter table city_new import tablespace; Query OK, 0 rows affected, 1 warning (0.04 sec)

6.修改表名

mysql> alter table city_new rename city;

ERROR 1050 (42S01): Table 'city' already exists mysql> drop table city;

ERROR 1051 (42S02): Unknown table 'world.city'

 

mysql> alter table city_new rename city;

7.应用割接

 

MySQL 事务

1.什么是事务

主要是针对：DML（update delete insert）

 

2.事务的特性

A：原子性

把所有的步骤，视为一个单元，要么全部成功，只要有一条失败，全部回滚

 

C：一致性

事务执行前，和执行后都保持状态的一致

 

I：隔离性

事务与事务之间是互相隔离的，互不影响

 

D：持久性

在事务执行完成之后，数据是保持一致，不变。

 

 

 

DTL

#START TRANSACTION（或 BEGIN）：开始一个新事务

mysql> begin;

#SAVEPOINT：分配事务过程中的一个位置，以供将来引用

mysql> savepoint zls;

Query OK, 0 rows affected (0.00 sec)

#COMMIT：永久记录当前事务所做的更改

#ROLLBACK：取消当前事务所做的更改

#ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改

读档...

mysql> rollback to savepoint zls;

Query OK, 0 rows affected (0.00 sec)

RELEASE SAVEPOINT：删除 savepoint 标识符

mysql> release savepoint zls;

Query OK, 0 rows affected (0.00 sec)

SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

一个完整的事务，生命周期：

#成功的事务 begin; sql1

sql2

sql3

...

commit;

#失败的事务 begin; sql1

sql2

sql3

...

rollback;

#1.

begin;

insert into

select

create database

#2.

update

insert

delete

#3.

begin;

insert

commit;

#4.

begin;

update

rollback;

1）现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。

mysql> show variables like 'autocommit';

+--------------- +------- +
| Variable_name | Value |
+--------------- +------- +
| autocommit | ON |

+---------------+-------+

##事务的隐士提交

1.在开启事务的过程中，如果手动执行了begin;会自动提交上一次事务

2.在开启事务的过程中，如果执行了，DDL、DCL，都会自动提交事务

3.在开启事务的过程中，如果执行了锁表操作，也会自动提交事务

4.load data infile

5.select for update

6.autocommit=1

 

事务日志-redo

WAL：write ahead log

日志优先写

 

redo,顾名思义“重做日志”，是事务日志的一种。

 

 

[root@db03 ~]# ll /application/mysql/data/

-rw-rw---- 1 mysql mysql 50331648 Dec 28 23:56 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Dec 28 19:20 ib_logfile1

 

事务日志-undo

undo,顾名思义“不做日志”，是事务日志的一种。