I have a database with a lot of data for a client that's on a shared web host. Doing a plain mysqldump is causing the job to get killed on the server, so we were looking at backing up the tables individually.

I've found that the --tab option will automatically create separate files for each table, but my question comes to restoring them while maintaining integrity. This site shows various ways to write shell scripts that loops through the list of backup files and loads them one-by-one. However I am suspicious that this may not guarantee integrity of data.

Is there a more automated way I can lock the database and load the individual tab files, to ensure that I have the database restored to the state it was backed up in?

  • You need to summon @RolandoMySQLDBA – Philᵀᴹ Sep 1 '13 at 23:43
  • If the tables are InnoDB, you need to be using the --single-transaction option with mysqldump ... are they and did you? – Michael - sqlbot Sep 2 '13 at 2:12
  • @Michael-sqlbot yes. – user1359 Sep 2 '13 at 14:59
  • --single-transaction is the correct way to ensure that all of the data in the backup represents all of the InnoDB table data from a single point in time. What do you mean by "causing the job to get killed on the server?" Is mysqldump terminating with an error? – Michael - sqlbot Sep 2 '13 at 21:51
  • @michael, are you familiar with the --tab option? It creates separate files for each table. I'm not worried about the backup, I'm worried about the restore process. I want to ensure that they are inserted in a single transaction so that the database is restored into a consistent state, that no other commands are issued while the restore is happening. The backup runs in a cron script and it uses to much memory or io, which causes it to get killed by the server. – user1359 Sep 3 '13 at 0:17

It doesn't really make sense that you're able to do the backup with the --tab option but not without it, and I think that should be investigated.

Concerns about integrity are generally concerns with the backup, and you should be concerned about the backup, because --single-transaction issues a START TRANSACTION WITH CONSISTENT SNAPSHOT before the backup, ensuring that what gets written to the file represents the state of all the tables at one consistent point in time.

Without this, your "user" table, being alphabetically later than your "history" table could (for example) in the backup file contain a new user but not have caught history of that account's creation, since the backup of "history" finished before the backup of "user" was started.

You say you want to restore within a transaction, but that's not possible (by default) and probably not really what you intend at any rate.

It's not possible, by default, because of the way mysqldump works. Examine a backup file

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `action` (
  `id` int(11) NOT NULL ...

Both CREATE TABLE and DROP TABLE cause an implicit commit. This isn't the only concern, because non-conflicting writes to the tables could still be done by other threads.

There are two other ways to prevent changes on a MySQL server:


This attempts to close all tables and acquire a global lock that prevents any thread from writing. The problem here again in with the standard behavior of mysqldump, and what it writes to the dump file for execution during the restore:

/*!40000 ALTER TABLE `action` DISABLE KEYS */;
/*!40000 ALTER TABLE `action` ENABLE KEYS */;

These individual table locks are not compatible with the global lock:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.16 sec)

mysql> lock tables t1 write;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

You can disable this behavior with --skip-add-locks but then you wouldn't be able to write data into the tables if you had the global read lock because it prevents you from writing as well as other threads... so that one seems to be a non-starter.

Alternately, you could force the entire server to read_only mode.

FLUSH TABLES WITH READ LOCK; -- force a wait until all ongoing writes have finished
SET GLOBAL READ_ONLY = 1;    -- set entire server to read-only except for users with `SUPER` privilege
UNLOCK TABLES;               -- release the global read lock
--- restore backup

If you're concerned with other threads modifying the tables during the restore, but you don't want the entire server to be read-only for the duration of the operation, I only see one really solid option, and that would be to restore all of the tables with an alternate name, and then, in a single SQL statement, rename them all to their correct names.

When multiple tables are renamed in a single statement, the entire statement is atomic.

RENAME TABLE t1 TO t1_old, t1_new to t1;

This acquires a global mutex that prevents t1 from being accessed until all of the renames are complete... but it would require manipulating your dump file to give the tables alternate names, thought it might also work if you restored to an alternate database name and renamed the tables in a fully-qualified form.

Another approach would be to drop and add all of the tables and lock all of them for write, before inserting the data, after removing any LOCK and UNLOCK from the dump file(s). The one additional step you'd need to do, here, would be after acquiring the locks, you'd need to run through every table and delete any data that got inserted by other threads between the time you created the table and acquired the locks, or the restore would fail. SET FOREIGN_KEY_CHECKS = 0 is a session-level setting that would allow your session to freely remove any stray data without regard to foreign key constraints.

The bottom line is that there is not a simple, straightforward way to keep all hands off of the data you are attempting to restore, and if faced with that requirement, I would be inclined to shut down the application server or otherwise prevent access to the server with a mechanism outside of mysql itself, such as IP-level filtering.

  • After you mention it, I realized it's the compression jobs that run afterwards that are getting killed, not the actual dumps themselves. – user1359 Sep 8 '13 at 2:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.