mysql 导出数据 csv文件_Mysql数据备份导出csv文件

Mysql数据库备份

1 . 备份数据库

//远程登陆服务器

ssh root@公网ip

//mysqldump -u数据库名 -p [-h host] --databases 数据库名 > xxx.sql

//备份数据库test到xxx.sql中

mysqldump -uroot -p --databases test > xxx.sql

备份多个数据库

//把数据库dbname1，dbname2 备份到db.sql中

mysqldump -u root [-h host] -p --databases dbname1, dbname2 > db.sql

备份所有数据库

//备份所有数据库到db.sql中

mysqldump -u root [-h host] -p --all-databases > db.sql

2 . 备份表数据

//备份数据库dbname中表tbname1,tbname2到tabledb.sql中

mysqldump -u root -p [-h host] dbname tbname1, tbname2 > tabledb.sql

//备份数据库javacode中testcode表的结构和数据,

//备份地址testdb.sql

mysqldump -u root -p javacode testcode > testdb.sql

还原数据

// 登陆到mysql数据库

//执行source命令前需要先选择数据库, mysql执行sql语句

source /opt/db.sql

导出数据到csv文件

1 select ... into outfile

//导出数据到文件

select * from testtable into outfile '/opt/test.csv';

报错：目录问题

mysql> select * from testcode into outfile '/download/test.csv';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看 secure-file-priv 当前的值是什么:

mysql> show variables like '%secure%';

+--------------------------+-----------------------+

| Variable_name | Value |

+--------------------------+-----------------------+

| require_secure_transport | OFF |

| secure_auth | ON |

| secure_file_priv | /var/lib/mysql-files/ |

+--------------------------+-----------------------+

3 rows in set (0.02 sec)

secure_file_priv 指向的目录就是导出文件的目录：/var/lib/mysql-files/

正确方式导出

//登陆mysql数据库

//执行sql，导出10条数据到test.csv文件

select * from testtable limit 10 into outfile '/var/lib/mysql-files/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

fields terminated by :描述字段的分隔符，默认情况下是tab字符(\t)

optionally enclosed by :描述的是字段的括起字符。

escaped by :描述的转义字符。默认的是反斜杠(backslash：\ )

lines terminated by : 行与行之间的分隔