What is the output of mysqldump?

There are many an instance where you want to export data from your MySQL database using mysqldump to another destination like a Data Warehouse. You may want to integrate this data with that from other sources and perform a holistic analysis or you may want to format and publish your data in the way you want.

If you are a MySQL user looking for a way to export your data effortlessly, you have landed on the right page.

Table of Contents

• What is MySQL?
• What is mysqldump?
• How Do We Use mysqldump?
• What are the Benefits of Backing Up Data?
• Steps to Export Database and Tables Using mysqldump
  • Steps to Export Tables
  • Steps to Export Database
  • Steps to Export MySQL Server
• mysqldump What Does the –quick flag Do?
• mysqldump When Using Lock Tables?
• mysqldump Command Syntax
• How to Generate Backup using mysqldump Utility?
• Generate the Backup of a Single Database
• How to mysqldump Backup Large Database?
  • How to Compress mysqldump Output?
  • How to Import the General MySQL Database?
  • Separate Databases Into Separate Data Files
• How to Use mysqldump Without Password?
• Conclusion

What is MySQL?

MySQL is an open-source relational database management system widely used by startups and established corporations alike. It is a well-known database solution that is battle-tested as it has been used in production for many years by a slew of companies. Naturally, that leads to the question of how MySQL databases can be exported or transferred from one host to another.

MySQL provides you the mysqldump utility tool that enables users to export data by writing simple queries. In this article, you will learn how to export tables, databases, and whole MySQL servers using the mysqldump utility tool.

The information presented in this article will also serve as a reference guide for performing basic backup operations using mysqldump command. This article assumes the user is familiar with database technologies like mysqldump and understands what a terminal or shell is and how to issue terminal commands.

What is mysqldump?

The MySQL database engine has a client utility tool called mysqldump that can be used to perform backups of a MySQL database by exporting a “.sql” file type that contains SQL statements that can be executed to recreate the original database.

In order to use the mysqldump tool, you must have access to a server running an instance of MySQL. You must also have user credentials with the required privileges for the database which you want to export. The mysqldump tool is capable of producing not only .sql output files but CSV, delimited text, and XML.

However, exporting data is not a one-time-only task, and doing it manually can be tedious.

How Do We Use mysqldump?

mysqldump is easy to use and learn. A few basic syntaxes of mysqldump commands are listed below:

mysqldump -u USERNAME -p PASSWORD DBNAME > DBBACKUP.sql

mysqldump -u USERNAME -p PASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql

mysqldump -u USERNAME -p PASSWORD --all-databases > ALLDBBACKUP.sql

where,

What are the Benefits of Backing Up Data?

• The importance of having a pristine snapshot of the copy of your data at different points in time cannot be overemphasized for companies or organizations that have systems in production.
• A database installation that does not have a robust backup strategy is a disaster waiting to happen as any number of things could go wrong that could lead to corrupt data or permanent data loss. To avoid this doomsday scenario, organizations usually have a well-laid-down procedure to carry out a periodic backup of data.
• One of the main benefits of regularly backing up data is the ability to restore data operations in the case of a catastrophic failure of the system. Another benefit is data versioning, which means that specific versions of your data are stored when changes occur so that you can go back in time to restore older versions in order to get the state of your ground truths at that point in time.
• The practice of backing up data also means that in the event of a migration to a new server or development environment, data can be transferred without any fear of loss. This brings us to the crux of this article, where you will be introduced to mysqldump and you will use it to perform logical backups of your data.

Hevo, a No-code Data Pipeline, provides you with a platform to export data from your MySQL database to any data warehouse. It helps you migrate data in real-time and provides state-of-the-art infrastructure. Hevo is fully automated and hence does not require you to code.

Get Started with Hevo for free

Check out some of the cool features of Hevo:

1. Fully Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
2. Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
3. 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
4. Scalable Infrastructure: Hevo has in-built integrations for 100 + sources that can help you scale your data infrastructure as required.
5. Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
6. Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.

Sign up here for a 14-day Free Trial!

Steps to Export Database and Tables Using mysqldump

There are three ways in which the mysqldump tool can be used:

• First, it can be used to export specific tables in a MySQL database.
• Second, it can be used to export databases.
• Third, it can be used to export an entire MySQL server.

In the following sections, you will perform each of those actions.

a] Steps to Export Tables

First, make sure that you are on the machine on which you have MySQL installed. Then make sure you have a valid database user that has at least full read access privileges. Do note that depending on the options that are passed with the command you may require additional privileges to use that particular option.

Download the Whitepaper on Automating Data Integration

Learn the key benefits of automating Data Integration

In most cases where the intention is a basic backup, full read access privileges should suffice. Next launch a terminal where you will issue the command to backup tables in a database. The command to backup tables is shown below:

mysqldump [options] your_db_name [tbl_name ...]

In the command above, [options] is to be replaced by a list of valid option names or flags, the most common of which is -u and -p for the user and password respectively. [tbl_name …] is to be replaced with table names separated by spaces. Below is a full example for backing up the tables orders and products for a database named store.

mysqldump -u your_username -p store orders products > name_of_file.sql

The terminal will prompt you to put in the password associated with the database user as it is not passed with the -p flag. The > character is the output redirection used to create the dump file. 

b] Steps to Export Database

The steps to export a database are similar to those of exporting tables with a little change in the command semantics. Once again, make sure you are on the appropriate server and you have valid credentials. The command to export databases is shown below:

mysqldump -u your_username -p --databases db_name1 db_name2 db_name3 > name_of_file.sql

In the command above, the database to be exported is supplied after the –databases option. If multiple databases are to be exported, they are separated by spaces.

c] Steps to Export MySQL Server

To export an entire MySQL server, you can issue the command below from a terminal:

mysqldump -u your_username -p --all-databases

There are some important options that you should be aware of such as the –compatible option, which is used when you want the export to be compatible with other database systems or older MySQL servers.

If you are using PowerShell on Windows, you should use the –result-file option, for example, –result-file=name_of_file.sql to specify the file name so that the output file is in ASCII format so it can load correctly subsequently.

mysqldump What Does the –quick flag Do?

Copying data from one place to another requires RAM, as it acts as temporary storage or buffer memory for some time. mysqldump also retrieves data and dump it in the table contents row by row. It takes up the entire dataset that needs to be moved in the buffer memory or RAM in a computer.

Exporting large datasets cause problem because RAM cannot load a large amount of data in a single go. To avoid errors and system failures –quick flag helps to read data from tables using a method that doesn’t require RAM to fit large tables in memory

mysqldump When Using Lock Tables?

Databases needed uptime to support all the necessary activities, handle data requests, and many more. By default, mysqldump command locks all the tables until the backup process is complete but it is not a good option as it will bring the Database offline.

mysqldump locks all the tables of the Database to avoid any changes in data while backing up and for the protection of data integrity. MyISAM tables need locking tables because that doesn’t support transactions.

If you also use MyISAM and InnoDB tables then you can use the –lock-tables command to dump your MyISAM from tables separately.

mysqldump Command Syntax

Let’s go through the basics of the mysqldump command before we get into how to use it.

The utility expressions for mysqldump are as follows:

mysqldump [options] > file.sql

• options – The options file for mysqldump.
• sql – The backup [dump] file

The MySQL server must be available and operating in order to use the mysqldump command.

How to Generate backup using mysqldump utility?

mysqldump is a command-line program that creates a logical backup of the MySQL database. It generates SQL statements that can be used to rebuild database objects and data. The program can also provide output in XML, delimited text, or CSV.

The only issue that arises while restoring the database is that this command is simple to use. When we create a backup of the MySQL database, it creates a backup file that contains SQL commands that are required to rebuild or restore the database, as I previously said.

When we restore the database, the command now runs all of the SQL statements necessary to build tables and enter data. The restoration process takes a long time to finish if you have a huge database.

The information schema database, performance schema database, and MySQL Cluster ndbinfo database are not dumped by default when using the mysqldump command.

You must specifically supply the database name in the mysqldump command, as well as the —skip-lock-tables option if you want to include the information schema tables.

With mysqldump, you can use a variety of settings and capabilities. The full list of possibilities can be found here. I’ll go through some of the fundamental aspects. The syntax for the mysqldump utility is as follows.

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

The Parameters are:

• -u [user name]: This is the username that will be used to connect to the MySQL server. To create a backup with mysqldump, select ‘Select’ to dump tables, ‘Show View’ to dump views, and ‘Trigger’ to dump triggers. If the —single-transaction option is not used, the user must be granted ‘Lock Tables’ access.
• –p [password]: The MySQL user’s valid password [option]: Customize the backup
• [database name] with the following configuration
• option: The name of the database you want to back up
• [name of table]: This is a non-mandatory parameter. If you want to take a backup of specific tables, use the command
• “” to provide the names: This character denotes whether we are creating a database backup or restoring a database. “>” can be used to create a backup and “ C:MySQLBackupsakila_20200424.sql

  This command will prompt you for a password when you run it. Please enter the correct password. Consider the following illustration:

  Image Source

  Let’s examine the backup file to see what’s inside it once it’s been successfully made. Double-click the “sakila 20200424.sql” file in the backup directory.

  Image Source

  The backup file, as seen in the image above, contains the numerous T-SQL statements that can be used to recreate the objects.

  How to mysqldump Backup Large Database?

  In this section, you will learn different methods that you can use to backup large datasets using mysqldump.

  • Method 1: How to Compress mysqldump Output?
  • Method 2: How to Import General MySQL Database?
  • Method 3: Separate Databases Into Separate Data Files

  Method 1: How to Compress mysqldump Output?

  1. One can use the file compression method to compress the database backup into gzip format to reduce the size of data to be backup. You can do the same by running the following command given below:

  mysqldump -u root -ppassword wpdb | gzip > wpdb_backup.sql.gz

  1. Here, 2 commands are executed together. One is dumping the database name wpdb and another command is to compress the dumped database into gzip format.

  Method 2: How to Import the General MySQL Database?

  1. Log in to your SQL shell using the command given below.

  mysql -u root -p

  1. Now, set the network buffer length to a large size as given in the code below.

  set global net_buffer_length=1000000;

  1. Set the maximum allowed packet size to a large byte number as given in the code below.

  set global max_allowed_packet=1000000000;

  1. To avoid any delays or errors, disable the foreign key checking by the following command given below.

  SET foreign_key_checks = 0;
  SET UNIQUE_CHECKS = 0;
  SET AUTOCOMMIT = 0;

  1. Now, import your dump file with the command given below.

  source /backup-path/wpdb.sql

  1. After this don’t forget to enable the foreign key checking with the given command given below.

  SET foreign_key_checks = 1;
  SET UNIQUE_CHECKS = 1;
  SET AUTOCOMMIT = 1;

  Method 3: Separate Databases Into Separate Data Files

  1. In this method, one can slit the database backup into separate data files.
  2. You can create a new file with a list of all databases using the following command given below.

  mysql -u root -ppassword -A --skip-column-names -e"SELECT schema_name FROM
  information_schema.schemata WHERE schema_name NOT IN ['information_schema','mysql']" >
  db_list.txt

  1. Now you can create a loop in which all the databases will dump one by one by using mysqldump command given below.

  for DB in `cat db_list.txt`
  do
  mysqldump -u root -ppassword --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
  done
  wait

  How to Use mysqldump Without Password?

  MySQL always asks for username and password when you back up the database with mysqldump command. To avoid repeated prompts and interruptions in backup one can follow this method. 

  1. You have to create a file in your home directory with SQL credentials. Use the given command to create a file.

  nano ~/mysql.txt

  1. Now add your MySQL credentials as given below.

  [mysqldump]
  user=root
  password=password

  1. Save and close the file and every time you can provide a file in place of credentials like in the code given below.

  mysqldump --defaults-file=~/mysql.txt wpdb > wpdb_backup.sql

  Conclusion

  Through this article, you have been able to understand the benefits of performing regular backups of data. You have also been introduced to the mysqldump tool and you used it to export tables, databases, and an entire MySQL server.

  Although this is unarguably a good start as the mysqldump tool is flexible and easy to use, it is not meant to be a fast and scalable backup solution. This is because while backup may take a reasonable time, it typically takes a longer period to restore data, especially in cases where the data size is massive as is often the case with enterprises. To fill this void, MySQL provides MySQL Enterprise Backup which is a paid solution.

  Learning about other methods to export a MySQL database is the next step ahead.

  An even simpler approach will be to leverage an online managed solution like the Hevo platform to transfer your data seamlessly to a data warehouse in real-time.

  Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

  Have you used mysqldump to export data from MySQL? Let us know in the comments below.

  Where do Mysqldump files go?

  The mysqldump tool is located in the root/bin directory of the MySQL installation directory.

  What type of backup is performed by Mysqldump?

  Logical backups are performed with the MySQL server running. The server is not taken offline. Logical backup tools include the mysqldump program and the SELECT ... INTO OUTFILE statement.

  Does Mysqldump drop database?

  In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

  How do I export from Mysqldump?

  Export a MySQL database.

  Log into your server via SSH..

  Use the command cd to navigate to a directory where your user has write access. ... .

  Export the database by executing the following command: mysqldump --add-drop-table -h internal-db.s00000.gridserver.com -u username -p dbname > dbname.sql..