Understanding How mysqldump Works

Databases are critical tools for collecting and storing massive amounts of data. They are used to store information in all complicated applications. There are several database systems available, but MySQL is one of the most used. Using MySQL is critical to keeping these databases secure and working effectively. Managing and backing up servers and databases can all be done in MySQL. Utilities have been built to assist these complicated procedures go easily. Mysqldump is one of those tools designed to make developers' life simpler. In this article, we'll go over what mysqldump is, how to use it, detect some frequent mistakes, and present some clear examples along the way so that you can use mysqldump efficiently.

‍

What is mysqldump?

MySQL is a database system that has been around for a long time and is still one of the most popular alternatives for website hosting. It is open-source and flexible. These databases may be used by developers to store anything that a website may require. The data saved in online databases can range from consumer information and simple text to photo galleries and network information.

mysqldump is a component of the MySQL relational database suite. It is used to save all of the data in a database to a single text file. These files, known as "dumps," can be created for individual databases or groups of databases. The file's text is shown as a series of SQL queries that may be reassembled into its original state afterwards.

This tool's goal is to export a database to a backup file or to migrate the database to another web server. mysqldump may also create delimited text formats such as XML and CSV. These basic queries simplify the backup procedure.

The importance of backing up data

Companies that want to function smoothly must have immaculate copies of their data at various periods in time. There is nothing to safeguard them in the event of a disaster if they do not have a backup system in place.

The simplicity with which data might be permanently lost is too much to bear, as data can simply get damaged or lost over time. Malicious intent and natural calamities are not required for worst-case situations to occur. Keeping backups at regular intervals allows the organization to turn back the clock by reloading the prior database. This serves as a lifeline for the system whenever something breaks or fails. Data versioning is also accessible from the firm.

There are several versions of the database and product to choose from. Critical modifications that later prove to damage the system may be undone, and the previous versions can be restored without issue. By backing up everything, migrations to new servers or development environments can take place without worry of data loss.

How to use mysqldump

A developer can obtain the .sql file that acts as a backup for the complete database by using mysqldump. To use the tool, the developer must have access to the server that hosts the MySQL instance. To export anything, the necessary permissions must be granted. The database user credentials, including the username and password, will also be required.

mysqldump can export data in three ways:

exporting particular tables in a MySQL database

exporting single databases

and exporting the complete MySQL server.

‍

Export 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 minimum- full read access privileges. This should do 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 inputs to conform to the following mysqldump command structure:

mysqldump [options] db_name [table_name ...]

Replace [options] with the permissible option names or flags for the live command. These will almost certainly include -u and -p, which stand for user and password, respectively. MySQL.com contains a thorough table of all the settings supported by mysqldump. When utilizing several options, be mindful of the sequence in which they are listed, since they will be processed from first to last. [table name...] will be substituted with the name of the table you are exporting here. Spaces must be used to separate different tables. The following example is for backing up the tables mystery and cash in the database db cooper.

mysqldump -u username -p db_cooper mystery cash > file_name.sql

Because the -p parameter does not include the password for the database user, you must enter it. The > character denotes the output redirection that will result in the creation of the dump file. The final file will be named file name in this case.

Exporting a MySQL database

The procedures for exporting a database are similar to those for exporting a table. The format of the command has only been slightly altered. You will require the same server access and credentials as before.

Match your inputs to the following mysqldump command structure:

‍

mysqldump -u username -p --databases db_larry db_curly db_moe > file_name.sql

The database to be exported follows the --databases option. Multiple databases are separated by a space character.

Exporting a MySQL server

The command is very similar for entire servers as well.

Match your inputs to conform to the following mysqldump command structure:

mysqldump -u username -p --all-databases

The command is straightforward, with --all-databases specifying that everything on the server should be dumped. If there are particular criteria, the command's options come into play. Including -compatible makes the output file compatible with older MySQL servers or database systems.

Developers using PowerShell on Windows must include -result-file as an option. This will define the file name and ensure that the output is in ASCII format so that it can be loaded later.

Other frequent options include adding --no-data, which will just back up the database structure, and using --no-create-info, which will back up the database without any structure.

Importing a mysqldump

Importing a .sql file is straightforward. The only kink is to make sure the target server has a blank database before importing anything (check our mini guide on how to import SQL files).

Match your inputs to conform to the following mysqldump command structure:

mysql -u root -pmypassword wpdb < db_backup.sql

The mysqlimport command will also work on databases you want to restore that already exist on the target machine:

mysqlimport -u root -pmypassword wpdb < wpdb_backup.sql

You also have the option to import all databases by running a command that looks like this:

mysql -u root -pmypassword < alldb_backup.sql

What does the --quick flag do?

mysqldump can operate in one of two ways.

1. The tool can go grab all data at once, buffer it in memory, and then dump it.
2. It dumps the tables row by row.

When working with enormous tables, the second way comes in handy. When the --quick flag is used, mysqldump reads huge databases without requiring a significant amount of RAM to fit the entire table into memory.

This guarantees that the databases are read and copied successfully on computers with limited RAM and massive data sets.

Dump without locking tables and the --skip-lock-tables flag

Using —skip-lock-tables avoid table locking throughout the dump operation. This is critical when backing up a production database since you cannot lock it down during the dump. It is generally suggested to use --skip-lock-tables for dumping InnoDB tables. However, with MyISAM tables, you may need to lock tables for data consistency. 

So when should you use --skip-lock-tables?

• Yes, if you're backing up InnoDB tables. For optimum results, combine it with --single-transaction.
• Do not use --skip-lock-tables on a production server, especially when backing up MyISAM tables, unless you can keep your database tables locked during the backup process.

mysqldump -u root -pmypassword my_database --skip-lock-tables > my_database.sql

What does the --single-transaction flag do?

In summary, the --single-transaction option allows MySQL InnoDB dumps to have a consistent database state. It informs MySQL that we are about to dump the database, thus breaking changes such as table structure queries will be disallowed in order to maintain data consistency.

Note that this only applies for

InnoDB tables.mysqldump -u root -pmypassword my_database --single-transaction > my_database.sql 

Note: MyISAM tables will not benefit from this flag and should be locked if you want to preserve their dump integrity.

In summary, the --single-transaction option allows MySQL InnoDB dumps to have a consistent database state. It informs MySQL that we are about to dump the database, thus breaking changes such as table structure queries will be disallowed in order to maintain data consistency.

MyISAM tables will not benefit from this flag and should be locked if you want to protect their dump integrity.

mysqldump -u root -pmypassword my database --single-transaction > my database.sql

How to dump large tables?

To dump large tables, you could combine the following two flags, --single-transaction and --quick. Here's how it's going to look:

mysqldump -u root -pmypassword my_large_db --single-transaction --quick > my_large_db.sql

This is very useful for InnoDB tables. Because it uses less RAM and produces consistent dumps without locking tables.

‍

How to ignore tables using mysqldump?

When using mysqldump, you may ignore a table by using the --ignore-table option.

Here's an example that would only enable you to ignore one table:

‍

mysqldump -u root -pmypassword my_db –-ignore-table=my_db.table_to_ignore > my_db.sql

As you have seen, the format is as follows:

 –-ignore-table=DATABASE_NAME.TABLE_TO_IGNORE.

To disregard all tables in a database (or an entire database when emptying all your databases), you must repeat the option.

mysqldump -u root -pmypassword –-ignore-table="my_db.table1" –-ignore-table="my_db.table2" –-ignore-table="my_db.table3" > all_databases.sql

How to dump binary BLOB data?

If the resultant dump contains binary data, you may have problems. As a result, when you dump a MySQL database containing binary data, you could use the mysqldump flag --hex-blob. Under the hood, it dumps the binary strings it finds (BINARY, VARBINARY, BLOB) in a hexadecimal format that represents these data structures in a reliable way.

Here is a mysqldump example to dump binary data correctly:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

Does the "where" clause work with mysqldump?

Yes, this clause is compatible with the command line. This makes it simple to provide the parameters for the data you want to dump from the database. If a huge firm that has been in operation for decades wishes to remove the data after July 17, 2021, this provision permits them to do so. The where clause accepts a string as the criteria and retrieves the desired records.

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2021-07-07'" > wpdb_myrecord.sql

Troubleshooting common errors

And while you're going through all the motions, it is inevitable that you might encounter MySQL common errors that are, to some extent, easily mitigated.

We'll go over some of these mistakes and how to fix them below.

Error 2013: lost connection to MySQL server during query when dumping table

To resolve this issue, edit the MySQL configuration file and raise some settings. After you've added those, save and close the file, then restart MySQL to see the changes take effect.

The values you need to adjust are:

• max_allowed_packet
• net_write_timeout
• net_read_timeout
• innodb_buffer_pool_size

The adjustments to the file will be under the [mysqld] and [mysqldump] sections and will look like this:

[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
[mysqldump]max_allowed_packet=1024
Mnet_read_timeout=3600
net_write_timeout=3600

Error 2020: got packet bigger than 'max_allowed_packet' bytes when dumping table

This error appears if the database to be backed up is huge and the file size exceeds the maximum allowable packet size.

This problem may be resolved by editing the MySQL configuration file and raising the value of max allowed packet in the [mysqld] and [mysqldump] sections. When completed, save and quit the file, then restart MySQL for the changes to take effect.

The modifications will look like this:

[mysqld]
max_allowed_packet=desired-value
[mysqldump]max_allowed_packet=desired-value

The table does not exist (1146), couldn't execute 'show create table x'

It is possible that you will delete a table when backing up. If this is the case, you can use the --ignore-table option to exclude certain tables from the mysqldump command. To identify the table, you will have to give both the database and table names.

mysqldump -u root -pmypassword example_db --ignore-table=name_of_table > db_backup.sql

By listing the option multiple times, you can ignore multiple tables:

mysqldump -u root -pmypassword example_db --ignore-table=table --ignore-table=tableaux > db_backup.sql

Selecting the database returns 'unknown database'

This problem occurs most frequently when the -p flag is used in the command line with the password and there is a space between -p and mypassword. If this happens when using "root" as the user with a password of "base-face", there will be an error stating "Unknown database base-face."

The right input would be as follows:

mysqldump -u root -pbase-face wpdb > wpdb_backup.sql

Error 1044 when selecting the database

This error happens if the user attempting the dump does not have the requisite database access privileges. Logging into MySQL and granting those rights to the user will resolve the problem.
Enter the following command:

mysql -u root -p

Then input the proper password and proceed to grant the selected user rights.

GRANT ALL PRIVILEGES ON wpdb.* TO 'definitely_not_a_hackerman'@'guilible_host';

After that, flush the privileges and exit from MySQL by entering the command:

FLUSH PRIVILEGES;

EXIT;

‍

Access denied for user when trying to connect to mysqldump

There are various probable causes for this issue. Here are three of the most typical reasons for the problem.

Wrong mysqldump command

This error will display if you use the incorrect command. The command is generally accurate, but it lacks a vital component of the mysqldump format. The fundamental command will be as follows:

mysqldump -u user -pmypasword database > database.sql

If you do not provide a username or password, the following message will be shown:

mysqldump: Got error: 1045: "Access denied for user 'user' @ 'localhost' (using password: NO)"when trying to connect

Remote host not allowed to connect to database

This issue occurs when attempting to back up to a remote server. MySQL's settings are set to prevent external connections. Only the localhost is permitted to do backups in this case. This is a security measure, so it's a good thing to have, but if you need to modify it, go to settings and adjust MySQL to allow connections from a remote host.

Wrong user credentials

This error occurs if you attempt to connect to the database with the incorrect username and password combination. MySQL is unable to validate the request and returns an error. You'll have to re-submit the request with valid credentials; ensure sure there are no mistakes in your initial command since that is the most common error.

Conclusion

MySQL Dump is a powerful tool which was developed to simplify and automate the backing up of MySQL databases. It is still not as popular as it could be, so you may have never heard of it before. Despite this fact, there are many people who use this tool every day and know how to use it really well. If you have experienced issues with MySQL backup and restore, the mysqldump guide may help you fix these issues. While complex in nature, you will see that MySQL dump is very easy to understand if you simply follow the steps above; and even easier to utilize once you already start using it on your projects.

‍