Backup of a database is necessary to recover data in case of problems such as system crashes, hardware failures, and user deleting data accidentally, etc... Backup is also essential before upgrading MYSQL, transfer a MYSQL installation or another system or setting up replication slave servers.

Backups can be done in many types such as logical or physical, full or incremental ... and so on. Backups can be done with programs such as mysqldump program or mysqlhotcopy script. Mysqldump is more generally used as it can back up all kinds of tables, while Mysqlhotcopy works only with some storage engines.

The mysqldump

The mysqldump is a client that dumps a MYSQL database into file such as SQL, text, XML, or similar. A dump file can be used as backup to enable data recovery in case of data loss, a source of data for setting up replication slaves or to test potential upgrade incompatibilities. The mysqldump dumps the MySQL databases locally on server. It is fast and does not lock the MySQL for long time during backup. It is located in root/bin folder of MySQL installation folder.

Syntax

The syntax for backup and restore of database:

backup:  mysqldump -u [username] -p[password] [database_name] > dumpfilename.sql

restore: mysql -u [username] -p[password] [database_name] < dumpfilename.sql

Where,

  • user is username
  • password is password of the user account
  • database_name is name of database
  • dumpfilename is the path of the file to which database has to be stored and restored

Examples:

mysqldump -u user1  -p Nisha123  tblstudents > c:\temp\backupFile1.sql

To store multiple databases we use commands below:

mysqldump -u [username] -p[password]  [database_name1, database_name2, ...] > [dumpfilename.sql]

Or:

mysqldump -u [username] -p[password] all-database > [dumpfilename.sql]

To back up the database structure only we use syntax as given below:

mysqldump -u [username] -p[password] -no-data [database_name] > [dumpfilename.sql]

To back up the data only, i.e. whenever we want to refresh data in the dump we use -no-create-info option in syntax as given here:

mysqldump -u [username] -p[password] -no-create-info [database_name] > [dumpfilename.sql]