20道shell第8和第9,分库和分表备份

企业实战题8:如何实现对MySQL数据库进行分库备份，请用脚本实现

企业实战题9:如何实现对MySQL数据库进行分库加分表备份，请用脚本实现

答案如下：

第8题：

root@clc-server ~]# cat shell8.sh 
#!/bin/bash
port=3306
mysql_user=root
mysql_pwd=oldboy123
mysql_sock=/data/3306/mysql.sock
mysql_log_cmd="mysql -u$mysql_user -p$mysql_pwd -S $mysql_sock"
mysql_dbs=`$mysql_log_cmd -e "show databases;"|egrep -v "Database|_schema|mysql"`
mysql_dump_cmd="mysqldump -u$mysql_user -p$mysql_pwd -S $mysql_sock"

IP=`ifconfig eth0|awk -F "[: ]+" 'NR==2{print $4}'`
backupdir=/backup/$IP

[ ! -d $backupdir ]&&mkdir -p $backupdir

for dbname in $mysql_dbs
 do
 $mysql_dump_cmd --events -B $dbname|gzip >$backupdir/${dbname}_$(date +%F)_bak.sql.gz
 done
[root@clc-server ~]#

第9题答案如下：

[root@clc-server ~]# cat shell9.sh 
#!/bin/bash
port=3306
mysql_user=root
mysql_pwd=oldboy123
mysql_sock=/data/3306/mysql.sock
mysql_log_cmd="mysql -u$mysql_user -p$mysql_pwd -S $mysql_sock"
mysql_dbs=`$mysql_log_cmd -e "show databases;"|egrep -v "Database|_schema|mysql"`
mysql_dump_cmd="mysqldump -u$mysql_user -p$mysql_pwd -S $mysql_sock"

IP=`ifconfig eth0|awk -F "[: ]+" 'NR==2{print $4}'`
backupdir=/backup/$IP

[ ! -d $backupdir ]&&mkdir -p $backupdir

for dbname in $mysql_dbs
 do
 [ ! -d $backupdir/${dbname} ]&& mkdir -p $backupdir/${dbname} 
 tables=`$mysql_log_cmd -e "show tables from $dbname"|sed '1d'`
 for table in $tables
 do
 $mysql_dump_cmd $dbname $table|gzip >$backupdir/${dbname}/${dbname}_${table}_$(date +%F)_bak.sql.gz
 done
 done
[root@clc-server ~]#