{"id":13441,"date":"2023-12-24T21:07:33","date_gmt":"2023-12-25T04:07:33","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13441"},"modified":"2023-12-25T17:35:27","modified_gmt":"2023-12-26T00:35:27","slug":"mysql-dump-one-table","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-dump-one-table\/","title":{"rendered":"Backing Up MySQL Tables: A Step-by-Step Guide"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to back up one or some tables in MySQL using the mysqldump<\/code> program.<\/p>\n\n\n\n

To make a backup of one table, you use mysqldump<\/code> command with the following option:<\/p>\n\n\n

mysqldump -h hostname -u username -p dbname tblname > table.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To back up some tables, you specify a list of table names after the database name in the mysqldump command:<\/p>\n\n\n
mysqldump -h hostname -u username -p dbname tblname1 tblname2 > table.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
We’ll illustrate how to dump a table or some tables from a database.<\/p>\n\n\n\n
Creating a sample database<\/h2>\n\n\n\n
First, open a Command Prompt on Windows or Terminal program on Unix-like systems and connect to a MySQL server<\/a>:<\/p>\n\n\n
mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> DATABASE<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, switch the current database to sales<\/code>:<\/p>\n\n\n
USE<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> products (\n    product_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    product_name VARCHAR<\/span>(255<\/span>),\n    unit_price DECIMAL<\/span>(10<\/span>, 2<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> customers (\n    customer_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    customer_name VARCHAR<\/span>(255<\/span>),\n    email VARCHAR<\/span>(255<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> orders (\n    order_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    customer_id INT<\/span>,\n    order_date DATE<\/span>,\n    FOREIGN<\/span> KEY<\/span> (customer_id) REFERENCES<\/span> customers(customer_id)\n);\n\nCREATE<\/span> TABLE<\/span> order_details (\n    order_detail_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    order_id INT<\/span>,\n    product_id INT<\/span>,\n    quantity INT<\/span>,\n    total_price DECIMAL<\/span>(10<\/span>, 2<\/span>),\n    FOREIGN<\/span> KEY<\/span> (order_id) REFERENCES<\/span> orders(order_id),\n    FOREIGN<\/span> KEY<\/span> (product_id) REFERENCES<\/span> products(product_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
-- Inserting into Products Table<\/span>\nINSERT<\/span> INTO<\/span> products (product_name, unit_price)\nVALUES<\/span>\n    ('Desktop Computer'<\/span>, 800.00<\/span>),\n    ('Tablet'<\/span>, 300.00<\/span>),\n    ('Printer'<\/span>, 150.00<\/span>);\n\n-- Inserting into Customers Table<\/span>\nINSERT<\/span> INTO<\/span> customers (customer_name, email)\nVALUES<\/span>\n    ('Alice Johnson'<\/span>, 'alice@example.com'<\/span>),\n    ('Charlie Brown'<\/span>, 'charlie@example.com'<\/span>),\n    ('Eva Davis'<\/span>, 'eva@example.com'<\/span>);\n\n-- Inserting into Orders Table<\/span>\nINSERT<\/span> INTO<\/span> orders (customer_id, order_date)\nVALUES<\/span>\n    (1<\/span>, '2023-02-01'<\/span>),\n    (2<\/span>, '2023-02-02'<\/span>),\n    (3<\/span>, '2023-02-03'<\/span>);\n\n-- Inserting into Order_Details Table<\/span>\nINSERT<\/span> INTO<\/span> order_details (order_id, product_id, quantity, total_price)\nVALUES<\/span>\n    -- Order 1 details<\/span>\n    (1<\/span>, 1<\/span>, 2<\/span>, 1600.00<\/span>),\n    (1<\/span>, 2<\/span>, 3<\/span>, 900.00<\/span>),\n\n    -- Order 2 details<\/span>\n    (2<\/span>, 2<\/span>, 2<\/span>, 600.00<\/span>),\n    (2<\/span>, 3<\/span>, 1<\/span>, 150.00<\/span>),\n\n    -- Order 3 details<\/span>\n    (3<\/span>, 1<\/span>, 3<\/span>, 2400.00<\/span>),\n    (3<\/span>, 3<\/span>, 2<\/span>, 300.00<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SHOW<\/span> TABLES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------------+<\/span>\n| Tables_in_sales |\n+-----------------+<\/span>\n| customers       |\n| order_details   |\n| orders          |\n| products        |\n+-----------------+<\/span>\n4 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, exit the mysql program:<\/p>\n\n\n
exit<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Backing up one table<\/h2>\n\n\n\n
First, open a Command Prompt on Windows or Terminal program on Unix-like systems.<\/p>\n\n\n\n
Second, execute the mysqldump<\/code> command to back up the order_details<\/code> table in the sales<\/code> database:<\/p>\n\n\n
mysqldump -u root -p sales order_details > D:\\backup<\/span>\\order_details.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the command:<\/p>\n\n\n\n
    \n
  • mysqldump<\/code>: The command-line utility for creating MySQL database backups.<\/li>\n\n\n\n
  • -u root<\/code>: Specifies the MySQL user, in this case, “root,” which is often a superuser with administrative privileges.<\/li>\n\n\n\n
  • -p<\/code>: Prompts for the MySQL user’s password. After entering the command, you’ll be prompted to input the password interactively.<\/li>\n\n\n\n
  • sales<\/code>: The name of the database in which you want to back up a table.<\/li>\n\n\n\n
  • order_details<\/code>: The name of the table within the “sales” database. Only the data from the “order_details<\/code>” table will be included in the backup.<\/li>\n\n\n\n
  • > D:\\backup\\order_details<\/code>.sql<\/code>: Redirects the output of the mysqldump<\/code> command to a file named “order_details<\/code>.sql” located at the specified path “D:\\backup\\”. This creates an SQL file containing the dumped data from the “order_details<\/code>” table.<\/li>\n<\/ul>\n\n\n\n
    Making a backup of some tables<\/h2>\n\n\n\n
    First, open a Command Prompt on Windows or Terminal program on Unix-like systems.<\/p>\n\n\n\n
    Second, execute the following mysqldump<\/code> command to back up the orders<\/code> and order_details<\/code> tables in the sales<\/code> database:<\/p>\n\n\n
    mysqldump -u root -p sales orders order_details > D:\\backup<\/span>\\some_tables.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the mysqldump<\/code> program to make a backup of one or more tables from a database.<\/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
Sixth, show all the tables<\/a> in the sales<\/code> database:<\/p>\n\n\n
Fifth, insert data<\/a> into the tables:<\/p>\n\n\n
Fourth, create four tables<\/a> in the sales<\/code> database called products<\/code>, customers<\/code>, orders<\/code>, and order_details<\/code>:<\/p>\n\n\n
Second, create a new database<\/a> called sales<\/code>:<\/p>\n\n\n