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

九万里大数据 2021-07-20

1683

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

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
复制

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

从分隔符纯文件导入表 https://dev.mysql.com/doc/refman/5.7/en/load-data.html

load脚本

#!/usr/bin/env bash
# table_load.sh 
 
db_user=hive
db_pass=Hive@1234
db_old=metastore
db_name=metanew
 
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_old}
    #cmd1=`mysql -u${db_user} -p${db_pass} -D${db_name} < ${tb_output}/${tb_name}.sql`
    cmd1=`mysql -u${db_user} -p${db_pass} -D${db_name} -e "SET FOREIGN_KEY_CHECKS = 0; source ${tb_output}/${tb_name}.sql; SET FOREIGN_KEY_CHECKS = 1"`
    now=`date '+%Y-%m-%d %H:%M:%S'`
    echo ${now}
    printf "%-30s %-10s\n" ${tb_name} end
done
 
# 再load数据
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_old}
    cmd2=`mysql -u${db_user} -p${db_pass} -D${db_name} -e "SET FOREIGN_KEY_CHECKS = 0; LOAD DATA INFILE '${tb_output}/${tb_name}.txt' INTO TABLE ${tb_name} FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'; SET FOREIGN_KEY_CHECKS = 1"`
    now=`date '+%Y-%m-%d %H:%M:%S'`
    echo ${now}
    printf "%-30s %-10s\n" ${tb_name} end
done
复制

load启动脚本

#!/bin/bash
# load_start.sh 
 
nohup /bin/bash table_load.sh >> load.log 2>&1 &
复制

load停止脚本

#!/bin/bash
# load_stop.sh 
 
ps -ef | grep "/bin/bash table_load.sh" | grep -v grep | awk '{print $2}' | xargs kill -9
复制

纯文本表结构和表数据

[root@centos hive_meta_dump]# ls -l metastore | head -n 5
total 4172
-rw-r--r-- 1 root  root     1613 Jul  9 15:10 BUCKETING_COLS.sql
-rw-rw-rw- 1 mysql mysql       0 Jul  9 15:10 BUCKETING_COLS.txt
-rw-r--r-- 1 root  root     1323 Jul  9 15:10 CDS.sql
-rw-rw-rw- 1 mysql mysql     125 Jul  9 15:10 CDS.txt
复制

数据库纯文本导入报错

ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (metanew
.PARTITIONS
, CONSTRAINT PARTI TIONS_FK1
FOREIGN KEY (TBL_ID
) REFERENCES TBLS
(TBL_ID
))

https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html http://www.sqlines.com/mysql/set_foreign_key_checks 对有外键的表，可以设置FOREIGN_KEY_CHECKS不检查，这样就不需要关注建表和导入数据的顺序了。

mysql -uhive -pHive@1234 -Dmetanew -e "SET FOREIGN_KEY_CHECKS = 0; LOAD DATA INFILE '/tmp/hive_meta_dump/metastore/TBLS.txt' INTO TABLE TBLS FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; SET FOREIGN_KEY_CHECKS = 1"
复制

欢迎关注我的微信公众号“九万里大数据”，原创技术文章第一时间推送。欢迎访问原创技术博客网站 jwldata.com^[1]，排版更清晰，阅读更爽快。

引用链接

[1]
jwldata.com: https://www.jwldata.com

mysql

文章转载自九万里大数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。