drop table if exists tb1;
create table tb1(
id int auto_increment,
adddate datetime,
title varchar(100),
primary key(id)
)
data directory = '/home/mysqldata'
;执行失败:[Err] 1030 - Got error -1 from storage engine
去掉 data directory 就是成功的
create table tb1(
id int auto_increment,
adddate datetime,
title varchar(100),
primary key(id)
)
data directory = '/home/mysqldata'
;执行失败:[Err] 1030 - Got error -1 from storage engine
去掉 data directory 就是成功的
-> id int auto_increment,
-> adddate datetime,
-> title varchar(100),
-> primary key(id)
-> )
-> data directory = '/home/mysqldata'
-> ;
Query OK, 0 rows affected (0.00 sec)你的版本是多少
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| innodb_force_recovery | 0 |
| innodb_ft_server_stopword_table | |
| innodb_version | 5.6.14 |
| master_verify_checksum | OFF |
| myisam_recover_options | OFF |
| protocol_version | 10 |
| relay_log_recovery | OFF |
| server_id | 0 |
| server_id_bits | 32 |
| server_uuid | d73ba116-3b02-11e3-83a9-000c29701595 |
| slave_sql_verify_checksum | ON |
| slave_type_conversions | |
| version | 5.6.14 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | Linux |
+---------------------------------+--------------------------------------+
18 rows in set (0.00 sec)mysql 是这两天才到官网上下载的
linux 用过了 CentOS,也在虚拟机上试了 Fedora,都是一样的结果
id int auto_increment,
adddate datetime,
title varchar(100),
primary key(id)
)
data directory = '/home/mysqldata/';
受影响的行: 0
时间: 0.020ssql_mode 属性为空:
select @@sql_mode;
查询结果为空字符串
create table tb1(
id int auto_increment,
adddate datetime,
title varchar(200),
primary key(id)
)
ENGINE = MyISAM
data directory = '/home/mysqldata';
[Err] 1 - Can't create/write to file '/home/mysqldata/tb1.MYD' (Errcode: 13)另外,不会为了指定数据存储目录,要限定我的表引擎吧??
没办法,上述是MYSQL的HELP
如果要用INNODB,试试设置
my.cnf中[mysqld]下设置
innodb_file_per_table=1可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。独立表空间:优点:1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收(除drop table操作处,表空不能自已回收)a) Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。c) 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。缺点:单表增加过大,如超过100个G。结论:共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整一 下:innodb_open_files 。
DATA DIRECTORY, INDEX DIRECTORY
By using DATA DIRECTORY='directory', you can specify where the InnoDB storage engine puts the .ibd tablespace file for a new table. This clause only applies when the innodb_file_per_table configuration option is enabled. The directory must be the full path name to the directory, not a relative path. See Section 14.2.4.2.34, “Improved Tablespace Management” for details about the performance aspects of tablespace management. When creating MyISAM tables, you can use the DATA DIRECTORY='directory' clause, the INDEX DIRECTORY='directory' clause, or both. They specify where to put a MyISAM table's data file and index file respectively.
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> create table tb1(
-> id int auto_increment,
-> adddate datetime,
-> title varchar(100),
-> primary key(id)
-> )
-> data directory = '/data/aaron/mysqldata';
Query OK, 0 rows affected (0.42 sec)Server version: 5.5.31-0+wheezy1 (Debian)-----------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`adddate` datetime DEFAULT NULL,
`title` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
另外,缺省情况下,InnoDB好象是一个数据库的一个表创建一个文件的奇怪的是,为什么 linux 下 mysql 5.5 在建表时 data directory 会无效,而 mysql 5.6 则干脆报错
用 mysql5.5 执行是没有问题,但是它的 data directory 选项就无效了,在我的机器上,指定的目录下并没有生成数据文件,数据文件还是被保存到 /var/lib/mysql/ 目录下
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.6.14 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show variable like '%per_ta%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'variable like '%per_ta%'' at line 1
mysql> show variables like '%per_ta%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.6.14 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> create table tb1(
-> id int auto_increment,
-> adddate datetime,
-> title varchar(200),
-> primary key(id)
-> )
-> engine = innodb,
-> data directory = '/home/mysqldata';
ERROR 1030 (HY000): Got error -1 from storage engine
还有什么要设置的吗?
[ERROR] InnoDB: File /home/mysqldata/test: 'mkdir' returned OS error 113但是,我已经把这个目录的所有者和所在组都设置给了 mysql 用户,并且 mysql 用户拥有对它的所有权限
[test@TestFedora home]$ ll
总用量 12
drwxr-xr-x. 2 mysql mysql 4096 10月 23 19:38 mysql
drwxrwxrwx. 2 mysql mysql 4096 10月 22 18:31 mysqldata
drwx------. 23 test test 4096 10月 23 21:51 test