MySQL backup and restore (mysqldump and mysql to restore)

mysqldump is an effective mysql’s native way to backup a database. With just a single command you can easily do a backup of your database. Of course, mysql provide as well a way to restore dumps.
Beyond its native way, it’s better to use this way instead of using e.g. phpmyadmin. Why? Well, first phpmyadmin don’t allow to restore backups from files highers than aproximately 2mb, and has also a limit for the dumps.
 

Backup a database.

So to backup a database, just do in a terminal/console:

mysqldump -u[user] -p[password] [dbname] > nameofdumpfile.sql

This option has more options (like backup all the databases, just some tables/databases, etc). To have a list of them, just query it by using –help flag.
 

Restore a database.

Restore a database is quite easy as well:

mysql -u[user] -p[password] [dbname] < nameofdumpfile.sql

Easy isn’t it?
If your dump file contains a CREATE DATABASE statement, you can avoid the [dbname] param.
 

Common problem on restoring a database

If you are trying to restore a database and you get the following error:

ERROR 2006 (HY000) at line XX: MySQL server has gone away

Most likely you are trying to load a bigger file than mysql configuration can handle. To solve that, just edit your mysql configuration file and restart the mysql service.

  1. Edit the file my.cnf (depending on the version or the OS, folder containing it may change)
    sudo nano /etc/mysql/my.cnf
  2. Look for the max_allowed_packet and increase it to fit the size of the file you want to load.
    [mysqld]
    max_allowed_packet=256M
  3. And finally restart the mysql service
    /etc/init.d/mysqld restart

 
Easy as it is, this can avoid you a lot of headaches.

Read More