Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

15Jul/100
Simple MySQL Database Backup

Another simple backup script. This script builds on the rsync file backup to allow daily, monthly, and yearly incremental MySQL database backups. The backup will be a file name that includes the database name followed by an underscore and either the day, month, or year. The script will overwrite daily and monthly files as needed. The script relies on a separate file to hold the password, and we all know that is a very bad idea. You should use something more secure, so use this script is only as an example. Replace databasename with the name of your database. Give it a try, it is a pretty good example of an incremental database backup.

# Author: Kevin Verhoeven
# Description: Script to backup a MySQL database based on daily, monthly, and yearly increments
#
[ ! -f asof.dat ] && touch asof.dat
ATIME=$(stat -c "%y" asof.dat)

rm asof.dat
touch asof.dat
if [ `date +%e` -eq 1 ]; then
     date +%B >> asof.dat
fi
if [ `date +%Y` -gt ${ATIME:0:4} ]; then
      echo ${ATIME:0:4} >> asof.dat
fi
date +%A >> asof.dat
for i in $(cat asof.dat); do "mysqldump" -uroot -p`cat passwordfile` --all --add-drop-table --add-locks --databases databasename > databasename_$i.sql; done
29Dec/090
Reset the auto increment number in a MySQL table

To reset the auto increment number in a MySQL table, simply run the following command:


ALTER TABLE table AUTO_INCREMENT=1

Replace the number 1 with the value you are wish to reset the auto increment number to. Substitute table with the table name you are modifying. If you leave the number 1, the table will reset the auto increment value to the next available number.

Filed under: MySQL No Comments
14Dec/090
Finding Duplicate Rows in SQL

I used this to solve a problem today.

// Finding duplicates in a table

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

// Find rows that occur exactly once

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

// Return all duplicate cities and how often they appear
// Works with ORACLE

select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1

// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL

select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc

// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear

select city_name, count(city_name) as cnt
from areas
group by city_name

// version for Micrsoft's MSSQL Server
// make use of the HAVING clause

select city_name
from areas
group by city_name
having count(*) > 1

// multiple columns in MySQL:
SELECT PL_CONTACT_FK, PL_PHONE_FK, COUNT(*) as DuplicateCount FROM PHONE_ALTERNATE GROUP BY PL_CONTACT_FK, PL_PHONE_FK HAVING DuplicateCount > 1;

26May/090
How to Repair a MySQL Database

If a server is shutdown unexpectedly (power outage or hardware failure), one or more MySQL tables can be locked open and may need to be repaired.

Browse to the folder that contains the MySQL databases:

cd /var/lib/mysql/

Check your tables by running the following command:

myisamchk *.MYI

The easiest and safest method is to run the following command (-r means "recovery mode" and -q means "quick recovery mode"). This command will not touch the data within the database:

myisamchk -r -q tbl_name

A more difficult repair to run is as follows. This command will delete damaged data:

myisamchk -r tbl_name

Reference http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html for more information.