MySQL怎么实现分库分表备份

题目：MySQL分库分表备份

脚本详细内容

[root@db02 scripts]# cat /server/scripts/Store_backup.sh  #!/bin/sh  MYUSER=root
  MYPASS=xuliangwei
  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=/server/backup/$dbname  [ ! -d $MYDIR ] && mkdir -p $MYDIR  $MYDUMP $dbname|gzip >$MYDIR/${dbname}_$(date +%F).sql.gz
  done

执行后的结果

  [root@db02 /]# tree /server/backup/  /server/backup/
  ├── oldboy26
  │ ├──oldboy26_2015-12-29.sql.gz
  │ ├──oldboy26_2015-12-30.sql.gz
  │ └──oldboy26_2015-12-31.sql.gz
  ├── oldboy_xuliangwei26
  │ ├──oldboy_xuliangwei26_2015-12-29.sql.gz
  │ ├──oldboy_xuliangwei26_2015-12-30.sql.gz
  │ └──oldboy_xuliangwei26_2015-12-31.sql.gz
  └── xuliangwei26
  ├──xuliangwei26_2015-12-29.sql.gz
  ├──xuliangwei26_2015-12-30.sql.gz
  └──
  xuliangwei26_2015-12-31.sql.gz
  3 directories, 9 files

脚本详细内容

  [root@db02 scripts]# cat mysql_table.sh #  #!/bin/sh  USER=root
  PASSWD=xuliangwei
  SOCKET=/data/3306/mysql.sock
  MYLOGIN=”mysql -u$USER -p$PASSWD -S$SOCKET”
  MYDUMP=”mysqldump -u$USER -p$PASSWD -S$SOCKET”
  DATEBASE=”$($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=/server/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 /server/backup/  /server/backup/
  ├── oldboy26
  │ ├──oldboy26_2015-12-30
  │ │├──oldboy26_oldboy_test_2015-12-30.sql.gz
  │ │└──oldboy26_oldboy_test2_2015-12-30.sql.gz
  │ └──oldboy26_2015-12-31
  │ ├── oldboy26_oldboy_test_2015-12-31.sql.gz
  │ └── oldboy26_oldboy_test2_2015-12-31.sql.gz
  ├── oldboy_xuliangwei26
  │ ├──oldboy_xuliangwei26_2015-12-30
  │ │├──oldboy_xuliangwei26_cloudstack_2015-12-30.sql.gz
  │ │├──oldboy_xuliangwei26_dashborad_2015-12-30.sql.gz
  │ │└── oldboy_xuliangwei26_student_2015-12-30.sql.gz
  │ └──oldboy_xuliangwei26_2015-12-31
  │ ├──oldboy_xuliangwei26_cloudstack_2015-12-31.sql.gz
  │ ├──oldboy_xuliangwei26_dashborad_2015-12-31.sql.gz
  │ └──oldboy_xuliangwei26_student_2015-12-31.sql.gz
  └── xuliangwei26
  ├──xuliangwei26_2015-12-30
  │├──xuliangwei26_openstack_2015-12-30.sql.gz
  │├──xuliangwei26_saltstack_2015-12-30.sql.gz
  │├──xuliangwei26_server_2015-12-30.sql.gz
  │└──xuliangwei26_zabbix_2015-12-30.sql.gz
  └──xuliangwei26_2015-12-31
  ├──xuliangwei26_openstack_2015-12-31.sql.gz
  ├──xuliangwei26_saltstack_2015-12-31.sql.gz
  ├──xuliangwei26_server_2015-12-31.sql.gz
  └──xuliangwei26_zabbix_2015-12-31.sql.gz
  9 directories, 18 files