Restore Base de Datos "Pesadas" en MySQL

Bueno... después de algunos años se me dió por escribir algo nuevamente; esta vez de la restauración de datos de MySQL.

Estuve luchando con la restauración de un dump SQL (de unos 13Gb aprox.) de una BDD MySQL 5.x, para lograr realizarlo en un tiempo razonable... Digo en un tiempo razonable, porque este restore me llevaba más de 1 día... algo que no era aceptable bajo ningún concepto para el negocio.

Empecé a ver comentarios por todos lados, pero nada me daba resultado, probé herramientas de terceros... pero tampoco era lo que estaba buscando... yo pretendía que usando mysql desde línea de comandos con el dump como entrada, con los parámetros indicados, pudiera hacer ese restore en no más de 2 o 3 horas...

Hasta que dí con lo siguiente, un script que tiene la paramétrización indicada para llegar al objetivo... y lo dejo por acá...


 #!/bin/sh

  # Guarda fecha de inicio
  inicio=`date`

  echo "Inicio restore: OK"
  # Ruta completa del dump de la base de datos a restaurar
  dumpfile="/home/backups/nombre_del_dump_a_restaurar.sql"

  ddl="set names utf8; "
  ddl="$ddl set global net_buffer_length=1000000;"
  ddl="$ddl set global max_allowed_packet=1000000000; "
  ddl="$ddl SET foreign_key_checks = 0; "
  ddl="$ddl SET UNIQUE_CHECKS = 0; "
  ddl="$ddl SET AUTOCOMMIT = 0; "
  # Si el archivo de dump no crea la base de datos, en la linea a continuación especificar la base de datos destino luego del USE
  ddl="$ddl USE facturaelectronicaweb_prueba2; "
  ddl="$ddl source $dumpfile; "
  ddl="$ddl SET foreign_key_checks = 1; "
  ddl="$ddl SET UNIQUE_CHECKS = 1; "
  ddl="$ddl SET AUTOCOMMIT = 1; "
  ddl="$ddl COMMIT ; "

# Setear contraseña
passw=contraseña_del_usuario_para_realizar_restore

  time mysql -h 127.0.0.1 -u root -p$passw -e "$ddl" &
  wait

  # Guarda fecha de fin
  fin=`date`

  echo "Inicio restore: $inicio"
  echo "Fin restore: $fin"


Espero que sea de utilidad y que bajen los tiempos de esos restore!
Cualquier sugerencia o modificación es muy bienvenida!
ÉXITOS!!!

Ahora PostgreSQL... Como otorgar permisos sobre todas las vistas?

Como hacemos para otorgar permisos sobre todas las vistas de un mismo schema en PostgreSQL?

No hay, o al menos no encontré, algo como GRANT SELECT ON ALL VIEWS TO usuario... lamentablemente hasta la version 9... imposible!

Algo que nos puede sacar del paso...

SELECT 'GRANT SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' TO "Usuario/Role";' FROM pg_views WHERE schemaname = 'NombreSchema';

Con esto, generamos todas las sentencias para otorgarle al "Usuario/Role", los permisos correspondientes sobre el schema "NombreSchema".

AWSTATS y JBoss

QUÉ ES AWSTATS?

(Copiado de Wikipedia, se nota no?... solo la introducción eh!!!... el resto es copiado de otro lado... je je)
AWStats es una herramienta open source de informes de análisis web, apta para analizar datos de servicios de Internet como un servidor web, streaming, mail y FTP. AWstats analiza los archivos de log del servidor, y con base a ellos produce informes HTML. Los datos son presentados visualmente en informes de tablas y gráficos de barra. Pueden crearse informes estáticos mediante una interfaz de línea de comando, y se pueden obtener informes on-demand a través de un navegador web, gracias a un programa CGI.
AWStats soporta la mayoría de los formatos de archivos log de servidor web conocidos, entre ellos Apache (formato de log NCSA combinado/XLF/ELF o formato común/CLFt), WebStar, IIS (formato de log del W3C) y muchos otros formatos comunes de Internet. Los desarrolladores pueden contribuir con el proyecto AWStats a través de SourceForge.



Instalación AWSTATS en Debian

Desde hacer un apt-get del paquete awstats
apt-get install awstats

Los archivos de configuración quedarán ubicados en /etc/awstats.

Previamente debemos tener instalado un servidor Apache en nuestro equipo.

En Apache:

vim /etc/apache2/sites-enabled/000-default

y agregar dentro de la configuracion del virtual host:

-VirtualHost *:80-
.
.

ScriptAlias /awstats/ "/usr/lib/cgi-bin/"
Alias /awstats-icon/ "/usr/share/awstats/icon/"


Options None
AllowOverride None
Order allow,deny
Allow from 127.0.0.1 ## ip permitida para ver los cambios
.
.
--VirtualHost--

Luego reiniciamos Apache:
/etc/init.d/apache2 restart


CONFIGURACIÓN DEL LOG JBOSS

En nuestro Server JBoss debemos habilitar el logging compatible para que AWSTATS pueda interpretarlo.

En server/default/deploy/jboss-web.sar/server.xml descomentar la entrada siguiente:

Valve className="org.apache.catalina.valves.AccessLogValve"
        prefix="localhost_access_log." suffix=".log" ...
        ...
        ...
        ...

CONFIGURACIÓN DE AWSTATS

Ahora es tiempo de crear nuestra primer archivo de configuración para un servidor determinado.


perl /usr/share/doc/awstats/examples/awstats_configure.pl

Luego seguimos los siguiente pasos:

Do you want to continue setup from this NON standard directory [yN] ?
y

Config file path ('none' to skip web server setup):
/etc/apache2/apache2.conf <----- archivo de configuracion de apache Do you want me to build a new AWStats config/profile file (required if first install) [y/N] ?
y

Your web site, virtual server or profile name:

www.midominio.com

Directory path to store config file(s) (Enter for default):

dejar en blanco + enter

Si nos sale este error:
Error: Failed to open '/etc/awstats/wwwroot/cgi-bin/awstats.model.conf' for read.

hacer lo siguiente:

cd /etc/awstats/

mkdir -p wwwroot/cgi-bin
cp /usr/share/doc/awstats/examples/awstats.model.conf.gz wwwroot/cgi-bin/cd wwwroot/cgi-bin/
gunzip awstats.model.conf.gz

y luego realizar el paso 2 (tuve que ejecutar dos veces seguidad :( ) y escribir todo nuevamente.

luego si se corrigio ese error le damos enter hasta finalizar.

Bien unas ves que se ha creado nuestro archivo de configuracion entramos a:

cd /etc/awstats

y verificamos que este nuestro archivo, hacemos:

vi awstats.www.midominio.com.conf

una vez abierto el fichero buscamos los siguiente parámetros a configurar:

LogFile="opt/jboss/jboss5/server/default/log/localhost_access_log.%YYYY-0-%MM-0-%DD-0.log""

aquí se encuentra el log que configuramos anteriormente (con Valve) de nuestro JBoss...

LogType=W (para servidor web)
LogFormat=4 (para apache en general)
DirData="/var/lib/awstats" (esto debe existir previamente)

Luego ejecutamos el siguiente comando para que se empieze a generar nuestro reporte:

/usr/lib/cgi-bin/awstats.pl -update -config=www.midominio.com

Si el dominio es diferente simplemente lo cambiamos por el correspondiete.

Una vez finalizado el proceso de actualización de las estadísticas, vamos a nuestro browser y llamamos a la URL:

http://localhost/awstats/awstats.pl?config=www.midominio.com

En fin... debería haber quedado funcionando... de no ser así... a investigar un poquito más... ÉXITOS!!!

Shrink Space

Hola! Se me ocurrió escribir esto, que aunque a primera vista sea muy sencillo, en alguna otra ocasión nos puede salvar... al menos tener las sentencias a mano en caso de necesitarlas.
Qué sucede cuando tenemos una tabla que pesa bastante... digamos 3, 4 o 5 Gigas o quizás no tanto... y eliminamos de un saque (via delete) un porcentaje importante de filas?... La respuesta es fácil... todo ese espacio alocado (traducción a lo indio de allocated) para ese tabla no es liberado... y sigue ocupando el mismo espacio en disco!... Bien... Cómo hacemos para liberarlo?

Sencillo:

ALTER TABLE schema.NOMBRE_TABLA enable row movement;

ALTER TABLE schema.NOMBRE_TABLA SHRINK SPACE;

ALTER TABLE schema.NOMBRE_TABLA disable row movement;

Con ésto, deberíamos poder recuperar ese espacio inutilizado... Espero haya sido de utilidad!

P.D.: si la tabla tiene índice function-based esto no nos será posible, lo que haríamos en ese caso es eliminar el o los índices, ejecutar las sentencias anteriores, y volver a crear los índices function-based .

ORA-39087: directory name DATAPUMPDIR is invalid

Si al intentar realizar un expdp obtenemos este error... les sugiero que chequeen el user con el que están tirando la tarea tenga permisos sobre el directorio en el cual se va a volcar el dump...

SELECT * FROM ALL_DIRECTORIES -- chequeamos los directories existentes
SYS | DATAPUMPDIR | /u01/app/oracle/oracle/product/10.2.0/db_1/rdbms/log/

SELECT *  -- chequeamos los permisos que tienen los usuarios sobre los directories
FROM user_tab_privs
WHERE table_name = 'DATAPUMPDIR'

-- asignamos permisos al usuario correspondiente, para que pueda acceder al directory específico

GRANT READ, WRITE on DIRECTORY DATAPUMPDIR to USUARIO1;

Listo! Ahora sí! Corremos el expdp y no deberíamos tener problemas... Suerte!

Consultas útiles

Aquí van algunas de esas consultas que nos podrían ser útiles en el día a día.
Copien, peguen y guarden bien a mano... Espero les sean de utilidad...

•• Consulta Oracle SQL sobre la vista que muestra el estado de la base de datos:
select * from v$instance

•• Consulta Oracle SQL que muestra si la base de datos está abierta
select status from v$instance

•• Consulta Oracle SQL sobre la vista que muestra los parámetros generales de Oracle
select * from v$system_parameter

•• Consulta Oracle SQL para conocer la Versión de Oracle
select value from v$system_parameter where name = 'compatible'

•• Consulta Oracle SQL para conocer la Ubicación y nombre del fichero spfile
select value from v$system_parameter where name = 'spfile'

•• Consulta Oracle SQL para conocer la Ubicación y número de ficheros de control
select value from v$system_parameter where name = 'control_files'

•• Consulta Oracle SQL para conocer el Nombre de la base de datos
select value from v$system_parameter where name = 'db_name'

•• Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle.
Para visualizarla es necesario entrar con privilegios de administrador
select osuser, username, machine, program
from v$session
order by osuser

•• Consulta Oracle SQL que muestra el número de conexiones actuales a Oracle agrupado
por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc

•• Consulta Oracle SQL que muestra los usuarios de Oracle conectados y el número
de sesiones por usuario
select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc

 •• Consulta Oracle SQL que muestra propietarios de objetos y número de objetos por
propietario
select owner, count(owner) Numero
from dba_objects
group by owner

•• Consulta Oracle SQL sobre el Diccionario de datos (incluye todas las vistas y
tablas de la Base de Datos)
select * from dictionary

•• Consulta Oracle SQL que muestra los datos de una tabla especificada (en este caso
todas las tablas que lleven la cadena "XXX"
select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'

•• Consulta Oracle SQL para conocer las tablas propiedad del usuario actual
select * from user_tables

•• Consulta Oracle SQL para conocer todos los objetos propiedad del usuario
conectado a Oracle
select * from user_catalog

•• Consulta Oracle SQL para el DBA de Oracle que muestra los tablespaces, el espacio
utilizado, el espacio libre y los ficheros de datos de los mismos:
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC

•• Consulta Oracle SQL para conocer los productos Oracle instalados y la versión:
select * from product_component_version

•• Consulta Oracle SQL para conocer los roles y privilegios por roles:
select * from role_sys_privs

•• Consulta Oracle SQL para conocer las reglas de integridad y columna a la que
afectan:
select constraint_name, column_name from sys.all_cons_columns

•• Consulta Oracle SQL para conocer las tablas de las que es propietario un usuario,
en este caso "xxx":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx'

•• Consulta Oracle SQL como la anterior, pero de otra forma más efectiva (tablas de
las que es propietario un usuario):
SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'HR'

•• Parámetros de Oracle, valor actual y su descripción:
SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1

•• Consulta Oracle SQL que muestra los usuarios de Oracle y datos suyos (fecha de
creación, estado, id, nombre, tablespace temporal,...):
Select * FROM dba_users

•• Consulta Oracle SQL para conocer tablespaces y propietarios de los mismos:
select owner, decode(partition_name, null, segment_name,
segment_name || ':' || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
from dba_segments
Where 1=1 And extents > 1 order by 9 desc, 3

•• Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
select distinct vs.sql_text, vs.sharable_mem,
vs.persistent_mem, vs.runtime_mem, vs.sorts,
vs.executions, vs.parse_calls, vs.module,
vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.users_opening, vs.loads,
to_char(to_date(vs.first_load_time,
'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value ,
rows_processed , vs.command_type, vs.parsing_user_id ,
OPTIMIZER_MODE , au.USERNAME parseuser
from v$sqlarea vs , all_users au
where (parsing_user_id != 0) AND
(au.user_id(+)=vs.parsing_user_id)
and (executions >= 1) order by buffer_gets/executions desc

•• Consulta Oracle SQL para conocer todos los tablespaces:
select * from V$TABLESPACE

•• Consulta Oracle SQL para conocer la memoria Share_Pool libre y usada
select name,to_number(value) bytes
from v$parameter where name ='shared_pool_size'
union all
select name,bytes
from v$sgastat where pool = 'shared pool' and name = 'free memory'

•• Cursores abiertos por usuario
select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3

•• Consulta Oracle SQL para conocer los aciertos de la caché (no debería superar el
1 por ciento)
select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

•• Sentencias SQL completas ejecutadas con un texto determinado en el SQL
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece

•• Una sentencia SQL concreta (filtrado por sid)
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and sid = 105
ORDER BY c.sid, d.piece

•• Consulta Oracle SQL para conocer el tamaño ocupado por la base de datos
select sum(BYTES)/1024/1024 MB from DBA_EXTENTS

•• Consulta Oracle SQL para conocer el tamaño de los ficheros de datos de la base
de datos
select sum(bytes)/1024/1024 MB from dba_data_files

•• Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta sin
incluir los índices de la misma
select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'

•• Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta
incluyendo los índices de la misma
select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))

•• Consulta Oracle SQL para conocer el tamaño ocupado por una columna de una tabla
select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA

•• Consulta Oracle SQL para conocer el espacio ocupado por usuario
SELECT owner, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by owner

•• Consulta Oracle SQL para conocer el espacio ocupado por los diferentes segmentos
(tablas, índices, undo, rollback, cluster, ...)
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_TYPE

•• Consulta Oracle SQL para obtener todas las funciones de Oracle: NVL, ABS, LTRIM,...
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD'
order by object_name

•• Consulta Oracle SQL para conocer el espacio ocupado por todos los objetos de la base de
datos, muestra los objetos que más ocupan primero
SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_NAME
order by 2 desc

Caracter de escape

Como ya bien sabemos % y _ son caracteres de escape que sirven para generar patrones.

% = equivale a O o N caracteres

_= equivale a O o 1 caracter

Nos podemos encontrar en situaciones en las que en el WHERE de una consulta queramos buscar caracteres que contengan _ o % por lo que necesitaremos un caracter de escape que le diga a Oracle que esos dos  caracteres no son caracteres especiales. 

Se hace de esta forma:

SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';