mysql数据导入导出

mysql数据导入导出

mysqldump

• 本地需安装mysql客户端，适用于操作本地数据库和远程数据库

# 命令行连接：

mysql -h172.30.1.46 -P3306 -uroot -proot

# 备份指定数据库所有表结构及数据：(--skip-lock-tables跳过锁定表备份)

mysqldump -h172.30.1.46 -P3306 -uroot -p123456 ecas --skip-lock-tables --skip-add-locks > /usr/local/dmp/test.dump

# 备份指定数据库的指定表结构及数据：

mysqldump  -h172.30.1.46 -P3306  -uroot -p123456 ecas table1 table2 table3 --skip-lock-tables --skip-add-locks > /usr/local/dmp/test.dump

# 导入指定数据库

mysql -h172.30.1.46 -P3306  -uroot -p123456 ecas2 < /usr/local/dmp/test.dump

# -t: 只导出数据，而不添加CREATE TABLE 语句; -w 导出数据条件; --skip-lock-tables:跳过锁定所有表; --skip-add-locks: 去掉导出数据前后的LOCK和UNLOCK; --skip-comments: 跳过附加注释信息; --compact: 导出更少的输出信息(用于调试)。去掉注释和头尾等结构; --log-error:附加警告和错误信息到给定文件(文件不存在会自动创建);

mysqldump -t ecas -h172.30.1.46 -P3306 -uroot -proot  aitacen -w "ech_date=date_format('20180717','%y%m%d')" --skip-lock-tables --skip-add-locks --compact --log-error=/usr/local/databack/mysqldump_error_log.err> /usr/local/databack/test.dump;

mysql –> csv

• 本地需安装mysql客户端，只适用于操作本地数据库

# 登陆mysql：

mysql -uroot -proot

# 将数据导出到csv，关于为什么导出路径指定为/var/lib/mysql-files/，下文做解释
# csv格式参数介绍：
# fields terminated by ---- 字段终止字符 
# optionally enclosed by ---- 封套符 
# lines terminated by ---- 行终止符

select * from aicmt into outfile '/var/lib/mysql-files/test.csv' fields terminated by ',' optionally enclosed by '' lines terminated by '\n';

# 删除原表数据

delete from aicmt;

# 将csv中数据导入表中

load data local infile '/var/lib/mysql-files/test.csv' into table aicmt fields terminated by ',' lines terminated by '\n';

• 执行导出导入可能遇到的问题

报错：

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

解决1：

mysql -uroot -proot

show variables like '%secure%';

返回结果有：secure_file_priv=xxxxxx

导出的csv文件路径必须是secure_file_priv指定的路径

解决2：

修改MySQL安装路径下的my.cnf(vi /etc/my.cnf)，在末尾添加“secure-file-priv=路径名”这一行，重启mysql（service mysqld restart）

将要通过load data infile的文件放在上面所定义的路径下进行操作即可

命令行操作mysql数据库

• mysql -h172.30.1.46 -P3306 -uroot -proot -e “select * from aicmt” ecas;

shell脚本中操作mysql，并处理返回值

方法一：（执行sql，并获取返回值）

value=$(mysql -h172.30.1.46 -P3306 -uroot -proot -e "select count(1) from aicmt;" ecas);
或者
value=`mysql -h172.30.1.46 -P3306 -uroot -proot -e "select count(1) from aicmt;" ecas`;

# 对返回值进行加工
# mysql -h172.30.1.46 -P3306 -uroot -proot -e "select count(1) from aicmt" ecas，执行结果返回两行，去第二行的第一个字段值
value=`mysql -h172.30.1.46 -P3306 -uroot -proot -e "select count(1) from aicmt" ecas|awk '{if (NR>1){print $1}}'`;

方法二：（执行sql，无法获取返回值）

#!/bin/bash  
mysql -h172.30.1.46 -P3306 -uroot -hroot  <<EOF  
use ecas;
select * from aicmt;    
EOF