Clears MySQL Database Data Based on Field Conditions

Clears MySQL Database Data Based on Field Conditions

Background
An online database has 1000 database shards and disk alerts. The size of each database is not very large, but the total size is very large. It is unrealistic to manually clean up data based on the time field, so I decided to write a script to delete the data earlier than the specified time.

Script:

#/Bin/bash
# Auth by qunyingliu
# Files in xxx


HOST = $1
DBPORT = 3306
USER = "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 = $1
Local TABLE = $2
Local COLUMN = $3

SEARCH_RESULT =$ ($ {MySQL_show }$ {DB }$ {TABLE }$ {COLUMN} | awk '{if (NR = 5) print $2 }')
If ["$ {COLUMN}" = "$ {SEARCH_RESULT}"];
Then
Echo "true ";
Else
Echo "false ";
Fi
}


Function DataBasesBackup
{
Local DB = $1
Local TABLE = $2
Local DBBAK_DIR = $3
Local 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/mongodb.w.table. SQL .gz
Else
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 = $1
Local TABLE = $2
Local 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]
Do
If [$ 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 1
Else
$ {MySQL}-e "delete from $ {DB}. $ {TABLE} where Fdate <\" \ "limit 500000 ;"
Fi
Count = $ ($ 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/mysqlclean
Echo $ {ALL_DATABASES}>/data/logs/mysqlclean/databases.txt
COLUMN = "Fdate"

For DB in $ {ALL_DATABASES };
Do
Echo $ 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/mongodb.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
Fi
Done
Done

The above script only backs up and deletes data with specified conditions. After deleting the data, you need to run # $ {MySQL}-e "optimize table $ {DB }. $ {TABLE} "to release disk space. Since the deletion and release processes take a considerable amount of time, it is best to separate the two processes.

Before Running scripts in batches, you must create a single database table to verify the script cleanup effect.

This article permanently updates the link address: