MySql 大数据处理性能提升日志清理

最新推荐文章于 2024-06-17 10:04:37 发布

Alex许恒

最新推荐文章于 2024-06-17 10:04:37 发布

阅读量1.7k

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/xuheng8600/article/details/79792839

版权

Mysql 专栏收录该内容

33 篇文章 1 订阅

订阅专栏

对于Oracle数据库

truncate table后空间没有释放解决方法：

查询数据库表使用情况，发现一张无用的表，占60G，查询语句如下。

www.2cto.com

select segment_name,sum(bytes)/1024/1024/1024 from dba_segments

where owner='TEST' group by segment_name

order by 2 desc

truncate table后，再通过以上查询空间没有释放，做以下操作就好了：

alter table table_name enable row movement;

alter TABLE table_name shrink SPACE;

alter table table_name DISABLE row movement;

truncate表后，不释放表空间

执行了truncate，但奇怪的是truncdate后这些表所占的表空间并没有释放。

最后发现是INITIAL_EXTENT在作怪。

下面模拟一下问题的现象，及解决过程。

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as testtest

--1.T_IMP_TRUNCATE表中没有数据。
SQL> select count(1) from T_IMP_TRUNCATE;
COUNT(1)
----------
0

--2.但T_IMP_TRUNCATE占用了104MB的空间
SQL> SELECT SUM(T.BYTES) / 1024 / 1024 MB
2 FROM USER_SEGMENTS T
3 WHERE T.SEGMENT_NAME = 'T_IMP_TRUNCATE';

MB
----------
104

--3.truncate掉表T_IMP_TRUNCATE
SQL> truncate table T_IMP_TRUNCATE;
Table truncated

--4.分析表T_IMP_TRUNCATE
SQL> ANALYZE TABLE T_IMP_TRUNCATE COMPUTE STATISTICS;
Table analyzed

--5.发现表空间依然没有释放
SQL> SELECT SUM(T.BYTES) / 1024 / 1024 MB
2 FROM USER_SEGMENTS T
3 WHERE T.SEGMENT_NAME = 'T_IMP_TRUNCATE';

MB
----------
104

--6.检查T_IMP_TRUNCATE表的INITIAL_EXTENT参数将近100MB
SQL>   SELECT T.TABLE_NAME, T.INITIAL_EXTENT/1024/1024 mb
2      FROM USER_TABLES T
3     WHERE T.TABLE_NAME = 'T_IMP_TRUNCATE'
4 ;

TABLE_NAME                                MB
------------------------------        ----------
T_IMP_TRUNCATE                        99.0

--7.决定对T_IMP_TRUNCATE表进行收缩shrink

--a.启用行迁移

SQL>   alter table T_IMP_TRUNCATE enable row movement;

--b.shrink表T_IMP_TRUNCATE

SQL>   alter TABLE T_IMP_TRUNCATE shrink SPACE;

--c.关闭行迁移

SQL>   alter table T_IMP_TRUNCATE DISABLE row movement;

Table altered

--8.shrink后T_IMP_TRUNCATE表空间已释放
SQL> SELECT SUM(T.BYTES) / 1024 / 1024 MB
2    FROM USER_SEGMENTS T
3   WHERE T.SEGMENT_NAME = 'T_IMP_TRUNCATE';
        MB
----------

0.1875

MySql的Delete、Truncate、Drop分析

相同点：

　　truncate 和不带 where 子句的 delete，以及 drop 都会删除表内的数据

不同点:

　　1.   truncate 和 delete 只删除数据不删除表的结构(定义)
　　drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

　　2.   delete 语句是数据库操作语言(dml)，这操作会放到rollback segement 中，事务提交之后才生效;如果有相应的 trigger，执行的时候将被触发。
　　truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

　　3.   delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动
　　显然 drop 语句将表所占用的空间全部释放。
　　truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage;truncate 会将高水线复位(回到最开始)。

　　4.   速度，一般来说: drop> truncate > delete

　　5. 安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及

　　使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.
　　想删除表,当然用 drop
　　想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
　　如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

　　Delete from Tablename where 条件
　　Truncate table Tablename
　　Drop table Tablename

另：Mysql 的 truncate和delete的区别

truncate table table_name 和delete from table_name 都是删除表中所有记录。

区别：

truncate能够快速清空一个表。并且重置auto_increment的值。而delete只能一行一行的删除。

但对于不同的类型存储引擎需要注意的地方是：

A 对于myisam

truncate会重置auto_increment的值为1。而delete后表仍然保持auto_increment。

B 对于innodb

truncate会重置auto_increment的值为1。delete后表仍然保持auto_increment。但是在做delete整个表之后重启MySQL的话，则重启后的auto_increment会被置为1。

也就是说，innodb的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存，但是重启后就丢失了，只能从1开始。实质上重启后的auto_increment会从 SELECT 1+MAX(ai_col) FROM t 开始。

MySQL删除数据几种情况以及是否释放磁盘空间:

1、drop table table_name 立刻释放磁盘空间，不管是 Innodb和MyISAM ;

2、truncate table table_name 立刻释放磁盘空间，不管是 Innodb和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;

3、delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间（应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;

4、对于delete from table_name where xxx带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;

5、delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。

6、delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

mysql truncate table未释放表空间磁盘空间

truncate table tablename;

该命令可以清空一个表里的所有数据，并归1自增ID的值。

但myisam的表和innodb的表在使用上有一定的区别。

myisam表会清空所有数据，并释放表空间，即硬盘空间会得到释放。

innodb表也会清空所有数据，但不释放表空间。

Innodb数据库对于已经删除的数据只是标记为删除，并不真正释放所占用的磁盘空间，这就导致InnoDB数据库文件不断增长。如果想彻底释放这些已经删除的数据，需要把数据库导出，删除InnoDB数据库文件，然后再倒入。下面是基本的步骤：

1 使用mysqldump命令将InnoDB数据库导出

2 停止MySQL

3 删除所有InnoDB数据库文件和日志

4 启动MySQL并自动重建InnoDB数据库文件和日志文件

5 导入前面备份的数据库文件

具体命令：

# 备份数据库：

mysqldump -uroot -proot --quick --force --all-databases > mysqldump.sql

# 停止数据库

service mysqld stop

# 删除这些大文件

rm /usr/local/mysql/var/ibdata1

rm /usr/local/mysql/var/ib_logfile*

# 手动删除除Mysql之外所有数据库文件夹，然后启动数据库

service mysqld start

# 还原数据

mysql -uroot -proot < mysqldump.sql

还有一种方式是在创建数据库的时候设置innodb_file_per_table，这样InnoDB会对每个表创建一个数据文件，然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。

编辑my.ini或my.cnf 在innodb段中加入 innodb_file_per_table=1 # 1为启用，0为禁用

通过mysql语句可以查看该变量的值：mysql> show variables like '%per_table%';

--本篇文章转自：http://www.cnblogs.com/xuxyblog/p/3966430.html

Mysql InnoDB删除数据后释放磁盘空间的方法

这篇文章主要介绍了Mysql InnoDB删除数据后释放磁盘空间的方法,Innodb数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,这就导致InnoDB数据库文件不断增长,本文就讲解释放磁盘空间的方法,需要的朋友可以参考下

Innodb数据库对于已经删除的数据只是标记为删除，并不真正释放所占用的磁盘空间，这就导致InnoDB数据库文件不断增长。

如果在创建数据库的时候设置innodb_file_per_table=1，这样InnoDB会对每个表创建一个数据文件，然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。

运行OPTIMIZE TABLE 表名后，虽然最后会报Table does not support optimize, doing recreate + analyze instead，但其实已经成功了：）

-------------------------------------------------------------

如果没有设置这个参数，又想释放空间，彻底释放这些已经删除的数据，需要把数据库导出，删除InnoDB数据库文件，然后再导入。

下面是基本的步骤：

1 使用mysqldump命令将InnoDB数据库导出
2 停止MySQL
3 删除所有InnoDB数据库文件和日志
4 启动MySQL并自动重建InnoDB数据库文件和日志文件
5 导入前面备份的数据库文件

----------------------------------------------

具体命令：

复制代码代码如下:

# 备份数据库：

mysqldump -uroot -proot --quick --force --all-databases > mysqldump.sql

# 停止数据库

service mysqld stop

# 删除这些大文件
rm /usr/local/mysql/var/ibdata1
rm /usr/local/mysql/var/ib_logfile*

# 手动删除除Mysql之外所有数据库文件夹，然后启动数据库

service mysqld start

# 还原数据

mysql -uroot -proot < mysqldump.sql

MySQL InnoDB表空间重新释放实战

博客分类： DB

MySQL SQL Server SQL

实战：

------备份DB

mysqldump -quick --database ixxx> ixxx.sql

------db所在的目录情况

[root@localhost mysql]# ls -all
总计 64422864
drwxr-xr-x 3 mysql mysql        4096 09-08 23:21 .
drwxr-xr-x 4 root root         4096 06-02 22:08 ..
-rw-r----- 1 mysql mysql 64938311680 09-08 23:21 ibdata1
-rw-r----- 1 mysql mysql   268435456 09-08 23:21 ib_logfile0
-rw-r----- 1 mysql mysql   268435456 09-08 21:05 ib_logfile1
-rw-r----- 1 mysql mysql   268435456 09-08 23:18 ib_logfile2
-rw-rw---- 1 mysql mysql         403 09-08 23:21 localhost.localdomain.err
-rw-rw---- 1 mysql mysql       30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql    23292032 09-08 23:14 localhost-slow.log
drwx--x--x 2 mysql mysql        4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql   137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql          19 09-08 19:42 mysql-bin.index

--------所有的db：

-------删除掉占用空间的

drop database ixxx;

quit;

------停止mysql

[root@localhost mysql]# service mysql stop
Shutting down MySQL.. [确定]

------删除innodb相关文件

[root@localhost mysql]# rm -rf ib_logfile0
[root@localhost mysql]# rm -rf ib_logfile1
[root@localhost mysql]# rm -rf ib_logfile2
[root@localhost mysql]# ls -all
总计 157332
drwxr-xr-x 3 mysql mysql      4096 09-08 23:22 .
drwxr-xr-x 4 root root       4096 06-02 22:08 ..
-rw-rw---- 1 mysql mysql       403 09-08 23:21 localhost.localdomain.err
-rw-rw---- 1 mysql mysql     30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql 23292032 09-08 23:14 localhost-slow.log
drwx--x--x 2 mysql mysql      4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql 137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql        19 09-08 19:42 mysql-bin.index

-----启动mysql

[root@localhost mysql]# service mysql start
Starting MySQL.................... [确定]

此时文件重新生成了：

[root@localhost mysql]# ls -all
总计 954808
drwxr-xr-x 3 mysql mysql      4096 09-08 23:23 .
drwxr-xr-x 4 root root       4096 06-02 22:08 ..
-rw-rw---- 1 mysql mysql 10485760 09-08 23:23 ibdata1
-rw-rw---- 1 mysql mysql 268435456 09-08 23:23 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 09-08 23:23 ib_logfile1
-rw-rw---- 1 mysql mysql 268435456 09-08 23:23 ib_logfile2
-rw-rw---- 1 mysql mysql      1912 09-08 23:23 localhost.localdomain.err
-rw-rw---- 1 mysql mysql     30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql         5 09-08 23:23 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql 23292220 09-08 23:23 localhost-slow.log
drwx--x--x 2 mysql mysql      4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql 137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql       106 09-08 23:23 mysql-bin.000002
-rw-rw---- 1 mysql mysql        38 09-08 23:23 mysql-bin.index
srwxrwxrwx 1 mysql mysql         0 09-08 23:23 mysql.sock

---进入查询下mysql是否正常

[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.45-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye

--------建库、重新导入

CREATE DATABASE `ixxx` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

mysql ixxx<ixxx.sql

truncate操作后不释放空间的解决办法

truncate操作后不释放空间的解决办法
Truncate不支持回滚，并且不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。
truncate table 后，有可能表空间仍没有释放，可以使用如下语句：
alter table 表名称 deallocate UNUSED KEEP 0;
注意如果不加KEEP 0的话，表空间是不会释放的。
例如：
alter table tablename deallocate UNUSED KEEP 0;
或者：
TRUNCATE TABLE tablename DROP STORAGE才能释放表空间。
例如： truncate table tablename DROP STORAGE;

如何在删除ibdata1和ib_logfile的情况下恢复MySQL数据库

删除了ibdata1和ib_logfile，后来，能正常启动了，但所有的表通过show tables能看到，但是select的过程中却报“Table doesn't exist”。

于是，建议他试试可传输表空间。

同时，自己也测试了下，确实可行。

测试版本 MySQL 5.6.32 社区版

恢复的基本步骤

1. 将原来的数据文件COPY到其它目录下。

2. 创建同名表，表结构必须保持一致。

3. 导出表空间

mysql> ALTER TABLE t DISCARD TABLESPACE;

4. 将原来的数据文件COPY回来

5. 导入表空间

mysql> ALTER TABLE t IMPORT TABLESPACE

下面的演示会略为复杂，主要是还原整个场景，并针对上述步骤中的2,4做了一个测试。

首先，创建测试数据

在这里创建两张表。之所以创建两张相同的表是为了方便后续的测试。

mysql> create table t1(id int,hiredate datetime);
Query OK, 0 rows affected (0.14 sec)

mysql> create table t2(id int,hiredate datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1,now());
Query OK, 1 row affected (0.06 sec)

mysql> insert into t1 values(2,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(1,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(2,now());
Query OK, 1 row affected (0.00 sec)

关闭数据库

# /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqladmin shutdown -uroot -p123456 -h127.0.0.1 -P3310

删除ibdata1，ib_logfile0和ib_logfile1

[root@localhost data]# cd /data/
[root@localhost data]# ls
auto.cnf  ib_logfile0  localhost.localdomain.err  mysql_upgrade_info  test
ibdata1   ib_logfile1  mysql                      performance_schema
[root@localhost data]# rm -rf ibdata1 
[root@localhost data]# rm -rf ib_logfile*[root@localhost data]# ls
auto.cnf  localhost.localdomain.err  mysql  mysql_upgrade_info  performance_schema  test

重新启动数据库

# /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld --defaults-file=/usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/my.cnf &

并没有报错

启动过程中的日志信息如下：

# 2016-08-18 11:13:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-08-18 11:13:18 0 [Note] /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld (mysqld 5.6.32) starting as process 3948 ...
2016-08-18 11:13:18 3948 [Note] Plugin 'FEDERATED' is disabled.
2016-08-18 11:13:18 3948 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-08-18 11:13:18 3948 [Note] InnoDB: The InnoDB memory heap is disabled
2016-08-18 11:13:18 3948 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-08-18 11:13:18 3948 [Note] InnoDB: Memory barrier is not used
2016-08-18 11:13:18 3948 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-08-18 11:13:18 3948 [Note] InnoDB: Using Linux native AIO
2016-08-18 11:13:18 3948 [Note] InnoDB: Using CPU crc32 instructions
2016-08-18 11:13:18 3948 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-08-18 11:13:19 3948 [Note] InnoDB: Completed initialization of buffer pool
2016-08-18 11:13:19 3948 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-08-18 11:13:19 3948 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-08-18 11:13:19 3948 [Note] InnoDB: Database physically writes the file full: wait...
2016-08-18 11:13:19 3948 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-08-18 11:13:21 3948 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-08-18 11:13:22 3948 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-08-18 11:13:22 3948 [Warning] InnoDB: New log files created, LSN=45781
2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-08-18 11:13:22 3948 [Note] InnoDB: Doublewrite buffer created
2016-08-18 11:13:22 3948 [Note] InnoDB: 128 rollback segment(s) are active.
2016-08-18 11:13:22 3948 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-08-18 11:13:22 3948 [Note] InnoDB: Foreign key constraint system tables created
2016-08-18 11:13:22 3948 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-08-18 11:13:22 3948 [Note] InnoDB: Tablespace and datafile system tables created.
2016-08-18 11:13:22 3948 [Note] InnoDB: Waiting for purge to start
2016-08-18 11:13:22 3948 [Note] InnoDB: 5.6.32 started; log sequence number 0
2016-08-18 11:13:22 3948 [Note] Server hostname (bind-address): '*'; port: 3310
2016-08-18 11:13:23 3948 [Note] IPv6 is available.
2016-08-18 11:13:23 3948 [Note]   - '::' resolves to '::';
2016-08-18 11:13:23 3948 [Note] Server socket created on IP: '::'.
2016-08-18 11:13:23 3948 [Note] Event Scheduler: Loaded 0 events
2016-08-18 11:13:23 3948 [Note] /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysqld: ready for connections.
Version: '5.6.32'  socket: '/data/mysql.sock'  port: 3310  MySQL Community Server (GPL)

可见，在启动的过程中，MySQL会重建ibdata1和redo log。

登录mysql客户端，看之前创建的t1，t2是否能访问

# /usr/test/mysql-5.6.32-linux-glibc2.5-x86_64/bin/mysql -h127.0.0.1 -p123456 -uroot -P3310

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

通过show tables能查看有t1表存在，但表中的具体内容则无法查看

同时，错误日志中输出以下信息

2016-08-18 11:15:13 3948 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

将数据目录下的test目录中的t1，t2表的数据文件和表定义文件COPY到其它地方

[root@localhost test]# cd /data/test/
[root@localhost test]# ll
total 216
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t1.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t2.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t2.ibd
[root@localhost test]# mv * /backup/
[root@localhost test]# ls
[root@localhost test]# ll /backup/
total 216
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t1.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t1.ibd
-rw-rw---- 1 mysql mysql  8594 Aug 18 11:06 t2.frm
-rw-rw---- 1 mysql mysql 98304 Aug 18 11:07 t2.ibd

登录客户端，创建t1和t2表，注意表结构和之前的必须保持一致

细心的童鞋会发现，下面的创表语句和刚开始的创表语句并不一样，列名不一致，这个其实是为了后续的测试

mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1(id_1 int,hiredate_1 datetime);
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

明明已经手动移除了，为什么创建表的时候还报这个错误呢？

接下来，可先执行个drop table操作

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 'test.t1'
mysql> create table t1(id_1 int,hiredate_1 datetime);
Query OK, 0 rows affected (0.07 sec)

对于t2表，我们定义一个不同的表结构，看是否可行？

mysql> drop table t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> create table t2(id_1 int);
Query OK, 0 rows affected (0.01 sec)

导出表空间

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

这个时候，数据目录下的test目录下，数据文件没有了，只剩下了表结构文件

[root@localhost test]# ls
t1.frm  t2.frm

导入表空间

首先对t1表进行测试

在这里，测试如下两种情况

1. 新的t1.frm+旧的t1.ibd

2. 旧的t1.frm+旧的t1.ibd

第一种情况

只是将t1表的数据文件COPY回来

[root@localhost test]# cp /backup/t1.ibd .
[root@localhost test]# chown mysql.mysql t1.ibd

导入t1表的表空间

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.21 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t2.cfg', will attempt to import without schema verification |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看t1表是否能访问

mysql> select * from t1;
+------+---------------------+
| id_1 | hiredate_1          |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> flush table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+---------------------+
| id_1 | hiredate_1          |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

喔，确实能访问，注意观察，表的列名与新的创表语句保持一致。

在这里之所以使用flush table操作，是为了刷新内存中的表定义。

下面看看t1的第二种情况，旧的t1.frm+旧的t1.ibd

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

[root@localhost test]# cp /backup/t1.frm .
cp: overwrite `./t1.frm'? y
[root@localhost test]# cp /backup/t1.ibd .
[root@localhost test]# chown mysql.mysql t1.frm 
[root@localhost test]# chown mysql.mysql t1.ibd

mysql> ALTER TABLE t1 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select * from t1;
+------+---------------------+
| id_1 | hiredate_1          |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> flush table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2016-08-18 17:45:02 |
|    2 | 2016-08-18 17:45:02 |
+------+---------------------+
2 rows in set (0.00 sec)

第一次查询的时候还是新的列名，对表进行flush后，就恢复到原来的列名了。

下面来看看t2表的导入情况

因为t2表的表结构发生了改变，在这里，也是测试如下两种情况

1. 新的t2.frm+旧的t2.ibd

2. 旧的t2.frm+旧的t2.ibd

首先，只是导入t2表的数据文件

[root@localhost test]# cp /backup/t2.ibd .
[root@localhost test]# ll
total 216
-rw-rw---- 1 mysql mysql  8594 Aug 18 17:55 t1.frm
-rw-r----- 1 mysql mysql 98304 Aug 18 18:00 t1.ibd
-rw-rw---- 1 mysql mysql  8556 Aug 18 17:52 t2.frm
-rw-r----- 1 root  root  98304 Aug 18 18:10 t2.ibd
[root@localhost test]# chown mysql.mysql t2.ibd

导入t2表的表空间进行测试

mysql> ALTER TABLE t2 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> select * from t2;
+------+
| id_1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> flush table t2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
+------+
| id_1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

从结果可以看出，只能读出第一列。

下面测试第二种情况，旧的t2.frm和t2.ibd

mysql> ALTER TABLE t2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.06 sec)

[root@localhost test]# rm -rf t2.frm 
[root@localhost test]# cp /backup/t2.frm .
[root@localhost test]# cp /backup/t2.ibd .
[root@localhost test]# chown mysql.mysql t2.frm 
[root@localhost test]# chown mysql.mysql t2.ibd

mysql> ALTER TABLE t2 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> select * from t2;
+------+
| id_1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> flush table t2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

在重新刷新后，就出现错误了，个人感觉，这个和系统表空间中的数据字典信息有关。

实际上，后续还测试了一下，如果将hiredate的列定义为varchar，则无论是使用之前的frm文件还是之后的，在导入表空间，进行查询时，数据库直接挂掉。

mysql> create table t1(id int,hiredate varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t1 import TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> select * from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

结论

经过上面的一系列测试，可以看到

1. 使用可传输表空间，可以解决在删除ibdata1和ib_logfile的情况下恢复MySQL数据库，当然，本文测试的前提是数据库正常关闭下删除的ibdata1和ib_logfile。

2. 使用可传输表空间，建议新建表的表结构和原来的表结构完全一致，同时，在导入表空间前，只需COPY回原来的数据文件，即ibd。

事实上，在数据库正常关闭下删除ibdata1，会导致mysql库中的以下几张表无法访问

mysql> select table_name from information_schema.tables  where table_schema='mysql' and engine='innodb';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

mysql> select * from mysql.innodb_index_stats;
ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist
mysql> select * from mysql.innodb_table_stats;
ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
mysql> select * from mysql.slave_master_info;
ERROR 1146 (42S02): Table 'mysql.slave_master_info' doesn't exist
mysql> select * from mysql.slave_relay_log_info;
ERROR 1146 (42S02): Table 'mysql.slave_relay_log_info' doesn't exist
mysql> select * from mysql.slave_worker_info;
ERROR 1146 (42S02): Table 'mysql.slave_worker_info' doesn't exist

同时，错误日志中报如下信息

2016-08-19 12:10:18 3041 [Warning] InnoDB: Cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-08-19 12:10:26 3041 [Warning] InnoDB: Cannot open table mysql/innodb_table_stats from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-08-19 12:10:34 3041 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-08-19 12:10:40 3041 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-08-19 12:10:46 3041 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

要解决这个问题，只能重建这些表。

参考

1. http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

2. http://dba.stackexchange.com/questions/48166/cannot-open-table-mysql-innodb-index-stats

optimize在mysql中的用法

optimize命令是mysql的常用优化命令，但是在InnoDB与MyISAM这两个存储引擎中却有很大的分别。本文将对这两个常用的存储引擎进行讨论

工具/原料

mysql，linux

方法/步骤

当对表有大量的增删改操作时，需要用optimize对表进行优化。可以减少空间与提高I/O性能，命令optimize table tablename;假如有foo表且存储引擎为MyISAM。

mysql>optimize table foo;

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

| Table | Op | Msg_type | Msg_text |

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

| test.foo| optimize | status | OK |

+------------+----------+----------+----------+
如果是InnoDB引擎，首先查看innodb_file_per_table（是否独享表空间）。

mysql>show variables like 'innodb_file_per_table';

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

| Variable_name | Value |

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

| innodb_file_per_table | OFF |

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

OFF代表开启共享表空间没有打开，即采用的是默认的共享表空间。这个时候可以在mysql的datadir路径下看到一个非常大的文件ibdata1，这个文件存储了所有InnoDB表的数据与索引。
如果foo是InnoDB，执行如下命令

mysql>optimeze table foo;

会返回如图信息，最后的一条Table does not support optimize, doing recreate + analyze instead，即代表optimize无法优化表。

这个时候使用如下命令优化表

mysql>alter table foo ENGINE = 'InnoDB';

mysql>analyze table foo;

返回如下信息

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

| Table | Op | Msg_type | Msg_text |

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

| test.foo | analyze | status | OK |

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

即可优化该表
如果开启了独享表空间，即每张表都有ibdfile。这个时候如果删除了大量的行，索引会重组并且会释放相应的空间因此不必优化
由于共享表空间所有表的数据与索引都存放于ibddata1文件中，随着数据量的增长会导致该文件越来越大。超过10G的时候查询速度就非常慢，因此在编译的时候最好开启独享表空间。因为mysql默认是关闭了独享表空间，下面有两个解决方案
方案一：先逻辑备份所有的数据库，将配置文件中innodb_file_per_table参数=1，再将备份导入
方案二：只要修改innodb_file_per_table参数，然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后，原来库中的表中的数据会继续存放于ibdata1中，新建的表才会使用独立表空间

END

Mysql删除数据后磁盘空间未释放的解决办法

原因及解决办法：

使用delete删除的时候，mysql并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。（BTW：看官方文档上好像是innodb引擎的可以利用操作系统来帮忙回收这些碎片，MyISam的表没有办法自己回收，这里待定，后续再看下）

官方推荐使用 OPTIMIZE TABLE命令来优化表，该命令会重新利用未使用的空间，并整理数据文件的碎片。

语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

注：该命令将会整理表数据和相关的索引数据的物理存储空间，用来减少占用的磁盘空间，并提高访问表时候的IO性能。但是，具体对表产生的影响是依赖于表使用的存储引擎的。该命令对视图无效。

该命令目前只对MyISAM、InnoDB，ARCHIVE的表起作用，其余引擎的不起作用，不过可以设置，后面会讲到~~

具体的使用例子：

1、查看表占用的空间大小：

Mysql删除数据后，磁盘空间未释放的解决办法

大约占用了51G的空间。

2、使用optimize命令

Mysql删除数据后，磁盘空间未释放的解决办法

使用的时间比较长，需要耐心等待。

3、查看优化之后的空间占用大小。

Mysql删除数据后，磁盘空间未释放的解决办法

占用空间15G左右，减少了大部分。

可见优化效果很好~

BTW：

查看表占用硬盘空间大小的SQL语句如下：（默认用M做展示单位）

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename';

对于InnoDB引擎的mysql， optimize 命令，将会被映射到alter table上，具体可以参见官方文档。

补充:

1、如何使optimize 支持其他引擎？

默认情况下，optimize不支持其他存储引擎，但是可以在启动mysqld的时候，使用 --skip-new 参数，这种情况下，optimize命令，将会被映射到alter table命令上，实现上述的功能。

2、该物理删除还是逻辑删除？

生产环境下，尽量不要用物理删除，一旦物理删除了，意味着数据恢复就会很麻烦。建议逻辑删除，数据仍存储在DB里。如果数据量很大的时候，可以考虑使用分库分表。但，这个仍旧是需要根据业务场景来。

3、optimize执行时会将表锁住，所以不要在高峰期使用。也不要经常使用，每月一次就足够了

mysql查表空间大小的SQL语句

SQL语句如下:
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'databasename';

例如查mysql数据库表空间大小:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_shop |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.52 sec)

mysql>
mysql> select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
-> concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
-> concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
-> concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
-> from information_schema.tables where TABLE_SCHEMA = ' mysql';
+-----------+--------------------+-----------+------------+
| data_size | max_data_size | data_free | index_size |
+-----------+--------------------+-----------+------------+
| 0.51MB | 1938103992319.99MB | 0.00MB | 0.09MB |
+-----------+--------------------+-----------+------------+
1 row in set (1.49 sec)

Mysql ibdata1文件瘦身与清理方法

终究，我还是从innodb转到了myisam，光innodb库的大小就够我受的，更别说查询速度了。

但是我遇到了一个问题，从innodb转到myisam很快就能完成，ibdata1文件还是那么大，有27G。

嗯，可以理解，可能因为我的库还在。

但是当我删除一个大库的时候瞬间就完成了，那一瞬间我就感觉不妙，几百万的数据瞬间就能删完？

没错，ibdata1文件还是稳稳的27G，然后我就慌了，然后我把所有库都转成myisam，绝望的事情发生了，

ibdata1还是那么大，删掉它让他重新生成还不行，直接启动不了：

Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/var/localhost.localdomain.pid)

看来innodb的缓存很难清了，只能把库全部备份，删了重新导了。

但是这样太麻烦，还可能出意外，于是我在网上找到了一个方法，只用了一半就搞定了，在这里备忘一下。

设置innodb一个表一个文件

默认innodb所有表的数据都在一个ibdata1里，但是如果设置一下每个表都分开，这样删了之前的ibdata1就没关系了，因为不依赖了。

如果你的表全部设置成了myisam了，确保没有表的数据在ibdata中，这时候想瘦身的话可以这样快速解决：

不管怎样先备份总不会错的

#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql

打开my.ini或my.cnf文件
[mysqld]下增加下面配置
innodb_file_per_table=1

验证配置是否生效，可以重启mysql后,执行
#service mysqld restart
#mysql -uroot -ppassword
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

关闭mysql，删除ibdata1和ib_logfile*文件，启动mysql，完成。

如果你的部分表必须还要用innodb，只是想清理下ibdata1的话

上面的步骤执行完后，还需要导入之前的备份，因为你部分表的数据在之前的ibdata1中，已经被删了。

mysql -uroot -ppasswrod < /home/backup/all.sql

完成

MySql清除日志

如果想要关闭二进制mysql日志，可以在my.ini里把log-bin这行注释掉要清二进制日志的话，在mysql开启了二进制日志的状态下，以root身份登录mysql以后执行下面两个命令之一：

PURGE MASTER LOGS before '2020-1-1'; 清除指定日期之前的二进制日志 
PURGE MASTER LOGS TO 'mysql-bin.000010'; 清除指定文件编号之前的二进制日志

MySQL中有六种五种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。

这里简单总结一下这三者具有一定相关性的日志。

重做日志（redo log）

作用：　　确保事务的持久性。　　防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。内容：　　物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。什么时候产生：　　事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。什么时候释放：　　当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。对应的物理文件：　　默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2　　innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。　　innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2　　关于文件的大小和数量，由一下两个参数配置　　innodb_log_file_size 重做日志文件的大小。　　innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1其他：　　很重要一点，redo log是什么时候写盘的？前面说了是在事物开始之后逐步写盘的。　　之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，　　原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb_log_buffer中。

　　然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘　　1，Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。　　2，每个事务提交时会将重做日志刷新到重做日志文件。　　3，当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件　　由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。　　因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。　　另外引用《MySQL技术内幕 Innodb 存储引擎》（page37）上的原话：　　即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。　　这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

回滚日志（undo log）

作用：　　保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：　　逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

什么时候产生：　　事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

什么时候释放：　　当事务提交之后，undo log并不能立马被删除，　　而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

对应的物理文件：　　MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。　　MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数　　如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。　　关于MySQL5.7之后的独立undo 表空间配置参数如下　　innodb_undo_directory = /data/undospace/ --undo独立表空间的存放目录　　innodb_undo_logs = 128 --回滚段为128KB　　innodb_undo_tablespaces = 4 --指定有4个undo log文件

　　如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。　　

其他：　　undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。　　默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。　　因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。　　因此，mysql5.7之后的“独立undo 表空间”的配置就显得很有必要了。

二进制日志（binlog）：

作用：　　1，用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。　　2，用于数据库的基于时间点的还原。内容：　　逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。　　但又不完全是sql语句这么简单，而是执行的sql语句（增删改）反向的信息，　　也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。　　在使用mysqlbinlog解析binlog之后一些都会真相大白。　　因此可以基于binlog做到类似于Oracle的闪回功能，其实都是依赖于binlog中的日志记录。

什么时候产生：　　事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。　　这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。　　因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。　　这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

什么时候释放：　　binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。　　

对应的物理文件：　　配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。　　对于每个binlog日志文件，通过一个统一的index文件来组织。

其他：　　二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同　　1，作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。　　2，内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句　　3，另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

　　关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，　　MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

总结：

　　MySQL中，对于以上三种日志，每一种细化起来都可以够写一个章节的，这里粗略地总结了一下三种日志的一些特点和作用，以帮助理解MySQL中的事物以及事物背后的原理。

参考：《MySQL技术内幕 Innodb 存储引擎》 PDF 下载见 http://www.linuxidc.com/Linux/2013-06/86413.htm

Alex许恒

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySql 大数据处理性能提升日志清理

对于Oracle数据库truncate table后空间没有释放解决方法：查询数据库表使用情况，发现一张无用的表，占60G，查询语句如下。 www.2cto.com select segment_name,sum(bytes)/1024/1024/1024 from dba_segments where owner='TEST' group by segment_name order by ...
复制链接

扫一扫