mysql - 从远程服务器导入数据库的脚本

Question

我有一个脚本，它通过 SSH 连接到远程服务器，运行一些命令来导出数据库，将它们归档为单个文件，然后通过 SCP 下载到本地服务器，本地服务器删除数据库然后导入它们.

本地系统基本上是远程生产系统的镜像，除了不需要为开发提供的大型数据集。

#!/bin/bash

echo "Importing..."

Hostname=xxxxxx
Port=xxxxxx
Username=xxxxxx
Password=xxxxxx
Location=/sql/dump/$(date +"%Y-%m-%d")

ssh -p $Port $Username@$Hostname "mkdir -p $Location"

ssh -p $Port $Username@$Hostname <<'SSH'
Ignore=$(mysql --user=root --password=$Password -BNe "SELECT CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'rec%' ORDER BY CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME );" | awk '{ printf " --ignore-table=%s", $0 }')
mysql --user=root --password=$Password -e "SHOW DATABASES;" | grep -Ev "information_schema|mysql|performance_schema" | xargs mysqldump --force --user=root --password=$Password $Ignore --databases | gzip > $Location/full.sql.gz
SSH

mkdir -p $Location
scp -P$Port -c blowfish $Username@$Hostname:$Location/full.sql.gz $Location
ssh -p $Port $Username@$Hostname "rm -rf $Location" >/dev/null 2>&1
gunzip $Location/full.sql.gz
mysql --user=root --password=$Password -e "SHOW DATABASES;" | grep -Ev "information_schema|mysql|performance_schema" | awk '{print "DROP DATABASE " $1 "; SELECT SLEEP(0.1);"}' | mysql --user=root --password=$Password
mysql --user=root --password=$Password  < $Location/full.sql
rm -rf $Location

echo "Done."

目前，除了这部分，一切正常:

Ignore=$(mysql --user=root --password=$Password -BNe "SELECT CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'rec%' ORDER BY CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME );" | awk '{ printf " --ignore-table=%s", $0 }')
mysql --user=root --password=$Password -e "SHOW DATABASES;" | grep -Ev "information_schema|mysql|performance_schema" | xargs mysqldump --force --user=root --password=$Password $Ignore --databases | gzip > $Location/full.sql.gz

这应该做的是获取所有以“rec”开头的表名的列表，并将它们从导入中排除(因为它们很大，不需要存储在本地) .我觉得我很接近，但它在我的 mysqldump 命令中产生了错误的语法。

Importing...
Pseudo-terminal will not be allocated because stdin is not a terminal.
Linux xxxxxx 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
zsh: permission denied: /full.sql.gz
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
scp: /sql/dump/2014-05-15/full.sql.gz: No such file or directory
gzip: /sql/dump/2014-05-15/full.sql.gz: No such file or directory
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Database' at line 1
_private/import2.sh: line 23: /sql/dump/2014-05-15/full.sql: No such file or directory
Done.

我正在寻找一种在我的导出中排除这些表和系统数据库的方法，但也许更优雅:摆脱 heredoc 语法，因为它在我的 STDOUT 中产生不需要的输出，而且似乎也不接受局部变量，我用它来解决一些奇怪的引用转义问题。

假设我确实正确设置了变量，这是我得到的输出:

mysqldump: Got error: 1102: "Incorrect database name ' --ignore-table=0004_reset_manager.rec_alert --ignore-table=0005_reset_manager.rec_alert --ignore-ta'" when selecting the database
xargs: mysqldump: terminated by signal 7

更新:有效!对于那些有兴趣在他们的服务器上使用它的人，这里是最终脚本:

#!/bin/bash

echo "Importing..."

Hostname=xxxxxx
Port=xxxxxx
Username=xxxxxx
Password=xxxxxx
Directory=xxxxx
Filename=xxxxxx-$(date +"%Y-%m-%d")

Databases=$(ssh -p $Port $Username@$Hostname "mysql --user=root --password=$Password -BNe \"SHOW DATABASES;\" | grep -Ev \"information_schema|mysql|performance_schema\" | awk '{ printf \" %s\", \$0 }'")
Ignore=$(ssh -p $Port $Username@$Hostname "mysql --user=root --password=$Password -BNe \"SELECT CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'rec%';\" | awk '{ printf \" --ignore-table=%s\", \$0 }'")

ssh -p $Port $Username@$Hostname "mysqldump --force --user=root --password=$Password $Ignore --databases $Databases | gzip > $Directory/$Filename.sql.gz"
scp -P$Port -c blowfish $Username@$Hostname:$Directory/$Filename.sql.gz $Directory
ssh -p $Port $Username@$Hostname "rm -f $Directory/$Filename.sql.gz" >/dev/null 2>&1
gunzip $Directory/$Filename.sql.gz
mysql --user=root --password=$Password -Ne "SHOW DATABASES;" | grep -Ev "information_schema|mysql|performance_schema" | awk '{ printf "DROP DATABASE %s;", $0 }' | mysql --user=root --password=$Password
mysql --user=root --password=$Password < $Directory/$Filename.sql
rm -f $Directory/$Filename.sql

echo "Done."

Answer 1

它不起作用，因为它没有对变量进行插值。删除 SSH 周围的引号，它应该可以工作。但请注意，您将不得不转义 $ 以及其他可能的字符。

例子:

$cat test.sh 
a=test

cat << 'SSH'
$a
EOF

cat << SSH
$a 
EOF
$ bash test.sh 
$a
test

试试这个:

ssh -p $Port $Username@$Hostname << SSH
Ignore=$(mysql --user=root --password=$Password -BNe "SELECT CONCAT( TABLE_SCHEMA , '.' ,  TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'rec%' ORDER BY CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME );" | awk '{ printf " --ignore-table=%s", \$0 }')
mysql --user=root --password=$Password -e "SHOW DATABASES;" | grep -Ev "information_schema|mysql|performance_schema" | xargs mysqldump --force --user=root --password=$Password \$Ignore --databases | gzip > $Location/full.sql.gz

SSH

我无法测试，但应该没问题。