It's important for system administrators/web developers, to back up their databases regularly, or on the fly, so you can recover them if/when a disaster happens.

How to export a database using the Command Line Interpreter (cli)

The export command is the same for all operating systems.

mysqldump -u user -p name > fileName.sql

• mysqldump is the command.
• -u tells mysqldump to expect a database user.
• user is the authorized database user name.
• -p tells mysqldump that a password is expected but notice the password is entered into the command for security reasons, however the password will be required when the command is run.
• name is the name of the database you want to dump.
• > output redirection.
• fileName.sql is the name of the dump file.

To learn more about dumping the database read the MySql manual.

If you're dumping a database using a command line interpreter (cli) you'll need to tell the operating system where the mysqldump.exe executable file is located.

You can do this in several ways.

1. Set the PATH or Environment variable permanently
   Use this method if you're planing on performing manual dumps on a regular basis so you don't have to enter the path every time you need to dump the db.
2. Set the PATH or Environment variable for the current session only.
   Use this method when you'll be performing several manual dumps while the current session is open.
3. Set the path to the executable file in the command line each time you need to dump the database.
   Use this method when you only need to perform periodic dumps.

On a local Windows machine running an Amp Stack

1. Tell Windows where the executable file is permanently, by setting the $Path variable (learn how).
   Once you've set the $PATH variable:
   1. Open the Command prompt.
   2. Type: mysqldump -u databaseUser -p databaseName > output path\fileName.sql
   3. You'll be prompted for the database password.
   4. Enter the password and hit the enter key.
   5. Type: DIR "output path" [without quotes] (the path to the directory where you dumped the database) to list the contents of the directory to see if the sql file has been generated.
   6. Type: exit
2. Tell Windows where the executable file is for the current session only.
   1. Open the Command prompt.
   2. Type: set path = directory where your mysqldump.ece file is located  (typically c:\wamp\bin\mysql\mysqlx.x.xx\bin, yours may differ)
   3. Type: mysqldump -u databaseUser -p databaseName > output path\fileName.sql
   4. You'll be prompted for the database password.
   5. Enter the password and hit the enter key.
   6. Type: DIR "output path" [without quotes] (the path to the directory where you dumped the database) to list the contents of the directory to see if the sql file has been generated.
   7. Type: exit
3. Tell Windows where the executable file is each time you need to dump the database.
   1. Open the Command prompt.
   2. Type: path to the executable file including the executable file
      example: c:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe -u databaseUser -p databaseName > output path\fileName.sql
   3. You'll be prompted for the database password.
   4. Enter the password and hit the enter key.
   5. Type: DIR "output path" [without quotes] (the path to the directory where you dumped the database) to list the contents of the directory to see if the sql file has been generated.
   6. Type: exit

Note:

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:
   mysqldump [options] > dump.sql
However, UTF-16 is not permitted as a connection character set, so the dump file will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:
   mysqldump [options] --result-file=dump.sql

referenced from the MySql manual

Other Notes:

If you need to compress the database file to reduce it's file size you can use windows default compression program or you can install a third party compression program (winzip, 7 zip, winrar)  and compress the sql file after the dump is exported. Configuring mysql and windows to compress it upon export is beyond the scope of this article.

On a local Linux machine running an Amp Stack

1. Tell Linux where the executable file is permanently by setting the $Path variable (learn how).
   Once you've set the $PATH variable:
   1. Open a shell
   2. Type: mysqldump -u databaseUser -p databaseName >  output path/fileName.sql
   3. You'll be prompted for the database password.
   4. Enter the password and hit the enter key.
   5. Type: ls output path (directory where the db dump is located) to list the contents of the directory to see if the sql file has been generated.
2. Tell Linux where the executable file is for the current session only. (learn how)
   1. Open a shell
   2. Type: export PATH=$PATH:/path/to/mysql/mysqlx.x.xx/bin/ (path to the mysqldump.exe executable file)
   3. Type: mysqldump -u databaseUser -p databaseName >  output path/fileName.sql
   4. You'll be prompted for the database password.
   5. Enter the password and hit the enter key.
   6. Type: ls output path (directory where the db dump is located) to list the contents of the directory to see if the sql file has been generated.
3. Tell Linux where the executable file is each time you need to dump the database.
   1. Open a shell.
   2. Type: path to the executable file including the executable file
      example: /usr/local/mysql/mysql5.6.17/bin/mysqldump.exe -u databaseUser -p databaseName > output path\fileName.sql
   3. You'll be prompted for the database password.
   4. Enter the password and hit the enter key.
   5. Type: ls "output path" [without quotes] (the path to the directory where you dumped the database) to list the contents of the directory to see if the sql file has been generated.
   6. Type: exit

On a local Mac machine running an Amp Stack

The Mac OS is built upon freeBSD, which is a free Unix clone. Linux was designed as a open source operating system, also based off of the Unix operating system. Therefore, you can use the directions above to export the database on a Mac. The one caveat is that the Mac's OS default shell is the the Bash shell, so you'll need use the bash syntax.

Export the database using a Content Management System (CMS)

If your website is built using a CMS, the best way to export/import your database would be to use a backup module/plugin for your particular CMS.

In Drupal there is the Backup and Migrate module.

If your using wordpress look at the wpbeginner website, which compares several different plugins.

Check your particular CMS for a backup solution.

Export the database using phpMyAdmin

If you are running an amp stack or have installed the phpMyAdmin module on your localhost, you can export your websites database using the phpMyAdmin interface. Any webhosting solution worth it's salt running Linux, has the phpMyAdmin module running on its server. Simply open phpMyAdmin, choose the database you wish to dump and export it.