PDF de programación - Administración de MySQL

Administración de MySQL

Noviembre 2005

Jordi Llonch
[email protected]

http://creativecommons.org/licenses/by-sa/2.0/

Administración de MySQL

Índice

• Conceptos de MySQL
• Instalación
• Configuración
• Tipos de campos
• Introducción a la administración
• Monitor (aplicación cliente)
• Permisos
• Tuning
• Monitoraje (mytop)
• Chequear y reparar tablas
• Backup
• Replicar
• phpMyAdmin

2

Administración de MySQL

Conceptos de MySQL

• MySQL es un motor de base de datos SQL con las siguientes

características:

– Muy rápido.

– Multi-thread.

– Multiusuario.

• Disponemos de dos tipos de licéncia:

– Open Source/Software libre (GNU General Public License).

– Standard commercial license.

3

Administración de MySQL

Conceptos de MySQL: Motores y tipos de tablas

– Es el motor original. En deprecated a partir de la versión 4.1.

• ISAM

• MyISAM

• HEAP

• MERGE

– Reemplazo de ISAM.
– Tipo de tabla por defecto.
– No soporta transacciones.

– Tablas en memoria.
– Actualmente se conoce como MEMORY.
– No soporta transacciones.

– Permite que una colección de tablas MyISAM sean tratadas como

una única tabla.

– No soporta transacciones.

4

Administración de MySQL

Conceptos de MySQL: Motores y tipos de tablas

• BDB (Berkeley DB)

– Soporta transacciones.
– No funciona en todas las plataformas.

• InnoDB

– Soporta transacciones (ACID compliant).
– Bloqueo a nivel de fila (incrementa la capacidad de concurrencia y

rendimiento).

– Se puede mezclar InnoDB con otros tipos de tablas en la misma

bbdd.

• NDB Cluster

– Soporta tablas que se encuentran particionadas en diferentes

ordenadores.

5

Administración de MySQL

Instalación

• Supondremos un sistema Fedora Core 3:

– Instalaremos los siguientes paquetes:

• rpm -i mysql-3.23.58-13.i386.rpm
•
• Si queremos habilitar el soporte de PHP para MySQL:
• rpm -i php-mysql-4.3.9-3.i386.rpm
•

• También podríamos instalar los binarios que podemos descargar en

la web oficial (http://dev.mysql.com/downloads/).

6

Administración de MySQL

Configuración

• Este documento explica algunas directivas de configuración de

MySQL.

• Archivo de configuración:

– my.cnf (plataforma Linux)
– my.ini (plataforma Windows)

7

Administración de MySQL

Configuración

• [client]

– password

= my_password

• contraseña general de acceso para los clientes (es aconsejable

usar el sistema de autentificación de MySQL)

– port = 3306

• puerto dónde se conectaran los clientes (acceso via TCP/IP)

– socket = /var/run/mysqld/mysqld.sock

• acceso mediante sockets Unix (localhost)

8

Administración de MySQL

Configuración

• [safe_mysqld]

– err-log = /var/log/mysql/mysql.err

• archivo dónde se registrarán los errores del servidor

• [mysqld]

– user

= mysql

• usuario bajo el cual se ejecuta el servidor

– pid-file = /var/run/mysqld/mysqld.pid

• Fichero en el que el servidor guarda el ID del proceso demonio de

escucha (daemon).

– socket = /var/run/mysqld/mysqld.sock

• Fichero en el que el servidor crea el socket

– port = 3306

• Puerto desde donde se aceptaran conexiones

9

Administración de MySQL

Configuración
• [mysqld]

– log = /var/log/mysql.log

• Archivo donde se registra el log

– basedir = /usr

• Directorio donde se encuentra MySQL.

– datadir = /var/lib/mysql

• Directorio donde están los datos

– tmpdir = /tmp

• Directorio temporal (para crear pej. tablas temporales)

– language

= /usr/share/mysql/english

• Archivo de mensajes

– skip-locking

parar el servidor)

– skip-networking

• No escucha conexiones TCP/IP.

• No usar sistema de bloqueo (para hacer chequeos se tendrá que

10

Administración de MySQL

Configuración

• [mysql.server]

– key_buffer=16M

• tamaño de buffer de los índices

– max_allowed_packet=1M

• tamaño máximo de un paquete. Se tendrá que incrementar si se

quiere utilizar columnas BLOB grandes.

– thread_stack=128K

• tamaño de cada hilo

– max_connections=650

• número máximo de conexiones simultáneas

11

Administración de MySQL

Configuración

• [mysql.server]

– wait_timeout=9600

– connect_timeout=10

• segundos que el servidor esperará actividad en conexiones no

interactivas antes de cerrar

• segundos que el servidor esperará un paquete de conexión antes

de responder con Bad handshake.

– max_connect_errors=10

• si hay más de este número de conexiones interrumpidas por el

servidor, el servidor es bloqueado

– log-slow-queries = /var/log/mysql/mysql-slow.log

• registra las consultas lentas

– skip-innodb

• no utiliza bases de datos tipo innodb

12

Administración de MySQL

Tipos de campos

• Tipos numéricos:

– TINYINT[(M)] [UNSIGNED] [ZEROFILL]

• Entero muy pequeño (-128 a 127 con signo / 0 a 255 sin signo).

– BIT/BOOL/BOOLEAN

• Sinónimo de TINYINT(1).
• Añadido en MySQL 4.1.0.
• Cero es falso. Valores no zero son considerados verdaderos.

– SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

• Entero pequeño (-32768 a 32767 con signo / 0 a 65535 sin signo).

– MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

• Entero medio (-8388608 a 8388607 con signo / 0 a 16777215 sin

– INT[(M)] [UNSIGNED] [ZEROFILL]

• Entero (-2147483648 a 2147483647 con signo / 0 to 4294967295

signo).

sin signo).

– INTEGER[(M)] [UNSIGNED] [ZEROFILL]

• Sinónimo de INT.

13

Administración de MySQL

Tipos de campos

• Tipos numéricos:

– BIGINT[(M)] [UNSIGNED] [ZEROFILL]

• Entero grande (-9223372036854775808 a 9223372036854775807

con signo / 0 to 18446744073709551615 sin signo)

– FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

• Número coma flotante pequeño.
• -3.402823466E+38 a -1.175494351E-38, 0, y 1.175494351E-38 a

3.402823466E+38

– DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

• Número coma flotante normal.
• -1.7976931348623157E+308 a -2.2250738585072014E-308, 0, y

2.2250738585072014E-308 a 1.7976931348623157E+308

– DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
– REAL[(M,D)] [UNSIGNED] [ZEROFILL]

• Sinónimos de DOUBLE.

14

Administración de MySQL

Tipos de campos

• Tipos numéricos:

– DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

• Número con decimal fijo.
• Se comporta com un CHAR.
• M es el total de dígitos y D es el número de decimales.

– DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
– NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
– FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

• Sinónimos de DECIMAL

15

Administración de MySQL

Tipos de campos

• Tipos fecha y hora:

– DATE

– DATETIME

• Fecha. Rango de '1000-01-01' a '9999-12-31'.
• Formato: 'YYYY-MM-DD'.

• Combinación de fecha y hora. Rango de '1000-01-01 00:00:00' a

'9999-12-31 23:59:59'.

• Formato: 'YYYY-MM-DD HH:MM:SS'.

• Rango de '1970-01-01 00:00:00' a aproximadamente el año 2037.

– TIMESTAMP[(M)]

– TIME

– YEAR[(2|4)]

• Tiempo. Rango de '-838:59:59' a '838:59:59'.
• Formato: 'HH:MM:SS'.

• Año en 2 dígitos o 4 dígitos. Rango de 1901 to 2155, and 0000 para

4 dígitos y de 70 a 69 (de 1970 a 2069) para 2 dígitos.

16

Administración de MySQL

Tipos de campos

• Tipos string:

– [NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]

• Cadena de carácteres de longitud fija.
• M de 0 a 255.

– CHAR

• Sinónimo de CHAR(1).

– [NATIONAL] VARCHAR(M) [BINARY]

• Cadena de carácteres de longitud variable.
• M de 0 a 255.
– TINYBLOB/TINYTEXT

• Una columna BLOB o TEXT de como máximo 255 carácteres.

– BLOB/TEXT

• Una columna BLOB o TEXT de como máximo 65,535 carácteres.

– MEDIUMBLOB/MEDIUMTEXT

• Una columna BLOB o TEXT de como máximo 16,777,215

carácteres.

17

Administración de MySQL

Tipos de campos

• Tipos string:

– LONGBLOB/LONGTEXT

carácteres.

– ENUM('value1','value2',...)

lista.

– SET('value1','value2',...)

• Una columna BLOB o TEXT de como máximo 4,294,967,295

• Enumeración.
• Cadena de carácteres que sólo puede tener un valor dado en la

• Lista que puede tener valor cero o más valores. Deben estar en la

lista y como máximo pueden haber 64 miembros.

18

Administración de MySQL

Introducción a la administración
• El sistema de base de datos MySQL está formado por diversos

componentes:

– El servidor MySQL:

• mysqld: es el demonio principal, realiza todas las manipulaciones de

bbdd y tablas.

• mysqld_safe: usado para iniciar el servidor, monitorizar y reiniciar en

caso de parada (antes de MySQL 4, safe_mysqld).

• mysqld_multi: si se ejecutan múltiples servidores en el mismo host.

– El cliente MySQL y utilidades:

• mysql: cliente interactivo que permite enviar instrucciones SQL
• mysqladmin: programa de administración
• mysqlcheck, myisamchk: utilidades de análisis, optimización y

• mysqldump, mysqlhotcopy: herramientas de backup o copia de base de

reparación de tablas.

datos a otros servidores.

•
• (Aunque muchas operaciones se podrán realizar con la utilidad

mysqladmin o alguna otra aplicación de administración es preferible
conocer el lenguaje SQL)

19

Administración de MySQL

Introducción a la administración

– El directorio de datos de MySQL:

• Es el directorio dónde se almacenan las bases de datos y archivos.

• Es importante conocer:

– Dónde estan situados los archivos que representan las bbdd.
– Archivos de log.
– Evitar que el sistema de archivos esté demasiado lleno.

20

Administración de MySQL

Introducción a la administración
• Administración General:

– Iniciar y parar el servidor.

• Se debe conocer cómo iniciar y parar el servidor manualmente.
• Qué hacer si no se inicia correctamente o se para inesperadamente.

– Mantenimiento de las cuentas de usuario.

• Se debe entender la diferencia entre las cuentas UNIX y las MySQL.
• Cómo crear y mantener cuentas de usuario.
• De que forma se deben conectar los nuevos usuarios al servidor.
• Cómo resetear un password perdido.

– Mantenimiento del archivo de log.

• Se deben entender los tipos de archivos de log que genera MySQL.
• Rotar y expirar los archivos de log para prevenir que se llene el sistema

de archivos.

– Backup y copia de base de datos.

• La copia de segur