{"id":13427,"date":"2023-12-24T19:51:43","date_gmt":"2023-12-25T02:51:43","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13427"},"modified":"2023-12-24T19:55:58","modified_gmt":"2023-12-25T02:55:58","slug":"mysql-backup-a-database","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-backup-a-database\/","title":{"rendered":"How to Back Up and Restore a Database in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn the steps of how to back up a database on a MySQL server using mysqldump<\/code> program and restore it using the mysql<\/code> program.<\/p>\n\n\n\n

To back up a database on the MySQL server, you use the mysqldump<\/code> program that comes by default with the MySQL client installation.<\/p>\n\n\n\n

To restore a backup created by the mysqldump program, you use the mysql command-line program.<\/p>\n\n\n\n

We’ll demonstrate how to back up and restore a database on a MySQL server.<\/p>\n\n\n\n

Creating a sample database<\/h2>\n\n\n\n

First, connect to the MySQL server<\/a> using the mysql<\/code> program:<\/p>\n\n\n

mysql -u root -p<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new database called hr<\/code>:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> hr;<\/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 hr<\/code>:<\/p>\n\n\n
USE<\/span> hr;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> employees (\r\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\r\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\r\n    email VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> employees (name<\/span>, email) \r\nVALUES<\/span>\r\n    ('John Doe'<\/span>, 'john.doe@example.com'<\/span>),\r\n    ('Jane Smith'<\/span>, 'jane.smith@example.com'<\/span>),\r\n    ('Bob Johnson'<\/span>, 'bob.johnson@example.com'<\/span>),\r\n    ('Alice Jones'<\/span>, 'alice.jones@example.com'<\/span>),\r\n    ('Charlie Brown'<\/span>, 'charlie.brown@example.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, exit the mysql<\/code> 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 a database<\/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> program to back up the hr<\/code> database:<\/p>\n\n\n
mysqldump -h localhost -u root -p hr > D:\\backup\\hr.sql<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s break down each part of the command:<\/p>\n\n\n\n
    \n
  • mysqldump:<\/strong> This is the command-line utility for MySQL that allows you to dump the contents of a database into a file.<\/li>\n\n\n\n
  • -h localhost:<\/strong> This option specifies the hostname where the MySQL server is running. In this case, it’s set to “localhost,” which means the MySQL server is on the same machine as the command is being executed. If you want to back up a database located on a remote server, please specify the hostname of the server.<\/li>\n\n\n\n
  • -u root:<\/strong> This option specifies the MySQL user used for the connection. In this case, it’s set to “root,” which is a common default superuser account in MySQL.<\/li>\n\n\n\n
  • -p:<\/strong> This option prompts the user for the MySQL password. After entering the command, you will be prompted to enter the password for the specified user (in this case, the “root” user).<\/li>\n\n\n\n
  • hr:<\/strong> This is the name of the MySQL database that you want to back up. Replace “hr” with the actual name of the database you want to back up.<\/li>\n\n\n\n
  • > D:\\backup\\hr.sql:<\/strong> This part of the command uses the output redirection symbol (><\/code>) to send the output of the mysqldump<\/code> command to the file \"D:\\backup\\hr.sql\"<\/code>.<\/li>\n<\/ul>\n\n\n\n
    After entering a valid password, the mysqldump<\/code> program will create a backup of the hr<\/code> database and store it in the hr.sql<\/code> file located in the D:\\backup<\/code> directory.<\/p>\n\n\n\n
    Accidentally deleting some rows from a table<\/h2>\n\n\n\n
    First, connect to the MySQL:<\/p>\n\n\n
    mysql -u root -p<\/code><\/span><\/pre>\n\n\n
    Second, switch the current database to the hr<\/code>:<\/p>\n\n\n
    USE<\/span> hr;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, delete the employee with id 3 from the employees<\/code> table:<\/p>\n\n\n
    delete<\/span> from<\/span> employees where<\/span> id<\/span> = 3<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Suppose that this deletion is unintended and you want to restore the hr<\/code> database to recover the data.<\/p>\n\n\n\n
    Restoring a database<\/h2>\n\n\n\n
    First, open the Command Prompt on Windows or Terminal on Unix-like systems.<\/p>\n\n\n\n
    Second, use the following command to restore the hr<\/code> database from the backup file created by the mysqldump<\/code> utility:<\/p>\n\n\n
    mysql -u root -p hr < D:\\backup<\/span>\\hr.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here’s the breakdown of the command:<\/p>\n\n\n\n
      \n
    • mysql:<\/strong> This is the MySQL command-line client, which is used to interact with the MySQL server through the command line.<\/li>\n\n\n\n
    • -u root:<\/strong> This option specifies the MySQL user to be used for the connection. In this case, it’s set to “root,” which is a common default superuser account in MySQL.<\/li>\n\n\n\n
    • -p:<\/strong> This option prompts the user for the MySQL password. After entering the command, you will be prompted to enter the password for the specified user (in this case, the “root” user).<\/li>\n\n\n\n
    • hr:<\/strong> This is the name of the MySQL database that you want to restore. Replace “hr” with the actual name of the database you want to restore.<\/li>\n\n\n\n
    • < D:\\backup\\hr.sql:<\/strong> This part of the command uses the input redirection symbol (<<\/code>) to read the contents of the specified file (“D:\\backup\\hr.sql<\/code>“) and pass them as input to the mysql<\/code> command.<\/li>\n<\/ul>\n\n\n\n
      Third, connect to the MySQL server:<\/p>\n\n\n
      mysql -u root -p<\/code><\/span><\/pre>\n\n\n
      Fourth, switch the current database to hr<\/code>:<\/p>\n\n\n
      USE<\/span> hr;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Finally, retrieve data from the employees<\/code> table to verify the restoration:<\/p>\n\n\n
      SELECT<\/span> * FROM<\/span> employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +----+---------------+---------------------------+\r\n| id | name          | email                     |\r\n+----+---------------+---------------------------+\r\n|  1 | John Doe      | john.doe@example.com      |\r\n|  2 | Jane Smith    | jane.smith@example.com    |\r\n|  3 | Bob Johnson   | bob.johnson@example.com   |\r\n|  4 | Alice Jones   | alice.jones@example.com   |\r\n|  5 | Charlie Brown | charlie.brown@example.com |\r\n+----+---------------+---------------------------+\r\n5 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
      The output indicates that the hr<\/code> database has been fully restored.<\/p>\n\n\n\n
      Summary<\/h2>\n\n\n\n
        \n
      • Use the mysqldump<\/code> program to back up a database on a MySQL server.<\/li>\n\n\n\n
      • Use the mysql<\/code> program to restore a database from a backup file created by the mysqldump program.<\/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
Fifth, insert some rows<\/a> into the employees<\/code> table:<\/p>\n\n\n
Fourth, create a new table<\/a> inside the hr<\/code> database called employees<\/code>:<\/p>\n\n\n