Delete MySQL Rows Older Than Date

June 25th, 2008 * Comments(8)
Share the knowledge

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:

DELETE FROM [table] WHERE [column] < DATE_SUB(NOW(), INTERVAL 3 MONTH);

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 Perove

Benjamin has been associated with computer technology starting from a very early age, and has contributed to the success of many businesses and enterprises since 2001.

He loves to crush pow at Keystone, play acoustic guitar, climb rocks, and ascend mountains on his road bike.

Benjamin is an Avalanche fan and currently resides in Boulder, Colorado.

 

Perform Basic MySQL Database Operations

September 17th, 2007 * Comments(2)
Share the knowledge

Assuming you are a superuser, create a new database:

mysqladmin -p create db_name

Import data:

mysql -p db_name < db_name.sql

Dump the db:

mysqldump -p –opt db_name > db_name.sql

Backup!

Here’s a script called db_backup (make the file executable with chmod a+x filename):

#!/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

Edit the crontab with crontab -e and add the following line (which runs every day at 11 pm and logs to the file specified):

0 23 * * * /backup/db_backup.sh >> /var/log/db_backup.log

Alternately you can backup all databases with one command:

mysqldump –all-databases -p > mysqlbackup.sql

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 Perove

Benjamin has been associated with computer technology starting from a very early age, and has contributed to the success of many businesses and enterprises since 2001.

He loves to crush pow at Keystone, play acoustic guitar, climb rocks, and ascend mountains on his road bike.

Benjamin is an Avalanche fan and currently resides in Boulder, Colorado.

 

Add this site to your Firefox Search Bar

Twitter Updates


@dandrewsify Apparently something's up with the way my sandbox environment works. Try

Recent Entries

Topics

Archives

This site is optimized for Firefox.