What is mysqldump and How Do I Use It?

What is mysqldump,As one of the leading freely-to be had databases, MySQL is a popular preference for all types of internet programs. Being exposed to the Internet, your app is of direction uncovered to malicious assaults. If your server is compromised, at pleasant you’ll want to re-set up your software; at worst, you may lose your records absolutely. You’ll also in the end be in a situation in which you want emigrate a database from one server to some other.

What Is mysqldump ,Used For?

The mysqldump device has you covered for each of those conditions. It’s simple characteristic is to take a MySQL database and “sell off” it out as a text record. But now not simply any text document… The record is a set of SQL statements. These statements, when carried out, will re-assemble your database to the proper state it turned into in when you executed the sell off.

So you could use mysqldump to take exports of a database. These can be for the purpose of backups, or due to the fact you are shifting the database to a new host. In both case, you’ll in some unspecified time in the future import the textual content record again right into a MySQL database server. It will execute all the SQL statements inside the record, which rebuilds your DB to its authentic kingdom. This element would not sincerely use the mysqldump command, however it wouldn’t be possible with out this utility either!

The MySQL docs list different methods of creating backups, but those all have their very own drawbacks:

  • Hotcopying a DB from MySQL Enterprise is a notable manner to attain these backups… If you do not thoughts the Enterprise fee tag.
  • Copying the DB’s statistics directories may be complex in case you’re transferring across running structures, as their destinations might be specific.
  • Exporting to a delimited textual content report will provide you with the content, but you may must recreate the structure your self. And you would higher get it just proper…
  • You can regularly backup databases from GUI packages like MySQL Workbench. But that is a manual manner, i.E. Not some thing you may script or include in a batch job.

What is mysqldump,Installing the mysqldump Tool

If you are interested in this newsletter, chances are you already have MySQL installed. If this is the case, desirable information — you already have mysqldump! If not, you can honestly install MySQL within the general way on your operating gadget.

For Windows, test our commands here for putting in MySQL on Windows 7 (the set up method is still very a lot the identical). Likewise on macOS, see our directions to put in MySQL on macOS 10.7 (again, older but nonetheless relevant). Users of Ubuntu-based Linux systems can use the following command to install the MySQL consumer and utilities:

sudo apt install mysql-client

Extracting a MySQL Dump

Once installed, you can use mysqldump to get a full backup of your database.

mysqldump -h [your DB host's name or IP] -u [the DB user's name] -p [the database name] > db_backup.sql

Let’s ruin this command down a little:

  • “-h”: This flag is the database host. It can be a full host call (e.G. Myhost.Area.Com) or an IP deal with. You can also leave this blank if you’re running it on the equal host as the MySQL server.
  • “-u”: As referred to, that is your username.
  • “-p”: If you are nicely secured your MySQL set up, you may need a password to attach. This flag with out a argument will spark off you for a password while you execute the command. Sometimes it is beneficial to provide your password directly because the argument to this flag, as an example in a backup script. But on the prompt, you shouldn’t, because if someone won get admission to in your computer, they may get this password on your command history.
  • “> db_backup.Square”: This closing part is telling mysqldump to direct all its output right into a report. Normally, the command might output the whole thing at once to the console, which means you’ll see a group of SQL statements fly by means of. But instead, the > image is a sign to funnel all those things into the named text report as a substitute. And if this record doesn’t exist, it is going to be created robotically.

When it’s finished, you will have a .SQL report. This is only a textual content report containing SQL statements. You can open it up in any textual content editor to investigate the contents. Looking at an export from a WordPress database, you may see how those documents are put together.

The record is divided up into sections. In the above image, we will see the first section sets up the table for WordPress remarks. The second section then recreates the content material (i.E. Comment information) that changed into in those tables. When you go to re-import your MySQL sell off once more, the command will work through the record, executing the statements and re-building your database just the way it turned into. So how do you do that? Let’s take a look.

Importing a MySQL Dump File

Before you’re able to try this, you’ll need a database already created, and feature a legitimate username and password. You must also have all the permissions for the database. (Strictly speakme you do not want the GRANT permission, however it is simpler to simply furnish them all.) You can find details on database permissions in this article.

To re-import your data, comply with these steps:

  1. Log into the MySQL server with the mysql command.
  2. Type use [database name] at the set off, substituting the ideal call of the DB.
  3. Enter supply [filename], substituting the call of the unload report you took formerly.

When you are completed, you may see an extended listing of messages noting that SQL statements are executing. Keep a watch out for errors, but if you have the right permissions, you have to be best because it’s a clean database.

Once the manner is entire, you’ll have a reproduction of your authentic database. You can confirm this with the aid of doing every other sell off, and evaluating the 2 outputs. A respectable text editor can be capable of try this for you, or a committed diff tool like the one proven within the below photo:

There are best two variations between those files, as represented by means of red strains at the very top and bottom of the right scrollbar. The first is the line that contains the database call, and we anticipate this to be specific due to the fact we named them otherwise. The 2nd, all the manner at the stop, is the timestamp for the sell off record. And we also expect this to be special, as we recreated the second database after the first. Otherwise, the documents are precisely the same, meaning the databases that generated them are as nicely!