Delete large amounts of data in MySQL 2022-05-19 00:00
Recently I want to delete a large amount of data from mysql. There are about 400 million records. Fortunately, the data to be deleted can be queried according to the index. If I delete one by one, it will be very slow. If the entire deletion will cause the database to freeze. So I Write a script to delete these data in batches. The script probably looks like this.
#!/bin/bash echo 'begin delete...' > /tmp/delete_target_record.log input="/home/work/target_record.txt" total=0; while IFS= read -r line do stop_flag=0; while(( $stop_flag<=0 )) do stop_flag=`mysql -h<host_address> -P<port> -u<user_name> -p<password> -e "delete from <table_name> where <column_name>='$line' limit 2000;" -vvv|grep 'Query OK, 0 rows affected'|wc -l` sleep 1s; total=$(($total+2)); echo "deleting... '$line', total delete : $total k" >> /tmp/delete_target_record.log done done < "$input"
This file '/home/work/target_record.txt' stores the conditions that need to be deleted.
It should be noted that the query conditions here must be able to hit the index. Deleting 2,000 items at one time will not cause data freezes.