 Register |
| Unregistered users click here to register registered users can post their website here |
 Science Humor: |
Air is water with holes in it.
|
| Chuck Norris Humor: |
Chuck Norris smells what the Rock is cooking... because the Rock is Chuck Norris' personal chef.
|
|
 |
| GeoCode - |
|---|
//Mysql!
set @orig_lat=38.70032 ;
set @orig_lon=-9.38603;
/*Meters*/
set @dist=10;
set @mylat = @orig_lat;
set @mylon = @orig_lon;
/*1º= 69miles = 111Km or 111000 meters*/
set @unit = 111000;
set @lon1 = @mylon - @dist/abs(cos(radians(@mylat))*@unit);
set @lon2 = @mylon + @dist/abs(cos(radians(@mylat))*@unit);
set @lat1 = @mylat - (@dist/@unit);
set @lat2 = @mylat + (@dist/@unit);
SELECT
id, slug, latitude, longitude, councilId, @lon1, @lon2, @lat1, @lat2, @orig_lat, @orig_lon, @dist, @mylat, @mylon
from
entity
where
latitude
between
@lat1 AND @lat2
AND
longitude
between
@lon1 AND @lon2;
|
| Backup mysql databases and compress with LZMA, bash script - |
|---|
Bash Script to backup mysql and compress with LZMA
Compress with LZMA mode 2, fastest mode, low cpu, low ram.
Final backup will look like:
backup/mysql/YEAR/MONTH/{DB_NAME}_YYYMMMDDD_HHMMSS.sql.lzma
Example:
/backup/mysql/2010/07/mail_20100727_163027.sql.lzma
#!/bin/bash
# Script to backup mysql
# Compress with LZMA mode 2, fastest mode, low cpu, low ram.
# writen by Frederico Araujo
# http://frederico-araujo.com
#
# Final backup will look like:
# /backup/mysql/YEAR/MONTH/{DB_NAME}_YYYMMMDDD_HHMMSS.sql.lzma
# Example:
# /backup/mysql/2010/07/mail_20100727_163027.sql.lzma
# Mysql Settings
DB_USER="root"
DB_PASSWD="password"
# List of Databases to backup:
DATABASES="mydb mydb1 mydb2"
# Nice Level. Try to use less resources
NICE=17
# Backup Folder
BACKUP_DIR="/backup/mysql"
NOW="$(date +"%Y%m%d_%H%M%S")"
DATE_FOLDER="$(date +"%Y/%m")"
FINAL_FOLDER="${BACKUP_DIR}/${DATE_FOLDER}"
mkdir -p $FINAL_FOLDER
for DB in $DATABASES
do
SQL="${FINAL_FOLDER}/${DB}_${NOW}.sql"
nice -n $NICE /usr/bin/mysqldump -u$DB_USER -p$DB_PASSWD $DB >"${SQL}"
nice -n $NICE /usr/bin/lzma -2 -z "${SQL}"
chmod 600 "${SQL}.lzma"
echo "Creating backup of: ${DB}"
done
echo "Done"
exit 0
|
| Remove mysql duplicates - |
|---|
delete from table1
USING table1, table1 as vtable
WHERE (table1.ID >vtable.ID)
AND (table1.field_name=vtable.field_name)
|
| develop and production server on same php file - |
|---|
// same mysql connection file for development and production, or more servers
// can you use the global $_SERVER['HTTP_HOST'] to identify your current active server, simple huh??
// this simple scrip identify your server by address
// if you want more serves, just add a elseif clause
//first - your development server
if ($_SERVER['HTTP_HOST'] == "127.0.0.1")
{
$hostname_content = "localhost";
$database_content = "YOUR_DATABASE";
$username_content = "YOUR_USER";
$password_content = "YOUR_PASS";
$conn = mysql_pconnect($hostname_content, $username_content, $password_content) or trigger_error(mysql_error(),E_USER_ERROR);
}
//second- your production server
else
{
$hostname_content = "YOUR_PRODUTION_SERVER";
$database_content = "YOUR_PRODUCTION_DATABASE";
$username_content = "YOUR_PRODUCTION_USER";
$password_content = "YOUR_PRODUCTION_PASS";
$conn = mysql_pconnect($hostname_content, $username_content, $password_content) or trigger_error(mysql_error(),E_USER_ERROR);
}
?>
|
| convert data (mysql >br) - |
|---|
// convert data from mysql format (yyyy-mm-dd) to brazilian format (dd/mm/yyyy)
function bd_to_br($date){
$date_array = explode ("-", $date);
$date_ok = $date_array[2]."/".$date_array[1]."/".$date_array[0];
return($date_br);
}
?>
|
| convert data (br >mysql) - |
|---|
// convert the data from brazilian format(dd/mm/yyyy) to mysql format(yyyy-mm-dd)
function date_to_db($date){
$date_array = explode ("/", $date);
$date_ok = $date_array[2]."-".$date_array[1]."-".$date_array[0];
return($date_ok);
}
?>
|
| Remove tables widthout foreign key checks - |
|---|
//Muitas vez em ambiente de desenvolvimento em um servidor compartilhado ou com poucas permissões, precisa-se reinstalar as tabelas, seja por simular uma reinstalação, etc..., sem precisar(ou no meu caso, sem ter permissão) remover a basededados, em muitos caso não se consegue remover todas as tabelas sem executar este comando antes. Por causa das chaves estrangeiras.
SET FOREIGN_KEY_CHECKS =0;
DROP TABLE ... //comece a remover agora.
|
| drop all tables from mysql - |
|---|
//
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
|
| Replace weird characters in MySQL - |
|---|
Replace weird characters in MySQL database (usually caused by copy/paste from a Microsoft product)
update set = replace(replace(replace(replace(replace(replace(replace(replace(,'â??', '\''),
'â?¦', '...'),'â??', '-'),'â??', '"'),'â?', '"'),'â??', '\''),'â?¢', '-'),'â?¡', 'c');
|
| Script to check slave replication - |
|---|
#!/usr/bin/env bash
repeat_alert_interval=15 # minutes
lock_file=/tmp/slave_alert.lck
active=yes
## Check if alert is already sent ##
function check_alert_lock () {
if [ -f $lock_file ] ; then
current_file=`find $lock_file -cmin -$repeat_alert_interval`
if [ -n "$current_file" ] ; then
# echo "Current lock file found"
return 1
else
# echo "Expired lock file found"
return 2
fi
else
return 0
fi
}
## Find the location of the mysql.sock file ##
function check_for_socket () {
if [ -z $socket ] ; then
if [ -S /var/lib/mysql/mysql.sock ] ; then
socket=/var/lib/mysql/mysql.sock
elif [ -S /tmp/mysql.sock ] ; then
socket=/tmp/mysql.sock
else
ps_socket=`netstat -ln | egrep "mysql(d)?\.sock" | awk '{ print $9 }'`
if [ "$ps_socket" ] ; then
socket=$ps_socket
fi
fi
fi
if [ -S "$socket" ] ; then
echo UP >/dev/null
else
echo "No valid socket file "$socket" found!"
echo "mysqld is not running or it is installed in a custom location"
echo "Please set the $socket variable at the top of this script."
exit 1
fi
}
check_for_socket
Slave_IO_Running=`mysql -Bse "show slave status\G" | grep Slave_IO_Running | awk '{ print $2 }'`
Slave_SQL_Running=`mysql -Bse "show slave status\G" | grep Slave_SQL_Running | awk '{ print $2 }'`
Last_error=`mysql -Bse "show slave status\G" | grep Last_error | awk -F \: '{ print $2 }'`
if [ -z $Slave_IO_Running -o -z $Slave_SQL_Running ] ; then
echo "Replication is not configured or you do not have the required access to MySQL"
exit
fi
if [ $Slave_IO_Running == 'Yes' ] &&[ $Slave_SQL_Running == 'Yes' ] ; then
if [ -f $lock_file ] ; then
rm $lock_file
echo "Replication slave is running"
echo "Removed Alert Lock"
fi
exit 0
elif [ $Slave_SQL_Running == 'No' ] ; then
if [ $active == 'yes' ] ; then
check_alert_lock
if [ $? = 1 ] ; then
## Current Lock ##
echo "up" >/dev/null
else
## Stale/No Lock ##
touch $lock_file
echo "SQL thread not running on server `hostname -s`!"
echo "Last Error:" $Last_error
fi
fi
exit 1
elif [ $Slave_IO_Running == 'No' ] ; then
if [ $active == 'yes' ] ; then
check_alert_lock
if [ $? = 1 ] ; then
## Current Lock ##
echo "up" >/dev/null
else
## Stale/No Lock ##
touch $lock_file
echo "LOG IO thread not running on server `hostname -s`!"
echo "Last Error:" $Last_error
fi
fi
exit 1
else
if [ $active == 'yes' ] ; then
check_alert_lock
if [ $? = 1 ] ; then
## Current Lock ##
echo "up" >/dev/null
else
## Stale/No Lock ##
touch $lock_file
echo "Unexpected Error!"
echo "Check Your permissions!"
fi
fi
exit 2
fi
|
|
|
Jobs:
