Translate

miércoles, febrero 11, 2015

MySQL – Comandos Útiles I, II y III

Descripción:
MySQL es un sistema de gestión de bases de datos relacional, multihilo y multiusuario con más de seis millones de instalaciones. MySQL AB —desde enero de 2008 una subsidiaria de Sun Microsystems y ésta a su vez de Oracle Corporation desde abril de 2009— desarrolla MySQL como software libre en un esquema de licenciamiento dual.
Por un lado se ofrece bajo la GNU GPL para cualquier uso compatible con esta licencia, pero para aquellas empresas que quieran incorporarlo en productos privativos deben comprar a la empresa una licencia específica que les permita este uso. Está desarrollado en su mayor parte en ANSI C.

Comandos útiles I


Como ingresar a la interface de administración de MySQL:
mysql -u usuario -p
Como crear una base de datos:
–  mysql> CREATE DATABASE newdatabase;
Visualizar las bases de datos en mi servidor:
–  mysql> SHOW DATABASES;
Crear un usuario para que acceda a una base de datos específica:
–  mysql> CREATE USER ‘operador’@’localhost’ IDENTIFIED BY ‘password';
Asigno privilegios específicos al usuario “operador” en la base en la base de datos:
–  mysql> GRANT SELECT,INSERT,UPDATE ON ‘newdatabase’.’localhost’ TO ‘operadores’@’localhost';
O asigno todos los privielgios en todas las bases de datos para el usuario operador:
–  mysql> GRANT ALL PRIVILEGES ON *.* TO ‘operador’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
Selecciono la base de datos con la que quiero trabajar:
–  mysql> USE test
Database changed
Creo una tabla llamada “pet” con 6 columnas cada una con su nombre y tipo de dato:
–  mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Muestro las tablas de mi base de datos:
–  mysql> SHOW TABLES;
+———————+
| Tables in menagerie |
+———————+
| pet |
+———————+
Para verificar que la tabla ha sido creada en la forma esperada, utilice la sentencia DESCRIBE:
–  mysql> DESCRIBE pet;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+———+————-+——+—–+———+——-+
Para cargar el fichero pet.txt dentro de la tabla pet, utilice este comando:
–  mysql> LOAD DATA LOCAL INFILE ‘/path/pet.txt’ INTO TABLE pet;
Cargo datos manualmente en la tabla “pet”:
–  mysql> INSERT INTO pet VALUES (Puffball’,’Diane’,’hamster’,’f’,’1999-03-30′,NULL);
Actualizo un dato en la tabla “pet” usando el comando UPDATE. UPDATE modifica solo el registro en cuestión y no requiere que se vuelva a llenar la tabla.
–  mysql> UPDATE pet SET birth = ‘1989-08-31′ WHERE name = ‘Bowser';
Selecciono todas las columnas de la tabla “pet” donde el nombre coincida con “Bowser”
–  mysql> SELECT * FROM pet WHERE name = ‘Bowser';
+——–+——-+———+——+————+————+
| name | owner | species | sex | birth | death |
+——–+——-+———+——+————+————+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+——–+——-+———+——+————+————+
Selecciono todas las columnas de la tabla “pet” donde el campo “brith” sea mayor a 1998-1-1:
–  mysql> SELECT * FROM pet WHERE birth > ‘1998-1-1′;
+———-+——-+———+——+————+——-+
| name | owner | species | sex | birth | death |
+———-+——-+———+——+————+——-+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |

Cuento el número de filas: COUNT(*) cuenta el número de filas, por ello, la consulta para contar animales luce así:
–  mysql> SELECT COUNT(*) FROM pet;
+———-+
| COUNT(*) |
+———-+
| 9 |
+———-+
Se puede usar COUNT() para hallar cuantas mascotas tiene cada propietario:
–  mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+——–+———-+
| owner | COUNT(*) |
+——–+———-+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+——–+———-+
Selecciono solo las columnas nombre y nacimiento de la tabla “pet”:
–  mysql> SELECT name, birth FROM pet;
Borro la tabla “pet” entera:
–  mysql> DELETE FROM pet;
Borro parte el contenido de la tabla “pet” que coincida con “condicion”:
–  mysql> DELETE FROM “tabla” WHERE “condicion”
Borro toda la base de datos:
–  mysql> drop database newdatabase;
Exportar Base de Datos:
–  root# mysqldump -u operador -p newdatabase > newdatabase.sql
Importar Base de Datos:
–  root# mysql -u operador -p newdatabase < newdatabase.sql
Ver tamaño de las bases de datos:
–  mysql> SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ;

Comandos útiles II

En este post intento recoger una serie de comandos útiles si en tu día a día te toca trabajar con MySQL desde la consola.
  • Conectarte al interprete de MySQLcon el usuario USUARIO
] # mysql -u USUARIO -p
  • Ver / Listar las bases de datos que hay dadas de alta en el servicio
SHOW databases;
  • Ver / Listar los usuarios dados de alta en el servicio
SELECT * FROM mysql.user;
  • Seleccionar / Utilizar una base de datos BD
USE BD;
  • Crear usuario con todos los privilegios / Dar todos los privilegios a un usuario USUARIO desde una dirección HOST con la contraseña CLAVE para cierta base de datos BD o para todas
GRANT ALL PRIVILEGES ON BD.* TO USUARIO‘@’HOST IDENTIFIED BY ‘CLAVE‘ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO USUARIO‘@’HOST IDENTIFIED BY ‘CLAVE‘ WITH GRANT OPTION;
  • Crear usuario con ciertos privilegios / Dar ciertos privilegios a un usuario USUARIO desde una dirección HOST con la contraseña CLAVE para cierta base de datos BD o para todas
GRANT PRIVILEGIO [, PRIVILEGIO] …. ON BD.* TO ‘USUARIO‘@’HOST‘ IDENTIFIED BY ‘CLAVE‘;
    • ALTER: Modificar tablas con ALTER TABLE
    • CREATECrear una nueva base de datos o tabla
    • DELETEEliminar registros de las tablas
    • DROPEliminar bases de datos o tablas
    • INDEXCrear o eliminar índices de tablas
    • INSERTCrear registros en las tablas
    • SELECTListar registros de las tablas
    • UPDATEModificar registros de las tablas
  • Listar privilegios para un usuario USUARIO desde una dirección HOST
SHOW GRANTS FOR ‘USUARIO‘@’HOST‘;
  • Eliminar / Quitar privilegios para un usuario USUARIO desde una dirección HOST para una base de datos BD
REVOKE PRIVILEGIO [, PRIVILEGIO] … ON BD.* FROM ‘USUARIO‘@’HOST‘;
  • Eliminar / Quitar un usuario USUARIO desde una dirección HOST
DROP USER ‘USUARIO‘@’HOST‘;
  • Cambiar contraseña CLAVE a un usuario USUARIO desde una dirección HOST
SET PASSWORD FOR ‘USUARIO‘@’HOST‘ = PASSWORD(‘CLAVE‘);
  • Migrar base de datos de un servidor a otro directamente
] # mysqldump -uUSUARIO -pCLAVE BD | mysql -h HOST_DESTINO -uUSUARIO -CLAVE BD
  • Exportar todas la base de datos MySQL a fichero con fecha en el nombre comprimido con gzip
] # mysqldump –password=CLAVE –single-transaction –all-databases | gzip -9 >RUTA_DESTINONOMBRE_`date +%Y-%m-%d`.sql.gz

Comandos útiles III


Por defecto, ya sea en una instalación limpia de mysql server o bien en algun hosting compartido, éste servidor de bases de datos relaccionales, viene ‘capado’ para que sólo pueda ser usado desde el propio host donde fue instalado.
Me ha llevado varias horas arreglarlo, pero por fin, con este sencillo tip, se pueden permitir conexiones desde cualquier otra IP:
sudo nano /etc/mysql/my.cnf
#localizamos la linea bind-address y ponemos la ip 0.0.0.0
bind-adress = 0.0.0.0
#reiniciamos el servidor mysql
sudo /etc/init.d/mysql restart
Ahora tenemos que dar permiso en la base de datos para poder hacer las consultas, para ello nos conectamos mediante el comando mysql a la base de datos desde el mismo servidor donde está instalada:
mysql -u root -p
y nos pedirá la contraseña para acceder.
Ahora creamos un usuario, y su con su contraseña:
CREATE USER 'walo'@'%' IDENTIFIED BY '1234';
Le otorgamos privilegios a todo en lodos los dominios/ip’s (@):
GRANT ALL PRIVILEGES ON *.* TO walo@'%' IDENTIFIED BY '1234';
Si queremos dárselo en localhost (solo para otro usuario que no sea root:
GRANT ALL PRIVILEGES ON *.* TO walo@localhost IDENTIFIED BY '1234';
Actualizamos todos los campos host con % para que deje conectarse desde cualquiera, para mi usuario:
USE mysql;
UPDATE db SET host='%' WHERE user='walo';
Creamos la base de datos prueba para trabajar con ella:
CREATE DATABASE prueba;
SHOW DATABASES;
Y ya podremos conectarnos remotamente desde otro ordenador al servidor mysql remotamente:
mysql -h IPDELSERVIDORMYSQL -u root -p
Ahora recomiendo trabajar con MySQL workbench, un complejo programa multiplataforma para monitorizar bases de datos remotas, que además viene con editor de consultas y permite crear diagramas E/R.


* Comando para ver el espacio ocupado por todas las BB.DD en MySQL:
 SELECT table_schema "Nombre de Base de datos", sum( data_length + index_length ) / 1024 / 1024 "Tamaño en  MB"  FROM information_schema.TABLES GROUP BY table_schema;
*Comando para ver el espacio ocupado por las tablas de una BB.DD específica, en este caso "nagios" (podemos cambiar nagios por la BB.DD que nos interese ver):
SELECT TABLE_NAME, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "nagios";

Si una BB.DD mysql , está funcionado en localhost (127.0.0.1), para que sea monitorizada por un servidor externo nagios tiene que admitir escuchas para todas las interfaces de red (0.0.0.0), para ello es necesario modificar un parámetro en el siguiente fichero:

my.cnf

El parámetro es el siguiente:
bind-address=127.0.0.1
Debería quedar así:
bind-address=0.0.0.0

Si por temas de seguridad deseamos que solo entre la IP de nuestro servidor externo nagios deberemos aplicar filtros de firewall para el servidor nagios (ver iptables)

No hay comentarios: