The former is a little faster, but the latter is more flexible. With mysqldump you can choose to copy just the structure of the database, just the data, or both. 

The most basic usage of mysqldump is:

mysqldump -u username -p databasename > dumpfilename.sql

This command will dump a text file that can be read into another database server, like this:

mysql -u root -p databasename < dumpfilename.sql

Instead of directing the output of mysqldump to a file, you can also pipe it directly to another vserver, like so:

mysqldump -u username -p databasename |
mysql -h remote-host -u remoteuser -p -C databasename

However, this can be less secure in some cases, since you have to tell the remote host to accept database-modifying connections from external clients. 

This basic command is fine as far as it goes—meaning it will result in a nice SQL file containing both the structure and data of the named database. But sometimes you will want something more specific than that: maybe just the structure, or just the data, or all the databases on that server, or just some tables from your chosen database.

MySQL allows you to both specify different combinations of databases and/or tables and to add option flags to your command.
If you want to select specific tables to dump from your chosen database, just list them after the database name:

mysqldump -u username -p databasename table1 table2
> dumpfilename.sql

If you want to dump some but not all databases on your server, use the --databases flag and then list the databases. However, in this case, you will not be able to specify tables.

mysqldump -u username -p --databases database1 database2 >
dumpfilename.sql

If you want to dump all databases, use the --all-databases flag:

mysqldump -u username -p --all-databases > dumpfilename.sql

You can specify any of these options before specifying the databases and tables. There are many mysqldump  options, but you can see  thre lists  withthe most commonly used options. 

 

Option                                                              Explanation

--add-locks                               Adds table locking to SQL file for faster inserts on the target
                                             table. See also --opt.

--add-drop-table                        Will overwrite each table definition. Be careful with this option,
                                               as you could delete data! If you don’t use this option but a
                                              table of the same name already exists, you will get an error on
                                              the target database.

-a, --all                                     All options. Be careful!

-c, --complete-insert                   Use more complete insert statements with column names,
                                                instead of simply reading in values.

--help                                        Displays help message with options.

-l, --lock-tables                            Locks tables on the source machine before the dump.

-n, --no-create-db                        Will not create databases of the specified names if they
                                                don’t exist already. Default with the --databases and
                                               --all-databases options.

-t, --no-create-info                      Will not create tables of the specified names if they don’t exist
                                                already.

-d, --no-data                              Just the structure of the specified database(s) or tables.

--opt                                         Equal to --quick --add-drop-table --add-locks
                                               --extended-insert --lock-tables . Fastest possible
                                                 dump. Make sure you want to drop existing tables if there’s
                                                 a conflict.

-q, --quick                                  No buffering.

-r, --result-file=filename                Dump result to file. In DOS, creates Unix-style line breaks.

-w, --where=’condition’               Select results by the WHERE clause in single quotes.

 

Because mysqldump  is so easy to use, you should have no excuse for not adhering to a regular backup schedule. This is why cronjobs were invented! If your data changes relatively infrequently, you might be able to get away with weekly or fortnightly backups; if you have a fairly high-traffic site, you’ll want to schedule one every night.