MySQL

1. Introducción

El MySQL es un gestor de base de datos, es el mas popular en el mundo de la informática, por diferentes aspectos, como su flexibilidad, su coste es económico inexistente, su versatilidad,…

2. Instalación de MySQL

Primero actualizar los repositorios de Ubuntu.

:~$ sudo –s
Password: ********
:~# cd /
:/# apt-get update

:/#

Instalar los siguientes paquetes, mysql-server, mysql-client, mysql-admin y mysql-query-browser. El paquete mysql-admin es básicamente un entorno gráfico para poder gestionar la base de datos y el mysql-query-browser es también un entorno gráfico para realizar consultas SQL, también encontramos otra seria de paquetes entre el que cabria destacar el mysql-navigator, se trata de un entorno gráfico para interactuar con las bases de datos (la instalación esta ligada al gusto del usuario). Aunque existen estos dos paquetes con sus respectivos entornos gráficos, todo lo que se puede realizar des de ellos también se puede realizar des de un Terminal.

:/# apt-get install

mysql-server mysql-client mysql-admin mysql-query-browser

:/#

Durante la instalación solicitara la introducción de un password para el root (administrador) de la base de datos, este root tendrá permisos generales en todo el gestor. No olvidar dicha contraseña, aunque mas adelante explicaremos como modificarla.

3. Alternativa a los paquetes mysql-admin y al mysql-query-browser

Existe en la red la aplicación PhpMyAdmin se trata de una herramienta escrita en PHP donde el principal objetivo es la administración de MySQL mediante paginas Web, vía Internet. En la actualidad permite crear y eliminar bases de datos, crear, eliminar y modificar tablas, borrar, editar y añadir campos y ejecutar cualquier sentencia SQL de consulta, administrar claves de campos, administrar privilegios de usuarios, exportar datos en diferentes formatos y se encuentra disponible en 50 idiomas. Para instalar se puede instalar des de Terminal como cualquier otro paquete, solo añadir que también incluye paquetes adicionales de Apache2 para poder acceder vía Web.

4. Configuración del password del MySQL para versiones antiguas

Este paso es únicamente en caso que la versión instalada del MySQL sea bastante anterior a la que estáis instalando, pero no os debería preocupar mucho. Esta acción debe realizarse al momento de finalizar la instalación del gestor.

:/# cd /usr/bin/
:/usr/bin# mysqladmin –u root password “new_password”
:/usr/bin#

5. Configuración del MySQL

Aquí se indica el file donde se puede modificar los parámetros del MySQL como puede ser las opciones básicas, el archivo de registro, el número de puerto,… Este file se encuentra en la siguiente dirección /etc/mysql/my.cnf

:/# cd /etc/mysql/
:/etc/mysql# gedit my.cnf
:/etc/mysql#

6. Verificar que el MySQL esta funcionando

:/# cd /etc/init.d/
:/etc/init.d# ./mysql status
* /usr/bin/mysqladmin Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribuye it Ander the GPL license
Server version 5.0.45-Debian_1ubuntu3.3-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 min 59 sec
Threads: 1 Questions: 46 Slow queries: 0 Opens: 30 Flush tables: 1 Open tables: 24 Queries
per second avg: 0.257
:/etc/init.d#

7. Conectar con MySQL

:/# mysql –h localhost –u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
mysql> \q
Bye
:/#

Donde localhost es la dirección del host y root el nombre del usuario. También existe el mismo comando mas breve.

:/# mysql –u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
mysql> quit
Bye
:/#

Estas mismas acciones se puede hacer de otra manera con el entorno gráfico llamado MySQL Administrador.

8. Conectar desde un host remoto

El MySQL por defecto no permite que cualquier usuario pueda acceder a la base de datos usando una conexión TCP. Para poder permitir esto se debe realizar dos pasos previos, es necesario crear un usuario con los permisos correspondientes para dicho usuario y editar el file my.cnf que se encuentra en el folder /etc/mysql/

:/# cd /etc/mysql/
:/etc/mysql# gedit my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# – «/etc/mysql/my.cnf» to set global options,
# – «~/.my.cnf» to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with –help to get a list of available options and with
# –print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain «#» chars…
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice =0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size =8
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement 🙂
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id =1
log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See
README.Debian!
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI «tinyca».
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
#
# * IMPORTANT: Additional settings that can override those from this file!
#
!includedir /etc/mysql/conf.d/

y modificar la siguiente línea

bind-address = 127.0.0.1

modificando la IP local por la IP del servidor. A continuación, reiniciar el servidor.

:/etc/mysql# cd /etc/init.d/
:/etc/init.d# ./mysql restart
:/etc/init.d# ./mysql start

:/etc/init.d# ./mysql stop

Comentario adicional

Para que mas ordenadores puedan tener acceso al servidor y para mayor seguridad, puedes poner una lista de las IP’s de los ordenadores que deseas que tengan acceso al servidor. En caso de no poder conectarte des de un ordenador remoto puedes editar el file hosts.allow que se encuentra en el folder /etc/

:/etc/mysql# cd /etc/
:/etc# gedit hosts.allow &

# /etc/hosts.allow: list of hosts that are allowed to access the system.
# See the manual pages hosts_access(5) and hosts_options(5).
#
# Example: ALL: LOCAL @some_netgroup
# ALL: .foobar.edu EXCEPT terminalserver.foobar.edu
#
# If you’re going to protect the portmapper use the name «portmap» for the
# daemon name. Remember that you can only use the keyword «ALL» and IP
# addresses (NOT host or domain names) for the portmapper, as well as for
# rpc.mountd (the NFS mount daemon). See portmap(8) and rpc.mountd(8)
# for further information.
#

y añadir la siguiente línea mysqld: all

9. Comentario al uso de SQL

Los nombres de los usuarios, de las bases de datos, de las tablas,… son sensibles al uso de mayúsculas y minúsculas. No como las palabras clave de SQL, por ello debemos tener cuidado al momento de escribir correctamente el nombre de la base de datos, del usuario o las tablas.
10. Gestión de usuarios

Cualquier creación, modificación o eliminación de un usuario debe realizarse des de root (administrador).
Crear un usuario:
mysql>
mysql> CREATE USER user_name IDENTIFIED BY ‘password’;
Query OK, 0 rows affected (0.18 sec)
mysql>
Eliminar un usuario:
mysql>
mysql> DROP USER user_name;
Query OK, 0 rows affected (0.18 sec)
mysql>
Permisos a un usuario:
Los permisos se componen de tres categorías Nivel global, Nivel de base de datos y Nivel de tabla.
mysql>
mysql> GRANT SELECT ON *.* TO user_name;
Query OK, 0 rows affected (0.00 sec)
mysql>
Quitar permisos a un usuario:
mysql>
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name;
Query OK, 0 rows affected (0.00 sec)
mysql>

11. Gestión de bases de datos

Comprobar si la base de datos existe:

mysql>
mysql> USE name_database;
ERROR 1049 (42000): Unknown database ‘bristoldata’
mysql>
Crear una base de datos:
Esta acción se puede realizar des de un usuario normal, no es necesario que sea el root,
siempre y cuando este usuario tenga los permisos para dicha acción. Además debéis
conectaros al mysql con dicho usuario ‘:/# mysql –h localhost –u bristolFFF –p’
mysql>
mysql> CREATE DATABASE name_database;
Query OK, 1 row affected (0.09 sec)
mysql>
Eliminar la base de datos:
mysql>
mysql> DROP DATABASE name_database;
Query OK, 0 rows affected (0.00 sec)
mysql>
Verificar la lista de bases de datos:
mysql>
mysql> SHOW DATABASES;
+————————————+
| Database |
+———————————— +
| information_schema |
| mysql |
| bristoldata |
+———————————— +
3 rows in set (0.00 sec)
mysql>
Verificar la lista de tablas:
mysql>
mysql> SHOW TABLES;
+————————————+
| Tables_in_bristoldata |
+———————————— +
| bristol |
| parroquia |
| article |
| banc |
+———————————— +
4 rows in set (0.00 sec)
mysql>
Inserir en la base de datos:
mysql>
mysql> INSERT INTO name_database.name_table VALUES (8,’Fontaneda’);
Query OK, 1 row affected (0.00 sec)
mysql>
Consulta de la base de datos:
mysql>
mysql> SELECT * FROM name_database.name_table;
+—————————+————————————-+
| parroquia_codi | parroquia_nom |
+—————————+————————————-+
| 1 | Canillo |
| 2 | Encamp |
| 3 | Ordino |
| 4 | La Massana |
| 5 | Andorra la Vella |
| 6 | Sant Julià de Lória |
| 7 | Escaldes-Engordany |
+—————————+————————————-+
8 rows in set (0.00 sec)
mysql>

12. Backup de bases de datos

A la base de datos a la que sea desea realizar el backup y mas tarde restaurar el backup debe existir en el servidor MySQL.
Crear el Backup:

:/# mysqldump –h localhost –u root –p name_database > backup.sql
Enter password: ******
:/#
Se puede realizar de esta otra manera.
/# mysqldump –u root –p name_database > backup.sql
Enter password: ******
:/#
El ‘backup.sql’ es el file que se crear para guardar la base de datos, este mismo file será el
que se utilice para restaurar la base de datos.
Restaurar el Backup:
:/# mysql –h localhost –u root –p name_database < backup.sql
Enter password: ******
:/#
Se puede realizar de esta otra manera.
/# mysql –u root –p name_database < backup.sql
Enter password: ******
:/#

13. Copia de seguridad de las bases de datos de MySQL

En caso de querer realizar una copia de seguridad de las bases de datos que hay en el servidor MySQL, existe el siguiente script para realizar dicha copia.

#!/bin/bash
## BEGIN CONFIG ##
HOST=localhost
USER=usuario
PASS=secreto
BACKUP_DIR=Backup
## END CONFIG ##
if [ ! -d $BACKUP_DIR ]; then
mkdir -p $BACKUP_DIR
fi
# Backup de MySQL
MYSQL_DBS=$(mysqlshow -h $HOST -u $USER -p$PASS | awk ‘ (NR > 2) && (/[a-zA-
Z0-9]+[ ]+[|]/) && ( $0 !~ /mysql/) { print $2 }’);
for DB in $MYSQL_DBS ; do
echo «* Backuping MySQL data from $DB@$HOST…»
mysqldump -h $HOST -u $USER -p$PASS $DB > $BACKUP_DIR/mysql_$DB.sql
done

14. Recuperar password del root del servidor MySQL

En caso que deseemos recuperar o restaurar el password del root u otro usuario del servidor MySQL debemos realizar lo siguiente.

:/# cd /etc/init.d/
:/etc/init.d# ./mysql stop
* Stopping MySQL database Server mysqld [OK]
:/etc/init.d# cd /
:/# mysqld_safe –skip-grant-tables &
[1] 6884
:/etc/init.d# Starting mysqld daemon with databases from /var/lib/mysql
mysql_safe[6921]: started
:/# mysql –u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with –A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
mysql> UPDATE user SET Password=PASSWORD(‘new_password’) WHERE User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye
:/# killall mysqld;
:/# STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6945]: ended
:/# cd /etc/init.d/
:/etc/init.d# ./mysql start
* Starting MySQL databases Server mysqld [OK]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
:/etc/init.d#

15. Configurar MySQL para que se encienda al iniciar el PC

El objetivo de este apartado es realizar una breve explicación del porque el servidor MySQL se inicia al encenderse el ordenador. El file que a continuación muestro se crear automáticamente al instalar los paquete de mysql. Dicho file se encuentra en el folder /etc/init.d/ llamado mysql.

:/# cd /etc/init.d/
:/etc/init.d# gedit mysql &
:/etc/init.d#

#!/bin/bash
#
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $syslog mysql-ndb-mgm
# Required-Stop: $syslog
# Should-Start: $local_fs $remote_fs $network $named $time
# Should-Stop: $local_fs $remote_fs $network $named $time
# Default-Start: 2 3 4 5
# Default-Stop: 016
# Short-Description: Start and stop the mysql database server daemon
# Description: Controls the main MySQL database server daemon «mysqld»
# and its wrapper script «mysqld_safe».
### END INIT INFO
#
set -e
set -u
${DEBIAN_SCRIPT_DEBUG:+ set -v -x}
test -x /usr/sbin/mysqld || exit 0
. /lib/lsb/init-functions
SELF=$(cd $(dirname $0); pwd -P)/$(basename $0)
CONF=/etc/mysql/my.cnf
MYADMIN=»/usr/bin/mysqladmin –defaults-file=/etc/mysql/debian.cnf»
# priority can be overriden and «-s» adds output to stderr
ERR_LOGGER=»logger -p daemon.err -t /etc/init.d/mysql -i»
# Safeguard (relative paths, core dumps..)
cd /
umask 077
# mysqladmin likes to read /root/.my.cnf. This is usually not what I want
# as many admins e.g. only store a password without a username there and
# so break my scripts.
export HOME=/etc/mysql/
## Fetch a particular option from mysql’s invocation.
#
# Usage: void mysqld_get_param option
mysqld_get_param() {
/usr/sbin/mysqld –print-defaults \
| tr » » «\n» \
| grep — «–$1» \
| tail -n 1 \
| cut -d= -f2
}
## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
# check for config file
if [ ! -r /etc/mysql/my.cnf ]; then
log_warning_msg «$0: WARNING: /etc/mysql/my.cnf cannot be read. See
README.Debian.gz»
echo «WARNING: /etc/mysql/my.cnf cannot be read. See
README.Debian.gz» | $ERR_LOGGER
fi
# check for buggy options
if grep -q ^expire.logs.days $CONF && ! grep -q ^log.bin $CONF; then
log_failure_msg «$0: ERROR: Using expire_logs_days without log_bin crashes the
server. See README.Debian.gz»
echo «ERROR: Using expire_logs_days without log_bin crashes the server.
See README.Debian.gz» | $ERR_LOGGER
exit 1
fi
# check for diskspace shortage
datadir=`mysqld_get_param datadir`
if LC_ALL=C BLOCKSIZE= df –portability $datadir/. | tail -n 1 | awk ‘{ exit ($4>4096) }’;
then
log_failure_msg «$0: ERROR: The partition with $datadir is too full!»
echo «ERROR: The partition with $datadir is too full!» | $ERR_LOGGER
exit 1
fi
}
## Checks if there is a server running and if so if it is accessible.
#
# check_alive insists on a pingable server
# check_dead also fails if there is a lost mysqld in the process list
#
# Usage: boolean mysqld_status [check_alive|check_dead] [warn|nowarn]
mysqld_status () {
ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))
ps_alive=0
pidfile=`mysqld_get_param pid-file`
if [ -f «$pidfile» ] && ps `cat $pidfile` >/dev/null 2>&1; then ps_alive=1; fi
if [ «$1» = «check_alive» -a $ping_alive = 1 ] ||
[ «$1» = «check_dead» -a $ping_alive = 0 -a $ps_alive = 0 ]; then
return 0 # EXIT_SUCCESS
else
if [ «$2» = «warn» ]; then
echo -e «$ps_alive processes alive and ‘$MYADMIN ping’ resulted
in\n$ping_output\n» | $ERR_LOGGER -p daemon.debug
fi
return 1 # EXIT_FAILURE
fi
}
#
# main()
#
case «${1:-»}» in
‘start’)
sanity_checks;
# Start daemon
log_daemon_msg «Starting MySQL database server» «mysqld»
if mysqld_status check_alive nowarn; then
log_progress_msg «already running»
log_end_msg 0
else
/usr/bin/mysqld_safe > /dev/null 2>&1 &
# 6s was reported in #352070 to be too few when using ndbcluster
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
sleep 1
if mysqld_status check_alive nowarn ; then break; fi
log_progress_msg «.»
done
if mysqld_status check_alive warn; then
log_end_msg 0
# Now start mysqlcheck or whatever the admin wants.
output=$(/etc/mysql/debian-start)
[ -n «$output» ] && log_action_msg «$output»
else
log_end_msg 1
log_failure_msg «Please take a look at the syslog»
fi
fi
# Some warnings
if $MYADMIN variables | egrep -q have_bdb.*YES; then
echo «BerkeleyDB is obsolete, see /usr/share/doc/mysql-server-
5.0/README.Debian.gz» | $ERR_LOGGER -p daemon.info
fi
if [ -f /etc/mysql/debian-log-rotate.conf ]; then
echo «/etc/mysql/debian-log-rotate.conf is obsolete, see /usr/share/doc/mysql-
server-5.0/NEWS.Debian.gz» | $ERR_LOGGER -p daemon.info
fi
;;
‘stop’)
# * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible
# at least for cron, we can rely on it here, too. (although we have
# to specify it explicit as e.g. sudo environments points to the normal
# users home and not /root)
log_daemon_msg «Stopping MySQL database server» «mysqld»
if ! mysqld_status check_dead nowarn; then
set +e
shutdown_out=`$MYADMIN shutdown 2>&1`; r=$?
set -e
if [ «$r» -ne 0 ]; then
log_end_msg 1
[ «$VERBOSE» != «no» ] && log_failure_msg «Error: $shutdown_out»
log_daemon_msg «Killing MySQL database server by signal» «mysqld»
killall -15 mysqld
server_down=
for i in 1 2 3 4 5 6 7 8 9 10; do
sleep 1
if mysqld_status check_dead nowarn; then server_down=1; break; fi
done
if test -z «$server_down»; then killall -9 mysqld; fi
fi
fi
if ! mysqld_status check_dead warn; then
log_end_msg 1
log_failure_msg «Please stop MySQL manually and read /usr/share/doc/mysql-
server-5.0/README.Debian.gz!»
exit -1
else
log_end_msg 0
fi
;;
‘restart’)
set +e; $SELF stop; set -e
$SELF start
;;
‘reload’|’force-reload’)
log_daemon_msg «Reloading MySQL database server» «mysqld»
$MYADMIN reload
log_end_msg 0
;;
‘status’)
if mysqld_status check_alive nowarn; then
log_action_msg «$($MYADMIN version)»
else
log_action_msg «MySQL is stopped.»
exit 3
fi
;;
*)
echo «Usage: $SELF start|stop|restart|reload|force-reload|status»
exit 1
;;
esac

16. Anexo
MySQL
http://www.guia-ubuntu.org/index.php?title=MySql
http://mysql.conclase.net/curso/index.php?sen=CREATE_DATABASE
http://www.programacion.com/php/tutorial/mysql_basico/
PhpMyAdmin
http://es.wikipedia.org/wiki/PhpMyAdmin
http://www.phpmyadmin.net/home_page/index.php
http://www.guia-ubuntu.org/index.php?title=PhpMyAdmin