mysql导出csv/sql/newTable/txt的方法,mysql的导入txt/sql方法...mysql备份恢复mysqlhotcopy、二进制日志binlog、直接备份文件、备份策略、灾难恢复.....................................................

时间：2023-09-16 08:36

-->

mysql备份表结构和数据

方法一、 Create table new_table_nam备份到新表：
MYSQL不支持: Select * Into new_table_name from old_table_name;
替代方法: create table tb2 select c1,c2,c3 from tb1 group by c1,c2,c3;

方法二、insert into newtable select * from oldtable;

1. 语法介绍
有三张表a、b、c，现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况，可以使用如下的语句来实现：

INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name

上面的语句比较适合两个表的数据互插，如果多个表就不适应了。对于多个表，可以先将需要查询的字段JOIN起来，然后组成一个视图后再SELECT FROM就可以了：

INSERT INTO a (field1,field2) SELECT * FROM(SELECT b.f1,c.f2 FROM b JOIN c) AS tb

其中f1是表b的字段，f2是表c的字段，通过JOIN查询就将分别来自表b和表c的字段进行了组合，然后再通过SELECT嵌套查询插入到表a中，这样就满足了这个场景了，如果需要不止2个表，那么可以多个JOIN的形式来组合字段。

2. 语法错误注意
需要注意的是嵌套查询部分最后一定要有设置表别名，如下：

SELECT * FROM (SELECT f1,f2 FROM b JOIN c) AS tb

即最后的AS tb是必须的（tb这个名称可以随意取），即指定一个别名。每个派生出来的新表都必须指定别名，否则在mysql中会报如下错误：

方法三、mysqldump批量导出表结构（数据）到sql：

1.先备份表结构和数据

仅导出结构，不导出数据：

1、导出數據库為dbname的表结构
mysqldump -h主机地址 -u用户名 -p密码 -d dbname >db.sql;

2、导出數據库為dbname某张表(test)结构
mysqldump -h主机地址 -u用户名 -p密码 -d dbname test>db.sql;

导出结构＋数据：
3、导出數據库為dbname所有表结构及表數據（不加-d）
mysqldump -h主机地址 -u用户名 -p密码 dbname >db.sql;

4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
mysqldump -h主机地址 -u用户名 -p密码 dbname test>db.sql;

　 5.导出数据中dbname多张表（test1,test2,test3）结构及表数据用用空格隔开

mysqldump -h主机地址 -u用户名 -p密码 dbname test1 test2 test3>db.sql;

方法四、在本地直接导出服务器数据：

mysql DB_name -h10.36.10.11 -utest -p -e "select * from TABLE_name where acti_type <> '无效'; order by acti_date desc" > ./test.txt

-N 即可 -N, --skip-column-names.Don't write column names in results.

方法五、 select into outfile导出到文件：

BACKUP TABLE 语法其实和 mysqlhotcopy 的工作原理差不多，都是锁表，然后拷贝数据文件。它能实现在线备份，但是效果不理想，因此不推荐使用。它只拷贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。
例子：
BACK TABLE tbl_name TO '/tmp/db_name/';
注意，必须要有 FILE 权限才能执行本SQL，并且目录 /tmp/db_name/ 必须能被 mysqld 用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。
SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件，可以自定义字段间隔的方式，方便处理这些数据。
例子：
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
注意，必须要有 FILE 权限才能执行本SQL，并且文件 /tmp/db_name/tbl_name.txt 必须能被 mysqld 用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。

恢复
用 BACKUP TABLE 方法备份出来的文件，可以运行 RESTORE TABLE 语句来恢复数据表。
例子：
RESTORE TABLE FROM '/tmp/db_name/';
权限要求类似上面所述。
用 SELECT INTO OUTFILE 方法备份出来的文件，可以运行 LOAD DATA INFILE 语句来恢复数据表。
例子：
LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;
权限要求类似上面所述。倒入数据之前，数据表要已经存在才行。如果担心数据会发生重复，可以增加 REPLACE 关键字来替换已有记录或者用 IGNORE 关键字来忽略他们。

使用into outfile 和 load data infile导入导出备份数据，这种方法的好处是，导出的数据可以自己规定格式，并且导出的是纯数据，不存在建表信息，你可以直接导入另外一个同数据库的不同表中，相对于mysqldump比较灵活机动。
我们来看下面的例子：
（1）下面的mysql命令是把select的mytable表中的数据导出到/home/db_bak2012文件。
select * from mytable where status!=0 and name!='' into outfile '/home/db_bak2012'
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;
导入刚才备份的数据，可以使用load file方法，下面的mysql命令，把导出的数据导入了mytable_bak的表中：
load data infile '/home/db_bak2012' into table mytable_bak
fields terminated by '|' enclosed by '"' lines terminated by '\r\n' ;

select * into outfile './bestlovesky.xls' from bestlovesky where 1 order by id desc;

由MySQL服务器直接创建输出文件，这样的文件名应指定您希望在服务器主机上的文件将被写入。没有本地版本的LOAD DATA LOCAL版本的类似的语句。
必须要有MySQL的FILE权限才能执行的SELECT... INTO语句。
输出文件必须不存在。这可以防止MySQL从会破坏文件可能重要。
应该在服务器主机上或一些方法来检索文件从该主机的登录帐户。否则SELECT... INTO OUTFILE可能是没有价值的。
在Unix下，创造了世界可读的文件和所拥有的MySQL服务器。这意味着虽然就可以读取该文件，但是不能够删除它。