为了提高IO性能,可以将不同的文件放在不同的磁盘。这里只说明InnoDB,MyISAM的修改方式。
1.环境
OS: CentOS 6.5 x64
MySQL: 5.6 for Linux (x86_64)
2.InnoDB
InnoDB指定数据文件的位置
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'; #先检查一下配置变量
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
如果innodb_file_per_table值为OFF,可以动态修改:
set global innodb_file_per_table=1;
CREATE TABLE temp3
(empno smallint(4) not null,
ename varchar(10),
job varchar(9),
mgr smallint(4),
hiredate date,
sal float(7, 2),
comm float(7, 2),
deptno tinyint(2)
) engine=innodb charset=utf8 data directory='/u01/mysql/temp/';
[root@test1 temp]# pwd
/u01/mysql/temp
[root@test1 temp]# ls
temp2.MYD temp2.MYI temp.MYD temp.MYI test #由于表在test库下,所以在指定的目录里产生了个test目录
[root@test1 temp]# cd test
[root@test1 test]# pwd
/u01/mysql/temp/test
[root@test1 test]# ll
total 96
-rw-rw---- 1 mysql mysql 98304 Jun 29 18:27 temp3.ibd #数据文件在这
InnoDB的数据文件不能像MyISAM一样使用标签链接方式指定数据文件。
3.MyISAM
mysql> SHOW VARIABLES LIKE 'have_symlink'; #先检查一下配置变量
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_symlink | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)
DISABLED 不可用,修改have_symlink变量,需要重启服务。
[root@test1 temp]# vi /etc/my.cnf
[mysqld]
basedir=/u01/mysql
datadir=/u01/mysql/data
socket=/u01/mysql/log/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=1 #需要配置成1
log-bin=mysql-bin
server-id=1
。。。
[root@test1 test]# /etc/init.d/mysql restart
Shutting down MySQL.... [ OK ]
Starting MySQL... [ OK ]
mysql> SHOW VARIABLES LIKE 'have_symlink'; #再次检查,现在ok
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink | YES |
+---------------+-------+
1 row in set (0.00 sec)
如果是新表,可以在创建的时候指定目录:
-
CREATE TABLE temp
-
(empno smallint(4) not null,
-
ename varchar(10),
-
job varchar(9),
-
mgr smallint(4),
-
hiredate date,
-
sal float(7, 2),
-
comm float(7, 2),
-
deptno tinyint(2)
-
) engine=myisam data directory='/u01/mysql/temp' index directory='/u01/mysql/temp';</span>
这时在数据目录里看数据文件:
[root@test1 test]# pwd
/u01/mysql/data/test
[root@test1 test]# ll -rth
。。。
-rw-rw---- 1 mysql mysql 8.6K Jun 29 06:56 temp.frm
lrwxrwxrwx 1 mysql mysql 24 Jun 29 06:56 temp.MYI -> /u01/mysql/temp/temp.MYI
lrwxrwxrwx 1 mysql mysql 24 Jun 29 06:56 temp.MYD -> /u01/mysql/temp/temp.MYD
这里用/u01/mysql/temp/目录只是用了演示,生产环境应该将这两个文件放到别的磁盘上。
如果表已经存在,需要先停掉mysql,再ln:
[root@test1 test]# pwd
/u01/mysql/data/test
[root@test1 test]# ll temp2* #已经存在temp2表
-rw-rw---- 1 mysql mysql 8780 Jun 29 09:10 temp2.frm
-rw-rw---- 1 mysql mysql 548 Jun 29 09:10 temp2.MYD
-rw-rw---- 1 mysql mysql 1024 Jun 29 09:10 temp2.MYI
[root@test1 test]# /etc/init.d/mysql stop #停掉mysql
Shutting down MySQL... [ OK ]
[root@test1 test]# mv temp2.MYD ../../temp/ #移到别的目录
[root@test1 test]# mv temp2.MYI ../../temp/ #移到别的目录
[root@test1 test]# ln -s /u01/mysql/temp/temp2.MYD /u01/mysql/data/test/temp2.MYD
[root@test1 test]# ln -s /u01/mysql/temp/temp2.MYI /u01/mysql/data/test/temp2.MYI
[root@test1 test]# ll temp2*
-rw-rw---- 1 mysql mysql 8780 Jun 29 09:10 temp2.frm
lrwxrwxrwx 1 root root 25 Jun 29 09:18 temp2.MYD -> /u01/mysql/temp/temp2.MYD
lrwxrwxrwx 1 root root 25 Jun 29 09:19 temp2.MYI -> /u01/mysql/temp/temp2.MYI
[root@test1 test]# /etc/init.d/mysql start
Starting MySQL. [ OK ]
mysql> select * from temp2;
+-------+--------------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------------+-----------+------+------------+---------+---------+--------+
| 7369 | 员工SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | 员工ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | 员工WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | 员工JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | 员工MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | 员工BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | 员工CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | 员工SCOTT | ANALYST | 7566 | 1982-10-09 | 3000.00 | NULL | 20 |
| 7839 | 员工KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | 员工TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | 员工ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | 员工JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | 员工FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | 员工MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
数据正常。
不管是InnoDB还是MyISAM,都不能使用alter table 修改数据文件所以位置。