在MySQL中经常需要导出一些表的数据,或者加载数据到某些表中,这时就涉及到一些权限的问题。今天就遇到一个开发加载数据的问题,我在测试环境进行了一些测试,并得出了一结论,过程和结果如下:
在测试环境中,创建一个测试用户,然后以root身份
执行导入导出过程,导出导出过程正常:
$ mysqlplus.sh
Login MySQL Srevice: lcoalhost_3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.19-log MySQL Community Server (GPL)
Copyright (c) 2000, 2011, 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>
mysql>
mysql> select user,host from mysql.user;
+----------+-----------+
| user | host |
+----------+-----------+
| fileuser | % |
| mycat | % |
| zhao | % |
| root | 127.0.0.1 |
| root | ::1 |
| | centos01 |
| root | centos01 |
| | localhost |
| root | localhost |
+----------+-----------+
9 rows in set (0.01 sec)
mysql>
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DataPlatform |
| auction |
| db1 |
| db2 |
| db3 |
| db_bcty365 |
| db_pursey |
| discuzX3 |
| hibernate_20140223 |
| hivemeta |
| jjbbs |
| jjwiki |
| jjwikidb |
| liferay |
| lportal |
| mysql |
| performance_schema |
| test |
+--------------------+
19 rows in set (0.10 sec)
mysql>
mysql>
mysql>
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> show tabels;
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 'tabels' at line 1
mysql>
mysql>
mysql> show tables;
+-----------------------------------+
| Tables_in_test |
+-----------------------------------+
| department |
| networkspeed_day_isp_r |
| networkspeed_day_province_isp_r |
| networkspeed_day_province_r |
| networkspeed_day_system_ip |
| networkspeed_month_isp_r |
| networkspeed_month_province_isp_r |
| networkspeed_month_province_r |
| networkspeed_month_system_ip |
| networkspeed_week_isp_r |
| networkspeed_week_province_isp_r |
| networkspeed_week_province_r |
| networkspeed_week_system_ip |
| privilege |
| province_r |
| province_test |
| role |
| role_privilege |
| user |
| user_role |
+-----------------------------------+
20 rows in set (0.00 sec)
mysql>
mysql>
mysql> select user,host from mysql.user;
+----------+-----------+
| user | host |
+----------+-----------+
| fileuser | % |
| mycat | % |
| zhao | % |
| root | 127.0.0.1 |
| root | ::1 |
| | centos01 |
| root | centos01 |
| | localhost |
| root | localhost |
+----------+-----------+
9 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON test.* TO 'mis_lda'@'192.168.226.121' IDENTIFIED BY 'fPUw2TgI';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> select user,host from mysql.user;
+----------+-----------------+
| user | host |
+----------+-----------------+
| fileuser | % |
| mycat | % |
| zhao | % |
| root | 127.0.0.1 |
| mis_lda | 192.168.226.121 |
| root | ::1 |
| | centos01 |
| root | centos01 |
| | localhost |
| root | localhost |
+----------+-----------------+
10 rows in set (0.00 sec)
mysql>
mysql>
mysql> show tables;
+-----------------------------------+
| Tables_in_test |
+-----------------------------------+
| department |
| networkspeed_day_isp_r |
| networkspeed_day_province_isp_r |
| networkspeed_day_province_r |
| networkspeed_day_system_ip |
| networkspeed_month_isp_r |
| networkspeed_month_province_isp_r |
| networkspeed_month_province_r |
| networkspeed_month_system_ip |
| networkspeed_week_isp_r |
| networkspeed_week_province_isp_r |
| networkspeed_week_province_r |
| networkspeed_week_system_ip |
| privilege |
| province_r |
| province_test |
| role |
| role_privilege |
| user |
| user_role |
+-----------------------------------+
20 rows in set (0.01 sec)
mysql>
mysql>
mysql> create table test01 as select user,host from mysql.user where 1=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> select user,host from mysql.user into outfile '/tmp/test01.txt';
Query OK, 10 rows affected (0.01 sec)
mysql>
mysql> select * from test02;
ERROR 1146 (42S02): Table 'test.test02' doesn't exist
mysql>
mysql> select * from test01;
Empty set (0.00 sec)
mysql>
mysql> load data infile '/tmp/test01.txt' into table test01;
Query OK, 10 rows affected (0.04 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> select * from test02;
ERROR 1146 (42S02): Table 'test.test02' doesn't exist
mysql>
mysql> select * from test01;
+----------+-----------------+
| user | host |
+----------+-----------------+
| fileuser | %