MySQL分库备份脚本
#脚本详细内容
1 [root@db02 scripts]# cat /server/scripts/Store_backup.sh 2 #!/bin/sh 3 MYUSER=root 4 MYPASS=yangliheng 5 SOCKET=/data/3306/mysql.sock 6 MYLOGIN="mysql -u$MYUSER -p$MYPASS -S $SOCKET" 7 MYDUMP="mysqldump -u$MYUSER -p$MYPASS -S$SOCKET -B" 8 DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql")" 9 10 for dbname in $DATABASE11 do12 MYDIR=/server/backup/$dbname13 [ ! -d $MYDIR ] && mkdir -p $MYDIR14 $MYDUMP $dbname|gzip >$MYDIR/${dbname}_$(date +%F).sql.gz15 done
#执行后的结果
1 [root@db02 /]# tree /server/backup/ 2 /server/backup/ 3 ├── oldboy28 4 │ ├──oldboy28_2016-07-29.sql.gz 5 │ ├──oldboy28_2016-07-30.sql.gz 6 │ └──oldboy28_2016-07-31.sql.gz 7 ├── oldboy_yangliheng28 8 │ ├──oldboy_yangliheng28_2016-07-29.sql.gz 9 │ ├──oldboy_yangliheng28_2016-07-30.sql.gz10 │ └──oldboy_yangliheng28_2016-07-31.sql.gz11 └── yangliheng2812 ├──yangliheng28_2016-07-29.sql.gz13 ├──yangliheng28_2016-07-30.sql.gz14 └── yangliheng28_2016-07-31.sql.gz15 16 3 directories, 9 files
MySQL分库分表备份脚本
#脚本详细内容
1 [root@db02 scripts]# cat mysql_table.sh 2 #!/bin/sh 3 USER=root 4 PASSWD=yangliheng 5 SOCKET=/data/3306/mysql.sock 6 MYLOGIN="mysql -u$USER -p$PASSWD -S$SOCKET" 7 MYDUMP="mysqldump -u$USER -p$PASSWD -S$SOCKET" 8 DATEBASE="$($MYLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql")" 9 10 for dbname in $DATEBASE11 do12 TABLE="$($MYLOGIN -e "use $dbname;show tables;"|sed '1d')"13 for tname in $TABLE14 do15 MYDIR=/server/backup/$dbname/${dbname}_$(date +%F)16 [ ! -d $MYDIR ] && mkdir -p $MYDIR17 $MYDUMP $dbname $tname |gzip >$MYDIR/${dbname}_${tname}_$(date +%F).sql.gz18 done19 done
#查看执行后的结果
1 [root@db02 scripts]# tree /server/backup/ 2 /server/backup/ 3 ├── oldboy28 4 │ ├──oldboy28_2016-07-30 5 │ │├──oldboy28_oldboy_test_2016-07-30.sql.gz 6 │ │└──oldboy28_oldboy_test2_2016-07-30.sql.gz 7 │ └──oldboy28_2016-07-31 8 │ ├── oldboy28_oldboy_test_2016-07-31.sql.gz 9 │ └── oldboy28_oldboy_test2_2016-07-31.sql.gz10 ├── oldboy_yangliheng2811 │ ├──oldboy_yangliheng28_2016-07-3012 │ │├──oldboy_yangliheng28_cloudstack_2016-07-30.sql.gz13 │ │├──oldboy_yangliheng28_dashborad_2016-07-30.sql.gz14 │ │└── oldboy_yangliheng28_student_2016-07-30.sql.gz15 │ └──oldboy_yangliheng28_2016-07-3116 │ ├──oldboy_yangliheng28_cloudstack_2016-07-31.sql.gz17 │ ├──oldboy_yangliheng28_dashborad_2016-07-31.sql.gz18 │ └──oldboy_yangliheng28_student_2016-07-31.sql.gz19 └── yangliheng2820 ├──yangliheng28_2016-07-3021 │├──yangliheng28_openstack_2016-07-30.sql.gz22 │├──yangliheng28_saltstack_2016-07-30.sql.gz23 │├──yangliheng28_server_2016-07-30.sql.gz24 │└──yangliheng28_zabbix_2016-07-30.sql.gz25 └──yangliheng28_2016-07-3126 ├──yangliheng28_openstack_2016-07-31.sql.gz27 ├──yangliheng28_saltstack_2016-07-31.sql.gz28 ├──yangliheng28_server_2016-07-31.sql.gz29 └──yangliheng28_zabbix_2016-07-31.sql.gz30 31 9 directories, 18 files