Mysql Instalación

Requerimientos de software previos

• Para gestionar Mysql disponemos de varias herramientas clientes, entre las cuales están.

• El front-end MysqlWorkBench
• El gestor web PhpMyAdmin

• Para poder instalar el segundo, es necesario tener instalado un servidor web, ya que PhpMyAdmin no es más que un sitio web de página PHP para gestionar un servidor Mysql.

• A mayores, cuando instalamos el Mysql disponemos de utilidades tipo consola, que nos permite conectarnos al gestor Mysql y realizar múltiples operaciones. Por ejemplo el programa mysql o el mysqladmin. Para ver la lista completa, abrir un terminal y escribir mysql pulsando después dos veces la tecla 'tabulador'.

• Como en este curso vamos a trabajar con las dos herramientas, necesitamos tener instalado localmente un servidor web.

Para ello podemos hacer uso de un paquete denominado XAMPP o instalar cada componente por separado. En nuestro caso vamos a hacer la instalación individual.

• Nota: Aunque en ocasiones se indicará como se realiza alguna operación en Windows, casi todo el manual está orientado al uso con S.O. Linux.

Instalación

• Tanto en windows como en Linux disponemos de un paquete de software en el que está integrado Apache, Mysql, soporte para PHP,posibilidad de ejecutar Servlets/jsp en Apche...Se denomina XAMPP.

Podéis ver como se instala en el siguiente enlace, pero nosostros vamos a hacer una instalación individual por componentes.

Xampp es utilizado para entornos de desarrollo, normalmente de sitios web, [http://wiki.cifprodolfoucha.es/index.php?title=Drupal utilizando CMS, páginas php con acceso a datos,....y no se debe utilizar para entornos de producción.

LINUX

• Servidor web Apache: Seguir este enlace

• Instalación y configuración de Mysql:

• Podemos hacerlo a través de consola: Seguir este enlace.
• Podemos bajarnos los paquetes necesarios en función del S.O. que tengamos e instalarlos uno a uno.

En este caso es mejor añadir el repositorio de Mysql para el S.O. correspondiente siguiendo estas instrucciones.

• Instalación de MySqlWorkBench, programa que permite gestionar gráficamente el Mysql: sudo apt-get install mysql-workbench

• Instalación de phpmyadmin, sitio web que permite gestionar mysql a través de un servidor web. Usado en los alojamientos compartidos de Internet: sudo apt-get install phpmyadmin

Tendréis que seguir los pasos indicados en este enlace. Para acceder al sitio tendréis que escribir en un navegador: http://localhost/phpmyadmin

WINDOWS

• Descargar e instalar el servidor web Apache: Seguir los pasos indicados en este enlace.

• Descargar e instalar o soporte PHP para windows: Seguir los pasos indicados en este enlace.

• Descargar e instalar el gestor de bases de datos Mysql junto con la herramienta de administración MysqlWorkBench y el PhpMyAdmin.Seguir este enlace.

Configuración

Estado inicial

• La instalación crea en el sistema el usuario 'mysql' y el grupo 'mysql'. Pertenecen al S.O. y no tienen nada que ver con los usuarios que tienen acceso al gestor Mysql.

Los usuarios de la base de datos no tienen nada que ver con los usuarios del sistema operativo.

• La instalación crea en el servidor de bases de datos el usuario 'root', La contraseña fue solicitada durante la instalación.

Dicho usuario tiene control total sobre todas las bases de datos del servidor.

• Por defecto hay una base de datos de nombre 'mysql' con información de MySQL y los usuarios creados, y otra base de datos de nombre 'information_schema' con información relacionada con las bases de datos, tablas,...creados

• Ficheros y directorios importantes (pueden variar en otras instalaciones):

• /var/lib/mysql/

• Guarda las bases de datos del servidor.

A cada base de datos corresponderá un directorio con el mismo nombre.

A cada tabla MyISAM corresponderán varios ficheros con el mismo nombre que la tabla y diferente extensión (*.frm = formato, *.myd = datos tablas, *.myi = índices) dentro de un directorio con el mismo nombre que su base de datos.

A cada tabla InnoDB corresponderán un fichero con el mismo nombre que la tabla y extensión .frm (formato) dentro de un directorio con el mismo nombre que su base de datos, y varios ficheros con nombre 'ibdata1','ibdata2',... (tablespace") que compartirá con otras bases de datos en el directorio principal de datos.

El propietario de los ficheros es el usuario 'mysql' y el grupo 'mysql'.

• /var/log/mysql/

• Anotaciones y alertas del servidor.

• /etc/mysql/

• Ficheros de configuración general (my.cnf).

Cada vez que cambiemos la configuración deberemos reiniciar el servidor para que se activen los nuevos cambios.

• /etc/init.d/mysql

Script para arrancar, parar y reiniciar el servidor (lo utiliza la orden service mysql start, vista posteriormente).

• /usr/bin/ , /usr/sbin/ , /usr/share/mysql/

Programas de MySQL

• El puerto por defecto del servidor MySQL es el TCP/UDP 3306.

• Para acceder remotamente al servidor debo modificar /etc/mysql/my.cnf y comentar la línea "bind-address" o comentar la línea "skip-networking"

• Para saber la versión de Mysql instalada debemos ejecutar la orden: mysqld --version

Inicio / Parada / Estado

• Cuando se instala Mysql en Linux, este pasa a ser un servicio (proceso que se ejecuta automáticamente cuando se inicia el ordenador, no tiene interface gráfica).

• Podemos iniciar, parar o ver el estado del proceso con los siguientes comandos:

• Estados del servicio Mysql
• 

  Estado del servicio => Orden: sudo service mysql status Vemos como el servicio está iniciado (en verde). Pulsar la tecla 'q' para salir.

• 

  Para servicio => Orden: sudo service mysql stop . Vemos como el servicio está inactivo. En este caso nadie puede hacer uso del Mysql.

• 

  Iniciar servicio => Orden: sudo service mysql start

También se pueden parar con la orden:

1 mysqladmin -u root -p shutdown

• Cuando arrancamos el servicio se crea un proceso de nome mysqld. Este es el proceso que va a estar escuchando en un puerto determinado (lo especificamos en el archivo de configuración) las peticiones de consultas u órdenes de gestión sobre el Mysql.

Podemos comprobar, con el servidor Mysql iniciado como aparece dicho proceso:

1 sudo ps aux | grep mysqld

• En LINUX, cada vez que iniciamos el servicio se crea el directorio /var/run/mysqld y cuando el servicio se para, dicho directorio se borra.

Si quisiéramos iniciar el servidor Mysql desde la linea de comandos con el comando mysqld y poder pasarle diferentes opciones de configuración, deberíamos primero crear dicho directorio y darle permiso de acceso al usuario 'mysql'

Para ello, podemos ejecutar la sentencia:

1 sudo install -m 755 -o mysql -g root -d /var/run/mysqld

Debemos de tener en cuenta que dicho directorio es el indicado en el archivo de configuración de mysql.cnf (/etc/mysql/mysql.conf.d/mysqld.cnf) en la sección [mysqld_safe] y [mysqld]:

 1 [mysqld_safe]
 2 socket          = /var/run/mysqld/mysqld.sock
 3 nice            = 0
 4 
 5 [mysqld]
 6 #
 7 # * Basic Settings
 8 #
 9 user            = mysql
10 pid-file        = /var/run/mysqld/mysqld.pid
11 socket          = /var/run/mysqld/mysqld.sock

Por lo tanto, si cambiamos el directorio en dicho archivo tendremos que modificar la orden anterior y crear el directorio correspondiente.

• Podemos comprobar si conectamos al servidor desde la línea de comandos con los programas que nos suministra mysql:

1 sudo mysqlshow -p

Muestra las bases de datos creadas.

Nota: Lleva el parámetro '-p' ya que el usuario root tiene un password. En caso de que no tenga una password asignada, podríamos no ponerlo.

1 sudo mysqlshow -p mysql

Muestra las tablas de la base de datos mysql, donde se guarda información de mysql e información de los usuarios.

1 mysql  -p -e "select * from mysql.user"

Muestra los usuarios de la tabla user de la base de datos mysql.

Nota: Todo lo anterior podría ser ejecutado desde cualquier herramienta cliente de las instaladas, como MysqlWorkBench o PhpMyAdmin.

Cuenta del usuario root

• Esta cuenta se crea inicialmente cuando instalamos el servidor Mysql.

Debería de tener una contraseña asociada y suficientemente segura.

• Si durante la instalación permitimos conexiones anónimas, estará creada otra cuenta sin password asociada, para permitir este tipo de conexiones.

Si queremos que no haya conexiones anónimas tendremos que eliminar estas cuentas o ponerles una password.

• Todos los usuarios se encuentran en la tabla user de la base de datos mysql.

Cambio de password de la cuenta root

• Para ponerle contraseña debemos escribir el la línea de comandos, en caso de que no tenga una previa:

1 sudo mysqladmin -u root password nueva_contraseña

En caso de tener una contraseña puesta y queramos cambiarla:

1 sudo mysqladmin -u root -p password nueva_contraseña

• Podemos ejecutar la sentencia SQL SET, de la forma:

1 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NUEVA_CONTRASEÑA');

Nota: Recordar que la ejecución de sentencias SQL se puede realizar desde:

• Ejecutable mysql: mysql -uroot -p
• Desde el MysqlWorkBench, una vez conectados.
• Desde el sitio web PhpMyAdmin una vez conectados.

Podemos saber el nombre del usuario conectado con la sentencia SQL: SELECT CURRENT_USER();

• Podemos ejecutar la orden SQL UPDATE de la forma:

1 UPDATE USER SET authentication_string=password('root') where user='root';
2 FLUSH PRIVILEGES;

Nota: La segunda sentencia lo que hace es 'obligar' a que el servidor lea los datos de la tabla. En caso de no hacerlo, los cambios no tendrían efecto hasta el siguiente reinicio del servidor.

Nota: Esta forma se modificó a partir del mysql 5.7, ya que antes, el nombre de la columna donde se guardaba el password era password.

Recuperación del password de la cuenta root

• En alguna situación puede que nos encontremos con el 'problema' de que hemos olvidado o por alguna razón, no podemos entrar con el usuario root.

• Existen varias formas de asignar una nueva password al usuario root.

• Una de ellas es iniciar el servicio con la opción skip-grant-tables activada.

Esta opción se puede activar en el fichero de configuración del gestor Mysql o se puede iniciar el gestor pasando como parámetro dicha opción de la forma: sudo mysqld --skip-grant-tables

Una vez iniciado, nos conectamos con la herramienta mysql de la forma: mysql -uroot

No nos pedirá password.

Ejecutamos la orden FLUSH PRIVILEGES para que recargue los datos de los usuarios y podamos ejecutar alguna de las órdenas anteriores.

1 FLUSH PRIVILEGES;

Por ejemplo:

1 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NUEVA_CONTRASEÑA');

IMPORTANTE: Si iniciamos el gestor Mysql de esta forma, cualquier usuario podría conectarse al mismo sin contraseña y hacer cualquier cosa. Se recomienda desconectar el servidor de la red para que sólo el administrador desde el servidor tenga acceso al mismo.

Veremos más adelante como utilizar las variables del sistema de Mysql.

Archivo de configuración del servidor

• El archivo de configuración de Mysql es my.cnf, que se encuentra en la carpeta de instalación de Mysql.
• En Linux es /etc/mysql/. Este archivo es en realidad un acceso directo el cual va a acabar apuntando al archivo mysql,cnf del mismo directorio.

• El contenido del archivo de configuración global tiene las siguientes líneas:

1 !includedir /etc/mysql/conf.d/
2 !includedir /etc/mysql/mysql.conf.d/

• Estas líneas lo que indican es que van a buscar en los dos directorios indicados, todos los archivos de configuración que encuentren. Dichos archivos pueden tener cualquier nombre, pero deben acabar con la extensión .cnf

• El archivo de configuración del servicio Mysql (mysql deamon=>mysqld) se encuentra en la carpeta: /etc/mysql/mysql.conf.d/mysqld.cnf y tiene como nombre de sección [mysqld].

Podemos ver todas las opciones de configuración en este enlace.

1 sudo mysqld --verbose --help

• El archivo de configuración de la utilidad mysql de consola, que nos permite conectarnos al servidor y realizar las operaciones sobre el mismo, dispone de un archivo de configuración en /etc/mysql/conf.d/mysql.cnf.

Podemos ver todas las opciones de configuración en este enlace.

Para ver todas las opciones que podemos modificar en este archivo de configuración, debemos ejecutar desde una consola la siguiente orden:

1 sudo mysql --help

Nota: Podemos tener diferentes archivos de configuración que sean configurados para diferentes usuarios. El archivo de configuración situado en /etc/mysql/my.cnf afectará a todos los usuarios, pero el situado en el directorio "~/.my.cnf" (que es el directorio home de un usuario determinado) afectará a ese usuario.

Como cambiar las opciones de configuración

• En Mysql existen multitud de variables del sistema que podemos configurar.

Los valores de estas variables fueron establecidos en el/los archivo de configuración de Mysql (visto anteriormente)

• Para saber los valores actuales de dichas variables debemos ejecutar la orden (desde Mysql):

1 show variables

Nota: Desde mysql quiere decir que debemos estar conectados a mysql ya que es una orden SQL.

Para hacerlo desde consola debemos conectarnos a mysql de la forma:

1 sudo mysql -u root -p

Y después de escribir la contraseña de root podemos ejecutar las órdenes SQL que queramos, las cuales deben acabar en punto y coma.

Para salir al shell debemos escribir: exit (y pulsar enter)

• Los valores de dichas variables (algunos de ellos) están establecidos en el archivo de configuración de Mysql, que como hemos visto anteriormente, las relacionadas con el servidor Mysql, se encuentran en el archivo /etc/mysql/mysql.conf.d/mysqld.conf.

Si editamos dicho archivo podemos ver asignaciones a variables del sistema.

Dichos valores pueden modificarse en este archivo o a través de las herramientas cliente de Mysql.

El cambio de valor en alguna de las variables del sistema que sean de tipo Global (lo veremos después) llevará consigo la necesidad de reiniciar el servicio, para que coja los valores modificados.

Variables del sistema

• Listado de todas las variables del sistema.

Tipos

• En base a su alcance las variables puede ser:

• Globales: Afectan al funcionamiento de gestor Mysql y de todos los usuarios conectados.
• De sesión: Sólo afectan a cada cliente conectado, de tal forma que una modificación en el valor de una variable sólo afectará a los nuevos clientes que se conecten, mientras el resto siguen teniendo sus propios valores.

• Cuando un servidor Mysql se inicia, carga por defecto un valor para cada una de las variables globales.

Dicho valor, si es global puede ser modificado cambiándolo en el archivo de configuración o enviándolo como parámetro cuando el gestor se inicia. Cualquier cambio va a necesitar reiniciar el servicio.

• Cada cliente que se conecte al servidor cargará unas variables de sesión, las cuales pueden ser cambiadas, afectando sólo al usuario que las cambió.

• En base a si se pueden modificar en tiempo de ejecución las variables puede ser:

• Dinámicas: Se pueden modificar.
• Estáticas: No se pueden modificar.

• En base a si se refieren al estado del servidor en un momento concreto las variables puede ser:

• De estado: Se refieren al estado del servidor en un momento dado.
• De sistema: Se refieren a su comportamiento en general.

• En esta imagen podemos ver un estrato de las variables del sistema Mysql.

• Podemos ver como la cabecera nos indica si la variable de puede pasar por línea de comando al servicio (cmd-line), si puede ir en el fichero de configuración (option file), si es del sistema (system var), el alcance (scope) y si es dinámica (dinamic).

Visualización

• SHOW VARIABLES: Muestra las variables del sistema y sus valores.

Permite filtrar por nombre.'

show variables like '%Connect%': Muestra todas las variables del sistema que tengan la palabra 'Connect' en su interior.

show variables where value=3306: Muestra todas las variables del sistema que tengan un valor de 3306.

• SHOW STATUS: Muestra información sobre el estado del servidor, por medio de una serie de variables de estado.

Permite filtrar por nombre.

show status like '%inno%': Muestra las variables de estado y sus valores, pero sólo las que tengan la palabra inno en su interior.

show status where value>100: Muestra las variables de estado y sus valores, pero sólo las que tengan un valor mayor que 100.

Muchas de dichas variables se inicializan a 0 con la orden: FLUSH STATUS

• SHOW ERRORS: Muestra los errores que se han registrado después de la última sentencia ejecutada.
• SHOW WARNINGS: Muestra los errores, avisos o informaciones que se han registrado después de la última sentencia ejecutada.

Podemos consultar la lista de variables de estado y lo que significan en este enlace

Algunos ejemplos:

• Aborted_connections: Número de intentos de conexión al servidor Mysql que han fallado.
• Bytes_rececived: Número de bytes recibidos de todos los clientes.
• Bytes_send: Número de bytes enviados de todos los clientes.
• Connections: Número de intentos de conexión (con éxito o no) al servidor.
• Max_used_connections: Número máximo de conexiones que han sido utilizadas simultáneamente.
• Open_tables: Número de tablas abiertas (una tabla es abierta cuando hacemos un listado de las tablas de una base de datos o al realizar algún tipo de operación sobre ella).

Relacionado con lo anterior está el comando: show open tables; que muestra el listado de tablas abiertas. Admite la búsqueda por patrón con Like.

Modificación de valores

• Importante: Es necesario que la variable sea DINAMICA para que pueda ser modificada.

• Para modificar un valor de una variable del sistema tenemos dos opciones:

• Modificar su valor con la sentencia SET.
• Editar el archivo de configuración y modificar allí su valor.

En el primer caso, la modificación no afectará a los usuarios ya conectados, sino a los nuevos usuarios.

En el segundo caso será necesario reiniciar el servicio.

Por ejemplo:

1 SET GLOBAL max_connections = 1000;

1 SET SESSION sort_buffer_size = 10*1024*1024;

Ejemplos de Variables

Variables generales

• port=valor: Establece el puerto tcp/ip en el que el servidor Mysql va a escuchar las peticiones de los clientes.

• basedir=path: Directorio de instalación de Mysql. Normalmente las rutas toman como base dicho valor.

• datadir=path: Directorio donde se encuentran las bases de datos y tables del gestor.

• default-storage-engine=valor: Cambia el valor por defecto del tipo de tablas (normalmente Innodb o MyIsam).

• general_log: Permite activar el log general de Mysql

• general_log_file: Fichero donde se registra toda la actividad de Mysql. Por defecto tendrá de nombre 'host_namme.log'.

• log_error: De solo lectura. Indica el fichero donde se guardarán los errores que afecten al Mysql. Si está activa y no se indica ningún nombre, por defecto toma host_name.err y se encuentra en el directorio de datos.

• log_error_verbosity: Indica el nivel de gravedad de los errores que se van a registrar en el archivo anterior. Valores posibles se muestran en el enlace.

• skip-grant-tables: Esta opción deshabilita la seguridad en Mysql y permite que se pueda conectar al mismo sin necesidad de usar ningún usuario/password. Se tiene acceso a todas las bases de datos y a todas las tablas del sistema.

Utilizado como una de las formas de recuperar la contraseña de root.

• innodb_file_per_table: A partir de la versión 5.6.6 de Mysql tiene un valor true. Hace que las tablas Inndb generen un archivo ibd para cada una. Dentro de este archivo se guardan los datos e índices de cada tabla. Es empleado para recuperar una base de datos de un desatre, en la que tengamos una copia de seguridad y acceso a dichos ficheros (junto a los ficheros frm). También podemos crear tablas en directorios concretos (fuera del directorio por defecto para datos de Mysql) y de esta forma aumentar el rendimiento (por ejemplo, podemos hacer que ciertas tablas se guarden en discos SSD), como se indica en este enlace.

Variables relacionadas con la memoria caché

• Nota: A partir de versiones posteriores a Mysql 7.5 estas variables serán eliminadas.

• have_query_cache: Permite activar o desactivar el uso de la caché para guardar las consultas de los usuarios.
• query_cache_size: Tamaño en bytes del tamaño de la caché de resultados. No debe ser menor a 40KB, ya que necesita guardar información sobre la estructura.
• query_cache_type: Indica que tipo de sentencias van a ser llevadas a la cache. Por defecto, ninguna (valor 0). Se pueden ver los valores posibles en el enlace.

• Actualización Con Mysql 8, en lo relativo a formas de cómo acelerar la consulta de datos, se recomienda el uso de:

• Proxy SQL.
• Query Rewrite Plugins.

• Podemos leer en el siguiente enlace por qué es más eficiente 'llevar' la caché al cliente que tener una caché en el propio gestor Mysql.

Podemos observar en esta imagen obtenida del enlace anterior, como el rendimiento es mucho mayor en el caso de utilizar ProxySQL

Variables relacionadas con la red

• skip_networking: Impide que se pueda conectar a Mysql utilizando el protocolo TCP/IP. Es la opción recomendada cuando sólo hay usuarios locales.

• skip-name-resolve: Cuando veamos la parte de gestión de usuarios, veremos que podemos asociar a un usuario un nombre de máquina o dirección ip, haciendo que dicho usuario sólo tenga acceso desde esa máquina. Con esta opción activada, solamente permitiríamos poner direcciones IP. Útil cuando estamos en una red en la que no tenemos un servidor de nombres.

Actualización

• mysql_upgrade: Herramienta en línea de comandos para actualizar las tablas que necesiten alguna adaptación al nuevo gestor una vez actualizado.

1 sudo mysql_upgrade -uroot -p

-- Ángel D. Fernández González -- (2017).