Convertir base de datos latin1 a UTF-8 en MySQL

Desde la version de  MySQl 4.1 que el charset por default es UTF-8. Si tienen una base de datos antigua con datos en latin1 y desean actualizar, deben de hacer lo siguiente:

  1. Para no perder los datos exportamos la informacion de la base de datos en latin1 a un archivo plano con el comando mysqldump:
    :~$ mysqldump -u usuario -p base_de_datos –default-character-set=latin1 > backuplatin1.sql
  2. Entrando a la consola de MySQl (o mariadb) o administracion via web con PhpMyAdmin debemos de crear una nueva base de datos con el set de caracteres UTF8:
    :~$ mysql -u mi_usuario -p
    mysql> CREATE DATABASE nueva_db CHARACTER SET utf8 COLLATE utf8_general_ci;
  3. Ahora procedemos a convertir el set de caracteres del archivo backuplatin1.sql. Para luego con el archivo resultante cambiamos todas las referencias al juego de caracteres latin1:
    :~$ iconv -f ISO-8859-1 -t UTF-8 backup.sql > backup_utf8.sql
    :~$ perl -pi -w -e 's/CHARSET=latin1/CHARSET=utf8/g;' backup_utf8.sql
  4. Ahora con el backup o dump convertido al charset utf8 lo importamos a la nueva base de datos recien creada
    :~$ mysql -u mi_usuario -p  db_destino  –default-character-set=utf8 < backup_utf8.sql

Script para optimizar las tablas fragmentadas en MySQL

Bueno otro buen script a la huchaca… esta escrito en bash aparte de ser utilisisimo porque chequea las tablas fragmentadas y tambien chequea todas las bbdds en busca de tablas MyISAM o INNODB y las optimiza.

#!/bin/bash

VERSION="0.7.2"
log="$PWD/mysql_error_log.txt"

echo "MySQL fragmentation finder (and fixer) v$VERSION, written by Phil Dufault ( http://www.dufault.info/ )"

showHelp() {
echo -e "\tThis script only repairs MyISAM and InnoDB tables"
echo -e "\t--help or -h\t\tthis menu"
echo -e "\t--user username\tspecify mysql username to use\n\t\t\tusing this flag means the script will ask for a password during runtime, unless you supply..."
echo -e "\t--password \"yourpassword\""
echo -e "\t--host hostname\tspecify mysql hostname to use, be it local (default) or remote"
}

#s parse arguments
while [[ $1 == -* ]]; do
case "$1" in
--help|-h) showHelp; exit 0;;
--user) mysqlUser="$2"; shift 2;;
--password) mysqlPass="$2"; shift 2;;
--host) mysqlHost="$2"; shift 2;;
--) shift; break;;
esac
done

# prevent overwriting the commandline args with the ones in .my.cnf, and check that .my.cnf exists
if [[ ! $mysqlUser && -f "$HOME/.my.cnf" ]]; then
if grep "user=" "$HOME/.my.cnf" >/dev/null 2>&1; then
if grep "pass=" "$HOME/.my.cnf" >/dev/null 2>&1; then
mysqlUser=$(grep user= < "$HOME/.my.cnf" | awk -F\" '{print $2}'); mysqlPass=$(grep pass= < "$HOME/.my.cnf" | awk -F\" '{print $2}'); if grep "host=" "$HOME/.my.cnf" >/dev/null 2>&1; then
mysqlHost=$(grep host= < "$HOME/.my.cnf" | awk -F\" '{print $2}'); fi else echo "Found no pass line in your .my.cnf,, fix this or specify with --password" fi else echo "Found no user line in your .my.cnf, fix this or specify with --user" exit 1; fi fi # set localhost if no host is set anywhere else if [[ ! $mysqlHost ]]; then mysqlHost="127.0.0.1" fi # error out if [[ ! $mysqlUser ]]; then echo "Authentication information not found as arguments, nor in $HOME/.my.cnf" echo showHelp exit 1 fi if [[ ! $mysqlPass ]]; then echo -n "Enter your MySQL password: " read -s mysqlPass fi # Test connecting to the database: mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" --skip-column-names --batch -e "show status" >/dev/null 2>&1
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information.";
exit 1;
fi

# Retrieve the listing of databases:
databases=( $(mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" --skip-column-names --batch -e "show databases;" 2>"$log") );
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information."
exit 1;
fi

echo -e "Found ${#databases[@]} databases";
for i in ${databases[@]}; do
# get a list of all of the tables, grep for MyISAM or InnoDB, and then sort out the fragmented tables with awk
fragmented=( $(mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" --skip-column-names --batch -e "SHOW TABLE STATUS FROM $i;" 2>"$log" | awk '{print $1,$2,$10}' | egrep "MyISAM|InnoDB" | awk '$3 > 0' | awk '{print $1}') );
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information."
exit 1;
fi
tput sc
echo -n "Checking $i ... ";
if [[ ${#fragmented[@]} -gt 0 ]]; then
if [[ ${#fragmented[@]} -gt 0 ]]; then
if [[ ${#fragmented[@]} -gt 1 ]]; then
echo "found ${#fragmented[@]} fragmented tables."
else
echo "found ${#fragmented[@]} fragmented table."
fi
fi
for table in ${fragmented[@]}; do
let fraggedTables=$fraggedTables+1;
echo -ne "\tOptimizing $table ... ";
mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" -D "$i" --skip-column-names --batch -e "optimize table $table" 2>"$log" >/dev/null
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information."
exit 1;
fi
echo done
done
else
tput rc
tput el
fi
unset fragmented
done

# footer message
if [[ ! $fraggedTables -gt 0 ]]; then
echo "No tables were fragmented, so no optimizing was done.";
else
if [[ $fraggedTables -gt 1 ]]; then
echo "$fraggedTables tables were fragmented, and were optimized.";
else
echo "$fraggedTables table was fragmented, and was optimized.";
fi
fi

if [[ ! -s $log ]]; then
rm -f "$log"
fi

unset fraggedTables

Link | http://www.dufault.info/blog/a-script-to-optimize-fragmented-tables-in-mysql/

Cambiar la password de root de MySQL

Muchas veces cuando trabajamos con MySQL perdemos la contraseña del root lo cual es un gran problemas si solo tenemos una cuenta con todos los privilegios para acceder a MySQL, como nuestro manejo de mysql es poco, terminamos por eliminar el motor de bases de datos y empezar el trabajo de nuevo esto es una gran perdida de tiempo y hacer de esfuerzo por eso le mostrare una forma para poder cambiar la password del root en MySQL lo primero que debemos hacer es detener el servicio Mysql

#> /etc/rc.d/init.d/mysqld stop

Una vez el servicio este abajo escribimos las siguientes líneas:

#> mysqld_safe –skip-grant-tables –skip-networking

Cuando abrimos el mysqld_safe es como si trabajásemos en el modo a prueba de fallos de Windows

–skip-grant-tables esta opción causa que el servidor no use el sistema de privilegios lo que le da acceso ilimitado a todas las bases de datos a todos los usuarios de la base de datos.

–skip-networking deja de escuchar (LISTEN) conexiones TCP/IP provenientes de la red, es decir MySQL trabajaría en un ambiente totalmente local

Ahora nos logeamos como root pero no damos un password ya que en el modo abierto nos permite obviar la contraseña del usuario root hasta que terminemos de asignar una nueva password :

#>mysql -u root

Una vez dentro de mysql usamos la base de datos mysql:

mysql> use mysql;

Cambiamos la contraseña de ‘root’ agregando la siguiente línea:

mysql> UPDATE user SET password=PASSWORD(‘nuevo_pass’) WHERE user=’root’;

Si lo hacemos bien veremos algo como esto:

Query OK, 1 rows affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Reiniciamos el servicio y listo

#> service mysqld restart

La siguiente vez que entre al monitor (mysql -u root -p) usa la nueva clave o contraseña.

Reparar todas las tablas de todas las bases de datos de MySQL en Plesk

Para reparar todas las tablas de todas las bases de datos de MySQL en Plesk (Linux) tenemos que poner en una sola linea este pedazo churro:

for database in $(mysql --skip-column-names -uadmin -p`cat /etc/psa/.psa.shadow` -e
"show databases" ); do echo "optmizing tables from $database";
for table in $(mysql --skip-column-names -uadmin -p`cat /etc/psa/.psa.shadow` -e
"show tables" $database ); do echo "-> $table " ;
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "OPTIMIZE TABLE $table" $database ;
done ; done ;

Guardar el resultado de MySQL en un archivo

Simpleza de MySQL y a la misma vez útil cuando se trata de guardar resultados (de SELECT por ejemplo) bastantes grandes en MySQL.
Con tee lo que hacemos es loguear todo lo que se produce en la consola de MySQL, tanto los comandos como los resultados de estos :D, con notee paramos de escribir en el archivo que le hemos indicado antes con tee

Esto me lo apunto aqui en el blog, para ayudar a la memoria 😀

mysql>tee archivo.txt
mysql>select * from tabla;
mysql>notee

MySQL Tips

Aqui os dejo una pequeña Cheat Sheet con muchos MySQL Tips (Spaninglish owned :D)

MySQL : 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;

MySQL: Borrar una base de datos

Borrar bases de datos:

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

o bien:

$ mysqladmin drop joomla

MySQL: 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.

MySQL: 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

MySQL: 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

MySQL: Importar la estructura de base de datos

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

MySQL: Vaciar una tabla

$ mysql
> use joomla;
> truncate table jos_users;

MySQL: 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.

 ~/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:

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

MySQL: 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'

MySQL: 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

MySQL: 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

MySQL: 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.

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;

Si sabéis o quereis saber mas pequeños tips de mysql y quereis que los añada aqui hacer un comentario y lo añado 😀