PDF de programación - Guía rápida de administración de MySQL

Guía rápida de administración de MySQL

http://www.xtec.cat/~acastan/textos/Administracio...

Guía rápida de administración de MySQL

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

FUNCIONES DEL ADMINISTRADOR
INSTALACIÓN DE MYSQL Y CONFIGURACIÓN
INSTALACIÓN DE LA BASE DE DATOS DE EJEMPLO
FUNCIONAMIENTO DEL SERVIDOR MYSQL
MOTORES DE ALMACENAMIENTO
REPASO DE CONSULTAS
ADMINISTRACIÓN DE TABLAS Y BASES DE DATOS
ADMINISTRACIÓN DE USUARIOS
REALIZAR Y RESTAURAR COPIAS DE SEGURIDAD
IMPORTAR Y EXPORTAR DATOS
INTEGRIDAD Y REPARACIÓN DE TABLAS
ANALISIS Y OPTIMIZACIÓN DE TABLAS
ANÁLISIS Y OPTIMIZACIÓN DEL SERVIDOR MYSQL
CONECTANDO APLICACIONES CON BASES DE DATOS
REPLICACIÓN Y DISTRIBUCIÓN DE CARGA
CLÚSTER DE SERVIDORES MYSQL

FUNCIONES DEL ADMINISTRADOR

Las funciones de un administrador de bases de datos son:

* Gestión del software:

instalación del software,
actualización del software,
conexión de programas (por ej. ofimática) con el servidor de bases de datos.

* Gestión de las bases de datos:

creación de bases de datos,
importación y exportación de datos,
copias de seguridad y recuperación,
optimización de las tablas,
análisis de la integridad y reparación.

* Gestión de los usuarios:

creación de usuarios y bajas,
asignación de permisos.

* Gestión de los recursos:

monitorización del rendimiento y el tamaño,
gestión del espacio y asignación de quotas,
configuración óptima del servidor.

* Gestión de la seguridad:

monitorización de los accesos,
configuración segura del servidor.

INSTALACIÓN DE MYSQL 5.0 EN UBUNTU 7.04

* Instalaremos los paquetes 'mysql-server' y 'mysql-client'.

(Opcionalmente podemos instalar los paquetes 'mysql-admin', que es un
entorno gráfico de administración, y 'mysql-query-browser', que es un
entorno gráfico para realizar consultas.)

* La instalación crea en el sistema el usuario 'mysql' y el grupo 'mysql'.

* La instalación crea en el servidor de bases de datos el usuario 'root' sin
contraseña, que tiene control total sobre todas las bases de datos del
servidor. Para ponerle contraseña debemos escribir el la línea de comandos:

mysqladmin -u root password nueva_contraseña

(En algunas instalaciones también existe un usuario anónimo '' sin
contraseña, propietario de la base de datos de prueba 'test'.)

1 de 15

02/10/15 20:53

Guía rápida de administración de MySQL

http://www.xtec.cat/~acastan/textos/Administracio...

(En versiones posteriores de Ubuntu, la contraseña del usuario 'root' se
solicita en el momento de la instalación.)

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

* Por defecto hay una base de datos 'mysql' con información de MySQL y usuarios,
y otra base de datos 'information_schema' con información de bases de datos.

* 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

- /usr/bin/ , /usr/sbin/ , /usr/share/mysql/

Programas de MySQL

* Se puede iniciar la ejecución de varias maneras:

sudo /etc/init.d/mysql start
sudo /usr/sbin/mysql start
sudo /usr/bin/mysqld-multi
sudo /usr/bin/mysqld-safe

* Se puede parar la ejecución de varias maneras:

sudo /etc/init.d/mysql stop
sudo /usr/sbin/mysql stop
mysqladmin -u root -p shutdown

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

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

* Si quiero los mensajes en otro idioma debo modificar /etc/mysql/my.cnf
y cambiar la línea "[mysqld] language = ". Por ejemplo "laguage = spanish".

* Lecturas para profundizar:
- http://dev.mysql.com/doc/refman/5.0/es/server-side-scripts.html
- http://dev.mysql.com/doc/refman/5.0/es/instance-manager.html
- http://dev.mysql.com/doc/refman/5.0/es/configuring-mysql.html
- http://dev.mysql.com/doc/refman/5.0/es/server-shutdown.html
- http://dev.mysql.com/doc/refman/5.0/es/security.html
- http://dev.mysql.com/doc/refman/5.0/es/mysql.html
- http://dev.mysql.com/doc/refman/5.0/es/mysqladmin.html

INSTALACIÓN DE LA BASE DE DATOS DE EJEMPLO

Para las diferentes pruebas que haremos necesitaremos una base de datos.
Podemos descargar una sencilla base de datos con tres tablas en:

http://downloads.mysql.com/docs/world.sql.gz

Opcionalmente, también se puede descargar otra base de datos más compleja, con
más tablas, vistas, procedimientos almacenados, triggers, etc. en:

http://downloads.mysql.com/docs/sakila-db.tar.gz

2 de 15

02/10/15 20:53

Guía rápida de administración de MySQL

http://www.xtec.cat/~acastan/textos/Administracio...

Para trabajar con la base de datos 'world' haremos lo siguiente:

1) Descomprimirla en nuestra carpeta personal. Obtendremos un fichero
'world.sql' con sentencias SQL que al ejecutarlo en el servidor creará las
tablas de la base de datos.

2) Iniciar el cliente de MySQL:

mysql [-h dirección_servidor] -p -u usuario base_datos [< fichero_sql]

En nuestro caso:

mysql -p -u root

3) Creamos la base de datos y la seleccionamos:

CREATE DATABASE world;
USE world;

4) Ejecutamos el fichero 'world.sql':

SOURCE world.sql;

Fuera del cliente MySQL, desde la cónsola, podemos comprobar que se ha creado
un directorio para la base de datos y los ficheros para las tablas:

sudo ls -l /var/lib/mysql
sudo ls -l /var/lib/mysql/world

FUNCIONAMIENTO DEL SERVIDOR MYSQL

* Funcionamiento:
1) Los clientes se conectan a servidor.
2) Los clientes inician autentificación, codifican y envían peticiones,
comprimen y cifran peticiones, cachean los resultados del servidor, ...
3) El servidor procesa las peticiones y devuelve las respuestas.
4) Las peticiones son procesadas primero por la capa de manipulación, que las
desencripta, valida su sintaxis, las busca en la caché, y las envía al
correspondiente motor de almacenamiento.
5) Los motores de almacenamiento (MyISAM, InnoDB, Memory, ...) manejan la
representación en memoria y disco de bases de datos, tablas e índices, así
como generación de estadísticas y algunos logs.
6) La capa de manejo escribe logs a disco, guarda y lee caches en memoria,
lee logs binarios de la red, ... Los motores de almacenamiento guardan
datos (tablas, logs, ...) en disco y en memoria, envía datos a otros
servidores remotos, ...

* El servidor MySQL utiliza espacio en disco para almacenar lo siguiente:
- Los programas cliente y servidor, y sus librerías.
- Los ficheros de registro ("logs") y de estado.
- Las bases de datos.
- Los ficheros de formato de tablas ('*.frm') para todos los motores de
almacenamiento, y los ficheros de datos y ficheros de índices para algunos
motores de almacenamiento.

3 de 15

02/10/15 20:53

Guía rápida de administración de MySQL

http://www.xtec.cat/~acastan/textos/Administracio...

- Los ficheros de "tablespaces" de InnoDB, si el motor de almacenamiento
InnoDB está activado.
- Tablas temporales internas que han sobrepasado el límite de tamaño en
memoria y deben ser convertidas a tablas en disco.

* El servidor MySQL utiliza espacio en memoria para almacenar lo siguiente:
- Gestores de conexión (cada conexión consume memoria).
- Buffers que guardan tablas temporales internas que no han sobrepasado el
límite de tamaño en memoria.
- Cachés: caché de hosts, la caché de tablas, la caché de consultas, ...
- Una copia de la tabla de permisos.
- El contenido de las tablas HEAP (motor de almacenamiento en memoria). Su
fichero de formato ('*.frm') se continua guardando en disco.

* El servidor MySQL utiliza los siguientes buffers por cada cliente:
- Buffers de registros para las búsquedas secuenciales en tablas
('read_buffer_size') y para leer las líneas después de una ordenación
('read_rnd_buffer_size') normalmente conseguida mediante la cláusula ORDER.
- Buffer de join para las uniones de tablas.
- Buffer de ordenación para las operaciones de ordenación.
- Buffer de comunicaciones para intercambiar información con el cliente.
Comienza con un tamaño de 'net_buffer_length', pero si es necesario el
servidor aumenta su tamaño al señalado por 'max_allowed_packet'.

* Los límites que el sistema operativo puede imponer al servidor MySQL son:
- El máximo número de ficheros abiertos por proceso limita el tamaño máximo
de la caché de tablas, que guarda los descriptores de ficheros para los
ficheros de tablas.
- El máximo número de hilos de ejecución por proceso limita el número de
clientes que se pueden conectar simultáneamente al servidor MySQL.
- El 'bac