Cliente
El comando mysql es un programa (cli) para acceder al servidor de base de datos MySQL
Todo comando debe terminarse con ; o \G si se quiere que los resultados aparezcan de forma vertical
Comandos
Conectarse a un servidor # mysql -h SERVIDOR -u USUARIO -p
Listar las bases de datos: mysql> SHOW DATABASES;
Conectarse con una BD: mysql> USE NOMBRE_BD;
Listar las tablas: mysql> SHOW TABLES;
Listar las tablas y mostrar mas informacion sobre ellas: mysql> SHOW TABLE STATUS;
Servidor
Administracion de usuarios
Seccion del manual: MySQL User Account Management
Seccion del manual: Account Management Statements
Backup
mysqldump -h $host -u $user --password=$password --flush-logs --opt $db > $db.sql
Backup remoto
Con este script se puede hacer backup de todas las bases de datos de un servidor mysql desde otra maquina.
#backup completo de las bases de datos mysql
host=192.168.1.59
user=backup
password=algoimposiblederecordar
dir=/var/backup/mysql/full_backup
mkdir -p $dir
for db in $(mysql -h $host -u $user --password=$password -se 'show databases' | egrep -v "bk|ibdata" | tail -n +2); do
mysqldump -h $host -u $user --password=$password --flush-logs --opt $db > $dir/$db.sql
done
Sentencias que se estan ejecutando
Para mirar los procesos y sentencias sql que se estan ejecutando se pueden usar cualquiera de estos comandos:
mysql> SHOW FULL PROCESSLIST;
# mysqladmin processlist --verbose
Debido a la velocidad con la que se crean nuevos procesos es posible que sea mejor mirar este listado una vez por segundo lo cual se puede hacer con el comando:
# mysqladmin processlist --verbose -i1
Informacion sobre el estado
mysql> SHOW STATUS;
# mysqladmin extended-status
Las opciones GLOBAL y SESSION son nuevas en MySQL 5.0.2 Con GLOBAL, obtiene los valores de estado para todas las conexiones a MySQL. Con SESSION, obtiene los valores de estado para la conexión actual. Si no usa estas opciones, por defecto es SESSION. LOCAL es sinónimo de SESSION.
mysql> SHOW GLOBAL STATUS;
Una forma muy util de consultar esta informacion es mirando los cambios de los valores una vez por segundo:
# mysqladmin extended-status -i1 -r
Al mysqladmin que viene con MySQL 5.1 no le sirve la opcion -r en ese caso puede usar mext:
# mext -r -- mysqladmin extended-status -i1 -c3
Variables de estado
Algunas de las variables:
- Max_used_connections: El número máximo de conexiones que han sido utilizadas simultáneamente desde que el servidor ha sido iniciado.
- Threads_connected: El número de conexiones abiertas actualmente.
Threads_running: El numero de preguntas que se estan procesando en ese momento
- Slow_launch_threads: El número de subprocesos que han tardado en crearse más de slow_launch_time segundos.
- Slow_queries: El número de consultas que han tardado más de long_query_time segundos.
- Table_locks_waited: El número de veces que un bloque de tabla no se ha podido adquirir inmediatamente y se ha necesitado una espera. Si esto es alto, y tiene problemas de rendimiento, debería primero optimizar sus consultas y después, o bien utilizar replicación, o dividir sus tablas.
- Connections: El número de intentos de conexión (con éxito o no) al servidor MySQL.
- Handler_update: El número de peticiones de modificación de un registro en una tabla.
- Handler_write: El número de peticiones de inserción de un registro en una tabla.
- Select_scan: El número de joins que han hecho un escaneo total de la primera tabla.
- Handler_read_rnd_next: El número de peticiones para leer el siguiente registro en el archivo de datos. Éste es alto si está haciendo muchos escaneos de tablas. Generalmente esto sugiere que las tablas no están indexadas adecuadamente o que las consultas no están escritas para obetner ventaja de los índices que se tienen.
Una explicacion completa de las variables se puede ver en el manual de mysql seccion: Variables de estado del servidor
Variables de sistema
- Ver las variables de sistema:
# mysqladmin variables
mysql> SHOW GLOBAL VARIABLES;
Ver variables de la sesion actual: mysql> SHOW VARIABLES;
Modificar variables sin reiniciar
Dede MySQL 4.0.3, muchas variables de sistema son dinamicas y pueden cambiarse con el comando SET mientras el sistema esta corriendo:
- SET GLOBAL variable: para cambiar variables de sistema (no se cambia la de la sesion actual)
- SET variable: para cambiar variables de la sesion actual
Ejemplo:
- SET tmp_table_size=(36*1024*1024);
- SET GLOBAL tmp_table_size=(36*1024*1024);
Recuerde que al reinicar mysql estos cambios despareceran excepto que tambien los ponga en el archivo de configuracion de mysql
Analizar su desempeño y optimizar
Registro de consultas lentas
Para crear un archivo de registro con las consultas que se demoran mas de un segundo añada las siguientes variables al final su archivo de configuracion de mysql:
Para versiones de mysql menores a 5.1.12
long_query_time = 1 log-slow-queries = /var/log/mysql-slow-queries.log
En versiones a partir de 5.1.12 y antes de 5.1.29
long_query_time = 1 log-slow-queries = /var/log/mysql-slow-queries.log slow_query_log = 1
En las versiones a partir de 5.1.29
long_query_time = 1 slow_query_log_file = /var/log/mysql-slow-queries.log slow_query_log = 1
Si tiene algun problema o quiere profundizar sobre el tema, puede consultar la seccion The Slow Query Log del manual correspondiente a su version de mysql
Algunas veces en el registro el tiempo se da en el formato timestamp, para transformarlo a algo mas legible puede ejecutar la sentencia:
SELECT FROM_UNIXTIME(1247670725);
mysqlreport
mysqlreport es un script que hace un informe amigable sobre el estado del servidor, este informe da unas buenas bases para empezar a optimzar el desempeño del servidor. (en Debian este script es parte del paquete mysql-client)
Maatkit
Maatkit hace que las bases de datos de fuentes abiertas sean mas faciles y seguras de manejar. Proporciona simple y predecibles maneras de hacer cosas que de otra forma no se podrian. Esa es la razon por la que Maatkit ahora viene por defecto con muchas distribuciones GNU/Linux como Debian y CentOS. Usted puede usar Maatkit para probar que la replicacion esta funcionando correctametne, arreglar datos corruptos, automatizar tareas repetitivas, aumentar la velocidad de sus servidores y mucho mas.
Pruebas de desempeño
SysBench – benchmark tool articulo de mysqlperformanceblog.com
Is your MySQL Server Loaded ? articulo de mysqlperformanceblog.com
Cargar un archivo csv
Una forma util de cargar archivos de hojas de calculo es guardarlo en el formato CSV (Comma-separated values) y luego importarlos con el comando LOAD DATA.
Ejemplo sencillo, carga cada fila de un csv hecho con excel en la base de datos:
LOAD DATA INFILE '/tmp/datos.csv' INTO TABLE la_tabla FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n'
Ejemplo avanzado:
- Continua importando aunque haya errores (IGNORE)
- Carga un csv hecho con excel (LINES TERMINATED BY '\r\n')
- No carga la primera fila, asi que se puede usar para ponerle el nombre a cada columna (IGNORE 1 LINES)
- Carga directamente del csv la 1ª y 2ª columna, pero modifica los datos de la 3ª 4ª y 5ª
- La 3ª(cumpleanos) es una fecha en el formato csv de excel(DD/MM/AAAA) y se le convierte al formato de mysql
- La 4ª(email) y 5ª(telefono_movil) se revisa si es una cadena de texto vacia, si es asi se inserta NULL en la base de datos sino se pone el valor del csv
- En el campo fecha_insercion de la base de datos se carga el resultado de la funcion now() de mysql
LOAD DATA INFILE '/tmp/datos.csv' IGNORE INTO TABLE la_tabla FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (nombres, apellidos, @cumpleanos, @email, @telefono_movil) SET cumpleanos = str_to_date(@cumpleanos, 'd/m/Y'), email = NULLIF(@email,''), telefono_movil = NULLIF(@telefono_movil,''), fecha_insercion=now()
Por problemas tecnicos(tal vez el filtro de spam) en el ejemplo anterior:
d/m/Y debe cambiarse por el_simbolo_porcentajed/el_simbolo_porcentajem/el_simbolo_porcentajeY
Advertencias (Warnings)
Asi se ve la salida de load data cuando hay warnings:
Query OK, 495 rows affected, 221 warnings (0.00 sec) Records: 495 Deleted: 0 Skipped: 0 Warnings: 220
Si desea saber cuales son estos warnings, inmediatamente despues de ejecutar LOAD DATA, ejecute el comando:
SHOW WARNINGS
Comandos utiles
pager less -n -i -S
Blogs
Libros
High performance MySQL: optimization, backups, replication, and load balancing By Jeremy D. Zawodny, Derek J. Balling. Este libro fue traducido como MySQL Avanzado (Segunda Edición)
Soporte técnico