Notas de MySQL

apuntes_mysql.md

MySQL

MySQL desde línea de comandos

Para cambiar los parámetros de autenticación del usuario root

Primero debemos ingresar a mysql

$ sudo mysql

Luego ejecutamos la siguiente query:

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

Reemplace "mynewpassword" por la clave que quiera.

mysql_secure_installation

Este programa le permite mejorar la seguridad de su instalación de MySQL de las siguientes maneras:

• Puede establecer una contraseña para las cuentas raíz.
• Puede eliminar las cuentas raíz a las que se puede acceder desde fuera del host local.
• Puede eliminar cuentas de usuarios anónimos.
• Puede eliminar la base de datos de prueba (a la que todos los usuarios, incluso los usuarios anónimos, pueden acceder de manera predeterminada) y los privilegios que permiten que cualquier persona acceda a las bases de datos con nombres que comienzan con test_.

Para acceder a este programa sólo debe ejecutar el siguiente comando:

$ sudo mysql_secure_installation

Para más información click aquí

Conectarse al servidor remoto MySQL

Sintaxis:

$ mysql -h host -u username -p 

Conectarse al servidor local MySQL

$ mysql -u root -p
Enter password:

e ingresamos la contraseña.

Generar dump de base de datos local

$ mysqldump -u root -p dbname > schema.sql

Generar dump de base de datos remota

$ mysqldump -u [usuario] -p[password] -h [host] [base_de_datos] > archivo_backup.sql --set-gtid-purged=off --column-statistics=0

MySQLdump tool require lo siguiente como mínimo:

• SELECT Privilegio para las tablas volcadas
• SHOW VIEW para las vistas volcadas
• TRIGGER para los disparadores descargados
• LOCK TABLES en caso de que no esté utilizando la opción --single-transaction
• PROCESS si no usa la opción --no-tablespaces

Para asignar estos privilegios:

$ mysql -u root -p

mysql> GRANT SELECT, LOCK TABLES, PROCESS ON *.* TO 'username'@'localhost';

ó bien especificando sobre que BD tendrá estos privilegios el usuario:

mysql> GRANT SELECT, LOCK TABLES, PROCESS ON DBNAME.* TO 'username'@'localhost';

Descargue todos los privilegios y salga de MySQL usando el siguiente comando:

mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Nota: Por razones de seguridad no se recomienda dar los privilegios LOCK TABLES, PROCESS a usuarios de una base de datos de producción. En vez de hacer esto es preferible utilizar las opciones --single-transaction y --no-tablespaces, respectivamente.

Exportar esquema de una base de datos sin los datos

$ mysqldump -u root -p --no-data dbname > schema.sql

Generar dump de algunas tablas de una base de datos

$ mysqldump -u root -p dbname tablename1 tablename2 > backup.sql

Realizar back up a más de una base de datos

$ mysqldump -u root -p --databases database1 database2 database3 > backup_databases.sql

Para realizar back up a todas las bases de datos del servidos

$ mysqldump -u root -p --all-databases > allbackup.sql

Restaurar una base de datos

$ mysql -u root -p dbname < db_backup.sql

Restaurar una base de datos de forma segura con codificación UTF8

Crear la BD con codificación utf8

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

Agregar lo siguiente en la primera línea del dump:

SET NAMES 'utf8' COLLATE 'utf8_general_ci';

E importar la BD

mysql -u root -p --default-character-set=utf8 nombre_db < archivo_dump.sql

SQL

Llaves Primarias e Indices

Modificar una tabla redefiniendo multiples llaves primarias:

ALTER TABLE nombre_tabla DROP PRIMARY KEY, ADD PRIMARY KEY (campo_1, campo_n);

Para mostrar los indices de una tabla:

SHOW INDEX FROM nombre_tabla;

Para mostrar SQL utilizada en una tabla existente:

SHOW CREATE TABLE nombre_tabla;

Para eliminar un indice de una tabla:

ALTER TABLE nombre_tabla DROP INDEX nombre_indice;

Crear indices con multiples campos:

CREATE INDEX nombre_indice ON nombre_tabla (campo1, campo_n);

La función CONCAT en MySQL

Ejemplos:

SELECT CONCAT(Nombre, ' ', Apellidos) AS Nombre FROM usuarios;

SELECT CONCAT(visitas, ' %') AS Total FROM estadisticas;

Modificar contraseña usuario root post instalación del servidor de mysql

$ sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

Crear usuario/clave y asignar todos los privilegios

CREATE USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

Crear usuario contraseña para máquina remota y asingar todos los privilegios

CREATE USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

ó de una IP específica:

CREATE USER 'username'@'192.168.1.100' IDENTIFIED WITH mysql_native_password BY 'password';

Para que esto funcione asegurese de tener esta configuración en el archivo mysqld.cnf:

bind-address            = 0.0.0.0

Este archivo en ubuntu se ubica en /etc/mysql/mysql.conf.d/mysqld.cnf

De lo contrario en esa línea reemplace 127.0.0.1 por 0.0.0.0 y reinicie el servicio:

$ systemctl restart mysql.service

Nota: En caso que tenga que realizar estos cambios debe utilizar super usuario, es decir usuario root o utilizar sudo.

Asignar todos los privilegios a un usuario

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

Aginar privilegios para sólo lectura (all read)

GRANT SELECT ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

Aginar privilegio PROCESS

GRANT PROCESS ON *.* TO username@localhost;
FLUSH PRIVILEGES;
EXIT;

Tenga en cuenta que esta opción presenta problemas de seguridad. Por lo tanto, solo debería usar esta opción para la instalación de su propio servidor de desarrollo local.

Cambiar la clave de un usuario

set password for root@'localhost' = password('qwerty');

Listar usuarios mysql

SELECT User, host FROM mysql.user;

Eliminar un usuario mysql

DROP USER 'username'@'localhost';

Listar bases de datos

show databases;

Crear una base de datos

create database nombre_bd;

Seleccionar base de datos

use nombre_bd;

Listar tablas de una BD

show tables;

Versión instalada

SHOW VARIABLES LIKE "%version%";

Status motor innodb

Muy útil en caso de errores poco descriptivos como por ejemplo: errno 150

show engine innodb status;

Crear tabla

Sintaxis:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Ejemplo:

CREATE TABLE customers(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR (20) NOT NULL,
  age INT NOT NULL,
  address CHAR (25) ,
  salary DECIMAL (18,2),
  PRIMARY KEY (id)
);