Select Page

Delete MySQL Rows Older Than Date

I ran into a problem recently where I needed to delete all rows in a MySQL table that were older than three months. This I was able to accomplish with the following statement: [code] DELETE FROM [table “” not found /] WHERE [column] < DATE_SUB(NOW(), INTERVAL 3 MONTH); [/code] Substitute table and column, and a proper date option. The date_sub function subtracts a time period from the date specified. In this statement, NOW() grabs the current date from which INTERVAL 3 MONTHS is subtracted (along with the < operand). About Benjamin PeroveBen has been associated with a broad spectrum of technologies starting from an early age, and he's contributed to the success of many businesses and enterprises professionally since 2001. Most of his time is spent building cool stuff. When he's not working, he enjoys reading, playing acoustic guitar, and being with friends. He currently resides in Medellin,...

Perform Basic MySQL Database Operations

Assuming you are a superuser, create a new database: [code]mysqladmin -p create db_name[/code] Import data: [code]mysql -p db_name < db_name.sql[/code] Dump the db: [code]mysqldump -p –opt db_name > db_name.sql[/code] Backup! Here’s a script called db_backup (make the file executable with chmod a+x filename): [code] #!/bin/bash # vars path=/backup suffix=$(date +%m%d%Y) filename=db_backup_$suffix.tar.gz db_user=root db_pass= # dumb the dbs mysqldump -u$db_user -p$db_pass –opt asterisk > $path/asterisk.sql mysqldump -u$db_user -p$db_pass –opt gallery2 > $path/gallery2.sql mysqldump -u$db_user -p$db_pass –opt wordpress > $path/wordpress.sql # create an archive, cleanup tar -P -zcf $path/$filename $path/*.sql rm -f $path/*.sql exit 0 [/code] Edit the crontab with crontab -e and add the following line (which runs every day at 11 pm and logs to the file specified): [code] 0 23 * * * /backup/db_backup.sh >> /var/log/db_backup.log [/code] Alternately you can backup all databases with one command: [code] mysqldump –all-databases -p > mysqlbackup.sql [/code] Or backup the database files directly from /var/lib/mysql/. You don’t have to be a guru to administer your databases (though some knowledge of relational databases & SQL definitely helps). PHPMyAdmin is a handy tool that you should setup if you haven’t already. About Benjamin PeroveBen has been associated with a broad spectrum of technologies starting from an early age, and he's contributed to the success of many businesses and enterprises professionally since 2001. Most of his time is spent building cool stuff. When he's not working, he enjoys reading, playing acoustic guitar, and being with friends. He currently resides in Medellin,...