导出数据
使用 SELECT ... INTO OUTFILE 语句导出数据
实例:
将数据表 t_user 数据导出到 E:/mysql/backup/user.txt文件中:
SELECT *FROM loaderman.t_userINTO OUTFILE 'E:/mysql/backup/user.txt';
将数据表 t_user 数据导出到 E:/mysql/backup/user.xls文件中:
SELECT *FROM loaderman.t_userINTO OUTFILE 'E:/mysql/backup/user.xls';
将数据表 t_user 数据导出成 CSV 格式:
select *from t_userinto outfile 'E:/mysql/backup/user.txt' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';
生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用:
SELECT user_name, user_ageINTO OUTFILE 'E:/mysql/backup/user4.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'FROM t_user;
SELECT ... INTO OUTFILE 语句有以下属性:
LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT...INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。
SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则SELECT...INTO OUTFILE 不会起任何作用。
在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。这意味着,虽然你就可以读取该文件,但可能无法将其删除。
导出数据库作为原始数据
使用 mysqldump 命令备份一个数据库的语法格式如下:
mysqldump -u username -p dbname [tbname ...]> filename.sql
对上述语法参数说明如下:
username:表示用户名称;
dbname:表示需要备份的数据库名称;
tbname:表示数据库中需要备份的数据表,可以指定多个数据表。省略该参数时,会备份整个数据库;
右箭头“>”:用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件;
filename.sql:表示备份文件的名称,文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为
.sql
的文件。
注意:mysqldump 命令备份的文件并非一定要求后缀名为.sql
,备份成其他格式的文件也是可以的。例如,后缀名为 .txt
的文件。通常情况下,建议备份成后缀名为 .sql
的文件。因为,后缀名为.sql
的文件给人第一感觉就是与数据库有关的文件。
mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个SQL 脚本,其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 导出数据需要使用
命令终端输入:
mysqldump -h 127.0.0.1 -u root -p loaderman t_user > E:/mysql/backup/user_bak.sql
下面使用 root 用户备份所有数据库。命令如下:
mysqldump -u root -p --all-databases > C:\all.sql
执行完后,可以在 C:\
下面看到名为 all.sql 的文件,这个文件中存储着所有数据库的信息。
导入数据
第一种方法:
mysql 命令语法格式如下:
mysql -u username -P [dbname] filename
其中:
username 表示用户名称;
dbname 表示数据库名称,该参数是可选参数。如果 filename.sql 文件为 mysqldump 命令创建的包含创建数据库语句的文件,则执行时不需要指定数据库名。如果指定的数据库名不存在将会报错;
filename.sql 表示备份文件的名称。
下面使用 root 用户恢复所有数据库,命令如下:
mysql -u root -p < C:\all.sql
mysql -h 数据库地址 -u 用户名 -p -P 数据库端口号 要还原到的数据库< 备份的数据库
mysql -h 192.168.1.103 -u root -p -P 3306 你的DbName< sql_bak.sql
第二种方法:
1.登录MySQL
mysql -u root -p 登录密码
2.导入数据
use 要还原数据库的名字;source 数据库的备份文件;exit;
遇到的问题
问题一:MYSQL导出数据出现The MySQL server is running with the --secure-file-priv option
解决:应该是mysql设置的权限问题,输入
show variables like '%secure%';
查看secure-file-priv当前的值是什么。默认有可能是NULL就代表禁止导出,所以需要设置一下:默认有可能是NULL就代表禁止导出,所以需要设置一下
找到mysql安装路径下的my.ini文件,设置一下路径:
[client]# 设置mysql客户端默认字符集default-character-set=utf8 [mysqld]# 设置3306端口port = 3306# 设置mysql的安装目录basedir=C:\develop\mysql-8.0.20-winx64# 设置 mysql数据库的数据的存放目录,MySQL 8+ 不需要以下配置,系统自己生成即可,否则有可能报错# datadir=C:\\web\\sqldata# 允许最大连接数max_connections=20# 服务端使用的字符集默认为8比特编码的latin1字符集character-set-server=utf8# 创建新表时将使用的默认存储引擎default-storage-engine=INNODB#设置secure-file-privsecure_file_priv=E:/mysql/backup
重启mysql服务即可
问题二:mysqldump找不到命令
解决:配置环境变量
问题三:还原数据出现错误Unknown collation: 'utf8mb4_0900_ai_ci'等信息
报错原因:生成转储文件的数据库版本为8.0,要导入sql文件的数据库版本为5.6,因为是高版本导入到低版本,引起1273错误
解决办法:
打开sql文件,将文件中的所有utf8mb4_0900_ai_ci替换为utf8_general_ciutf8mb4替换为utf8
可视化软件DataGrip备份和还原
选择要备份的数据库
选择路径等,点击run 即可
还原,选择数据库,右击如下,运行SQL脚本即可:
【MySQL】概述和环境搭建
【MySQL】创建、删除和选择数据库
【MySQL】数据类型
【MySQL】创建、修改和删除数据表
【MySQL】查询数据和where子句
【MySQL】运算符
【MySQL】插入、更新和删除数据
【MySQL】模糊匹配查询LIKE子句和UNION 连接操作符
【MySQL】表连接和NULL 值处理
【MySQL】正则表达式和事务
【MySQL】排序和分组
【MySQL】函数
【MySQL】索引、临时表和复制表
【MySQL】处理重复数据和SQL 注入
【MySQL】元数据和序列
【MySQL】视图
【MySQL】触发器
【MySQL】用户权限表
【MySQL】用户管理
【MySQL】处理无效数据值
【MySQL】存储
【MySQL】事件
【MySQL】密码管理
【MySQL】字符集