{"id":13433,"date":"2023-12-24T20:18:00","date_gmt":"2023-12-25T03:18:00","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13433"},"modified":"2023-12-24T20:22:03","modified_gmt":"2023-12-25T03:22:03","slug":"mysql-backup-all-databases","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-backup-all-databases\/","title":{"rendered":"How to Back Up and Restore All Databases in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to make a backup of all the databases on a MySQL Server using the mysqldump<\/code> program and restore them using the mysql<\/code> program.<\/p>\n\n\n\n

Creating sample databases<\/h2>\n\n\n\n

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:<\/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
Next, show all the databases<\/a>:<\/p>\n\n\n
show<\/span> databases<\/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| Database           |\n+--------------------+<\/span>\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\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
The command returns four system databases.<\/p>\n\n\n\n
CREATE<\/span> DATABASE<\/span> testdb1;\nCREATE<\/span> DATABASE<\/span> testdb2;\nCREATE<\/span> DATABASE<\/span> testdb3;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, show all the databases again:<\/p>\n\n\n
show<\/span> databases<\/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| Database           |\n+--------------------+<\/span>\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n| testdb1            |\n| testdb2            |\n| testdb3            |\n+--------------------+<\/span>\n7 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 all databases<\/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 back up all databases on the MySQL server:<\/p>\n\n\n
mysqldump -h localhost -u root -p --all-databases > D:\\backup\\all_databases.sql<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s break down the command:<\/p>\n\n\n\n
    \n
  • mysqldump:<\/strong> This is the command-line utility for MySQL that allows you to dump all databases 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,” indicating that the MySQL server is on the same machine as the command is being executed.<\/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<\/code> user).<\/li>\n\n\n\n
  • –all-databases:<\/strong> This option tells mysqldump<\/code> to dump all databases on the MySQL server, not just a specific one. It includes the structure and data for all databases including the system databases.<\/li>\n\n\n\n
  • > D:\\backup\\all_databases<\/code>.sql:<\/strong> This part of the command uses the output redirection symbol (><\/code>) to send the output of the mysqldump<\/code> command to a file. In this case, the file is specified as D:\\backup\\all_databases<\/code>.sql<\/code>. The file will contain the SQL statements necessary to recreate all databases and their data.<\/li>\n<\/ul>\n\n\n\n
    Third, examine the all_database<\/code>.sql file in the D:\\backup directory.<\/p>\n\n\n\n
    Accidentally removing a database<\/h2>\n\n\n\n
    First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:<\/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
    Second, drop the testdb1<\/code> and testdb2<\/code> databases:<\/p>\n\n\n
    drop<\/span> database<\/span> testdb1;\ndrop<\/span> database<\/span> testdb2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, show all the databases:<\/p>\n\n\n
     show<\/span> databases<\/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| Database           |\n+--------------------+<\/span>\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n| testdb3            |\n+--------------------+<\/span>\n5 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
    Suppose you want to restore the testdb1<\/code> and testdb2<\/code> from the backup created by the mysqldump program.<\/p>\n\n\n\n
    Restoring all databases<\/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 mysql<\/code> program to restore all databases from the backup created by the mysqldump<\/code> program:<\/p>\n\n\n
    mysql -h localhost -u root -p < D:\\backup<\/span>\\all_databases.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, connect to the MySQL server:<\/p>\n\n\n
    mysql -h localhost -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Finally, show all the databases:<\/p>\n\n\n
    show<\/span> databases<\/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| Database           |\n+--------------------+<\/span>\n| information_schema |\n| mysql              |\n| performance_schema |\n| sys                |\n| testdb1            |\n| testdb2            |\n| testdb3            |\n+--------------------+<\/span>\n7 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
    The output indicates that the testdb1<\/code> and testdb2<\/code> databases have been restored successfully.<\/p>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the mysqldump<\/code> with the --all-databases<\/code> option to dump all databases on a MySQL server into a file.<\/li>\n\n\n\n
    • Use the mysql<\/code> program to restore all the databases from a backup file created by the mysqldump<\/code> 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
Then, create three databases<\/a> called testdb1<\/code>, testdb2<\/code>, and testdb3<\/code>:<\/p>\n\n\n