backups

ERROR 2013 (HY000): Lost connection to MySQL server

Have you ever had a big (and when i say big I mean BIG) database which you need to import in a sql file?

If so, you'll probably have found this issue (or even you are having the problem just now and you are reading this looking for a solution):

ERROR 2013 (HY000): Lost connection to MySQL server

This uses to happen when trying to import the dump with the mysql -u command:

mysql -u root -p database < databasetoimport.sql

You can try to play with the my.ini settings, but a best secure solution is just to do the import from the mysql own console. How? easy.

1. go to the place where your sql is. Then log in mysql:

mysql -uroot -p

2. create the database if needed:

create database mynewdb

3. Change to it:

use mynewdb;

4. and finally, import it:

source databasetoimport.sql

Wait and hopefully the problem will not appear again.

The reason is simple. Mysql is designed to have this behabiour of closing the connection dealing with external scripts, like php or the own mysql command. If you are executing the import from inside the console, the problem will not appear.

backups with screen and ncftp

Do you have a BIG database (or databases) in your server as a result of months and years of working? It use to be a hell to download the sql right? One solution is to get a backup ftp in your hosting provider. They are cheap, and the are going to save you a lot of time.

The trick, do the mysql backup maintenance things. Then, it's time to download your ENORMOUS .sql, but not directly to your computer.

First install screen, a usefull app to launch any console script or commad, and be able to let running while even you close your local computer.

Secondly, install ncftp, a wonderful ftp client (i love it).

And, lastly, some magic. Execute:

 

  1. screen
  2. put the file on the  ftp: ncftpput -v -R -u USER -p PASS FTPHOST [DIRECTORYWHERECOPY] [FILE(S)TOCOPY]

 

If file is too big, you can close your terminal with [CONTROL]A+D keys. But, we'll speak in another post about screen.

categorias: 

backup y restore (volcar) una base de datos mysql

Cambiar de servidor una base de datos? Ningún problema

el primer paso, sencillo, hacer el backup:

mysqldump -a -u admin -p DATABASE > FILENAME.mysql

--default-character-set=latin1 table >

(esta última linea es opcional)

1.Drop the database

mysqladmin -u admin -p drop DATABASE

(idem, si queremos eliminarla estupendo, pero por seguridad yo la dejaría en su sitio)

2.Recreate the database

mysqladmin -u admin -p create DATABASE

-esto lo hacemos ya en el nuevo servidor

3.Import the backup data

mysql -u USER -p PASSWORD DATABASE < FILENAME.mysql

mysql -u admin -p < .mysql

 

Otras notas:

Hacer backup de varias bases de datos:

mysqldump -u username -ppassword –databases db_name1 [db_name2 ...] > dump.sql

 

TODAS LAS BASES DE DATOS:

mysqldump -u username -ppassword –all-databases > dump.sql

 

categorias: