mysql(mariadb) 테이블별 mysqldump 백업

디비별 디렉토리를 만들고, 테이블별 .sql 파일을 만든다.

[root@localhost cron.daily]# vim sqlBackup.sh 
#!/bin/bash

_DB_PASS='password'
_BACKUP_DIR='/backup/sqlBackup/'

find $_BACKUP_DIR* -mtime +7 -exec rm -fr {} \; > /dev/null 2>&1

db_list=`echo "show databases;" | mysql -N -uroot -p${_DB_PASS}`
eval `date "+day=%d; month=%m; year=%Y"`

INSTD="$_BACKUP_DIR/sql-backup-$year-$month-$day"
mkdir -p $INSTD

for db in $db_list; do
        if [ ! -d $INSTD/$db ]
        then
                mkdir $INSTD/$db
        fi
        table_list=`echo "show tables" | mysql -N -uroot -p${_DB_PASS} $db`
        for table in $table_list; do
                mysqldump -uroot -p${_DB_PASS}  --lock-all-table --opt --quick --quote-names $db $table  > $INSTD/$db/${table}.sql
        done
done