{"id":12460,"date":"2023-11-21T02:29:10","date_gmt":"2023-11-21T09:29:10","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12460"},"modified":"2023-11-23T17:55:18","modified_gmt":"2023-11-24T00:55:18","slug":"mysqldump","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysqldump\/","title":{"rendered":"MySQL Backup Utility: mysqldump"},"content":{"rendered":"
\n
\"MySQL<\/figure>\n<\/div>\n\n\n

Summary<\/strong>: in this tutorial, you will learn how to use the mysqldump<\/code> tool to make a backup of one or more databases in a MySQL Server.<\/p>\n\n\n\n

Introduction to the mysqldump tool<\/h2>\n\n\n\n

The mysqldump<\/code> is a command-line utility in MySQL used for creating backups of MySQL databases. <\/p>\n\n\n\n

The mysqldump<\/code> tool allows you to dump the structure and\/or data of one or more databases into a file, which you can use to restore the databases later.<\/p>\n\n\n\n

In practice, you often use the mysqldump<\/code> for backup and restore operations, database migration, and transferring databases between servers.<\/p>\n\n\n\n

mysqldump tool location<\/h3>\n\n\n\n

The mysqldump<\/code> tool typically comes with the MySQL server installation by default. Its location depends on your operating system:<\/p>\n\n\n\n

    \n
  • Linux<\/strong>: on Linux systems, including distributions like Ubuntu, CentOS, and others, you can find mysqldump<\/code> in the \/usr\/bin\/<\/code> directory. You can use the which<\/code> command to locate it: which mysqldump<\/code><\/li>\n\n\n\n
  • Windows<\/strong>: on Windows, you can find the mysqldump in the directory C:\\Program Files\\MySQL\\MySQL Server X.Y\\bin\\<\/code>, where X.Y<\/code> is the version number of the MySQL database server.<\/li>\n\n\n\n
  • macOS<\/strong>: Like Linux systems, you can find the mysqldump<\/code> at \/usr\/bin\/<\/code> directory on macOS.<\/li>\n<\/ul>\n\n\n\n

    It’s a good practice to include the directory that contains mysqldump<\/code> in your system’s PATH environment variable so that you can invoke the command from any location in the command prompt or terminal.<\/p>\n\n\n\n

    Basic syntaxes of the mysqldump tool<\/h2>\n\n\n\n

    There are three basic syntaxes for using the mysqldump<\/code> tool:<\/p>\n\n\n\n

    1) Dump one or more tables<\/h3>\n\n\n
    mysqldump -u root -p [options] db_name [tbl_name ...] > output_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    2) Dump one or more databases<\/h3>\n\n\n
    mysqldump -u root -p [options] --databases db_name ... > output_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    3) Dump all databases<\/h3>\n\n\n
    mysqldump -u root -p [options] --all-databases > output_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    The mysqldump options<\/h2>\n\n\n\n
    The mysqldump tool provides you with two different ways to specify an option: long form and short form.<\/p>\n\n\n\n
    For example, if you want to specify the long form for the user, you can use the --user=username<\/code> option:<\/p>\n\n\n
    --user=username<\/code><\/span><\/pre>\n\n\n
    Alternatively, you can use the shorter and more concise option -u username<\/code>:<\/p>\n\n\n
    -u username<\/code><\/span><\/pre>\n\n\n
    Here are some common mysqldump<\/code> options:<\/p>\n\n\n\n
    Option<\/th>Short Form<\/th>Meaning<\/th><\/tr><\/thead>
    –user<\/td>-u<\/td>username<\/td><\/tr>
    –password<\/td>-p<\/td>password<\/td><\/tr>
    –add-drop-database<\/td>N\/A<\/td>Add a DROP DATABASE<\/a> statement before each CREATE DATABASE<\/a> statement<\/td><\/tr>
    –add-drop-table<\/td>N\/A<\/td>Add a DROP TABLE<\/a> statement before each CREATE TABLE<\/a> statement.<\/td><\/tr>
    –add-drop-trigger
    <\/td>    		N\/A<\/td>Add a DROP TRIGGER<\/a> statement before each CREATE TRIGGER<\/a> statement.<\/td><\/tr>
    –add-locks
    <\/td>    		N\/A<\/td>Enclose each table dump with LOCK TABLES and UNLOCK TABLES statements.<\/td><\/tr>
    –all-databases<\/td>-A<\/td>Perform a database dump for all tables in all databases.<\/td><\/tr>
    –databases<\/td>-B<\/td>Dump one or more databases by instructing mysqldump to treat all name arguments on the command line as database names.<\/td><\/tr>
    –no-data<\/td>-d<\/td>Do not include any row information for the table.<\/td><\/tr>
    –result-file<\/td>-r<\/td>Direct the output to a specified file. If the file already exists, the tool will overwrite it.<\/td><\/tr>
    –routines<\/td>-R<\/td>Include stored routines (procedures and functions) when dumping the databases.<\/td><\/tr>
    –no-create-db<\/td>-n<\/td>Suppress the CREATE DATABASE<\/code> statements that would otherwise be included in the output when the --databases<\/code> or --all-databases<\/code> option is given.<\/td><\/tr>
    –no-create-info<\/td>-t<\/td>Do not include CREATE TABLE<\/code>\u00a0statements for each dumped table.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    The mysqldump tool examples<\/h2>\n\n\n\n
    Let’s explore some examples of using the mysqldump<\/code> tool to create backups.<\/p>\n\n\n\n
    1) Creating a backup of a single database<\/h3>\n\n\n\n
    The following command creates a backup of a single database:<\/p>\n\n\n
    mysqldump -u username -p -B db_name > path_to_backup_file<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this syntax:<\/p>\n\n\n\n
      \n
    • mysqldump<\/code>: This is the command-line utility for MySQL database backups.<\/li>\n\n\n\n
    • -u username<\/code>: This option specifies the MySQL user to use for the connection. Replace username<\/code> with the actual username you want to use.<\/li>\n\n\n\n
    • -p<\/code>: This option prompts you for the password associated with the MySQL user specified by the -u<\/code> option. After entering the command, you will be prompted to enter the password interactively.<\/li>\n\n\n\n
    • -B<\/code>: This option tells mysqldump<\/code> to treat the next argument as a database name, rather than a list of tables. In your command, db_name<\/code> is the database name you want to back up.<\/li>\n\n\n\n
    • db_name<\/code>: Replace this with the name of the MySQL database you want to back up.<\/li>\n\n\n\n
    • > path_to_backup_file<\/code>: This part of the command redirects the output of mysqldump<\/code> to a file specified by path_to_backup_file<\/code>. This is where the backup data will be stored.<\/li>\n<\/ul>\n\n\n\n
      For example, the following command creates a backup of the database classicmodels<\/code> to the file D:\\backup\\classicmodels.sql<\/code>:<\/p>\n\n\n
      mysqldump -u root -p -B classicmodels > D:\\backup\\classicmodels.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
      It’ll prompt you to enter a password for the root account. After inputting a valid password for the root account, the tool will dump the classicmodels<\/code> database into the file D:\\backup\\classicmodels.sql<\/code><\/p>\n\n\n\n
      If you want to put the password in the command, you can use the --password<\/code> long-form option:<\/p>\n\n\n
      mysqldump -u root --password=Abcd1234 -B classicmodels > D:\\backup\\classicmodels.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
      It’ll issue a warning:<\/p>\n\n\n
      mysqldump: [Warning] Using a password on the command line interface can be insecure.<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
      2) Creating a backup of multiple databases<\/h3>\n\n\n\n
      To make a backup of multiple databases, you specify a list of the database names after the --database<\/code> option:<\/p>\n\n\n
      mysqldump -u username -p -B <dbname1>[,<dbname2>, ...] > path_to_backup_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
      In this syntax: <\/p>\n\n\n\n
        \n
      • mysqldump<\/code>: This is the command-line utility for MySQL database backup.<\/li>\n\n\n\n
      • -u username<\/code>: This option specifies the MySQL user to be used for the operation. You would replace “username” with the actual MySQL username.<\/li>\n\n\n\n
      • -p<\/code>: This option prompts for the MySQL user’s password. After entering the command, you will be prompted to provide the password.<\/li>\n\n\n\n
      • -B<\/code>: This option is used to indicate that one or more databases will follow. It’s followed by the list of databases you want to include in the backup.<\/li>\n\n\n\n
      • <dbname1>[,<dbname2>, ...]<\/code>: This part specifies the name of the database or databases you want to back up. You can list multiple databases separated by commas.<\/li>\n\n\n\n
      • > path_to_backup_file<\/code>: This part of the command redirects the output of the mysqldump operation to a file specified by “path_to_backup_file”.<\/li>\n<\/ul>\n\n\n\n
        For example, the following command makes a backup of the classicmodels<\/code> and world<\/code> databases:<\/p>\n\n\n
        mysqldump -u root -p --databases classicmodels world > D:\\backup\\databases.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        3) Creating a backup of all databases<\/h3>\n\n\n\n
        To make a backup of all databases in a MySQL Server, you use the -A option:<\/p>\n\n\n
        mysqldump -u username -p -A > path_to_backup_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        The following statement makes a backup of all databases in the current MySQL server:<\/p>\n\n\n
        mysqldump -u root -p -A > D:\\backup\\all_databases.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        4) Creating a backup of specific tables in a database<\/h3>\n\n\n\n
        To make a backup of one or more tables from a database, you use the following command:<\/p>\n\n\n
        mysqldump -u username -p db_name tbl_name1, tbl_name2,... > path_to_backup_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        For example, to make a backup of the employees<\/code> table from the classicmodels<\/code> database, you use the following command:<\/p>\n\n\n
        mysqldump -u root -p classicmodels employees > d:\\backup\\employees.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        5) Creating a backup of the database structure only<\/h3>\n\n\n\n
        To make a backup of the database structure only, you use the -d<\/code> option:<\/p>\n\n\n
        mysqldump -u username -p -d db_name > path_to_backup_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        The statement will generate a dump file that contains the SQL statement necessary to re-create the database structure. The dump file does not contain INSERT<\/code> statements.<\/p>\n\n\n\n
        For example, the following statement makes a backup of the database structure of the database classicmodels<\/code>:<\/p>\n\n\n
        mysqldump -u root -p -d classicmodels > d:\\backup\\classicmodels.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        6) Creating a backup of data only<\/h3>\n\n\n\n
        To make a backup of the database data only, you use the -t<\/code> option:<\/p>\n\n\n
        mysqldump -u username -p -t db_name > path_to_backup_file<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        The statement will generate a dump file that contains the SQL statements necessary to lock tables and insert data into the tables. It has no CREATE TABLE<\/code> statements.<\/p>\n\n\n\n
        The following command makes a backup of data from the database classicmodels<\/code>:<\/p>\n\n\n
        mysqldump -u root -p -t classicmodels > d:\\backup\\classicmodels.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
        Summary<\/h2>\n\n\n\n
          \n
        • Use the mysqldump<\/code> utility program to create backups of databases in MySQL Server.<\/li>\n<\/ul>\n
          \n\t
          \n\t\t
          Was this tutorial helpful? <\/div>\n\t\t
          \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
          \n\t\t
          \n\t\t\t
          <\/div>\n\t\t\t\n\t\t\t