许恒的博客

学习和分享IT技术经验

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


实战:

------备份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:

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

 

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

drop database ixxx;

 

quit;

 

------停止mysql

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

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

[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
[root@localhost mysql]# rm -rf ibdata1

[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

方法/步骤

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

    mysql>optimize table foo;

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

    | Table      | Op       | Msg_type | Msg_text |

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

    | test.foo| optimize | status   | OK       |

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

  2. 如果是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表的数据与索引。

  3. 如果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       |

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

    即可优化该表                             

    optimize在mysql中的用法
  4. 如果开启了独享表空间,即每张表都有ibdfile。这个时候如果删除了大量的行,索引会重组并且会释放相应的空间因此不必优化

  5. 由于共享表空间所有表的数据与索引都存放于ibddata1文件中,随着数据量的增长会导致该文件越来越大。超过10G的时候查询速度就非常慢,因此在编译的时候最好开启独享表空间。因为mysql默认是关闭了独享表空间,下面有两个解决方案

  6. 方案一:先逻辑备份所有的数据库,将配置文件中innodb_file_per_table参数=1,再将备份导入

  7. 方案二:只要修改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执行时会将表锁住,所以不要在高峰期使用。也不要经常使用,每月一次就足够了



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

阅读更多
版权声明:本文为博主原创文章,转载请申明原文出处。 https://blog.csdn.net/xuheng8600/article/details/79792839
个人分类: Mysql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

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

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭