{"id":94885,"date":"2022-10-14T00:00:44","date_gmt":"2022-10-14T00:00:44","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94885"},"modified":"2025-06-30T11:15:51","modified_gmt":"2025-06-30T11:15:51","slug":"backing-up-mysql-part-1-mysqldump","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/backing-up-mysql-part-1-mysqldump\/","title":{"rendered":"Backing up MySQL Part 1: mysqldump"},"content":{"rendered":"<p><code>mysqldump<\/code> is one of the most popular database backup tools in the MySQL world.<\/p>\n<p>The tool is prevalent partly because it\u2019s very basic and quite powerful &#8211; <code>mysqldump<\/code> database backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this command line-based tool is one of the top choices for junior and senior database engineers across the world alike.<\/p>\n<h2><a id=\"post-94885-_nr46ol1xzb8i\"><\/a>What is mysqldump and How Does It Work?<\/h2>\n<p>On a high level, <code>mysqldump<\/code> is a CLI-based database backup utility that is widely used to take logical backups. A logical backup creates a \u201clogical copy\u201d of the database by creating SQL statements that can be used to recreate the database structure, indexes, partitions, and data inside of tables within the database.<\/p>\n<p>The utility can be invoked by typing <code>mysqldump<\/code> inside of the command line once we\u2019re logged into MySQL and in most use cases, the usage of the tool looks something like this (all as one command on one line, separated for formatting purposes).<\/p>\n<pre class=\"lang:none theme:none\">mysqldump -u[username] -p[password] \n    [one_or_more_of_the_available_options] \n    [database_name] [table_name] &gt; output.sql<\/pre>\n<p>In this code block:<\/p>\n<ul>\n<li><code>username - <\/code>depicts your username that has access to do the backup.<\/li>\n<li><code>password -<\/code>\u00a0is the password of that user.<\/li>\n<li><code>one_or_more_of_the_available_options<\/code> &#8211; lets us define the options needed for a specific dump of the data (we will get into that a little later.) You can also define a <code>database_name<\/code> from where they want their data to be dumped.<\/li>\n<\/ul>\n<p>You can also elect to define a <code>table_name<\/code> inside of the database as well to only dump one table.<\/p>\n<p>The mysqldump command is also usually finalized by specifying a file to write the output to \u2013 an arrow towards the right (\u201c&gt;\u201d) with a file name after it will tell MySQL where to write the output of the command (the file can have any legal filename.) Without it, mysqldump will print all results to the screen. Users can also make the arrow face towards the left to import their data dumps into MySQL using the CLI like so:<\/p>\n<p><code>mysql -uroot -p db_name &lt; backup.sql<\/code><\/p>\n<p>Bear in mind that, like most command line tools, it\u2019s not advisable to use <code>mysql<\/code> or<code> mysqldump<\/code> by supplying a password in clear text. There are many reasons for this, but the main one is that might be possible for another person to log in and see the last issued commands, which include the provided username and password. It\u2019s best to provide a username and password in the <code>my.cnf<\/code> file and avoid providing the password in the command line by doing something like this (also, choose a stronger password than the one provided here):<\/p>\n<pre class=\"lang:none theme:none\">[mysqldump]\nuser=secret\npassword=useastrongpasswordhere<\/pre>\n<p>Then, our query would look the same, just without the <code>-p[password]<\/code> option due to the password already being provided in <code>my.cnf<\/code>: provide a username in the same fashion and you can get rid of the <code>-u[username]<\/code> option as well.<\/p>\n<p>The output of mysqldump is usually a file with a <code>.sql<\/code> extension: if the name of the file is specified, MySQL creates the specified file, but if not, the name of the file will consist of the name of the database or the table that is being backed up (the following command would back up all tables inside of a demo_database into a file called <code>demodb_backup.sql<\/code>):<\/p>\n<p>mysqldump -uroot -p demo_database &gt; demodb_backup.sql<\/p>\n<h2><a id=\"post-94885-_wyr6a83207m9\"><\/a>Options Provided by mysqldump<\/h2>\n<p>Now that we have covered the basics of how <code>mysqldump<\/code> works, let\u2019s look at the options of <code>mysqldump<\/code>. Some of the commonly used options are as follows (all options should be provided after the username and the password, in the <code>[one_or_more_of_the_available_options]<\/code> position as indicated in the previous section.) All of the options can also be used in conjunction with other options as well:<\/p>\n<table style=\"width: 100%;\">\n<tbody>\n<tr>\n<td style=\"width: 26.2559%;\"><strong>Option<\/strong><\/td>\n<td style=\"width: 72.9858%;\"><strong>What Does it Do?<\/strong><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><\/td>\n<td style=\"width: 72.9858%;\"><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--all-databases<\/code><\/td>\n<td style=\"width: 72.9858%;\">Can be used to take a backup of all databases in a database instance.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--add-drop-[table|database]<\/code><\/td>\n<td style=\"width: 72.9858%;\">Adds a <code>DROP [TABLE|DATABASE]<\/code> statement before creating tables or databases. Useful if we\u2019re migrating updated data back to the server where the old pieces of the data reside.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--fields-terminated-by<\/code><\/td>\n<td style=\"width: 72.9858%;\">Adds a character at the end of one column and at the start of another (data will look like <code>\u201ccolumn1|column2\u201d<\/code> if the <code>\u201c|\u201d <\/code>termination denominator is used, etc.) Very frequently used together with <code>LOAD DATA INFILE | LOAD DATA INTO OUTFILE<\/code> statements.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--force<\/code><\/td>\n<td style=\"width: 72.9858%;\">Using this setting, the database dump continues even if any errors are encountered. Useful in a demo environment. Think of this setting as a brother to the <code>--ignore<\/code> command when loading a dump into the database \u2013 it essentially ignores all of the non-critical errors encountered by MySQL (such as duplicate value errors) letting the backup process continue until it\u2019s finished no matter what.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--no\u2013data<\/code><\/td>\n<td style=\"width: 72.9858%;\">Tells MySQL to avoid dumping the data existing inside tables (i.e. MySQL will dump only the database and table structure, so the dumps will be considerably faster if we have a lot of data.)<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--where<\/code><\/td>\n<td style=\"width: 72.9858%;\">Only dumps data matching a specified <code>WHERE<\/code> clause.<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 26.2559%;\"><code>--lock-tables<\/code><\/td>\n<td style=\"width: 72.9858%;\">Allows us to lock all of the tables before taking a copy of them so that no operations could interfere with our backup \u2013 in such a case, data will not be inserted, updated, deleted, or otherwise modified. All locks last until the backup operation is finalized.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The table above should allow you to realize how powerful <code>mysqldump<\/code> can be if used properly; keep in mind that the table above doesn\u2019t provide a complete list of the available options (you can find all options <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\">in the MySQL documentation<\/a>); but it should be a decent starting point to gain a basic understanding of what <code>mysqldump<\/code> can do.<\/p>\n<h2><a id=\"post-94885-_tkhgoae59ex5\"><\/a>mysqldump Use Cases<\/h2>\n<p>Beyond simple backups, <code>mysqldump<\/code> can also be used to assist when we find ourselves dealing with \u201ccorner-case\u201d usage cases as well: for example, the tool can provide tab-delimited output that can be exceptionally useful if we want to work with data in \u201cbig data\u201d text viewers such as <a href=\"https:\/\/www.emeditor.com\/\">EmEditor<\/a> or work with our data using <a href=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/excel\">Microsoft Excel<\/a>: we simply specify the <code>--tab<\/code> option as part of the options and <code>mysqldump<\/code> will do all of the work for us. Should we want to test if the database object definitions are loaded into the database properly, all we have to do is dump our data by using a combination of four options provided one after another:<\/p>\n<ul>\n<li>The <code>--all-databases<\/code> option would dump all databases.<\/li>\n<li>The <code>--no-data<\/code> option would refrain from copying data.<\/li>\n<li>The <code>--routines<\/code> option would dump procedures and functions inside of our database.<\/li>\n<li>The <code>--events<\/code> option would dump the existing scheduled events.<\/li>\n<\/ul>\n<p>When we use <code>mysqldump<\/code> in such a fashion, a dump (backup) file will be created, but it won\u2019t have any data. However, loading the file back into a database server, though, will allow us to test for any weird, odd-looking warnings, or spot other noteworthy errors.<\/p>\n<p><code>mysqldump<\/code> can also be used together with other client-line programs such as <code>mysql<\/code>, <code>mysqlimport<\/code> or in a manual fashion using <code>LOAD DATA INFILE<\/code>: all we have to do is have the file taken by <code>mysqldump<\/code> at hand, then issue a statement like so (the following statement would import a file named demo.txt into a database named <code>database_name<\/code>):<\/p>\n<pre class=\"lang:none theme:none\">mysqlimport --fields-terminated-by=: --lines-terminated-by=\\n \n        database_name demo.txt<\/pre>\n<p>We can also use <code>LOAD DATA INFILE<\/code> in the same fashion: if our data is delimited by, say, the \u201c<code>,<\/code>\u201d sign, we could make use of a query like this and load our data existing in demo.txt into a table called <code>demo_table<\/code>:<\/p>\n<pre class=\"lang:none theme:none \">LOAD DATA INFILE 'demo.txt' INTO TABLE demo_table FIELDS  \n        TERMINATED BY ',';<\/pre>\n<h2>Combining Options with mysqldump<\/h2>\n<p>Now let\u2019s rewind a little. Throughout this article, we\u2019ve kept mentioning that other options can be used together with <code>mysqldump<\/code> to do complex things with the tool. For some, here\u2019s where the depths of <code>mysqldump<\/code> become a little complex but bear with us and we will explain everything you need to know.<\/p>\n<p>First off, bear in mind that all components of MySQL (including, but not limited to mysqldump) usually have a couple of additional options can be specified and those options vary according to the component that we work with. Combining options is simple:<\/p>\n<ol>\n<li>Choose the component you need to work with (in our case, we\u2019re looking at mysqldump.)<\/li>\n<li><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\">Visit the documentation<\/a> and familiarize yourself with the available options regarding that component (the complete list of options can be found in the documentation of the component you\u2019ve chosen \u2013 the options for mysqldump can be found <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\">here<\/a>.)<\/li>\n<li>Evaluate the issue that you\u2019re having. How does the option you\u2019re looking at help solve it? Will the results be satisfactory?<\/li>\n<li>Choose one or multiple options from those available.<\/li>\n<\/ol>\n<p>Once you have chosen an option, remember that the output of any issued command is directly dependant on additional options. In other words, any option that is specified modifies the functionality of the command you\u2019ve chosen. All options need to be specified by writing their name as part of a command after \u201c&#8211;\u201c, like so:<\/p>\n<pre class=\"lang:none theme:none\">mysqldump --no-data<\/pre>\n<p>The above statement would make <code>mysqldump<\/code> work with a command named \u201c<code>no-data<\/code>\u201d (as explained above, the command wouldn\u2019t dump any data, and it would only dump the structure of the database.) Combining <code>--add-drop-table<\/code> to tell MySQL to make sure the table of the same name doesn\u2019t exist before creating any other tables. Some people may also want to specify an absolute path to the file in order to ensure that the file is going to be stored in a directory of their choice (doing so may be a necessity if the &#8211;secure-file-priv option is enabled: the option limits the ability for users to export or import data to or from the database \u2013 in that case, data could only be exported into a directory specified in the variable.)<\/p>\n<p>Coming back to mysqldump, though, one of the most frequent usage scenarios consist of dumping all databases and including a DROP TABLE statement before any table is created \u2013 doing so will ensure that even if the backup is loaded into a database that has the exact same table names, they will be dropped and created anew.:<\/p>\n<pre class=\"lang:none theme:none\">mysqldump -uroot --all-databases --add-drop-table &gt; backup.sql<\/pre>\n<p>As we have covered \u2013 <code>mysqldump<\/code> will dump the data inside of MySQL, but the specifics of what it will do are directly dependent on the options you specify when executing it. All options have a specific use, and once we choose what options we are going to combine the command with, we need to keep in mind the fact that it affects the output that we receive.<\/p>\n<p>The complete list of available options exclusive to <code>mysqldump<\/code> can be found <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\">over at the documentation<\/a>. When you\u2019re done dumping, test the backup as well: load it into your MySQL database instance through <code>phpMyAdmin<\/code> or simply specify the name of the file through the CLI like so (note that the database must be created before importing the data into it):<\/p>\n<pre class=\"lang:none theme:none\">mysql demo &lt; backup.sql<\/pre>\n<p>The above command will import a backup into your database called \u201cdemo.\u201d Do note this command assumes that you have your username and password specified in <code>my.cnf<\/code> in the same way you did for <code>mysqlbackup<\/code> (see example above) \u2013 if the username and password are not specified in your <code>my.cn<\/code>f file, you will still have to provide a username and a password like in the example below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1377\" height=\"539\" class=\"wp-image-94886\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-26.png\" \/><\/p>\n<p>Then, inspect your database \u2013 does the data in it represent the data you expect to be in the instance?<\/p>\n<p>With that being said, we will now leave the gates towards the depths of <code>mysqldump<\/code> open for you to explore; the advice contained in this blog post should be a decent starting point for those who are interested in how the CLI-based database dumping tool works and why does it work in the way that it does, and <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\">the MySQL documentation<\/a> will act as a guide for people who are interested in the tool on a deeper level. Nothing beats your own experience though \u2013 do experiment with <code>mysqldump<\/code> on local servers, make sure to <a href=\"https:\/\/askubuntu.com\/questions\/1153200\/backup-of-mysql-database-using-terminal\">learn from the mistakes other developers made in the past<\/a> as well, and until next time!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>mysqldump is one of the most popular database backup tools in the MySQL world. The tool is prevalent partly because it\u2019s very basic and quite powerful &#8211; mysqldump database backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this&#8230;&hellip;<\/p>\n","protected":false},"author":339547,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792],"tags":[],"coauthors":[146040],"class_list":["post-94885","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94885","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/339547"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94885"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94885\/revisions"}],"predecessor-version":[{"id":107324,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94885\/revisions\/107324"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94885"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}