MySQL¶
Recomendaciones¶
- Utilizar siempre el tipo de dato DOUBLE para los reales (pag. 125 de libro "Desarrollo Web con PHP 6 y MySQL 5.1")
Instalación¶
Instalamos el paquete mysql-server:
$ apt-get install mysql-server
En caso de no haberlo configurado durante la instalación del paquete, ajustar el password de root con el comando:
$ mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost" = PASSWORD('new_password');
Por defecto sólo podemos entrar con el usuario root en local. Para permitir el acceso remoto ejecutar el siguiente comando:
$ mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit
También hay que permitir las conexiones remotas a nivel de red comentando la siguiente linea del fichero de configuración /etc/mysql/my.cnf:
1 | |
Conexión¶
MySQL Client¶
1 | |
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 89 to server version: 4.1.11-Debian_4-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Trabajando con Bases de Datos¶
Creación de BBDD¶
mysql> CREATE DATABASE irontec_db;
Query OK, 1 row affected (0.03 sec)
Selección de BBDD¶
Cambiar o elegir base de datos a utilizar:
mysql> USE irontec_db;
Database changed
Ver la base de datos seleccionada:
mysql> SELECT DATABASE();
+------------+
| database() |
+------------+
| irontec_db |
+------------+
1 row in set (0.00 sec)
Eliminación de BBDD¶
mysql> DROP DATABASE irontec_db;
Query OK, 0 rows affected (0.60 sec)
Eliminación de todas las tablas de una BBDD¶
mysqldump -uUSUARIO -pPASSWORD --add-drop-table --no-data BBDD | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -uUSUARIO -pPASSWORD BBDD
Trabajando con Usuarios / Privilegios¶
Creación de Usuarios¶
Primero creamos el usuario:
mysql> CREATE USER 'irontec_user_allpriv'@'localhost' IDENTIFIED BY 'irontec_pass';
Luego otorgamos privilegios:
-
Con todos los privilegios:
mysql> GRANT ALL PRIVILEGES ON irontec_db.* TO irontec_user_allpriv@localhost; Query OK, 0 rows affected (0.14 sec) -
Con privilegios de solo lectura en registros:
mysql> GRANT SELECT ON irontec_db.* TO irontec_user_ro@localhost; Query OK, 0 rows affected (0.02 sec) -
Con privilegios de solo inserción o modificación de registros:
mysql> GRANT INSERT,UPDATE on irontec_db.* TO irontec_user_wo@localhost; Query OK, 0 rows affected (0.01 sec)
Selección de Usuarios¶
Ver el usuario con el que se está trabajando:
mysql> SELECT user();
+------------------------+
| user() |
+------------------------+
| irontec_user@localhost |
+------------------------+
1 row in set (0.00 sec)
Visualización de Privilegios de Usuarios¶
mysql> show grants for 'irontec_user_allpriv'@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for irontec_user_allpriv@localhost |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'irontec_user_allpriv'@'localhost' IDENTIFIED BY PASSWORD '1a7f0760d48e' |
| GRANT ALL PRIVILEGES ON `irontec_db`.* TO 'irontec_user_allpriv'@'localhost' |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Eliminación de Usuarios¶
mysql> REVOKE ALL PRIVILEGES ON irontec_db.* from 'irontec_user_allpriv'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'irontec_user_allpriv'@'localhost';
+------------------------------------------------------------------------------------------------+
| Grants for irontec_user_allpriv@localhost |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'irontec_user_allpriv'@'localhost' IDENTIFIED BY PASSWORD '1a7f0760d48e' |
+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> revoke usage on *.* from 'irontec_user_allpriv'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'irontec_user_allpriv'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Trabajando con Tablas¶
Tipos de Tablas y Motores de Almacenamiento¶
Tipos de Campos¶
Creación Tablas¶
MyISAM¶
mysql> CREATE TABLE clientes (
-> id_cliente INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> razon_social VARCHAR (50) NOT NULL,
-> domicilio VARCHAR (100) NOT NULL,
-> nif VARCHAR (9) NOT NULL,
-> contacto VARCHAR (40)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE presupuestos (
-> id_presupuesto INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> empresa INT(8) NOT NULL,
-> asunto VARCHAR (60),
-> precio FLOAT DEFAULT 0.0,
-> fecha DATE,
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.20 sec)
InnoDB¶
mysql> CREATE TABLE clientes (
-> id_cliente INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> razon_social VARCHAR (50) NOT NULL,
-> domicilio VARCHAR (100) NOT NULL,
-> nif VARCHAR(9) NOT NULL,
-> contacto VARCHAR(40)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> CREATE TABLE presupuestos (
-> id_presupuesto INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> empresa INT(8) NOT NULL,
-> asunto VARCHAR (60),
-> precio FLOAT DEFAULT 0.0,
-> fecha DATE,
-> index (empresa),
-> FOREIGN KEY (empresa) REFERENCES clientes(id_cliente) ON DELETE CASCADE ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
Visualización Tablas¶
Tablas contenidas en una Base de Datos:
mysql> show tables;
+------------------+
| Tables_in_prueba |
+------------------+
| clientes |
| presupuestos |
+------------------+
2 rows in set (0.00 sec)
Estructura de una tabla:
mysql> DESC presupuestos;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id_presupuesto | int(8) | | PRI | NULL | auto_increment |
| empresa | varchar(50) | | | | |
| asunto | varchar(60) | YES | | NULL | |
| precio | float | YES | | 0 | |
| fecha | date | YES | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
Estado de todas las tablas:
mysql> show table status;
Modificación Tablas¶
Añadir un nuevo campo¶
mysql> ALTER TABLE [database.]tabla ADD campo_nuevo tipo AFTER campo_viejo;
mysql> ALTER TABLE presupuestos ADD adjunto BLOB AFTER fecha ;
Query OK, 0 rows affected (0.06 sec)
Registros: 0 Duplicados: 0 Peligros: 0
Cambiar el nombre de una tabla¶
mysql> ALTER TABLE [database.]tabla RENAME tabla_nueva;
mysql> ALTER TABLE presupuestos RENAME proyectos;
Query OK, 0 rows affected (0.03 sec)
Cambiar el tipo de dato de un campo¶
mysql> ALTER TABLE [database.]tabla CHANGE campo campo nuevo_tipo;
mysql> ALTER TABLE presupuestos CHANGE asunto asunto VARCHAR(100);
Query OK, 0 rows affected (0.23 sec)
Registros: 0 Duplicados: 0 Peligros: 0
Eliminar un campo existente¶
mysql> ALTER TABLE [database.]tabla DROP campo;
mysql> ALTER TABLE presupuestos DROP adjunto;
Query OK, 0 rows affected (0.05 sec)
Registros: 0 Duplicados: 0 Peligros: 0
Eliminación Tablas¶
mysql> DROP TABLE [database.]tabla;
mysql> DROP TABLE presupuestos;
Query OK, 0 rows affected (0.44 sec)
Trabajando con Registros¶
Nota: Si no se indica la Base de Datos a la que una tabla hace referencia, es necesario seleccionar previamente dicha BBDD (use database).
Consultas¶
mysql> SELECT campo1,campo2,.. FROM [database.]tabla;
mysql> SELECT * FROM proyectos;
Empty set (0.10 sec)
Inserción de datos¶
mysql> INSERT INTO [database.]tabla (campo1,campo2,..) VALUES ('valor_campo1','valor_campo2',..);
mysql> INSERT INTO presupuestos (empresa,asunto,precio,fecha) VALUES ('UPV','Formación GNU/Linux','600','2005-07-01');
Query OK, 1 row affected (0.00 sec)
Modificación de datos¶
mysql> UPDATE [database.]tabla SET campo1='valor_nuevo_campo1',campo2='valor_nuevo_campo2' WHERE condición;
mysql> UPDATE presupuestos SET empresa='UPV/EHU' WHERE empresa='UPV';
Eliminación de datos¶
mysql> DELETE FROM [database.]tabla WHERE condición;
mysql> DELETE FROM presupuestos WHERE id='1';
Query OK, 1 row affected (0.04 sec)
Trabajando con MySQL¶
Variables de Sistema¶
Ver variables de sistema:
mysql> SHOW VARIABLES;
+---------------------------------+----------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------------------------+
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
..............................................................................................
| version | 4.1.11-Debian_4-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (April 1, 2005) |
| version_comment | Source distribution |
| version_compile_machine | i386 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+----------------------------------------------------------+
196 rows in set (0.01 sec)
Configuración¶
/etc/mysql/my.cnf
Tablas InnoDB por defecto¶
1 2 | |
Localización¶
1 2 | |
UTF8 por defecto¶
Como cabrea tener bases de datos en latin1 y cosas así que siempre dan problemas al cambiarlas de servidor o de versión de BDD, o con los programas. Cuando aprendes eso empiezas a configurar todo en UTF8, el apache, el mysql, las locales del sistema, etc...
Y cada vez que creas una BDD en mysql este se empeña en ponerte "latin1" o "unicode_swedish_ci" ¿Swedish? ¿Que es esto? ¿Un rollo chovinista?
Después para cada tabla, incomprensiblemente, también tienes que especificar que es "utf8_unicode_ci".
Para evitar esto lo que podemos hacer es configurar mysql añadiendo estas líneas en la sección [mysqld] de /etc/mysql/my.cnf:
1 2 3 4 | |
Si después pedimos un status:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | |
Ahora si, ya podemos dormir tranquilos.
Backups¶
mysqldump¶
Programa incluído en el paquete mysql-client
Backup de todas las bases de datos:
$ mysqldump --opt -d -h host --all-databases --user=root --password=contraseña > backup_irontec_mysql.sql
Para cargar un backup hecho previamente:
$ mysql -h host --user=root --password=contraseña < backup_irontec_mysql.sql
Backup de una base de datos (database):
$ mysqldump --opt -d -h host --user=root --password=contraseña database > backup_irontec_mysql.sql
Backup de una lista de tablas (t1, t2) de una base de datos (database):
$ mysqldump --opt -d -h host --user=root --password=contraseña database t1 t2 > backup_irontec_mysql.sql
Para cargar un backup hecho previamente sobre una base de datos concreta:
$ mysql -h host --user=root --password=contraseña database < backup_irontec_mysql.sql
Si se quita la opción -d en el volcado se incluirán los datos que contengan las tablas. Para tablas muy grandes, para evitar los bloqueos que se producen durante su respaldo, añadir la opción --single-transaction.
BCPs¶
El comando equivalente al bcp de otros gestores es mysqlimport. Aquí está la página de ayuda del comando.
Un ejemplo de uso sería:
$ mysqlimport -h localhost -u adw -p -v -L --delete adw keywords.csv
Para que no de errores hay que guardar el fichero en formato UTF8 (revisar con el comando od -c fichero.csv si no hay caracteres extraños al principio). Por defecto se espera el carácter TAB como separador de columnas y el retorno de carro como separador de registros. El fichero será un CSV con las columnas en el mismo orden que la tabla en que se desea cargar los datos. El nombre del fichero sin extensión se debe corresponder con el nombre de la tabla. En el ejemplo anterior, la tabla sería keywords.
Para indicar un separador de campos y registros diferentes se pueden usar las opciones siguientes (ver man):
1 | |
El mismo efecto se consigue con la siguiente query:
LOAD DATA LOCAL INFILE 'keywords_clean.csv'
INTO TABLE keywords
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"';
Para que el cliente acepte la opción LOCAL debe ser ejecutado con la opción --local-infile, por ejemplo:
$ mysql -h localhost --local-infile -u adw -p adw
Otras Herramientas¶
mysqladmin¶
Para cambiar la contraseña del usuario root:
$ mysqladmin -u root password `<nueva_password>`
Muestra las variables del gestor:
$ mysqladmin -u root -p variables
Solución de problemas¶
Tabla de Schrödinger¶
La única forma de solucionar un caso de tablas de Schrödingers fue purgando la instalación de MySQL y borrando el directorio de datos:
$ sudo dpkg --purge mysql-server-5.5 mysql-server-core-5.5 mysql-server
$ sudo mv /var/lib/mysql /var/lib/mysql.bak
$ sudo apt-get install mysql-server-5.5 mysql-server-core-5.5 mysql-server
Otra forma menos agresiva fue encontrada aquí y consiste en borrar los ficheros siguientes con el servidor parado y luego arrancarlo:
$ sudo service mysql stop
$ sudo rm /var/lib/mysql/ibdata1
$ sudo rm /var/lib/mysql/ib_logfile0
$ sudo rm /var/lib/mysql/ib_logfile1
$ sudo service mysql start
La última vez que sucedió borré todas las tablas. Ver si borrando estos ficheros en cuanto ocurre se restituyen todas las tablas.
Información en error¶
Para tener más información de un error, si es sobre tablas InnoDB, se puede lanzar el siguiente comando:
SHOW ENGINE INNODB STATUS;
Concretamente la sección LATEST FOREIGN KEY ERROR muestra más detalle en los errores relacionados con foreign keys.