MYSQL Bulk Deleter
Ever wanted to delete a tonne of rows from a live database but ended up crying in the corner like a little baby? Ever kicked of a delete job only to find your server has given up the will to live?
If the answer is yes then this may be the solution for you.
Please note this has only been tested in Ubuntu
What this tool does
- Deletes rows from mysql in batches, it has small and big sleeps so that anything accessing the database can still hopefully run as normal.
- Ideal for leaving overnight when you have the parameters tuned correctly and millions of rows to delete on a prod system.
Features
nice cli interface including:
- progress bar
- estimated time remaining
- row counts
screenshot
requirements
python modules
- configobj
easy_install configobj - mysqldb
sudo apt-get install python-mysqldb
What this tool does not
- monitor mysql performance, load average etc
- sanity check your SQL's
To use this tool
- copy template.job to a new file i.e. good.job
- fill in database connection details
- fill in sqlQueryRecon with SQL to find the minimum id of the data you want to keep
- fill in sqlQueryDelete to accept the id genereated in 3 and delete everything below it
- tune timeouts i.e. deleteing in batches of > 5000 kills my server
- pass the filename of your job into the script
python mysql_bulk_deleter.py good.job - Ctrl + C will cancel this script in linux
Notes
- When using this tool I highly recommend having mtop or innotop running while you tune your delete parameters.
Database Delete Time Remaining does not currently include big and small wait times.