Tips de MySQL #

Tips #

Crear una base de datos #

Crear una base de datos:

$ mysql
mysql > show databases;
mysql > create database joomla;
mysql > use database joomla;

o bien:

$ mysqladmin create joomla

Si es necesario un encoding en particular se necesitan definir el character set y el collate:

• En ISO: mysql > create database joomla character set latin1 collate latin1_spanish_ci;
• En UTF8: mysql > create database joomla character set utf8 collate utf8_general_ci;

Borrar una base de datos #

Borrar bases de datos:

$ mysql
mysql > show databases;
mysql > drop database joomla;

o bien:

$ mysqladmin drop joomla

Hacer un dump de la base de datos #

$ mysqldump -h localhost -u root -p joomla > joomla.sql

donde joomla es la base de datos, root es el usuario de la base datos y localhost el servidor de la base de datos.

Importar una base de datos #

$ mysql
mysql> create database joomla;
mysql> use joomla
mysql> source joomla.sql

o bien:

$ mysql -u root -p --database=joomla < joomla.sql

Importar tablas de una base de datos #

$ mysqldump --user=root joomla jos_core_acl_aro jos_core_acl_groups_aro_map jos_users > jos_users.sql

Importar la estructura de base de datos #

$ mysqldump --no-data --user=root joomla > joomla.sql

Vaciar una tabla #

$ mysql
> use joomla;
> truncate table jos_users;

Cómo generar los scripts individuales para las tablas de una base de datos #

El script generate-mysql-tables-scripts.sh

Nota: el usuario es root y no tiene contraseña.

zylk@hipnos ~/tmp $ cat generate-mysql-tables-scripts.sh 
DATABASE=$1
for i in `echo "use $DATABASE; show tables;" | mysql -u root|grep -v "Tables_in"`
        do
                echo "Dumping $i"
                mysqldump -u root $DATABASE $i > $i.sql
        done

De este modo:

zylk@hipnos ~/tmp $ generate-mysql-tables-scripts.sh zyf
Dumping ...
Dumping 
.
.
.

Importar una base de datos desde un fichero CSV #

$ mysql
> load data local infile 'users.csv' into table `jos_users` 
> fields terminated by ',' 
> optionally enclosed by '"'
> lines terminated by '\n\r';

Especificamos que el contenido se encuentra en el servidor, en un fichero llamado users.csv

> local infile 'users.csv'

que los campos se separan mediante comas:

> fields terminated by ','

que el contenido puede estar entrecomillado, y de ser así el contenido se pasará a la BD sin comillas (o en su defecto sin el símbolo especificado en el comando). Si quisiéramos pasar el contenido a la BD con comillas y todo, no especificaríamos esta opción.

> optionally enclosed by '"'

especificando cual es el carácter que señaliza el final de una línea, de no tener nada que marque el fin de una línea omitiríamos esta opción.

> lines terminated by '\n\r'

Exportar a CSV una select #

Desde la consola de mysql:

> select * from joomla.jos_users into outfile '/tmp/filename.csv' 
fields terminated by ',' lines terminated by '\n';

o desde la terminal:

$ mysql -u root joomla -B -e \
"select \* from users " | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Cambiar la contraseña de root en mysql #

A todos nos ha pasado alguna vez ir a modificar algo en nuestro servidor MySQL y encontrarnos con:

ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Aquí está la receta:

$ killall mysqld
$ /usr/sbin/mysqld --skip-grant-tables &
$ mysql -u root mysql
mysql> UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> EXIT
$ killall mysqld
$ /usr/sbin/mysqld &

Si se conoce la contraseña anterior sólo sería necesario:

mysql> set password for 'root'@'localhost' = password('');

o en su defecto:

$ mysql -u root mysql
mysql> UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> EXIT

Listado de privilegios de MySQL #

En la siguiente http://dev.mysql.com/doc/refman/5.0/es/privileges-provided.html página de documentación de MySQL se listan y explican en castellano todos los privilegios de MySQL.

Dar privilegios a usuario en una base de datos #

Para dar privilegios a un usuario sobre una base de datos ejecutaremos el siguiente comando, en el ejemplo le damos todos los permisos que una base de datos tiene, la sintaxis queda debajo:

$ mysql -u root -p
mysql> grant all on joomla.* to joomla identified by 'joomla';

Donde el primer joomla.* es la base de datos, el segundo el nombre de usuario y por ultimo la contraseña:

mysql> grant all on nombre_de_la_basededatos.* to nombre_usuario identified by ‘contraseña’;

Por último, actualizamos los privilegios:

mysql> flush privileges;

Ya podremos entrar en la base de datos usando el usuario y la contraseña asignada;

$ mysql -u joomla -p

Mostrar privilegios de un usuario de MySQL #

mysql> show grants for usuario;

Quitar privilegios a un usuario de MySQL #

mysql> revoke privilegio on basededatos.table from usuario;

Mostrar host y usuarios de MySQL #

mysql> use mysql;
mysql> select host,user from user;

Eliminar un usuario de MySQL #

mysql> drop user usuario;

Permitir acceso remoto a MySQL #

Editar el fichero /etc/mysql/my.cnf y comentar la línea: bind-address = 127.0.0.1

vi /etc/mysql/my.cnf
# bind-address          = 127.0.0.1

Después tendríamos que crear usuarios utilizando la siguiente sentencia:

> grant all on basededatos.tabla to usuario@'dirección_ip' identified by 'contraseña';

Ejemplos #

Sentencia para establecer permisos en la tabla table de la base da datos database para el usuario user desde el host de la dirección IP 192.168.10.10 con la contraseña password:

> grant all on database.table to user@'192.168.10.10' identified by 'password';

Sentencia para establecer permisos en todas las tablas de la base de datos database para el usuario user desde un host que tiene cualquier dirección IP y sin contraseña:

> grant all on database.* to user@'%';

Modificar codificación del servidor MySQL #

Podemos ver la codificación de MySQL mediante el comando:

> show variables;

En este ejemplo vamos a cambiar la codificación a UTF-8. Para ello haremos lo siguiente:

$ vi /etc/mysql/my.cnf

Buscaremos la sección mysqld y después añadiremos las siguientes líneas.

And add these lines under it:

# utf8
init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci

Una vez hecho esto sólo nos queda reiniciar el servicio.

/etc/init.d/mysql stop
/etc/init.d/mysql start

Comprobar codificación de una tabla #

mysql> SHOW CREATE DATABASE `prueba`;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| prueba   | CREATE DATABASE `prueba` /*!40100 DEFAULT CHARACTER SET latin1 */ | 
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

Modificar codificación de una tabla #

> ALTER TABLE tabla CONVERT TO CHARSET charset COLLATE collation;

Por ejemplo:

> ALTER TABLE tabla CONVERT TO CHARSET latin1 COLLATE latin1_spanish_ci;

Variables de mysql #

$ mysqladmin variables 
$ mysqladmin variables | grep datadir
| datadir                         | /var/lib/mysql/             |

Errores comunes #

ERROR 1005 (HY000) at line 1: Can't create table './joomla/jos_users.frm' (errno: 150) #

'Explicación:'

Es un error que puede surgir al importar una base de datos.

Debe de haber algún error en la comprobación de Foreign Keys.

'Solución:'

Añadir la siguiente línea al principio del fichero .sql:

> SET FOREIGN_KEY_CHECKS = 0;

Y después la siguiente línea al final del fichero .sql:

> SET FOREIGN_KEY_CHECKS = 1;