Not sure if the specifics matter but just to get it out of the way I am talking about a Galera cluster on XtraDB (MariaDB).

I have a master-master setup and am interested in backing up and restoring the database if necessary. On a single instance of MySQL I do the run of the mill

 mysqldump -u USER -p DB_NAME > DB-DATE.sql

 -and to restore-

 mysql -u USER -p < /path/to/dump.sql

I am not sure how these commands will fare in a clustered environment. I'm going to divide this into two parts with my comments in each:

Backing up

The only issue I can think of as far as backing up goes is during the backup process some data could be written to the cluster, a query happens, my backup runs, then another related query happens leaving my data in an inconsistent state. Let's say I have two tables users and counts. When a user submits a form a PHP script updates the users favorite color in users as well as increments a field in counts called times_color_changed. This simple script may look like:

    //Yes, these are vulnerable to SQL injection but this is to demonstrate a point.
    $query1 = "UPDATE `users` SET `favorite_color` = ".$_POST['color']." WHERE id = $user_id";
    $query2 = "UPDATE `counts` SET `times_color_changed`  = `times_color_changed` + 1 WHERE id = $user_id";

My worry is that while running the mysqldump in parallel the queries run as follows:

 QUERY_1 runs
 Query_2 runs


 Query1 from remote server commits on local server
 Query2 from remote server commits on local server

Either of these can lead to an inconstant state. I could always stop the SQL server but then I can't run MySQL dump.

There is an xtrabackup command but I'm not familiar with it. I've always done my dumps through mysqldump


When restoring my dump how can I be sure that this replicates to the remote server? Will the cluster think my dump is the "old" data and not replicate it?

  • "Query1 from remote server commits on local server MySQL_DUMP Query2 from remote server commits on local server" This is not an inconsistent state. If you need q1 and q2 to be an atomic change then wrap it into a transaction. Now they're two different transactions.
    – akuzminsky
    Jun 10 '14 at 23:29
  • what's the database size byte-wise?
    – akuzminsky
    Jun 10 '14 at 23:30
  • @akuzminsky - Well it's de-facto atomic because we assume the entire script executes meaning both run. This is how most PHP scripts are written... Do a query, calculate stuff, and do some more queries. The DB is a few hundred MB Jun 10 '14 at 23:37
  • 1
    No. Atomic means begin-update-update-commit. In your example these are two different transactions. This might be not what you want, but one can't call this inconsistent state.
    – akuzminsky
    Jun 10 '14 at 23:40
  • @akuzminsky Wow interesting point. As a sysadmin I've killed a lot of PHP scripts and bounced apache a lot in my day. I've never had issues with corrupt databases but could this theoretically leave the DB inconsistant? Imagine a vBulletin forum where the person created a post but the script terminates before the post count is updated.... or is the MySQL engine smart enough to handle and recover from this? I've never seen anyone care to begin\commit transactions even in commercial software and never thought this would be a problem. Jun 10 '14 at 23:50

I would strongly urge you to use Percona's XtraBackup. You're already using their storage engine - take the time to get to know this tool! Good reasons not to use mysqldump can be found here. If you really have to get text file SQL dumps of your data, check out mydumper, see here and here for why (disclaimer - haven't used).

  • I just feel safer using mysqldump since I have the actual raw SQL. I plan on doing xtrabackups also but it's comforting to know I have a SQL file I can plop onto a database at any time. Is there an issue with using mysqldump besides the speed (the inconsistent state issue I mentioned) Jun 10 '14 at 22:46
  • Check this out - I know it's "nice" to have human readable backups, but perhaps leaving this (complicated) area in the hands of the experts is the best choice? The important bit in the link above is "the downside of mysqldump and rsync is that your cluster becomes READ-ONLY while data is being copied from one node to another" whereas Xtrabackup does not.
    – Vérace
    Jun 10 '14 at 22:57
  • I'm not worried about taking down the database for a few minutes. This will be done off hours. If possible I could even pull a node offline, start it in "non-clustered" mode, and dump it. Is this possible? Jun 10 '14 at 23:09
  • I don't know for sure, but why not? You could test and see, but it strikes me that you're making extra (Xtra?) work for yourself when you have tools that will do the heavy lifting for you. Test and see.
    – Vérace
    Jun 10 '14 at 23:32
  • Xtra lol. Ok I will focus on xtrabackup. However another commenter mentioned that my queries are not wrapped in a single transaction and thus are not atomic. I am actually using ActiveRecord which does a ton of non-atomic queries. Could xtrabackup theoretically get my data in an inconsistent state (since it does a hot backup) if a PHP script fires at the right time? Would it be safer to pull the node offline first? Jun 10 '14 at 23:42

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.