数据库纯文本导出自动化脚本

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

数据库纯文本导入自动化脚本

此处内容已被隐藏，扫码关注微信公众号，发送【九万里】获取验证码解锁