Archive

Archive for the ‘MySQL’ Category

PHP based torrent file creator, tracker and seed server – PHPTracker

September 16th, 2011 Comments off

Great software!

Why PHPTracker? Because it’s not “just a tracker”, it contains a seeder server too so the distribution of your files is automatic. This is a big step for the content providers to adapt this amazing technology and change the stereotype that “torrent = warez”.

PHP based torrent file creator, tracker and seed server – PHPTracker.

Categories: Linux, MySQL, PHP Tags: , , ,

Plesk FTP account passwords

May 5th, 2011 Comments off

Open phpMyAdmin or mysql command line client, choose database pma and execute query:

USE psa;
SELECT REPLACE( sys_users.home, '/var/www/vhosts/', '' ) AS domain, sys_users.login, accounts.password
FROM sys_users
LEFT JOIN accounts ON sys_users.account_id = accounts.id
ORDER BY sys_users.home ASC

It will show you all FTP users from Plesk

Categories: MySQL, Plesk Tags: , , ,

MySQL backup databases into separate files by weekdays with compression

May 15th, 2010 Comments off

I’ve found in Net script, which only backups databases into separate files and modified it. I’ve added gzip compression and backup by weekday. So, you can cron this job daily and get backups for each weekday. It’s very flexible backup algorithm.

Script is below:

#!/bin/bash

# This script backups every MySQL database to its own file

#Some variables you can set how you like
USER='<possible root>'
PASSWORD='<password>'
DAYOFWEEK=`/bin/date +"%w"`
OUTPUTDIR="/usr/backup/mysql/$DAYOFWEEK" # backup dir
MYSQLDUMP='/usr/local/bin/mysqldump'     # path to mysqldump, may be /usr/bin/mysqldump
MYSQL='/usr/local/bin/mysql'             # path to mysql, may be /usr/bin/mysql  

#Clean up any old backups
rm -f $OUTPUTDIR/*

#Get a list of databases names except the system one
databases=`$MYSQL --user=$USER --password=$PASSWORD -e 'SHOW DATABASES;' | grep -Ev '(Database|information_schema)'`

#Dump each database in turn and compress the output with gzip
for db in $databases; do
$MYSQLDUMP --opt --hex-blob --force --user=$USER --password=$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
done

Download script mysql_backup.sh (800 kB)

Categories: Linux, MySQL Tags: , ,

Relevant search in MySQL

September 11th, 2009 2 comments

We have an query. Query is a long phrase, for example 4 words. We want to found in table most relevant results.

There is built-in operator in MySQL which called MATCH – AGAINST. This operator executes relevant search in specified FULLTEXT indexes.

Our table:

CREATE TABLE `clients` (
`id` int(10) unsigned NOT NULL auto_increment,
`firm` varchar(150) NOT NULL default '',
`name_` varchar(30) NOT NULL default '',
`address` varchar(150) default NULL,
PRIMARY KEY  (`id`),
FULLTEXT KEY `firm` (`firm`,`name_`,`address`)
) ;

To find all rows which fields `firm`,`name_`,`address` contain ‘lawyer Braun park avenue’ use the query (for MySQL 5.0)

SELECT * FROM `clients` WHERE MATCH ( `firm`,`name_`,`address` ) AGAINST ( 'lawyer Braun park avenue' )

Important. All fields you are searching through must be in one FULLTEXT index. You can create FULLTEXT index for each search type.

Important. If you want to search by a part of word you can be dissapointed. While MySQL indexes the words from the start, the Fulltext-search also available only with ending wildcard * (wildcards enabled only in boolean mode). When you using a boolean mode, you need to sort the results manually with ORDER clause.

SELECT *, MATCH ( `firm`,`name_`,`address` ) AGAINST ( 'lawy* Braun park avenu* IN BOOLEAN MODE' ) AS `score` FROM `clients` WHERE MATCH ( `firm`,`name_`,`address` ) AGAINST ( 'lawy* Braun park avenu* IN BOOLEAN MODE' ) ORDER BY `score` DESC
Categories: MySQL Tags: