一，系统参数datadir
在MySQL 中，每创建一个数据库都会在定义好（或者默认）的数据目录创建一个以数据库名字命名的文件夹，用来存放该数据库中的数据文件

mysql> show variables like '%data%';
+-------------------------------+------------------------+
| Variable_name | Value |
+-------------------------------+------------------------+
| character_set_database | latin1 |
| collation_database | latin1_swedish_ci |
| datadir | /u01/mysql/ |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_stats_on_metadata | OFF |
| max_length_for_sort_data | 1024 |
| metadata_locks_cache_size | 1024 |
| metadata_locks_hash_instances | 8 |
| myisam_data_pointer_size | 6 |
| skip_show_database | OFF |
| updatable_views_with_limit | YES |
+-------------------------------+------------------------+
12 rows in set (0.04 sec)

mysql> system ls -l /u01/mysql/
total 307844
-rw-r--r-- 1 root root 15625 Aug 4 05:30 8.txt
-rw-rw---- 1 mysql mysql 56 Mar 21 03:06 auto.cnf
drwxr-xr-x 5 mysql mysql 4096 Aug 17 04:34 data1
drwxr-xr-x 5 mysql mysql 4096 Aug 17 04:34 data2
-rw-rw---- 1 mysql mysql 213909504 Aug 20 03:24 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Aug 20 03:24 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Aug 20 03:24 ib_logfile1
-rw-rw---- 1 mysql mysql 129 Aug 20 03:24 master.info
drwx--x--x 2 mysql mysql 4096 Jun 15 22:40 mysql
-rw-r--r-- 1 root root 2609 Jul 21 14:59 mysql-bin.000001.txt
-rw-rw---- 1 mysql mysql 384 Aug 17 05:47 mysql-bin.000005
-rw-r--r-- 1 root root 1761 Aug 17 05:50 mysql-bin.000005.txt
-rw-rw---- 1 mysql mysql 167 Aug 17 06:03 mysql-bin.000006
-rw-rw---- 1 mysql mysql 963 Aug 17 06:16 mysql-bin.000007
-rw-rw---- 1 mysql mysql 30853 Aug 17 11:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql 143 Aug 17 11:20 mysql-bin.000009
-rw-rw---- 1 mysql mysql 2168 Aug 20 03:24 mysql-bin.000010
-rw-rw---- 1 mysql mysql 120 Aug 20 03:24 mysql-bin.000011
-rw-rw---- 1 mysql mysql 133 Aug 20 03:24 mysql-bin.index
-rw-r----- 1 mysql mysql 181859 Aug 20 03:37 mysqldb.err
-rw-rw---- 1 mysql mysql 5 Aug 20 03:24 mysqldb.pid
-rw-rw---- 1 mysql mysql 120 Aug 20 03:24 mysqldb-relay-bin.000028
-rw-rw---- 1 mysql mysql 27 Aug 20 03:24 mysqldb-relay-bin.index
-rw-rw---- 1 mysql mysql 2923 Aug 20 03:24 mysqldb-slow.log
-rw-r--r-- 1 root root 4146 Aug 17 04:34 mysqld_multi.log
srwxrwxrwx 1 mysql mysql 0 Aug 20 03:24 mysql.sock
drwx------ 2 root root 4096 Jun 24 13:54 performance_schema
-rw-rw---- 1 mysql mysql 63 Aug 20 03:24 relay-log.info
-rw-r--r-- 1 mysql mysql 2791 Jun 24 13:52 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql 545 Jun 24 13:52 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x 2 mysql mysql 4096 Aug 17 11:24 test
mysql> create database mbs;
Query OK, 1 row affected (0.00 sec)

mysql> system ls -ltr /u01/mysql/
total 307848
-rw-rw---- 1 mysql mysql 56 Mar 21 03:06 auto.cnf
drwx--x--x 2 mysql mysql 4096 Jun 15 22:40 mysql
-rw-r--r-- 1 mysql mysql 545 Jun 24 13:52 RPM_UPGRADE_MARKER-LAST
-rw-r--r-- 1 mysql mysql 2791 Jun 24 13:52 RPM_UPGRADE_HISTORY
drwx------ 2 root root 4096 Jun 24 13:54 performance_schema
-rw-r--r-- 1 root root 2609 Jul 21 14:59 mysql-bin.000001.txt
-rw-r--r-- 1 root root 15625 Aug 4 05:30 8.txt
-rw-r--r-- 1 root root 4146 Aug 17 04:34 mysqld_multi.log
drwxr-xr-x 5 mysql mysql 4096 Aug 17 04:34 data2
drwxr-xr-x 5 mysql mysql 4096 Aug 17 04:34 data1
-rw-rw---- 1 mysql mysql 384 Aug 17 05:47 mysql-bin.000005
-rw-r--r-- 1 root root 1761 Aug 17 05:50 mysql-bin.000005.txt
-rw-rw---- 1 mysql mysql 167 Aug 17 06:03 mysql-bin.000006
-rw-rw---- 1 mysql mysql 963 Aug 17 06:16 mysql-bin.000007
-rw-rw---- 1 mysql mysql 30853 Aug 17 11:17 mysql-bin.000008
-rw-rw---- 1 mysql mysql 143 Aug 17 11:20 mysql-bin.000009
drwxr-xr-x 2 mysql mysql 4096 Aug 17 11:24 test
-rw-rw---- 1 mysql mysql 63 Aug 20 03:24 relay-log.info
srwxrwxrwx 1 mysql mysql 0 Aug 20 03:24 mysql.sock
-rw-rw---- 1 mysql mysql 2923 Aug 20 03:24 mysqldb-slow.log
-rw-rw---- 1 mysql mysql 27 Aug 20 03:24 mysqldb-relay-bin.index
-rw-rw---- 1 mysql mysql 120 Aug 20 03:24 mysqldb-relay-bin.000028
-rw-rw---- 1 mysql mysql 5 Aug 20 03:24 mysqldb.pid
-rw-rw---- 1 mysql mysql 133 Aug 20 03:24 mysql-bin.index
-rw-rw---- 1 mysql mysql 2168 Aug 20 03:24 mysql-bin.000010
-rw-rw---- 1 mysql mysql 129 Aug 20 03:24 master.info
-rw-rw---- 1 mysql mysql 50331648 Aug 20 03:24 ib_logfile1
-rw-rw---- 1 mysql mysql 213909504 Aug 20 03:24 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Aug 20 03:24 ib_logfile0
-rw-r----- 1 mysql mysql 182004 Aug 20 03:38 mysqldb.err
-rw-rw---- 1 mysql mysql 211 Aug 20 03:39 mysql-bin.000011
drwx------ 2 mysql mysql 4096 Aug 20 03:39 mbs

二。MyISAM 引擎的数据文件
mysql> use mbs
Database changed

mysql> create table t_myi(name varchar(100)) engine myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_myi values('abcdef');
Query OK, 1 row affected (0.00 sec)

mysql> system ls -ltr /u01/mysql/mbs
total 136
-rw-rw---- 1 mysql mysql 65 Aug 20 03:39 db.opt
-rw-rw---- 1 mysql mysql 8570 Aug 20 03:40 t.frm
-rw-rw---- 1 mysql mysql 98304 Aug 20 03:40 t.ibd
-rw-rw---- 1 mysql mysql 8560 Aug 20 03:42 t_myi.frm
-rw-rw---- 1 mysql mysql 1024 Aug 20 03:42 t_myi.MYI
-rw-rw---- 1 mysql mysql 20 Aug 20 03:42 t_myi.MYD

三.innodb 引擎的数据文件
在这种情况下，表的存储分两种情况，独享和共享表空间，首先看共享，将 innodb_file_per_table设置为off

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_file_per_table=off;
Query OK, 0 rows affected (0.05 sec)

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create table t3(tid int,tname varchar(100));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3 values(1,'lsq');
Query OK, 1 row affected (0.02 sec)

mysql> show variables like '%innodb_data%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

mysql> insert into t3 select * from t3;
Query OK, 2097152 rows affected (1 min 4.75 sec)
Records: 2097152 Duplicates: 0 Warnings: 0

[root@mysqldb mysql]# ls -ltr ibdata1
-rw-rw---- 1 mysql mysql 213909504 Aug 20 04:10 ibdata1

[root@mysqldb mysql]# ls -ltr ibdata1
-rw-rw---- 1 mysql mysql 281018368 Aug 20 04:12 ibdata1

可见，表的数据都被存放在ibdata1文件中，再看独享的情况：

mysql> set global innodb_file_per_table=on;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like '%file_per%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create table t2(id int,ename varchar(100));
Query OK, 0 rows affected (0.03 sec)

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

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

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

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

[root@mysqldb mbs]# ls -ltr
total 45376
-rw-rw---- 1 mysql mysql 65 Aug 20 03:39 db.opt
-rw-rw---- 1 mysql mysql 8570 Aug 20 03:40 t.frm
-rw-rw---- 1 mysql mysql 98304 Aug 20 03:40 t.ibd
-rw-rw---- 1 mysql mysql 8560 Aug 20 03:42 t_myi.frm
-rw-rw---- 1 mysql mysql 1024 Aug 20 03:42 t_myi.MYI
-rw-rw---- 1 mysql mysql 20 Aug 20 03:42 t_myi.MYD
-rw-rw---- 1 mysql mysql 8580 Aug 20 03:43 t_inno.frm
-rw-rw---- 1 mysql mysql 98304 Aug 20 03:44 t_inno.ibd
-rw-rw---- 1 mysql mysql 8588 Aug 20 03:56 t2.frm
-rw-rw---- 1 mysql mysql 46137344 Aug 20 04:02 t2.ibd
-rw-rw---- 1 mysql mysql 8590 Aug 20 04:08 t3.frm

可见t2表的数据被存放在t2.ibd的文件中

ITPub博客

mysql的数据文件

最新文章