关于show tables命令的一场探险

已下是操作记录,在此先说结论

show tables命令更像是操作系统级的命令,通过找出所有.frm的文件，以确定该数据库中是否有该表。而至于.frm文件是否是表的结构文件，并不会去验证

mysql> desc test;

ERROR 1146 (42S02): Table 'wzy.test' doesn't exist




[root@localhost bak]# ls
Test.frm  Test.ibd
[root@localhost bak]# cp Test.frm ..
[root@localhost bak]# cp Test.ibd ..


[root@localhost wzy]# ls
bak  db.opt  test.frm  Test.frm  test.ibd  Test.ibd




mysql> use wzy
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_wzy |
+---------------+
| Test          |
| test          |
+---------------+
2 rows in set (0.00 sec)
我们在此看到两个表，可见只要有frm文件，就可以在库中看到表。

mysql> desc Test;
ERROR 1146 (42S02): Table 'wzy.test' doesn't exist
mysql> desc test;
ERROR 1146 (42S02): Table 'wzy.test' doesn't exist

这里是由于数据库启用lower_case_table_names=1，所有的sql都强制转换成小写，所以报错的都是test doesn't exist

mysql> use wzy
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_wzy |
+---------------+
| Test          |
| test          |
+---------------+
2 rows in set (0.00 sec)


mysql> desc Test;
ERROR 1017 (HY000): Can't find file: './wzy/Test.frm' (errno: 13 - Permission denied)
mysql> desc test;
ERROR 1146 (42S02): Table 'wzy.test' doesn't exist
mysql> quit
在这里lower_case_table_names=0，所有的sql大小写敏感，Test表之前是在大小写敏感时创建的，这个之后解释。



"uctest.frm" [New] 1L, 8C written
[root@localhost wzy]# ls

bak  db.opt  Test.frm  Test.ibd  uctest.frm

在这里创建了一个空的文件uctest.frm

[root@localhost wzy]# service mysql start
Starting MySQL. SUCCESS! 
[root@localhost wzy]# mysql -uroot -poracle
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25 MySQL Community Server (GPL)


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 wzy
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_wzy |
+---------------+
| Test          |
| uctest        |
+---------------+
2 rows in set (0.00 sec)
我们可以看到，在数据库中可以看到uctest。

mysql> desc Test;
ERROR 1017 (HY000): Can't find file: './wzy/Test.frm' (errno: 13 - Permission denied)
mysql> Ctrl-C -- exit!
Aborted
[root@localhost wzy]# exit
logout
我们最后来处理一下Permission denied的问题

Last login: Mon Aug 10 20:09:16 2015 from 192.168.40.1
[root@localhost ~]# cd /var/lib/mysql/
[root@localhost mysql]# cd wzy/
[root@localhost wzy]# ls
bak  db.opt  Test.frm  Test.ibd  uctest.frm
[root@localhost wzy]# ll
total 120
drwxr-xr-x. 2 root  root   4096 Aug 11 01:03 bak
-rw-rw----. 1 mysql mysql    65 Aug 10 21:35 db.opt
-rw-r-----. 1 root  root   8560 Aug 11 00:32 Test.frm
-rw-r-----. 1 root  root  98304 Aug 11 00:32 Test.ibd
-rw-r--r--. 1 root  root      8 Aug 11 01:07 uctest.frm
[root@localhost wzy]# chown mysql Test.frm 
[root@localhost wzy]# chown mysql Test.ibd 
[root@localhost wzy]# ls
bak  db.opt  Test.frm  Test.ibd  uctest.frm
[root@localhost wzy]# ll
total 120
drwxr-xr-x. 2 root  root   4096 Aug 11 01:03 bak
-rw-rw----. 1 mysql mysql    65 Aug 10 21:35 db.opt
-rw-r-----. 1 mysql root   8560 Aug 11 00:32 Test.frm
-rw-r-----. 1 mysql root  98304 Aug 11 00:32 Test.ibd
-rw-r--r--. 1 root  root      8 Aug 11 01:07 uctest.frm
[root@localhost wzy]# chown -g mysql Test.ibd 
chown: invalid option -- 'g'
Try `chown --help' for more information.
[root@localhost wzy]# chgrp mysql Test.ibd 
[root@localhost wzy]# chgrp mysql Test.frm 
[root@localhost wzy]# ll

drwxr-xr-x. 2 root  root   4096 Aug 11 01:03 bak
-rw-rw----. 1 mysql mysql    65 Aug 10 21:35 db.opt
-rw-r-----. 1 mysql mysql    8560 Aug 11 00:32 Test.frm
-rw-r-----. 1 mysql mysql  98304 Aug 11 00:32 Test.ibd
-rw-r--r--. 1 root  root      8 Aug 11 01:07 uctest.frm

使用chown与chgrp将用户属组转给mysql用户mysql组

[root@localhost wzy]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25 MySQL Community Server (GPL)


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 wzy
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_wzy |
+---------------+
| Test          |
| uctest        |
+---------------+
2 rows in set (0.00 sec)


mysql> desc Test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> 

这时已经正常只用这张Test表了。

实验比较混乱，再说下发现的东西，show tables命令应该是通过检索.frm文件来确定库中的表名，本身也不对frm文件进行校验，但是表的相关信息是有写到ibdata1中的，你直接移动.frm与.ibd文件并不能直接迁移文件。