mysql
Gestor de bases de datos [BDs]. Las BDs se guardan en el directorio /var/lib/mysql/
$ mysqldump -u root -p proveedores > fichero.sql
$ mysql -u root -p proveedores < fichero.sql
$ mysqlimport -u root -p clientes fichero.sql
$ mysqldump -u root -p --all-databases > fichero.sql
$ mysqldump -u root -p clientes proveedores > fichero.sql
$ mysqldump -u root -p proveedores --ignore-table=antiguos > fichero.sql
$ mysqldump -u root -p base_de_datos tabla1 tabla2 > tabla1_y_tabla2.sql
$ mysql -u root -p1234
$ mysql -u root -p
$ mysql -u root -p datos
Una vez en el prompt de mysql [Todas las sentencias han de terminar con un punto y coma [;] menos la sentencia "USE"]:
mysql> SHOW VARIABLES LIKE 'datadir'; (muestra el directorio donde se almacenan las BDs)
mysql> show databases; (Mostrar las BDs existentes)
mysql> create database datos; (Crear la BD "datos")
mysql> DROP DATABASE datos; (Borrar toda una base de datos. El directorio /var/lib/mysql/datos/ tiene que estar vacio)
mysql> USE datos (Entrar en la BD "datos". Sin punto y coma final)
mysql> show tables; (Mostrar las tablas de la BD en la que se ha entrado)
mysql> DESCRIBE una_tabla (Mostrar los campos de una tabla concreta)
mysql> exit (Salir de mysql. Tambien con "quid" o Ctrl+d)
mysql> select version(), current_date; (Mostrar versión de mysql y fecha)
mysql> SET lc_messages = 'es_ES'; (Que los mensajes de error salgan en español)
Resumen de comandos:
CREATE DATABASE (rear nueva base de datos vacía)
DROP DATABASE (eliminar completamente una base de datos existente)
CREATE TABLE (crear nueva tabla)
ALTER TABLE (modificar una tabla ya existente)
DROP TABLE (eliminar por completo una tabla existente)
SELECT (leer o seleccionar datos)
INSERT (añadir o insertar nuevos datos)
UPDATE (cambiar o actualizar datos existentes)
DELETE (eliminar o borrar datos existentes)
REPLACE (añadir, cambiar o reemplazar datos nuevos o ya existentes)
TRUNCATE (vaciar o borrar todos los datos de la plantilla)
Modelo de creación de una tabla dentro de una BD mysql:
Nota: en caso de equivocación podemos suspender la entrada de datos con \\n . Si existe alguna comilla abierta, primero ha de cerrarse antes de lanzar \\n
mysql> CREATE TABLE usuarios(
-> nombre VARCHAR(20), apellido VARCHAR(20),
-> profesión VARCHAR(20), sexo CHAR(1), nacimiento DATE,
-> antiguedad DATE);
VARCHAR(20) (Que los valores que se almacenarán son de longitud variable de hasta 20 caracteres)
CHAR(1) (Que el valor será fijo y solo tendrá un caracter)
DATE (En formato YYYY-MM-DD)
Para entrar datos en la tabla anterior, podemos crear un archivo de texto "usuarios.txt" que contenga un registro por línea con valores separados por tabuladores, cuidando que el orden de las columnas sea el mismo que utilizamos en la sentencia CREATE TABLE. Para valores que no conozcamos podemos usar valores nulos usando \N. Ejemplo:
$ nano usuarios.txt
Juan Borras contable m 1964-05-12 1988-03-27
Maria Gil administrativo f 1983-10-03 2004-08-05
Antonio Gomez portero m \N 2006-11-01
Nota.- El final del archivo no ha de contener ninguna linea en blanco. El archivo tiene que estar en /var/lib/mysql/datos (En el directorio de la misma base de datos donde se carga)
Para cargarlo en la BD:
mysql> LOAD DATA INFILE "usuarios.txt" INTO TABLE datos;
Nota.- En caso de que salga el error: The used command is not allowed with this MySQL version:
# nano /etc/mysql/my.cnf
Y en la sección [mysql] añadir la linea "local-infile":
[mysql]
local-infile
Reinciar mysql.
LOAD DATA (Permite especificar cuál es el separador de columnas y el separador de registros. Por defecto el tabulador es el separador de columnas (campos) y el salto de línea es el separador de registros)
Para añadir un nuevo registro a la BD anterior:
mysql> INSERT INTO usuarios
-> VALUES('Jose','Robles','administrativo','m',NULL,'2012-12-01');
Valores y fechas entre comillas simples y valores desconocidos NULL y no \N como entrando un archivo.
mysql> SELECT * FROM usuarios; (Ver toda la tabla para comprobar si se ha añadido el último registro)
Para modificar un registro puede realizarse borrando toda la tabla de datos y entrando nuevamente el archivo usuarios.txt si este está actualizado:
mysql> DELETE FROM usuarios;
mysql> LOAD DATA INFILE "usuarios.txt" INTO TABLE usuarios;
O modificando solo el registro que corresponda:
mysql> UPDATE usuarios SET nacimiento="1959-04-12" WHERE apellido="Gomez"; (Modificar campo "nacimiento" del apellido "Gomez"
mysql> SELECT * FROM usuarios WHERE apellido="Gomez"; (Verificar si se ha realizado la modificación)
mysql> SELECT * FROM usuarios WHERE nacimiento >= "1990-1-1"; (filtrar los que han nacido antes de la fecha indicada)
mysql> SELECT * FROM usuarios WHERE profesion="administrativo" AND sexo="f"; (filtrar los administrativos de sexo femenino)
mysql> SELECT * FROM usuarios WHERE profesion="portero" OR profesion="contable"; (filtrar porteros y contables)
mysql> SELECT * FROM usuarios WHERE (profesion="portero" AND sexo="f")
-> OR (nombre = "juan" AND sexo = "m"); (Filtrar distintas opciones encerradas entre parentesis)
mysql> SELECT apellido, nacimiento FROM usuarios;
mysql> SELECT antiguedad FROM usuarios; (Filtrar la columna "antiguedad")
mysql> SELECT nombre, nacimiento FROM usuarios ORDER BY nacimiento; (Ordenar por fecha nacimiento)
mysql> SELECT * FROM la_tabla ORDER BY la_columna; (Ordenar por el número de una columna)
Incrementos en un campo:
mysql> update instrumentos set precio=precio+10; (Incrementar todos los campos "precio" de la tabla "instrumentos" en 10)
mysql> update instrumentos set precio=precio+10 where tipo=cuerda; (Solo incrementar en 10 el campo "precio" de la tabla "instrumentos" para los de "cuerda":)
mysql> update instrumentos set precio=(precio+precio*0.20) where tipo='viento'; (Solo incrementar un 20% (0,2) el campo "precio" de la tabla "instrumentos" para los de "viento":)
mysql> select nombre, precio, existencias*precio from instrumentos; (Ver de cada entrada (nombre) el precio unitario (precio) y el precio del total según las existencias:)
Otras operaciones:
mysql> DELETE FROM usuarios WHERE nombre='Maria'; (Eliminar toda la entrada que contenga "Maria")
mysql> DROP TABLE usuarios; (Eliminar una tabla)
mysql> DROP TABLE IS EXIST usuarios; (Eliminar tabla si existe)
mysql> CHECK TABLE la_tabla EXTENDED; (Chequear la integridad de una tabla)
mysql> REPAIR TABLE la_tabla; (Reparar una tabla)
1.-
En ciertos casos, para resolver el error: mysqldump: Got error: xxxx: Table 'xxxxxxxxx' doesn't exist when using LOCK TABLES:
$ mysqldump -u root -p BDs --single-transaction > dump.sql
2.-
Otro sistema para importar una base de datos. Primero crearla, entrar en ella y ejecutar el source sobre el fichero sql:
mysql> CREATE DATABASE datos;
mysql> USE datos;
mysql> SOURCE datos.sql;
3.-
Exportar una tabla a txt con determinado formato:
mysql> SELECT * FROM tabla INTO OUTFILE "tabla.txt"
--> FIELDS TERMINATED BY ' '
--> LINES TERMINATED BY '\n\n\r';
FIELDS TERMINATED BY ' ' (indica que los campos de cada línea terminarán con dos espacios.
LINES TERMINATED BY '\n\n\r '(indica el carácter como acaba cada linea, en este caso dos saltos de línea y retorno de carro
otras instrucciones de formato:
ENCLOSED BY '"' (Que los campos de tipo char, text, binary y enum de cada línea se entrecomillan)
IGNORE 1 LINES (No cargar la primera línea del fichero de texto)
LINES STARTING BY '---' (Que todas las líneas empiezen con "---".
4.-
Ejemplo parecido al anterior pero ordenando el fichero por el segundo campo:
mysql> SELECTC campo1,campo2,campo3 FROM tabla ORDER BY campo2 INTO OUTFILE "campos.txt"
--> FIELDS TERMINATED BY ' '
--> LINES TERMINATED BY '\n\r';
5.-
Gestion de usuarios.
Cuando creamos un nuevo usuario en MySQL, éste queda identificado por su nombre de usuario más el nombre o IP del ordenador desde el cual hemos dicho que accederá (el carácter comodín '%' representa varios ordenadores).
Ejemplos:
juan
juan@'%'
juan@localhost
juan@'192.168.1.%'
juan@'%.dominio.org'
Operaciones con usuarios:
mysql> SELECT User,Host,Password FROM mysql.user; (Ver los usuarios)
mysql> CREATE USER juan@'%' IDENTIFIED BY '1234'; (Crear un usuario con la contraseña 1234)
mysql> CREATE USER john IDENTIFIED BY '1234', peter IDENTIFIED BY '4321'; (Crear dos usuarios)
mysql> RENAME USER john TO juan, peter TO pedro;
mysql> DROP USER juan, pedro; (Borrar dos usuarios)
mysql> SET PASSWORD FOR juan = PASSWORD('abcde') (Cambiar la contraseña de un usuario)
mysql> SHOW GRANTS FOR pedro; (Ver los privilegios de un usuario)
mysql> GRANT ALL ON *.* TO juan@localhost (Otorgar todos los privilegios a un usuario)
mysql> REVOKE ALL ON *.* FROM juan@localhost; (Anular todos los privilegios de un usuario)
6.-
Acceso remoto a mysql con el usuario root:
# nano /etc/mysql/my.cnf
Comentar la linea:
#bind-address = 127.0.0.1
Reiniciar el servicio:
# /etc/init.d/mysql restart
Entrar en el prompt de mysql y otorgar privilegios:
# mysql -u root -p
Entrar la contraseña y:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'la_contraseña' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit
7.-
Solucionar:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables
$ mysql
mysql> use mysql (Enrar en la BDs mysql)
mysql> UPDATE user SET Password=PASSWORD('12345') WHERE user='root';(Actualizar el campo Password del usuario root)
mysql> exit (Salir)
$ mysql -u root -p
8.-
Solucionar error: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
# nano /etc/mysql/my.cnf
Y en el apartado mysqld añadir la linea:
secure-file-priv = ""
9.-
Modificar el promt de mysql
Si solo se desea modificar para la presente sesión, puede hacerse directamente desde el prompt de mysql:
mysql> prompt \h [\d]>\_
PROMPT set to '\h [\d]> '
localhost [(none)]>
Para hacerlo permanente:
# nano /etc/mysql/my.cnf
Y en la sección [mysql] añadimos:
prompt=\h [\d]>\_
Variables genéricas:
\S muestra un punto y coma
\' muestra comillas simples
\" muestra comillas dobles
\v muestra la versión del servidor
\p muestra el puerto de conexión
\\ Muestra una barra invertida
\n Inserta un salto de linea
\t Inserta una fabulación
\ Inserta un espacio (debe haber un espacio en blanco después de \ )
\_ Inserta un espacio (debe haber un _ después de \ )
\d Muestra la base de datos por defecto
\h Muestra el nombre del servidor
\c Muestra un contador de sentencias ejecutadas. Continua aumentando a medida que introducimos comandos.
\u Muestra el usuario
\U Muestra el nombre de la cuenta de usuario en el formato usuario@servidor
10.-
Solucionar error: ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
11.-
Colocar un contador de registros en una tabla. Incluir las lineas id_image... y PRIMARY KEY...:
mysql> CREATE TABLE usuarios(
-> id_image INT(3) NOT NULL AUTO_INCREMENT,
-> nombre VARCHAR(20), apellido VARCHAR(20),
-> profesión VARCHAR(20), sexo CHAR(1), nacimiento DATE,
-> antiguedad DATE),
-> PRIMARY KEY (id_image)
-> );
12.-
Aumentar la seguridad de un servidor mysql
# nano /etc/mysql/my.cnf
Y modificar las siguientes lineas dejándolas:
bind-address=127.0.0.1
[mysqld]
local-infile = 0
secure-file-priv = /dev/null
update mysql.user set user="USER" where user="root";
flush privileges;
La primera linea indica que el servidor MySQL solo reciba conexiones de localhost. Las tres siguientes evitan la lactura de los ficheros de configuración. Las dos siguientes renombran al usuario root con el nombre que digamos, que colocaremos donde poner "USER"
Tambien es interesante realizar consultas para ver los privilegios de los usuarios y sus permisos dentro del prom de mysql:
> select distinct(grantee) from information_schema.user_privileges;
Si solo queramos ver los permisos de un usuario:
> show grants for 'root'@'localhost';
Otra herramienta importante es el script "mysql_secure_installation", el cual nos ayuda a eliminar usuarios anónimos, establecer una contraseña al administrador, eliminar las bases de datos de pruebas, habilitar o deshabilitar el acceso remoto del usuario administrador, etc.
$ sudo mysql_secure_installation