Databases are essential tools for collecting and storing mountains of data. All complex applications use them to store information. There are many database solutions out there, but MySQL is one of the most popular. Using MySQL is vital to keep these databases safe and running smoothly.
Administration and backup of servers and databases can be done in MySQL.To help these complex processes run smoothly, utilities have been developed. Mysqldump is one such tool meant to make developers’ lives easier.
In this guide, we’ll walk you through what mysqldump is, how to use it, identify some common mistakes, and provide some clear examples along the way so you can use mysqldump effectively.
Are you looking for a simple service to manage your MySQL backups?→ Try SimpleBackups for free
Table of
ContentsWhat is mysqldump?
MySQL is a database system that has been around for years and is still one of the most popular choices for websites. It is open source and agile. Developers can use these databases to store anything a website might need. The information stored in online databases can range from consumer information and plain text to image galleries and network information.
Mysqldump is part of the relational database package for MySQL. Used to back up all data in a database to a single text file. These files or “dumps” can be generated for individual databases or a collection of them. The text in the file is displayed as a set of SQL statements that can later be rebuilt in their original state.
The purpose of this tool is to export a database to a backup file or move the database to another web host. Other delimited text formats such as XML and CSV can also be generated with mysqldump. These simple queries make the backup process easier.
The importance of backing up
data
Businesses that expect to run smoothly need pristine copies of their data at different times. Without a backup strategy, there is nothing to protect them in the event of a disaster. The ease with which data can be lost forever is too much to cope with, as data can easily get corrupted or lost over time. Malicious intent and natural disasters are not a requirement for worst-case scenarios to occur.
Having backups at regular intervals gives the company the ability to rewind the clock by reloading the old database. If something breaks or fails, this acts as a lifeline for the system. The company also has data versions available. There are different versions of the database and product available to return. Critical changes that later prove to break the system can be undone, then you can restore previous versions without the problem.
By backing everything up, migrations to new servers or development environments occur without fear of data loss.
How
to use mysqldump
By using mysqldump, a developer can get the .sql file that serves as a backup for the entire database. To use the tool, the developer needs access to the server running the MySQL instance. The necessary privileges must be granted to export anything. User credentials will also be required for the database, including user name and password.
The three ways mysqldump is used to export data include:
Exporting specific tables in a MySQL database Exporting
- individual databases
- server
Exporting the entire MySQL
Exporting MySQL tables via mysqldump Make sure
you are on a machine that has MySQL installed. You will also need a valid database user with at least full read access privileges. This should work for basic options, but more advanced commands may require additional privileges. With that in order, launch a terminal where you will send the command to back up the tables.
Match your entries to fit the following mysqldump command structure:
For the live command, replace [options] with valid option names or flags. These will most likely include -you and -p, which stands for username and password. MySQL.com has a complete table of all the options that MySQLDUMP supports. When using more than one option, be careful about the order in which they are listed because they will be processed in order from first to last. Here, [table_name…] will be replaced by the name of the table you are exporting. The different tables must be separated by spaces. The following example is for backing up tables named mystery and cash, and the database name is db_cooper.
It will then provide the password for the database user because it is not passed along with the -p flag. The > character indicates the output redirection that the dump file will create. Here, file_name is what the final file will be called.
Exporting
a MySQL database
The steps to export a database are very similar to exporting a table. There is only a small change in the format of the command. You will need the same server access and credentials.
Match your entries to fit the following mysqldump command structure:
The database you will export comes after the -databases option. The space character separates multiple databases.
Exporting a MySQL
server The command is
also very similar for full servers
.
Match your entries to fit the following mysqldump command structure:
The command itself is pretty basic, with -all-databases indicating that everything on the server needs to be dumped. If there are specific requirements, that’s where the options for the command come in. Add -compatible will make the file being exported compatible with MySQL servers or older database systems.
Developers using PowerShell on Windows should include -result-file as an option. This will specify the name of the file and ensure that the output is in ASCII format so that it loads correctly later.
Other common options include adding -no-data will only back up the database structure, using -no-create-info backs up the database without any structure.
Import a
mysqldump Importing a
.sql file is simple. The only problem is making sure the destination server has a blank database before importing anything (see our mini guide on how to import SQL files).
Match your entries to fit
the following mysqldump command structure:
The mysqlimport command will also work on databases that you want to restore that already exist on the destination computer
:You
also have the option to import all databases by running a command similar to the following:
What does the -quick prompt do?
Mysqldump can operate in two ways.
- The tool can take all the data at once, store it in memory, and then dump it.
- Dump the tables row by row.
The second method is important when it comes to large tables. By using the -quick flag, mysqldump reads large databases without requiring large amounts of RAM to fit the entire table in memory. This ensures that databases will be read and copied correctly on systems with small amounts of RAM and large data sets.
Dump without locking tables and the
-skip-lock-tables flag
Using -skip-lock-tables prevents tables from locking during the dump process. This is important when backing up a production database that cannot be locked during dumping.
In general, it is recommended to use -skip-lock-tables whenever you are dumping InnoDB tables. However, for MyISAM tables, you might need to lock the tables for the sake of data consistency.
So, should I use -skip-lock-tables?
- If you are backing up InnoDB tables, yes. Combine it with -single-transaction for best results.
- MyISAM tables to a production server, do not use -skip-lock-tables unless you can keep the database tables locked during the backup process.
If you are backing up
What does the -single-transaction indicator do?
In short, the single transaction allows MySQL InnoDB dumps to have a consistent database state. It tells MySQL that we are about to dump the database, therefore break changes like table structure queries will be locked to preserve data consistency. Note that this only applies to InnoDB tables.
Note: MyISAM tables will not benefit from this flag and must be locked if you want to preserve their dump integrity
. How to dump large tables
?
To dump large tables, you can combine the following two indicators, -single-transaction and -quick
.
Note: This is ideal for InnoDB tables. Since it will use less RAM and also produce consistent dumps without locking tables.
How to ignore tables using mysqldump?
With the -ignore-table option, you can ignore a table when you use mysqldump.
Here is an example that will only allow you to ignore a
table:
As you can see, the format is as follows: -ignore-table = DATABASE_NAME. TABLE_TO_IGNORE.
To skip all tables in a database
(or an entire database when you dump all databases), you must repeat the argument to include all tables that you want to ignore
. How to dump binary data
from blobs?
Sometimes you may have problems with the resulting dump if you have binary data. For this reason, you can use the following mysqldump -hex-blob flag when dumping a MySQL database with binary data.
Under the hood, it dumps the binary strings it finds (BINARY, VARBINARY, BLOB) into a hexadecimal format that represents this data structure in a reliable way
.
Here is an example of mysqldump to dump binary data correctly:
Does the “where” clause work with mysqldump?
Yes, this clause works with the command line. This makes it easier to set conditions on the data you need to dump from the database. If there is a large company that has been in business for decades that wants to extract the information after April 27, 2017, then this clause allows that to happen. The where clause passes a string for the condition and takes the specific records requested.
Troubleshooting
Common Errors
Along the way, you may face some common MySQL errors that are, to some extent, easy to mitigate. We will share below some of these errors and how to resolve them.
Error 2013: Lost connection to mysql server during query when dumping table
To fix this problem, you need to enter the MySQL configuration file and increase some values. When they are added, save and close the file, then restart MySQL for the changes to take effect.
The
values you need to adjust are
:max_allowed_packet
- net_write_timeout
- net_read_timeout
- innodb_buffer_pool_size
The settings in the file will be in the [mysqld] and [mysqldump] sections and will look like this:Error 2020: You have a packet larger than ‘max_allowed_packet
‘ bytes when you dump the table
If the database you need to back up is large, and the file size ends up being larger than the maximum allowed package size, this error appears.
This error can be corrected by entering the MySQL configuration file and increasing max_allowed_packet value in the [mysqld] and [mysqldump] sections. Save and close the file when you’re done, then restart MySQL for the changes to take effect.
The
changes will look like this:The
table
does not exist (1146), could not run ‘show create table x’There may
be times when you delete a table during backup. If this is the case, you can restrict certain tables from the mysqldump command with the -ignore-table option. To identify the table, you must enter the names of the database and the table.
When
listing the option multiple times, you can ignore multiple tables:Selecting
the database
returns ‘unknown database’This
error occurs most often when using the -p flag on the command line with the password and there is a space between -p and mypassword. If this happens when using “root” as a user with a “base-face” password, there will be an error stating “Unknown base-face database”.
The correct entry would look like this:
Error 1044 when selecting
the database
If the user trying to perform the dump does not have the necessary privileges to access the database, this error occurs. Logging into MySQL and assigning those privileges to the user will fix the problem. Enter
the command:
Then enter the correct password and proceed to grant
privileges to the selected user.
After that, empty the privileges and exit MySQL by entering the command:
Access denied for user when trying to connect to mysqldump
This error has several possible causes. Here are three of the most common causes of the problem.
Incorrect mysqldump
command
If you are using the wrong command, you will receive this error. The command may be mostly correct, but it is missing a critical ingredient in the mysqldump format. The basic command will look like this
:
If you do not specify a user name or password, it will spit out the following message:
The remote host cannot connect
to the database
This error appears if the backup is attempting to perform on a remote server. The configurations for MySQL are configured to not allow external connections. Here, the localhost is the only one authorized to make a backup. This is a security measure, so it’s a good measure, but if you need to change this, go to settings and change MySQL to allow connections from a remote host.
Incorrect
user credentials
If you try to use the incorrect username and password combination when connecting to the database, this error occurs. MySQL cannot verify that the request is authentic and returns the error. You will have to make the request again with the proper credentials, make sure there are no typos in your original command as that is the easiest mistake to make.
Conclusion
Mysqldump is a useful tool to help backup databases with minimal commands. A command allows you to spit the entire database into a single text file. The tool is versatile enough to back up the parts of the database that are needed and comes with a variety of options to change the data you need to save.
Automated MySQL backups as a service
SimpleBackups is a database and website backup automation tool that offloads all backup tasks. It automates MySQL backups and sends them to the cloud storage of your choice. SimpleBackups helps you make sure your backups run well, on time, securely, with all the bells and whistles.
→ Set up your first MySQL backup for free