mysql用户管理与数据库导入导出方法总结

weixin_47680367

已于 2025-03-26 16:43:42 修改

阅读量4.4k

点赞数

文章标签：数据库 mysql sql

于 2022-02-11 19:11:49 首次发布

原文链接：https://blog.csdn.net/u010700335/article/details/40554703

版权

本文详细介绍了MySQL用户管理，包括创建用户、权限分配、重命名与删除账户。重点讲解了mysqldump的使用，如何导出SQL文件、CSV和TXT格式数据，以及解决导入导出文件时的常见问题，涉及性能优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement　

数据的性能优化

Mysql用户管理

环境：

# mysqld -V
mysqld  Ver 5.7.42 for Linux on x86_64 (MySQL Community Server (GPL))

创建用户账号：

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |       root@'%'主机名为远程的登录用户
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |       root@'localhost'主机名为本地的登录用户 
+---------------+-----------+
5 rows in set (0.00 sec)

设置访问权限：

mysql> show grants for root@'%';
+----------------------------------+
| Grants for root@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

mysql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for root@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for root@'localhost';          
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |   
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |    #GRANT OPTION表示赋予root@'localhost'账户的grant和revoke权限
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

###创建用户账号和设置访问权限可以用一条sql语句实现，管理账户需要有GRANT OPTION权限(例如root@'localhost')
mysql> grant select on test.* to dongm identified by '123456';   #test.* 表示database.table  dongm表示默认创建dongm@'%'
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for dongm;
+-----------------------------------------+
| Grants for dongm@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'dongm'@'%'       |
| GRANT SELECT ON `test`.* TO 'dongm'@'%' |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

重命名用户账户

mysql> rename user dongm to test;        #将dongm重命名为test
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test;
+----------------------------------------+
| Grants for test@%                      |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%'       |
| GRANT SELECT ON `test`.* TO 'test'@'%' |
+----------------------------------------+
2 rows in set (0.00 sec)

删除用户账户

mysql> drop user test;
Query OK, 0 rows affected (0.00 sec)

权限列表

mysqldump的使用

一：数据库导出为sql文件

Navicat连接mysql出现2003——can't connect to mysql server on localhost(10061)

mysql 数据库导入导出方法总结（是时候总结）

注意：mysqldump 是在操作系统命令行下运行的。

不是在 mysql 命令行下运行的，进入MySQL目录下的bin文件夹,如：C:\Program Files\MySQL\MySQL Server 5.5\bin>

一般形式：mysqldump -h IP -u 用户名 -p 数据库名 > 导出的文件名
mysqldump是在cmd下的命令，不能再mysql下面，即不能进入mysql的（得exit退出mysql下才可以的。）

一：数据库的导出（备份）

1:(备份数据库即导出所有表以及数据不加-d)

mysqldump -h localhost -u root -p test > G:\arcgisworkspace\zypdoc\test.sql

2：(备份数据库即导出所有表结构) ##test为数据库名称，-d表示只导出表结构，不导出数据

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -h localhost -u root -p -d test > G:\arcgisworkspace\zypdoc\test.sql
ENTER PASSWORD:******

3：(导出某张表的表结构不含数据) ##test是数据库名，pollution是表名

mysqldump -h localhost -u root -p -d test pollution > G:\arcgisworkspace\zypdoc\test.sql

4：(导出某张表的表结构和数据，不加-d)

mysqldump -h 127.0.0.1 -u root -p test pollution > G:\arcgisworkspace\zypdoc\test.sql

5：备份多个数据库的语法C:\Program Files\MySQL\MySQL Server 5.5\bin>

mysqldump -h 127.0.0.1 -u root -p --databases test bank > G:\arcgisworkspace\zypdoc\test.sql

或 mysqldump -h 127.0.0.1 -u root -p --databases test bank > G:\arcgisworkspace\zypdoc\test.txt 是没有用的，因为仍然是sql的格式。

6:备份所有的数据库的语法C:\Program Files\MySQL\MySQL Server 5.5\bin>
mysqldump -h 127.0.0.1 -u root -p --all-databases > G:\arcgisworkspace\zypdoc\test.sql

实例：
D:\mysql\mysql-5.7.37-winx64\bin目录内shift+右键→在此处打开Powershell窗口

二：数据库的导入(利用sql文件还原)

导入数据库（首先得创建数据，再导入）
C:\Program Files\MySQL\MySQL Server 5.5\bin>

mysql -h localhost -u root -p（进入mysql下面）

create database abc;(创建数据库)

show databases;(就可看到所有已经存在的数据库，以及刚刚创建的数据库abc)

use abc;(进入abc数据库下面)

show tables;(产看abc数据库下面的所有表,空的)

source G:\arcgisworkspace\zypdoc\test.sql（导入数据库表）

show tables;(查看abc数据库下面的所有表,就可以看到表了)

desc pollution;(查看表结构设计)

select * from pollution;

exit(或者ctrl + c)退出mysql

MYSQL如何导入导出文本文件

有的时候需要把在一张表中用 select 语句查询出来的结果保存到另一张结构相同的表中，可以有几种方法来实现：

在命令行下使用一对SQL语句完成该操作：

导出查询结果：Select语句 into outfile '保存路径+文件名';

导入查询结果：load data local infile '保存路径+文件名' into table 表名 character set utf8;

mysql> select * from bbb;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbbb |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from bbb into outfile '/backup/5.sql';
Query OK, 2 rows affected (0.00 sec)

mysql> delete from bbb;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from bbb;
Empty set (0.00 sec)

mysql> load data local infile '/backup/5.sql' into table bbb character set utf8;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from bbb;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbbb |
+----+------+
2 rows in set (0.00 sec)

mysql导出表后找不到文本文件

原文链接：https://blog.csdn.net/weixin_39873177/article/details/113120905

使用INTO OUTFILE命令时,它会将数据导出到服务器的本地文件夹,而不是您正在执行查询的文件夹.

示例：您在计算机上(ip：192.168.0.100)并使用mysql命令连接到mysqlserver(ip：192.168.0.101)：

mysql -uuser -h192.168.0.101 -A database.
通过执行SELECT * INTO OUTFILE,文件保存在mysqlserver(ip：192.168.0.101)而不是计算机上(ip：192.168.0.100).

client：192.168.0.100

server：192.168.0.101

现在,您可以使用创建CSV文件的脚本(在您的cronjob中 – 您选择所有数据,生成文件并通过scp发送到其他服务器).

或者 – 您也可以在/ shared /上安装NFS,当您自动创建文件时,其他服务器也会使用它.

或者 – 您可以在第一台服务器上的bash脚本中运行mysql命令.

mysql -uroot test -B -e "select * from test.mytable;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/filename.csv

一：MYSQL如何导出文本文件（备份为csv、txt等，更加有用）

mysql 下导出为指定格式的数据的外部任意文件类型 mysql>

use dbname

Database Changed

select * from pollution into outfile 'G:\\arcgisworkspace\\zypdoc\\text.txt'; (只导出数据的，注意转义字符哦)

select * from pollution into outfile 'G:\\arcgisworkspace\\zypdoc\\text.csv' FIELDS TERMINATED BY '\,'; (输出格式控制)

结果为：

1,汽车尾气,200
2,建筑扬沙,180
3,汽车喷漆,160
4,燃煤,240
5,其它,80

知识补充：

一般形式：select [列名称] from tablename [where] into outfile '目标文件路径' [option]

其中option参数常用的5个选项

FIELDS TERMINATED BY ‘字符串’：设置字符串为字段的分割符，默认值为 \t；

FIELDS ENCLOSED BY ‘字符’：设置字符串括上char varchar text等字符型字段，默认值为无任何符号；

FIELDS OPTIONALLY ENCLOSED BY ‘字符’：设置字符串括上字段的值，默认值为无任何符号；

LINES STARTING BY ‘字符串’：设置每一行开头的字符，默认值为无任何字符；

FIELDS ESCAPED BY ‘字符’：设置转义字符，默认值为 \；

LINES TERMINATED BY ‘字符串’：设置每行结束符，默认值为 \n；

如：

select * from pollution into outfile 'G:\\arcgisworkspace\\zypdoc\\text2.csv'

FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"'
LINES STARTING BY '\>' TERMINATED BY '\r\n';

结果为：

>1,"汽车尾气","200"
>2,"建筑扬沙","180"
>3,"汽车喷漆","160"
>4,"燃煤","240"
>5,"其它","80"

1：mysqldump 下导出为指定格式的数据的外部任意文件类型
C:\Program Files\MySQL\MySQL Server 5.5\bin>
mysqldump -u root -ppassword -T /backup/ pollution "--fields-terminated-by=,"
(记住不要任何多余的空格，也不用转移字符;注意目标目录是文件夹，导出的文件名是表名，后缀是txt、sql文件)，/backup/为目标目录，pollution为数据库名

知识补充：
mysqldump -u root -p -T 目标目录 dbname tablename [option]
option 和上面的mysql的一样，只是更改为"--fields-terminated-by=字符"(不要任何多余的空格,就是指fields-terminated-by紧密相连的)

二：MYSQL如何导入文本文件（更加有用）

（1）mysql 下导入指定格式的数据的外部任意文件类型 mysql>

第一步：创建对应字段的数据表

create table csv_test2(
id int(8) primary key,
name varchar(64),
value int(32)
);

（2）插入：加入value的类型开始时设计错了，如何更改：ALTER table csrv_test2 MODIFY column value varchar(32);

（3）第二步：导入外部数据

LOAD DATA INFILE 'D:\\tjdata_metro\\test\\mysql_infile3.csv'
INTO TABLE csv_test2
FIELDS TERMINATED BY '\,'
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED by'\r\n'
ignore 1 lines
(id,name,value);

上面的 lines terminated by '\r\n' 是要求换行符号,为 windows的换行
上面的 ignore 1 lines是忽略第一行的标题行。

#######################MySQL表导入文件数据#########################
~# cat /tmp/11.txt 
1;aaa;22
2;bbb;33
3;ccc;100
~# cat mysql.sh 
#!/bin/bash
#load file data
source /etc/profile
dbname=ceshi
tablename=test
echo `date +F%s`,'start loading data.'
mysql ${dbname} -uroot -pXXXXXX -e "LOAD DATA INFILE '/tmp/11.txt' into table ${tablename} FIELDS TERMINATED BY ';';"
echo `date +%F%s`,'end loading data....'
exit
~# bash mysql.sh 
F1725949862,start loading data.
mysql: [Warning] Using a password on the command line interface can be insecure.
2024-09-101725949862,end loading data....
~# mysql ceshi -uroot -pXXXXXX -e 'desc test;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(10)  | NO   | PRI | NULL    |       |
| name  | char(11) | NO   |     | NULL    |       |
| value | int(255) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
~# mysql ceshi -uroot -XXXXXX -e 'select * from test;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-------+
| id | name | value |
+----+------+-------+
|  1 | aaa  |    22 |
|  2 | bbb  |    33 |
|  3 | ccc  |   100 |
+----+------+-------+

常见问题：

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement　

参考文章：原文链接：https://blog.csdn.net/shardy0/article/details/87911448
原因：出现这个问题的原因是mysql的secure_file_prive这个选项没有开启，或者这个选择了特定的文件路径，只有在这个文件路径下的文件才能导入导出mysql

解决方法
首先在mysql环境下查询secure_file_priv参数。

mysql中执行一下下边语句，查看 secure-file-priv

show variables like '%secure%'

secure_file_prive=null -- 限制mysqld 不允许导入导出

secure_file_priv=/tmp/ -- 限制mysqld的导入导出只能发生在/tmp/目录下

secure_file_priv=' ' --不对mysqld 的导入导出做限制

在my.cnf配置文件里面添加secure_file_prive参数，根据自己需要修改配置文件即可

[root@mysql conf]# more my.cnf |grep secure_file_priv
secure_file_priv = ''

然后重启mysql服务后进行导出验证

mysql> select * from xuexi into outfile '/root/test.txt';
ERROR 1 (HY000): Can't create/write to file '/root/test.txt' (Errcode: 13 - Permission denied)
mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | /tmp  |
+--------------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from xuexi into outfile '/tmp/text.txt';
Query OK, 2 rows affected (0.00 sec)

mysql> delete from xuexi;
Query OK, 2 rows affected (0.01 sec)

mysql> load data local infile '/tmp/text.txt' into table xuexi character  set utf8;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from xuexi;
+---------+--------------+--------------+-----------+------------+----------+--------------+
| vend_id | vend_name    | vend_address | vend_city | vend_state | vend_zip | vend_country |
+---------+--------------+--------------+-----------+------------+----------+--------------+
|       1 | after insert | NULL         | NULL      | NULL       | NULL     | NULL         |
|       2 | aaaaaaaa     | NULL         | NULL      | NULL       | NULL     | NULL         |
+---------+--------------+--------------+-----------+------------+----------+--------------+
2 rows in set (0.00 sec)