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 : 行与行之间的分隔
