1、mysqldump and mysql
#1.导出导入指定表的数据
mysqldump -t 数据库名 -u用户名 -p密码 -h地址 -P端口 --tables 表名1 表名2 > /data/db_data.sql
mysqldump -t 数据库名 -u用户名 -p密码 -h地址 -P端口 --hex-blob --tables 表名1 表名2 > /data/db_data.sql
mysqldump -t -c --compact enterprise01133 -uroot -pCsdn@123456 -h192.168.168.100 -P3306 --tables xxxx> /data/xxxx.sql
mysqldump -t -c --compact enterprise01133 -uroot -pCsdn@123456 -h192.168.168.100 -P3306 > /data/xxxx.sql
-t 不要create和drop表的语句
-c insert语句带有字段名
--compact 不要导出的 所有假注释信息
mysql -u用户名 -p密码 -h地址 -P端口 数据库名 < /data/db_data.sql
或mysql> source /data/db_data.sql
#2.导出指定表的结构
mysqldump -d 数据库名 -u用户名 -p密码 -h地址 -P端口 --tables 表名1 表名2 > /data/db_structrue.sql
#3.导出导入指定表的数据及结构
mysqldump 数据库名 -u用户名 -p密码 -h地址 -P端口 --tables 表名1 表名2 > /data/db_structrue_data.sql
mysql -u用户名 -p密码 -h地址 -P端口 数据库名 < /data/db_structrue_data.sql
或mysql> source /data/db_structrue_data.sql
#4.导出导入指定数据库的数据及结构
mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 > /data/xxx.sql
mysqldump --no-data -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名1 数据库名2 > /data/xxx.sql
mysql -u用户名 -p密码 -h地址 -P端口 < /data/xxx.sql
或mysql> source /data/xxx.sql
#1、可使用管道符"|"将mysqldump和mysql连接组合使用
mysqldump 源数据库 -u用户名 -p密码 | mysql 目标数据库 -u用户名 -p密码
#2、如果不在同一个mysql服务器上
mysqldump 源数据库 -u用户名 -p密码 | mysql -h主机名 -P端口 目标数据库 -u用户名 -p密码
ls | xargs sed -i 's/^\/\*!.*//g'
删除导出sql语句中的假注释信息 也可直接使用--compact参数导出无此类信息的文件
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
2、使用gzip、bzip2备份与恢复
备份并用gzip压缩：
mysqldump 布拉布拉布拉 | gzip > outputfile_sql.gz
从gzip备份恢复：
gunzip < outputfile_sql.gz | mysql 布拉布拉布拉
备份并用bzip压缩：
mysqldump 布拉布拉布拉 | bzip2 > outputfile_sql.bz2
从bzip2备份恢复：
bunzip2 < outputfile_sql.bz2 | mysql 布拉布拉布拉
2、outfile and infile
帮助文档：help mysql
https://dev.mysql.com/doc/refman/5.6/en/select-into.html
https://dev.mysql.com/doc/refman/5.6/en/load-data.html
#1、SELECT * INTO OUTFILE 备份：
SELECT * INTO OUTFILE '/tmp/xxx.dbf' CHARACTER SET utf8 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' LINES TERMINATED BY '\n' FROM 表名;
#2、LOAD DATA INFILE 恢复：
LOAD DATA INFILE '/tmp/xxx.dbf' INTO TABLE 数据库名.表名 CHARSET utf8 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'C:\\\\tmp\\\\xxx.dbf' INTO TABLE 数据库名.表名 CHARSET utf8 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ',' LINES TERMINATED BY '\n';
报错解决方案：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%';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
2 rows in set (0.00 sec)
chmod 777 /xx/yy
或者：
如果系统提示：secure-file-priv问题，说明配置没有允许进行文件的导入导出。
需要在配置文件里配置好这个配置项：secure-file-priv = 数据导入导出路径/不指定值（重启MySQL生效）
select */字段列表 into outfile 文件所有路径 from 数据源; -- 前提外部文件不存在
-----------------------------------------------------------------------------
SELECT * FROM xxxxx
INTO OUTFILE '/data/mysql/xxx.dbf'
CHARACTER SET utf8
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY ''
LINES
TERMINATED BY '\n';
-------------------------------------------------------------------------------
select */字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源;
FIELDS: 字段处理
OPTIONALLY ENCLOSED BY: 字段使用什么内容包裹, 默认什么都没有
TERMINATED BY: 字段以什么结束,字段间分割符, 默认是'\t',tab键
ESCAPED BY: 特殊符号用什么方式处理,默认是'\\',使用反斜杠转义
LINES: 行处理
STARTING BY: 行开始符号, 默认是'',空字符串
TERMINATED BY: 行结束符号,默认是'\r\n',换行符
LOAD DATA LOCAL INFILE '/data/mysql/xxx.dbf'
INTO TABLE xxxxx
charset utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' starting by 'START:'
[(字段列表)]; # 如果是部分表字段，那么必须将字段列表放到最后
---------------------------------------------------------
load data infile '/data/mysql/xxx.dbf'
into table xxxxx
fields -- 字段处理
TERMINATED BY ','
enclosed by '"' -- 数据使用双引号包裹
terminated by '|' -- 使用竖线分隔字段数据
lines-- 行处理
starting by 'START:'
[(字段列表)]; # 如果是部分表字段，那么必须将字段列表放到最后
load data infile 'D:/xxxx.csv' into table 表名; # 有可能因为字符集出现问题
load data infile 'D:/xxxx.csv' into table 表名 charset utf8;
load data infile 'D:/xxxx.csv' into table t_40 charset utf8 fields terminated by '-' enclosed by '"' lines starting by 'START:' (name,price,width,height) ;
–secure-file-priv option so it cannot execute this statement
目录下权限不足
chmod 777 /xx/yy
文件备份：直接对数据表进行文件保留，属于物理备份
- 文件备份操作简单，直接将数据表（或者数据库文件夹）进行保存迁移
- MySQL中不同表存储引擎产生的文件不一致，保存手段也不一致
- InnoDB：表结构文件在ibd文件中，数据和索引存储在外部统一的ibdata文件中(Mysql7以前话是frm后缀)
- MyIsam：每张表的数据、结构和索引都是独立文件，直接找到三个文件迁移即可
步骤
1、设定备份时间节点
2、设定备份文件存储位置
3、确定备份表的存储引擎
4、根据节点进行文件备份：将文件转移（复制）到其他存储位置
示例
1、MyIsam表的文件备份：找到三个文件，复制迁移
- sdi：表结构文件
- MYI：索引文件
- MYD：数据文件
2、InnoDB表的文件备份：找到两个文件，复制迁移
- ibd：表结构文件
- ibdata：所有InnoDB数据文件
小结
1、文件备份是一种简单粗暴的数据备份方式，是直接将数据文件打包管理的方式
- MyIsam存储引擎相对比较适合文件备份，因为MyIsam存储引擎表文件独立，不关联其他表
- InnoDB不适合文件备份，因为不管是备份一张表还是全部数据表，都需要备份整个数据存储文件ibdata（适合整库迁移）
2、文件备份方式非常占用磁盘空间
4、文件还原
目标：了解文件还原的概念，理解文件还原的方法
概念
文件还原：利用备份的文件，替换出现问题的文件，还原到备份前的良好状态
-
直接将备份的文件放到对应的位置即可
-
文件还原影响
- MyIsam存储引擎：单表备份，单表还原，不影响其他任何数据
- InnoDB存储引擎：单表结构，整库数据，只适合整库备份还原，否则会影响其他InnoDB存储表
步骤
1、找到出问题的数据文件
- MyIsam：表结构、表数据、表索引三个文件（删掉即可）
- InnoDB：表结构、整库数据表ibdata（删掉）
2、将备份数据放到相应删除的文件位置
示例
1、MyIsam数据备份表的数据迁移：单表迁移到不同数据库
2、InnoDB数据备份完成整个数据库的迁移（包括数据库用户信息）
小结
1、文件备份的还原通常使用较少
- 数据备份占用空间大，这种备份方式就少
- InnoDB的备份是针对整个数据库里所有InnoDB表，还原会覆盖掉所有不需要还原的表
2、文件备份与还原通常可以在数据迁移的情况下使用
- MyIsam：独立表的迁移（现在很少用，myisam很少用）
- InnoDB：整个数据库的迁移
5、SQL备份
目标：了解SQL备份的概念，掌握SQL备份的语法和原理
SQL备份：将数据库的数据以SQL指令的形式保存到文件当中，属于逻辑备份
-
SQL备份是利用Mysqldump.exe客户端实现备份
-
SQL备份是将备份目标（数据表）以SQL指令形式，从表的结构、数据和其他信息保存到文件
mysqldump.exe -h -P -u -p [备份选项] 数据库名字 [数据表列表] > SQL文件路径 -
备份选项很多，常见的主要是数据库的备份多少
- 全库备份：
--all-databases所有数据库的所有表，也不需要指定数据库名字 - 单库备份：
[--databases] 数据库指定数据库里的所有表（后面不要给表名） - 部分表（单表）备份：
数据库名字 表1[ 表2...表N]
- 全库备份：
SQL还原：在需要用到SQL备份数据时，想办法让SQL执行，从而实现备份数据的还原
SQL还原可以使用Mysql.exe进行操作
mysql.exe -h -P -u -p [数据库名字] < SQL文件路径
SQL还原可以在进入到数据库之后利用SQL指令还原
source SQL文件路径;
SELECT * FROM xxx INTO OUTFILE '/home/michael/xxx.dbf' CHARACTER SET utf8 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'D:\\\\data\\\\xxx.dbf' INTO TABLE xxx charset utf8 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';
LOAD DATA INFILE 'hdfs://michael:michael@192.168.168.102:50070/michael/user/hive/warehouse/student/*' INTO TABLE student DATA_FORMAT 3 FIELDS TERMINATED BY '|' PRESERVE BLANKS AUTOFILL ;
使用sqluldr从oracle导出文件备份
sqluldr264 user=用户名/密码@127.0.0.1:1521/数据库名 query="select * from 表名" Field="|" head=no file=D:\data\文件名.dbf
mysql使用load命令从ftp服务器上导入数据到mysql或gbase
LOAD DATA INFILE 'ftp://用户名:密码@10.85.60.216:21/文件名.dbf' INTO TABLE 表名 DATA_FORMAT 3 FIELDS AUTOFILL TERMINATED BY '|' PRESERVE BLANKS ;
若报错
Loading local data is disabled; this must be enabled on both the client and server sides
解决方法：
set global local_infile = 1;
1184
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
