关于show tables命令的一场探险
来源:互联网 发布:淘宝开店认证在哪里弄? 编辑:程序博客网 时间:2024/05/01 00:06
已下是操作记录,在此先说结论
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 startStarting 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 -pEnter 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文件并不能直接迁移文件。
0 0
- 关于show tables命令的一场探险
- oracle中与mysql中的命令 show databases, show tables, desc table类似的命令集
- oracle中与mysql中的命令 show databases, show tables, desc table类似的命令集
- mysql -- show tables的结果不一定准确
- postgresql的show databases、show tables、describe table操作
- 北向峰会:一场安全圈极致北人的探险盛会
- SHOW OPEN TABLES语法
- SHOW OPEN TABLES语法
- 一场关于Android的争论
- SQL Server类似MySql的Show Tables功能
- MySQL Show命令的使用
- MYSQL的MySQL Show命令
- MySQL Show命令的使用
- MySQL SHOW 命令的使用
- MySQL Show命令的使用
- MySQL SHOW 命令的使用
- MySQL Show命令的使用
- MySQL Show命令的使用
- 程序异常处理
- 2015 Multi-University Training Contest 7
- 畅通工程再续
- file_put_contents and fputs
- 网络中的长连接和短链接
- 关于show tables命令的一场探险
- 1053. Path of Equal Weight (30)
- CC_CALLBACK原理及应用
- $.ajax 提交数据
- Android之Inflate()方法用途
- 不带缓冲IO和带缓冲IO
- 26-网络编程-08-网络编程(UDP协议-接收端)
- Caffe下自己的数据训练和测试
- 再谈算法复杂度