shell指令碼，對MySQL資料庫進行分庫加分表備份

標籤：

[[email protected] wyb]# cat table_backup.sh #!/bin/bashflag=0user=rootpass=testmysql -u$user -p"$pass" -e "show databases;" &>/dev/null[ $? -ne 0  ] && read -p "Mysql do not running,start it?(`echo -e "\033[32myes/no\033[0m"`):" choice && flag=1[[ "choice" -eq "yes" ]] && service mysqld start &>/dev/null && flag=0[ $flag -eq 1 ] && exit 2database=`mysql -u$user -p$pass  -e "show databases;"|sed 1d|grep -v ‘schema‘`echo -e "\033[32m==================backup start=====================\033[0m"for i in $databasedo  tables=`mysql -u$user -p"$pass" -e "use $i;show tables;"|sed 1d`  for j in $tables  do    mysqldump -u$user -p"$pass"   -B --databases $i --tables $j > /tmp/${i}-${j}-`date +%F`.sql   [ $? -eq 0 ] && echo $i $j ok >>/tmp/table.log||echo $i $j failed >>/tmp/table.log   [ $? -eq 0 ] && echo -e "$i $j \033[32mok\033[0m" ||echo -e "$i $j \033[31mfailed\033[0m"  donedoneecho -e "\033[32m===================backup stop=======================\033[0m"[[email protected] wyb]# bash table_backup.sh ==================backup start=====================mysql columns_priv okmysql db okmysql func okmysql help_category okmysql help_keyword okmysql help_relation okmysql help_topic okmysql host okmysql proc okmysql procs_priv okmysql tables_priv okmysql time_zone okmysql time_zone_leap_second okmysql time_zone_name okmysql time_zone_transition okmysql time_zone_transition_type okmysql user ok===================backup stop=======================[[email protected] wyb]# cd tmp-bash: cd: tmp: No such file or directory[[email protected] wyb]# cd /tmp[[email protected] tmp]# lsa.log                              mysql-help_category-2016-09-07.sql  mysql-tables_priv-2016-09-07.sql                table.logdashazi123.sh                      mysql-help_keyword-2016-09-07.sql   mysql-time_zone-2016-09-07.sql                  VMwareDnDkeyring-cmzNiY                     mysql-help_relation-2016-09-07.sql  mysql-time_zone_leap_second-2016-09-07.sql      vmware-rootmapping-root                       mysql-help_topic-2016-09-07.sql     mysql-time_zone_name-2016-09-07.sql             vmware-root-1723735141mysql-columns_priv-2016-09-07.sql  mysql-host-2016-09-07.sql           mysql-time_zone_transition-2016-09-07.sql       VMwareTools-9.6.2-1688356.tar.gzmysql-db-2016-09-07.sql            mysql-proc-2016-09-07.sql           mysql-time_zone_transition_type-2016-09-07.sql  vmware-tools-distribmysql-func-2016-09-07.sql          mysql-procs_priv-2016-09-07.sql     mysql-user-2016-09-07.sql[[email protected] tmp]# ls mysql*mysql-columns_priv-2016-09-07.sql   mysql-help_topic-2016-09-07.sql   mysql-time_zone_leap_second-2016-09-07.sqlmysql-db-2016-09-07.sql             mysql-host-2016-09-07.sql         mysql-time_zone_name-2016-09-07.sqlmysql-func-2016-09-07.sql           mysql-proc-2016-09-07.sql         mysql-time_zone_transition-2016-09-07.sqlmysql-help_category-2016-09-07.sql  mysql-procs_priv-2016-09-07.sql   mysql-time_zone_transition_type-2016-09-07.sqlmysql-help_keyword-2016-09-07.sql   mysql-tables_priv-2016-09-07.sql  mysql-user-2016-09-07.sqlmysql-help_relation-2016-09-07.sql  mysql-time_zone-2016-09-07.sql[[email protected] tmp]# cat table.log mysql columns_priv okmysql db okmysql func okmysql help_category okmysql help_keyword okmysql help_relation okmysql help_topic okmysql host okmysql proc okmysql procs_priv okmysql tables_priv okmysql time_zone okmysql time_zone_leap_second okmysql time_zone_name okmysql time_zone_transition okmysql time_zone_transition_type okmysql user ok[[email protected] tmp]# 

 

shell指令碼，對MySQL資料庫進行分庫加分表備份