Dicas MySQL

Postado em 05/11/2014 por Rogerio - Categoria: Dicas Linux - Tags: mysql

gerar dump todas as bases
mysqldump -u root -p --all-databases > nome_dump.sql

restaurar dump criado anteriormente
mysql -u root -p < nome_dump.sql

/etc/init.d/mysql restart
Stopping MySQL database server: mysqld failed!
Starting MySQL database server: mysqld already running.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

/etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = passdedebiansysmaint
socket   = /var/run/mysqld/mysqld.sock

[mysql_upgrade]
user     = debian-sys-maint
password =  passdedebiansysmaint
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

atualizar o password do usuario debian-sys-maint no servidor novo
update user set password=PASSWORD("passdedebiansysmaint") where User='debian-sys-maint';

eliminar todas as tabelas usando um único comando
SELECT concat('DROP TABLE IF EXISTS ', TABLE_NAME, ';') FROM information_schema.TABLES;