Mysqldump is a client utility that is used to perform logical backups of the MySQL database. This popular migration tool is useful for various use cases of MySQL such as:
- 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 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 2-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 while importing the MySQL table data and in the second part, we will talk about how to handle import for stored program objects and views.
mysqldump Best Practices |
|---|
1. Space requirements
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.
2. Sql_mode
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 which has a date column having entries as zero dates:
mysql> show create table sched; --------------------------------------------------------+ | Table | Create Table | --------------------------------------------------------+ | sched | CREATE TABLE `sched` ( `id` int(11) DEFAULT NULL, `ts` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------- mysql> select * from sched; +------+------------+ | id | ts | +------+------------+ | 1 | 2020-01-12 | | 2 | 0000-00-00 | +------+------------+
Suppose the strict sql_mode (and NO_ZERO_DATE) is disabled on source, but enabled on the destination – restoring such rows will result in failure such as:
ERROR 1292 (22007) at line 40: Incorrect date value: '0000-00-00' for column 'ts’' at row 2
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 following conditional statement as part of the dump:
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
This means that during the restore sql_mode is set to 'NO_AUTO_VALUE_ON_ZERO' before restoring the table data so restore goes through fine.
3. Unique_checks and foreign_key_checks
By default (if you don’t use –compact option), mysqldump also sets the following:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
As explained here, you can speed up 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 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.
Disabling FOREIGN_KEY_CHECKS will also help to avoid errors due to foregin key constraint checks during the restore operation. Whenever a table with foregin key constraint is created, MySQL expects that the parent table which is referred to by the foregin 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:
CREATE TABLE `solution_table` ( `num1` int(11) NOT NULL, `num2` int(11) DEFAULT NULL, PRIMARY KEY (`num1`)); CREATE TABLE `ref_table` ( `key` int(11) DEFAULT NULL, `ref_num` int(11) DEFAULT NULL, KEY `ref_num` (`ref_num`), CONSTRAINT `ref_num_ibfk_1` FOREIGN KEY (`ref_num`) REFERENCES `solution_table` (`num1`) )
The table 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:
ERROR 1215 (HY000) at line 50: Cannot add foreign key constraint -
Which happens while executing the create table statement for ‘ref_table’.
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. Suppose you don’t have SHOW VIEW permissions, then the mysqldump will fail with:
mysqldump: Couldn't execute 'show create table `ivew`': SHOW VIEW command denied to user ‘dumpuser’@'172.31.18.79' for table 'iview' (1142)
Another point of interest is if your dumpuser has SELECT permissions only on a particular table of the database, mysqldump will dump data only for that particular table and automatically ignores any other tables or views.
So please ensure that the user executing mysqldump has all the appropriate privileges upfront in order to avoid any surprises or failures at a later time.
|
5. Max_allowed_packet
The largest communication packet handled by mysql is determined by the setting max_allowed_packet. In the context of import, 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 the error:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `huge1` at row: 2.
So ensure that mysqldump uses the same or bigger value of 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:
ERROR 2006 (HY000) at line 70: MySQL server has gone away
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.
