Install Mysql
#apt-get install mysql-server
Inicialmente
#mysql -h localhost -u root -p*********
mysql> create database miBD
Exportar BD
#mysqldump -u root -p****** nom_BD > nom_DB.sql
#mysqldump -u [usuario] -p[clave] [nombredelabasededatos] > BACKUP.sql
Importar BD
Antes que nada revisar el /etc/my.cnf
y modificar o agregar los parametros max_allowed_packet de 16M a 1024M ademas del
read-only = 1 por read-only = 0 para poder importat backups de mayor tamaño
read-only = 0
max_allowed_packet=1024M
mysql> show databases;
mysql> use midatabase;
mysql> source /INF/nom_DB.sql
#mysql -u [usuario] -p[clave] [nombredelabasededatos] < BACKUP.sql
Asignar Usuarios de Conexion
mysql> create user 'usertest'@'*' identified by 'passtest';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON `basededatos`.* TO 'usertest'@'*';
GRANT SELECT ON *.* TO 'usertest'@'%' IDENTIFIED BY 'passtest';
Query OK, 0 rows affected (0.02 sec)
##########################################################################################################
•Arranque de los servidores.
Servidor Linux:
safe_mysqld –user=mysqladm &
root &
Servidor Windows:
Mysqld
Arranque como usuario:
mysql –u usuario –p clave
En el caso en el que el servidor no se encuentre en el ordenador que estamos trabajando la
orden de uso será:
mysql –h nombre_host –u usuario –p clave
•Consultas generales sobre las bases de datos
Mostrar las bases de datos:
show databases;
Mostrar las bases de datos seleccionadas:
select databases();
Mostrar las tablas que contiene una base de datos:
show tables;
Seleccionar una base de datos:
use nombre_base;
Describir la estructura de campos de una tabla:
describe nombre_tabla;
•Creación de bases de datos, tablas y registros
Crear una base de datos:
create database nombre_base;
Creación de una tabla:
create [temporary] table [if no exists] nombre_tabla (
nombre_campo tipo1 opciones2 cláusulas3,
“
“
“ ,
“
“
“ ,
[último campo] );
[temporary] --> la tabla existirá mientras exista la conexión con el cliente actual o hasta que se
emita la instrucción drop table.
[if no exist] --> si existe la tabla no se crea una nueva.
1) Las posibles opciones de tipo de campo son:
tinyint --> 1 byte
smallint --> 2 byte
mediumint --> 3 byte
int --> 4 byte
bigint --> 8 byte
float --> 4 byte
double --> 8 byte
decimal --> variable
char(n) --> cadena de caracteres de longitud fija
varchar(n) --> cadena de caracteres de longitud variables
tinyblob --> objeto binario largo (muy pequeño)
blob --> objeto binario largo (pequeño)
mediumblob --> objeto binario largo (medio)
longblob --> objeto binario largo (grande)
tinytext --> cadena de texto muy pequeña
text --> cadena de texto pequeña
mediumtext --> cadena de texto media
longtext --> cadena de texto larga
enum --> una enumeración
set --> un conjunto
date --> valor fecha (aaaa-mm-dd)
time --> valor de hora (hh-mm-ss)
datetime --> valor de fecha y hora
timestamp --> valor de lapso de tiempo (aaaammddhhmmss)
year --> valor de año
(2) Las posibilidades del apartado opciones son:
Generales:
null --> admite valores nulos
no null --> rechaza dejar el campo en blanco
default --> permite establecer un valor por defecto
Columnas numéricas:
auto_increment --> para generar identificadores únicos o valores en serie.
unisgned --> rechazo de valores negativos
Cadena:
binary --> trata los valores como cadenas binarias (campos char y varchar)
(3) Cláusulas:
primary key --> columna indexada para búsqueda rápida. Clave principal, solo puede
haber una
unique --> crea un índice con valores únicos
index, key --> son sinónimos y crean índices que pueden contener valores repetidos.
Inserción de registros en una base de datos:
insert [low_priority | delayed][ignore][into] nombre_tabla forma_introducción1
I. LOW_PRIORYTY | DELAYED: la primera de las opciones hace que la inserción sea
postergada hasta que ningún cliente utilice la tabla. La segunda genera una cola de
introducción de registros, que se añadirán a la tabla cuando esta quede libre de usuarios.
II. IGNORE: se desechan las filas que duplican valores para claves únicas
III. INTO: se requiere en versiones 3.22.5.
(1)Las formas de introducción son:
(lista_columnas) VALUE (lista_valores_o_expresión)
(lista_columnas) SELECT (opción_de_recuperación)
SET (nombre_columna=expresion, ....)
Uso de una base de datos
Use database;
•Consulta de una base de datos:
La forma general del comando de consulta es:
select opciones1 lista2 cláusulas3;
(1) Tipos de opciones permitidas:
all --> provoca el regreso de todas las filas
distinct, distinctrow --> especifican que las líneas duplicadas deben ser eliminadas
high_prioruty --> da mayor prioridad a la instrucción en el caso de espera.
sql_byg_result, sql_small_result --> especifican que el conjunto de resultados será mas largo
o más corto
straignt_join --> fuerza a las tablas a unirse en el orden citado en la cláusula from
(2) Lista --> especifica las columnas que deben devolverse, el símbolo “*” especifica que deben
extraerse todas las columnas. La separación entre los diferentes nombres de columnas se
hacen con el símbolo “,”.
(3)Cláusulas (en caso de haber varias cláusulas deben seguir el orden expuesto en la lista):
Intro outfile ‘nombre_fichero’ opciones --> el resultado de la consulta es transferido a un
fichero, paro lo cual debemos tener privilegio de crear el fichero y además este no debe
existir. Las opciones de importanción son las siguientes:
[fields [terminate by ‘cadena’]a [opcionally] [enclosed by ‘carácter’]b [escape by ‘carácter’]c]
[lines terminated by ‘carácter’] d
a)especifica el carácter o caracteres que delimitan valores dentro de la línea
b)especifica un carácter entrecomillado que se quita del final del campo de valores.
OPTIONALLY establece que los valores solamente se entrecomillan para las
columnas char y varchar
c)Se utiliza para especificar el carácter de escape especial
d)Especifica un carácter o caracteres que determinan el final de la línea.
Los caracteres especiales son los siguientes:
-\0 ascii 0
-\b retroceso
-\n línea nueva
-\r retorno de carro
-\s espacio
-\t tabulación
-\’ comilla simple
-\” comilla doble
-\\ barra inversa
from tabla-tablas --> especifica una o varias tablas desde las que se obtendrán las filas.
Existen varias posibilidades de unión de tablas (mirar libro de mysql).
where expresión --> establece una expresión que se aplica a las filas seleccionadas
group by lista_columnas --> agrupa filas del conjunto de resultados de acuerdo con las
columnas citadas.
having expresión --> especifica una expresión secundaria para limitar filas después de
satisfechas las condiciones expuestas en la cláusula where.
order by entero_unsigne|nombrecoluma|fórmula --> indica como se va a clasificar el conjunto
de resultados. Las posibilidades son:
asc – ascendente
desc – descendente
rand() – orden aleatorio
procedure nombre_procedimiento --> indica un nombre de procedimiento al que serán
enviados los datos antes de su salida.
•
Alteración de una tabla de datos y registros:
Modificación de la estructura de las tablas: permite renombrar la tabla o variar la estructura de la
tabla de datos. La sintaxis base es:
alter [ignore] table nombre1 lista_de_acción2
La cláusula [ignore] se utiliza si existen datos duplicados en los valores de las claves de los
índices, de esta forma no se eliminaran una vez introducido el primero.
(1) Nombre: establece el nombre de la tabla sobre la que deseamos actuar.
(2)Lista_de_acción: establece una o varias acciones a realizar, las posibilidades son:
a)ADD INDEX [nombre _ índice][columna _ índice]: añade un índice a la tabla basado en la
columna especificada. Si se especifican varias columnas deben ir separadas por comas.
b) ADD PRIMARY KEY [columna _ índice]: añade una clave principal siguiendo la columna
establecida.
c) ADD UNIQUE [nombre _ índice][columna _ índice]: añade un índice de valor único a la
tabla en función de la columna especificada.
d) ALTER [column] nombre _ columna (set default valor | drop default): modifica el valor dedo
de una columna o reduce su valor predeterminado actual.
e) CHANGE [column] nombre _ columna declaración _ columna: cambia el nombre y la
definición de una columna; nombre _ columna -> nombre de la columna a modificar,
declaración _ columna -> es el valor de definición por el cual se debe regir la columna, sus
opciones son las mismas que las señaladas para la creación de los campos de las tablas.
f) DROP [column] nombre _ columna: elimina la columna especificada.
g) DROP INDEX nombre_index: elimina el índice de la tabla.
h) DROP PRIMARY KEY: elimina la clave principal de la tabla. Si no existe clave principal y si
índices con especificación UNIQUE se elimina el primero existente.
i) MODIFY [column] declaración _ columna: cambia la declaración de una columna.
j)RENAME [AS] nombre_tabla_nueva: cambia el nombre de la tabla.
Borrado de registros: para borrar filas de una tabla se utiliza la expresión:
delete [low_priory] from nombre_tabla [where expresión][limit n]
LOW_PRIORY: posterga la petición hasta que ningún usuario este utilizando la tabla.
WHERE expresión: especifica una condición que deben cumplir los registros a borrar, si se
omite en la petición se eliminan todos los registros de la tabla.
LIMIT n: estable un número máximo de registros a borrar.
Sustitución de registro: permite modificar registros existentes.
remplace (sigue la misma sintaxis que INSERT).
La salvedad se refiere a los valores correspondientes a índices unique, si el valor existe en la
tabla previamente a la inserción de la modificación se eliminara.
Mitificación de registros en bloque:
Updates [low_priority] nombre_tabla set nombre_col=expresion, ....[where
expresión_where][limit n]
Borrado de tablas: elimina una o varias tablas:
DROP TABLE [if exists] (lista_tablas_separadas_po_comas)
Borrado de una base de datos. Elimina la base de datos y todas las tablas que contiene.
DROP DATABASE [if exist] nombre_database
Optimización de tablas: optimiza el espacio asignado a la tabla.
OPTIMIZE TABLE nombre_tabla
•
Índices, creación, modificación y eliminación
Un índice corresponde a un método de ordenación de una o varias columnas que permite a los
motores de búsqueda de bases de datos trabajar de forma eficiente y rápida.
Los índices se pueden crear al crear las tablas con la instrucción create table o bien con
posterioridad a través del comando alter table o el comando:
create [UNIQUE] index nombre _ índice ON nombre_tabla (columna _ índice)
La eliminación de un índice se realiza por el comando:
drop index nombre_index on nombre_tabla
•
Comandos básicos MYSQL
Instrucciones para mostrar información
Para mostrar las bases de datos disponibles:
show databases [like patrón _ búsqueda]
Para mostrar las tablas de una base de datos:
show tables [from nombre_base_datos][like patrón _ búsqueda]
Para mostrar información descriptiva de una tabla:
show table status [from nombre_base-datos][like patrón _ búsqueda]
El resultado obtenido muestra:
Name: nombre de la tabla.
Tipe: tipo de tablas
Row format: formato de almacenamiento de la fila
Rows: número de filas
Avg_row_length: promedio de bytes usados por las filas de la tabla
Data_length: tamaño actual en bytes de fichero de tabla
Max_data_length: tamaño máximo en bytes que puede alcanzar el archivo.
Index_length: tamaño actual en bytes del archivo de índice.
Data_free: número de bytes en el archivo de datos que no se utilizan.
Auto_increment: el siguiente valor generado por una columna con la propiedad.
Create_time: hora que se creo la tabla.
Update_time: hora de la última modificación
Check_time: última vez que la tabla fue reparada o revisada por myisamchk
Create_options: opciones adicionales especificadas.
Comment: comentarios aparecidos en la creación de la tabla.
Para mostrar las diferentes columnas de una tabla:
show columns from nombre_tabla [from nombre_base-datos][like patrón-búsqueda]
El resultado de la petición muestra:
Field: nombre de la columna.
Type: el tipo de columna.
Null: si la columna puede contener valores nulos.
Key: si la columna está indexada.
Default: el valor por defecto.
Extra: información extra de la columna:
Privileges: los privilegios de la columna.
Para mostrar los índices que contiene una tabla se utiliza las instrucciones:
show index from nombre_tabla
show keys from nombre_tabla
El resultado de la petición se muestra en:
Table: el nombre de la tabla que contiene el índice.
Nom_unique: 1 si puede contener valores duplicados.
Key_name: El nombre del índice.
Seq_in_index: el número de columnas en el índice.
Column_name: el nombre de la columna.
Collation: Orden de clasificación de la columna dentro del índice.
Cardinality: El número de valores únicos en el índice.
Sub_part: La longitud del prefijo.
Packed: si toda la clave está empaquetada.
Comment: Reservado para comentarios internos sobre los índices.
Las siguientes variantes de la orden show están relacionadas con la administración del
entorno:
Información sobre usuarios:
show grants for nombre_usuario
Información sobre los procesos del servidor:
show processlist
La salida de la información se estructura en los campos:
Id: el no id del hilo para el cliente:
User: el nombre del cliente asociado al hilo.
Host: el host desde el que se conecta.
Db: la base de datos predeterminada para el hilo.
Command: el comando que se ejecuta en el hilo
Time: el tiempo en segundos que utiliza el comando.
State: Información de lo que hace Mysql mientras se ejecuta una instrucción SQL
Info: ejecuta la consulta.
Información sobre las variables de estado del servidor:
show status
La información dada por el servidor es muy alta:
Aborted_clients: número de conexiones de un cliente canceladas
Aborted_connects: número de intentos fallidos de conexión al servidores.
Connectios: número de intentos realizados para conectarse al servidor.
Created_tmp_tables: número de tablas temporales creadas mientras se procesan las
consultas.
Delayed_errors: no de errores ocurridos mientras se procesan filas insert delayed.
Delayed_insert_threads: no de errores actuales del mensaje insert delayed.
Delayed_writes: no de filas insert delayed introducidas.
Flush_commands: no de columnas flush que se han ejecutado.
Hendler_delete: no de solicitudes necesarias para suprimir una fila de una tabla.
Hendler_read_first: no de solicitudes para leer la primera fila de la tabla.
Handler_read_key: no de solicitudes para leer una fila basada en un valor de índice.
Handler_read_next: no de solicitudes para leer la siguiente fila basada en un valor de
índice.
Handler_read_md: no de solicitudes para leer una fila basada en su posición.
Handler_update: no de solicitudes para actualizar una fila.
Handler_write: no de solicitudes para insertar una fila.
Key_blocks_use: no de bloques en uso en la cache de índice.
Key_read_request: no de solicitudes para leer un bloque desde la cache.
Key_reads: no de lecturas físicas de los bloques de índice desde el disco.
Key_write_requests: no de requisitos para escribir un bloque en la cache de índice.
Key_writes: no de escrituras físicas de los bloques de índices de disco.
Max_used_connections: no máximo de conexiones que se han abierto
simultáneamente.
Not_flushed_delayed_rows: no de filas a la espera de ser escritas por las consultas
insert delayed.
Not_flushed_key_blocks: no de bloques en la cache de clave que han sido
modificados pero aun no han sido vaciados del disco.
Open_files: no de archivos abiertos.
Open_streams: no de flujos abiertos.
Open_tables: no de tablas abiertas.
Opened_tables: total de tablas que han sido abiertas.
Questions: no de consultas que ha recibido el servidor.
Slow_queries: no de consultas que tardan más segundos en ejecutarse que
long_query_time.
Threads_connected: no de conexiones abiertas en ese momento.
Threads_running: no de hilos que están latentes.
Uptime: Segundos desde que el servidor comenzó a ejecutarse.
Para ver las variables en el servidor se utiliza la forma:
show variables
Administración:
•Mysqladmin:
* mysqladmin [opciones] comando
opciones generales:
--compres, -C -> activa la compresión para el protocolo utilizado en el protocolo cliente-servidor.
--debug=opcion_depuración, -# opciones _ depurador -> activa la salida del depurador.
--host =nombre_depurador, -h nombre_host -> especifica a que host debemos conectarnos.
--port=número_puerto -P número _ puerto -> para los programas clientes este el número de puerto
del servidor al que deben conectarse.
--user=nombre_usuario, -u nombre _ usuario -> nombre del usuario que conecta con el servidor.
--password , -p ->clave de usuario para acceder al servidor.
--silent, -s -> especifica modo silencioso, es decir el programa produce menos mensajes de lo
habitual.
--version, -V -> muestra la versión del programa.
--help, -¿ -> nuestra mensajes de ayuda.
--pipe, -W ->utiliza una tubería con nombre para conectarse al servidor (solamente se utiliza por
programas clientes funcionando bajo Windows).
--socket=nombre_ruta, -S nombre_ruta -> para los programas clientes indica el fichero de socket que
deben usar cuando se conectan al servidor.
opciones específicas:
--force, -f -> hace que no se pida confirmación cuando se ejecuta drop db_nombre y cuando se
ejecutan múltiples comando intenta ejecutar todos los comandos aunque se produzcan errores.
--relative ->muestra la diferencia de los valores anterior y posterior cuando se ejecuta –sleep.
----sleep=n, -i n -> ejecuta los comandos dados en la línea de comandos repetidamente cada n
segundos.
--wait[=n], -w [n] -> establece el número de veces a esperar y reintentar si no puede establecer
conexión con el servidor.
Comandos:
create db_name -> crea una base de datos con el nombre dado.
drop db_name -> borra la base de datos con el nombre dadoy cualquier tabla que exista en ella.
flush_host -> vacía el fichero log del servidor.
flush_status -> limpia las variables de estado.
flush_tables ->vacia la cache de tablas.
kill id, id .. -> mata los procesos del servidor.
password new -> cambia la contraseña de usuario.
ping -> comprueba si el servidor se está ejecutando.
process list -> muestra una lista de datos.
refresk -> vacia la cache de las tablas.
reload -> recarga la tabla de permisos.
shutdown -> desconecta el servidor.
status -> muestra el estado del servidor.
variables -> muestra los valores y nombres de la variable.
version -> version del servidor.
•Creación de privilegios de usuarios:
* grant [privilegio] [columna] on [nivel] to [usuario] identified by “contraseña” with grant
option
privilegios -> si hay varios privilegios se separan por comas.
alter ->alterar tablas e índices.
create -> crear bases de datos y tablas.
delete -> borrar de las tablas registros.
drop -> eliminar bases de datos y tablas.
index -> crear o eliminar índices.
insert -> insertar nuevos registros en las tablas.
references -> no se utiliza.
select -> recuperar registros de tablas.
update -> modificar registros de las tablas.
file -> leer o escribir archivos del servidor.
proces -> ver información sobre los hilos en ejecución dentro del servidor y poder matarlos.
reload -> recargar tablas de concesión.
shutdouwn -> cerrar el servidor.
all -> cualquier cosa.
usage -> un privilegio especial “sin privilegios”.
Columna: indica las columnas a las que se aplicarán los privilegios y es opcional.
Nivel: especifica a que se otorgan los privilegios, que pueden ser globales, de bases de datos o de
tablas. Si se especifica *.* indica todas las tablas de todas las bases de datos; nombre_base.*
indica todas las tablas de la base de datos; nombre_base.nombre_tabla especifica una tabla de
una determinada base de datos.
Usuario: el usuario al que se le otorgan los privilegios y consiste en un usuario y un host
(usuario@host); si no se especifica host se entiende que puede ser desde cualquiera y si no se
especifica usuario puede ser cualquiera (anónimo).
Contraseña: la clave de acceso del usuario.
With grant option: se le permite al usuario dar privilegios a otros usuarios. Es una cláusula opcional.
Revocar privilegios:
revoke [privilegios] [columna] on [nivel] from [usuario]
Eliminar usuarios:
delete from user where user=[usuario] and host=”nombre_host”
Después de eliminar un usuario es necesario recargar la tabla de usuarios
flush privileges;
•Copias de seguridad
mysqldump [nombre de la base] > [fichero de salida]
Es una orden externa del programa que genera una copia en texto plano de las tablas de la base
de datos con todo el conjunto de instrucciones para volver a crearlas e incluir los registros existentes.
La recuperación de la base de datos se realiza de forma simple:
mysql < [nombre fichero de copia de seguridad]
Para obtener una recuperación rápida sin necesidad de operaciones previas debemos introducir
en la cabecera del fichero las líneas:
CREATE DATABASE [nombre de la base de datos];
USE [nombre base de datos];
Operaciones diversas:
EXPLAIN
explain nombre_tabla1
explain sentencia_select2
(1)equivale a la orden que informa sobre las columnas de una tabla.
(2)Da información de como se va ha ejecutar la sentencia select.
FLUSH vacía varias caches usadas por el servidor.
flush opcion_flush, ..
opciones_flush pueden ser:
Hosts: vaciar la cache del servidor.
Logs: vacia las caches de registros cerrándolos y volviéndolos a abrir
Privileges: recarga las tablas cedidas.
Status: vuelve a iniciar los estatus variables.
Tables: cierra cualquier tabla abierta en la tabla cache.
KILL elimina hilos del servidor.
Kill id_hilo
LOCK TABLES bloqueo de tablas:
lock tables lista_bloqueo
Las opciones de lista _ bloqueo son los nombre de las tablas separadas por comas; las tablas
deben tener el formato.
nombre_tabla [as nombre_alias](read | [low_priority] write)
Read: bloqueo de solo lectura.
Write: bloqueo de escritura, bloquea la tabla completamente.
Low_priority: pide permiso para leer la tabla.
UNLOCK TABLES: desbloqueo de las tablas.
Unlock tables
SET: se utiliza para especificar un conjunto de caracteres usando por el cliente.
Set (option) opción_configuración
Las opciones _ configuración pueden ser:
Carácter set (conjcar_nombre | default) especifica el conjunto de caracteres usado
por el cliente.
Insert_id=n:especifica el valor que se utilizara en la columna auto_incrementen la
próxima instrucción insert.
12
Miguel Ángel Sanz Santos
Comandos básicos MYSQL
Last_insert Id=n: especifica el valor que será devuelto por last_insert_id() ->
actualización de procedimiento de registro.
Password [for usuario] = password(“contraseña”):sin la cláusula for se establece
contraseña para el usuario actual, si se indica se establece para el usuario dado.
Hay que tener privilegios de establecimiento de contraseñas.
Sql_auto_is_null=[0|1]: si se establece en 1, la última fila insert que contiene el valor
auto_increment puede seleccionarse usando la cláusula where auto_inc_coll is null.
Sql_big_tables [1|0]: si se establece a 1; se admiten las instrucciones select que
aparezcan más que la filas max_join_size.
Sql_log_off=[0|1]: Si esta opción se establece en 1 las ordenes sql del cliente actual
no aparecerán en el registro del archivo general.
Sql:log_update=[1|0]: es igual que la anterior pero afecta al registro de actualización
y no al general.
Sql_low_priority_updates=[0|1]: si se establece en 1, las instrucciones que modifican
la tabla de contenidos esperaran hasta que las instrucciones select terminen.
Sql_select_limit=(n|default): especifica el no máximo de registros que se podrán
devolver desde una instrucción select.
Sql_warnings=(1|0): si se establece en 1 mysql informa de los errores tipo 'aviso'.
Timestamp=(timestamp_value|default): especifica un valor timestamp de
actualización del registro actual.
Atentamente:
Franklin Campo