How to do MySQL dump

How to do MySQL dump

06.09.2021
Author: HostZealot Team
2 min.
2128

MySQL database dump is a procedure for backing up a file that contains instructions in SQL language. These instructions allow you to exactly reproduce the copy of your database as it was at the time the dump was created. The need to create a SQL Dump may arise in two cases:

What you will learn about in this article?
  1. When transferring data to a new server or hosting. In this case it will be enough to import the created dump to recreate the server with all your settings and data.
  2. Data backup. Creating a point where your server was as stable as possible is a good solution if any major updates or changes are planned in the near future. In case some kind of failure happens, you can easily roll back the database to its stable state.

Similarly, you can also replace an existing database if needed.

How to dump a MySQL database

The first way - with the help of phpMyAdmin. This tool is preinstalled in all CMS without exception, so it is the most convenient one. So, to export the database in .sql extension, you need:

  1. Connect to phpMyAdmin by entering your username and password.
  2. On the left, find the item "MySQL databases", select the desired database.
  3. Go to the "Export" tab, you will see a dialog box where you select the type of SQL database - choose and click "ok".

After that, it remains to wait until the system completes the creation of a dump of the database on your computer. The resulting file in .sql format you can later import through the appropriate tab in the same phpMyAdmin panel.

The second way - dumping by using the console. At first glance it may seem that this is a more complex option, but in fact it takes much less time. The algorithm is as follows:

  1. Open the console.
  2. Enter the command "mysqldump -uuser -ppass db_name > file_to_save".

To make things clear, let's explain:

  • -uUser - here you should enter the name of a user who has enough rights to create a dump (for example, u[mylogin];
  • -pPassword - enter the user's password here (for example, -p[secretpassword];
  • DB_NAME - the name of the database;
  • FILE_NAME_TO_SAVE - path to save the dump.

Everything is very simple and straightforward. Well, to restore the database from the dump, the created file can be similarly imported through the console. To do this, enter the command "mysql -u[mylogin] -p[secretpassword] -f mydb < mydb_dump.sql".

Now you know how to dump a MySQL database, and if you need to, you can easily export and then import the file with the .sql extension from one server to another.

Related Articles