[root@server1 ]# mysql //进入容器的数据库
mysql> create database school; //创建school数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; //查看所有数据库,是否出现school
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use school; //进入数据库
Database changed
mysql> create table users (username char(64) not null, password int(24) default '123123', primary key (username)); //创建users表
Query OK, 0 rows affected (0.02 sec)
mysql> show tables; //查看school数据库中的所有表
+------------------+
| Tables_in_school |
+------------------+
| users |
+------------------+
mysql> insert into users(username,password) values('wangtong',111111); //添加5条数据
Query OK, 1 row affected (0.01 sec)
mysql> insert into users(username,password) values('lining',111112);
Query OK, 1 row affected (0.01 sec)
mysql> insert into users(username,password) values('xuwenyu',111113);
Query OK, 1 row affected (0.01 sec)
mysql> insert into users(username,password) values('wangwu',111114);
Query OK, 1 row affected (0.01 sec)
mysql> insert into users(username,password) values('zhaoliu',111115);
Query OK, 1 row affected (0.01 sec)
mysql> select* from users; //查询数据
+----------+----------+
| username | password |
+----------+----------+
| lining | 111112 |
| wangtong | 111111 |
| wangwu | 111114 |
| xuwenyu | 111113 |
| zhaoliu | 111115 |
+----------+----------+
另起一个终端:新建一个/a的目录
[root@server1 ~]# mkdir /a
[root@server1 ~]# cd /
[root@server1 /]# ll
总用量 30
drwxr-xr-x. 2 root root 6 12月 3 10:31 a
mysql> select * from users into outfile '/a/test.xls'; //将数据库文件导入/a目录下
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement //出现报错,需要附加权限
mysql> exit //退出数据库
Bye
修改配置文件
[root@server1 mysql]# vi /etc/my.cnf
19 secure-file-priv = ""
20 server-id=1
添加一行数据,若特定路径需在“”内添加该路径,若不限定,自由创建,只要有权限就可以导入,就留空值
重启服务,查看服务是否开启
[root@server1 ~]# systemctl restart mysqld.service
[root@server1 ~]# netstat -anpt | grep 3306
tcp6 0 0 :::3306 :::* LISTEN 73461/mysqld
再次进入数据库,进行配置
[root@server1 ~]# mysql
mysql> use school;
mysql> select * from users into outfile '/a/test.xls'; //将数据库文件导入/a目录下 出现以下报错
ERROR 1 (HY000): Can't create/write to file '/a/test.xls' (Errcode: 13 - Permission denied) 提示权限出现问题
另一台终端修改权限 给/a目录
[root@server1 /]# cd
[root@server1 ~]# chmod 777 /a
[root@server1 ~]# ll /
总用量 30
drwxrwxrwx. 2 root root 6 12月 3 10:31 a
mysql> select * from users into outfile '/a/test.xls'; //再次测试,此时可以创建成功
Query OK, 4 rows affected (0.00 sec)
另一台终端查看/a目录下是否有表test生成
[root@server1 ~]# ls /a
test.xls
登录虚拟机图形化界面(查看有test.xls文件生成)
将该文件复制粘贴到系统桌面
打开,发现数据以表格的形式存储
附加:报错解决方案
现网现在流行一个技术为将mysql数据库中表格导出给excel去用,那么如何实现呢?
已进入数据库:
mysql> select xingming,chengji from chengji into outfile '/tmp/user.xls';
未登录数据库的数据导出(终端导出)
#mysql -uroot -pabc123 -e "select xingming,chengji from aaa.chengji" > /opt/user.xls;
此时出现了报错信息:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
报错原因:
mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
secure_file_priv 为 NULL 时,表示限制mysqld不允许导入或导出。
secure_file_priv 为 /tmp 时,表示限制mysqld只能在/tmp目录中执行导入导出,其他目录不能执行。
secure_file_priv 没有值时,表示不限制mysqld在任意目录的导入导出。
解决方案:修改secure_file_priv变量值
mysql> set global secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
为只读变量,不给修改变量值
解决方案:
vi /etc/my.cnf
[mysqld]
secure_file_priv=''
systemctl restart mysqld
此时,再打开头那两条命令即可。