一,指定存储引擎
mysql> create table if not exists depart_inno(did int,dname varchar(30)) engine=InnoDB; --支持行所和外键,事物安全。创建新表是的默认存储引擎
Query OK, 0 rows affected (0.05 sec)
mysql> create table if not exists depart_myisam(did int, dname varchar(30)) engine=MyISAM; --二进制可移植的存储引擎,用于只读或多数为查询的环境
Query OK, 0 rows affected (0.01 sec)
[root@vmhostd sx]# ll -t
total 102984
-rw-rw---- 1 mysql mysql 0 Feb 8 02:34 depart_myisam.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 8 02:34 depart_myisam.MYI
-rw-rw---- 1 mysql mysql 8590 Feb 8 02:34 depart_myisam.frm
-rw-rw---- 1 mysql mysql 98304 Feb 8 02:34 depart_inno.ibd
-rw-rw---- 1 mysql mysql 8590 Feb 8 02:34 depart_inno.frm
[root@vmhostd test]# cp ../sx/depart_myisam.* ./ --将myisam表的物理文件复制到另一个数据库中
[root@vmhostd test]# ll
total 20
-rw-r--r-- 1 mysql mysql 65 Nov 21 13:34 db.opt
-rw-r----- 1 root root 8590 Feb 8 02:43 depart_myisam.frm
-rw-r----- 1 root root 0 Feb 8 02:43 depart_myisam.MYD
-rw-r----- 1 root root 1024 Feb 8 02:43 depart_myisam.MYI
[root@vmhostd test]# chown mysql:mysql depart* --修改数据库文件所有者
[root@vmhostd test]# chmod 660 depart* --修改数据库文件权限
[root@vmhostd test]# ll
total 20
-rw-r--r-- 1 mysql mysql 65 Nov 21 13:34 db.opt
-rw-rw---- 1 mysql mysql 8590 Feb 8 02:43 depart_myisam.frm
-rw-rw---- 1 mysql mysql 0 Feb 8 02:43 depart_myisam.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 8 02:43 depart_myisam.MYI
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 |
+----------------+
| depart_myisam |
+----------------+
1 row in set (0.00 sec)
mysql> select * from depart_myisam;
ERROR 1017 (HY000): Can't find file: './test/depart_myisam.frm' (errno: 13 - Permission denied) --由于没有改变复制的文件的所有者和权限所以报错,数据库是运行在mysql用户下的
mysql> select * from depart_myisam; --修改文件所有者和权限后
Empty set (0.00 sec)
mysql> show engines; --查看支持的数据库引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
二,指定数据文件位置,指定索引文件位置(对于myisam引擎,InnoDB数据和索引在同一个文件中)
mysql> create table cart(iid int primary key, cname varchar(30))engine=MyISAM data directory='/var/pub/db' index directory='/var/pub/db';
Query OK, 0 rows affected (0.01 sec)
[root@vmhostd db]# ll
total 4
-rw-rw---- 1 mysql mysql 0 Feb 8 03:13 cart.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 8 03:13 cart.MYI
[root@vmhostd db]# ll /usr/local/mysql/data/test
total 268
-rw-rw---- 1 mysql mysql 8590 Feb 8 03:13 cart.frm
lrwxrwxrwx 1 mysql mysql 20 Feb 8 03:13 cart.MYD -> /var/pub/db/cart.MYD
lrwxrwxrwx 1 mysql mysql 20 Feb 8 03:13 cart.MYI -> /var/pub/db/cart.MYI
-rw-r--r-- 1 mysql mysql 65 Nov 21 13:34 db.opt