Clean up MySQL database data based on field conditions

Clean up MySQL database data based on field conditions

Background

A database on the line has 1000 db of library, disk alarm, each library size is not very big but add up is very big.
Manually cleaning up the data based on the time field is not realistic, so decide to write a script to delete the data before the specified time.

Script:

#/bin/bash# #auth  by qunyingliu## files in xxx  host=$1dbport=3306user= "xxxx "Password=" xxxx "db_skip_clean=" Mysql|performance_schema|information_schema "dbbak_dir="/data/backup/databases/$ HOST " mysql=" Mysql -h ${host} -u${user} -p${password} -p${dbport} "MySQL_show=" mysqlshow -h ${host} -u${user} -p${password} -p${dbport}  "MySQL_Dump=" mysqldump  -h ${host} -u${user} -p${password} -p${dbport} "  function  existscolumn{local db=$1local table=$2local column=$3 search_result=$ ( ${MySQL_show}  ${DB} ${TABLE} ${COLUMN} | awk  ' { if  ( nr == 5)   print $2 } ') if [  "${column}"  =  "${search_result}"  ];thenecho  "true"; elseecho  "false"; fi}  function databasesbackup{local db=$1local table=$2local  dbbak_dir=$3local count=0 [ ! -d ${dbbak_dir}/$DB  ] && mkdir -p  ${dbbak _dir}/$DB   #count =$ (${mysql} -e   "Select count (fdate)  from ${db}.${table}  where fdate < \ "2014-01-01\"  order by fdate "|awk " {if  (NR  == 2)  print $0} ') count=$ (${mysql} -e   "use ${db};show table  status like  ' ${table} '; ' | awk  ' {if (nr==2)  print $5} ')   if [  $count  -gt 0 ];then     echo  "Start backup  ${db} ${table}"     echo   "Count: $count"     ${MySQL_Dump}  $DB   $TABLE  |gzip -c >${ dbbak_dir}/$DB/$DB. $TABLE .sql.gzelse   echo  "fdate older than 2014-01-01  count :  $count,skip backup  "   #${mysql} -e  " select fdate from ${db}.${table} limit 1; " fi#sleep 10#exit 1} function databasesclean{local db=$1local table=$2local  count=0#count=$ (${mysql} -e   "Select count (fdate)  from ${db}.${table} where  fdate < \ "2014-01-01\"  order by fdate "|awk " {if  (NR == 2 )  print $0} ') count=$ (${mysql} -e   "use ${db};show table status  like  ' ${table} '; ' | awk  ' {if (nr==2)  print $5} ') engine=$ (${mysql} -e   "use ${db};show table  status like  ' ${table} '; ' | awk  ' {if (nr==2)  print $2} ')   while [  $count  -gt 0 ]doif  [  $count  -le 500000 ];then ${MySQL} -e  "delete  from $ {db}.${table} where fdate < \ "2014-01-01\"; " echo  "Clean  ${db}.${tAble} ok "sleep 1   #sleep  10#exit 1else ${MySQL} -e " delete   from ${db}.${table} where fdate < \ "2014-01-01\"  limit 500000; " ficount=$ (($count  - 500000)) done #${mysql} -e  "optimize table  ${db}.$ {TABLE} "}    ######## #main ############### if  [   ! $ host   ]then  echo  "Input error, please check! "  echo " Usage: $0  ip "  exit 1  fi all_databases= "$ (${mysql_show} |awk  ' {++n;if (n>3&&nf>=3&&$2!~" (' ${db_skip_clean} ') ") print$2} ') "mkdir -p /data/logs/mysqlcleanecho ${all_databases} >/data/logs/mysqlclean/ Databases.txtcolumn= "FDate"  for db in  ${all_databases};d oecho  $DB #${mysql_show}   $DB  all_tables=$ (${mysql_show}  $DB  |awk  ' { if  (nr >4 )  print $_} '  |sed -e  ' s/[|+-]//g; /^$/d  ' |xargs  ) echo ${all_tables} >/data/logs/mysqlclean/$DB. txt    for table  in ${ALL_TABLES};    do        if  [  "true"  =  "$ (existscolumn  $DB   $TABLE   $COLUMN)"  ];         then            echo  $DB   $TABLE                          DataBasesBackup   $DB   $TABLE  ${dbbak_ dir}                         DataBasesClean    $DB   $TABLE         &nbsP;fi    donedone 

The above script simply backs up and deletes the data for the specified condition, and after deleting the data, it also needs to run #${mysql}-e "optimize table ${db}.${table}" to free up disk space. Because both the delete and release processes take a considerable amount of time, it is best to separate the two processes.

Before you can run a script in bulk, you need to make a single library table to verify the cleanup effect of the script.

This article from "Ops said: from rookie to veteran" blog, please be sure to keep this source http://liuqunying.blog.51cto.com/3984207/1745196

Clean up MySQL database data based on field conditions