Diferencia entre revisiones de «Mysql Instalación»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 883: Línea 883:
 
* [https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_skip_networking skip_networking]: Impide que se pueda conectar a Mysql utilizando el protocolo TCP/IP. '''Es la opción recomendada cuando <u>sólo hay usuarios locales</u>'''. En la ayuda habla del sistema operativo Windows y Linux.
 
* [https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_skip_networking skip_networking]: Impide que se pueda conectar a Mysql utilizando el protocolo TCP/IP. '''Es la opción recomendada cuando <u>sólo hay usuarios locales</u>'''. En la ayuda habla del sistema operativo Windows y Linux.
 
: En Linux solamente hace caso a la seguinte variable:
 
: En Linux solamente hace caso a la seguinte variable:
:: [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address bind-address]: Indica la dirección IP desde la cual el servidor Mysql va a aceptar conexiones. Se la comentamos deja conectar desde cualquier dirección IP.
+
:: [https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bind_address bind-address]: Indica la dirección IP desde la cual el servidor Mysql va a aceptar conexiones. Se la comentamos deja conectar desde cualquier dirección IP.
 
: Se queréis hacer una práctica con esta variable, tendréis que modificar un usuario de los creados en Mysql (utilizando la herramienta MysqlWorkBench por ejemplo, tendréis que acceder a la sección de usuarios) para que pueda conectarse desde cualquier IP  cambiando la propiedad 'Limit to host' al valor:'''%'''  
 
: Se queréis hacer una práctica con esta variable, tendréis que modificar un usuario de los creados en Mysql (utilizando la herramienta MysqlWorkBench por ejemplo, tendréis que acceder a la sección de usuarios) para que pueda conectarse desde cualquier IP  cambiando la propiedad 'Limit to host' al valor:'''%'''  
  

Revisión del 11:43 30 mar 2019

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 Apache...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, utilizando CMS, páginas php con acceso a datos,....y no se debe utilizar para entornos de producción.


LINUX

  • 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.

Creando una base de datos de ejemplo

  • Para poder utilizar algunas de las variables del sistema, vamos a crear una base de datos de ejemplo con datos.

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.
  • Por defecto, se crea un fichero de nombre error.log donde se registran los eventos que producen problemas en el servidor.
  • En el caso de que el servidor no pueda iniciarse, debemos ir a este fichero y encontrar los mensajes de error. Una forma de hacerlo es por medio de la siguiente orden:
sudo tail -n100 /var/log/mysql/error.log | grep ERROR .Esta orden buscará en las últimas 100 líneas del archivo error.log, aquellas que tengan la palabra 'ERROR'.
  • /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:


Nota: Podemos reiniciar el servicio (pararlo e iniciarlo) con la orden: sudo service mysql restart


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


Mysql conf 5.jpg



  • Una forma de 'parar' el servicio (no es la recomendada, pero se puede utilizar si no responde a la forma correcta) es 'matar' el proceso.
Esto se consigue con la orden de consola: sudo kill -9 XXXX siendo XXXX el número de proceso que aparece al ejecutar la orden anterior.


  • 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 -uroot -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.


Atención: Tanto la herramienta mysql como mysqlshow como todos los programas que instala Mysql y que se conectan al servidor, pueden recibir parámetros de configuración, tanto desde la línea de comandos cuando se invoca, como desde un archivo de configuración cnf como veremos posteriormente.


  • Otra forma de arrancar el servidor son con los comandos:
  • mysqld
  • mysqld_safe. Este segundo es igual que el primero, con el añadido que en caso de que el servidor se pare (baje el servicio) intentará subirlo automáticamente y guarda información sobre la ejecución del servidor Mysql en el fichero de log.
Son comandos de consola y una vez ejecutados, la consola queda bloqueada (a no ser que se ejecuten en modo background).
Para para el servicio en estos casos, podemos ejecutar la orden: sudo mysqladmin -uroot -p shutdown (partiendo que el usuario root tiene asignada una contraseña).

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
Nota: mysqladmin es otra herramienta cliente de Mysql y al igual que ocurre con mysqlshow tiene su propia sección de configuración de nombre [mysqladmin] dentro de un archivo 'cnf' de configuración. Podemos ver todas las opciones en el siguiente enlace.
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 USE mysql;
2 UPDATE user SET authentication_string=password('root') where user='root';
3 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

  • Nota: Antes debemos de tener en cuenta un problema que vamos a tener si arrancamos el servicio de una forma diferentes a 'service mysql start'.
Como veremos a continuación, podemos arrancar el servicio con la orden 'mysqld_safe' o 'mysqld'. Ello lleva consigo (por defecto) la lectura de un archivo de configuración (lo veremos a continuación) que hace uso de unos archivos que se crean en un directorio. Ese directorio, si hacemos uso del 'service' se crea cuando el servicio se inicia y se borra cuando el servicio se para. Por lo tanto si arrancamos el servicio de otra forma, dicho directorio no existe.
Debemos de ejecutar previamente lo siguiente:
1 sudo mkdir /var/run/mysqld
2 sudo chown mysql:mysql /var/run/mysqld
3 sudo chmod 755 /var/run/mysqld


  • 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 (o bien escribir dicha opción en el archivo de configuración sin el doble guión).
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
Al arrancar de esta forma el servidor, se desactiva el sistema de seguridad, por lo que en ese momento cualquier usuario podría conectarse al servidor desde una máquina remota (si éste lo permite) y tener acceso al mismo.
Para evitarlo podemos hacer uso de la opción skip-networking, la cual desactiva que el servidor escuche conexiones TCP/IP.
Por lo tanto, la forma de arrancar el servidor para recuperar el password sería: sudo mysqld --skip-grant-tables --skip-networking (esto no evitaría que un usuario con una sesión ssh se pueda conectar, por lo que si es el caso, deberemos desconectar físicamente el cable de red).
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;
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.



Ejercicios


Ejercicio 1
  • Cambia la contraseña del usuario root de alguna de las formas vistas anteriormente.



Ejercicio 2
  • Ponte en el supuesto que no puedes conectarte con el usuario root. Realiza los pasos necesarios para poder conectarte con dicho usuario.
Recuerda: El servicio tiene que estar parado previamente para que al iniciarse cargue las modificaciones del archivo de configuración.




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.
Mysql conf 4.jpg


  • 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
  • O en la carpeta: /etc/mysql/mysql.conf.d/mysqld.cnf y tiene como nombre de sección [mysqld].
  • O tiene la sección [mysqld] en el mismo archivo /etc/mysql/mssql.cnf
Podemos ver todas las opciones de configuración en este enlace.
1 sudo mysqld --verbose --help
  • Todas esas opciones puede ser 'escritas' en el archivo de configuración o bien pueden ser pasadas como 'parámetros' por la línea de comandos.
Para poder hacer esta segunda opción, no podemos iniciar el servicio con la forma tradicional (service mysql start), sino que tenemos que iniciarlo ejecutando el archivo mysqld de la forma:
1 sudo mysqld --port=3308
En este ejemplo, el servicio estaría escuchando en el puerto 3308.


MUY IMPORTANTE: El servicio, sino indicamos lo contrario, buscará el archivo de configuración del servidor en '/etc/mysql/mysqld.conf.d/mysqld.cnf'. Si recordáis, en dicho archivo, se crea el pid y el socket en el directorio /var/run/mysqld, el cual, en algunas distribuciones Linux, no está creado!!!! Lo crea cuando iniciamos el servicio y lo borra al pararlo. Por lo tanto tendremos que crearlo y darle los permisos adecuados de la forma:
En el caso de Mysql debemos cambiar el propietario del usuario/grupo al usuario/grupo 'mysql' y dar permiso 755 sobre la carpeta.
Para ello, podemos ejecutar las órdenes:
1 sudo mkdir /var/run/mysqld
2 sudo chown mysql:mysql /var/run/mysqld
3 sudo chmod 755 /var/run/mysqld
Podéis comprobarlo en vuestra distribución haciendo un: 'sudo service mysql stop' y comprobando si el directorio está borrado.
Nota: Veremos más adelante en este manual como cambiar el directorio por defecto.
Tener en cuenta que se volvéis a llamar al servicio de la forma 'tradicional' (con service mysql start/stop) con usar 'stop' se borrará el directorio creado.


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.
Importante: Siempre que se modifique el archivo de configuración es conveniente hacer una copia de seguridad del mismo. Dicha copia no debe de tener extensión .cnf si está situado en el directorio /etc/mysql/, ya que el gestor leerá todos los archivos que encuentre con dicha extensión y los 'unirá'.


Caso especial: Los directorios
  • Existen ciertos directorios que son utilizados por el servidor Mysql en su fichero de configuración.
  • Algunos de ellos son:
1 pid-file        = /var/run/mysqld/mysqld.pid
2 socket          = /var/run/mysqld/mysqld.sock
3 
4 datadir         = /var/lib/mysql
5 log_error       = /var/log/mysql/error.log
  • Si queremos cambiar esos directorios por otros, no vale sólo con cambiar las rutas en el archivo de configuración.
Recordar que si necesitamos crear el directorio tenemos que darle los permisos adecuados (como vimos anteriormente), permisos 755 y propieatario 'mysql:mysql'.
Además, la distribución Linux basada en Ubuntu incorpora en el Kernel un sistema de seguridad de acceso a archivos. Este sistema se llama AppArmor.
Su archivo de configuración, para Mysql, se encuentra en el archivo /etc/apparmor.d/usr.sbin.mysqld
Tendremos que editarlo y mirar los permisos que tiene para el directorio que queremos cambiar, y o bien, podemos crear un alias para que el 'viejo' apunte al nuevo directorio o bien copiamos y pegamos las líneas correspondientes cambiando el directorio.
  • Por ejemplo, si buscamos por '/var/lib/mysql/', que es el directorio de los datos
1 # Allow data dir access
2   /var/lib/mysql/ r,
3   /var/lib/mysql/** rwk,
4 
5 # Allow data files dir access
6   /var/lib/mysql-files/ r,
7   /var/lib/mysql-files/** rwk,
Si necesitamos conservar los que hay ahora, copiaríamos las líneas cambiando el directorio (por ejemplo, si tenemos dos instancias de Mysql)
Si no los necesitamos conservar, podemos hacer un 'alias' editando el archivo nano /etc/apparmor.d/tunables/alias y escribiendo:
1 # importante la coma del final
2 alias /var/lib/mysql/ -> /nuevo/directorio/,
Después de cualquier cambio es necesario 'recargar' las modificaciones:
1 sudo service apparmor reload
Esta sería la forma de llevar las bases de datos de un lugar físico a otro diferente.


  • Podemos querer tener una instalación limpia,es decir, con todas las bases de datos borradas y las bases de datos del sistema inicializadas (mysql, sys,...).
Para crearlas, debemos de llamar al servicio mysql con un parámetro de la forma:
1 sudo mysqld --initialize-insecure
De esta forma se crean las bases de datos del sistema y un usuario root sin contraseña.
Será necesario conectarnos de alguna de las formas que vimos en los puntos anteriores y asignar un password a root. Recordar que todas las herramientas cliente llevaban el parámetro -p pero si no se tiene password, dicho parámetro no debe ponerse...
Puesta en marcha de múltiples servidores Mysql


  • El fichero que permite 'iniciar' una nueva instancia de Mysql es mysqld o mysqld_safe.
Al hacerlo de esta forma, desde consola, ésta queda bloqueada. Si quisiéramos 'parar' el servicio deberíamos hacerlo desde una herramienta cliente, como mysqladmin, ya comentando anteriormente, de la forma: mysqladmin -uroot -p shutdown


  • Pero qué pasa si queremos cargar otro archivo de configuración ?
En ese caso, debemos para el servicio (si está iniciado) de Mysql y pasar como parámetro la opción: sudo mysqld --defaults-file=/path/archivo.cnf'


  • Para poder tener varios servidores funcionando tendremos que cambiar el directorio de datos, el de log y el puerto de escucha para que no coincidan.
Tampoco podrán tener el mismo socket y pid.


  • Para poder gestionar múltiples servidores mysql podemos hacer uso de la herramienta cliente: mysqld_multi.
Será necesario tener un usuario creado previamente con el mismo nombre/password en todas las instancias a gestionar con permiso SHUTDOWN sobre todas las instancias.
Este usuario es el que va a utilizar la herramienta para parar o iniciar una de las instancias.
Esta información suele ir en un archivo de configuración (my.cnf) en el directorio /etc/mysql/:
1 [mysqld_multi]
2 mysqld     = /usr/local/mysql/bin/mysqld_safe
3 mysqladmin = /usr/local/mysql/bin/mysqladmin
4 user       = multi_admin  # Usuario de Mysql con acceso a todas las instancias y permiso para SHUTDOWN
5 pass   = my_password     # En la versión 5.7 se cambió password por pass. No es necesario ponerlo en el archivo. Se puede pasar por la línea de comandos.
A continuación dentro del mismo archivo se define la información para cada instancia, variando el puerto, socket y directorio de datos.
Cada instancia debe de tener de nombre mysqldX siendo 'X' un número.
 1 [mysqld2]
 2 socket     = /tmp/mysql.sock2
 3 port       = 3307
 4 pid-file   = /usr/local/mysql/data2/hostname.pid2
 5 datadir    = /usr/local/mysql/data2
 6 user       = unix_user1    # (normalmente mysql)
 7 log_error  = /var/log/mysql/error2.log
 8 
 9 [mysqld3]
10 socket     = /tmp/mysql.sock3
11 port       = 3308
12 pid-file   = /usr/local/mysql/data3/hostname.pid3
13 datadir    = /usr/local/mysql/data3
14 user       = unix_user2     # (normalmente mysql)
15 log_error  = /var/log/mysql/error3.log
Ahora podríamos iniciar las instancias utilizando el número asociado a cada una de ellas.
1 mysqld_multi start 2,3
O para las instancias:
1 mysqld_multi stop 2
Si no queremos tener el password puesto en el fichero de configuración podemos escribirlo en la consola:
1 mysqld_multi stop 2 --password=root
Para ver el estado de cualquier instancia (en el ejemplo de la instancia 2) :
1 mysqld_multi report 2
Ejercicio: Puesta en marcha de dos servidores Mysql
  • Servidor 1: Escuchando en el puerto 3307 y directorio de datos '/var/lib/mysql'.
Su fichero de configuración será: /mysql/mysqld1.cnf
  • Servidor 2: Escuchando en el puerto 3308 y directorio de datos '/var/lib/mysql2'
Su fichero de configuración será: /mysql/mysqld2.cnf


  • Notas aclaratorias:
  • Cada servidor tiene que tener un archivo de socket y pid diferente.
  • Si se van a crear en el directorio por defecto, no tenemos que tocar los permisos en Apparmor,pero recordar que el directorio está borrado si el servicio por defecto está parado.
Por lo tanto, si el servicio por defecto está iniciado (con la orden 'service mysql start') el directorio está creado y no hay que hacer nada (en este caso habría 3 instancias de Mysql ejecutándose)
En caso de que el servicio esté parado, tendréis que crear manualmente el directorio (/var/run/mysql/) e dar los permisos adecuados.
  • Recordar que si después iniciáis el servidor con 'service mysql start' y lo paráis, el directorio será borrado otra vez.
  • Recuerda cambiar los permisos de los directorios y editar el archivo del sistema de seguridad apparmor.


Solución Ejercicio: Puesta en marcha de dos servidores Mysql
  • En esta solución vamos a iniciar los dos servicios desde la línea de comandos, pero podríamos utilizar el 'clásico' servicio utilizando la orden: service mysql start y después iniciar otro desde la línea de comandos.
Nota: Recordar que como el socket/pid lo vamos a crear en el directorio por defecto, debemos de tener el servicio iniciado o crear el directorio nosotros manualmente asignando los permisos adecuados.
Cambiar el directorio donde se crea el pid y socket de Mysql
  • En LINUX, cada vez que iniciamos el servicio se crea el directorio /var/run/mysqld que es donde se crean varios archivos necesarios para el funcionamiento de Mysql (como el identificador de proceso y el socket) y cuando el servicio se para, los archivos son borrados y el directorio se borra (comprobarlo, no siempre pasa,depende de la distribución).
Si quisiéramos cambiar el lugar donde se crean estos datos, tendríamos que modificar el archivo de configuración, pero cuando iniciemos el servicio, este no va a crear el nuevo directorio (podemos ir a la sección 'run' del archivo /etc/init.d/mysql, y comprobar como se crea el directorio /var/run/mysqld).
Por lo tanto tendremos que ser nosotros los que creemos dicho directorio y darle los permisos adecuados.


Por ejemplo, el directorio anterior está 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 crear el directorio correspondiente y darle permiso de acceso a Mysql.
  • En el caso de Mysql debemos cambiar el propietario del usuario/grupo al usuario/grupo 'mysql' y dar permiso 755 sobre la carpeta.
Para ello, podemos ejecutar las órdenes:
1 sudo chown mysql:mysql /nuevo/directorio
2 su chmod 755 /nuevo/directorio
  • Después, la distribución Linux basada en Ubuntu incorpora en el Kernel un sistema de seguridad de acceso a archivos. Este sistema se llama AppArmor.
Su archivo de configuración, para Mysql, se encuentra en el archivo /etc/apparmor.d/usr.sbin.mysqld
Tendremos que editarlo y mirar los permisos que tiene para el directorio que queremos cambiar, y o bien, podemos crear un alias para que el 'viejo' apunte al nuevo directorio o bien copiamos y pegamos las líneas correspondientes cambiando el directorio.
Por ejemplo, para cambiar de directorio el 'pid' y el 'socket' podemos ver que dicho archivo tiene los siguientes permisos:
1 # Allow pid, socket, socket lock file access
2   /var/run/mysqld/mysqld.pid rw,
3   /var/run/mysqld/mysqld.sock rw,
4   /var/run/mysqld/mysqld.sock.lock rw,
5   /run/mysqld/mysqld.pid rw,
6   /run/mysqld/mysqld.sock rw,
7   /run/mysqld/mysqld.sock.lock rw,
Por lo tanto, tendremos que hacer un alias, editando el archivo nano /etc/apparmor.d/tunables/alias y escribiendo:
1 alias /var/run/mysqld/ -> /nuevo/directorio/,
O bien modificamos el directorio o bien copiamos y pegamos las líneas anteriores, cambiando el directorio:
1 # Allow pid, socket, socket lock file access
2   /nuevo/directorio/mysqld.pid rw,
3   /nuevo/directorio/mysqld.sock rw,
4   /nuevo/directorio/mysqld.sock.lock rw,
5   /nuevo/directorio/mysqld.pid rw,
6   /nuevo/directorio/mysqld.sock rw,
7   /nuevo/directorio/mysqld.sock.lock rw,
Después de cualquier cambio es necesario 'recargar' las modificaciones:
1 sudo service apparmor reload
En el caso del pid y socket, esto último no llega.
Si intentamos iniciar el servicio dará un error. Si ejecutamos la orden: sudo journalctl -xe podemos comprobar que indica que necesita acceder en forma de lectura a ciertos directorios.
Tendremos que añadirlos:
1   /proc/* r,
2   /proc/*/status r,
3   /sys/devices/system/node/ r,
4   /sys/devices/system/node/node0/meminfo r,
Después de cualquier cambio es necesario 'recargar' las modificaciones:
1 sudo service apparmor reload


  • En este punto ya debemos ser capaces de iniciar el servicio y podemos comprobar como en el nuevo directorio aparecen los archivos del pid y socket.
  • Importante: Recordar que si utilizamos una herramienta cliente, como 'mysqlshow' o 'mysql',...debemos cambiar su configuración para que busque el archivo de socket en su nueva dirección.


  • Podemos ver como sería todo el proceso:

De las herramientas cliente

  • Alguna de las herramientas clientes de las que disponemos son:
  • mysql
  • mysqladmin
  • mysqlshow
  • .......
  • Todas ellas tienen múltiples opciones de configuración, las cuales pueden ser cargadas a través de un archivo de configuración o directamente desde la línea de comandos.
Si se realiza desde la línea de comandos, es necesario utilizar doble guión al comienzo de la opción, mientras que si se pone en un archivo de configuración, no es necesario.


  • Cada herramienta cliente tiene su propia sección de configuración, pero podemos hacer uso de la sección [client] la cual afectará a todas las herramientas clientes.


  • 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


  • En el caso de la herramienta mysqlshow (que lo único que hace es ejecutar sentencias SQL y mostrar el resultado) lee sus opciones de configuración de la sección [mysqlshow], y podemos ver todas las opciones que tiene en este enlace.
  • En el caso de la herramienta mysqladmin, herramienta que nos permite realizar tareas administrativas, lee sus opciones de configuración de la sección [mysqladmin], y podemos ver todas las opciones que tiene en este enlace.
  • IMPORTANTE: Cuando tenemos varias instancias de Mysql escuchando en puertos diferentes, no nos llega con hacer que la herramienta cliente se conecte al puerto en el que escucha.
Al hacerlo de esta forma, la herramienta cliente intentará utilizar el socket 'por defecto' y no hará caso del parámetro 'port'.
Para evitarlo, tenemos varias opciones:
  • Indicar cual es el socket que debe utilizar la herramienta cliente, con el parámetro 'socket=/ruta/fichero.sock'
  • Indicando que el protocolo a utilizar sea el TCP, con el parámetro protocol de la forma 'protocol=tcp' y además debemos de poner la opción --host con valor 127.0.0.1 o la opción --protocol=tcp.


  • Un par de ejemplos:
  • Directamente pasando el parámetro por la línea de comandos:
1 mysqlshow -uroot -p --port=3308 --host=127.0.0.1
1 mysqlshow -uroot -p --socket=/var/run/mysqld/mysqld.sock


  • Creando una sección en el archivo de configuración /etc/mysql/conf.d/mysql.cnf:
1 [mysqlshow]
2 port=3308
3 socket=/var/run/mysqld/mysqld.sock
Nota: Podemos crear otro archivo de configuración con cualquier nombre, pero siempre debe de tener extensión .cnf y ponerlo en /etc/mysql/conf.d/.
También se puede poner en el archivo ~/.my.cnf creado en el perfil del usuario.
Se conectará al gestor que esté escuchando en el puerto tcp/ip 3308.


Ejercicio
  • Partiendo del ejercicio anterior en el que se creaban dos instancias de Mysql en dos puertos diferentes, crea dos usuarios a nivel del S.O. Linux e haz que uno de ellos se conecte a uno de los servidores Mysql y el otro al contrario.
Para crear un usuario desde Linux, puedes hacerlo gráficamente o a través de la consola con la orden: adduser NOMBRE_USUARIO
Tendrás que crear un archivo de configuración por cada usuario.
Recuerda que el archivo de configuración se crea en el directorio del usuario (/home/nombre_usuario/) y debe de tener de nombre .my.cnf (con un punto delante).
Desde consola, podéis crearlo con la orden: nano /home/nombre_usuario/.my.cnf. Para guardar debéis presionar las teclas Control+O y para salir las teclas Control+X


Solución Ejercicio

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.
Sólo se podrán cambiar su valor aquellas variables de tipo 'dinámico'. Las de solo lectura será necesario reiniciar el servidor.

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 la sesión del cliente conectado.
Puede existir variables que tienen un valor global y de sesión. Quiero esto decir que inicialmente recogen un valor inicial de la variable Global y después almacena dicho valor en forma de variable local (de sesión). De tal forma que una modificación en dicho valor (de sesión) no afecta al valor de la variable con el alcance 'global'. De la misma forma, si modificamos un valor global y un usuario ya estaba previamente conectado, teniendo un valor local (de sesión), no le va a afectar. Sólo afectará a los nuevos usuarios conectados.
  • Cuando un servidor Mysql se inicia, carga por defecto un valor para cada una de las variables globales.
Dicho valor puede ser modificado cambiándolo en el archivo de configuración o enviándolo como parámetro cuando el gestor se inicia.
Los valores de las variables globales (si son dinámicas) pueden ser modificados pero si el servidor se reinicia se volverá a inicializarlos con los valores del archivo de configuración.
Si una variable es global y no tiene una versión 'de sesión', su modificación lleva consigo que afecte a todos los usuarios (por ejemplo la variable general_log)
Si tiene un valor 'de sesión', su valor se mantiene y sólo afectará a los nuevos usuarios conectados (por ejemplo la variable sql_select_limit).


  • 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.


Mysql conf 6.jpg
  • 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
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.
Podemos consultar la lista de variables de estado y lo que significan en este enlace
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 sql: FLUSH STATUS. El resto sería necesario reiniciar el servidor para que se inicializaran.
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.


  • SHOW ERRORS: Muestra los errores que se han registrado después de la última sentencia ejecutada con error para el usuario actual.
  • SHOW WARNINGS: Muestra los errores, avisos o informaciones que se han registrado después de la última sentencia ejecutada con error para el usuario actual.
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 segundo caso será necesario reiniciar el servicio.
Por ejemplo:
1 SET GLOBAL max_connections = 1000;
1 SET SESSION sort_buffer_size = 10*1024*1024;


  • Para asignar su valor 'original' podemos hacer uso de la palabra 'default' de la forma:
1 SET SESSION sort_buffer_size = DEFAULT;


  • Podemos consultar el valor de una variable global/local de la siguiente forma:
1 SELECT @@SESSION.sort_buffer_size;
2 SELECT @@GLOBAL.sort_buffer_size;
Dependiendo de las variables podemos tener un valor de sesión diferente de uno global.


  • Si estamos utilizando la herramienta MysqlWorkBench, podemos ver gráficamente las variables del sistema y sus valores, como viene indicado en este enlace.
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.
  • general_log: Permite activar el log general de Mysql. Al activarlo, todas las sentencias enviadas al servidor serán registradas en el fichero indicado en la siguiente variable del sistema.
  • general_log_file: Fichero donde se registra toda la actividad de Mysql. Por defecto tendrá de nombre 'host_name.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 /var/lib/mysql/host_name.err y se encuentra en el directorio de datos. En la versión 5.7 viene establecido un valor por defecto: /var/log/mysql/error.log. En este fichero se registran los errores que pueda tener el servidor Mysql cuando se intenta levantar el servicio o cuando estando funcionando, se produce algún tipo de error que provoque la 'caída' del mismo.
  • 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.


  • Nota: Algunas variables del sistema no 'funcionan' desde el PhpMyAdmin o desde el MysqlWorkBench pero si desde las herramientas clientes del Mysql, como el ejecutable 'mysql'.
Por ejemplo, la variable del sistema sql_select_limit.
Variables relacionadas con la memoria caché
  • Nota: A partir de versiones posteriores a Mysql 5.7 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:
  • 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.

Mysql conf 7.jpg 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. En la ayuda habla del sistema operativo Windows y Linux.
En Linux solamente hace caso a la seguinte variable:
bind-address: Indica la dirección IP desde la cual el servidor Mysql va a aceptar conexiones. Se la comentamos deja conectar desde cualquier dirección IP.
Se queréis hacer una práctica con esta variable, tendréis que modificar un usuario de los creados en Mysql (utilizando la herramienta MysqlWorkBench por ejemplo, tendréis que acceder a la sección de usuarios) para que pueda conectarse desde cualquier IP cambiando la propiedad 'Limit to host' al valor:%


  • 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.

Ejercicios

  • En la modificación de cualquier variable del sistema, tendréis que verificar que sea dinámica y su alcance.
En caso de no ser dinámica tendréis que modificar el archivo de configuración (recordar hacer un backup previo) y reiniciar el servicio.
  • Recuerda: Cualquier modificación en el fichero de configuración lleva consigo la necesidad de reiniciar el servicio.
Ejercicio 1
  • Modifica las variables del sistema que permiten registrar toda la actividad del gestor Mysql y haz que el archivo de registro sea /var/log/mysql/general.log
  • Realiza una consulta SQL y comprueba como queda registrada en el fichero de log.
  • Vuelve a dejar todas las variables del sistema utilizadas a sus valores por defecto.
Ejercicio 2
  • Previamente sal de cualquiera herramienta cliente (phpmyadmin, workbench, mysql, mysqladmin,...) conectada al servidor.
Reinicia el servicio.
  • Conéctate con tres instancias de la herramienta cliente mysql utilizando un usuario creado previamente (podéis crear el usuario desde WorkBench, es más sencillo).
Comprueba el valor de la variable de estado que indica cuantas conexiones simultáneas fueron establecidas al servidor Mysql (para ello busca la cadena 'connection' en las variables de estado.
Modifica la variable del sistema que limita el número de conexiones simultáneas a 3 (busca que variable es de la misma forma que en el paso anterior). Piensa: Con que usuario tendrás que ejecutar la orden SQL.
Intenta conectarte con una nueva instancia de Mysql utilizando el usuario creado previamente.
Deja la variable del sistema a su valor por defecto.

Curiosidad: La variable del sistema que limita el número de conexiones simultáneas siempre tiene un valor+1, ya que reserva una conexión para que el usuario con permiso SUPER (root en nuestro caso) pueda conectarse.

Solución Ejercicio 2
Ejercicio 3
  • Previamente crea un usuario de Mysql que pueda conectarse desde cualquier máquina (ip).
Recuerda que (lo vimos en clase) debemos poner '%' en el campo 'Limit to host'.
  • Modifica la variable del sistema para que se pueda conectar a Mysql desde cualquier ip.
  • Cambia el tipo de red en la máquina virtual y escoge la opción 'Adaptador puente'. Averigua cual es la dirección IP de la máquina virtual y conéctate a ella desde la máquina real utilizando el usuario creado previamente.
  • Deja el archivo de configuración como estaba origalmente.


Solución Ejercicio 3

Configuración en MysqlWorkBench

  • Desde el WorkBench podemos visualizar los valores de las opciones de configuración y cambiarlas de forma gráfica.

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
  • Dependiendo de cada versión, los requerimientos pueden variar, por lo que será necesario leer la sección correspondiente en la que vienen indicados los pasos a seguir.
Podemos ver cuales son en el caso de querer actualizar una versión de Mysql a la versión Mysql 5.7 en este enlace.



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