cat /server/scripts/Store_backup.sh #脚本详细内容
#!/bin/sh
MYUSER=root
MYPASS=123456
SOCKET=/data/3306/mysql.sock
MYLOGIN=`mysql -u$MYUSER -p$MYPASS -S $SOCKET`
MYDUMP=`mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -B`
DATABASE=$($MYLOGIN -e "show databases;" |egrep -vi "Data|_schema|mysql")
for dbname in $DATABASE
do
MYDIR=/data/backup/$dbname
[ ! -d $MYDIR ] && mkdir -p $MYDIR
$MYDUMP $dbname|gzip >$MYDIR/${dbname}_$(date +%F).sql.gz
done
[root@db02 /]# tree /data/backup/ #执行后的结果
/data/backup/
├── jianjie2.0
│ ├──jianjie2.0_2017-2-26.sql.gz
│ ├──jianjie2.0_2017-2-27.sql.gz
│ └──jianjie2.0_2017-2-28.sql.gz
├── jiuji
│ ├──jiuji_2017-2-26.sql.gz
│ ├──jiuji_2017-2-27.sql.gz
│ └──jiuji_2017-2-28.sql.gz
└── bbs
├──bbs_2017-2-26.sql.gz
├──bbs_2017-2-27.sql.gz
└──
[root@db02 scripts]# cat mysql_table.sh #脚本详细内容
#!/bin/sh
USER=root
PASSWD=123456
SOCKET=/data/3306/mysql.sock
MYLOGIN=`mysql -u$MYUSER -p$MYPASS -S $SOCKET`
MYDUMP=`mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -B`
DATABASE=$($MYLOGIN -e "show databases;" |egrep -vi "Data|_schema|mysql")
for dbname in $DATEBASE
do
TABLE=$($MYLOGIN -e "use $dbname;show tables;" |sed '1d')
for tname in $TABLE
do
MYDIR=/data/backup/$dbname/${dbname}_$(date +%F)
[ ! -d $MYDIR ] && mkdir -p $MYDIR
$MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$(date +%F).sql.gz
done
done
[root@db02 scripts]# tree /data/backup/ #查看执行后的结果
/data/backup/
├── jianjie2.0
│ ├──jianjie2.0_2017-2-27
│ │├──jianjie2.0_oldboy_test_2017-2-27.sql.gz
│ │└──jianjie2.0_oldboy_test2_2017-2-27.sql.gz
│ └──jianjie2.0_2017-2-28
│ ├── jianjie2.0_oldboy_test_2017-2-28.sql.gz
│ └── jianjie2.0_oldboy_test2_2017-2-28.sql.gz
├── jiuji
│ ├──jiuji_2017-2-27
│ │├──jiuji_cloudstack_2017-2-27.sql.gz
│ │├──jiuji_dashborad_2017-2-27.sql.gz
│ │└── jiuji_student_2017-2-27.sql.gz
│ └──jiuji_2017-2-28
│ ├──jiuji_cloudstack_2017-2-28.sql.gz
│ ├──jiuji_dashborad_2017-2-28.sql.gz
│ └──jiuji_student_2017-2-28.sql.gz
└── bbs
├──bbs_2017-2-27
│├──bbs_openstack_2017-2-27.sql.gz
│├──bbs_saltstack_2017-2-27.sql.gz
│├──bbs_server_2017-2-27.sql.gz
│└──bbs_zabbix_2017-2-27.sql.gz
└──bbs_2017-2-28
├──bbs_openstack_2017-2-28.sql.gz
├──bbs_saltstack_2017-2-28.sql.gz
├──bbs_server_2017-2-28.sql.gz
└──bbs_zabbix_2017-2-28.sql.gz
2.备份时跳过指定的表
[root@db02 ~]#mysqldump -uroot -p database --flush-logs --single-transaction --ignore-table=database.tablename_1–ignore-table=database.tablename_2 > databasedump.sql