mysql的导入和导出数据,删除数据 - 暗雪星辰

时间：2024-02-19 20:10:55

mysql的导入和导出数据,删除数据

本文转自网络优秀的文章

详细可以参考https://www.cnblogs.com/Cherie/p/3309456.html

https://www.cnblogs.com/roverliang/p/6436140.html

https://www.cnblogs.com/mkfywj/p/5452045.html

https://www.cnblogs.com/waynechou/p/7794939.html

删除表内容，不删除表结构

truncate table 表名    --自动增长的列重置为1

delete from 表名        --自动增长的列不重置，继续延续以前的。

一、SELECT INTO…OUTFILE语法：

select * from Table into outfile \'/路径/文件名\'
fields terminated by \',\'
enclosed by \'"\'
lines terminated by \'\r\n\'

(1)路径目录必须有读写权限777
(2)文件名必须唯一
(3)fields terminated by \',\'必须存在，否则打开的文件的列在同一的单元格中出现
(4)我验证的表结构为gbk的，否则出现乱码

●  fields子句：在FIELDS子句中有三个亚子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，则这三个亚子句中至少要指定一个。

（1）TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY \',\'”指定了逗号作为两个字段值之间的标志。

（2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY \' " \'”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

（3）ESCAPED BY子句用来指定转义字符，例如，“ESCAPED BY \'*\'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。

●   LINES子句：在LINES子句中使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY \'?\'”表示一行以“?”作为结束标志。

二、LOAD DATA …INFILE语句是SELECT INTO…OUTFILE语句的补语，该语句可以将一个文件中的数据导入到数据库中。

> delete from e;
> load data infile "/data/mysql/e.sql" into table e fields terminated by \',\';
三、mysql导入excel

1、第一步我们得到了一个excel表，里面有很多需要我们导入的数据。

2、删除第1行"准考证号""XXX"....只保留我们需要的数据部分。

3、单击"文件"--"另存为"，类型选择为"CSV(逗号分隔)(*.csv)"，将excel表另存为csv文档。中间不管提示什么一律"是"就好了...

4、重点！另存为的slyar.csv是可以用记事本或者Editplus等文本编辑器打开的，我们打开来看一下。

可以看到其实csv文档就是txt(即你可以使用自己的txt然后换个后缀名就行)，只不过把各个字段之间用","分隔开了而已。既然它是txt文档，那么就涉及到了文件编码的问题！默认保存的文件编码是ANSI，如果你的数据库(数据表)使用UTF-8编码，那么一定要将这个csv文件另存为UTF-8格式！



5.进入phpMyAdmin创建一个表，新建字段，字段名与你要导入的excel表字段关联且顺序相同。太简单，不截图了。


6、在phpMyAdmin中打开你创建的表，在最上面单击"导入"。


7、"导入文件的格式"选择第二个"CSV 使用 LOAD DATA"。


8、"要导入的文件"选择你刚才另存为的csv文件。


9、"字段分隔符"更改为","，就是excel另存为默认选择的"(逗号分隔)"，你可以*选择分隔符，通常出现在你的excel表格数据里有","的情况下。


10、"字段名"是个高级应用，这里的字段名是你数据表里的字段名，它表示你要进行填充的字段，多个字段之间用","隔开。这里为空表示全部填充，并且按顺序填充，多余的数据不要。








10、检查没问题就可以按"执行"了。


11、"导入成功，执行了 1 个查询"。看到这个就表示成功了，如果导入不成功请从头到尾再仔细对照本文重做一次，如果还不行请往下看...








12、某些excel导出的数据可能会变得很奇怪，例如中间会有特殊字符神马神马的，总之如果这个方法不行的话，你还是可以去用我之前写的那个软件..


四、使用mysqldump备份和恢复数据库




mysqldump也是MySQL转储数据库常用的自带工具，mysqldump产生副本文件有两种情形：




1、文件是指定格式的文本文件 2、是可以执行的SQL脚本文件


使用方法有以下3种：


1、备份指定的多个数据库。


 mysqldump -u root -p --databases choose test > roverliang.sql




2、备份所有的数据库






mysqldump -u root -p --all-databases choose test > roverliang.sql






3、备份指定数据库中的某些表






mysqldump -u root -p  choose test > roverliang.sql






mysqldump 完整的参数是：






mysqldump -u USER_NAME -p PASSWORD [其他选项] DB [其他数据库库表]






--default-character-set： 设置字符集




--single-transaction : 将导出设置成事务




--no-data : 导出的SQL脚本中，将只包含创建表的create 语句。




--add-drop-table :导出的脚本中，包含 drop table if exists




--routines导出存储过程及函数




--events导出事件




--triggers导出触发器




导入mysqldump 的数据文件






一句命令就能搞定，在mysql终端下执行：
命令格式：
source FILE_PATH
source ~/demo.sql 


一、备份常用操作基本命令


1、备份命令mysqldump格式


   格式：mysqldump -h主机名  -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql 


2、备份MySQL数据库为带删除表的格式


备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。


mysqldump  --add-drop-table -uusername -ppassword -database databasename > backupfile.sql


3、直接将MySQL数据库压缩备份


mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz


4、备份MySQL数据库某个(些)表


mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql


5、同时备份多个MySQL数据库


mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql仅仅备6、仅备份份数据库结构


mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql


7、备份服务器上所有数据库


mysqldump –all-databases > allbackupfile.sql


8、还原MySQL数据库的命令


mysql -hhostname -uusername -ppassword databasename < backupfile.sql


9、还原压缩的MySQL数据库


gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename


10、将数据库转移到新服务器


mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename


11、--master-data 和--single-transaction


   在mysqldump中使用--master-data=2，会记录binlog文件和position的信息 。--single-transaction会将隔离级别设置成repeatable-commited


12、导入数据库


常用source命令，用use进入到某个数据库，mysql>source d:\test.sql，后面的参数为脚本文件。


13、查看binlog日志


查看binlog日志可用用命令 mysqlbinlog  binlog日志名称|more


 


14、general_log


General_log记录数据库的任何操作，查看general_log 的状态和位置可以用命令show variables like "general_log%"  ,开启general_log可以用命令set global general_log=on


二、增量备份


小量的数据库可以每天进行完整备份，因为这也用不了多少时间，但当数据库很大时，就不太可能每天进行一次完整备份了，这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog志。


 1、首先做一次完整备份：


mysqldump -h10.6.208.183 -utest2 -p123  -P3310 --single-transaction  --master-data=2  test>test.sql这时候就会得到一个全备文件test.sql


在sql文件中我们会看到：
-- CHANGE MASTER TO MASTER_LOG_FILE=\'bin-log.000002\', MASTER_LOG_POS=107;是指备份后所有的更改将会保存到bin-log.000002二进制文件中。
2、在test库的t_student表中增加两条记录，然后执行flush logs命令。这时将会产生一个新的二进制日志文件bin-log.000003，bin-log.000002则保存了全备过后的所有更改，既增加记录的操作也保存在了bin-log.00002中。


3、再在test库中的a表中增加两条记录，然后误删除t_student表和a表。a中增加记录的操作和删除表a和t_student的操作都记录在bin-log.000003中。


 三、恢复


1、首先导入全备数据


mysql -h10.6.208.183 -utest2 -p123  -P3310 < test.sql，也可以直接在mysql命令行下面用source导入


2、恢复bin-log.000002


   mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123  -P3310  


3、恢复部分 bin-log.000003


   在general_log中找到误删除的时间点，然后更加对应的时间点到bin-log.000003中找到相应的position点，需要恢复到误删除的前面一个position点。


可以用如下参数来控制binlog的区间


--start-position 开始点 --stop-position 结束点


--start-date 开始时间  --stop-date  结束时间


找到恢复点后，既可以开始恢复。


  mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123  -P3310 


 




 





        

        
    


相关文章