Mysql backup

Mysql DB バックアップ用シェルをメモしておきます。

backup shell

#!/bin/bash

PATH=/usr/local/sbin:/usr/bin:/bin
BKUPDIR=/home/backup/mysqlhot
DATE=`date +%Y%m%d%H%S`
DATE1=`date +%Y%m%d`

sudo rm  -rf $BKUPDIR
mkdir -p $BKUPDIR

# performance_schemaをスキップ
DBLIST=`sudo ls -p /var/lib/mysql | grep / | grep -v performance_schema | tr -d /`

for dbname in $DBLIST
        do
        table_count=`mysql -u root -B -e "show tables" $dbname|wc -l`
        [ $table_count -ne 0 ] &&
        sudo mysqlhotcopy $dbname -u admin -p admin $BKUPDIR | logger -t mysqlhotcopy
done

sudo tar czvf /home/backup/bkup/mysql_dbs.$DATE.tar.gz /home/backup/mysqlhot
sudo /usr/bin/find /home/backup/bkup/ -name "*.tar.gz" -mtime +4 -exec rm {} \;

problem 1

以下のようなエラーが出力されました。

$ mysqlhotcopy  -u root mysqlhot/
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 12) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge.
 at /usr/bin/mysqlhotcopy line 200

solution

 $ sudo yum install "perl(DBD::mysql)"

problem 2

DBD::mysql::db do failed: SELECT, LOCK TABLES command denied to user 'admin'@'localhost' for table 'accounts' at /usr/bin/mysqlhotcopy line 523.

solution

performance_schema DBのバックアップをスキップする

この記事がお役にたちましたらシェアをお願いします:)

• ツイート
•