![备份 mySQL 表,但仅限于自上次备份以来发生变化的表?](https://linux22.com/image/762997/%E5%A4%87%E4%BB%BD%20mySQL%20%E8%A1%A8%EF%BC%8C%E4%BD%86%E4%BB%85%E9%99%90%E4%BA%8E%E8%87%AA%E4%B8%8A%E6%AC%A1%E5%A4%87%E4%BB%BD%E4%BB%A5%E6%9D%A5%E5%8F%91%E7%94%9F%E5%8F%98%E5%8C%96%E7%9A%84%E8%A1%A8%EF%BC%9F.png)
我正在尝试编写一个小脚本,该脚本将在夜间 cron 上将表备份到单独的 .SQL 文件中。到目前为止,我有这个,并且运行良好:
db-备份.sh:
#!/bin/bash
DATABASES=$(mysql --defaults-extra-file=/root/.my.cnf -hlocalhost -uroot -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)
for db in $DATABASES; do
DIR=/backup/mysql/$db
[ -n "$DIR" ] || DIR=.
test -d $DIR || mkdir -p $DIR
echo "Dumping tables into separate SQL command files for database '$db' into dir=$DIR"
tbl_count=0
for t in $(mysql --defaults-extra-file=/root/.my.cnf -uroot -D $db -NBA -e 'show tables')
do
echo "DUMPING TABLE: $db.$t"
mysqldump --defaults-extra-file=/root/.my.cnf -uroot $db $t | gzip > $DIR/$db.$t.sql.gz
tbl_count=$(( tbl_count + 1 ))
done
echo "$tbl_count tables dumped from database '$db' into dir=$DIR"
done
这非常有效,可以将所有表格备份到较小的压缩文件中。我想这样做的原因是,有些表格会发生变化,但很多表格将保持不变(其中一些表格相当大 - 10+gb)。
我正在尝试解决是否可以做到这一点,以便仅当自上次备份以来发生变化时才发生 MySQL 转储?
答案1
好吧,回答我自己的问题。我确信有更好的方法可以做到这一点,但这对我来说有效:
#!/bin/bash
DATABASES=$(mysql --defaults-extra-file=/root/.my.cnf -hlocalhost -uroot -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)
for db in $DATABASES; do
if [[ "$db" != "performance_schema" && "$db" != "information_schema" && "$db" != "mysql" ]]; then
DIR=/backup/mysql/$db
[ -n "$DIR" ] || DIR=.
test -d $DIR || mkdir -p $DIR
echo "Dumping tables into separate SQL command files for database '$db' into dir=$DIR"
tbl_count=0
for t in $(mysql --defaults-extra-file=/root/.my.cnf -uroot -D $db -NBA -e 'show tables');
do
echo "DUMPING TABLE: $db.$t"
# detect which type of DB we are using - myISAM or Innodb
if test -f "/var/lib/mysql/$db/$t.MYI"; then
#echo "its myi"
last_modified=$(stat -c %y "/var/lib/mysql/$db/$t.MYI")
else
#echo "its ibd"
last_modified=$(stat -c %y "/var/lib/mysql/$db/$t.ibd")
fi
# see if we have a mod timestamp for this...
if test -f "$DIR/$t.mod"; then
value=$(<$DIR/$t.mod)
else
value=0
fi
echo " LAST MOD: $last_modified"
if [ "$value" != "$last_modified" ]; then
echo " Seems like this one has changed... '$value' != '$last_modified' ";
# make sure we update the modified timestamp
echo $last_modified > $DIR/$t.mod
# doing dump now...
mysqldump --defaults-extra-file=/root/.my.cnf --add-drop-table --routines -uroot $db $t | gzip > $DIR/$db.$t.sql.gz
# write down the tables that are changed...
tbl_count=$(( tbl_count + 1 ))
else
echo " Not changed since last dump on $last_modified"
fi
done
echo "$tbl_count tables dumped from database '$db' into dir=$DIR"
else
echo "SKIP DB: $db "
fi
done
它将跳过 performance_schema、information_schema 和 MySQL DB,因为我不关心这些。然后,它会在.mod
转储每个表后为其设置一个文件。下次运行脚本时会读取该文件,如果此后该文件没有更改,则不会重新转储该表。它依赖于在 中设置根 MySQL 登录名/root/.my.cnf
,如下所示:
[client]
password='xxxx'
这意味着您可以使用脚本进行无密码操作(而不是在进程列表中显示它)