数据库纯文本导出自动化脚本
mysqldump支持按照表,分隔符纯文本导出。
https://dev.mysql.com/doc/refman/5.7/en/mysqldump-delimited-text.html
如果加上-t参数的话,只会导出表数据(txt文件),不导出表结构(sql文件)。
导出目录不要是/root下的目录,否则会因为txt文件是mysql用户写的导致权限报错。
先导出库的所有表名
mysql -uhive -pHive@1234 -Dmetastore -sN -e "show tables" > tables.txt
dump脚本
#!/usr/bin/env bash
# table_dump.sh
db_user=hive
db_pass=Hive@1234
db_name=metastore
conf_dir=/tmp/hive_meta_dump
table_file=tables.txt
for table in `cat ${conf_dir}/${table_file}`
do
tb_name=${table}
echo "------------------------------------------------"
now=`date '+%Y-%m-%d %H:%M:%S'`
echo ${now}
printf "%-30s %-10s\n" ${tb_name} begin
tb_name=${table}
tb_output=${conf_dir}/${db_name}
cmd=`mysqldump -u${db_user} -p${db_pass} -T ${tb_output} --fields-terminated-by=',' --fields-enclosed-by='"' --no-tablespaces ${db_name} ${tb_name}`
now=`date '+%Y-%m-%d %H:%M:%S'`
echo ${now}
printf "%-30s %-10s\n" ${tb_name} end
done
dump启动脚本
#!/bin/bash
# dump_start.sh
nohup /bin/bash table_dump.sh >> dump.log 2>&1 &
dump停止脚本
#!/bin/bash
# dump_stop.sh
ps -ef | grep "/bin/bash table_dump.sh" | grep -v grep | awk '{print $2}' | xargs kill -9
数据库纯文本导出报错
1
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
增加--no-tablespaces参数
mysqldump -u${db_user} -p${db_pass} -T ${tb_output} --fields-terminated-by=',' --fields-enclosed-by='"' --no-tablespaces ${db_name} ${tb_name}
2
mysqldump: Got error: 1045: Access denied for user ‘hive’@’%’ (using password: YES) when executing ‘SELECT INTO OUTFILE’
增加FILE ON *.*权限,注意需要为*.*,如果是XXDB.*会报语法错误
GRANT FILE ON *.* TO 'hive'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'hive'@'%';
3
mysqldump: Got error: 1290: The MySQL server is running with the –secure-file-priv option so it cannot execute this statement when executing ‘SELECT INTO OUTFILE’
在/etc/my.cnf中修改secure_file_priv参数配置(需要重启mysql)
show variables like "%secure_file_priv%";
默认值是/var/lib/mysql-files/
vi /etc/my.cnf
[mysqld]
secure_file_priv = ''
systemctl restart mysqld
导出目录不要是/root下的目录,否则会因为txt文件是mysql用户写的导致权限报错。
可以将导出路径指定到/tmp下
mysqldump -uhive -pHive@1234 -T /tmp/hive_meta_dump/metastore --fields-terminated-by=',' --fields-enclosed-by='"' --no-tablespaces metastore DBS
4
mysqldump: Got error: 1: Can’t create/write to file ‘/tmp/hive_meta_dump/metastore/VERSION.txt’ (Errcode: 13 – Permission denied) when executing ‘SELECT INTO OUTFILE’
txt文件(数据文件)用户是mysql,sql文件(表结构文件)用户是root
chown mysql:mysql /tmp/hive_meta_dump/metastore
5
mysqldump: Can’t create/write to file ‘/tmp/hive_meta_dump/metastore/VERSION.sql’ (Errcode: 2 – No such file or directory)
创建一下目录
mkdir -p /tmp/hive_meta_dump/metastore
数据库纯文本导入自动化脚本