通過連線的方式把資料檔案放在別的地方

上篇寫到通過create table data direcotry把資料檔案放在別的地方。http://blog.itpub.net/25099483/viewspace-1662886/

對於建立好的表同樣以

通過連線的方式把資料檔案放在別的地方：

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> select * from t1;

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

| id   | name | sex  |

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

|    1 | NULL | NULL |

|    2 | NULL | NULL |

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

2 rows in set (0.00 sec)

mysql> flush tables;

Query OK, 0 rows affected (0.00 sec)

[mysql@localhost test]$ cd /data/test

[mysql@localhost test]$ mv t1.ibd /data2/aaa 
[mysql@localhost test]$ cd /data2/aaa/ 
[mysql@localhost aaa]$ ll 
total 224 
-rw-rw----. 1 mysql mysql 131072 May 18 22:38 t1.ibd 
-rw-rw----. 1 mysql mysql 98304 May 19 20:32 test.ibd

[mysql@localhost test]$ cd /data/test

[mysql@localhost test]$ ll | grep t1.i 
lrwxrwxrwx. 1 mysql mysql 17 May 19 20:46 t1.ibd -> /data2/aaa/t1.ibd

mysql> exit 
Bye 
[mysql@localhost ~]$ ps -ef | grep mysql 
root 2000 1878 0 20:21 ? 00:00:00 sshd: mysql [priv] 
mysql 2049 2000 0 20:21 ? 00:00:00 sshd: mysql@pts/1,pts/0,pts/2,pts/3 
mysql 2052 2049 0 20:21 pts/1 00:00:00 -bash 
mysql 2399 2052 0 20:22 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/usr/my-new.cnf 
mysql 2631 2399 0 20:22 pts/1 00:00:00 /usr/sbin/mysqld --defaults-file=/usr/my-new.cnf --basedir=/usr --datadir=/data --plugin-dir=/usr/lib64/mysql/plugin --log-error=/data/localhost.localdomain.err --pid-file=/data/localhost.localdomain.pid 
mysql 2656 2049 0 20:24 pts/0 00:00:00 -bash 
mysql 2689 2049 0 20:32 pts/2 00:00:00 -bash 
mysql 2711 2049 0 20:33 pts/3 00:00:00 -bash 
mysql 2757 2052 6 20:47 pts/1 00:00:00 ps -ef 
mysql 2758 2052 0 20:47 pts/1 00:00:00 grep mysql 
[mysql@localhost ~]$ kill -9 2631 2399     --關庫
[mysql@localhost ~]$ 
[1]+ Killed mysqld_safe --defaults-file=/usr/my-new.cnf 
[mysql@localhost ~]$ 
[mysql@localhost ~]$ 
[mysql@localhost ~]$ mysqld_safe --defaults-file=/usr/my-new.cnf &   --啟庫
[1] 2759 
[mysql@localhost ~]$ 150519 20:47:15 mysqld_safe Logging to '/data/localhost.localdomain.err'. 
150519 20:47:15 mysqld_safe Starting mysqld daemon with databases from /data 

[mysql@localhost ~]$ 
[mysql@localhost ~]$ 
[mysql@localhost ~]$ mysql -u root 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 1 
Server version: 5.6.23-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) 

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

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> select * from t1;        --檢視沒有錯誤
+------+------+------+ 
| id | name | sex | 
+------+------+------+ 
| 1 | NULL | NULL | 
| 2 | NULL | NULL | 
+------+------+------+ 
2 rows in set (0.00 sec) 

mysql> insert into t1 (id) values (3); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from t1; 
+------+------+------+ 
| id | name | sex | 
+------+------+------+ 
| 1 | NULL | NULL | 
| 2 | NULL | NULL | 
| 3 | NULL | NULL | 
+------+------+------+ 
3 rows in set (0.00 sec)

轉載請註明源出處

QQ 273002188  歡迎一起學習