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:
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
counts. When a user submits a form a PHP script updates the users favorite color in
users as well as increments a field in
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"; mysql_query($query1); mysql_query($query2); ?>
My worry is that while running the
mysqldump in parallel the queries run as follows:
QUERY_1 runs MySQL_DUMP Query_2 runs -or- Query1 from remote server commits on local server MySQL_DUMP 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
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?