::Albertux::Blog::

MySQL 5.1

“We ran a side by side comparison between MySQL and Oracle both partitioned and unpartitioned. MySQL outperformed Oracle at least 2:1 in all like for like tests and, at the extreme, well partitioned MySQL was over 31 times faster than vanilla Oracle tables.”

Guy Adams

Chief Technical Officer

Parallel Ltd.

Downloads:

http://dev.mysql.com/downloads/mysql/5.1.html#downloads

MySQL Workbench:

http://dev.mysql.com/downloads/workbench/5.1.html

GUI Tools:

http://dev.mysql.com/downloads/gui-tools/5.0.html

Connectors:

http://www.mysql.com/products/connector/

database, develop mysql

MySQL Workbench (Linux)

What is MySQL Workbench ?

MySQL Workbench is a cross-platform, visual database design tool developed by MySQL. It is the highly anticipated successor application of the DBDesigner4 project. MySQL Workbench will be available as a native GUI tool on Window, Linux and OS X.

For Ubuntu (8.04) users i made this install script (run as root):

#!/bin/bash
# This script tested only on Ubuntu 8.04 (32 bits)
# You need some extra libs to run MySQL Workbench
sudo apt-get install liblua5.1-0 libzip1 libmysqlclient15off
# Download MySQL Workbench Ubuntu 8.04 Bin Files
wget -c ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.2-alpha-ubuntu8-i386.tar.gz
# Download the md5 checksum file
wget -c ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-workbench-5.1.2-alpha-ubuntu8-i386.tar.gz.md5
# Check md5sum
real=`md5sum mysql-workbench-5.1.2-alpha-ubuntu8-i386.tar.gz | awk '{ print $1 }'`
confirm=`cat mysql-workbench-5.1.2-alpha-ubuntu8-i386.tar.gz.md5 | awk '{ print $1 }'`
if [ "$real" = "$confirm" ]
then
	# Extract (/usr/local/bin/ /usr/local/lib/mysql-workbench/ /usr/local/share/mysql-workbench/)
	tar xvzf mysql-workbench-5.1.2-alpha-ubuntu8-i386.tar.gz -C /
else
	echo "Wrong MD5, run the script again."
fi

Note: MySQL Workbench is alpha on Linux

More information about MySQL Workbench

database, develop bash, mysql, tool, ubuntu

GeoIP Database MySQL

Sometimes we need to know the country of the users on the website, you can know using only the ip address.
I write this simple Bash script to install on a new database (or existing database) the table “geoip”, now you can use PHP, Python, Perl, Ruby or whatever you want.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

#!/bin/bash
# Script: geoip.sh
# Author: Alberto Isaac Ayala Esquivias
# E-mail: <albertoi7@gmail.com>
# Web: http://albertux.ayalasoft.com
clear
echo "=====================================-"
echo " GeoIp MySQL Database Easy Installer. "
echo "======================================"
echo ""
echo "Script Created By http://AyalaSoft.com";
echo ""
stty -echo
read -p "MySQL root password: " mysql_pass; echo
stty echo
echo -n "Do you want to create database of MySQL (y/n): "
read op
if [ "$op" = "y" ]; then
echo -n "Create new database, name: "
	read database
	mysqladmin -u root --password=$mysql_pass create $database
else
echo -n "Which database do you want to use to insert 'geoip' table: "
read database
fi
echo -e "\nDownload  [ GeoIPCountryCSV.zip ]"
wget -c "http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip"
echo -e "\nUnZip [ GeoIPCountryCSV.zip ]"
unzip GeoIPCountryCSV.zip
echo -e "\nInsert geoip table to database $database"
awk -F, ' { print $3","$4","$5","$6 }' GeoIPCountryWhois.csv | sed s/\"//g > /tmp/geoip.txt
chmod 777 /tmp/geoip.txt
echo "
USE $database;
 
DROP TABLE IF EXISTS geoip;
 
CREATE TABLE geoip (
	ip_begin int unsigned,
	ip_end int unsigned,
	country varchar(2),
	country_name varchar(30)
);
 
LOAD DATA LOCAL INFILE '/tmp/geoip.txt'
INTO TABLE $database.geoip
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ip_begin, ip_end, country, country_name);
" > geoip.sql
mysql -u root --password=$mysql_pass $database < geoip.sql
rm /tmp/geoip.txt
echo -e "\nFinish."

database, develop, network, unix/linux bash, geoip, mysql

CSV to SQLite3 a Real Example

Supongamos que tenemos una base de datos grande en formato CSV (delimitado por comas) y queremos importarla a un DBMS debemos primero crear la estructura de la tabla a la cual vamos a insertar los datos del archivo CSV, en mi caso use SQLite3:

sqlite> .separator ,
sqlite> .import ./importfile.csv test_table

Si hubieramos preferido MySQL:

mysql> LOAD DATA LOCAL INFILE ‘/importfile.csv’
-> INTO TABLE test_table
-> FIELDS TERMINATED BY ‘,’
-> LINES TERMINATED BY ‘\n’
-> (field1, filed2, field3);

Ojo si quieren usar la base de datos del código postal Mexicano en formato CSV, van a tener que usar VIM o su editor favorito para corregir algunas lineas que agregan comas de mas.

develop csv, mysql, sqlite

Play Ruby On Rails

Hace tiempo que quería probar este lenguaje/framework hoy decidí probarlo antes de ir al trabajo, la verdad es que estoy molesto, no por ROR sino por no haberlo usado antes.

Solo tengo que generar la base de datos (en mi caso use MySQL pero tiene por default SQLite) editando el “config/database.yml”, estoy seguro de que no programe nada solo escribí estas 2 lineas en la shell:

1) ruby script\generate scaffold usuario id:integer usuario:string nombre:string pass:string correo:string fecha:string status:integer
2) ruby script\server

y con eso es mas que suficiente para que me genere controlador, modelo, vista y claro con eso tener una pagina que liste los registros, inserte uno nuevo, lo edite y lo elimine, aqui un par de screenshots:  usuario_nuevo y usuario_editar

Conclusión: tengo que aprender ROR.

develop mysql, ruby, sqlite