MysqlDump: Lock wait timeout exceeded; try restarting transaction

You are here because you got the following error while performing a MysqlDump: Lock wait timeout exceeded; try restarting transaction. What does this error mean and how can you get MysqlDump working again? You have come to the right place to get your answer.

Transactions

The error indicates that the mysqldump timed out while waiting for a transaction to complete. This error shows up when dumping from Innodb tables which support transactions. Transactions are the process by which the database ensures that all the data is properly modified in a table before it moves on to the next data change.

Think of a transaction in the database just like checking out at a grocery store. When you approach the cash register there is typically a line of other customers waiting to check out. The Cashier takes each customer in order and helps them purchase their items. When they have finished their purchase their transaction is complete. And the next customer in line is able to start their transaction.

So what is the Lock Wait Timeout

So when you get the error from MysqlDump: Lock wait timeout exceeded; try restarting transaction, this is why. MysqlDump is attempting to dump the information in the database/table. But to ensure the data is accurate it is attempting to lock the table during the process. This lock stops new transactions from starting and ensures that all previous transactions are complete.

If the lock attempt is timing out, that typically indicates that either the queue for changes was too long. Or a transaction took too long to complete. In either case the MysqlDump process was unable to get a complete copy of all the data. So the process errors out with “Lock wait timeout exceeded; try restarting transaction” and fails.

This error can be especially common when using MysqlDump in a cluster environment. Or on a very busy server with lots of transactions queued up.

How do I avoid Lock wait timeout exceeded errors?

The answer is a simple MysqlDump option that avoids the lock wait process altogether. I have previously briefly described the process in this post about how to Dump All MySQL Databases into Individual SQL Files. Use this MysqlDump option to avoid those errors.

--single-transaction

This simple command option tells MysqlDump to not wait for any table locks. Instead it will choose the most recent completed transaction for the table and export the data at that point. So you may not have the most 100% accurate data. Since transactions are still processing in the database. But you will have data that is 99.999999% accurate, just missing those latest changes.

This is super helpful in my environment taking periodic dumps of my databases at regular intervals. It is much more important that the data is backed up regularly than I have every change up to that second. If there was something changing at the time of the backup it will be changed in the next one. And if the MysqlDump process is failing because of too many changes, then I get no backup at all.

Good luck with your next MysqlDump process. Hopefully you get a good backup and avoid all the lock timeout errors 😉

Getting Fancy with Mysqldump Tricks

Mysqldump tricks help you get the most out of this amazing and essential tool. It is typically used for backing up and migrating MySQL databases and data. But sometimes you may want to get a little fancier then just backing up or restoring a full database. Here are a few Mysqldump tricks to maximize your effectiveness.

Mysqldump Tricks – Dump a single MySQL table

If you want to dump a single table from a MySQL database you can. The following command will help you accomplish this amazing feat:

mysqldump [options] db_name table_name > filename.sql

And just like that you have a file with just that table exported into it. But what if you want to dump more than one table, but not the whole DB. The following command will help you get there:

mysqldump [options] db_name table1_name [table2_name table3_name ...] > filename.sql

Simply adding the table names with spaces between each name will add them to the export file.

Skip specific tables in a DB with Mysqldump

Now that we know how to export only specific tables from a DB. How can we use Mysqldump to export a full DB but exclude one or more tables? Use the following command for a single table:

mysqldump [options] db_name --ignore-table=db_name.table1_name > filename.sql

Or if you want to exclude multiple tables:

mysqldump [options] db_name --ignore-table=db_name.table1_name --ignore-table=db_name.table2_name --ignore-table=db_name.table3_name > filename.sql

Now your Mysqldump powers are increasing. Now let’s move on to something a bit different.

Restore a single table from a Mysqldump File

So you already have a Mysqldump file with multiple tables in it, or a full DB dump. But you only want to restore a single table from the file. Ok, so this isn’t exactly a Mysqldump trick, it’s a Sed trick, but just using a Mysqldump file.

Let’s say you have a Mysqldump file called “filename.sql” and you want to restore only the table named “myFavoriteTable”. Using the command below Sed will copy the correct table contents into the file “myFavoriteTable.sql” so you can restore that file/table individually.

sed -n -e '/CREATE TABLE.*`myFavoriteTable`/,/Table structure for table/p' filename.sql > myFavoriteTable.sql

Now you have several new Mysqldump tricks up your sleeve. Hopefully they save you time and effort in the future.

If you would like to learn how to use compression with mysqldump check out this post.

How To Compress Mysqldump Output

if you read my previous writeup on dumping all mysql databases you will recognize some of this information. I wanted to pay some specific attention to some of the different methods for how to compress mysqldump output.

Obviously compressing your mysql databased exports can have some major benefits. The biggest benefit is the smallness of the file size. Mysql databases and really all databases have the tendency to grow to large sizes. Even small websites can quickly find hundreds of megabytes worth of data in their database. Storing large database export files in your backup can eat up disk space pretty rapidly. Compressing your mysql output can reduce the size of your export file by seven or more times.

If you need to keep individual database backups then compression really makes sense. But if you are using something like rdiff-backup then it makes more sense to skip the compression. Rdiff-backup is unable to do a diff on the compressed data, so it won’t save the space you expect.

Basic Mysqldump Compression Commands

Here are a couple different variations of mysqldump piped compression commands which we will breakdown.

1: mysqldump -u dbUser -p DBName > OutputFile.sql
2: mysqldump -u dbUser -p DBName | gzip > OutputFile.sql.gz
3: mysqldump -u dbUser -p DBName | gzip -9 > OutputFile.sql.gz
4: mysqldump -u dbUser -p DBName | zip > OutputFile.sql.zip
5: mysqldump -u dbUser -p DBName | bzip2 > OutputFile.sql.bz2

In these examples we see the same database being exported in each command. But there are a couple differences, in #1 we are employing no compression. Command #2 is using gzip with its default settings. Then command #3 is utilizing gzip with maximum compression. Command #4 is using zip to perform its compression. And finally command #4 is using bzip2 to perform its compression.

Compression Commands Comparison

Testing the commands above on the same database and on the same hardware yielded the following results.

CommandFilesizeOutput Time
#1391MB13.827s
#257MB16.122s
#355MB32.357s
#457MB16.169s
#544MB1m 18.701s
Output Mysql Database command results

The table above shows the effectiveness of each compression method on the same dataset. The first command sets the baseline for data export with no compression. Gzip applies basic compression and gives a significant size reduction with a very small speed hit. It comes in just a hair faster than zip with about the same compression results.

Adding the -9 to the Gzip command in #3 doubles the output time, and only provides 2MB of space savings. But then Bzip2 weighs in on command #5 taking an extra minute over Gzip or Zip. That extra minute was required to pack the file small enough to rescue another 13MB of space.

Compress Mysqldump Output Conclusions

If you can compress your database output, then you will see significant space savings in your backup storage. Even if backup speed is essential, gzip or zip offer a major reduction in size for minimal extra time. And if time is not a major issue then going with bzip2 will give you much larger space savings in exchange.

Understanding and utilizing compression as part of your backup methodology is an essential element for storage success. Proper implementation can ensure that you save the needed space and reduce backup transfer time. Especially in the event that you need to transfer your backup over a slow network connection. Compression will come to your aid and save the day. So don’t hesitate to compress mysqldump output, it might be just what the doctor ordered.

Further Reading

For additional details and info check out this post which talks more about Compressing Mysqldump Output

Dump All MySQL Databases into Individual SQL Files

Anyone who is responsible for managing a MySQL database will eventually run into this problem. You either need to dump all your MySQL databases for a backup, or to prepare for an upgrade. Whatever your circumstances are there are several different methods that can be employed to dump your MySQL DBs. Hopefully I can give you the basic tools to get you on your way.

Dump All DBs into a Single SQL File

This first method is what I would call the quick and dirty method. It is straight forward and just dumps all of the databases on a server into a single SQL file. For many individuals this is sufficient, but may not be a good option for larger databases or backup/restore processes. Since all of the DBs are bundled into a single file. Each of the values in brackets “[]” are placeholders for your own values.

mysqldump -u [username] -p --all-databases > allDB.sql

If all you need is to get a quick backup of everything this may be your ticket. The “–all-databases” flag does the magic here, dumping all of the MySQL databases into a single SQL file. More details on this method can be found here. But if you are wanting to be able to easily restore an individual database you may want to use an approach like this.

Dump All MySQL Databases into Individual Files

for I in $(mysql -u [username] -p[mypassword] -h [Hostname/IP] -e 'show databases' -s --skip-column-names); do mysqldump -u [username] -p[mypassword] -h [Hostname/IP] $I > "/home/user/$I.sql"; done

This command first calls “mysql” and gets a list of databases on the server. The command feeds that list of MySQL databases into “mysqldump” to get an individual SQL file for each database on the server. Finally those SQL files are then saved in the location indicated, “/home/user/dbname.sql” in this example.

A few things to note in this command are that first you will notice that I have included the password in the command. The command will complain that using the password on the command line is not safe. However it is required for the command to work. The “-p” is immediately followed by the password, without a space. This is how the option functions, if you add a space it will not work.

The “$I” is a variable and it will have all the database names in it, as it iterates through your listing of DBs. So as you modify the command to fit your specific setup, just make sure to keep that for consistency.

Additional Mysqldump Options

There are a couple of additional mysqldump options that you may want to add depending on your requirements.

--single-transaction

This option keeps mysqldump from trying to get a complete lock on the database. It tells mysqldump to just grab a single transaction and dump the DB contents at the time of that transaction. This can be especially helpful when you are dumping a DB that is especially large, or on a very busy server. Without this option mysqldump may timeout while waiting to get a lock on the DB. For more details on how this option works check out this post.

--default-character-set=utf8mb4 

If you are working with data that may contain emoticons you will want this flag. This ensures that your dumped sql file has the correct information to recreate the emoticons when the file gets reimported. Without this option, your emoticons will show up as strange/random text characters when you restore your backup.

Adding Compression

I don’t typically use compression on my SQL dumps since I like to use rdiff-backup as my backup mechanism. But for those who would like to compress their mysqldump in one step here is the basic gist of it.

mysqldump -u [username] -p --all-databases | gzip > allDB.sql.gz

You just pipe the output from the mysqldump command into gzip, or bzip2 to compress the contents. That can be easily added to the command above to dump all MySQL databases into individual files like so.

for I in $(mysql -u [username] -p[mypassword] -h [Hostname/IP] -e 'show databases' -s --skip-column-names); do mysqldump -u [username] -p[mypassword] -h [Hostname/IP] $I | gzip > "/home/user/$I.sql.gz"; done

Hopefully these examples help you get the backups you need. Have some fun along the way.