mysqldump Best Practices (Part 1): MySQL Prerequisites
What do you need to set up a successful mysqldump? Let's take a look.
Join the DZone community and get the full member experience.Join For Free
- Backup and restore of databases.
- Migrating data from one server to another.
- Migrating data across different managed MySQL service providers.
- Migrating data between different versions of MySQL.
mysqldump works by reading the source database objects and generating a set of SQL statements that are stored in a dump file. By replaying these statements on the destination database server, the original data is reconstructed. Since this model uses a reading of the whole database and then essentially rebuilding, both dump and restore are time-consuming operations for a large database. The process might even turn cumbersome if you encounter errors during either dump or restore as it may lead you to fix the issues and re-run the operations. This is why it's important to plan well before you take up the dump and restore activity.
In this two-part blog series, we discuss some of the common aspects you should handle upfront to ensure a successful dump and restore activity. In the first part, we focus on the prerequisites you need to take care of while importing the MySQL table data and in the second part, we will talk about how to handle imports for stored-program objects and views.
First off, it's important to ensure that your destination database volume has sufficient space to hold the imported data. Specifically, you need to be cautious if binary logs are enabled on your destination MySQL database, as binary logs generated while importing the data might take almost equal size as the data itself. Binary logs are needed if you want to restore your data on one server and want that to be replicated. In such cases, it's a good idea to plan the destination size greater than twice the size of the source database.
It is also important to ensure sufficient space is available on the volume where you generate the mysqldump output file. Without these precautions, you may see your dump or restore failing due to insufficient space after running for a long time which is a loss of your productive time and effort.
sql_mode settings for MySQL server determine the SQL statement syntax and data validation checks that the server performs for the operations. It is important to ensure the
sql_mode of source and destination MySQL servers are compatible with each other, or you may encounter failures while restoring the dump you have taken. Let's demonstrate this with an example.
Say you have a table on your source that has a date column having entries as zero dates:
Suppose the strict
NO_ZERO_DATE) is disabled on the source, but enabled on the destination — restoring such rows will result in failure, such as:
You will typically see such issues if you are taking a compact dump by enabling the compact option as part of your mysqldump.
If compact is disabled (which is by default) then you will not face this issue, as mysqldump generates the following conditional statement as part of the dump:
This means that during the restore
sql_mode is set to
'NO_AUTO_VALUE_ON_ZERO' before restoring the table data, so the restore goes through fine.
3. Unique_checks and foreign_key_checks
By default (if you don't use the --compact option), mysqldump also sets the following:
As explained here, you can speed up the restore operation by temporarily turning off the uniqueness checks during the session. For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch.
If you have
FOREIGN KEY constraints in your tables, you can speed up table the restore operation by turning off the foreign key checks for the duration of the restore session: For big tables, this can save a lot of disk I/O.
FOREIGN_KEY_CHECKS will also help to avoid errors due to foreign key constraint checks during the restore operation. Whenever a table with a foreign key constraint is created, MySQL expects that the parent table which is referred to by the foreign key already exists. This is a problem, since mysqldump utility dumps the tables in alphabetical order. Let’s take an example to demonstrate this.
On the source database, we have two tables:
ref_table has a foreign key constraint that references the
solution_table. Based on the alphabetical order, mysqldump first dumps the contents of
ref_table. When this is replayed at the time of restore, it will fail with the error:
Which happens while executing the create table statement for
In summary, be aware of the issues you may encounter, if you specify
--compact option while running mysqldump.
4. Privileges Required for Running mysqldump
The minimum privilege required by mysqldump for dumping a database is
SELECT on that database.
However, if your database has views, you will need SHOW VIEW permissions as well, as mysqldump always dumps views along with the tables of the database. If you don't have
SHOW VIEW permissions, then the mysqldump will fail with:
Another point of interest is if your dumpuser has
SELECT permissions only on a particular table of the database. The mysqldump will dump data only for that particular table and automatically ignore any other tables or views.
So please ensure that the user executing mysqldump has all the appropriate privileges up front in order to avoid any surprises or failures at a later time.
The largest communication packet handled by MySQL is determined by the setting
max_allowed_packet. In the context of imports, a communication packet is a single SQL statement sent to the MySQL server during the restore OR a single row that is sent to the client during the dump.
The default value of
max_allowed_packet for mysqldump is 24MB. If mysqldump receives a packet larger than this, then you may run into this error:
So ensure that mysqldump uses the same value (or bigger) as
max_allowed_packet that is configured on the source MySQL instance.
The option can be specified with the flag
--max-allowed-packet=value when invoking the mysqldump.
When restoring the dump, ensure that
max_allowed_packet size of your destination server is big enough to receive the packets from the dump file.
Otherwise, during restore of the dump, you will see an error message:
This error can be a little misleading, as you may think that the MySQL server has shut down or crashed. But, it just means that the server has received a bigger sized packet than its configured size of
max_allowed_packet. Again, the best practice is to ensure that the
max_allowed_packet value for your destination server is the same as the value in the source server. This is also an important setting that can be checked and set appropriately upfront, rather than facing the errors at a later time.
In this first part of the mysqldump series, we discussed prerequisites for a successful dump and restore operation for large MySQL databases in order to help you avoid multiple attempts and unproductive time spent.
In the next part, we will discuss best practices to import the stored programs and views from your MySQL database.
Published at DZone with permission of Anton Bjorkman. See the original article here.
Opinions expressed by DZone contributors are their own.