:::: MENU ::::

How to use mysqldump command to backup mysql 5 database?

What’s mysqldump command?

mysqldump is mysql utility for taking logical backup and restore of mysql tables and database. Logical backup means it consist of SQL statements like create table and insert statements.  Output format could be sql, CSV, tab delimited or XML.

Where to find this utility?

It’s shipped with your mysql server setup. So, if you are on windows, just add mysql’s bin directory path to your windows environment variables.


How to run this utility?

Just go to command prompt and type in:

All the available options will be printed on screen. Now to run this command for mysql server, mysql login credentials needed and moreover that login must be privileged to select query.

Basic command syntax

For and example:  default root login

Now if you open test.sql you could see output like this

So, this output – test.sql contains drop/create table and insert statements. which you could use to restore by running this command again using mysqldump. Let’s explore abilities of this utility.

Mysql data exporting using mysqldump.

  • How to take backup of multiple databases?
    Tip: Supply database names separated by spaces. 
  • How to take backup of all databases?
    Tip: Supply –all-databases as an argument. 
  • How to take backup of specific table?
    Tip: Supply table name as an argument after database name 

    So in above case gift table from test database will be exported to gift.sql
  • How to take backup in csv/tsv format?
    Tip: Supply –fields-terminated-by=’,’ as an argument along with csv filename path with -T option 
  • How to take backup in XML format?
    Tip: supply –-xml as an argument. 

Mysql data restoring using mysqldump

  • Restore single database.
  • Restore all databases
  • Restore a specific table
  • There is one more utility called mysqlimport, explore it here
  • Restore TSV (tab separated backup) file
  • Restore xml formatted dump.Try this utility – https://code.google.com/p/mysqldump-x-restore/

Mysql data backup and restoring using data files.

Just navigate to your mysql directory

  • Copy folder – data and archive (zip) it.

    Install mysql on new machine and overwrite it in its mysql directory.  Then use this backup db server’s credentials to get access and alter privileges on new machine.

So, what do you think ?