mysqldump 导出文本文件

1、创建相关账号

create user backup@'%' identified with mysql_native_password by '123456';
grant select,reload,process,lock tables,replication client,replication_slave_admin,show view,show_routine,trigger,event,file on *.* to backup@'%';
flush privileges;


select:			备份表数据
show view:   	备份试图
trigger:        备份触发器
lock tables:    没有使用--single-transaction
process:		没有使用 --no-tablespaces
reload：		备份前刷新mysql日志文件
super、replication_client：使用--master-data记录点位信息，需要执行show master status
super、replication_slave_admin: 使用了--dump-slave （要执行 stop slave sql_thread）
show_routine:     备份存储过程#8.0.20 新增
event： event事件
file:   导出文本文件



mysql> create user apply_log@'%' identified by '123456';
mysql> grant super,system_variables_admin,drop,create,insert,alter,file on *.* to apply_log@'%';
mysql> flush privileges;
mysql> exit

2、导出文本数据

-T, --tab=name      Create tab-separated textfile for each table to given path. (Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

--限制：mysqldump和mysql server必须在同一台服务器


--fields-terminated-by=str：隔开字段的字符，默认为tab制表符。
--fields-enclosed-by=char：包裹字段的符号（包裹所有字段），默认没有符号。
--fields-optionally-enclosed-by=char：包裹字段的符号（包裹除数字类型之外的所有字段），默认没有符号。
--fields-escaped-by=char：指定字符对特殊字符进行转义，例如--fields-enclosed-by指定为双引号，而字段内容包含双引号，就需要一个字符如#或其他，对字段内的双引号进行转义。
--lines-terminated-by=str：一行的终止，默认为换行符。



--导出文本文件
mysqldump -ubackup -p123456 -h192.168.100.82 -P3306 --set-gtid-purged=OFF  -q --single-transaction --tab=/data/mysql/3306/tmp/ test t1 --fields-terminated-by=, --fields-enclosed-by='"' 




[root@mysql2 ~]# tree /data/mysql/3306/tmp/                       
/data/mysql/3306/tmp/
├── t1.sql
└── t1.txt

0 directories, 2 files
[root@mysql2 ~]# egrep -v '^$|^/|^--' /data/mysql/3306/tmp/t1.sql 
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `row_create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `row_lastupdate_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
[root@mysql2 ~]# cat /data/mysql/3306/tmp/t1.txt 
"1","2022-12-11 20:31:24","2022-12-11 20:31:24"
"2","2022-12-11 20:31:25","2022-12-11 20:31:25"

3、mysqlimport导入文本文件

--新建临时表t2 
mysql> use test
mysql> create table t2 like t1;

--将t1.txt 复制一份t2.txt ，或者重命名为t2 
[root@mysql2 ~]# cd /data/mysql/3306/tmp/
[root@mysql2 tmp]# cp -a t1.txt t2.txt

mysqlimport -uapply_log -p123456 -h192.168.100.82 -P3306 --fields-terminated-by=',' --fields-enclosed-by='"'  test /data/mysql/3306/tmp/t2.txt

--load data 参数

character set utf8	字符集utf8，防止中文乱码，需要放在fields前面，否则报错
fields	域，后面常用字段有terminated/optionally/escaped
terminated by 'string'	设置字段数据之间的分隔符，如最常用的分隔符0x0f
optionally enclosed by 'char'	设置字段非数值的数据，使用什么符号引起，如英文双引号"
escaped by 'char'	字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"
lines	设置每条记录的开头starting和结尾字符terminated
lines starting by 'char'	设置每条记录的开头字符，默认空字符串''
lines terminated by 'char'	设置每条记录的结尾字符默认换行符'\n'



--开启客户端导入参数
mysql> set global local_infile=on;


--导入数据，登录的时候也需要开启 --local-infile=1
	
mysql -uapply_log -p123456 -h192.168.100.82 -P3306 --local-infile=1


load data local infile '/data/mysql/3306/tmp/t2.txt' into table t2 fields
terminated by ','  enclosed by '"' lines terminated by '\n'
(@id, @row_create_time, @row_lastupdate_time) set row_create_time=date_format(@row_create_time, '%Y-%m-%d %H:%i:%s'), row_lastupdate_time=date_format(@row_lastupdate_time, '%Y-%m-%d %H:%i:%s');	

4、select info outfile

character set utf8	字符集utf8，防止中文乱码，需要放在fields前面，否则报错
fields	域，后面常用字段有terminated/optionally/escaped
terminated by 'string'	设置字段数据之间的分隔符，如最常用的分隔符0x0f
optionally enclosed by 'char'	设置字段非数值的数据，使用什么符号引起，如英文双引号"
escaped by 'char'	字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"
lines	设置每条记录的开头starting和结尾字符terminated
lines starting by 'char'	设置每条记录的开头字符，默认空字符串''
lines terminated by 'char'	设置每条记录的结尾字符默认换行符'\n'




mysql -ubackup -p123456 -h192.168.100.82 -P3306 

select * from test.t2 into outfile '/data/mysql/3306/tmp/t2.txt' character set utf8 fields terminated by ',' optionally enclosed by '"' escaped by '"'  lines terminated by '\n';

--查看导出的文件

[root@mysql2 ~]# cat /data/mysql/3306/tmp/t2.txt
1,"2022-12-11 20:31:24","2022-12-11 20:31:24"
2,"2022-12-11 20:31:25","2022-12-11 20:31:25"


--导入数据
mysql -uapply_log -p123456 -h192.168.100.82 -P3306 --local-infile=1


load data local infile '/data/mysql/3306/tmp/t2.txt' into table t2 fields
terminated by ','  enclosed by '"' lines terminated by '\n'
(@id, @row_create_time, @row_lastupdate_time) set row_create_time=date_format(@row_create_time, '%Y-%m-%d %H:%i:%s'), row_lastupdate_time=date_format(@row_lastupdate_time, '%Y-%m-%d %H:%i:%s');



--csv文件也可以使用如下方法导入，导入的时候注意编码